10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO

10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO

DP-750 Exam Focus: Master automated Change Data Capture (CDC) processing using APPLY CHANGES INTO in SQL and dlt.apply_changes() in Python. Understand how the engine resolves out-of-order records using SEQUENCE BY, handles deletes via APPLY AS DELETES ON, processes sparse updates with IGNORE NULL UPDATES, and differentiates SCD Type 1 (in-place overwrite) from SCD Type 2 (historical version tracking with __start_at / __end_at). Know the critical downstream streaming restrictions on CDC target tables.

---

1. The Challenge of CDC Ingestion in Modern Lakehouses

Capturing changes from upstream relational databases (such as Azure SQL Database, PostgreSQL, Oracle, or MySQL via Debezium, Qlik, or Azure Data Factory) produces a continuous stream of change events: Inserts (I), Updates (U), and Deletes (D).

Implementing CDC ingestion manually in Apache Spark using traditional MERGE INTO queries presents severe technical challenges:

• Out-of-Order Delivery: Network latency or distributed source extraction frequently causes older updates to arrive after newer updates.
• Late-Arriving Deletes: If a delete event arrives before an update event due to partition skew, a naive merge could re-insert a deleted row.
• Partial / Sparse Payloads: Many CDC tools emit only modified columns in update events, leaving unmodified columns as NULL.
• SCD Type 2 Complexity: Writing custom merge logic to expire existing rows (__end_at = current_timestamp()) and insert new active versions requires complex, multi-statement transactional scripts that degrade performance.

Delta Live Tables solves these complexities with Auto CDC via the APPLY CHANGES INTO SQL statement and dlt.apply_changes() Python API.

+---------------------------------------------------------------------------------------------------------+
|                                 AUTO CDC (APPLY CHANGES INTO) ARCHITECTURE                              |
+---------------------------------------------------------------------------------------------------------+
|                                                                                                         |
|   CDC Change Stream (Debezium / Kafka / Event Hubs)                                                     |
|   +-------------------------------------------------------------------------------------------------+   |
|   | Record 1: PK=101, Val='Alice', Op='I', Seq=100 (Timestamp: 10:00:00)                            |   |
|   | Record 3: PK=101, Val='Alicia', Op='U', Seq=102 (Timestamp: 10:02:00) <-- Arrived BEFORE Rec 2! |   |
|   | Record 2: PK=101, Val='Alice M', Op='U', Seq=101 (Timestamp: 10:01:00) <-- Arrived Late!        |   |
|   +-------------------------------------------------------------------------------------------------+   |
|                                                  |                                                      |
|                                                  v                                                      |
|                    +-----------------------------------------------------------+                        |
|                    |    APPLY CHANGES INTO / dlt.apply_changes() Engine        |                        |
|                    |    - Evaluates KEYS (PK=101)                              |                        |
|                    |    - Evaluates SEQUENCE BY (Seq 100 -> 101 -> 102)        |                        |
|                    |    - Deterministically drops late Record 2 (Seq 101 < 102)|                        |
|                    +-----------------------------------------------------------+                        |
|                                                  |                                                      |
|                         +------------------------+------------------------+                             |
|                         |                                                 |                             |
|                         v                                                 v                             |
|            [ STORED AS SCD TYPE 1 ]                          [ STORED AS SCD TYPE 2 ]                   |
|            Target contains latest state only:                Target contains full historical versions:  |
|            +-----+--------+                                  +-----+--------+------------+------------+ |
|            | PK  | Val    |                                  | PK  | Val    | __start_at | __end_at   | |
|            +-----+--------+                                  +-----+--------+------------+------------+ |
|            | 101 | Alicia |                                  | 101 | Alice  | 10:00:00   | 10:02:00   | |
|            +-----+--------+                                  | 101 | Alicia | 10:02:00   | NULL       | |
|                                                              +-----+--------+------------+------------+ |
+---------------------------------------------------------------------------------------------------------+

---

2. Core Architecture and Syntax Requirements

To use Auto CDC in a declarative pipeline, you must follow a two-step pattern:

1. Declare Target Table: Declare the target streaming table using CREATE OR REFRESH STREAMING TABLE (SQL) or dlt.create_streaming_table() (Python) without an AS SELECT query body.
2. Apply Changes: Invoke APPLY CHANGES INTO (SQL) or dlt.apply_changes() (Python) targeting the declared table.

Complete SQL Syntax Reference

-- Step 1: Declare the target Streaming Table
CREATE OR REFRESH STREAMING TABLE silver_dim_customers
COMMENT "Conformed customer dimension managed via Auto CDC";

-- Step 2: Ingest CDC changes into target
APPLY CHANGES INTO LIVE.silver_dim_customers
FROM STREAM(LIVE.bronze_customers_cdc_view)
KEYS (customer_id)
APPLY AS DELETE WHEN cdc_operation = 'DELETE'
APPLY AS TRUNCATE WHEN cdc_operation = 'TRUNCATE'
SEQUENCE BY change_sequence_number
COLUMNS * EXCEPT (cdc_operation, change_sequence_number, _metadata)
STORED AS SCD TYPE 1;

Complete Python Syntax Reference

import dlt
from pyspark.sql.functions import col

# Step 1: Create target streaming table
dlt.create_streaming_table(
    name="silver_dim_customers_py",
    comment="Customer dimension managed via Auto CDC in Python"
)

# Step 2: Apply CDC changes
dlt.apply_changes(
    target="silver_dim_customers_py",
    source="bronze_customers_cdc_view",
    keys=["customer_id"],
    sequence_by=col("change_sequence_number"),
    apply_as_deletes=col("cdc_operation") == "DELETE",
    apply_as_truncates=col("cdc_operation") == "TRUNCATE",
    except_column_list=["cdc_operation", "change_sequence_number", "_metadata"],
    stored_as_scd_type="1" # or "2"
)

---

3. Detailed Parameter Breakdown

---

4. Handling Out-of-Order Events & Sparse Null Updates

Out-of-Order Event Resolution Mechanics

The SEQUENCE BY column is the backbone of Auto CDC determinism:

• When a CDC record arrives for key $K$ with sequence number $S_{new}$, the engine inspects the highest sequence number $S_{target}$ recorded for $K$.
• If $S_{new} > S_{target}$: The update is applied to the target table, and the internal target sequence pointer advances to $S_{new}$.
• If $S_{new} \le S_{target}$: The record is recognized as an out-of-order, late-arriving event or duplicate and is safely discarded without altering target state.

Sparse Updates with IGNORE NULL UPDATES

Many modern CDC engines (such as Debezium or Oracle GoldenGate) emit sparse updates to conserve bandwidth. In a sparse update, unchanged attributes are transmitted as NULL:

Target Current State: { customer_id: 401, email: 'alice@work.com', tier: 'Gold' }
Incoming Sparse CDC: { customer_id: 401, email: NULL, tier: 'Platinum', seq: 850 }

• Without IGNORE NULL UPDATES: The target email column is overwritten with NULL (data loss!).
• With IGNORE NULL UPDATES (ignore_null_updates=True): The engine retains email = 'alice@work.com' while updating tier = 'Platinum'.

-- SQL: Enabling IGNORE NULL UPDATES for Sparse Payloads
APPLY CHANGES INTO LIVE.silver_customers
FROM STREAM(LIVE.bronze_cdc_stream)
KEYS (customer_id)
SEQUENCE BY event_timestamp
IGNORE NULL UPDATES
STORED AS SCD TYPE 1;

---

5. SCD Type 1 vs. SCD Type 2 Dimensional Modeling

+---------------------------------------------------------------------------------------------------------+
|                                 SCD TYPE 1 VS. SCD TYPE 2 IN AUTO CDC                                   |
+---------------------------------------------------------------------------------------------------------+
|                                                                                                         |
|  SCD TYPE 1 (Current State Only)                                                                        |
|  - Target table schema exactly matches business columns.                                                |
|  - Existing rows are updated in-place when new sequence numbers arrive.                                 |
|  - Deleted rows are physically removed from the target active snapshot.                                 |
|  - Best for operational lookups, customer master profiles, and current-state reporting.                 |
|                                                                                                         |
|  SCD TYPE 2 (Full Historical Audit Trail)                                                               |
|  - Target table automatically adds metadata columns: `__start_at` and `__end_at`.                       |
|  - When an update occurs, the old row's `__end_at` is set to the new record's sequence timestamp,        |
|    and a new row is inserted with `__start_at = sequence_timestamp` and `__end_at = NULL`.              |
|  - The active current record is identified by `WHERE __end_at IS NULL`.                                 |
|  - Best for compliance, historical financial audit trails, and point-in-time dimensional joins.         |
|                                                                                                         |
+---------------------------------------------------------------------------------------------------------+

SCD Type 2 with Selective History Tracking (TRACK HISTORY ON)

By default in SCD Type 2, a change in any column creates a new version record. However, non-critical modifications (such as last_login_time or phone_number_verification_flag) should not trigger expensive SCD2 version splits.

Use TRACK HISTORY ON (or track_history_column_list in Python) to limit SCD2 versioning to significant attributes:

-- SQL: SCD Type 2 with Selective History Tracking
APPLY CHANGES INTO LIVE.silver_dim_customer_scd2
FROM STREAM(LIVE.bronze_customer_cdc)
KEYS (customer_id)
SEQUENCE BY updated_at
STORED AS SCD TYPE 2
TRACK HISTORY ON (address, city, state, zip_code, credit_limit);

---

6. Critical Downstream Streaming Restriction

Exam Trap / Hard Rule: A target table updated via APPLY CHANGES INTO (with SCD Type 1 or deletes) CANNOT be consumed as an incremental streaming source using STREAM(LIVE.target_table) or dlt.read_stream("target_table") in downstream pipeline steps.

Why This Limitation Exists

Streaming Tables require an append-only linear commit history. Because APPLY CHANGES INTO performs in-place UPDATE and DELETE operations on the target Parquet files, a downstream streaming query cannot determine incremental row deltas without full-table state recomputation.

The Solution for Downstream Processing

1. Read as Materialized View (Batch Snapshot): Downstream Gold tables should read the CDC target table using standard batch reads (FROM LIVE.target_table in SQL or dlt.read("target_table") in Python) within a Materialized View.
2. Enable Change Data Feed (CDF): If downstream streaming is strictly required, enable Delta Change Data Feed on the table (delta.enableChangeDataFeed = true) and query the change feed explicitly.