Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Keys, and why upsert presupposes them

What does a merge assume about keys, and what happens when the key is missing, duplicated, changed or null?

MERGE is a conditional rewrite: match target rows to source rows by a predicate, update on match, insert on miss, delete on a flag.

MERGE INTO dw.orders t
USING staged_changes s ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED THEN UPDATE SET status = s.status, total = s.total, last_lsn = s.lsn
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT (order_id, status, total, last_lsn)
                                      VALUES (s.order_id, s.status, s.total, s.lsn);

It presupposes a key that is unique, stable, non-null and present in both source and target. Each of those conditions is violated in practice, and each violation has its own failure.

ViolationWhat happens
No keyNo merge can be defined at all. You are limited to append plus read-time deduplication, which moves cost from write time to read time
Not uniqueThe merge is non-deterministic: several source rows match one target row, and the engine may pick one arbitrarily. The engine's "multiple matches" error is protecting you from silent corruption. Do not "fix" it by ignoring the error; fix the duplicates in the staged data (for example keep only the highest log position per key)
Mutated keyThe source reports one update. Semantically it is a delete of the old key and an insert of the new key. Without a , the old row is unreachable and becomes an orphan that never expires
Nullable keyIn SQL, NULL never equals NULL, so the merge predicate fails and every such row is inserted again, forever

A safe preparation step is to reduce the staged changes to one change per key, the one with the highest log position:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC) AS rn
  FROM staged_changes
) WHERE rn = 1;

In one line: merge is only as correct as the key's uniqueness, stability and non-nullity, and will not call one.