04. Implementing SCD Type 2
Implementing SCD Type 2 with dbt Snapshots
Slowly Changing Dimension Type 2 (SCD Type 2) means we keep a new row every time an important attribute changes.
In this lesson, we will implement SCD Type 2 using the same customer table from the earlier RAW → SILVER → GOLD demo:
- Snowflake raw table:
DEMO_DB.RAW.CUSTOMERS - dbt staging model:
stg_customers - dbt snapshot:
customers_snapshot
The goal is simple:
If a customer's EMAIL or COUNTRY changes, we should keep both:
- the old customer version
- the new customer version
Types of Slowly Changing Dimensions
Common SCD types include:
-
Type 0 – no changes are tracked; the value never changes.
-
Real-world example: a product category code assigned at launch and never updated in the warehouse.
Before and after:
PRODUCT_ID PRODUCT_NAME CATEGORY_CODE 100 Widget A CAT-10 If the source system later reclassifies
Widget Ato a different category, the warehouse still keeps the originalCAT-10value.In Type 0, the dimension row remains unchanged, and no history is recorded.
-
-
Type 1 – overwrite the old value with the new value; history is not preserved.
-
Example:
Before:
CUSTOMER_ID NAME COUNTRY 1 Alice Johnson USA After update:
CUSTOMER_ID NAME COUNTRY 1 Alice Johnson Canada Only the latest value remains; the previous
USAvalue is lost.
-
-
Type 2 – keep a new row for each change; full history is preserved.
-
Example:
Before:
CUSTOMER_ID NAME COUNTRY dbt_valid_from dbt_valid_to 1 Alice Johnson USA 2025-11-12 08:45:00 null After the change:
CUSTOMER_ID NAME COUNTRY dbt_valid_from dbt_valid_to 1 Alice Johnson USA 2025-11-12 08:45:00 2025-11-20 10:00:00 1 Alice Johnson Canada 2025-11-20 10:00:00 null Both rows exist so you can see the historical and current version.
-
-
Type 3 – keep a limited change history using extra columns, such as
previous_value.-
Example:
CUSTOMER_ID NAME COUNTRY PREVIOUS_COUNTRY 1 Alice Johnson Canada USA Type 3 stores only the current state plus one prior value.
-
-
Type 4 – separate current and history tables.
-
Example:
Current table:
CUSTOMER_ID NAME COUNTRY 1 Alice Johnson Canada History table:
CUSTOMER_ID NAME COUNTRY valid_from valid_to 1 Alice Johnson USA 2025-11-12 2025-11-20 Type 4 is less common and usually used for performance-sensitive systems.
-
This lesson focuses on Type 2 because it is the most common pattern for preserving change history over time.
Why most companies use SCD Type 2
Most companies prefer SCD Type 2 because it provides:
- Full auditability — every change is preserved in history.
- Point-in-time accuracy — analysts can reconstruct the state of a record at any date.
- Analytics flexibility — you can compare current and prior values, calculate churn, and report changes over time.
- Regulatory support — many finance, healthcare, and insurance use cases require historical tracking.
- Simple tooling support — dbt snapshots and many warehouse patterns are built around Type 2 history.
Type 2 is often the best balance between history and structure: it preserves changes without requiring complex custom columns, while still keeping each version as a separate row.
Where SCD Type 2 is used in real life
SCD Type 2 is used when you need to answer questions like:
- What was the customer’s email last month?
- Which country did this customer belong to at the time of the sale?
- How has a product’s category changed over time?
- What was the historical state of a customer when they made a purchase?
Real-world use cases:
- customer profile history in CRM / marketing systems
- product attribute history in catalog or pricing systems
- account status changes in finance or banking
- employee record history in HR systems
Simple Type 2 example
Imagine a customer record that changes from:
| CUSTOMER_ID | NAME | COUNTRY | |
|---|---|---|---|
| 1 | Alice Johnson | alice.johnson@example.com | USA |
To:
| CUSTOMER_ID | NAME | COUNTRY | |
|---|---|---|---|
| 1 | Alice Johnson | alice.new@example.com | Canada |
With SCD Type 2, the snapshot table keeps both rows:
- row 1: old email + old country + historical dates
- row 2: new email + new country + current active row
This lets analysts reconstruct history and compare changes over time.
What SCD Type 2 Output Looks Like
Suppose Alice changes her email and country.
The raw table will show only the latest value:
| CUSTOMER_ID | NAME | COUNTRY | |
|---|---|---|---|
| 1 | Alice Johnson | alice.new@example.com | Canada |
The snapshot table will show history:
| CUSTOMER_ID | NAME | COUNTRY | dbt_valid_from | dbt_valid_to | |
|---|---|---|---|---|---|
| 1 | Alice Johnson | alice.johnson@example.com | USA | first run time | second run time |
| 1 | Alice Johnson | alice.new@example.com | Canada | second run time | null |
The row where dbt_valid_to is null is the current active version.
Step 1: Snowflake Setup SQL
If you already completed the RAW → SILVER → GOLD demo, you may already have this table.
Use this SQL in Snowflake to reset the customer table for this exercise:
CREATE DATABASE IF NOT EXISTS DEMO_DB;
CREATE SCHEMA IF NOT EXISTS DEMO_DB.RAW;
CREATE OR REPLACE TABLE DEMO_DB.RAW.CUSTOMERS (
CUSTOMER_ID INT,
NAME STRING,
EMAIL STRING,
COUNTRY STRING,
CREATED_AT TIMESTAMP
);
INSERT INTO DEMO_DB.RAW.CUSTOMERS
(CUSTOMER_ID, NAME, EMAIL, COUNTRY, CREATED_AT)
VALUES
(1, 'Alice Johnson', 'alice.johnson@example.com', 'USA', '2025-11-12 08:45:00'),
(2, 'Carlos Rivera', 'carlos.rivera@example.com', 'Mexico', '2025-11-13 14:20:00'),
(3, 'Mei Wong', 'mei.wong@example.com', 'Singapore', '2025-11-14 10:05:00'),
(4, 'Priya Singh', 'priya.singh@example.com', 'India', '2025-11-15 17:30:00'),
(5, 'Max Mueller', 'max.mueller@example.com', 'Germany', '2025-11-16 09:10:00');
Check the source data:
SELECT *
FROM DEMO_DB.RAW.CUSTOMERS
ORDER BY CUSTOMER_ID;
Step 2: dbt Source Configuration
In your dbt project, make sure models/schema.yml has the raw.customers source.
version: 2
sources:
- name: raw
description: "Raw data landing zone in Snowflake"
database: DEMO_DB
schema: RAW
tables:
- name: customers
description: "Raw customer data"
columns:
- name: CUSTOMER_ID
description: "Unique customer identifier"
- name: NAME
description: "Customer full name"
- name: EMAIL
description: "Customer email address"
- name: COUNTRY
description: "Customer country"
- name: CREATED_AT
description: "Customer creation timestamp"
Step 3: dbt Staging Model
Create or confirm this model:
models/bronze/stg_customers.sql
{{ config(materialized='view') }}
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
CREATED_AT
FROM {{ source('raw', 'customers') }}
Run the staging model:
dbt run --select stg_customers
Verify in Snowflake if needed:
SELECT *
FROM DEV_DB.BRONZE.STG_CUSTOMERS
ORDER BY CUSTOMER_ID;
Your actual database and schema may be different depending on your dbt profile and dbt_project.yml.
Step 4: dbt Snapshot Code
Create this file:
snapshots/customers_snapshot.sql
Add this dbt snapshot:
{% snapshot customers_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='CUSTOMER_ID',
strategy='check',
check_cols=['NAME', 'EMAIL', 'COUNTRY']
)
}}
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
CREATED_AT
FROM {{ ref('stg_customers') }}
{% endsnapshot %}
Why strategy='check'?
Our training table has CREATED_AT, but it does not have UPDATED_AT.
For SCD Type 2, CREATED_AT is not enough because it does not change when a customer email or country changes.
So we use:
check_cols=['NAME', 'EMAIL', 'COUNTRY']
This tells dbt to create a new historical version when any of these values change.
Step 5: Run the Snapshot First Time
Run:
dbt snapshot --select customers_snapshot
On the first run, dbt creates the snapshot table and inserts the current customers.
Query the snapshot table in Snowflake:
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT
ORDER BY CUSTOMER_ID, dbt_valid_from;
Expected result:
- Each customer appears once.
dbt_valid_toisnullfor every row.- This means every row is currently active.
Step 6: Apply Source Changes with DML
Now simulate business changes in the source system.
Run this in Snowflake:
UPDATE DEMO_DB.RAW.CUSTOMERS
SET
EMAIL = 'alice.new@example.com',
COUNTRY = 'Canada'
WHERE CUSTOMER_ID = 1;
UPDATE DEMO_DB.RAW.CUSTOMERS
SET
COUNTRY = 'United States'
WHERE CUSTOMER_ID = 2;
Check the raw source table:
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
CREATED_AT
FROM DEMO_DB.RAW.CUSTOMERS
WHERE CUSTOMER_ID IN (1, 2)
ORDER BY CUSTOMER_ID;
At this point, Snowflake raw data shows only the latest values.
The previous values are overwritten in DEMO_DB.RAW.CUSTOMERS.
Step 7: Run the Snapshot Again
Run dbt snapshot again:
dbt snapshot --select customers_snapshot
dbt compares the current stg_customers data against the previous snapshot records.
Because customer 1 and customer 2 changed, dbt will:
- Set
dbt_valid_toon the old versions. - Insert new active versions.
Step 8: Query SCD Type 2 History
Query only the changed customers:
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT
WHERE CUSTOMER_ID IN (1, 2)
ORDER BY CUSTOMER_ID, dbt_valid_from;
Expected output pattern:
| CUSTOMER_ID | COUNTRY | dbt_valid_from | dbt_valid_to | |
|---|---|---|---|---|
| 1 | alice.johnson@example.com | USA | first run time | second run time |
| 1 | alice.new@example.com | Canada | second run time | null |
| 2 | carlos.rivera@example.com | Mexico | first run time | second run time |
| 2 | carlos.rivera@example.com | United States | second run time | null |
This is SCD Type 2.
The old versions are preserved, and the latest versions have dbt_valid_to = null.
Step 9: Query Current vs Historical Records
Current active customer records:
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT
WHERE dbt_valid_to IS NULL
ORDER BY CUSTOMER_ID;
Full customer history:
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT
ORDER BY CUSTOMER_ID, dbt_valid_from;
History as of a point in time:
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT
WHERE dbt_valid_from <= '2026-01-01 00:00:00'
AND (
dbt_valid_to > '2026-01-01 00:00:00'
OR dbt_valid_to IS NULL
)
ORDER BY CUSTOMER_ID;
Replace the timestamp with the reporting date you want to analyze.
Interactive Command Flow
Use this flow while teaching:
# 1. Build the staging model
dbt run --select stg_customers
# 2. Run the snapshot first time
dbt snapshot --select customers_snapshot
# 3. Apply UPDATE statements in Snowflake
# 4. Run the snapshot again
dbt snapshot --select customers_snapshot
# 5. Optional: run all snapshots
dbt snapshot
Use this when you want to rebuild staging and then snapshot:
dbt run --select stg_customers
dbt snapshot --select customers_snapshot
Important Columns Added by dbt
dbt automatically adds these columns to the snapshot table:
| Column | Meaning |
|---|---|
dbt_scd_id | Internal unique ID for the snapshot row |
dbt_updated_at | Timestamp dbt uses for the snapshot update |
dbt_valid_from | When this version became active |
dbt_valid_to | When this version stopped being active |
For current records:
dbt_valid_to IS NULL
For old historical records:
dbt_valid_to IS NOT NULL
Common SCD Type 2 Mistakes
| Mistake | Why it is a problem |
|---|---|
Using CREATED_AT as UPDATED_AT | Creation time does not change when customer values change |
Wrong unique_key | dbt cannot correctly match old and new customer versions |
Not including important columns in check_cols | dbt will not track changes to those columns |
| Running snapshot only once | History is captured only when dbt snapshot runs |
Querying without dbt_valid_to IS NULL | You may accidentally include old records in current reports |
Key Takeaways
SCD Type 2 keeps old and new versions of changing records
dbt implements SCD Type 2 using snapshots
For this demo, use strategy='check' because there is no UPDATED_AT column
CUSTOMER_ID is the unique_key
NAME, EMAIL, and COUNTRY are tracked in check_cols
Current rows have dbt_valid_to IS NULL
Historical rows have a value in dbt_valid_to