Delta Live Tables (DLT) – Slowly Changing Dimensions SCD Type 2

Delta Live Tables (DLT) is a declarative framework in Databricks for building reliable, automated ETL pipelines. It can automatically manage SCD Type 2 logic, tracking complete historical changes for dimension tables.

1. What SCD Type 2 Means

SCD Type 2 keeps full history of record changes.
When a tracked attribute changes, a new row is inserted.
Old rows are marked as no longer current.
Useful for customer history, product changes, employee roles, etc.

2. How DLT Implements SCD Type 2

DLT simplifies SCD2 by using the dlt.apply_changes function, which automatically:

Detects changes by business key
Expires the previous “current” row
Inserts a new version with updated values
Maintains current/historical indicators and effective dates

3. Pipeline Structure

A source table or stream (CDC feed or Silver table)
A target dimension table in DLT
A configuration using dlt.apply_changes with SCD Type 2 options

Example Source Table (Customer Changes)


@dlt.table
def customer_changes():
    return spark.readStream.table("silver.customer_changes")

customer_id – business key
name, address – tracked attributes
change_ts – timestamp used to order events

Example Target Table (Dimension Seed)


@dlt.table
def dim_customer():
    return spark.read.table("dim.dim_customer_seed")

DLT SCD Type 2 Logic


dlt.apply_changes(
    target = "dim_customer",
    source = "customer_changes",
    keys = ["customer_id"],
    sequence_by = col("change_ts"),
    stored_as_scd_type = "2",
    track_history_columns = ["name", "address"]
)

keys defines the business key
sequence_by determines the event order
stored_as_scd_type = "2" activates SCD Type 2 mode
track_history_columns defines which changes create new versions

4. Why Use DLT for SCD Type 2

Simplifies complex MERGE logic
Automates orchestration (Bronze → Silver → Gold)
Supports streaming and batch sources
Provides built-in lineage, monitoring, and data quality
Handles automatic maintenance of Delta tables

5. Summary

DLT reads change data from a source table or stream
A dimension table is defined as a DLT-managed table
dlt.apply_changes uses SCD2 logic to insert new versions and expire old ones
The result is a fully historical, audit-friendly dimension table

6. How the history rows evolve

With stored_as_scd_type = "2", the target table gets two extra columns, __START_AT and __END_AT, populated from the sequence_by column. When a change arrives for an existing key and one of the tracked columns differs, the current row is closed by setting its __END_AT to the new event's sequence value, and a new row is inserted with __END_AT null. The current version of each key is therefore the row where __END_AT IS NULL, and the version valid at any instant T is the row where __START_AT <= T and __END_AT is null or greater than T.

Because events are ordered by sequence_by rather than by arrival time, late or out-of-order events are placed into the correct position in history. That is the main thing hand-written MERGE logic for SCD2 usually gets wrong.

7. Pipeline flow

In a typical medallion pipeline the CDC feed (from Debezium, a database's change data feed, or a vendor such as Fivetran) lands in a bronze table, is cleaned into a silver streaming table with expectations that drop or quarantine malformed records, and is then applied to a gold dimension. The target of apply_changes must be declared with dlt.create_streaming_table; it is maintained entirely by the apply flow. Databricks has since renamed DLT to Lakeflow Declarative Pipelines and introduced create_auto_cdc_flow as the newer name for the same API; existing apply_changes code continues to work.

8. Worked example: complete pipeline

import dlt
from pyspark.sql.functions import col, expr

@dlt.view
@dlt.expect_or_drop("valid_key", "customer_id IS NOT NULL")
def customer_changes_clean():
    return spark.readStream.table("silver.customer_changes")

dlt.create_streaming_table("dim_customer")

dlt.apply_changes(
    target="dim_customer",
    source="customer_changes_clean",
    keys=["customer_id"],
    sequence_by=col("change_ts"),
    apply_as_deletes=expr("op = 'DELETE'"),
    except_column_list=["op"],
    stored_as_scd_type=2,
    track_history_column_list=["name", "address"],
)

@dlt.table(comment="Current row per customer")
def dim_customer_current():
    return dlt.read("dim_customer").where("__END_AT IS NULL")

track_history_column_list (or track_history_except_column_list) restricts which columns create new versions; changes to other columns update the current row in place. apply_as_deletes closes the current row when a delete event arrives.

9. SCD Type 1 vs Type 2 vs snapshots

Approach	History	Rows per key	Use when
SCD Type 1	None, overwrite	1	Corrections, attributes nobody reports on historically
SCD Type 2	Full, row per version	Many	Point-in-time reporting: revenue by the customer's region at the time of sale
Delta time travel	Table-level versions, limited by retention	1	Auditing and recovery, not modelled history
Periodic snapshots	Full copy per period	1 per period	Simple sources with no change feed

10. Pitfalls

Ties in sequence_by. Two events for the same key with identical sequence values have no defined order; use a struct of timestamp plus a log sequence number to break ties.
Writing to the target directly. The SCD2 target is owned by the flow; manual DML or a full refresh rebuilds history from whatever source data is still available.
Joining facts to the dimension. Fact rows must join on key and on the time range, not on key alone, or every fact multiplies by the number of versions.
Untracked noise. Tracking every column, including volatile ones such as last_login, creates a new version for each change and bloats the table.