Part 181: Slowly Changing Dimensions (SCD Types 1, 2, 3, 4 & 6) in SQL

Tracking historical attribute mutations in dimensional data warehouses.

1. The 6 SCD Archetypes

When a customer moves from Chicago to New York:

2. Implementing SCD Type 2 with SQL

-- Schema with surrogate key and temporal validity flags
CREATE TABLE dim_customer (
    customer_sk BIGSERIAL PRIMARY KEY, -- Surrogate Key
    customer_id INT NOT NULL,          -- Business Key
    full_name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP NULL,
    is_current BOOLEAN NOT NULL DEFAULT TRUE
);

-- Inactivating previous row when an address changes:
UPDATE dim_customer
SET valid_to = CURRENT_TIMESTAMP, is_current = FALSE
WHERE customer_id = 42 AND is_current = TRUE;

-- Inserting new active record:
INSERT INTO dim_customer (customer_id, full_name, city, valid_from, valid_to, is_current)
VALUES (42, 'Alice Smith', 'New York', CURRENT_TIMESTAMP, NULL, TRUE);

Key Takeaways

• Fact tables must always link to the customer_sk (surrogate key) to preserve historical metrics as they existed at transaction time.

In Part 182, we explore Data Vault 2.0 Modeling: Hubs, Links & Satellites!