dbt Snapshots

A snapshot captures the historical state of a database record over time. Without snapshots, your data warehouse only shows the current value of each row. When a customer changes their email address or an order's status changes from "pending" to "shipped," the old value disappears. Snapshots preserve every version of a row so you can answer questions like "what was this customer's address last month?" or "which orders were pending yesterday?"

The Problem Snapshots Solve

Imagine your CRM stores customer records. Today customer 42 lives in New York. Next month they move to Chicago. Your raw table gets updated:

Raw customers table (today):
  customer_id | name  | city
  42          | Alice | Chicago    ← old New York record is gone

Without snapshots: You cannot answer "Where did Alice live in January?"
With snapshots:    You can see the full history of changes

How dbt Snapshots Work

On the first run, dbt copies the full source table into the snapshot table and adds metadata columns. On every subsequent run, dbt compares the current source data to the snapshot and records any changed rows.

First run (Jan 1):
  customer_id | city     | dbt_valid_from | dbt_valid_to
  42          | New York | 2025-01-01     | NULL          ← current record

Second run (Feb 15, after move):
  customer_id | city     | dbt_valid_from | dbt_valid_to
  42          | New York | 2025-01-01     | 2025-02-15    ← expired record
  42          | Chicago  | 2025-02-15     | NULL          ← current record

The dbt_valid_to = NULL always marks the current active record. Records with a non-null dbt_valid_to are historical versions.

Creating a Snapshot File

Snapshot files live in the snapshots/ folder and use a {% snapshot %} Jinja block:

-- snapshots/customers_snapshot.sql

{% snapshot customers_snapshot %}

{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at'
  )
}}

select
    customer_id,
    name,
    email,
    city,
    country,
    updated_at
from {{ source('crm', 'customers') }}

{% endsnapshot %}

Configuration Options Explained

target_schema

The schema where dbt writes the snapshot table. Using a dedicated snapshots schema keeps snapshot tables separate from regular models.

unique_key

The column that uniquely identifies each entity. dbt uses this to detect which rows have changed. For customers it is customer_id. For orders it might be order_id.

strategy

dbt supports two strategies to detect changes:

Strategy        How It Detects Changes
---------       ----------------------
timestamp       Compares the updated_at column to the last snapshot run time
check           Compares the actual values of specified columns

Timestamp Strategy

Use the timestamp strategy when your source table has an updated_at column that the source system updates every time a row changes.

config(
    strategy='timestamp',
    updated_at='updated_at'
)

dbt looks at rows where updated_at is newer than the last snapshot run and inserts new version rows for those records.

Check Strategy

Use the check strategy when your source table does not have an updated_at column. dbt compares the current values of specific columns to the snapshot table to detect changes.

config(
    strategy='check',
    check_cols=['city', 'country', 'email']
)

If any of the listed columns have a different value since the last snapshot run, dbt creates a new historical record. You can also use check_cols='all' to compare every column.

Running Snapshots

# Run all snapshots
dbt snapshot

# Run a specific snapshot
dbt snapshot --select customers_snapshot

Run snapshots on a schedule that matches how often you want to capture changes. Daily snapshots capture daily changes. Hourly snapshots capture hourly changes.

Metadata Columns dbt Adds

dbt automatically adds these columns to every snapshot table:

Column              Content
-----------------   -------
dbt_scd_id          Unique identifier for each snapshot row (hash)
dbt_updated_at      When dbt last updated this row in the snapshot
dbt_valid_from      When this version of the record became active
dbt_valid_to        When this version expired (NULL = currently active)

Querying Snapshot Data

Get the current record for each customer

select *
from snapshots.customers_snapshot
where dbt_valid_to is null

Get what a customer's record looked like on a specific date

select *
from snapshots.customers_snapshot
where customer_id = 42
  and '2025-01-15' between dbt_valid_from and coalesce(dbt_valid_to, current_date)

Count how many times each customer changed their city

select
    customer_id,
    count(*) - 1   as number_of_city_changes
from snapshots.customers_snapshot
group by customer_id
having count(*) > 1

Snapshot in the DAG

[source: crm.customers]
         |
         v
[snapshot: customers_snapshot]   ← runs via dbt snapshot
         |
         v
[model: dim_customers]           ← reads from snapshot using ref()

Downstream models can reference snapshot tables using ref('customers_snapshot') just like any other model.

SCD Type 2

dbt snapshots implement the data warehousing pattern called Slowly Changing Dimension Type 2 (SCD2). This pattern stores full history by adding new rows for each change rather than overwriting old data. It is the most common approach for tracking customer, product, and account history in analytical databases.

Best Practices

• Run snapshots before models in your pipeline so downstream models use up-to-date history
• Use a dedicated snapshots schema to keep snapshot tables organized
• Use the timestamp strategy when updated_at exists — it is faster than the check strategy
• Add freshness monitoring to source tables feeding snapshots to catch pipeline failures early
• Do not add columns to a snapshot source without running dbt snapshot --full-refresh to rebuild