Question

A data engineer runs a MERGE INTO command to upsert a batch of customer updates into a Delta target table. The source DataFrame contains multiple rows with the same customer_id (the merge key) because the daily extract captured several updates per customer. The MERGE fails with an error stating that a matched target row was matched by more than one source row. What is the correct approach to resolve this while keeping only the most recent update per customer?

Accepted Answer

Deduplicate the source before the MERGE by using a window function to keep only the latest record per customer_id, then run the MERGE against the deduplicated source.

Answer

Add the SKIP DUPLICATES clause to the MERGE statement so Delta automatically retains one row per key.

Answer

Change the target table to allow multiple matches by disabling ACID enforcement with a table property.

Answer

Repartition the source DataFrame by customer_id so each partition contains only one row per key before the MERGE.