Skip to content
METRIC OAK

New: the 3-week Data Platform Assessment: fixed fee, priced roadmap. See what's included →

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

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

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.

Frequently asked questions

Is Delta change data feed the same as SCD type 2?

No. Change data feed records row-level changes (inserts, updates, deletes) per table version, for a retention period. It's a log of changes, not a history table with valid-from and valid-to dates. You can build SCD type 2 from it, for example with AUTO CDC, but CDF on its own isn't a dimension history.

What does AUTO CDC do?

AUTO CDC, which replaces the earlier APPLY CHANGES APIs with the same syntax, applies a stream of changes to a target table in a Lakeflow declarative pipeline. With SCD type 2 it keeps every version of a row and adds __START_AT and __END_AT columns; the current version has __END_AT set to null.

What decides __START_AT and __END_AT in AUTO CDC?

The SEQUENCE BY column you choose, not the time the pipeline processed the row. Pick a column that reflects when the change happened in the source, such as an updated_at timestamp or a source log sequence number.

Can I build SCD type 2 from a table my application updates in place?

Yes. Enable change data feed on that table, read it as a stream, drop the update_preimage rows, and feed the rest into AUTO CDC with STORED AS SCD TYPE 2. Make sure the table has a reliable change timestamp to sequence by.

Have 30 minutes? Get a senior architect’s view of your data platform.

No sales deck. You talk to the engineer who would lead the work.

Book an architecture review
Hemant WalchaleYou talk to Hemant Walchale, founder145 published guidesProfiles: LinkedIn · Clutch · GoodFirms

Free, takes 20 seconds

Get your starter data platform roadmap

Pick your platform and your main goal. You get a 90-day plan written by senior data engineers: what to do first, next and after.

You’ll also get our monthly email (confirm it in your inbox). Unsubscribe any time. Privacy policy.