Slowly Changing Dimensions (SCD)
In Data Warehousing, a "Dimension" table holds descriptive attributes about business entities (Customers, Products, Employees). But what happens when those attributes change over time?
If Arjun moves from Bengaluru to Mumbai, do we overwrite his old city? Do we keep a record of his old city?
The way we handle these changes defines our Slowly Changing Dimension (SCD) strategy.
Topics Covered
- SCD Type 1 (Overwrite)
- SCD Type 2 (Full History)
- SCD Type 3 (Limited History)
- Implementing SCD Type 2 in Snowflake
1. SCD Type 1: Overwrite (No History)
In SCD Type 1, we do not care about the past. When an attribute changes, we simply overwrite the old value.
- Pros: Easy to implement, keeps the table small.
- Cons: You lose all historical context. If an analyst tries to run a report on "Sales in Bengaluru for 2022," the numbers will be wrong, because Arjun's 2022 sales will now be attributed to Mumbai.
Snowflake Implementation (Standard UPDATE)
-- Arjun moves to Mumbai
UPDATE DIM_CUSTOMER
SET CITY = 'Mumbai'
WHERE CUSTOMER_ID = 101;
2. SCD Type 2: Add New Row (Full History)
This is the most common and robust method in modern data warehousing. Instead of overwriting the old row, we "expire" the old row and insert a brand new row.
To make this work, the table needs three tracking columns:
VALID_FROM(Timestamp)VALID_TO(Timestamp, NULL if active)IS_CURRENT(Boolean)
- Pros: Perfect historical accuracy. You can query exactly what the dimension looked like on any given date.
- Cons: Complex to implement and increases table size significantly.
Snowflake Implementation (The MERGE Statement)
(To verify your active role before writing MERGE statements, run SELECT CURRENT_ROLE();)
In Snowflake, SCD Type 2 is usually implemented by reading changes from a Stream and applying them via a MERGE. Because an SCD2 update requires two actions (Expire the old row + Insert the new row), we join the stream to the target table twice.
-- Assume STG_CUSTOMER_UPDATES contains the new data (Arjun moving to Mumbai)
MERGE INTO DIM_CUSTOMER target
USING (
-- The "Expire" record
SELECT CUSTOMER_ID, CITY, 'UPDATE' AS MERGE_ACTION FROM STG_CUSTOMER_UPDATES
UNION ALL
-- The "New" record
SELECT CUSTOMER_ID, CITY, 'INSERT' AS MERGE_ACTION FROM STG_CUSTOMER_UPDATES
) source
ON target.CUSTOMER_ID = source.CUSTOMER_ID
AND target.IS_CURRENT = TRUE
AND source.MERGE_ACTION = 'UPDATE'
-- Step 1: Expire the old row
WHEN MATCHED AND target.CITY != source.CITY THEN
UPDATE SET
target.VALID_TO = CURRENT_TIMESTAMP(),
target.IS_CURRENT = FALSE
-- Step 2: Insert the new row
WHEN NOT MATCHED AND source.MERGE_ACTION = 'INSERT' THEN
INSERT (CUSTOMER_ID, CITY, VALID_FROM, VALID_TO, IS_CURRENT)
VALUES (source.CUSTOMER_ID, source.CITY, CURRENT_TIMESTAMP(), NULL, TRUE);
3. SCD Type 3: Add New Column (Limited History)
SCD Type 3 is a compromise. We don't want the complexity of Type 2 (multiple rows), but we want to track some history.
We accomplish this by adding a "Previous" column to the table.
| CUST_ID | NAME | CURRENT_CITY | PREVIOUS_CITY |
|---|---|---|---|
| 101 | Arjun | Mumbai | Bengaluru |
- Pros: Keeps the table size small (one row per customer), while allowing comparison between current and previous states.
- Cons: You can only track one historical change. If Arjun moves a third time, the Bengaluru data is permanently lost.
Snowflake Implementation (Standard UPDATE)
-- Arjun moves to Chennai
UPDATE DIM_CUSTOMER
SET
PREVIOUS_CITY = CURRENT_CITY, -- Move Mumbai to Previous
CURRENT_CITY = 'Chennai' -- Set Chennai as Current
WHERE CUSTOMER_ID = 101;
Dynamic Tables and SCDs
Traditionally, SCD Type 2 logic requires complex MERGE statements inside Snowflake Tasks (as shown above).
However, with the introduction of Dynamic Tables, Snowflake can handle basic SCD Type 2 logic declaratively if your source system provides CDC (Change Data Capture) logs. If you load raw Debezium CDC logs into a Bronze table, a Dynamic Table can read those logs and automatically build an SCD Type 2 dimension without any MERGE logic.
Summary
- SCD Type 1 (Overwrite): Loses history. Use only for trivial attributes (e.g., spelling corrections).
- SCD Type 2 (Full History): Creates a new row for every change, tracking validity dates. The industry standard for dimensional modeling. Implemented via Streams and
MERGE. - SCD Type 3 (Previous Column): Adds a "Previous Value" column. Limited to tracking exactly one historical change.