MERGE INTO and upserts

What it does

MERGE is the standard way to apply changes (upserts, CDC feeds, corrections) to a lakehouse table. One statement, one atomic commit, no window in which readers see half the changes.

from delta.tables import DeltaTable

(DeltaTable.forName(spark, "customers").alias("t")
    .merge(updates.alias("s"), "t.customer_id = s.customer_id")
    .whenMatchedDelete(condition="s.op = 'DELETE'")
    .whenMatchedUpdate(set={"email": "s.email", "tier": "s.tier", "updated_at": "s.ts"})
    .whenNotMatchedInsert(condition="s.op != 'DELETE'", values={
        "customer_id": "s.customer_id", "email": "s.email", "tier": "s.tier", "updated_at": "s.ts"})
    .whenNotMatchedBySourceDelete(condition="t.tier = 'trial'")
    .execute())

MERGE INTO customers t
USING updates s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET t.email = s.email, t.tier = s.tier, t.updated_at = s.ts
WHEN NOT MATCHED AND s.op != 'DELETE' THEN INSERT (customer_id, email, tier, updated_at)
  VALUES (s.customer_id, s.email, s.tier, s.ts)
WHEN NOT MATCHED BY SOURCE AND t.tier = 'trial' THEN DELETE

The clauses

• Clauses of the same kind are checked in order; the first whose condition holds is applied to a row.
• UPDATE SET * and INSERT * copy all columns by name.
• Without a NOT MATCHED BY SOURCE clause, target rows absent from the source are untouched. With an unconditional one, MERGE becomes a full sync and deletes everything not in the source: be careful.

How MERGE executes

The duplicate source error

If two source rows match the same target row, MERGE cannot know which update to apply, so it fails (Delta reports that multiple source rows matched the same target row). The fix is to make the source unique per key before merging, usually keeping the latest change:

WITH latest AS (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ts DESC) AS rn
    FROM updates)
  WHERE rn = 1)
MERGE INTO customers t
USING latest s ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Making MERGE faster

• Put selective predicates in the ON condition, for example AND t.event_date >= '2025-03-01', so file skipping can prune the target.
• Cluster the target by the merge key (Z-order or liquid clustering) so matches concentrate in few files.
• Enable deletion vectors (Delta) or merge-on-read (Iceberg) for update-heavy tables.
• Keep the source small: merge only changed rows, not a full snapshot.
• Broadcast a small source; check the join strategy in the plan.

Common mistakes