Upserts with Delta Lake MERGE
Write idempotent upserts with MERGE INTO, handle duplicate source keys, apply deletes from CDC and avoid rewriting the whole table.
DatabricksIntermediate2 min readUpdated 11 Oct 2026
The basic upsert
MERGE INTO silver.customers AS t
USING updates AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;The updated_at condition makes it safe to replay: an older change never overwrites a newer one.
Deduplicate the source first
MERGE fails when several source rows match the same target row. Keep the latest per key before merging:
CREATE OR REPLACE TEMP VIEW updates AS
SELECT * FROM staged_changes
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;Deletes from CDC
MERGE INTO silver.customers t
USING updates s ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'DELETE' THEN DELETE
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'DELETE' THEN INSERT *;Keep MERGE fast
- Add a partition or date predicate to the
ONclause so Delta reads only the affected files. - Run
OPTIMIZEregularly. MERGE rewrites whole files, and smaller files mean less rewriting. - Enable deletion vectors where available, so updates mark rows instead of rewriting files.
Finished this note?