UPDATE or DELETE statement over table X would affect rows in the streaming buffer
The table has rows that were streamed in recently, and BigQuery will not run an UPDATE, a DELETE or a MERGE that updates or deletes while they are in the streaming buffer. Rows written through the legacy insertAll method or the REST Storage Write API stay in the buffer for about 30 minutes. Wait, insert instead, or write through gRPC next time.
UPDATE or DELETE statement over table my-project.my_dataset.events would affect rows in the streaming buffer, which is not supported
The text above is BigQuery’s message with a table name filled in. The table name is the full project.dataset.table.
What BigQuery means
Rows that are written to a table by streaming first go to a streaming buffer. The table has rows in the buffer, and your statement would change them. BigQuery refuses the whole statement.
Only some streaming methods cause this. Google documents that rows written through the legacy tabledata.insertAll method, or through the REST interface of the Storage Write API, cannot be changed by DML for about 30 minutes after the write. Rows written through the gRPC interface of the Storage Write API can be changed right away.
It is a runtime error. The statement is valid and a dry run passes. It only fails when the table has rows in the buffer at the moment it runs, so the same statement can work an hour later.
Which statements fail
These fail on a table that has rows in the streaming buffer:
UPDATEDELETEMERGEwithWHEN MATCHED THEN UPDATEorWHEN MATCHED THEN DELETE
These ran on the same kind of table:
INSERT- a
MERGEthat only inserts DELETE ... WHERE FALSE
Queries read the buffered rows like any other rows. A dry run counts 0 bytes for them, so the estimate of a query can be lower than the bytes the run reads.
The fix
Wait. Google puts the limit at about 30 minutes after the write. Run the statement again later. If the error comes back, the table is still receiving rows through the same method.
Insert instead of changing. INSERT and a MERGE that only inserts work while rows are in the buffer. If the change can be a new row, for example a corrected version of a row with a later timestamp, add the row and let the query pick the newest one per key. See MERGE and QUALIFY.
Stream through gRPC next time. The gRPC Storage Write API has no such limit. Move the writer to it when you need to change rows soon after they arrive. See Streaming data into BigQuery.
How to see the buffer
The metadata of a table (tables.get) has a streamingBuffer field while the table has buffered rows:
streamingBuffer: { estimatedBytes, estimatedRows, oldestEntryTime }
oldestEntryTime is the time of the oldest row in the buffer. In our check, a table that held only two streamed rows had estimatedRows of 2 and a numRows of "0". numRows leaves the buffered rows out, so it is not the count of rows you get from SELECT COUNT(*).
tabledata.list returns the buffered rows, but its totalRows can lag behind. It showed "0" while rows came back.
Different errors that look like this one
UPDATE/MERGE must match at most one source row for each target row. A different problem: the source has duplicate keys. See UPDATE/MERGE must match at most one source row.
In Querylab.io
The editor does not mark this error. The schema the editor uses has no streaming buffer in it, and a dry run passes, so the error comes back from BigQuery when the statement runs.
The table’s Overview tab shows a Streaming buffer row, with the estimated rows and size, while the table has one. Check it before you run an UPDATE or DELETE on a table that is fed by streaming.
After a failed run, the error dialog has an About this error link to this page. There is no fix button for it, because the way out is to wait or to change how the rows are written.