Slowly Changing Dimensions

Slowly Changing Dimensions (SCD)

# SCD Type 1: Overwrite (no history)
UPDATE dim_customer SET email = 'new@email.com' WHERE customer_id = 'C001';

# SCD Type 2: Track full history (most common)
-- New row for each change, old row closed
INSERT INTO dim_customer
    (customer_id,name,email,segment,valid_from,valid_to,is_current)
SELECT
    customer_id,name,email,segment,
    NOW() AS valid_from,
    '9999-12-31' AS valid_to,
    TRUE
FROM staging_customer_updates
WHERE changed = TRUE;

-- Close old record
UPDATE dim_customer d
JOIN staging_customer_updates s ON d.customer_id = s.customer_id
SET d.valid_to = NOW(), d.is_current = FALSE
WHERE d.is_current = TRUE AND s.changed = TRUE;

-- Query current state
SELECT * FROM dim_customer WHERE is_current = TRUE;

-- Query historical state at a point in time
SELECT * FROM dim_customer
WHERE valid_from <= '2024-06-01' AND valid_to > '2024-06-01';

# SCD Type 3: Add previous value column (limited history)
ALTER TABLE dim_customer ADD COLUMN prev_segment VARCHAR;

# SCD Type 6 = SCD 1 + 2 + 3 combined