Slowly changing dimension. Type 1 keeps only current attribute values; type 2 retains every past version, bounded by __START_AT and __END_AT. Pipelines get both via AUTO CDC (STORED AS SCD TYPE 1 or STORED AS SCD TYPE 2), while TRACK HISTORY ON names which columns produce history.
Also called slowly changing dimension.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains SCD in context, with comparison tables and the common traps.
Terms in this definition
- VALUES
Returns in DAX the distinct column values, or table rows, still visible after filters are applied, sometimes with an extra blank entry. CALCULATE often takes the result as a table filter.
- Get
Key Vault permission on secrets that allows a single secret to be read; App Service Key Vault references need nothing beyond it.
- AUTO CDC
The Lakeflow pipelines API for change data capture: it merges incoming changes into a streaming table, keeping either just the latest values (SCD 1) or full history (SCD 2), and uses an ordering column to cope with records that arrive late. A Python-only snapshot variant also exists.
Related terms
- AUTO CDC FROM SNAPSHOT
A Lakeflow pipelines API, available only in Python, for sources without a change data feed: it compares one snapshot with the next and maintains an SCD type 1 or type 2 target from the differences.
- Temporal table
Lets you ask what the data looked like on a past date by keeping its change history. Azure Databricks options are SCD type 2, change data feed, or Delta history with time travel; retention and purpose vary.