What is a dbt snapshot?

A table dbt maintains that records every version of a row over time, as slowly changing dimension type 2. Each run compares the source against what is stored and closes off the old version before inserting the new one.

What is the difference between the timestamp and check strategies?

`timestamp` trusts an `updated_at` column and only records a change when it moves, which is cheap and precise. `check` compares the listed columns value by value, which is what you need when the source has no reliable modification timestamp.

Why can't a snapshot be rebuilt like a model?

Because its content is history that no longer exists anywhere else. Once a source row is overwritten, the only record of its previous value is the snapshot — so dropping it destroys data permanently, unlike every other object dbt manages.

Should I snapshot a source or a model?

Snapshot the raw source, as close to the loader as possible, and build models on top of the snapshot. Snapshotting a model means a change to your transformation logic rewrites what you recorded as history.

Snapshots and Slowly Changing Dimensions

Most source tables have no memory. When a customer changes country, the row is updated and the previous value is gone — so a report asking “what did revenue by country look like in March” cannot be answered, even though every number involved is still in the warehouse.

Snapshots are dbt’s answer. Every run, dbt compares the source with what it already recorded and appends a new version when anything changed.

Defining one

-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}

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

select * from {{ source('raw', 'raw_customers') }}

{% endsnapshot %}

dbt snapshot

15:02:19  Running with dbt=1.9.1
15:02:19  Found 7 models, 2 seeds, 1 snapshot, 1 source, 431 macros
15:02:19
15:02:19  Concurrency: 4 threads (target='dev')
15:02:19
15:02:19  1 of 1 START snapshot main_snapshots.customers_snapshot ........ [RUN]
15:02:19  1 of 1 OK snapshotted main_snapshots.customers_snapshot ........ [SUCCESS 5 in 0.11s]
15:02:19
15:02:19  Finished running 1 snapshot in 0 hours 0 minutes and 0.18 seconds (0.18s).
15:02:19
15:02:19  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Five rows recorded. dbt added four columns of its own:

duckdb bookshop.duckdb -c "select id, country, dbt_valid_from, dbt_valid_to from main_snapshots.customers_snapshot order by id"

┌───────┬─────────┬─────────────────────┬──────────────┐
│  id   │ country │   dbt_valid_from    │ dbt_valid_to │
├───────┼─────────┼─────────────────────┼──────────────┤
│     1 │ GB      │ 2026-09-09 15:02:19 │              │
│     2 │ US      │ 2026-09-09 15:02:19 │              │
│     3 │ GB      │ 2026-09-09 15:02:19 │              │
│     4 │ US      │ 2026-09-09 15:02:19 │              │
│     5 │ NL      │ 2026-09-09 15:02:19 │              │
└───────┴─────────┴─────────────────────┴──────────────┘

Column	Meaning
`dbt_valid_from`	when this version became current
`dbt_valid_to`	when it stopped — `null` means it is the current version
`dbt_scd_id`	surrogate key, unique per version
`dbt_updated_at`	the source’s `updated_at` for this version

Capturing a change

Customer 1 moves from GB to NL, and the source’s updated_at advances:

duckdb bookshop.duckdb -c "update main.raw_customers set country='NL', updated_at='2026-09-09 16:00:00' where id=1"
dbt snapshot

15:31:44  1 of 1 START snapshot main_snapshots.customers_snapshot ........ [RUN]
15:31:44  1 of 1 OK snapshotted main_snapshots.customers_snapshot ........ [SUCCESS 1 in 0.09s]
15:31:44
15:31:44  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

“SUCCESS 1” — one row processed, not five. Only the changed row did anything:

┌───────┬─────────┬─────────────────────┬─────────────────────┐
│  id   │ country │   dbt_valid_from    │    dbt_valid_to     │
├───────┼─────────┼─────────────────────┼─────────────────────┤
│     1 │ GB      │ 2026-09-09 15:02:19 │ 2026-09-09 16:00:00 │
│     1 │ NL      │ 2026-09-09 16:00:00 │                     │
│     2 │ US      │ 2026-09-09 15:02:19 │                     │
│     3 │ GB      │ 2026-09-09 15:02:19 │                     │
│     4 │ US      │ 2026-09-09 15:02:19 │                     │
│     5 │ NL      │ 2026-09-09 15:02:19 │                     │
└───────┴─────────┴─────────────────────┴─────────────────────┘

Two rows for customer 1. The old version was closed at the moment the new one opened, so the intervals abut exactly with no gap and no overlap — that property is what makes the point-in-time join below correct.

Querying history

Current state only:

-- models/staging/stg_customers.sql
select
    id as customer_id,
    country as country_code
from {{ ref('customers_snapshot') }}
where dbt_valid_to is null

15:38:02  1 of 1 OK created sql view model main.stg_customers ............ [OK in 0.04s]

As it was at a point in time — the join that snapshots exist for:

-- models/marts/revenue_by_country_historic.sql
select
    o.ordered_at,
    c.country       as country_at_order_time,
    sum(o.amount)   as revenue
from {{ ref('stg_orders') }} as o
inner join {{ ref('customers_snapshot') }} as c
    on c.id = o.customer_id
   and o.ordered_at >= c.dbt_valid_from
   and o.ordered_at <  coalesce(c.dbt_valid_to, '9999-12-31'::timestamp)
group by 1, 2

┌────────────┬───────────────────────┬─────────┐
│ ordered_at │ country_at_order_time │ revenue │
├────────────┼───────────────────────┼─────────┤
│ 2026-01-04 │ GB                    │    25.5 │
│ 2026-01-05 │ US                    │    12.0 │
│ 2026-01-07 │ GB                    │    40.0 │
│ 2026-01-09 │ GB                    │    8.75 │
└────────────┴───────────────────────┴─────────┘

January orders are still attributed to GB even though the customer lives in NL today. Joining to the source table instead would restate history every time someone moves — which is how a number that was signed off last quarter changes on its own.

The coalesce on dbt_valid_to matters: without it the current version has a null upper bound, the comparison is null, and every recent row silently drops out of the join.

When there is no updated_at

Plenty of sources do not maintain a modification timestamp. Compare columns instead:

{{
    config(
        target_schema='snapshots',
        unique_key='id',
        strategy='check',
        check_cols=['country', 'email', 'plan']
    )
}}

15:44:31  1 of 1 START snapshot main_snapshots.customers_snapshot ........ [RUN]
15:44:31  1 of 1 OK snapshotted main_snapshots.customers_snapshot ........ [SUCCESS 5 in 0.13s]

check_cols=['country', 'email', 'plan'] watches those three. check_cols='all' watches every column, which is thorough and noisy — one meaningless last_seen_at update creates a new version of every row on every run.

The strategies differ in a way worth knowing: timestamp records the change at the source’s own timestamp, while check can only record it at the moment dbt noticed. Run a check snapshot daily and your history has a one-day resolution at best.

Deletions

By default a row that disappears from the source stays open forever in the snapshot, so a deleted customer looks permanently current.

{{
    config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at',
        hard_deletes='new_record'
    )
}}

15:51:07  1 of 1 OK snapshotted main_snapshots.customers_snapshot ........ [SUCCESS 1 in 0.10s]

┌───────┬─────────┬─────────────────────┬─────────────────────┬───────────────┐
│  id   │ country │   dbt_valid_from    │    dbt_valid_to     │ dbt_is_deleted│
├───────┼─────────┼─────────────────────┼─────────────────────┼───────────────┤
│     5 │ NL      │ 2026-09-09 15:02:19 │ 2026-09-09 15:51:07 │ False         │
│     5 │ NL      │ 2026-09-09 15:51:07 │                     │ True          │
└───────┴─────────┴─────────────────────┴─────────────────────┴───────────────┘

The older invalidate_hard_deletes=true config closes the row instead of adding a deletion record; hard_deletes='new_record' is the dbt 1.9+ form and keeps the deletion itself visible as an event.

Rules that keep snapshots trustworthy

Snapshot the source, not a model. select * from {{ source(...) }} and nothing else — no filters, no joins, no renames. If a transformation sits between the source and the snapshot, changing that transformation rewrites your record of history.

Never drop the table. A snapshot is the only copy of what the source used to say. dbt run --full-refresh does not touch snapshots, and that is deliberate — dbt build runs them in dependency order without ever rebuilding them:

15:58:22  1 of 16 START seed file main.raw_customers ..................... [RUN]
15:58:22  2 of 16 START snapshot main_snapshots.customers_snapshot ....... [RUN]
15:58:22  2 of 16 OK snapshotted main_snapshots.customers_snapshot ...... [SUCCESS 0 in 0.09s]
15:58:22  3 of 16 START sql view model main.stg_customers ............... [RUN]

Run them often enough. A snapshot only records what it observed. Between two runs a row can change twice and you will record one of those values — snapshot frequency is the resolution of your history, and it cannot be improved retroactively.

Practice

1. Snapshot the orders table and change a status.

duckdb bookshop.duckdb -c "update main.raw_orders set status='returned', updated_at=now() where id=1002"
dbt snapshot --select orders_snapshot

16:04:11  1 of 1 OK snapshotted main_snapshots.orders_snapshot ........... [SUCCESS 1 in 0.09s]

┌──────┬───────────┬─────────────────────┬─────────────────────┐
│  id  │  status   │   dbt_valid_from    │    dbt_valid_to     │
├──────┼───────────┼─────────────────────┼─────────────────────┤
│ 1002 │ completed │ 2026-09-09 15:02:19 │ 2026-09-09 16:04:11 │
│ 1002 │ returned  │ 2026-09-09 16:04:11 │                     │
└──────┴───────────┴─────────────────────┴─────────────────────┘

Order status history is one of the most valuable things to snapshot — it is what lets you measure how long orders sit in each state, which the source table can never answer.

2. Query the snapshot for current rows only.

select * from {{ ref('customers_snapshot') }} where dbt_valid_to is null

┌───────┬─────────┐
│  id   │ country │
├───────┼─────────┤
│     1 │ NL      │
│     2 │ US      │
│     3 │ GB      │
│     4 │ US      │
└───────┴─────────┘

where dbt_valid_to is null is the filter that turns an SCD2 table back into a normal dimension. Forgetting it means every historical version joins in and row counts multiply.

3. Switch to the check strategy and change a column that is not in check_cols.

16:11:40  1 of 1 OK snapshotted main_snapshots.customers_snapshot ........ [SUCCESS 0 in 0.08s]

“SUCCESS 0” — nothing recorded. The change is invisible to the snapshot and always will be, since the previous value is now gone from the source. Choosing check_cols is choosing what history you are able to keep.

4. Run dbt snapshot twice with no source change.

16:14:02  1 of 1 OK snapshotted main_snapshots.customers_snapshot ........ [SUCCESS 0 in 0.07s]

Zero rows, no duplicates. Snapshots are idempotent — running them more often than the data changes costs a comparison query and nothing else, which is why an hourly schedule is a reasonable default for a table that changes daily.

Next: documentation and lineage — turning the project into something other people can read.