Skip to main content

Chapter 31: Slowly Changing Dimensions (SCD)

Slowly Changing Dimensions

A data warehouse is not a snapshot of today. It is a historical record, a timeline of everything that happened, to whom, when, and where. That historical depth is exactly what makes a warehouse valuable for analysis. But maintaining it cleanly requires solving a problem that never comes up in operational databases: what do you do when the description of something changes?

A customer moves cities. A product gets reclassified into a different category. A salesperson is transferred to a new region. These are not new events; they are changes to existing dimensions. How you handle those changes determines whether your historical analysis tells the truth or silently lies.

Slowly Changing Dimensions (SCD) are the strategies for handling exactly this problem.

31.1 Why SCD Exists​

Dimension tables hold descriptive context: who a customer is, what a product belongs to, where a store is located. That context is the lens through which you interpret every fact in the warehouse. If the lens changes, you have a choice to make.

Consider a dimension table for customers:

customer_key | customer_id | first_name | last_name | city         | state
-------------+-------------+------------+-----------+--------------+-------
1            | C001        | Alice      | Johnson   | Sacramento   | CA
2            | C002        | Daniel     | Cho       | San Diego    | CA

Alice then moves from Sacramento to San Francisco. New data arrives:

customer_id | first_name | last_name | city          | state
------------+------------+-----------+---------------+-------
C001        | Alice      | Johnson   | San Francisco | CA

There is a conflict. The incoming row has the same customer_id but a different city. You cannot simply insert it, because that would create a duplicate. You cannot ignore it, because then the warehouse has stale data. You have to decide what to do, and that decision has downstream consequences for every report that uses city as a dimension.

SCD is not a single answer. It is a set of five strategies, each making a different trade-off between simplicity and historical accuracy.

31.2 SCD-0: No Updates​

What it does: Ignores incoming changes entirely. Once a row is written, it never changes.

When to use it: Only for dimensions that genuinely cannot change: values that are fixed at the point of creation. Date of birth, account creation date, the country code on an original transaction.

Existing table:
customer_id | date_of_birth
------------+--------------
C001        | 1990-03-15
C002        | 1985-11-22

Incoming data:
customer_id | date_of_birth
------------+--------------
C001        | 1991-03-15    ← ignored
-- Snowflake: Insert only new customer_ids, ignore existing ones
INSERT INTO dim_customer (customer_id, date_of_birth)
SELECT src.customer_id, src.date_of_birth
FROM staging_customer AS src
WHERE src.customer_id NOT IN (SELECT customer_id FROM dim_customer);

The incoming record for C001 is dropped. The original date of birth is preserved.

In practice: Use sparingly. Very few fields are truly immutable. Even date of birth gets corrected when someone enters it wrong the first time, which means you'll need to handle that exception somehow regardless.

31.3 SCD-1: Overwrite​

What it does: Overwrites the existing row with new values. No history is kept.

When to use it: When only the current value of a dimension matters and historical accuracy for that attribute is not required: email addresses, phone numbers, preference flags.

Before:
customer_key | customer_id | phone
-------------+-------------+------------------
1            | C001        | +1-916-555-0101

After (C001's number changed):
customer_key | customer_id | phone
-------------+-------------+------------------
1            | C001        | +1-415-555-0199   ← overwritten
-- Snowflake: MERGE statement for SCD-1
MERGE INTO dim_customer AS tgt
USING staging_customer AS src
    ON tgt.customer_id = src.customer_id
WHEN MATCHED THEN UPDATE SET
    tgt.phone     = src.phone,
    tgt.email     = src.email,
    tgt.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (customer_id, phone, email, updated_at)
    VALUES (src.customer_id, src.phone, src.email, CURRENT_TIMESTAMP());

In practice: Simple and low-maintenance. The risk is that once you overwrite, recovery requires going back to the source system. If business requirements change and historical phone numbers suddenly matter, there is nothing to recover. Use it for attributes where that loss is genuinely acceptable.

31.4 SCD-2: New Row Per Change​

What it does: Keeps full history by inserting a new row for every change, tagging each row with effective dates and a version number. The old row is not deleted; its end date is set to mark it as expired.

When to use it: Whenever you need to answer questions accurately for any point in time: customer location, product category, staff assignment, pricing tiers.

SCD Type 2 History Rows

Before (Alice in Sacramento):
customer_key | customer_id | city         | version | start_date | end_date   | is_current
-------------+-------------+--------------+---------+------------+------------+-----------
1            | C001        | Sacramento   | 1       | 2022-01-01 | 9999-12-31 | TRUE

After (Alice moves to San Francisco on 2024-06-01):
customer_key | customer_id | city           | version | start_date | end_date   | is_current
-------------+-------------+----------------+---------+------------+------------+-----------
1            | C001        | Sacramento     | 1       | 2022-01-01 | 2024-05-31 | FALSE
2            | C001        | San Francisco  | 2       | 2024-06-01 | 9999-12-31 | TRUE

The fact table points to customer_key, not customer_id. A sale made in 2023 still joins to customer_key = 1 (Sacramento). A sale made in 2024 joins to customer_key = 2 (San Francisco). Historical reporting stays accurate regardless of when Alice moved.

-- Snowflake: SCD-2 implementation with MERGE

MERGE INTO dim_customer AS tgt
USING staging_customer AS src
    ON tgt.customer_id = src.customer_id
    AND tgt.is_current = TRUE

-- Expire the old row if city changed
WHEN MATCHED AND tgt.city <> src.city THEN UPDATE SET
    tgt.end_date   = DATEADD(day, -1, CURRENT_DATE()),
    tgt.is_current = FALSE

-- Insert the new version
WHEN NOT MATCHED THEN INSERT (
    customer_id, city, version, start_date, end_date, is_current
) VALUES (
    src.customer_id, src.city, 1, CURRENT_DATE(), '9999-12-31', TRUE
);

-- Insert updated version for changed rows
INSERT INTO dim_customer (customer_id, city, version, start_date, end_date, is_current)
SELECT
    src.customer_id,
    src.city,
    tgt.version + 1,
    CURRENT_DATE(),
    '9999-12-31',
    TRUE
FROM staging_customer AS src
JOIN dim_customer AS tgt
    ON src.customer_id = tgt.customer_id
    AND tgt.is_current = FALSE
    AND tgt.end_date = DATEADD(day, -1, CURRENT_DATE());

The three metadata columns explained:

ColumnPurposeExample
start_dateWhen this version became active2024-06-01
end_dateWhen this version expired (9999-12-31 = still active)2024-05-31
is_currentQuick filter for the latest versionTRUE / FALSE
versionSequential version counter1, 2, 3

Querying current data:

SELECT * FROM dim_customer WHERE is_current = TRUE;

Querying as of a specific date:

SELECT * FROM dim_customer
WHERE '2023-08-15' BETWEEN start_date AND end_date;

In practice: SCD-2 is the most widely used strategy in production warehouses. dbt's snapshot feature automates the entire pattern, including defining a source, choosing which columns trigger a new version, and handling the date management, which we cover in Chapter 34.

Interview Angle "How do you implement SCD-2 in a data warehouse?" The expected answer names all three metadata columns (start_date, end_date, is_current), explains why the fact table uses a surrogate key (not the natural key) to preserve point-in-time accuracy, and mentions the MERGE statement for handling both expiry and insertion in one operation. Mentioning dbt snapshots as a modern implementation shows real-world experience.

31.5 SCD-3: Previous Value Column​

What it does: Adds a previous_value column alongside the current_value column. No new rows are added; the old value is shifted into the previous column.

Before:
customer_key | customer_id | current_city | previous_city
-------------+-------------+--------------+--------------
1            | C001        | Sacramento   | NULL

After (Alice moves):
customer_key | customer_id | current_city   | previous_city
-------------+-------------+----------------+--------------
1            | C001        | San Francisco  | Sacramento
-- Snowflake: SCD-3 update
UPDATE dim_customer AS tgt
SET
    tgt.previous_city = tgt.current_city,
    tgt.current_city  = src.city
FROM staging_customer AS src
WHERE tgt.customer_id = src.customer_id
  AND tgt.current_city <> src.city;

The limitation: If Alice moves a third time, to Oakland, Sacramento is permanently lost. The column can only hold one previous value. Some teams add previous_2_city, previous_3_city, but this scales poorly and becomes unreadable quickly.

In practice: SCD-3 is appropriate only when exactly one historical value per attribute needs to be retained and future changes are unlikely. Most production warehouses reach for SCD-2 instead, because it handles unlimited changes gracefully.

31.6 SCD-4: Current Table + History Table​

What it does: Maintains two separate tables: a dim_customer table that always holds the current state (like SCD-1), and a dim_customer_history table that records every previous version (like SCD-2).

dim_customer (current only):
customer_key | customer_id | city           | updated_at
-------------+-------------+----------------+------------
1            | C001        | San Francisco  | 2024-06-01

dim_customer_history (all versions):
history_key | customer_key | customer_id | city         | start_date | end_date
------------+--------------+-------------+--------------+------------+------------
1           | 1            | C001        | Sacramento   | 2022-01-01 | 2024-05-31
2           | 1            | C001        | San Francisco | 2024-06-01 | 9999-12-31
-- Snowflake: SCD-4, update current table
MERGE INTO dim_customer AS tgt
USING staging_customer AS src
    ON tgt.customer_id = src.customer_id
WHEN MATCHED AND tgt.city <> src.city THEN UPDATE SET
    tgt.city       = src.city,
    tgt.updated_at = CURRENT_TIMESTAMP();

-- Archive the old version to history table
INSERT INTO dim_customer_history (customer_key, customer_id, city, start_date, end_date)
SELECT
    tgt.customer_key,
    tgt.customer_id,
    tgt.city,
    tgt.updated_at,
    CURRENT_DATE()
FROM dim_customer AS tgt
JOIN staging_customer AS src
    ON tgt.customer_id = src.customer_id
WHERE tgt.city <> src.city;

When SCD-4 beats SCD-2:

If your dimension has very high update frequency, such as a user platform where addresses change thousands of times per day, SCD-2 inflates the dimension table to an unmanageable size. dim_customer with SCD-2 on a platform with 100M users and frequent address changes could have billions of rows. SCD-4 keeps dim_customer lean (100M rows, one per user) and moves the history out of the hot path.

When SCD-2 beats SCD-4:

If downstream analysts frequently need historical data, SCD-2 is one query. SCD-4 requires a join between dim_customer and dim_customer_history every time: two tables, extra complexity, higher risk of error.

31.7 SCD-5 and Beyond​

SCD-5 through SCD-7 exist but are rarely used in practice. They are combinations of earlier types:

  • SCD-5: SCD-4 + mini-dimension (stores frequently changing attributes separately)
  • SCD-6: SCD-1 + SCD-2 + SCD-3 combined
  • SCD-7: Dual table approach combining current and historical views

Unless you are facing a specific set of requirements that lower types cannot meet, these are over-engineered. They add unnecessary complexity to your data pipeline for benefits most warehouses never realize.

31.8 Choosing the Right SCD Type​

The right strategy depends on two questions: Does history matter? and How often does the data change?

History NOT neededHistory needed, low frequencyHistory needed, high frequency
Simple updateSCD-0 or SCD-1SCD-2SCD-4
One previous valueSCD-1SCD-3SCD-4
Full audit trailSCD-1SCD-2SCD-4

Decision Checklist​

Use SCD-0 when the field literally cannot change (creation timestamps, original transaction codes).

Use SCD-1 when only current state matters and data loss is acceptable (contact details, preference flags, correctable input errors).

Use SCD-2 when historical accuracy matters and changes are infrequent-to-moderate (customer location, product category, staff region assignment). This is the default choice for most production warehouses.

Use SCD-3 when exactly one prior value needs tracking and further changes are unlikely, a genuinely rare case.

Use SCD-4 when the dimension changes very frequently and the high volume of SCD-2 rows would make the dimension table impractical to query.

31.9 Implementing SCDs in Snowflake​

Snowflake provides two native features that pair well with SCD implementations.

Streams: Capturing Changes Automatically​

A Snowflake Stream tracks changes (inserts, updates, deletes) in a source table since the last time the stream was consumed. Rather than comparing staging data against the full dimension table on every load, the stream gives you only the delta: what actually changed.

-- Create a stream on the staging table
CREATE STREAM staging_customer_stream ON TABLE staging_customer;

-- Consume the stream in your SCD-2 MERGE
MERGE INTO dim_customer AS tgt
USING (
    SELECT * FROM staging_customer_stream
    WHERE METADATA$ACTION = 'INSERT'
) AS src
ON tgt.customer_id = src.customer_id AND tgt.is_current = TRUE
...

Tasks: Automating the SCD Pipeline​

A Snowflake Task schedules the SCD merge to run on a defined cadence: every hour, daily, or triggered by a stream having data.

CREATE TASK scd2_customer_refresh
    WAREHOUSE = compute_wh
    SCHEDULE  = 'USING CRON 0 * * * * UTC'   -- every hour
WHEN
    SYSTEM$STREAM_HAS_DATA('staging_customer_stream')
AS
    -- your SCD-2 MERGE statement here
;

ALTER TASK scd2_customer_refresh RESUME;

Together, Streams and Tasks give you a self-running SCD pipeline: data changes in the source, the stream captures them, the task fires only when there is something to process, and the dimension table stays current automatically.

31.10 Putting It Together​

The BikeStores warehouse needs to track customer location accurately over time. When a customer moves, historical orders should still reflect where the customer lived when the order was placed. SCD-2 is the right choice.

-- Step 1: Dimension table structure
CREATE TABLE dim_customer (
    customer_key  INT AUTOINCREMENT PRIMARY KEY,
    customer_id   INT NOT NULL,
    first_name    VARCHAR(100),
    last_name     VARCHAR(100),
    city          VARCHAR(100),
    state         VARCHAR(50),
    -- SCD-2 metadata
    version       INT DEFAULT 1,
    start_date    DATE NOT NULL,
    end_date      DATE DEFAULT '9999-12-31',
    is_current    BOOLEAN DEFAULT TRUE
);

-- Step 2: Stream on staging
CREATE STREAM staging_customer_changes ON TABLE staging_customer;

-- Step 3: SCD-2 MERGE
MERGE INTO dim_customer AS tgt
USING (
    SELECT * FROM staging_customer_changes
    WHERE METADATA$ACTION IN ('INSERT', 'UPDATE')
) AS src
    ON tgt.customer_id = src.customer_id
    AND tgt.is_current = TRUE

-- Expire the old version when city or state changed
WHEN MATCHED AND (tgt.city <> src.city OR tgt.state <> src.state)
    THEN UPDATE SET
        tgt.end_date   = DATEADD(day, -1, CURRENT_DATE()),
        tgt.is_current = FALSE

-- Insert first-time customers
WHEN NOT MATCHED THEN INSERT (
    customer_id, first_name, last_name, city, state,
    version, start_date, end_date, is_current
) VALUES (
    src.customer_id, src.first_name, src.last_name,
    src.city, src.state, 1, CURRENT_DATE(), '9999-12-31', TRUE
);

A fact table query that correctly attributes historical orders to the customer's location at the time:

SELECT
    dc.city,
    dc.state,
    COUNT(DISTINCT fo.order_id)               AS order_count,
    SUM(fo.revenue)                            AS total_revenue
FROM fact_orders AS fo
JOIN dim_customer AS dc
    ON fo.customer_key = dc.customer_key   -- surrogate key join
WHERE fo.order_date BETWEEN dc.start_date AND dc.end_date
GROUP BY dc.city, dc.state
ORDER BY total_revenue DESC;

The WHERE fo.order_date BETWEEN dc.start_date AND dc.end_date clause is what makes the history honest, because it ensures each order joins to the version of the customer that was active when the order happened.

Summary​

Slowly Changing Dimensions handle the fact that dimension attributes change over time, and a warehouse must decide what to do when they do. SCD-0 ignores changes, which is appropriate for truly immutable fields. SCD-1 overwrites: simple, no history. SCD-2 inserts new rows with effective dates and a version number, the most common production choice, preserving full history with clean point-in-time queries. SCD-3 adds a previous-value column, limited to one prior version and rarely adequate. SCD-4 splits current and history into two tables, better for high-frequency changes where SCD-2 would bloat the dimension. In Snowflake, Streams capture change data automatically and Tasks schedule the SCD merge to run only when changes exist, building a self-managing pipeline.

Exercises​

31.1 A dim_product table tracks product_name, category, and list_price. Which SCD type would you apply to each attribute and why? (Consider: product names are rarely changed; categories are reassigned periodically; prices change frequently.)

31.2 Write a CREATE TABLE statement for a dim_staff SCD-2 table that tracks region and store_id over time. Include all required SCD-2 metadata columns.

31.3 The following query is meant to get total revenue for customers currently based in California. What is the bug, and rewrite it correctly using SCD-2 columns:

SELECT SUM(fo.revenue)
FROM fact_orders fo
JOIN dim_customer dc ON fo.customer_key = dc.customer_key
WHERE dc.state = 'CA';

31.4 Write the Snowflake SCD-1 MERGE for a dim_product table where list_price should always reflect the latest price, with no history kept.

31.5 A warehouse uses SCD-2 on dim_customer and has been running for 3 years. The customer table started with 500,000 customers, and on average 15% of customers change their address each year. Estimate how many rows the SCD-2 dimension table has today. Does this change your strategy choice?

31.6 Think About It: A business requirement says "show me revenue by customer city for Q3 2023." Your warehouse implemented SCD-1 (overwrite) on dim_customer.city. A year later, 30% of customers have moved. What does the Q3 2023 report actually show, and what did the analyst think they were asking for? What is the real cost of the SCD-1 choice in this scenario?