Home › Data engineering › SCD2: AUTO CDC or own PySpark
Data engineering · free guide · one page
SCD Type 2 on Databricks: AUTO CDC or our own PySpark?
Two ways to keep history of changing records on Databricks: let a Lakeflow pipeline do it with AUTO CDC, or write the MERGE ourselves. This page shows when each is right, for input that arrives as changes (CDC) and input that arrives as full snapshots, with clean code for both and what each costs.
Input is not CDC: if the source is a Delta table you own, turn on its change data feed so it becomes CDC. For true full extracts, AUTO CDC FROM SNAPSHOT is fine for small and medium tables; for very large ones our own hash compare can be cheaper because we choose what to compare.
Write our own when the team runs only plain jobs at high volume, or needs rules AUTO CDC cannot express. Then build one tested SCD2 writer and give each input type a small adapter, never one SCD2 notebook per table.
Cost: the per-DBU gap is usually smaller than the engineering time to own the code. What really moves the bill is how much each run reads, which the input shape decides, not the tool.
In plain words
What SCD Type 2 keeps
A Slowly Changing Dimension Type 2 never overwrites. When a tracked value changes, the current row is closed and a new one opened, and each row says when it was true: valid_from, valid_to, is_current. See the SCD concept page.
One customer, three cities, then a delete. Facts join to the row valid on their own date.
The hard cases every implementation must handle: two changes for one key in the same batch, an older event arriving after a newer one, a "change" that changes nothing we track, a delete, a delete then a re-insert, and a failed batch that runs again.
Step 1
Four input shapes, one idea
Whatever arrives, turn it into change events. After that, SCD2 is one problem, solved once.
| Input you receive | Typical source | How it becomes change events | Deletes? |
|---|---|---|---|
| CDC feed | Debezium on Kafka or MSK, SAP SLT, AWS DMS, ADF CDC, Fivetran | Already events. Order by commit time plus log position. | Yes |
| Delta table we own | Silver tables, other teams' Delta tables | Turn on delta.enableChangeDataFeed and read the change feed; order by _commit_version. | Yes |
| Full snapshot | SAP table extracts, daily files, JDBC full loads | Compare with the open rows by hash: new key = insert, different hash = update, missing key = delete. | Yes, by absence |
| Pull by updated_at | JDBC 'where updated_at > last run' | Each row is an update event, ordered by updated_at. | No: add a periodic full key-list compare |
Step 2
Which to use
| Input | Situation | Use | Verdict |
|---|---|---|---|
| CDC | Already on Lakeflow pipelines, or happy to adopt them | AUTO CDC | Pursue: ordering, deletes and duplicates handled; no SCD code to own |
| CDC | Jobs-only team, very high volume, cost-sensitive | Own PySpark MERGE | Pursue: lower DBU rate; only with the table-driven tests below |
| CDC | Custom rules: business-date effective dates, rewriting history in the middle of the timeline | Own PySpark MERGE | Pursue: AUTO CDC orders by one SEQUENCE BY value |
| Snapshot | Source is a Delta table we control | Change data feed, then AUTO CDC | Pursue: reads only changed rows |
| Snapshot | Full extract, small to medium table | AUTO CDC FROM SNAPSHOT | Pursue: simple and correct; Python only |
| Snapshot | Full extract, hundreds of millions of rows | Own hash compare | Pursue: compare only the partitions that changed |
| Any | A hand-written SCD2 notebook per source table | None | Drop: the same bugs fixed many times over |
Clean implementation 1
AUTO CDC in a Lakeflow pipeline
AUTO CDC is the new name for APPLY CHANGES in Lakeflow Declarative Pipelines (formerly Delta Live Tables). The target is a streaming table the pipeline owns; it adds __START_AT and __END_AT from the SEQUENCE BY value.
Input is CDC, in SQL
CREATE OR REFRESH STREAMING TABLE dim_customer
TBLPROPERTIES ('pipelines.reset.allowed' = 'false'); -- protect history from a full refresh
CREATE FLOW dim_customer_scd2 AS AUTO CDC INTO dim_customer
FROM STREAM(bronze.customer_cdc)
KEYS (customer_id)
APPLY AS DELETE WHEN op = 'D'
SEQUENCE BY commit_ts -- or struct(commit_ts, lsn) to break ties
COLUMNS * EXCEPT (op, commit_ts, _rescued_data)
STORED AS SCD TYPE 2
TRACK HISTORY ON * EXCEPT (last_login_ts); -- noisy columns update in placeLakeflow pipeline SQL; needs a Databricks workspace, not run on this page.
Input is CDC, in Python
from pyspark import pipelines as dp
from pyspark.sql.functions import col, expr
@dp.view
def customer_cdc():
return spark.readStream.table("bronze.customer_cdc")
dp.create_streaming_table("dim_customer", table_properties={"pipelines.reset.allowed": "false"})
dp.create_auto_cdc_flow(
target="dim_customer",
source="customer_cdc",
keys=["customer_id"],
sequence_by=col("commit_ts"),
apply_as_deletes=expr("op = 'D'"),
except_column_list=["op", "commit_ts", "_rescued_data"],
stored_as_scd_type=2,
track_history_except_column_list=["last_login_ts"],
)Same flow in the Python API.
Input is a Delta table we own: turn on the change feed
ALTER TABLE silver.material SET TBLPROPERTIES (delta.enableChangeDataFeed = true); -- In the pipeline: drop pre-images, then feed AUTO CDC as above with SEQUENCE BY _commit_version CREATE TEMPORARY VIEW material_changes AS SELECT *, CASE _change_type WHEN 'delete' THEN 'D' ELSE 'U' END AS op FROM STREAM(silver.material) WITH (readChangeFeed = true) WHERE _change_type != 'update_preimage';
Input is a full snapshot (Python only)
from pyspark import pipelines as dp
dp.create_streaming_table("dim_material")
# Each pipeline update reads the latest full snapshot and compares it with the target.
dp.create_auto_cdc_from_snapshot_flow(
target="dim_material",
source="bronze.sap_mara_snapshot",
keys=["material_id"],
stored_as_scd_type=2,
track_history_except_column_list=["extract_ts"],
)
# For a backfill from many dated snapshots, pass a function instead of a table name:
# it gets the last processed version and returns (next_df, next_version) or None.The snapshot flow reads the whole snapshot each update: without a change feed, comparing full key lists is the only way to see deletes.
Clean implementation 2
Our own PySpark: one writer, an adapter per input
The writer only understands change events. Planning is a pure function from DataFrames to a DataFrame, so it is tested without Delta; the MERGE is a thin wrapper. Each input shape gets a small adapter.
Three adapters, one planning function, one MERGE.
The writer
"""One SCD Type 2 writer. Input is always change events: key, seq, op ('I','U','D'), tracked columns.
Target columns: key, tracked..., row_hash, valid_from, valid_to, is_current."""
from delta.tables import DeltaTable
from pyspark.sql import DataFrame, Window, functions as F
def row_hash(cols: list[str]):
return F.sha2(F.concat_ws("|", *[F.coalesce(F.col(c).cast("string"), F.lit("<null>")) for c in cols]), 256)
def plan_scd2(events: DataFrame, target: DataFrame, key: str, seq: str, tracked: list[str]) -> DataFrame:
"""Pure planning step. target = all rows of the SCD2 table (open and closed).
Returns staged rows for one MERGE:
merge_key set = close that key's open row at close_at
merge_key null = insert this row as a new version"""
by_key = Window.partitionBy(key).orderBy(seq)
cur = target.where("is_current").select(key, F.col("row_hash").alias("cur_hash"))
# Latest point already applied per key, open or closed (a deleted key has no open row).
seen = target.groupBy(key).agg(
F.max(F.greatest("valid_from", F.coalesce("valid_to", "valid_from"))).alias("last_seq"))
ev = (
events.dropDuplicates([key, seq])
.withColumn("row_hash", row_hash(tracked))
.join(cur, key, "left")
.join(seen, key, "left")
# 1. Late or replayed events (not newer than what is applied) are skipped.
# This makes re-running a failed batch safe, even for deleted keys.
.where(F.col("last_seq").isNull() | (F.col(seq) > F.col("last_seq")))
.withColumn("prev_hash", F.coalesce(F.lag("row_hash").over(by_key), F.col("cur_hash")))
.withColumn("prev_op", F.lag("op").over(by_key))
# 2. Drop events that change nothing we track.
.where((F.col("op") == "D") | (F.col("prev_op") == "D")
| ~F.col("row_hash").eqNullSafe(F.col("prev_hash")))
# 3. Each version ends where the next event for the same key starts.
.withColumn("valid_to", F.lead(seq).over(by_key))
)
closes = (
ev.groupBy(key).agg(F.min(seq).alias("close_at"))
.join(cur.select(key), key) # only keys that have an open row
.withColumn("merge_key", F.col(key))
)
inserts = (
ev.where(F.col("op") != "D")
.select(key, *tracked, "row_hash", F.col(seq).alias("valid_from"), "valid_to",
F.col("valid_to").isNull().alias("is_current"))
.withColumn("merge_key", F.lit(None).cast(events.schema[key].dataType))
)
return closes.unionByName(inserts, allowMissingColumns=True)
def apply_scd2(target: DeltaTable, staged: DataFrame, key: str, tracked: list[str]) -> None:
"""One atomic MERGE: close old versions and insert new ones together."""
cols = [key, *tracked, "row_hash", "valid_from", "valid_to", "is_current"]
(
target.alias("t")
.merge(staged.alias("s"), f"t.{key} = s.merge_key AND t.is_current")
.whenMatchedUpdate(set={"valid_to": "s.close_at", "is_current": "false"})
.whenNotMatchedInsert(condition="s.merge_key IS NULL", values={c: f"s.{c}" for c in cols})
.execute()
)Run on Spark 3.5.1 + Delta 3.2.0 on 2026-11-11.
The adapters
from delta.tables import DeltaTable
from pyspark.sql import DataFrame, functions as F
from scd2 import apply_scd2, plan_scd2, row_hash
def run_cdc(spark, source: str, target_name: str, key: str, seq: str, tracked: list[str], checkpoint: str):
target = DeltaTable.forName(spark, target_name)
def each_batch(batch: DataFrame, _batch_id: int):
apply_scd2(target, plan_scd2(batch, target.toDF(), key, seq, tracked), key, tracked)
(spark.readStream.table(source)
.writeStream.foreachBatch(each_batch)
.option("checkpointLocation", checkpoint)
.trigger(availableNow=True)
.start().awaitTermination())
def change_feed_events(spark, table: str, starting_version: int) -> DataFrame:
return (spark.readStream.option("readChangeFeed", "true")
.option("startingVersion", starting_version).table(table)
.where("_change_type != 'update_preimage'")
.withColumn("op", F.expr("CASE _change_type WHEN 'delete' THEN 'D' ELSE 'U' END"))
.withColumnRenamed("_commit_version", "seq"))
def snapshot_to_events(snapshot: DataFrame, current: DataFrame, key: str, tracked: list[str], snap_ts) -> DataFrame:
s = snapshot.select(key, *tracked).withColumn("new_hash", row_hash(tracked))
c = current.select(key, F.col("row_hash").alias("cur_hash"))
op = (F.when(F.col("cur_hash").isNull(), "I")
.when(F.col("new_hash").isNull(), "D")
.when(F.col("new_hash") != F.col("cur_hash"), "U"))
return (s.join(c, key, "full_outer")
.withColumn("op", op).where(F.col("op").isNotNull())
.withColumn("seq", F.lit(snap_ts))
.drop("new_hash", "cur_hash"))
def run_snapshot(spark, snapshot_table: str, target_name: str, key: str, tracked: list[str], snap_ts):
target = DeltaTable.forName(spark, target_name)
rows = target.toDF()
events = snapshot_to_events(spark.read.table(snapshot_table), rows.where("is_current"), key, tracked, snap_ts)
apply_scd2(target, plan_scd2(events, rows, key, "seq", tracked), key, tracked)
# Running the same snapshot twice is a no-op: seq is not newer than what is applied.
Target table
CREATE TABLE gold.dim_customer (
customer_id BIGINT, name STRING, email STRING, city STRING, segment STRING,
row_hash STRING, valid_from TIMESTAMP, valid_to TIMESTAMP, is_current BOOLEAN
)
CLUSTER BY (customer_id, is_current) -- MERGE reads only matching files
TBLPROPERTIES ('delta.enableDeletionVectors' = 'true'); -- cheaper closesDelta DDL for Databricks; liquid clustering keeps each MERGE small.
What the end-to-end run printed
after CDC batch 2 (42 moves then is deleted; 7 gets a late event; 9 gets a no-op) customer_id city segment valid_from valid_to is_current 7 Chennai sme 2026-02-01 NULL true <- late Madurai event skipped 9 Goa retail 2026-03-01 NULL true <- no-op ignored 42 Pune retail 2026-01-04 2026-05-19 false 42 Mumbai retail 2026-05-19 2026-09-02 false 42 Delhi retail 2026-09-02 2026-11-01 false <- closed by the delete replay the whole CDC source -> rows before 5, after 5 after 3 daily snapshots, then day 3 again 1 Pune retail 2026-11-01 2026-11-03 false <- missing on day 3 = deleted 2 Goa sme 2026-11-01 2026-11-02 false 2 Goa corp 2026-11-02 NULL true 3 Delhi retail 2026-11-02 NULL true re-run day 3 -> rows before 4, after 4 change data feed: insert (v1), update (v2), delete (v3) 5 Pune retail 1 2 false 5 Delhi retail 2 3 false
From e2e.py: a CDC stream in two batches, a full replay, three daily snapshots plus a re-run, and a Delta change data feed. Columns trimmed for width; arrows are our notes.
Proof
Table-driven tests
Because planning is pure, each hard case is one row. All 11 pass.
import pytest
from scd2 import plan_scd2, row_hash
O, X = True, False # open row, closed row
# name target rows (id, city, from, to, open) events (id, op, seq, city) closes (id, at) inserts (id, city, from, to)
CASES = [
("new key opens a row", [], [(1, "I", 1, "Pune")], [], [(1, "Pune", 1, None)]),
("no-op update is ignored", [(1, "Pune", 1, None, O)], [(1, "U", 2, "Pune")], [], []),
("change closes and opens", [(1, "Pune", 1, None, O)], [(1, "U", 2, "Mumbai")], [(1, 2)], [(1, "Mumbai", 2, None)]),
("two changes in one batch", [(1, "Pune", 1, None, O)], [(1, "U", 2, "Mumbai"), (1, "U", 3, "Delhi")], [(1, 2)], [(1, "Mumbai", 2, 3), (1, "Delhi", 3, None)]),
("no-op then change", [(1, "Pune", 1, None, O)], [(1, "U", 2, "Pune"), (1, "U", 3, "Delhi")], [(1, 3)], [(1, "Delhi", 3, None)]),
("delete closes, adds nothing", [(1, "Pune", 1, None, O)], [(1, "D", 2, None)], [(1, 2)], []),
("delete then re-insert", [(1, "Pune", 1, None, O)], [(1, "D", 2, None), (1, "I", 3, "Pune")], [(1, 2)], [(1, "Pune", 3, None)]),
("late event is skipped", [(1, "Delhi", 5, None, O)], [(1, "U", 3, "Mumbai")], [], []),
("replayed batch is a no-op", [(1, "Delhi", 3, None, O)], [(1, "U", 2, "Mumbai"), (1, "U", 3, "Delhi")], [], []),
("replay after delete stays deleted", [(1, "Pune", 1, 2, X)], [(1, "I", 1, "Pune"), (1, "D", 2, None)], [], []),
("re-insert after delete", [(1, "Pune", 1, 2, X)], [(1, "I", 4, "Goa")], [], [(1, "Goa", 4, None)]),
]
def by_key_and_start(row):
return (row[0], row[2] if len(row) > 2 else row[1])
@pytest.mark.parametrize("name, target, events, closes, inserts", CASES, ids=[c[0] for c in CASES])
def test_plan_scd2(spark, name, target, events, closes, inserts):
tgt = (spark.createDataFrame(target, "id INT, city STRING, valid_from INT, valid_to INT, is_current BOOLEAN")
.withColumn("row_hash", row_hash(["city"])))
ev = spark.createDataFrame(events, "id INT, op STRING, seq INT, city STRING")
staged = plan_scd2(ev, tgt, key="id", seq="seq", tracked=["city"]).collect()
got_closes = [(r.id, r.close_at) for r in staged if r.merge_key is not None]
got_inserts = [(r.id, r.city, r.valid_from, r.valid_to) for r in staged if r.merge_key is None]
assert sorted(got_closes, key=by_key_and_start) == sorted(closes, key=by_key_and_start)
assert sorted(got_inserts, key=by_key_and_start) == sorted(inserts, key=by_key_and_start)pytest, 11 passed on 2026-11-11. The Spark fixture is in conftest.py next to it.
For the snapshot adapter, add a second table: unchanged row gives no event, changed row gives U, new key gives I, missing key gives D. See Shift left for testing SCD2 without a cluster.
Honest comparison
What AUTO CDC gives you that own code must earn
| Concern | AUTO CDC | Own PySpark (above) |
|---|---|---|
| Events out of order | Reorders by SEQUENCE BY, including events older than the current row (history rewritten correctly) | Skips events not newer than what is applied; rewriting history mid-timeline needs extra code |
| Several changes per key in one batch | Handled | Handled with lag and lead (test 'two changes in one batch') |
| Deletes, delete then re-insert | APPLY AS DELETE WHEN, plus tombstones for late deletes | Handled (tests 6, 7, 10, 11); no tombstones |
| Changes to untracked columns | TRACK HISTORY ON ... EXCEPT updates them in place | Ignored; extend apply_scd2 with an in-place update if needed |
| Re-running a failed batch | Pipeline checkpoint | Safe through the sequence check, also for deleted keys (tests 9, 10) |
| Schema changes | Supported in pipelines | We add columns ourselves |
| Who can change the target | The pipeline owns it; outside DML is limited | A plain Delta table |
| Full refresh | History lost if bronze no longer holds it; set pipelines.reset.allowed = false | No such switch; a rebuild is our own job |
| Custom effective dates | One ordering column | Anything we can code |
Cost
What it costs
Three things make up the bill: the DBU rate of the compute type, the amount of work each run does (set by the input shape) and the engineering time to own the code. Pipelines cost more per DBU than plain jobs on classic compute; serverless is one rate for both and removes start-up and idle time.
| Compute | AWS $/DBU | Azure $/DBU (estimate) | VM cost | Runs |
|---|---|---|---|---|
| Jobs compute (classic) | 0.15 | 0.30 | extra, paid to the cloud | Own PySpark |
| Lakeflow pipeline Core (classic) | 0.20 | 0.30 | extra | No AUTO CDC |
| Lakeflow pipeline Pro (classic) | 0.25 | 0.38 | extra | AUTO CDC |
| Lakeflow pipeline Advanced (classic) | 0.36 | 0.54 | extra | AUTO CDC plus expectations |
| Serverless jobs and pipelines | 0.35 | 0.45 | included | Either |
AWS rates are list prices from the site's price file (checked 2026-11-09); Azure rates are our estimates. Contract discounts change both: use your own price sheet.
Where the work comes from
| Input shape | Reads per run | Cost lever |
|---|---|---|
| CDC feed or change data feed | Only new events, plus the matching target rows | Same for both tools. Cluster the target on the key. |
| Full snapshot | The whole snapshot plus the target, every run | The biggest cost driver. Turn it into a change feed where you can; with own code, compare only changed partitions. |
| Pull by updated_at | Changed rows, plus a weekly key-list compare for deletes | Cheap, if the source really sets the timestamp. |
Calculator
The example is 20 dimensions loaded hourly. Change any number.
Serverless rows assume the same DBU per run as classic; real serverless usage differs, so compare a week of system.billing.usage before deciding. Full model: the Databricks cost calculator.
Before going live
Checklist
| Check | Why |
|---|---|
| Ordering value | Unique per key: commit time alone can tie, so add the log position (LSN) or commit version. |
| Protect AUTO CDC history | pipelines.reset.allowed = false on the target; keep bronze as long as you might rebuild. |
| Noisy columns | Exclude last-login or extract timestamps from tracking, or every run makes a new version. |
| Layout | Cluster the target on the key and is_current so each MERGE touches few files; turn on deletion vectors. |
| Partial snapshots | A partial extract must never read as 'everything else was deleted': check row counts before applying. |
| One valid_to convention | NULL or 9999-12-31, the same everywhere, so Power BI and others filter the same way. |
| Tests in CI | The table-driven tests run on every change to the writer. |
FAQ
Questions people ask
Should I use AUTO CDC or write my own SCD Type 2 MERGE?
If the input is CDC and you can run Lakeflow pipelines, use AUTO CDC: it handles out-of-order events, deletes and duplicates. Write your own when you run only plain jobs at high volume, or need rules AUTO CDC cannot express. Then build one tested writer with an adapter per input type.
How do I build SCD2 from a full snapshot instead of CDC?
If the source is a Delta table you own, turn on its change data feed and treat it as CDC. Otherwise compare the snapshot with the open target rows by a hash of the tracked columns: new keys are inserts, changed hashes are updates, missing keys are deletes. AUTO CDC FROM SNAPSHOT does this for you in Python pipelines.
Is AUTO CDC the same as APPLY CHANGES?
Yes. APPLY CHANGES INTO was renamed AUTO CDC INTO when Delta Live Tables became Lakeflow Declarative Pipelines; the Python function is now create_auto_cdc_flow, and apply_changes_from_snapshot is create_auto_cdc_from_snapshot_flow.
Does AUTO CDC cost more?
On classic compute it needs the Pro pipeline tier, about a third more per DBU than plain jobs compute on AWS list prices. The VM cost is the same, so the total gap is smaller, and often less than the engineering time to own the code. Serverless jobs and serverless pipelines are billed at the same rate.
Can a full refresh delete my SCD2 history?
Yes, for an AUTO CDC target: a full refresh rebuilds from the source, and if bronze no longer holds all events, the history is gone. Set pipelines.reset.allowed = false on the target table.
How do I make a hand-written SCD2 MERGE safe to re-run?
Skip any event whose ordering value is not newer than the latest point already applied for that key, counting closed rows too, so a deleted key is not brought back. The tests on this page cover replay, late events and replay after delete.
Sources
Checked against
Related
Where it connects
Slowly changing dimensions · Bronze to silver, with SCD2 code · Time travel as SCD Type 2? · Delta vs Iceberg (change data feed) · Streaming on AWS and Databricks · All data engineering