Skip to main content

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_IDPRODUCT_NAMECATEGORY_CODE
      100Widget ACAT-10

      If the source system later reclassifies Widget A to a different category, the warehouse still keeps the original CAT-10 value.

      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_IDNAMECOUNTRY
      1Alice JohnsonUSA

      After update:

      CUSTOMER_IDNAMECOUNTRY
      1Alice JohnsonCanada

      Only the latest value remains; the previous USA value is lost.

  • Type 2 – keep a new row for each change; full history is preserved.

    • Example:

      Before:

      CUSTOMER_IDNAMECOUNTRYdbt_valid_fromdbt_valid_to
      1Alice JohnsonUSA2025-11-12 08:45:00null

      After the change:

      CUSTOMER_IDNAMECOUNTRYdbt_valid_fromdbt_valid_to
      1Alice JohnsonUSA2025-11-12 08:45:002025-11-20 10:00:00
      1Alice JohnsonCanada2025-11-20 10:00:00null

      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_IDNAMECOUNTRYPREVIOUS_COUNTRY
      1Alice JohnsonCanadaUSA

      Type 3 stores only the current state plus one prior value.

  • Type 4 – separate current and history tables.

    • Example:

      Current table:

      CUSTOMER_IDNAMECOUNTRY
      1Alice JohnsonCanada

      History table:

      CUSTOMER_IDNAMECOUNTRYvalid_fromvalid_to
      1Alice JohnsonUSA2025-11-122025-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_IDNAMEEMAILCOUNTRY
1Alice Johnsonalice.johnson@example.comUSA

To:

CUSTOMER_IDNAMEEMAILCOUNTRY
1Alice Johnsonalice.new@example.comCanada

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_IDNAMEEMAILCOUNTRY
1Alice Johnsonalice.new@example.comCanada

The snapshot table will show history:

CUSTOMER_IDNAMEEMAILCOUNTRYdbt_valid_fromdbt_valid_to
1Alice Johnsonalice.johnson@example.comUSAfirst run timesecond run time
1Alice Johnsonalice.new@example.comCanadasecond run timenull

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_to is null for 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:

  1. Set dbt_valid_to on the old versions.
  2. 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_IDEMAILCOUNTRYdbt_valid_fromdbt_valid_to
1alice.johnson@example.comUSAfirst run timesecond run time
1alice.new@example.comCanadasecond run timenull
2carlos.rivera@example.comMexicofirst run timesecond run time
2carlos.rivera@example.comUnited Statessecond run timenull

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:

ColumnMeaning
dbt_scd_idInternal unique ID for the snapshot row
dbt_updated_atTimestamp dbt uses for the snapshot update
dbt_valid_fromWhen this version became active
dbt_valid_toWhen 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​

MistakeWhy it is a problem
Using CREATED_AT as UPDATED_ATCreation time does not change when customer values change
Wrong unique_keydbt cannot correctly match old and new customer versions
Not including important columns in check_colsdbt will not track changes to those columns
Running snapshot only onceHistory is captured only when dbt snapshot runs
Querying without dbt_valid_to IS NULLYou 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