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
- CDC events arrive in customers_cdc
- Group events by KEYS (customer_id) and order by SEQUENCE BY seq
- Is the latest event a DELETE? Yes: Remove the key (SCD 1) or close its current row (SCD 2). No: Upsert the new values.
- 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.
- 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
- AUTO CDC replaces APPLY CHANGES INTO; older code and exam material may use the old name
- SEQUENCE BY must strictly order changes per key, e.g. a timestamp or log sequence number
- SCD 1 = latest values only; SCD 2 = every version with __START_AT / __END_AT
- Current SCD 2 rows have __END_AT = NULL
- TRACK HISTORY ON limits which columns create new SCD 2 versions
- Use AUTO CDC FROM SNAPSHOT when the source gives full snapshots, not change events