Skip to main content

06.5 Delta Lake MERGE, UPDATE & DELETE

Delta Lake is not just an append-only file format. Its transaction protocol lets you change table state atomically through MERGE, UPDATE and DELETE.

The important architectural distinction is:

UPDATE and DELETE change existing table state; MERGE combines matching, insertion and deletion logic into one transactional operation.

Delta MERGE UPDATE DELETE architecture

1. The mental model​

Think of a Delta mutation as:

Source/change intent | v Match target rows | +----+----+ | | MATCH NOT MATCHED | | UPDATE INSERT | optional DELETE | v Delta transaction | v New table snapshot

A mutation does not mean "edit a Parquet row in place."

Delta records a new table state through the transaction log. The physical implementation can involve reading existing files, writing replacement files, and committing adds/removes atomically.

2. UPDATE​

Use UPDATE when the target rows are already known and you need to change their values.

SQL​

UPDATE main.sales.orders
SET status = 'CLOSED'
WHERE order_date < current_date() - INTERVAL '90' DAY
  AND status = 'OPEN';

This is useful for:

  • correcting attributes
  • standardizing values
  • applying business rules
  • status transitions
  • controlled backfills

Databricks supports SQL UPDATE for Delta tables. See Databricks — UPDATE.

3. UPDATE is not a row-level in-place edit​

Conceptually:

Before

file_001.parquet file_002.parquet

UPDATE

Read affected data | Write new file(s) | Commit transaction | Old files become inactive

The transaction log determines which files belong to the current table version.

This is why an UPDATE can be much more expensive than changing a single database row.

4. DELETE​

DELETE removes rows matching a predicate.

DELETE FROM main.sales.orders
WHERE customer_id = 10042;

Typical use cases:

  • GDPR/privacy deletion
  • invalid-record removal
  • source-system corrections
  • retention policies
  • business-rule cleanup

See Databricks — DELETE.

Important​

A DELETE does not mean that the underlying Parquet bytes immediately disappear from object storage.

The Delta transaction changes the active table snapshot. Physical file cleanup is a separate lifecycle operation, normally involving VACUUM.

5. MERGE — the enterprise workhorse​

MERGE is used when incoming records must be compared with an existing target.

Typical pattern:

MERGE INTO target AS t
USING source AS s
ON t.business_key = s.business_key

WHEN MATCHED THEN
  UPDATE SET *

WHEN NOT MATCHED THEN
  INSERT *;

This gives you an atomic combination of:

  • update existing rows
  • insert new rows
  • optionally delete matched rows
  • optionally modify rows not present in the source

Databricks documents MERGE as the primary Delta operation for upsert-style workloads. Databricks — MERGE

6. MERGE clause model​

Current Databricks SQL supports three major branches:

WHEN MATCHED | +--> UPDATE | +--> DELETE

WHEN NOT MATCHED | +--> INSERT

WHEN NOT MATCHED BY SOURCE | +--> UPDATE | +--> DELETE

The third branch is particularly useful when synchronizing a target with a source that represents the authoritative state.

The current SQL syntax is documented in MERGE INTO.

7. WHEN MATCHED​

A matched source row has a corresponding target row according to the ON condition.

Update​

WHEN MATCHED THEN
  UPDATE SET
    t.amount = s.amount,
    t.status = s.status,
    t.updated_at = current_timestamp()

Conditional update​

WHEN MATCHED
  AND s.updated_at > t.updated_at
THEN UPDATE SET *

This is a powerful pattern for late-arriving or replayed data.

8. Multiple WHEN MATCHED clauses​

You can have multiple matched clauses.

Example:

WHEN MATCHED AND s.operation = 'DELETE'
  THEN DELETE

WHEN MATCHED AND s.operation = 'UPDATE'
  THEN UPDATE SET *

WHEN MATCHED
  THEN UPDATE SET *

Order matters.

Earlier conditional clauses are evaluated before later clauses. Databricks requires conditions on all but the final clause of a given type.

See MERGE semantics.

9. WHEN NOT MATCHED​

This branch handles source rows that have no target match.

WHEN NOT MATCHED THEN
  INSERT (
    order_id,
    customer_id,
    amount,
    status
  )
  VALUES (
    s.order_id,
    s.customer_id,
    s.amount,
    s.status
  )

Use explicit column mappings when the contract is sensitive.

Use INSERT * when the source and target are intentionally aligned.

10. WHEN NOT MATCHED BY SOURCE​

This clause operates on target rows that have no matching source row.

Example:

WHEN NOT MATCHED BY SOURCE
  AND t.is_active = true
THEN UPDATE SET
  t.is_active = false,
  t.closed_at = current_timestamp()

Or:

WHEN NOT MATCHED BY SOURCE
  AND t.snapshot_date = current_date()
THEN DELETE

This is useful for synchronization patterns, but it can be dangerous.

If the source represents only a partial extract, treating every missing source row as a deletion can incorrectly remove valid target records.

11. The most important MERGE rule: source uniqueness​

A MERGE can fail when multiple source rows match the same target row and the operation attempts to modify that target row.

Example:

Target

order_id = 1001

Source

order_id = 1001 order_id = 1001

Which source record should win?

There is no unambiguous answer.

Current Databricks behavior also considers matched-clause conditions when determining duplicate matches in newer runtimes; older runtimes used only the ON clause. Always verify the runtime behavior in production. Databricks — MERGE

Production rule​

Before MERGE:

SELECT business_key, COUNT(*) AS cnt
FROM source
GROUP BY business_key
HAVING COUNT(*) > 1;

Then decide how duplicates should be resolved.

12. Deterministic source deduplication​

For CDC or event streams, a common pattern is to select the latest event per business key.

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY business_key
      ORDER BY event_timestamp DESC, event_id DESC
    ) AS rn
  FROM source_changes
)
SELECT *
FROM ranked
WHERE rn = 1;

This converts:

many source events | v deterministic winner | v MERGE

The tie-breaker must itself be deterministic.

13. UPDATE versus MERGE​

RequirementPreferred operation
Change known target rowsUPDATE
Remove known target rowsDELETE
Insert-only loadINSERT
UpsertMERGE
Synchronize authoritative sourceMERGE
CDC with I/U/DMERGE
Complex source-to-target matchingMERGE

Do not automatically use MERGE for every mutation.

If a simple UPDATE can solve the problem, it may be clearer and cheaper.

14. MERGE and CDC​

A CDC feed commonly contains:

INSERT UPDATE DELETE

A Delta target can apply these events transactionally.

Conceptually:

CDC event | +---- I ---> INSERT | +---- U ---> UPDATE | +---- D ---> DELETE

Example:

MERGE INTO silver.customer AS t
USING deduplicated_cdc AS s
ON t.customer_id = s.customer_id

WHEN MATCHED AND s.operation = 'DELETE'
  THEN DELETE

WHEN MATCHED AND s.operation IN ('UPDATE', 'UPSERT')
  THEN UPDATE SET *

WHEN NOT MATCHED AND s.operation <> 'DELETE'
  THEN INSERT *;

For production CDC pipelines, also consider Lakeflow's native CDC capabilities where they fit the architecture. Databricks — MERGE

15. SCD Type 1​

SCD Type 1 keeps the latest value.

Example:

Customer 42

Old: city = Bengaluru

New: city = Hyderabad

MERGE:

MERGE INTO dim_customer AS t
USING customer_updates AS s
ON t.customer_id = s.customer_id

WHEN MATCHED THEN
  UPDATE SET *

WHEN NOT MATCHED THEN
  INSERT *;

Result:

customer_id = 42 city = Hyderabad

The old value is not represented as a current dimension row.

16. SCD Type 2​

SCD Type 2 preserves history.

Typical target:

customer_id city effective_from effective_to is_current

The architecture becomes:

Current dimension | Detect change | +---+---+ | | Close Insert old new row row

MERGE can participate in SCD2 implementations, but SCD2 is more than a single UPDATE statement. It requires deterministic change detection, effective dating, and careful handling of multiple versions.

For managed CDC pipelines, Databricks provides native SCD Type 1 and Type 2 patterns through Lakeflow pipelines. Databricks — MERGE

17. MERGE with schema evolution​

Modern Databricks supports:

MERGE WITH SCHEMA EVOLUTION INTO target
USING source
ON target.id = source.id

WHEN MATCHED THEN
  UPDATE SET *

WHEN NOT MATCHED THEN
  INSERT *;

This allows the MERGE operation to evolve the target schema where the source and assignments satisfy the supported schema-evolution rules.

For example, a new source column can be added to the target when the operation explicitly enables schema evolution.

See Update table schemas with schema evolution.

Do not confuse this with automatic data-quality validation.

A schema can evolve while bad business data is still bad.

18. MERGE performance — why it can be expensive​

A naive MERGE may search a large portion of the target table to find matches.

Conceptually:

Small source | v MERGE | +--> scan huge target | +--> shuffle | +--> compare keys | +--> rewrite affected files

This is why a MERGE can be unexpectedly expensive even when the source contains only a few thousand records.

19. Reduce the target search space​

If the business logic tells you which partitions or time ranges can contain matches, include those constraints.

Example:

MERGE INTO events AS t
USING updates AS s
ON t.event_id = s.event_id
AND t.event_date >= current_date() - INTERVAL '7' DAY
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

This can reduce the amount of target data that must be considered.

Databricks explicitly recommends reducing the search space for MERGE using known constraints. Databricks — Delta best practices

20. Small files hurt MERGE​

MERGE needs to identify files containing affected records.

If the table contains huge numbers of tiny files:

Target | | | | | | | | | | | | | | many small files

the operation can spend significant time discovering and processing files.

Good Delta table layout and file compaction therefore matter to mutation performance.

See Databricks — Delta best practices.

21. Partitioning is not automatically the answer​

A common mistake is:

"MERGE is slow, therefore add more partitions."

Over-partitioning can create small files and operational complexity.

Choose partitioning based on:

  • data volume
  • access patterns
  • cardinality
  • lifecycle
  • write patterns
  • concurrency

For many modern Delta workloads, other layout techniques such as liquid clustering may be preferable.

22. MERGE and concurrency​

MERGE is a Delta transaction.

Another concurrent transaction can modify overlapping data or metadata.

Possible outcomes include:

MERGE | +--> validation | +--> conflict | +--> retry / fail

A narrower MERGE predicate can improve both performance and reduce the likelihood of conflicts.

This connects directly to 06.3 ACID, Concurrency & Isolation.

23. Idempotency​

Production pipelines must answer:

What happens if the same batch runs twice?

A good MERGE design should produce the same final state when the same logical input is replayed.

For example:

batch_id = 2026-11-07-001

Record the batch identity and make the target mutation deterministic.

Avoid designs where rerunning the same batch blindly creates duplicate rows.

24. Idempotent CDC pattern​

A robust architecture is:

Source CDC | raw event ID | deduplicate | deterministic latest event | MERGE | Delta target

Use an immutable event identifier where possible.

For replay handling, maintain enough metadata to distinguish:

  • new event
  • duplicate event
  • late event
  • corrected event

25. UPDATE/DELETE and VACUUM​

After UPDATE or DELETE, old data files can become inactive table files.

They may still be required for:

  • time travel
  • long-running readers
  • recovery workflows
  • streaming readers

VACUUM is responsible for removing old, unreferenced files after retention rules permit it.

Therefore:

UPDATE / DELETE | v New table version | Old files become inactive | v retention | v VACUUM

Do not manually delete Delta files.

Databricks explicitly recommends not modifying Delta data or transaction-log files directly. Databricks — Delta Lake

26. Mutation versus append architecture​

For high-volume systems, ask whether every change really needs a MERGE.

Pattern A — Frequent MERGE​

CDC → MERGE → Silver

Good for mutable current-state tables.

Pattern B — Append events​

Events → append Delta → derive current state

Good when preserving the complete event history is valuable.

Pattern C — Lakeflow CDC​

CDC source ↓ AUTO CDC ↓ SCD1 / SCD2 target

Good when managed declarative CDC capabilities fit the platform.

The correct architecture depends on workload semantics, latency, history requirements and operational model.

27. SAP/BW example​

Suppose SAP procurement CDC produces:

EBELN EBELP NETPR MENGE AEDAT CHANGE_TYPE

Target:

silver.purchase_order_item

A production pipeline can use:

SAP CDC ↓ Bronze raw events ↓ deduplicate by EBELN + EBELP ↓ validate latest event ↓ MERGE ↓ Silver current-state table ↓ Gold procurement analytics

This is conceptually similar to managing delta/change records in BW, but the physical execution and storage model are different.

28. Finance example​

A finance transaction table may receive corrections after the original posting.

Example:

invoice_id = 5001 amount = 1000

Later correction:

invoice_id = 5001 amount = 1100

A deterministic MERGE can update the current state.

But if auditability is required, do not destroy the only copy of the original event.

Use:

immutable finance events + current-state Delta table + audit/history layer

This separates operational state from financial traceability.

29. Common anti-patterns​

Anti-pattern 1 — MERGE everything​

Use simpler INSERT/UPDATE/DELETE when appropriate.

Anti-pattern 2 — No source deduplication​

This causes ambiguous matches and unstable results.

Anti-pattern 3 — Full-table MERGE for tiny daily changes​

Reduce the target search space.

Anti-pattern 4 — MERGE without deterministic ordering​

Late/replayed events can overwrite newer state.

Anti-pattern 5 — Treating missing source rows as deletes​

Only use NOT MATCHED BY SOURCE when the source represents the required authoritative scope.

Anti-pattern 6 — Ignoring small files​

Mutation performance can degrade dramatically.

Anti-pattern 7 — Manually modifying Parquet files​

This bypasses Delta's transaction protocol.

30. Hands-on Lab — Basic MERGE​

Create a target:

CREATE TABLE demo.customer_target (
  customer_id INT,
  customer_name STRING,
  city STRING
) USING DELTA;

Insert initial rows:

INSERT INTO demo.customer_target VALUES
(1, 'A', 'Bengaluru'),
(2, 'B', 'Delhi');

Create a source containing:

  • an existing customer with a changed city
  • a new customer

Then execute:

MERGE INTO demo.customer_target AS t
USING demo.customer_updates AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Verify:

  • existing row updated
  • new row inserted
  • no duplicate business key

31. Hands-on Lab — Duplicate source detection​

Create two source rows with the same customer ID.

Run:

SELECT customer_id, COUNT(*) AS cnt
FROM demo.customer_updates
GROUP BY customer_id
HAVING COUNT(*) > 1;

Then fix the source with ROW_NUMBER and perform the MERGE again.

The goal is to understand why source preparation is part of MERGE design.

32. Hands-on Lab — Conditional delete​

Create a CDC source containing:

operation = DELETE

Implement:

WHEN MATCHED
  AND s.operation = 'DELETE'
THEN DELETE

Inspect the Delta table history afterward.

33. Hands-on Lab — NOT MATCHED BY SOURCE​

Create a source representing the complete authoritative state for a small test domain.

Use:

WHEN NOT MATCHED BY SOURCE
  AND t.is_active = true
THEN UPDATE SET
  t.is_active = false;

Then repeat with a partial source and observe why the same logic would be unsafe.

34. Hands-on Lab — MERGE performance​

Create a larger target and a small update set.

Compare:

Test A​

MERGE with only the business key.

Test B​

MERGE with an additional known date constraint.

Inspect:

  • scan volume
  • shuffle
  • execution time
  • files touched

Use the Spark UI / SQL query profile to explain the difference.

35. Troubleshooting workflow​

When MERGE is slow or failing:

Step 1 — Validate source uniqueness​

Check duplicate business keys.

Step 2 — Validate the match condition​

Is the ON clause selective and correct?

Step 3 — Check source scope​

Is the source complete or partial?

Step 4 — Check target layout​

Look for excessive small files and poor data locality.

Step 5 — Inspect the physical plan​

Look for scans, exchanges, joins and skew.

Step 6 — Check concurrency​

Determine whether concurrent writes are causing conflicts.

Step 7 — Check mutation volume​

A tiny logical update can still touch many physical files.

Step 8 — Check idempotency​

Can the same batch safely run again?

36. Architect decision matrix​

RequirementPreferred pattern
Append new eventsINSERT / append
Correct known rowsUPDATE
Remove known rowsDELETE
Upsert current stateMERGE
CDC I/U/DMERGE or managed CDC
Full source synchronizationMERGE + NOT MATCHED BY SOURCE
Immutable audit historyAppend event table
SCD1MERGE / managed CDC
SCD2MERGE or managed CDC
Very high mutation frequencyReconsider table/mutation architecture

37. Interview questions​

Foundation​

  1. What is MERGE in Delta Lake?
  2. What is the difference between MERGE and UPDATE?
  3. When would you use DELETE?
  4. What does WHEN MATCHED mean?
  5. What does WHEN NOT MATCHED mean?
  6. What is WHEN NOT MATCHED BY SOURCE?

Engineering​

  1. Why can MERGE fail because of duplicate source rows?
  2. How do you deduplicate a CDC source?
  3. How do you make MERGE idempotent?
  4. How do you optimize MERGE?
  5. Why do small files affect MERGE?
  6. How does MERGE interact with Delta concurrency?
  7. What happens to old files after UPDATE or DELETE?
  8. When should you use NOT MATCHED BY SOURCE?

Architect​

  1. Would you MERGE every CDC event directly into a 20 TB table?
  2. How would you design SAP CDC ingestion?
  3. How would you prevent replayed events from overwriting newer data?
  4. When would you choose append-only events over current-state MERGE?
  5. How would you design SCD2 at enterprise scale?
  6. How would you minimize MERGE conflicts across concurrent pipelines?

38. Production checklist​

  • Define the business key explicitly.
  • Validate source uniqueness before MERGE.
  • Make source ordering deterministic.
  • Use explicit conditions for business-specific updates/deletes.
  • Avoid treating partial sources as complete snapshots.
  • Reduce the target search space where possible.
  • Monitor small files and table layout.
  • Inspect physical execution for expensive MERGEs.
  • Design for replay and idempotency.
  • Understand concurrent writers.
  • Keep immutable audit/event data when required.
  • Do not manually modify Delta files.
  • Use VACUUM according to your retention and reader requirements.
  • Prefer managed CDC capabilities where they simplify operations.
  • Load-test mutation patterns at realistic scale.

39. Architect mental model​

The most useful way to remember Delta mutations is:

UPDATE = change known target state

DELETE = remove known target state

MERGE = reconcile source state with target state

And at physical scale:

Business mutation ↓ Match / predicate ↓ Read affected data ↓ Write replacement files ↓ Delta transaction ↓ New snapshot ↓ Retention / VACUUM lifecycle

The key architect question is not:

"Can Delta update this row?"

It is:

"What is the correct state-transition model, and how cheaply and safely can the lakehouse maintain it?"

Official references​