01. What are Snapshots
What are Snapshots in dbt?
A dbt snapshot is used to keep the history of records that change over time.
In many source systems, tables are mutable. This means the same row can be updated again and again. When that happens, the old value is usually overwritten.
For analytics, that is a problem.
If a customer's email, address, status, or plan changes, we may need to answer questions like:
- What was the customer's old email?
- When did the customer's address change?
- Which plan was active last month?
- How long was an order in
pendingstatus before it becameshipped?
Snapshots help us answer these questions by storing different versions of the same record.
Simple Beginner Example
Imagine this source table:
| customer_id | customer_name | plan_name | updated_at |
|---|---|---|---|
| 101 | Ananya | Free | 2026-01-01 09:00:00 |
Later, the same customer upgrades the plan:
| customer_id | customer_name | plan_name | updated_at |
|---|---|---|---|
| 101 | Ananya | Premium | 2026-02-10 14:30:00 |
In the source table, the old value Free is gone.
With a dbt snapshot, we can keep both versions:
| customer_id | customer_name | plan_name | dbt_valid_from | dbt_valid_to |
|---|---|---|---|---|
| 101 | Ananya | Free | 2026-01-01 09:00:00 | 2026-02-10 14:30:00 |
| 101 | Ananya | Premium | 2026-02-10 14:30:00 | null |
The row where dbt_valid_to is null is the current active version.
Why Do We Need Snapshots?
Use snapshots when the business needs historical tracking, not just the latest value.
Common use cases:
- Customer profile changes: email, phone number, address, customer segment
- Order status changes: pending, shipped, delivered, cancelled
- Subscription changes: free, trial, paid, enterprise
- Employee department changes: sales, finance, engineering
- Audit and compliance: prove what value existed at a point in time
Without snapshots, the warehouse usually knows only the latest version. With snapshots, the warehouse can answer what changed, when it changed, and what the old value was.
Are Snapshots a Materialization?
This is an important beginner question.
In dbt, we usually talk about model materializations such as:
viewtableincrementalephemeral
A snapshot is not a normal model materialization like table or incremental.
A snapshot is a separate dbt resource used for historical tracking.
However, it is useful to explain snapshots in the context of materializations because both models and snapshots create objects in the data warehouse.
Snapshot vs Incremental Materialization
Beginners often confuse snapshots and incremental models because both deal with changed data.
| Feature | Incremental Model | Snapshot |
|---|---|---|
| Main purpose | Build or update a model efficiently | Preserve historical versions of records |
| Keeps old versions? | Usually no | Yes |
| Common command | dbt run | dbt snapshot |
| Config location | Inside a model .sql file | Inside a snapshot .sql file |
| Typical output | Final analytics table | History table with validity dates |
| Example use case | Load only new sales transactions | Track customer email or plan changes |
Simple Way to Remember
- Use an incremental model when you want to process only new or changed rows for performance.
- Use a snapshot when you want to remember old values after the source system changes them.
Example:
If a customer's plan changes from Free to Premium:
- An incremental model may only show the latest plan:
Premium - A snapshot can show both versions:
FreeandPremium
So, for historical tracking, snapshots are the correct dbt feature.
How Snapshots Work
When you run:
dbt snapshot
dbt performs these steps:
- Reads the current data from the source query.
- Compares each row with the previous snapshot version.
- Uses a
unique_keyto identify the same business record. - Detects whether important values changed.
- Closes the old version by setting
dbt_valid_to. - Inserts a new version with the latest values.
On the first run, dbt creates the snapshot table and inserts the current records.
On the next runs, dbt only adds new versions for records that changed.
Important Terms
| Term | Meaning |
|---|---|
unique_key | Column that uniquely identifies one business record, such as customer_id |
strategy | Method used by dbt to detect changes |
updated_at | Timestamp column that tells when the source row was last updated |
check_cols | List of columns dbt should compare for changes |
dbt_valid_from | When this version became active |
dbt_valid_to | When this version stopped being active |
Where Do We Create Snapshots?
Snapshots are usually created inside the snapshots/ directory of a dbt project.
Example project structure:
my_dbt_project/
├── models/
├── snapshots/
│ └── customers_snapshot.sql
├── seeds/
├── macros/
└── dbt_project.yml
Each snapshot is written in a .sql file.
Snapshot Example Using Our Existing Demo Tables
In the earlier RAW → SILVER → GOLD demo, we already created:
DEMO_DB.RAW.CUSTOMERSmodels/bronze/stg_customers.sqlmodels/silver/silver_orders.sql
For snapshots, the best beginner example is customer profile history.
Why?
Customer details such as EMAIL and COUNTRY can change over time. If the raw table is updated, the old values are overwritten. A snapshot keeps the old and new versions.
Step 1: Confirm the Input Customer Data
From the earlier demo, the raw customer table has data like this:
| CUSTOMER_ID | NAME | COUNTRY | CREATED_AT | |
|---|---|---|---|---|
| 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 |
You can check the raw table in Snowflake:
SELECT *
FROM DEMO_DB.RAW.CUSTOMERS
ORDER BY CUSTOMER_ID;
You can also check the dbt staging model:
SELECT *
FROM {{ ref('stg_customers') }};
In Snowflake directly, the compiled object may look like:
SELECT *
FROM DEV_DB.BRONZE.STG_CUSTOMERS;
The exact database and schema depend on your dbt target configuration.
Step 2: Create the Snapshot File
Create a snapshot file:
snapshots/customers_snapshot.sql
Add this 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 %}
Snapshot Model Explained
| Code | Explanation |
|---|---|
{% snapshot customers_snapshot %} | Starts the snapshot block and gives it a name |
target_schema='snapshots' | Stores the snapshot table in the snapshots schema |
unique_key='CUSTOMER_ID' | Tells dbt how to match the same customer across runs |
strategy='check' | Tells dbt to compare selected columns for changes |
check_cols=['NAME', 'EMAIL', 'COUNTRY'] | Tracks changes to customer name, email, or country |
from {{ ref('stg_customers') }} | Uses the Bronze staging model from the earlier demo |
{% endsnapshot %} | Ends the snapshot block |
Why are we using check strategy here?
Because the earlier RAW.CUSTOMERS table has CREATED_AT, but it does not have a reliable UPDATED_AT column.
For this training example:
CREATED_ATtells when the customer was first created.- It does not prove when
EMAILorCOUNTRYchanged. - So
check_colsis the better choice.
Step 3: Run the Snapshot for the First Time
Before running the snapshot, make sure the staging model exists:
dbt run --select stg_customers
Now run the snapshot:
dbt snapshot --select customers_snapshot
On the first run, dbt creates the snapshot table and inserts the current customer records.
Expected result:
Completed successfully
The snapshot table will be created in the configured snapshot schema.
Step 4: Query the Snapshot Table
To see the 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;
After the first run, each customer should have one active version.
The active version has:
dbt_valid_to = null
Step 5: Change the Input Data with DML
Now update one customer in the raw table.
Example: Alice changes her email and country.
UPDATE DEMO_DB.RAW.CUSTOMERS
SET
EMAIL = 'alice.new@example.com',
COUNTRY = 'Canada'
WHERE CUSTOMER_ID = 1;
Check the raw table:
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
CREATED_AT
FROM DEMO_DB.RAW.CUSTOMERS
WHERE CUSTOMER_ID = 1;
At this point, the raw table shows only the latest value.
The old values are gone from the raw table.
Step 6: Run the Snapshot Again
Run:
dbt snapshot --select customers_snapshot
dbt compares the current stg_customers data with the previous snapshot version.
Because EMAIL and COUNTRY changed, dbt will:
- Close the old customer record by setting
dbt_valid_to. - Insert a new customer record with the latest values.
Step 7: Query the History
Now query customer 1 again:
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT
WHERE CUSTOMER_ID = 1
ORDER BY dbt_valid_from;
Expected result:
| CUSTOMER_ID | NAME | COUNTRY | dbt_valid_from | dbt_valid_to | |
|---|---|---|---|---|---|
| 1 | Alice Johnson | alice.johnson@example.com | USA | first snapshot time | second snapshot time |
| 1 | Alice Johnson | alice.new@example.com | Canada | second snapshot time | null |
This is the main value of snapshots.
The raw table has only the latest customer row, but the snapshot table has the full history.
Interactive Commands Summary
Use this sequence during training:
# 1. Build the staging customer model
dbt run --select stg_customers
# 2. Run the customer snapshot first time
dbt snapshot --select customers_snapshot
# 3. After updating DEMO_DB.RAW.CUSTOMERS in Snowflake,
# run the snapshot again
dbt snapshot --select customers_snapshot
# 4. Optional: run all snapshots
dbt snapshot
Use these SQL commands in Snowflake:
-- View source customer
SELECT *
FROM DEMO_DB.RAW.CUSTOMERS
WHERE CUSTOMER_ID = 1;
-- Change source customer
UPDATE DEMO_DB.RAW.CUSTOMERS
SET
EMAIL = 'alice.new@example.com',
COUNTRY = 'Canada'
WHERE CUSTOMER_ID = 1;
-- View snapshot history
SELECT
CUSTOMER_ID,
NAME,
EMAIL,
COUNTRY,
dbt_valid_from,
dbt_valid_to
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT
WHERE CUSTOMER_ID = 1
ORDER BY dbt_valid_from;
Querying Current Records Only
To see only the current active customer records:
SELECT *
FROM SNAPSHOTS.CUSTOMERS_SNAPSHOT
WHERE dbt_valid_to IS NULL;
Snapshot Strategies
dbt has two common strategies for detecting changes:
| Strategy | How it detects change | Best for this course |
|---|---|---|
timestamp | Checks whether an updated_at column changed | Use when your source table has a reliable UPDATED_AT |
check | Compares selected column values | Best for our existing RAW.CUSTOMERS demo table |
For beginners:
- Use check strategy for this demo because we already have
NAME,EMAIL, andCOUNTRY. - Use timestamp strategy in real projects when a trustworthy
UPDATED_ATcolumn exists.
Beginner Practice Task
Try this with the existing DEMO_DB.RAW.CUSTOMERS table.
- Create a snapshot file in the
snapshots/folder. - Use
CUSTOMER_IDas theunique_key. - Use
strategy='check'. - Track
NAME,EMAIL, andCOUNTRYincheck_cols. - Run
dbt snapshot --select customers_snapshot. - Update one customer's email or country in
DEMO_DB.RAW.CUSTOMERS. - Run
dbt snapshot --select customers_snapshotagain. - Query the snapshot table and confirm that two versions exist.
Expected result:
- The old row should have a value in
dbt_valid_to. - The latest row should have
dbt_valid_toasnull.
Common Mistakes
| Mistake | Why it is a problem |
|---|---|
unique_key is not unique | dbt cannot correctly match old and new versions |
Using CREATED_AT as if it were UPDATED_AT | dbt may miss changes because creation time does not change |
| Snapshot is not scheduled | Changes are captured only when dbt snapshot runs |
| Tracking too many unnecessary columns | Snapshot table becomes harder to use and maintain |
| Using snapshots for every table | Snapshots are best for important changing dimensions, not all data |
Key Takeaways
Snapshots track historical versions of changing records
They are commonly used for Slowly Changing Dimension Type 2
dbt adds dbt_valid_from and dbt_valid_to columns automatically
Use unique_key to identify the same record across runs
Use check strategy for this demo because RAW.CUSTOMERS has no UPDATED_AT
Use timestamp strategy in production when a reliable UPDATED_AT column exists
Run snapshots regularly so changes are captured over time