92 ) Slowly Changing Dimensions (SCD) Types

 

Slowly Changing Dimensions (SCD) Types and Implementation

SCD Type	Definition & Strategy	SQL Implementation Approach
1. SCD Type 1	Overwrite: Old values are directly overwritten with new updates. No historical tracking is maintained.	UPDATE dim_customer SET address = 'New Address' WHERE customer_id = 101;
2. SCD Type 2	Add New Row: A new record row is inserted to capture the change, using effective start/end dates and an active flag.	UPDATE dim_customer SET end_date = CURRENT_DATE, is_current = 0 WHERE customer_id = 101 AND is_current = 1; INSERT INTO dim_customer (customer_id, address, start_date, end_date, is_current) VALUES (101, 'New Address', CURRENT_DATE, '9999-12-31', 1);
3. SCD Type 3	Add New Attribute: A new column is added to track the previous value alongside the current value, keeping a limited history window.	UPDATE dim_customer SET previous_address = address, address = 'New Address' WHERE customer_id = 101;
4. SCD Type 4	Mini-Dimension: Volatile attributes are split off from the main dimension into a separate secondary table to prevent row bloat.	Separate the profile into dim_customer_core and dim_customer_rapid_behavior, joining them via foreign keys.
5. SCD Type 5	Mini-Dimension with Type 1 Outrigger: A hybrid combining a Type 4 mini-dimension with a Type 1 current-state override overlay.	Combines historical tracking in a mini-dimension with a direct pointer column for fast current-state filtering.
6. SCD Type 6	Hybrid (Types 2 + 3 + 1): Combines historical row-version tracking (Type 2) with embedded current-attribute values (Type 1/3) in every row.	Updates all historical rows for a given key with the newest current attribute value while maintaining distinct start/end version intervals.