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 retrospectively reports — without SCDs, history gets rewritten and analysis becomes wrong.
Need help implementing this in your business?
Get Started