A BigQuery MERGE has to compare the source rows with the target table to decide what is a match. If you do not restrict the part of the target it looks at, it scans the whole target table every time, and you are billed for it. On a multi-terabyte table that is slow and costly, even if only a few thousand rows change.
The common mistake
MERGE analytics.orders t USING staging.orders_today s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...
Nothing here tells BigQuery which partitions of orders to read, so it reads all of them.
Make the target scan smaller
If the target is partitioned by order_date, add a condition on it in the ON clause, using values you know bound the changes:
ON t.order_id = s.order_id AND t.order_date BETWEEN DATE '2025-02-25' AND DATE '2025-03-01'
Now only those partitions are read. For this to be correct, every changed row must fall inside the window. In dbt's incremental models the equivalent setting is incremental_predicates. Clustering the target on the merge key helps too, so matching blocks can be found without a full scan.
Other limits
- Rows in the source must be unique by the merge key, or BigQuery fails because one target row matches several source rows. Deduplicate first.
- DML statements that change the same table run with limits on concurrency. Mutating statements on one table are queued, so many simultaneous MERGE jobs can wait on each other. There are also daily quotas on table modifications, so check the documentation.
- Each DML rewrites the storage it touches, so frequent small DML on big tables is wasteful.
An alternative for very frequent changes
If a table receives constant updates, you can append every change as a new row and expose the latest state with a view that picks the newest version per key, with a periodic compaction job that merges them into a base table. Reads cost a little more, writes are cheap, and you avoid a stream of tiny MERGE statements.