Hi everyone 👋
If you run incremental loads with MERGE INTO on Delta tables, you may have seen a merge fail with an error saying multiple source rows matched the same target row.
Why it happens
A MERGE compares each row in your source batch to your target table using a key, like customer_id. If the source contains two or more rows with the same key, Delta has no way of knowing which one should update the target, so it stops rather than guess. This comes up a lot with CDC feeds, event streams, and batches that capture several changes to the same record between runs.
The fix
Before merging, reduce the source to one row per key, keeping the most recent version. Rank rows within each key by a timestamp and keep only the top one. In Databricks SQL, QUALIFY makes this a one-liner you can add to your source query:
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1
Then run your MERGE against that cleaned-up result as usual.
A few gotchas
Make sure your ordering is reliable. If two rows can share the same timestamp, add a tiebreaker such as a sequence number so the "latest" row is always the same one.
Avoid simply dropping duplicates by key. Generic dedup functions keep an arbitrary row, not the newest, which can quietly write stale data into your table.
If you're using Lakeflow Declarative Pipelines (formerly Delta Live Tables), the AUTO CDC / APPLY CHANGES feature handles this ordering for you, which is worth a look for CDC-heavy workloads.
Hope this saves someone a debugging session! How do you handle duplicates before merging? I'd love to hear other approaches in the comments.
Liresa Ferizaj
