Study guide
10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO
Microsoft Certified: Azure Databricks Data Engineer Associate DP-750
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Azure Databricks Architecture & Compute Selection
1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute
2 Chapter 2: Compute Configuration, Performance & Cost Management
2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions
3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup
3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown
4 Chapter 4: Unity Catalog Privilege Model & Access Control
4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation
5 Chapter 5: Advanced Data Security, Masking & ABAC
5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks
6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing
6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions
7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming
7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory
8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance
8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg
9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture
9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment
10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables
10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs
11 Chapter 11: Workflow Orchestration with Lakeflow Jobs
11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling
12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles
12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines
13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning
13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics
10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO
Key Takeaways Explain This Topic AI
Lakeflow / DLT APPLY CHANGES INTO (or dlt.apply_changes() in Python) provides declarative Change Data Capture (CDC) ingestion that automatically handles out-of-order records, updates, and deletes without manual MERGE INTO logic.
The SEQUENCE BY clause specifies the ordering column (such as commit timestamp or log sequence number) used by the engine to resolve out-of-order and duplicate CDC records deterministically.
STORED AS SCD TYPE 1 overwrites existing records to maintain current state, while STORED AS SCD TYPE 2 maintains historical versions by generating __start_at and __end_at validity timestamps.
The IGNORE NULL UPDATES parameter prevents partial update payloads containing null fields from accidentally overwriting existing valid attribute values in the target table.
Downstream tables cannot consume an APPLY CHANGES target table as an incremental stream using read_stream() if SCD Type 1 updates or deletes occur, because non-append operations break streaming linear history.
Last updated: August 2026
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:
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.
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
Parameter (SQL / Python) Required? Technical Description
KEYS / keys Yes Primary or composite business key(s) (e.g., (customer_id) or ['store_id', 'product_id'] ) used to uniquely match entities.
SEQUENCE BY / sequence_by Yes The monotonically increasing ordering column (timestamp, integer sequence number, or LSN). Used to resolve out-of-order updates deterministically.
APPLY AS DELETE WHEN / apply_as_deletes Optional Boolean predicate defining when an incoming event is a logical DELETE (e.g., operation = 'DELETE' ).
APPLY AS TRUNCATE WHEN / apply_as_truncates Optional Boolean predicate identifying a full table truncation event from the source database.
IGNORE NULL UPDATES / ignore_null_updates Optional Boolean flag ( true / false ). When enabled, NULL values in update records do not overwrite existing non-null values in the target table.
COLUMNS ... EXCEPT / except_column_list Optional Excludes technical ingestion metadata columns (e.g., cdc_op , ingest_time ) from being written into the target business schema.
STORED AS SCD TYPE / stored_as_scd_type Yes Specifies dimension tracking mode: '1' (SCD Type 1 overwrite) or '2' (SCD Type 2 historical version tracking).
TRACK HISTORY ON / track_history_column_list Optional (SCD Type 2 only) Restricts versioning to a subset of columns. Modifications to non-tracked columns update the active row in place without generating a new SCD2 version.
- 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 K K with sequence number S n e w S_{new} S n e w , the engine inspects the highest sequence number S t a r g e t S_{target} S t a r g e t recorded for K K K .
If S n e w > S t a r g e t S_{new} > S_{target} S n e w > S t a r g e t : The update is applied to the target table, and the internal target sequence pointer advances to S n e w S_{new} S n e w .
If S n e w ≤ S t a r g e t S_{new} \le S_{target} S n e w ≤ S t a r g e t : 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
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.
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.