A dimensional modeling pattern for tracking how a dimension attribute (customer address, product price, employee manager) changes over time, preserving history for accurate point-in-time analytics.
SCDs solve the problem 'what was the customer's tier when this 2-year-old order was placed?' There are three main types: SCD Type 1 (overwrite, no history kept), SCD Type 2 (add a new row with valid_from/valid_to dates, preserves full history), SCD Type 3 (add a 'previous value' column, limited history). Type 2 is the most common in modern warehouses. dbt's snapshot feature implements SCD Type 2 declaratively.
Modeling customer addresses as SCD Type 2 so a shipping-cost analysis on 2024 orders uses the 2024 addresses, not the customer's current 2026 address.
SCD design is foundational to any analytics warehouse that reports retrospectively. Without SCDs, history gets rewritten and analysis becomes wrong.
Need help implementing this in your business?
Get Started