Skip to main content

Scenario 2: SCD Type 2 Snapshot

Business Context: Your Indian logistics company maintains a customers table in the source application. When a customer moves to a new city, the application simply overwrites their old address. The Data Science team is complaining that they cannot build accurate shipping models because the historical address data is being permanently lost.

Your Objective: You must implement a Slowly Changing Dimension (SCD Type 2) using dbt Snapshots to track all historical changes to customer addresses.


1. Setup the Database Environment​

(Execute this in your Snowflake worksheet to prepare the environment. Find your username by running SELECT CURRENT_USER();)

-- 1. Create a dedicated database for this scenario
CREATE DATABASE IF NOT EXISTS BEEMS_SCENARIO_2_SCD;
USE DATABASE BEEMS_SCENARIO_2_SCD;
CREATE SCHEMA IF NOT EXISTS RAW;
CREATE SCHEMA IF NOT EXISTS SNAPSHOTS;

-- 2. Create the raw source table
CREATE OR REPLACE TABLE RAW.app_customers (
customer_id INT,
name VARCHAR,
city VARCHAR,
updated_at TIMESTAMP_NTZ
);

-- 3. Insert Day 1 Data
INSERT INTO RAW.app_customers VALUES
(101, 'Arjun', 'Mumbai', CURRENT_TIMESTAMP()),
(102, 'Priya', 'Bengaluru', CURRENT_TIMESTAMP());

2. Creating the dbt Snapshot​

In your dbt project, navigate to the snapshots/ folder. This is a special folder where dbt expects snapshot definitions.

Create a new file: snapshots/snp_app_customers.sql

{% snapshot snp_app_customers %}

{{
config(
target_database='BEEMS_SCENARIO_2_SCD',
target_schema='SNAPSHOTS',
unique_key='customer_id',

strategy='timestamp',
updated_at='updated_at'
)
}}

SELECT *
FROM BEEMS_SCENARIO_2_SCD.RAW.app_customers

{% endsnapshot %}

3. Testing the Snapshot​

Step 1: The Initial Run​

Run the snapshot command for the first time.

dbt snapshot

Verify in Snowflake:

SELECT * FROM BEEMS_SCENARIO_2_SCD.SNAPSHOTS.snp_app_customers;

You will see both Arjun and Priya. Notice the new metadata columns: DBT_VALID_FROM is today, and DBT_VALID_TO is NULL (meaning these are the active, current records).

Step 2: Simulating a Data Change​

Let's pretend 6 months pass. Arjun gets a new job and moves from Mumbai to Delhi. The source application overwrites his address.

(Run this in Snowflake)

UPDATE RAW.app_customers 
SET
city = 'Delhi',
updated_at = CURRENT_TIMESTAMP()
WHERE customer_id = 101;

If you query RAW.app_customers, the historical record of Arjun living in Mumbai is permanently gone.

Step 3: Capturing the Change​

Run the dbt snapshot command again. It will detect that Arjun's updated_at timestamp has changed.

dbt snapshot

Verify in Snowflake:

SELECT * FROM BEEMS_SCENARIO_2_SCD.SNAPSHOTS.snp_app_customers ORDER BY customer_id, dbt_valid_from;

What happened?

  1. dbt kept Priya exactly the same.
  2. dbt took the old record of Arjun (Mumbai) and set the DBT_VALID_TO timestamp, officially retiring it.
  3. dbt inserted a brand new row for Arjun (Delhi) with a DBT_VALID_TO of NULL, marking it as the new active record!

Summary​

You have successfully implemented a Slowly Changing Dimension Type 2 (SCD2) using dbt Snapshots. Your Data Science team can now query BEEMS_SCENARIO_2_SCD.SNAPSHOTS.snp_app_customers, look at the DBT_VALID_FROM and DBT_VALID_TO columns, and perfectly reconstruct exactly where Arjun lived on any given day in history!