Dimension attributes — customer address, product category, sales region — change over time. Slowly changing dimension (SCD) techniques decide how warehouses handle this.
Type 1: Overwrite
Replace the old value. Simple, but history is lost: past sales appear under the current region.
Type 2: Add a Row
Insert a new row for each change, with valid-from and valid-to dates and a current flag. Facts link to the version valid at the time. Preserves full history; most common for important attributes.
Type 3: Add a Column
Keep current and previous values in separate columns. Limited history.
Choosing
- Corrections of errors → Type 1.
- Changes that matter for historical analysis → Type 2.
- Occasional before-and-after comparison → Type 3.
Implementation
Tools such as dbt snapshots automate Type 2 tracking.
Pitfalls
- Joining facts to the current dimension row when history matters.
- Type 2 tables growing large when attributes change often.
- Missing surrogate keys.