8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns

8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns

In modern enterprise data lakehouses, downstream Silver and Gold tables frequently depend on changes occurring in upstream Bronze tables. Traditionally, propagating row-level inserts, updates, and deletes required either computationally expensive full-table diffs or custom timestamp watermarking that failed to capture deleted rows.

Delta Lake solves this with Change Data Feed (CDF)—a native mechanism that exposes row-level change events from the transaction log—and MERGE INTO, the ANSI SQL standard for atomic upsert and Slowly Changing Dimension (SCD) data pipeline processing.

---

1. Delta Change Data Feed (CDF) Architecture

Change Data Feed records row-level modifications made to a Delta table. When enabled, every INSERT, UPDATE, DELETE, and MERGE operation generates structured CDC event records alongside the standard Parquet payload files.

+-------------------------------------------------------------------------+
|                    CHANGE DATA FEED (CDF) WORKFLOW                      |
+-------------------------------------------------------------------------+
|                                                                         |
|  [ Upstream App / Source ] ---> [ Bronze Delta Table (CDF Enabled) ]    |
|                                               |                         |
|                               +---------------+---------------+         |
|                               | CDF Micro-Batch Streaming     |         |
|                               v                               v         |
|                     [ Silver Aggregations ]       [ Audit Compliance ]  |
+-------------------------------------------------------------------------+

How to Enable Change Data Feed

1. On Table Creation:

CREATE TABLE customers_bronze (
    customer_id BIGINT,
    name STRING,
    email STRING,
    status STRING,
    updated_at TIMESTAMP
)
USING DELTA
TBLPROPERTIES (delta.enableChangeDataFeed = true);

2. On Existing Tables via ALTER TABLE:

ALTER TABLE customers_bronze 
SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

3. Workspace-Wide or Session-Wide Default:

SET spark.databricks.delta.properties.defaults.enableChangeDataFeed = true;

---

2. CDF Metadata Columns & Change Types

When querying Change Data Feed, Delta Lake returns all underlying table columns plus three critical CDC metadata columns:

The Four _change_type Values

+-------------------------------------------------------------------------+
|                         _change_type VALUES                             |
+-------------------------------------------------------------------------+
|  1. insert            | Row added via INSERT, COPY INTO, or MERGE       |
|  2. delete            | Row removed via DELETE or MERGE                 |
|  3. update_preimage   | Row values BEFORE an UPDATE was applied         |
|  4. update_postimage  | Row values AFTER an UPDATE was applied          |
+-------------------------------------------------------------------------+

Exam Tip: For every updated row, CDF produces two records: an update_preimage representing the state before the update, and an update_postimage representing the state after the update. Both records share the exact same _commit_version and _commit_timestamp.

---

3. Querying Change Data Feed (Batch & Streaming)

1. Batch SQL Query using table_changes()

-- Query all changes between commit version 5 and version 12
SELECT * FROM table_changes('customers_bronze', 5, 12);

-- Query all changes since a specific timestamp
SELECT * FROM table_changes('customers_bronze', '2026-08-20 00:00:00');

2. PySpark Structured Streaming with CDF

Downstream consumers can stream only the incremental changes directly from the CDF stream:

# Read incremental changes from upstream table using Structured Streaming
df_cdf_stream = spark.readStream \
    .format("delta") \
    .option("readChangeFeed", "true") \
    .option("startingVersion", 5) \
    .table("customers_bronze")

# Filter only updated and inserted records for downstream Silver upsert
df_clean_changes = df_cdf_stream.filter(
    "_change_type IN ('insert', 'update_postimage')"
)

---

4. Atomic Upserts with MERGE INTO

MERGE INTO is an ANSI SQL statement that enables conditional multi-operation updates, inserts, and deletes against a target Delta table within a single atomic transaction.

MERGE INTO customers_silver AS target
USING incoming_updates AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND source.status = 'DELETED' THEN
    DELETE
WHEN MATCHED AND target.updated_at < source.updated_at THEN
    UPDATE SET 
        target.name = source.name,
        target.email = source.email,
        target.status = source.status,
        target.updated_at = source.updated_at
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email, status, updated_at)
    VALUES (source.customer_id, source.name, source.email, source.status, source.updated_at);

Rules and Constraints of MERGE INTO

1. Deterministic Matching: The ON join condition must match each target row to at most one source row. If multiple source rows match the same target row, the query fails with a runtime error:

org.apache.spark.sql.delta.DeltaUnsupportedOperationException: 
Cannot perform Merge as multiple source rows matched the same target row.

1. Clause Order: WHEN MATCHED clauses are evaluated in the sequential order written; only the first matching condition executes.

---

5. Implementing Slowly Changing Dimensions (SCD Type 1 & 2)

Data warehousing architectures classify dimensional updates into distinct patterns:

1. SCD Type 1 (In-Place Overwrite)

SCD Type 1 overwrites existing dimension attributes without retaining historical version records. It is implemented with standard WHEN MATCHED THEN UPDATE SET *.

2. SCD Type 2 (Historical Tracking)

SCD Type 2 preserves complete history by expiring old records (setting is_current = false and end_date = current_timestamp()) and inserting new records (with is_current = true, start_date = current_timestamp(), end_date = NULL).

-- Step 1: Stage merged dataset containing updates to close and new rows to insert
MERGE INTO customer_dimension AS target
USING (
    -- Source records to insert (both brand new customers and new versions of existing customers)
    SELECT source.customer_id AS merge_key, source.* 
    FROM source_updates source
    
    UNION ALL
    
    -- Flag existing active target records that have changed so they can be closed (expired)
    SELECT NULL AS merge_key, source.*
    FROM source_updates source
    JOIN customer_dimension target
      ON target.customer_id = source.customer_id
     AND target.is_current = true
     AND (target.email <> source.email OR target.status <> source.status)
) AS staged_changes
ON target.customer_id = staged_changes.merge_key 
AND target.is_current = true

-- Close active record (expire old version)
WHEN MATCHED AND (target.email <> staged_changes.email OR target.status <> staged_changes.status) THEN
    UPDATE SET 
        target.is_current = false,
        target.end_date = current_timestamp()

-- Insert new active record version (or brand new customer)
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email, status, start_date, end_date, is_current)
    VALUES (staged_changes.customer_id, staged_changes.name, staged_changes.email, 
            staged_changes.status, current_timestamp(), NULL, true);

---

6. MERGE Performance Optimization Best Practices

1. Target Partition / Clustering Pruning: Always include partition keys or Liquid Clustering columns in the ON join predicate (e.g., ON target.date = source.date AND target.id = source.id). This prevents Delta Lake from scanning entire tables.
2. Deduplicate Source Data: Ensure the source DataFrame has no duplicate keys before running MERGE to avoid multiple-match exceptions.
3. Compact Source Data: If the source DataFrame consists of thousands of micro-batch files, coalesce or compact source data before executing the merge.
4. Enable Low Shuffle Merge: In Databricks Runtime, low shuffle merge optimizes join execution when only a small percentage of target files are modified (SET spark.databricks.delta.merge.lowShuffle.enabled = true).