Databricks DE · Topic 5.4

AUTO CDC — SCD Type 1 & 2

Apply a change feed to build slowly changing dimensions declaratively. SCD1 overwrites; SCD2 keeps versioned history with start/end timestamps. (Replaces APPLY CHANGES INTO.) This deep dive walks each with the code.

AUTO CDCSCD 1 / 2SEQUENCE BYAPPLY AS DELETEparametrized

The one-liner

AUTO CDC applies inserts/updates/deletes to a target by KEYS + SEQUENCE BY, STORED AS SCD TYPE 1 (overwrite) or TYPE 2 (history with __START_AT/__END_AT). It replaces APPLY CHANGES INTO.
🏠 Analogy: tracking a customer's address. SCD1 = a phone contact — editing the address erases the old one. SCD2 = an address-history logbook — each move adds a dated entry.

Apply a change — SCD1 vs SCD2

▸ pick a type, then apply an address change for customer 7

SCD Type 1 (overwrite)
SCD Type 2 (history)
change feed: {id:7, city:"Austin", ts:2026-06-20} already applied. Now apply {id:7, city:"Denver", ts:2026-06-24}:

1 · The mechanism & SCD1 vs SCD2

KEYS = the unique business key; SEQUENCE BY = a sortable column whose highest value wins regardless of arrival order — that's what makes AUTO CDC robust to late/out-of-order events vs a naive MERGE.

SCD Type 1SCD Type 2
Historynone (overwrite)full (versioned rows)
Extra columns—__START_AT, __END_AT
Use for"current value only" dimsaudited / point-in-time joins
Table growthflatgrows (row per change per key)

2 · SQL — AUTO CDC INTO

CREATE OR REFRESH STREAMING TABLE dim_customers;

CREATE FLOW cdc_flow AS AUTO CDC INTO dim_customers
FROM stream(customers_changes)
KEYS (customer_id)
APPLY AS DELETE WHEN operation = 'DELETE'      -- handle deletes explicitly
SEQUENCE BY change_ts                            -- ordering; late events resequenced
COLUMNS * EXCEPT (operation)
STORED AS SCD TYPE 2                             -- or SCD TYPE 1 (overwrite)
TRACK HISTORY ON * EXCEPT (last_seen_ts);        -- skip versioning noisy cols

3 · Python — create_auto_cdc_flow

from pyspark import pipelines as dp
from pyspark.sql.functions import col, expr

dp.create_streaming_table("dim_customers")

dp.create_auto_cdc_flow(                          # replaces legacy dlt.apply_changes
    target="dim_customers",
    source="customers_changes",
    keys=["customer_id"],
    sequence_by=col("change_ts"),
    stored_as_scd_type=2,                          # 1 or 2
    apply_as_deletes=expr("operation = 'DELETE'"))

Sources that arrive as full snapshots (Python-only)

dp.create_auto_cdc_from_snapshot_flow(
    target="dim_customers", source="daily_snapshot",
    keys=["customer_id"], stored_as_scd_type=2)

4 · Parametrized pipelines

Read pipeline configuration values (set in pipeline settings or an Asset Bundle) at runtime so one pipeline serves many environments.

# pipeline setting:  {"source_path": "/Volumes/main/raw/customers"}
src = spark.conf.get("source_path")               # read the parameter

@dp.table
def bronze_customers():
    return spark.readStream.format("cloudFiles").option("cloudFiles.format","json").load(src)
-- in SQL, reference a pipeline config value with ${...}
CREATE OR REFRESH STREAMING TABLE bronze_customers
AS SELECT * FROM STREAM read_files('${source_path}', format => 'json');

5 · Pipeline modes & monitoring — click each

Triggered (batch)▸
Runs to completion, then stops. Cheaper — compute spins down between runs. Use for scheduled batch updates.
Continuous (streaming)▸
Keeps running for low-latency updates. Higher cost (compute stays up). Use when freshness matters.
Monitoring▸
The pipeline UI shows the DAG, per-dataset row counts, expectation (data-quality) metrics, and an event log for debugging failures.

Why it matters

🗺️ Real-world use case: a customers change feed builds dim_customers as SCD2 — every address change is versioned with start/end timestamps, so historical orders join to the address current at the time.

Uses, edge cases & limitations

Common gotchas