Slowly Changing Dimensions

Transform and Analyze Data with Microsoft Fabric

Luis Silva

Solution Architect - Data & AI

How often does a dimension change?

Table describing the characteristics of a dimension that never changes

How often does a dimension change?

Table describing the characteristics of a dimension that never changes and a rapidly changing dimension

How often does a dimension change?

Table describing the characteristics of a dimension that never changes, a rapidly changing dimension, and a slowly changing dimension

Slowly Changing Dimensions

SCD Types based on different approaches to managing data change:
- Type 0 (No change)
- Type 1 (Overwrite)
- Type 2 (Keep all history)
- Type 3 (Keep limited history)
- Other Types (4, 5, 6, 7)

SCD Type 0: No change

No change accepted
Data changes are discarded
Data always reflects the original state
Used when data is not meant to change after the original load

Diagram showing a person's record in a dimension table. When a new record arrives for the same person with a different data of = 70 birth, the change is ignored and the original record is kept

SCD Type 1: Overwrite

Existing record is overwritten with new data
Data always reflects the latest values
Used for supplementary values that do not require a record of history, for example a phone number

Diagram showing a person's record in a dimension table. When a new record arrives for the same person with a different phone number, the record is updated with the new phone number

SCD Type 2: Keep all history

Keep existing record and create a new record with the new data
All history is maintained
Additional columns are used to track the date that the changes were applied

Diagram showing a salesperson's record in a dimension table. When a new record arrives for the same salesperson with a different sales region, the record is updated with the start date and end date that indicates the validity of the record, and a new record is created with the new values. The original record is also marked as not current, and the new record is marked as current

SCD Type 3: Keep some history

Keeps history of only one or a few attributes.
For each attribute, an additional column is added to keep a previous version of the attribute.
Used when limited history (current and last previous value) is required for an attribute without requiring extra rows.

Diagram showing a salesperson's record in a dimension table. When a new record arrives for the same salesperson with a different job title, the record is updated to reflect the new and previous job title, along with a date indicating when the change was made

Other SCD types

Other Types (4, 5, 6, 7) are variations of the core types 1, 2, and 3
These are not popular variations due to complexity
The most commonly used types of SCD are Type 1 and Type 2. Use them whenever possible:
- SCD Type 1 if history is not required.
- SCD Type 2 if history is required.

Let's practice!

Transform and Analyze Data with Microsoft Fabric