Quick answer: Use ROW_NUMBER() with QUALIFY to select one source row per business key. Feed that result into a partition-aware MERGE and validate affected row counts.
The challenge
Incremental files may include repeated records or updates to existing business keys. A direct load into a final analytics table can create duplicate keys or cause an invalid BigQuery MERGE when multiple source rows match the same target row.
1. Deduplicate the staging batch
SELECT
order_id, customer_id, order_total,
updated_at, ingestion_at
FROM staging.orders_delta
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC, ingestion_at DESC
) = 1;Choose an ordering field that reliably identifies the latest version. If timestamps can tie, include a deterministic source sequence or unique ingestion identifier.
2. Upsert with MERGE
MERGE curated.orders AS T
USING (
SELECT *
FROM staging.orders_delta
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC, ingestion_at DESC
) = 1
) AS S
ON T.order_id = S.order_id
WHEN MATCHED AND S.updated_at > T.updated_at THEN
UPDATE SET customer_id = S.customer_id,
order_total = S.order_total,
updated_at = S.updated_at
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, order_total, updated_at)
VALUES (S.order_id, S.customer_id, S.order_total, S.updated_at);3. Watch for production pitfalls
- Confirm business key uniqueness in the target before running the MERGE.
- Limit scans using partition filters where the table layout and correctness allow it.
- Persist batch identifiers for auditing, restarts and reconciliation.
- Handle deletions and late-arriving changes as separate, explicitly defined business rules.
Validation query
SELECT order_id, COUNT(*) AS copies
FROM curated.orders
GROUP BY order_id
HAVING COUNT(*) > 1;Adapt dataset names, types and update conditions to your own schema. This is a sample teaching pattern, not proprietary client SQL.