How do I build CDC and streaming pipelines without custom merge logic?

Summary

• Skip MERGE entirely. On Databricks you build change data capture (CDC) with the declarative AUTO CDC API in Lakeflow Pipelines (formerly APPLY CHANGES INTO): declare a target streaming table, the key columns, and a sequence column, and the engine applies inserts, updates, and deletes in the correct order.
• Out-of-order events are handled for you. A sequence column (SEQUENCE BY) gives deterministic ordering, so late-arriving records can't corrupt results, which is the failure mode that makes hand-written MERGE brittle.
• Get SCD Type 1 or Type 2 with one clause. STORED AS SCD TYPE 1 overwrites to current state; STORED AS SCD TYPE 2 keeps full history with start and end columns, with no custom history-tracking code.
• Streaming ingestion is built in. Streaming tables ingest incrementally from Auto Loader (cloud files) and message buses like Kafka, process each record once, and a single pipeline can mix streaming and batch.
• AUTO CDC is generally available and works in SQL or Python (create_auto_cdc_flow); pipelines run on serverless Lakeflow pipelines or the Pro and Advanced editions.

How do I build CDC and streaming pipelines without custom merge logic?

You build them declaratively. On Databricks, Lakeflow Pipelines provide an AUTO CDC API (formerly APPLY CHANGES INTO) that replaces hand-written MERGE statements. You declare a target streaming table, the key that identifies a row, and a sequence column that orders changes, and the engine applies every insert, update, and delete in the right order. It maintains slowly changing dimension (SCD) Type 1 or Type 2 history for you and handles late and out-of-order records on its own. For ingestion, streaming tables read incrementally from sources like Auto Loader and Kafka. The result is a pipeline defined in a few lines of SQL or Python, with no merge, reordering, or watermark code to write and maintain.

Why custom merge logic is the hard part

Teams have long used MERGE INTO to apply changes, but it's brittle for CDC. When change records arrive out of sequence, a raw merge can apply an older update on top of a newer one and produce incorrect results, so you end up writing extra logic to dedupe, reorder, and watermark events. That code is easy to get wrong and expensive to maintain across every table you replicate. The declarative approach removes it: you state the intent (these keys, ordered by this column, stored as this SCD type) and the framework generates the correct merge behavior.

How AUTO CDC works

AUTO CDC turns change application into a declaration. You point a streaming table at a change source and specify a few things, and each maps to logic you would otherwise hand-code.

CDC task	How you declare it	What you no longer hand-code
Insert or update a row	Upsert on KEYS (the default)	The match and insert branches of a MERGE
Delete a row	APPLY AS DELETE WHEN <condition>	Conditional delete handling
Handle out-of-order events	SEQUENCE BY <column>	Dedupe, reordering, and watermarks
Keep only current state	STORED AS SCD TYPE 1	Overwrite-in-place logic
Keep full history	STORED AS SCD TYPE 2	Start and end timestamping and versioning

Because ordering is declared with the sequence column, a record that arrives late can't overwrite a newer one: the engine processes changes in sequence order regardless of arrival time. The same flow is available in Python through create_auto_cdc_flow(), and you can mix SQL and Python in one pipeline. AUTO CDC is generally available; to use it, your pipeline runs on serverless Lakeflow pipelines or the Pro or Advanced editions. See the AUTO CDC documentation and the streaming tables reference.

Getting the change data in

AUTO CDC applies changes once they land in the lakehouse; you still need to capture them from the source. Lakeflow Connect provides managed ingestion connectors that bring change data from operational databases into the lakehouse without a custom extraction job. When a source has no native change feed, AUTO CDC FROM SNAPSHOT derives changes by comparing successive snapshots. Connector availability varies by source, so check the current list for your database. Once the change data lands, the same AUTO CDC declaration applies it to your target tables. For the broader picture, see the Lakeflow general availability announcement.

FAQs

What replaces MERGE INTO for CDC on Databricks?

The AUTO CDC API in Lakeflow Pipelines. You declare keys, a sequence column, and the SCD type, and it applies inserts, updates, and deletes correctly, including out-of-order records, without a hand-written MERGE.

How does AUTO CDC handle late or out-of-order records?

You specify a sequence column with SEQUENCE BY, and the engine orders changes by it before applying them, so a late record can't overwrite a newer one.

What's the difference between SCD Type 1 and Type 2 here?

STORED AS SCD TYPE 1 keeps only current state by overwriting changed rows. STORED AS SCD TYPE 2 keeps full history, adding start and end columns so you can see how a row looked at any point in time.

Can I build these pipelines in Python instead of SQL?

Yes. AUTO CDC is available in SQL and in Python through create_auto_cdc_flow(), and you can combine SQL and Python within one Lakeflow pipeline.

Does this work for batch, or only streaming?

Both. Streaming tables ingest incrementally from sources like Auto Loader and Kafka, and a single Lakeflow pipeline can combine streaming and batch datasets.