A MERGE can be atomic and still leave duplicate business keys. The missing ingredient is a clear key and version contract.
This tutorial builds a current-state orders table, not an event-history table. One row represents one order_id. It assumes a single writer and a source-provided, monotonically increasing integer version per order. Timestamps alone are not that contract.
Why the obvious MERGE fails
If two source rows match one existing target row and an update is required, BigQuery can reject the merge. If the key is absent from the target, multiple source rows can instead be inserted: rows inserted by the statement are not matched again within that same statement. Deduplicate the source before merging.
1. Create an isolated, runnable example
Run this GoogleSQL script in the BigQuery console. It uses temporary tables and does not modify your production tables.
CREATE TEMP TABLE orders ( order_id INT64, status STRING, source_version INT64 ); INSERT INTO orders VALUES (101, 'created', 1); CREATE TEMP TABLE incoming ( order_id INT64, status STRING, source_version INT64 ); INSERT INTO incoming VALUES (101, 'paid', 2), (101, 'paid', 2), -- identical delivery retry (102, 'created', 1), (102, 'paid', 2), (103, 'created', 1); ASSERT (SELECT COUNT(*) = 0 FROM incoming WHERE order_id IS NULL OR source_version IS NULL) AS 'Null key or version: quarantine this batch'; -- A version must identify one state. Do not pick a random -- winner when two payloads claim the same key/version. ASSERT ( SELECT COUNT(*) = 0 FROM ( SELECT order_id, source_version FROM incoming GROUP BY order_id, source_version HAVING COUNT(DISTINCT TO_JSON_STRING(STRUCT(status))) > 1 ) ) AS 'Conflicting payloads for the same key/version'; CREATE TEMP TABLE batch_latest AS SELECT order_id, status, source_version FROM incoming QUALIFY ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY source_version DESC ) = 1; -- Ties are harmless here only because the assertion above -- guarantees identical payloads at a given key/version. MERGE orders AS T USING batch_latest AS S ON T.order_id = S.order_id WHEN MATCHED AND S.source_version > T.source_version THEN UPDATE SET status = S.status, source_version = S.source_version WHEN NOT MATCHED THEN INSERT (order_id, status, source_version) VALUES (S.order_id, S.status, S.source_version); SELECT * FROM orders ORDER BY order_id; Expected result by inspection:
| order_id | status | source_version |
|---|---|---|
| 101 | paid | 2 |
| 102 | paid | 2 |
| 103 | created | 1 |
2. Check replay and late arrival
In the same script, repeat only the MERGE statement against the same temporary tables. The result should stay unchanged: equal versions do not update and existing keys do not insert.
Next, replace incoming with (101, 'created', 1), rebuild batch_latest, and merge again. Order 101 must remain paid at version 2. A delayed older event cannot overwrite newer state.
Finally, try two different statuses for the same key and version. The conflict assertion should stop the batch. In a real pipeline, persist those conflicting records to a quarantine table with enough metadata for investigation.
The SQL is an illustrative example; it has not been executed against a BigQuery project for this article. Run it in your own development environment before adapting it.
3. Close the production gaps
- Existing target duplicates: this merge does not repair them. Before adopting it, check
GROUP BY order_id HAVING COUNT(*) > 1and resolve duplicates using an authoritative version policy. - Cross-batch conflicts: also compare incoming equal-version payloads with the target. A different payload at an already stored version is a source-contract violation, not an ordinary retry. For wider rows, compare a canonical representation of all business fields.
- Concurrency: keep one writer for the table or serialize overlapping key ranges. This example is not a global uniqueness guarantee for independent concurrent writers.
- Keys: reject null keys. SQL null equality does not behave like ordinary equality. Composite keys need all their business-key columns in both the partition and join.
- Deletes: define their semantics explicitly. Physically deleting the row also removes its version guard; an old replay can resurrect it. Retain a versioned deletion marker or a durable version ledger when that matters.
- Partition filters: do not narrow the target to recent ingestion dates if older rows with the same key can still exist. A missed match becomes an insert.
BigQuery primary and foreign key constraints are not enforced, so declaring a primary key does not replace these checks.
A useful acceptance test
Test a new key with duplicates, an existing key with multiple versions, an exact replay, an older replay, null keys, conflicting equal versions and two overlapping writers. Verify both row count and final state. “The job succeeded” is not a data-quality assertion.
References: BigQuery MERGE semantics and BigQuery primary and foreign keys.
Saravana Kumar | TechX. Original tutorial generated by an AI assistant without human editing; example results are reasoned expectations, not a claim of production testing.
