AUTO CDC (APPLY CHANGES) for SCD Type 1 and 2

Databricks · Declarative Pipelines (Lakeflow)

Theory

AUTO CDC is the pipeline feature for applying a change data capture (CDC) feed to a target table. It replaced the APPLY CHANGES INTO syntax, which you will still see in older code. You create a target streaming table, then a flow: CREATE FLOW ... AS AUTO CDC INTO target FROM stream(source) KEYS (id) SEQUENCE BY seq_col. KEYS says which rows match, SEQUENCE BY orders the events so late or out-of-order changes are handled correctly, APPLY AS DELETE WHEN marks delete events, and STORED AS SCD TYPE 1 or 2 picks the history style. Type 1 overwrites, keeping only the latest values. Type 2 keeps every version with __START_AT and __END_AT columns; the current row has __END_AT = NULL.

Example: customer C02 moves from Pune to Delhi. In SCD 1 the row now says Delhi. In SCD 2 the Pune row is closed and a new Delhi row is opened.

Why we use it

Writing MERGE logic that copes with out-of-order events, deletes and history columns is tricky and error-prone. AUTO CDC handles ordering, upserts, deletes and SCD Type 2 versioning declaratively.

When to use it

Use AUTO CDC when the source is a change feed (from Lakeflow Connect, Debezium/Kafka, or Delta change data feed). Use SCD 1 when only current values matter and SCD 2 when you must report on history. For simple full snapshots, use AUTO CDC FROM SNAPSHOT or a materialized view.

How it works

  1. CDC events arrive in customers_cdc
  2. Group events by KEYS (customer_id) and order by SEQUENCE BY seq
  3. Is the latest event a DELETE? Yes: Remove the key (SCD 1) or close its current row (SCD 2). No: Upsert the new values.
  4. Stored as SCD TYPE 2? Yes: Close the old row's __END_AT and insert a new current row. No: Overwrite the row in place.
  5. Target table reflects the changes, even if events arrived out of order

Syntax

CREATE OR REFRESH STREAMING TABLE <target>;
CREATE FLOW <flow_name> AS AUTO CDC INTO <target>
FROM stream(<cdc_source>)
KEYS (<key_cols>)
[APPLY AS DELETE WHEN <condition>]
SEQUENCE BY <ordering_col>
[COLUMNS * EXCEPT (<cols>)]
STORED AS SCD TYPE 1 | SCD TYPE 2 [TRACK HISTORY ON * EXCEPT (<cols>)];
Python: dp.create_auto_cdc_flow(target=..., source=..., keys=[...], sequence_by=..., stored_as_scd_type="2")

Example

-- customers_cdc has: customer_id, name, city, segment, op ('UPSERT'/'DELETE'), seq
CREATE OR REFRESH STREAMING TABLE customers_current;

CREATE FLOW customers_scd1 AS AUTO CDC INTO customers_current
FROM stream(customers_cdc)
KEYS (customer_id)
APPLY AS DELETE WHEN op = 'DELETE'
SEQUENCE BY seq
COLUMNS * EXCEPT (op, seq)
STORED AS SCD TYPE 1;

-- Full history of city/segment changes
CREATE OR REFRESH STREAMING TABLE customers_history;

CREATE FLOW customers_scd2 AS AUTO CDC INTO customers_history
FROM stream(customers_cdc)
KEYS (customer_id)
APPLY AS DELETE WHEN op = 'DELETE'
SEQUENCE BY seq
COLUMNS * EXCEPT (op, seq)
STORED AS SCD TYPE 2;

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic