Definition
Change Data Capture in Databricks uses two main approaches.
- MERGE INTO (batch) – Atomically matches incoming change records against existing rows and applies insert, update, or delete in one transaction.
- APPLY CHANGES INTO (streaming) – DLT's purpose-built tool for consuming ordered change feeds from Debezium/Kafka, handling upserts, deletes, and out-of-order events automatically.
- Sequence column – Defines the ordering of changes per key; critical for applying only the latest change when multiple changes arrive for the same key.
- SCD support – APPLY CHANGES supports Type 1 (overwrite) and Type 2 (keep history with effective and end dates).
- Change Data Feed – A Delta table property that makes the table emit its own row-level changes for downstream consumers.
Use MERGE for batch CDC, APPLY CHANGES for streaming CDC from log-based capture tools.
Imagine the National Bank of India keeps a master customer ledger, and throughout the day the source system records every change: a new customer opened an account, an address was updated, a closed account was deleted. With MERGE INTO you are a careful clerk who, in one atomic pass, looks at each change and decides: this is new, so add a page; this is an update, so overwrite the existing page; this is a deletion, so remove the page. With APPLY CHANGES INTO you hand that whole job to an automated assistant that continuously watches the live change feed and keeps the ledger in sync, even sorting out changes that arrive slightly out of order.
Understanding the Concepts
What is Change Data Capture?
Change Data Capture is the practice of capturing inserts, updates, and deletes from a source system and applying those same changes to a target table so the target stays in sync. You rarely want to reload the entire source table every time something changes: you want to apply only the deltas.
Source system (core banking DB)
┌──────────────────────────────┐
│ INSERT: ACC-9999, new acct │
│ UPDATE: ACC-1001, new addr │
│ DELETE: ACC-2045, closed │
└──────────────────────────────┘
│
▼
Target Delta table (analytics lakehouse)
Must reflect these changes accurately
How does batch CDC work with MERGE INTO?
For batch CDC, the core tool is Delta Lake's MERGE INTO. You merge a batch of change records against the target on the primary key, and in one atomic transaction you specify what to do in each case.
-- Batch CDC with MERGE INTO
MERGE INTO banking_prod.accounts AS target
USING cdc_batch AS source
ON target.account_id = source.account_id
WHEN MATCHED AND source.operation = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET
target.customer_name = source.customer_name,
target.address = source.address,
target.balance = source.balance,
target.status = source.status,
target.last_updated = source.last_updated
WHEN NOT MATCHED THEN INSERT (
account_id, customer_name, address, balance, status, last_updated
) VALUES (
source.account_id, source.customer_name, source.address,
source.balance, source.status, source.last_updated
);
How do you handle multiple changes per key in one batch?
If a batch contains several changes for the same key, you must first reduce to the latest change per key using the sequence column. Otherwise the MERGE is ambiguous.
from pyspark.sql import functions as F
from pyspark.sql.window import Window
# Raw CDC batch: may have multiple changes per account
cdc_raw = spark.read.parquet("/data/bank/cdc/batch_20250615")
# Reduce to the latest change per key using sequence number
w = Window.partitionBy("account_id").orderBy(F.col("sequence").desc())
cdc_latest = cdc_raw.withColumn("rn", F.row_number().over(w)) \
.filter("rn = 1").drop("rn")
# Now safe for MERGE: each account_id appears at most once
cdc_latest.createOrReplaceTempView("cdc_batch")
spark.sql("""
MERGE INTO banking_prod.accounts AS target
USING cdc_batch AS source
ON target.account_id = source.account_id
WHEN MATCHED AND source.operation = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *
""")
How does streaming CDC work with APPLY CHANGES INTO?
DLT's APPLY CHANGES INTO is purpose-built for continuous change feeds. The common pattern is Debezium capturing changes from the database transaction log, publishing to Kafka, and Auto Loader bringing them into a bronze table.
import dlt
from pyspark.sql.functions import col
# Bronze: ingest CDC events from Kafka via Auto Loader
@dlt.table(comment="Raw CDC events from Debezium")
def bronze_cdc_accounts():
return (
spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "json")
.load("/data/bank/cdc/accounts/")
)
# Silver: apply changes to maintain a current-state table
dlt.create_streaming_table("silver_accounts")
dlt.apply_changes(
target="silver_accounts",
source="bronze_cdc_accounts",
keys=["account_id"],
sequence_by=col("sequence_number"),
apply_as_deletes=col("operation") == "DELETE",
stored_as_scd_type=1 # Type 1: overwrite with latest
)
How does APPLY CHANGES handle out-of-order events?
APPLY CHANGES uses the sequence column to determine the correct order. If an older event arrives after a newer one for the same key, it is automatically skipped because the sequence number is lower.
Events arriving out of order:
Event 1: account ACC-1001, seq=5, balance=50000
Event 2: account ACC-1001, seq=3, balance=45000 (stale)
Event 3: account ACC-1001, seq=7, balance=55000
APPLY CHANGES result:
After Event 1: ACC-1001 balance = 50000 (seq 5 applied)
After Event 2: ACC-1001 balance = 50000 (seq 3 < 5, SKIPPED)
After Event 3: ACC-1001 balance = 55000 (seq 7 > 5, applied)
How does SCD Type 2 work with APPLY CHANGES?
For slowly changing dimensions where you need to keep history with effective and end dates, set stored_as_scd_type=2.
# SCD Type 2: keep full history
dlt.create_streaming_table("silver_accounts_history")
dlt.apply_changes(
target="silver_accounts_history",
source="bronze_cdc_accounts",
keys=["account_id"],
sequence_by=col("sequence_number"),
apply_as_deletes=col("operation") == "DELETE",
stored_as_scd_type=2 # Type 2: keep history
)
SCD Type 2 result:
account_id customer_name address __START_AT __END_AT
ACC-1001 Rajesh Old Address 2025-01-01 2025-06-15
ACC-1001 Rajesh New Address 2025-06-15 NULL
What is Delta Change Data Feed?
Delta's Change Data Feed (CDF) feature makes a Delta table emit its own row-level changes. Downstream consumers can read only the changes since their last checkpoint.
-- Enable Change Data Feed on a table
ALTER TABLE banking_prod.accounts
SET TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true');
-- Read changes between versions
SELECT * FROM table_changes('banking_prod.accounts', 3, 5);
account_id balance _change_type _commit_version
ACC-1001 50000 update_postimage 4
ACC-1001 45000 update_preimage 4
ACC-9999 30000 insert 5
# Streaming read of changes for downstream processing
changes = spark.readStream.format("delta") \
.option("readChangeFeed", "true") \
.option("startingVersion", 3) \
.table("banking_prod.accounts")
# Delta tables can be both CDC targets and CDC sources
What should a senior engineer know?
- CDC is only correct if you apply changes in the right order per key and the operation is idempotent. The sequence column and Delta's ACID guarantees ensure this.
- Use MERGE for batch or micro-batch CDC where you control the batching. Use APPLY CHANGES for continuous streaming CDC from log-based capture.
- Debezium reads the database transaction log, so it captures every change without polling or missing updates.
- A mid-stream failure leaves the target consistent because Delta MERGE is atomic and DLT checkpoints are managed.
# End-to-end CDC pattern:
# 1. Source DB → Debezium → Kafka (captures changes)
# 2. Kafka → Auto Loader → Bronze DLT table (ingests)
# 3. Bronze → APPLY CHANGES → Silver (current state)
# 4. Silver → Gold (aggregations and reports)
Visual Overview
CDC Pipeline in Databricks:
Source Database
┌─────────────┐
│ INSERT │
│ UPDATE │──► Debezium ──► Kafka Topic
│ DELETE │ (reads (change events)
└─────────────┘ txn log)
│
▼
┌──────────────────────────────────────────┐
│ Batch CDC: │
│ Kafka → batch read → deduplicate │
│ → MERGE INTO target (atomic) │
└──────────────────────────────────────────┘
┌──────────────────────────────────────────┐
│ Streaming CDC: │
│ Kafka → Auto Loader → Bronze DLT │
│ → APPLY CHANGES INTO silver │
│ (handles ordering, deletes, SCD) │
└──────────────────────────────────────────┘
Silver Table (current state)
│
▼
Gold Tables (aggregations, reports)
Key Takeaway
Change Data Capture keeps a target table in sync with a source by applying only the inserts, updates, and deletes from the source, rather than reloading everything. In Databricks there are two main approaches: batch CDC with MERGE INTO, and streaming CDC with DLT APPLY CHANGES INTO.
For batch CDC, MERGE INTO atomically matches change records against the target on the primary key and applies insert, update, or delete in one transaction. The important subtlety is that multiple changes for the same key in one batch must be reduced to the latest using the sequence column before the merge. For streaming CDC, APPLY CHANGES INTO consumes a continuous change feed from Debezium through Kafka, handles ordering automatically using the sequence column, skips stale out-of-order events, and supports SCD Type 1 (overwrite) and Type 2 (history).
At the National Bank of India, the core banking database was the authoritative source for accounts, and the analytics lakehouse needed a current copy. For scheduled batches we used MERGE INTO keyed on account_id, reducing to the latest change first. For the continuous feed from Debezium we used APPLY CHANGES, which kept the table in sync in near real time and handled deletes for closed accounts. Delta's ACID guarantees meant a mid-stream failure left the target consistent.
Delta's Change Data Feed lets a table emit its own changes for downstream consumers, so a Delta table can be both a CDC target and a CDC source for further layers.
Remember one sentence: use MERGE INTO for batch CDC and APPLY CHANGES INTO for streaming CDC, always ordered by a sequence per key and backed by Delta's atomicity.
---