Articles

BigQuery MERGE Interview Questions: Duplicate Source Rows and Partition Costs

Practise BigQuery MERGE interview questions on duplicate source rows, deterministic winners, partition pruning, snapshot deletes, and safe replay checks.

BigQuery MERGE interview questions usually turn on two decisions: whether the source identifies one unambiguous change for each target row, and whether partition predicates reduce work without changing which rows match. A merge can fail on duplicate matches, succeed while inserting duplicates, or become cheaper by silently missing an existing row. Explain the match grain before discussing performance.

This article uses official BigQuery behavior and original payment-table exercises. The examples are interview practice, not candidate reports of a particular employer's questions.

What makes a BigQuery MERGE answer convincing?

State the target's logical key, the source's row meaning, and the intended update rule. Then predict a small example before proposing SQL. “MERGE is an upsert” skips the important details: matching is not a uniqueness constraint, and an atomic statement is not automatically an idempotent pipeline.

Official facts: Google's DML reference describes MERGE as an atomic combination of insert, update, and delete actions. Matching uses the tables' state when the statement starts; a row inserted during the statement does not become a match for another source row. Multiple source matches can produce a runtime error when the matched update/delete conditions described in the reference apply.

Evidence boundary: The scenarios and repair choices below are engineering inferences from those rules. We do not assert that an employer asks them, or that the example statements have been run against a billed BigQuery project. Verify execution and costs in an appropriate test dataset before production use.

Predict duplicate behavior before writing a fix

Suppose the target stores one row per payment_id. It already contains payment 42 at source version 6. A source batch contains versions 7 and 8 for payment 42, plus two copies of new payment 99.

Ask the interviewer whether rows represent successive states or separate events. Selecting one latest state might be appropriate for a current-payment table. It would lose information if the target is an immutable event ledger. A winner rule must follow the data contract.

Original scenario	Outcome to investigate	Correctness question
Two source versions match payment 42, matched update enabled	Duplicate-match runtime error	Which version is authoritative?
Two copies of payment 99, absent at statement start	Both can take the insert path	Was the source deduplicated before matching?
Target already contains two rows for payment 42	Existing bad grain remains a separate problem	Does the target satisfy its intended uniqueness?
Nullable keys compared only with `=`	Null keys do not match each other	Should null keys be rejected or matched explicitly?

For the new payment, do not reason as if the first insert changes the matching state for the second source row. That mistaken sequential model is exactly why “MERGE prevents duplicates” is an unsafe answer.

A declared primary key is not the missing protection. BigQuery's key-constraint documentation says these constraints are not enforced. Validate the data invariant yourself; do not declare a constraint over known duplicates and expect the engine to repair them.

Deduplicate with a defined, deterministic winner

For a current-state table, use a sequence supplied by the source system. Assume this original fixture has immutable payment_date, non-null payment_id, increasing source_version, and an event_id used as a documented tie-breaker.

SELECT payment_id, payment_date, amount,
       source_version, event_id
FROM `project.staging.payment_changes`
WHERE payment_date = @run_date
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY payment_id
  ORDER BY source_version DESC, event_id DESC
) = 1;

Google's numbering-function reference warns that peers in a ROW_NUMBER ordering have nondeterministic ordering. Therefore, a timestamp alone is insufficient when two different changes share that timestamp. “Take the latest” must explain how ties are resolved.

If identical versions contain conflicting amounts, a lexicographic event identifier might produce repeatable results without producing the correct business result. Quarantine the conflict or obtain an authoritative sequence. Determinism is necessary for replay; it does not establish truth.

Inspect the deduplicated relation before the merge. Count rows per key, reject missing keys, and record how many input rows were discarded. Keep the raw batch for investigation. A reduction from three rows to one should be explainable as state selection, not an unexplained cleanup step.

The GoogleSQL query-syntax reference documents QUALIFY for filtering window-function results. You can use a subquery instead; the important interview argument is the winner contract, not the spelling of one clause.

A single-day upsert with explicit assumptions

Here is an original GoogleSQL pattern for the stated immutable-date contract. The target is partitioned by payment_date, and every source row belongs to @run_date.

MERGE `project.analytics.payments` AS T
USING (
  SELECT payment_id, payment_date, amount,
         source_version, event_id
  FROM `project.staging.payment_changes`
  WHERE payment_date = @run_date
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY payment_id
    ORDER BY source_version DESC, event_id DESC
  ) = 1
) AS S
ON T.payment_id = S.payment_id
   AND T.payment_date = @run_date
WHEN MATCHED AND S.source_version > T.source_version THEN
  UPDATE SET amount = S.amount,
             source_version = S.source_version,
             event_id = S.event_id
WHEN NOT MATCHED THEN
  INSERT (payment_id, payment_date, amount,
          source_version, event_id)
  VALUES (S.payment_id, S.payment_date, S.amount,
          S.source_version, S.event_id);

For payment 42, source versions 7 and 8 reduce to version 8. If the target contains version 6, the amount updates to version 8's value. Replaying the same batch makes no further update under the strict version comparison. An older version arriving later also cannot overwrite version 8.

That is a reasoning result under the fixture's assumptions, not a concurrency guarantee. Discuss simultaneous writers, version conflicts, batch provenance, and how operators determine whether a submitted job completed. Retrying after an uncertain client response requires checking the actual job outcome, not guessing from a timeout.

Also explain the limitation of this guard: equal versions with different payloads are ignored. The example's contract must forbid that condition or detect it before merging. A version check is not an excuse to hide conflicting data.

When partition filtering changes the answer

Imagine payment 42 currently resides in the September 30 partition, but a correction arrives with payment_date = October 1. A target predicate restricted to October 1 hides the existing row. The source row can then take the insert path, leaving two payments with the same identifier.

The apparent optimization changed the meaning of the match. Under a global payment_id key and mutable payment date, the single-day pattern above is invalid. You need a strategy that includes the old location, or a stable partitioning attribute, rather than adding a broader “latest date” assumption after the failure.

If the intended key is instead (payment_id, payment_date), both rows might be legitimate. Say that explicitly. Physical partitioning and logical identity are related design choices, but one does not define the other.

Google's partitioned-DML guidance explains pruning opportunities in source filters, match predicates, and action conditions. Pruning depends on the statement and join shape; filtering source rows alone does not prove that the target scan is bounded.

Before presenting an optimization, write down which target partitions may contain matching rows, which source partitions contain the changes, and whether late updates can cross the proposed boundary. Only then compare plans and estimated bytes.

Why NOT MATCHED BY SOURCE can delete valid data

An incremental batch is not a complete snapshot. Suppose the target has payments 42, 43, and 44 for October 1, while today's change batch contains only payment 42. A clause deleting rows absent from that source would remove 43 and 44 even if they are still valid payments.

Ask whether absence means deletion. If the source is a complete authoritative snapshot of one partition, replacement may be intended. If it contains only changes, use explicit deletion events or another justified deletion contract.

For a complete daily snapshot, the following original replacement pattern deliberately avoids matching:

MERGE `project.analytics.payments` AS T
USING (
  SELECT * FROM `project.staging.daily_snapshot`
  WHERE payment_date = @run_date
) AS S
ON FALSE
WHEN NOT MATCHED BY SOURCE
     AND T.payment_date = @run_date THEN DELETE
WHEN NOT MATCHED THEN
  INSERT (payment_id, payment_date, amount,
          source_version, event_id)
  VALUES (S.payment_id, S.payment_date, S.amount,
          S.source_version, S.event_id);

Use it only after validating snapshot completeness and source uniqueness. An accidentally empty snapshot can erase the chosen partition. Treat completeness as a release gate, with a manifest or expected coverage check appropriate to the upstream system.

The official partitioned-DML page documents constant-false replacement and warns that an unfiltered NOT MATCHED BY SOURCE action can require scanning the full target. That makes clause review both a correctness exercise and a cost exercise.

Explain bytes and performance without inventing savings

The DML reference distinguishes insert-only merges from merges containing updates or deletes. For partitioned tables it describes processed bytes using q′ for scanned input and t′ for affected target partitions: insert-only uses q′; update/delete variants use q′ + t′. Do not turn a small source batch into a claim that the whole statement is cheap.

Use BigQuery's cost-estimation guidance to compare dry-run estimates before executing a rewrite. Estimates are not the same as observed billing, and the billing model matters. Under capacity pricing, reducing scanned bytes can still improve work efficiency without translating directly into the same dollar reduction as on-demand pricing.

After a controlled execution, examine the query plan and timeline, job statistics, and result reconciliation. Record input/output rows, selected partitions, elapsed time, and slot usage where available. Preserve the original job configuration so a change in capacity or competing workload is not mistaken for a SQL improvement.

A dry run also does not exercise a duplicate-match failure or validate business correctness. Use a disposable fixture containing an existing key, a new duplicate key, a null key, a late correction, and an empty snapshot. Predict each outcome before running it.

Reconcile outcomes at the intended grain

For the payment fixture, write an expected-result sheet before execution: payment 42 should have version 8, payment 99 should appear once, and unrelated payments should remain unchanged. After a retry, those expectations should still hold. Count rows by the complete logical key, not by the physical partition alone.

A total amount can match even when individual records are wrong. One duplicated payment and one missing payment of equal value can cancel in an aggregate. Combine aggregate checks with key-level checks and a sample of changed payloads. If a merge intentionally removes records, record their keys and the deletion reason separately.

For null identifiers, decide whether the row is malformed rather than automatically adopting null-safe matching. Combining all unknown identifiers into one logical payment would be a different error. The useful interview answer connects the SQL predicate to the business meaning of an identifier, then demonstrates the chosen behavior on two null-key rows.

Five questions for rehearsing the data contract

Question	BigQuery MERGE follow-up
Design an idempotent SQL ETL for late data	Defend replay and older-version behavior
Design SQL cleaning, mapping, dedupe, and keying	Specify uniqueness before merging
Write SQL for rankings, state, and aggregations	Resolve ordering ties deterministically
Answer SQL And Data Warehouse Fundamentals For A Data Engineering Interview	Explain target grain and warehouse constraints
Load Daily JSONL LLM Chat Logs Into a Warehouse: Schema, Validation, Dedup, Idempotency	Design batch validation and reconciliation

Explain the predicted rows before recommending a cheaper query.

Sources and Further Reading

Research checked October 1, 2026. Examples require the stated data contracts; actual costs and plans depend on your tables, pricing model, and execution environment.