Databricks
SCD type 2 on Databricks: change data feed vs AUTO CDC
Why Delta change data feed isn't SCD type 2, how AUTO CDC builds history with __START_AT and __END_AT, how to feed it from tables your applications write to, and the sequencing and backfill gotchas.

Hemant Walchale
· 4 min read
A common question on Databricks: “Our application writes to a Delta table, and we turned on change data feed. Do we have history now?” Not quite. Change data feed (CDF) tells you what changed. SCD type 2 tells you what was true when. You usually need both, and AUTO CDC is what turns one into the other.
CDF and SCD2 are different things
| Change data feed | SCD type 2 table | |
|---|---|---|
| What it holds | Row changes per table version: insert, update_preimage, update_postimage, delete |
One row per version of each key, with validity dates |
| Time columns | _commit_version, _commit_timestamp (when Delta committed) |
Valid-from and valid-to (when the change happened in the business) |
| How long | Limited by table retention and VACUUM |
As long as you keep the table |
| Typical query | “What changed since version 120?” | “What was this customer’s segment on 1 March?” |
CDF’s timestamps are commit times. If a batch loads a week of changes at once, every change gets the same commit time. That’s fine for incremental processing, and wrong for history.
Building SCD2 with AUTO CDC
In a Lakeflow declarative pipeline, AUTO CDC applies a change stream to a target and can keep history:
CREATE OR REFRESH STREAMING TABLE customers_history;
CREATE FLOW customers_scd2 AS AUTO CDC INTO customers_history
FROM (
SELECT * FROM STREAM app.customers WITH (readChangeFeed = true)
WHERE _change_type != 'update_preimage'
)
KEYS (customer_id)
APPLY AS DELETE WHEN _change_type = 'delete'
SEQUENCE BY updated_at
COLUMNS * EXCEPT (_change_type, _commit_version, _commit_timestamp)
STORED AS SCD TYPE 2;
The target gets __START_AT and __END_AT columns. The current version of each key has __END_AT null. In Python, the equivalent is create_auto_cdc_flow() with stored_as_scd_type=2. In Databricks SQL outside a pipeline, a streaming table can declare the same thing with FLOW AUTO CDC and a refresh schedule; Databricks’ SQL ETL tutorial shows that form.
AUTO CDC replaced the APPLY CHANGES APIs with the same syntax. Check the requirements for your workspace: current docs say the pipeline must use serverless or the Pro or Advanced edition.
The gotchas
1. SEQUENCE BY decides history, so choose it carefully. __START_AT and __END_AT come from the sequencing column, not from processing time. Use a timestamp the application sets when the business change happens (updated_at), or a source log sequence number. Databricks’ tutorial sequences by _commit_timestamp, which works when each change is committed on its own, but puts every change in a bulk load at the same moment. If the column has ties for the same key, add a tiebreaker (for example a struct of timestamp and sequence number), or the order of the two changes is ambiguous.
2. Drop update_preimage rows. CDF emits both the before and after image of each update. Feed only the after image into AUTO CDC, or every update looks like two changes.
3. Decide what a delete means. With APPLY AS DELETE WHEN, a deleted key’s last version gets an end date. Without it, deletes are ignored and the row stays current forever.
4. Choose which columns create history. By default, any column change creates a new version. If a last_seen_at column changes on every write, you get a new version every time. Use TRACK HISTORY ON (or EXCEPT) to limit history to the columns the business cares about.
5. Backfills. When you load old changes, history is placed by the sequence column, so a correct historical updated_at puts old changes in the right place. Test a backfill on a copy first, and check the docs for how your pipeline’s flows can feed the target.
6. CDF retention. CDF data is removed with old table versions. If the history pipeline is down for longer than the retention period, you lose changes. Monitor the pipeline, and don’t rely on CDF as the long-term archive.
Without a declarative pipeline
If you can’t use Lakeflow declarative pipelines, you can build SCD2 with MERGE from a CDF stream in foreachBatch: close the current row (set valid_to) and insert the new version in the same batch, keyed and sequenced the same way. It works, but you own every edge case the list above describes. AUTO CDC handles out-of-order changes and the bookkeeping for you.
The short version
Change data feed is a change log; SCD type 2 is history. Feed CDF (minus preimages) into AUTO CDC with STORED AS SCD TYPE 2, sequence by a business timestamp, decide how deletes and noisy columns are handled, and monitor the pipeline so CDF retention never runs out. Related: what is change data capture, idempotent Databricks jobs and what is medallion architecture.
Sources
- The AUTO CDC APIs: simplify change data capture with pipelines, Databricks documentation
- AUTO CDC advanced topics, Databricks documentation
- Use Delta Lake change data feed, Databricks documentation
- SQL ETL tutorial (change data feed into AUTO CDC), Databricks documentation
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Databricks. Need people to do this alongside your team? See data engineering consultants. Unfamiliar terms are explained in the data engineering glossary.