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 ).

Loading diagram... Delta Change Data Feed (CDF) Event Generation and Downstream Propagation