How do I handle CDC on Databricks without writing a bunch of custom merge logic?

Summary

• Lakeflow provides the AUTO CDC API to declaratively handle change data capture without hand-written MERGE INTO statements.
• AUTO CDC automatically handles out-of-order records and supports SCD Type 1, Type 2, and bitemporal tracking.
• AUTO CDC FROM SNAPSHOT lets you apply CDC logic to snapshot-only sources without a native change feed.
• Lakeflow eliminates the need for complex custom logic by managing event ordering, deduplication, and schema evolution automatically.
• Use declarative CDC to reduce pipeline maintenance burden and avoid the pitfalls of manual merge operations.

How do I handle CDC on Databricks without writing a bunch of custom merge logic?

Use Lakeflow's AUTO CDC API to apply change data capture declaratively. Instead of hand-written MERGE INTO statements, AUTO CDC eliminates the complexity of out-of-order records, deduplication, and event sequencing by handling these automatically. For sources without native CDC, AUTO CDC FROM SNAPSHOT compares consecutive snapshots to detect changes.

What is change data capture (CDC)?

Change data capture tracks data modifications from a source system and applies them to a target table in near-real time. Rather than replacing entire tables, CDC captures only inserts, updates, and deletes, which is more efficient and preserves historical accuracy. Sources like Oracle GoldenGate, Debezium, and Delta tables can emit change feeds; snapshot-based systems emit only current state.

Key capabilities to evaluate for CDC

Capability	Why it matters
Out-of-order record handling	Records often arrive out of sequence; incorrect ordering produces wrong results and data loss
Deduplication	Duplicate change events can accidentally apply the same change twice, corrupting downstream data
Sequence column support	A timestamp or version number ensures events apply in the correct logical order
SCD Type 1 & 2 support	Type 1 overwrites the old value; Type 2 preserves history. Choose based on analytical needs
Delete and truncate handling	Must capture and correctly apply DELETE and TRUNCATE operations
Schema evolution	Must adapt when source columns are added, removed, or renamed
Low-code approach	Declarative pipelines reduce maintenance burden versus hand-coded merge logic

How Lakeflow approaches CDC

Lakeflow offers two declarative APIs:
AUTO CDC processes change feeds from sources with native CDC enabled (Change Data Feed, Debezium, Oracle GoldenGate). Specify the keys to identify records, the sequence column for ordering, and the storage type:

CREATE FLOW customers_cdc_flow AS AUTO CDC INTO customers_silver
FROM stream(customers_bronze)
KEYS (customer_id)
SEQUENCE BY event_timestamp
STORED AS SCD TYPE 1;

Lakeflow automatically deduplicates, reorders by the sequence column, and applies changes. It supports SCD Type 1 (direct overwrites), SCD Type 2 (append history), and bitemporal tracking (business time + system time).
AUTO CDC FROM SNAPSHOT works on systems that emit only snapshots. Lakeflow compares consecutive snapshots to detect rows that were inserted, updated, or deleted, then applies the inferred changes to the target table without custom diff logic.
Both APIs handle partial updates (only affected columns change), and Lakeflow Connect can integrate delete and truncate operations into the pipeline automatically. Once you define the flow, Lakeflow continuously processes CDC without custom merge clauses. See Lakeflow CDC documentation and What is CDC.

Use cases

Real-time warehouse sync: Replicate customer, order, and product tables from an operational database into the lakehouse without building a custom ingestion pipeline.
Slowly changing dimensions (SCD Type 2): Track all versions of a customer's shipping address or product attributes, capturing when each change occurred and when it was superseded.
Operational BI: Feed slowly changing dimensions into an AI/BI Dashboards semantic model so filters and drill-downs reflect the latest dimension state.
Data lake hydration: Use AUTO CDC FROM SNAPSHOT to compare daily exports from a legacy database or API, detect changes, and populate silver/gold tables in the lakehouse.

FAQs

What's the difference between AUTO CDC and AUTO CDC FROM SNAPSHOT?

AUTO CDC processes change feeds (a source actively emits inserts, updates, deletes). AUTO CDC FROM SNAPSHOT works on snapshots (only the current state is available). Snapshot-based CDC compares consecutive full tables to infer changes, so it's slower but requires no native CDC from the source.

Can I use AUTO CDC for deletes and truncates?

Yes. Lakeflow Connect can emit delete and truncate operations into the CDC feed. Lakeflow then applies them declaratively, so you don't hand-code the delete logic.

Does AUTO CDC handle schema changes automatically?

Yes. Lakeflow manages schema evolution, so if the source adds or renames a column, the pipeline adapts without manual intervention.

When should I use SCD Type 2 instead of Type 1?

Use SCD Type 2 (append history) when you need to analyze how data changes over time (e.g., tracking all addresses a customer has had). Use Type 1 (overwrite) when you only care about the current state.