TopolearnIncremental pipelines upsert data with MERGE, and BigQuery's DML has quirks that differ from OLTP databases.
BigQuery supports full DML: INSERT, UPDATE, DELETE, TRUNCATE TABLE and MERGE.
UPDATEandDELETErequire aWHEREclause; writeWHERE TRUEto affect every row.MERGEmatches a source to a target and runsWHEN MATCHED THEN UPDATE | DELETE,WHEN NOT MATCHED [BY TARGET] THEN INSERTandWHEN NOT MATCHED BY SOURCE THEN ...branches, the standard way to upsert a batch of changes.- DML is billed like a query: on-demand, by bytes scanned in the tables it reads and modifies.
Performance traits come from the storage design. Data lives in large immutable files, so DML rewrites whole files, and it is built for set-based batches, not row-by-row changes. One MERGE of 100,000 changed rows beats 100,000 single-row UPDATEs by orders of magnitude. Only a small number of mutating DML statements run concurrently on one table and the rest queue, so many parallel small writers collide. Filtering DML on partition and clustering columns limits how much it rewrites.
Multi-statement transactions give atomicity across statements and tables in a script: BEGIN TRANSACTION; ... COMMIT TRANSACTION;, with ROLLBACK TRANSACTION on error. They use snapshot isolation; a conflicting concurrent change makes one transaction fail.
Rows recently written with the legacy insertAll streaming API sit in a streaming buffer and can't be changed by UPDATE, DELETE or MERGE until they're flushed. Prefer the Storage Write API or batch loads for data you'll modify.
Example
-- Upsert a batch of changes from a staging table
MERGE shop.customers AS t
USING shop.customers_staging AS s
ON t.id = s.id
WHEN MATCHED AND s.deleted THEN
DELETE
WHEN MATCHED THEN
UPDATE SET email = s.email, plan = s.plan, updated_at = s.updated_at
WHEN NOT MATCHED AND NOT s.deleted THEN
INSERT (id, email, plan, updated_at) VALUES (s.id, s.email, s.plan, s.updated_at);
-- Atomic multi-table change
BEGIN TRANSACTION;
INSERT INTO shop.orders_archive SELECT * FROM shop.orders WHERE status = 'closed';
DELETE FROM shop.orders WHERE status = 'closed';
COMMIT TRANSACTION;
UPDATE shop.customers SET plan = 'free' WHERE TRUE; -- WHERE is mandatoryRemember
- MERGE upserts and deletes a batch in one statement
- DML is batch-oriented: few large statements, not many tiny ones
- UPDATE/DELETE need a WHERE clause
- BEGIN/COMMIT TRANSACTION for multi-statement atomicity
Watch out for
- Updating rows one at a time from an application loop, causing queued DML and huge costs.