Slowly Changing Dimension (SCD)

Oracle: SCD is a dimension that stores and manages both current and historical data over time in a data warehouse

Microsoft: Slowly Changing Dimensions evolve over time, but at a slow pace and unpredictably

• Type 0 SCD: The dimension attributes never change.
• Type 1 SCD: Overwrites existing data, doesn't keep history.
• Type 2 SCD: Adds new records for changes, keeps full history for a given natural key.
• Type 3 SCD: History is added as a new column.
• Type 4 SCD: A new dimension is added.
• Type 5 SCD: When certain attributes of a large dimension change over time, but using type 2 isn't feasible due to the dimension’s large size.
• Type 6 SCD: Combination of type 2 and type 3.

type 1 and type 2 are the most frequently used

Practice

It's crucial to identify the appropriate version of the dimension record to ensure the correct surrogate key is used.

Reference

• StreamSet
• https://adatis.co.uk/introduction-to-slowly-changing-dimensions-scd-types/
• https://medium.com/geekculture/6-different-types-of-slowly-changing-dimensions-and-how-to-apply-them-b152ef908d4e