Why does a Delta Lake MERGE INTO fail when the source contains duplicate keys?

## What MERGE INTO is doing ```sql MERGE INTO customers t USING updates s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *; ``` Delta evaluates the `ON` condition as a join between the target table and the source relation, decides per target row whether a matched clause applies, and then rewrites the data files containing affected rows. The whole thing lands as one atomic commit. The critical property is that each target row can receive **at most one** matched action. That is not an implementation shortcut; it is what makes the statement deterministic. ## Why duplicates in the source break it If the source has two rows with `customer_id = 42` and the target has one, the join produces two candidate updates for the same target row. Their `SET` expressions may differ, and there is no ordering guarantee in a distributed join, so the outcome would depend on scheduling. Delta detects the situation and fails the statement with an error stating that multiple source rows matched — and, importantly, **fails the entire merge**: nothing is committed, so the target is not left half-applied. The practical trigger is nearly always a change feed. A CDC batch covering five minutes routinely contains an insert and two updates for the same primary key. Feeding it straight into `MERGE` works while those keys are new and starts failing the moment they exist in the target — which is why this shows up in production rather than in the first test run. ## The fix: deduplicate the source Collapse the source to one row per key, choosing the winner by an explicit ordering column — a sequence number, LSN or event timestamp: ```sql WITH latest AS ( SELECT * FROM ( SELECT s.*, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY event_ts DESC, op_seq DESC ) AS rn FROM updates s ) WHERE rn = 1 ) MERGE INTO customers t USING latest s ON t.customer_id = s.customer_id WHEN MATCHED AND s.op = 'D' THEN DELETE WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT *; ``` Two details worth stating in an interview. First, the ordering column must be a genuine sequencer; ordering by a wall-clock ingestion time with ties reintroduces the nondeterminism you were trying to remove, so include a tiebreaker. Second, a delete must be handled through the same last-write-wins pick — if the final event for a key is a delete, that is the row that must survive deduplication, otherwise you resurrect deleted records. ## The asymmetry with inserts Rows matching nothing in the target are inserted, one per source row, with no duplicate check at all. So the same un-deduplicated batch produces two behaviours over the lifetime of a table: duplicates on the first load, hard failures afterwards. If your table has no enforced primary key — and Delta does not enforce one — that first-load duplication is silent. When someone reports "our dimension table has duplicate keys but the merge has never failed," this is usually the reason. ## Target-side matching The converse — several **target** rows matching one source row — is fine and intended: it updates all of them. That is how you apply a correction across a fact table. Multiplicity is only a problem on the source side of a matched action. ## Cost, and why merge is a maintenance topic A merge rewrites every data file that contains an affected row, even if only one row in a gigabyte file changed. Two consequences follow. First, narrow the search: add predicates on partition or clustering columns to the `ON` condition (`AND t.event_date >= current_date() - 3`) so file pruning applies and the merge does not scan and rewrite the entire table. Second, expect small-file churn from frequent merges, which is what makes scheduled `OPTIMIZE` a companion to a merge-based pipeline. On runtimes where deletion vectors are enabled for the table, an update no longer necessarily rewrites the whole file: affected rows can be marked in a deletion vector and the new values appended, which shortens the write substantially at the cost of a merge-on-read step for readers. Even then, deduplicating the source is still mandatory — deletion vectors change how the write is applied, not the determinism rule. ## Related edges `WHEN NOT MATCHED BY SOURCE THEN DELETE` extends merge to full-table synchronization: rows in the target with no counterpart in the source are removed. It is powerful and dangerous — with a partial source batch it deletes almost everything, so it belongs with a `WHEN NOT MATCHED BY SOURCE AND t.partition_key IN (...)` guard scoping it to the range the batch actually covers.