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 CDC: use AUTO CDC. It handles events that arrive out of order, deletes and duplicate events, which is where hand-written SCD2 code usually goes wrong.
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.

customer 42 over one year42 · Punefrom 4 Jan · to 19 May42 · Mumbaifrom 19 May · to 2 Sep42 · Delhifrom 2 Sep · to 1 Nov1 Nov: deleted in sourcenothing is overwritten: each change closes one row and opens the next

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

CDC feedDebezium, SAP SLT, DMSDelta table we ownchange data feedFull snapshotdaily extract, filesupdated_at pullJDBC incrementalchange eventskey · op · order · valuesAUTO CDC(Lakeflow pipeline)our SCD2 writer(PySpark MERGE)turn every input into change events first; then SCD2 is the same problem every time

Whatever arrives, turn it into change events. After that, SCD2 is one problem, solved once.

Input you receiveTypical sourceHow it becomes change eventsDeletes?
CDC feedDebezium on Kafka or MSK, SAP SLT, AWS DMS, ADF CDC, FivetranAlready events. Order by commit time plus log position.Yes
Delta table we ownSilver tables, other teams' Delta tablesTurn on delta.enableChangeDataFeed and read the change feed; order by _commit_version.Yes
Full snapshotSAP table extracts, daily files, JDBC full loadsCompare with the open rows by hash: new key = insert, different hash = update, missing key = delete.Yes, by absence
Pull by updated_atJDBC '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

InputSituationUseVerdict
CDCAlready on Lakeflow pipelines, or happy to adopt themAUTO CDCPursue: ordering, deletes and duplicates handled; no SCD code to own
CDCJobs-only team, very high volume, cost-sensitiveOwn PySpark MERGEPursue: lower DBU rate; only with the table-driven tests below
CDCCustom rules: business-date effective dates, rewriting history in the middle of the timelineOwn PySpark MERGEPursue: AUTO CDC orders by one SEQUENCE BY value
SnapshotSource is a Delta table we controlChange data feed, then AUTO CDCPursue: reads only changed rows
SnapshotFull extract, small to medium tableAUTO CDC FROM SNAPSHOTPursue: simple and correct; Python only
SnapshotFull extract, hundreds of millions of rowsOwn hash comparePursue: compare only the partitions that changed
AnyA hand-written SCD2 notebook per source tableNoneDrop: 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 place

Lakeflow 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.

run_cdc()stream, foreachBatchchange_feed_events()Delta CDF to eventssnapshot_to_events()hash compare to eventsplan_scd2()pure: skip late, drop no-ops,build versionsapply_scd2()one MERGEdim tableDeltatested with 11 table rows, no Delta needed

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 closes

Delta 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.

A bug this run caught: the first version checked late events only against open rows. A deleted customer has none, so replaying an old batch brought them back (5 rows became 8). The fix compares each event with the latest point applied for that key, closed rows included. Two test rows now guard it.

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

ConcernAUTO CDCOwn PySpark (above)
Events out of orderReorders 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 batchHandledHandled with lag and lead (test 'two changes in one batch')
Deletes, delete then re-insertAPPLY AS DELETE WHEN, plus tombstones for late deletesHandled (tests 6, 7, 10, 11); no tombstones
Changes to untracked columnsTRACK HISTORY ON ... EXCEPT updates them in placeIgnored; extend apply_scd2 with an in-place update if needed
Re-running a failed batchPipeline checkpointSafe through the sequence check, also for deleted keys (tests 9, 10)
Schema changesSupported in pipelinesWe add columns ourselves
Who can change the targetThe pipeline owns it; outside DML is limitedA plain Delta table
Full refreshHistory lost if bronze no longer holds it; set pipelines.reset.allowed = falseNo such switch; a rebuild is our own job
Custom effective datesOne ordering columnAnything 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.

ComputeAWS $/DBUAzure $/DBU (estimate)VM costRuns
Jobs compute (classic)0.150.30extra, paid to the cloudOwn PySpark
Lakeflow pipeline Core (classic)0.200.30extraNo AUTO CDC
Lakeflow pipeline Pro (classic)0.250.38extraAUTO CDC
Lakeflow pipeline Advanced (classic)0.360.54extraAUTO CDC plus expectations
Serverless jobs and pipelines0.350.45includedEither

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 shapeReads per runCost lever
CDC feed or change data feedOnly new events, plus the matching target rowsSame for both tools. Cluster the target on the key.
Full snapshotThe whole snapshot plus the target, every runThe biggest cost driver. Turn it into a change feed where you can; with own code, compare only changed partitions.
Pull by updated_atChanged rows, plus a weekly key-list compare for deletesCheap, if the source really sets the timestamp.

Calculator

The example is 20 dimensions loaded hourly. Change any number.

Workload Rates, $ (filled by cloud, editable) Engineering, first year

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

CheckWhy
Ordering valueUnique per key: commit time alone can tie, so add the log position (LSN) or commit version.
Protect AUTO CDC historypipelines.reset.allowed = false on the target; keep bronze as long as you might rebuild.
Noisy columnsExclude last-login or extract timestamps from tracking, or every run makes a new version.
LayoutCluster the target on the key and is_current so each MERGE touches few files; turn on deletion vectors.
Partial snapshotsA partial extract must never read as 'everything else was deleted': check row counts before applying.
One valid_to conventionNULL or 9999-12-31, the same everywhere, so Power BI and others filter the same way.
Tests in CIThe 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