Deterministic deduplication before a BigQuery MERGE

An incremental load needs a rule for deciding which change wins. DISTINCT only removes identical rows; two changes for the same business key with different values remain two rows. A reliable design chooses one winner using business ordering and a stable tie-breaker.

Scenario

Illustrative lab: two versions of an order arrive in one batch. Both have the same update timestamp, so a source sequence decides which value is current.

Worked example

GoogleSQL for BigQuery. Read-only example; reviewed against documentation, not executed against a billed BigQuery project.

WITH changes AS (
  SELECT 7 order_id, TIMESTAMP '2026-10-01 09:00:00+00' updated_at,
         1 source_sequence, 'pending' status
  UNION ALL
  SELECT 7, TIMESTAMP '2026-10-01 09:00:00+00', 2, 'paid'
)
SELECT order_id, status
FROM changes
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id
  ORDER BY updated_at DESC, source_sequence DESC
) = 1;

Expected output

order_id | status
7        | paid

Walk through the code

1. Create two source records for one order using a read-only common table expression.
2. Partition the window by the business key so records compete only within the same order.
3. Sort newest timestamps first, then use the source sequence to break this deliberate timestamp tie.
4. QUALIFY retains the row with rank 1. The result contains one candidate for order 7.

Reason about the design

Use this selection as the source of a MERGE only after validating the ordering contract. A generated ingestion timestamp is not automatically the same as business event order. If two conflicting events tie on every ordering field, reject or quarantine the ambiguity rather than silently choosing a row. Source deduplication also does not repair duplicate keys already present in the target.

Common mistakes

• Using a non-deterministic ordering expression as a tie-breaker. Replaying the same batch can then produce a different winner.
• Treating append-only inserts as idempotent because the orchestration task has retries. A retry can repeat the same business effect.
• Moving a watermark before the target write is durably complete. Failure can leave source records permanently skipped.

Troubleshooting checklist

• Count source rows by business key and inspect keys with COUNT(*) greater than one.
• Look for ties in the complete ordering tuple, not only in the timestamp.
• Check the target independently for duplicate business keys and reconcile counts and amounts after the write.

Try it yourself

Change the second sequence to 1. The ordering no longer defines a unique winner. Describe a quarantine rule for conflicting equal-version events, then rerun a fixed batch twice and compare target counts and values.

Interview follow-up

How would you make a retry after a timeout safe when the first target write may have succeeded?

Questions learners ask

Does a successful MERGE prove the load is correct?

No. It confirms statement success, not that the key, ordering, delete semantics and reconciliation rules match the business requirement.

Is source deduplication enough for exactly-once processing?

No. Checkpoint recovery, repeated side effects and target write semantics must also be considered.

Primary reference

Official documentation for this topic. Check the documentation for your deployed product version before applying the example.