Slowly Changing Dimensions — Managing Historical Changes in Dimensions

Slowly Changing Dimensions address the question: what to do when dimension attributes change? A customer changes their address, a product changes its category — how to preserve history and report consistency.

SCD Types¶

Type 1 — Overwrite¶

The old value is overwritten with the new one. No history — simplest, but information is lost.

Type 2 — New Row¶

On change, a new row is added with validity dates (valid_from, valid_to). Complete history.

CREATE TABLE dim_customer_scd2 (
    customer_key    INT PRIMARY KEY,  -- surrogate
    customer_id     VARCHAR(50),      -- natural
    customer_name   VARCHAR(200),
    segment         VARCHAR(50),
    valid_from      DATE NOT NULL,
    valid_to        DATE DEFAULT '9999-12-31',
    is_current      BOOLEAN DEFAULT TRUE
);

-- Customer history:
-- key=1: Jan Novák, SMB, 2024-01-01 → 2025-06-30
-- key=2: Jan Novák, Enterprise, 2025-07-01 → 9999-12-31 (current)

Type 2 in dbt¶

-- dbt snapshot for SCD Type 2
{% snapshot customer_snapshot %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='check',
    check_cols=['customer_name', 'segment', 'email'],
) }}
SELECT * FROM {{ source('raw', 'customers') }}
{% endsnapshot %}

Type 3 — Add Column¶

A column is added for the previous value (previous_segment). Limited history.

Summary¶

SCD Type 2 is the most commonly used — complete history with valid_from/to. dbt snapshots automate the implementation.