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?
- dbt kept Priya exactly the same.
- dbt took the old record of Arjun (Mumbai) and set the
DBT_VALID_TOtimestamp, officially retiring it. - dbt inserted a brand new row for Arjun (Delhi) with a
DBT_VALID_TOofNULL, 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!