Slowly Changing Dimension (SCD)
A Slowly Changing Dimension (SCD) is a dimension table in a data warehouse whose attributes change slowly over time, requiring strategies to track both current and historical values.
Definition
In dimensional modeling, dimensions are tables that describe 'what' (customers, products, time), while facts measure 'what happened' (sales, transactions). A slowly changing dimension is one where attributes change infrequently but do change: a customer's address, a product's price, an employee's title. The question is how to handle these changes in a warehouse: do you overwrite the old value (losing history) or keep both old and new versions? Different strategies (SCD Type 1, 2, 3) balance historical accuracy against complexity. SCD Type 1 (overwrite) is simplest but loses history. SCD Type 2 (add new row, mark old as inactive) preserves full history but doubles the table size. Type 3 (keep both current and previous) is a middle ground.
How It Works
Type 1 (Overwrite): UPDATE customer SET address = '123 New St' WHERE id = 1. Simple, no history. Type 2 (History): INSERT new row with new address, set old row end_date = today. Requires surrogate keys and date tracking. Type 3 (Current + Previous): Keep two address columns: current_address, previous_address, plus last_change_date. Balances history and simplicity.
When to Use It
Use SCD in star schema designs (most data warehouses). Choose based on analytic needs: Type 1 if you only care about current values; Type 2 if you need historical accuracy (most reports); Type 3 if you want recent history without full versioning. Most fact tables join slowly-changing dimensions using surrogate keys.
Last updated: Jun 17, 2026