SCD types 0, 1, 2 and others compared, and when to keep history in a dimension table.
From Ultra Transcenders DP-750 by Tony Rough (coming November 2026)
A slowly changing dimension (SCD) defines how changes to a dimension’s attributes are applied after they land in an analytical table. The SCD type is a business decision: does the report need the current value only, the original value, or every value over time?
| Type | Behaviour | Choose it when |
|---|---|---|
| Type 0 | History not preserved; attributes keep their original values | The attribute must never change after first load (for example original sign-up channel) |
| Type 1 | Attributes overwritten with the latest values; no history | Only the current state matters; corrections; stable surrogate keys and incremental downstream refresh |
| Type 2 | Every version is a separate row with a validity period | Audit or regulatory requirements, point-in-time reporting, analysing how entities evolved |
| Type 3 | Limited history in extra columns of the same row (for example previous_region) |
Only the immediately previous value is needed |
| Type 4 | History kept in a separate table; the dimension holds current rows | Current lookups must stay small while full history is kept elsewhere |
In Azure Databricks, the AUTO CDC APIs (formerly APPLY CHANGES) of Lakeflow pipelines implement types 1 and 2 directly (STORED AS SCD TYPE 1 or STORED AS SCD TYPE 2, stored_as_scd_type in Python). With type 2, the target gets __START_AT and __END_AT columns built from the sequencing column, and the current version has __END_AT = NULL. By default any column change creates a new version; TRACK HISTORY ON (in Python, for example track_history_except_column_list) limits history to the columns that matter, and changes to untracked columns update the current row in place. A single dimension can mix behaviours: track history on region, overwrite phone number.
Common trap: Choosing SCD type 2 for every dimension “to be safe” - it multiplies rows and complicates joins (facts must match the version valid at the event time). Use type 1 for attributes where history has no business value, and limit type 2 history to the tracked columns that need it.
This note is one section of Ultra Transcenders DP-750: Implementing Data Engineering Solutions Using Azure Databricks, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.
Due on Amazon in November 2026, in Kindle and paperback editions.
About the book · DP-750 terms in the glossary · All DP-750 study notes
What standard (formerly shared) and dedicated (formerly single user) access modes allow, and when each is required.
Who manages the files, what DROP TABLE does to each, and why Databricks recommends managed tables.
How SQL UDF row filters and column masks restrict data per user, and how they differ from dynamic views.
How the two retention properties and VACUUM decide which table versions you can still query or restore.
The table-size thresholds for partitioning, partition sizing, and why liquid clustering is usually the better choice.
How expectations validate records in Lakeflow Spark Declarative Pipelines and what each violation action does.
Job and task notifications, system destinations, duration warnings and how retries affect which alerts are sent.