Skip to main content

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 pending status before it became shipped?

Snapshots help us answer these questions by storing different versions of the same record.


Simple Beginner Example​

Imagine this source table:

customer_idcustomer_nameplan_nameupdated_at
101AnanyaFree2026-01-01 09:00:00

Later, the same customer upgrades the plan:

customer_idcustomer_nameplan_nameupdated_at
101AnanyaPremium2026-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_idcustomer_nameplan_namedbt_valid_fromdbt_valid_to
101AnanyaFree2026-01-01 09:00:002026-02-10 14:30:00
101AnanyaPremium2026-02-10 14:30:00null

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:

  • view
  • table
  • incremental
  • ephemeral

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.

FeatureIncremental ModelSnapshot
Main purposeBuild or update a model efficientlyPreserve historical versions of records
Keeps old versions?Usually noYes
Common commanddbt rundbt snapshot
Config locationInside a model .sql fileInside a snapshot .sql file
Typical outputFinal analytics tableHistory table with validity dates
Example use caseLoad only new sales transactionsTrack 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: Free and Premium

So, for historical tracking, snapshots are the correct dbt feature.


How Snapshots Work​

When you run:

dbt snapshot

dbt performs these steps:

  1. Reads the current data from the source query.
  2. Compares each row with the previous snapshot version.
  3. Uses a unique_key to identify the same business record.
  4. Detects whether important values changed.
  5. Closes the old version by setting dbt_valid_to.
  6. 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​

TermMeaning
unique_keyColumn that uniquely identifies one business record, such as customer_id
strategyMethod used by dbt to detect changes
updated_atTimestamp column that tells when the source row was last updated
check_colsList of columns dbt should compare for changes
dbt_valid_fromWhen this version became active
dbt_valid_toWhen 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.CUSTOMERS
  • models/bronze/stg_customers.sql
  • models/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_IDNAMEEMAILCOUNTRYCREATED_AT
1Alice Johnsonalice.johnson@example.comUSA2025-11-12 08:45:00
2Carlos Riveracarlos.rivera@example.comMexico2025-11-13 14:20:00
3Mei Wongmei.wong@example.comSingapore2025-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​

CodeExplanation
{% 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_AT tells when the customer was first created.
  • It does not prove when EMAIL or COUNTRY changed.
  • So check_cols is 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:

  1. Close the old customer record by setting dbt_valid_to.
  2. 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_IDNAMEEMAILCOUNTRYdbt_valid_fromdbt_valid_to
1Alice Johnsonalice.johnson@example.comUSAfirst snapshot timesecond snapshot time
1Alice Johnsonalice.new@example.comCanadasecond snapshot timenull

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:

StrategyHow it detects changeBest for this course
timestampChecks whether an updated_at column changedUse when your source table has a reliable UPDATED_AT
checkCompares selected column valuesBest for our existing RAW.CUSTOMERS demo table

For beginners:

  • Use check strategy for this demo because we already have NAME, EMAIL, and COUNTRY.
  • Use timestamp strategy in real projects when a trustworthy UPDATED_AT column exists.

Beginner Practice Task​

Try this with the existing DEMO_DB.RAW.CUSTOMERS table.

  1. Create a snapshot file in the snapshots/ folder.
  2. Use CUSTOMER_ID as the unique_key.
  3. Use strategy='check'.
  4. Track NAME, EMAIL, and COUNTRY in check_cols.
  5. Run dbt snapshot --select customers_snapshot.
  6. Update one customer's email or country in DEMO_DB.RAW.CUSTOMERS.
  7. Run dbt snapshot --select customers_snapshot again.
  8. 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_to as null.

Common Mistakes​

MistakeWhy it is a problem
unique_key is not uniquedbt cannot correctly match old and new versions
Using CREATED_AT as if it were UPDATED_ATdbt may miss changes because creation time does not change
Snapshot is not scheduledChanges are captured only when dbt snapshot runs
Tracking too many unnecessary columnsSnapshot table becomes harder to use and maintain
Using snapshots for every tableSnapshots 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