MERGE / UPDATE / DELETE
Databricks · Delta Lake
Theory
Delta tables support row-level changes in SQL and Python. UPDATE changes columns for rows that match a condition, DELETE removes matching rows, and MERGE INTO combines both with inserts: it joins a source (new or changed data) to the target on a key and, for each row, runs WHEN MATCHED THEN UPDATE (or DELETE), WHEN NOT MATCHED THEN INSERT, and optionally WHEN NOT MATCHED BY SOURCE for target rows missing from the source. This is the standard way to upsert and to apply CDC feeds.
Under the hood Delta rewrites only the files that contain affected rows, or, with deletion vectors enabled, just marks the old rows as removed, then commits a new version atomically. For example, merging updates (102, 850) and (106, 700) into orders updates order 102 and inserts order 106 in one transaction. Each source row must match at most one target row, otherwise MERGE fails.
Why we use it
Data warehouses need upserts: late-arriving updates, slowly changing dimensions and change data capture. Without MERGE you would have to rewrite whole tables or partitions by hand, which is slow and risky.
When to use it
Use MERGE for upserts and CDC; UPDATE or DELETE for simple fixes and GDPR-style deletions. For append-only data, plain INSERT or append is cheaper. Deduplicate the source first so each key appears once.
How it works
- Read the source rows
- Join source to target on the merge key
- Does the source row match a target row? Yes: WHEN MATCHED: update (or delete) the target row. No: WHEN NOT MATCHED: insert it.
- Write changed files or deletion vectors
- Commit one new table version
Syntax
MERGE INTO target t USING source s ON t.key = s.key
WHEN MATCHED THEN UPDATE SET * | UPDATE SET col = s.col | DELETE
WHEN NOT MATCHED THEN INSERT * | INSERT (cols) VALUES (...)
WHEN NOT MATCHED BY SOURCE THEN DELETE
UPDATE t SET col = expr WHERE cond
DELETE FROM t WHERE cond
Python: DeltaTable.forName(spark, name).merge(src, cond).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()Example
MERGE INTO main.sales.orders AS t
USING main.sales.order_updates AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN
UPDATE SET t.region = s.region, t.amount = s.amount
WHEN NOT MATCHED THEN
INSERT (order_id, region, amount) VALUES (s.order_id, s.region, s.amount);
UPDATE main.sales.orders SET region = 'North' WHERE order_id = 104;
DELETE FROM main.sales.orders WHERE order_date < '2020-01-01';
# Python
from delta.tables import DeltaTable
DeltaTable.forName(spark, "main.sales.orders").alias("t") \
.merge(updates_df.alias("s"), "t.order_id = s.order_id") \
.whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()Key points
- MERGE = update matched rows + insert new rows in one atomic commit
- A source key that matches several target rows makes MERGE fail; dedupe the source
- WHEN NOT MATCHED BY SOURCE handles rows deleted at the source
- UPDATE SET * and INSERT * map columns by name
- Deletion vectors make UPDATE, DELETE and MERGE faster by not rewriting whole files