Study guide
8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns
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
8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns
Key Takeaways Explain This Topic AI
Delta Change Data Feed (CDF) captures row-level changes (inserts, updates, deletes) committed to a Delta table, allowing downstream consumers to process incremental changes efficiently.
Enabling CDF on a table ( delta.enableChangeDataFeed = true ) automatically generates CDC metadata columns: _change_type , _commit_version , and _commit_timestamp .
_change_type outputs four distinct row actions: insert , delete , update_preimage (row state before update), and update_postimage (row state after update).
MERGE INTO provides atomic upsert operations, matching source and target records on a join condition to execute conditional UPDATE , DELETE , and INSERT actions in a single transaction.
Slowly Changing Dimensions (SCD) are implemented using MERGE: SCD Type 1 performs in-place overwrites, while SCD Type 2 maintains historical record versions using effective date ranges and active flags.
Last updated: August 2026
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:
Metadata Column Data Type Description
_change_type StringType Identifies the nature of the row modification: insert , delete , update_preimage , or update_postimage .
_commit_version LongType The Delta table transaction log commit version that generated this change.
_commit_timestamp TimestampType The UTC timestamp when the transaction was committed to the log.
- 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
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.
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
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.
Deduplicate Source Data : Ensure the source DataFrame has no duplicate keys before running MERGE to avoid multiple-match exceptions.
Compact Source Data : If the source DataFrame consists of thousands of micro-batch files, coalesce or compact source data before executing the merge.
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 ).