Delta MERGE Complete Guide: UPSERT, Deduplication & SCD Patterns

MERGE INTO is one of the most-used data operations in Delta Lake. Because a single statement can update matched rows, insert unmatched rows, and delete rows you no longer want, it's widely used to implement UPSERT (Update or Insert), deduplication, and Slowly Changing Dimension (SCD) patterns. It's a high-frequency topic across every Data Engineer-track Databricks certification exam.

Basic Syntax

MERGE INTO is built from a target table, a source dataset, a match condition, an action for matched rows, and an action for unmatched rows.

MERGE INTO silver.customers AS t
USING bronze.raw_customers AS s
ON t.customer_id = s.customer_id

-- Matched rows: update
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET
    t.name = s.name,
    t.email = s.email,
    t.updated_at = s.updated_at

-- Unmatched rows: insert
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, created_at, updated_at)
  VALUES (s.customer_id, s.name, s.email, s.created_at, s.updated_at)

-- Rows missing from source: delete (optional)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

WHEN NOT MATCHED BY SOURCE was added in Delta Lake 2.4 and lets you handle rows that exist in the target but not in the source — i.e., rows that have been deleted upstream.

Pattern 1: UPSERT (Update or Insert)

The most fundamental pattern: update on key match, insert otherwise. This is the most common pattern when ingesting data into the Silver layer.

-- Simple UPSERT
MERGE INTO silver.products t
USING staging.new_products s
ON t.product_id = s.product_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

SET * and INSERT * mean "all columns." These shortcuts work when the source and target share the same column structure.

Pattern 2: Deduplication

The Bronze layer often receives the same record multiple times (retries, duplicate sends, etc.). The standard deduplication pattern uses MERGE plus ROW_NUMBER to ingest only the latest record per key.

MERGE INTO silver.events t
USING (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY event_id
    ORDER BY event_timestamp DESC
  ) AS rn
  FROM bronze.raw_events
  WHERE _ingested_at > current_timestamp() - INTERVAL 1 HOUR
  QUALIFY rn = 1
) s
ON t.event_id = s.event_id
WHEN MATCHED AND s.event_timestamp > t.event_timestamp
  THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

The key is to deduplicate on the source side with ROW_NUMBER before running MERGE. If the MERGE source contains duplicates, multiple matches will occur for the same key and the statement will fail.

Pattern 3: SCD Type 1 (Overwrite on Change)

SCD Type 1 simply overwrites the existing record whenever a change occurs. History is not preserved. Use it when only the latest state matters.

-- SCD Type 1: overwrite on any change
MERGE INTO dim.customers t
USING staging.customer_updates s
ON t.customer_id = s.customer_id
WHEN MATCHED AND (
  t.name != s.name OR
  t.email != s.email OR
  t.address != s.address
) THEN UPDATE SET
  t.name = s.name,
  t.email = s.email,
  t.address = s.address,
  t.updated_at = current_timestamp()
WHEN NOT MATCHED THEN INSERT *;

Pattern 4: SCD Type 2 (Historical Tracking)

SCD Type 2 expires the prior record and inserts the new version as a new row, preserving the complete change history. Lifecycle is managed via an is_current flag and a validity window (effective_from / effective_to).

-- SCD Type 2: Step 1 - expire existing records
MERGE INTO dim.customers t
USING staging.customer_updates s
ON t.customer_id = s.customer_id AND t.is_current = true
WHEN MATCHED AND (t.name != s.name OR t.email != s.email) THEN
  UPDATE SET
    t.is_current = false,
    t.effective_to = current_date();

-- SCD Type 2: Step 2 - insert new records
INSERT INTO dim.customers
SELECT
  customer_id,
  name,
  email,
  true AS is_current,
  current_date() AS effective_from,
  '9999-12-31' AS effective_to
FROM staging.customer_updates s
WHERE NOT EXISTS (
  SELECT 1 FROM dim.customers t
  WHERE t.customer_id = s.customer_id
    AND t.is_current = true
    AND t.name = s.name
    AND t.email = s.email
);

SCD Type 2 is hard to express in a single MERGE, so the typical implementation uses two steps: UPDATE then INSERT. Delta Live Tables (DLT) ships a dedicated APPLY CHANGES INTO statement for SCD Type 2 that lets you express the same logic far more concisely. This syntax is also fair game on the exams.

Pattern 5: foreachBatch + MERGE (Streaming)

To MERGE incremental data from Structured Streaming into the Silver layer, use foreachBatch. It hands each micro-batch result to you as a DataFrame, which you then MERGE.

def upsert_to_silver(batch_df, batch_id):
    # Deduplicate on the source side
    deduped = batch_df.dropDuplicates(["customer_id"])
    deduped.createOrReplaceTempView("updates")

    spark.sql("""
      MERGE INTO silver.customers t
      USING updates s
      ON t.customer_id = s.customer_id
      WHEN MATCHED THEN UPDATE SET *
      WHEN NOT MATCHED THEN INSERT *
    """)

(spark.readStream
  .format("delta")
  .table("bronze.raw_customers")
  .writeStream
  .foreachBatch(upsert_to_silver)
  .option("checkpointLocation", "/checkpoint/silver_customers")
  .trigger(availableNow=True)
  .start()
)

Performance Considerations

Key Points Tested on the Exam

• Basic MERGE INTO syntax (ON / WHEN MATCHED / WHEN NOT MATCHED)
• What SET * and INSERT * mean
• Errors caused by source duplicates and the ROW_NUMBER fix
• The difference between SCD Type 1 and Type 2
• The foreachBatch + MERGE pattern
• What WHEN NOT MATCHED BY SOURCE does
• When to use MERGE vs. INSERT OVERWRITE

Check Your Understanding

Frequently Asked Questions