Snapshots Keep History

Concepts covered: Snapshots

A table that forgets

billing.subscriptions holds 1 row per subscription, and the billing system updates that row in place. On March 1, subscription 2 reads plan_code basic. At 09:00 on March 2 the user upgrades, the row reads plus, and updated_at moves forward. stg_billing__subscriptions is a view over that table, so it shows plus too, and nothing in the project says basic was ever there. A revenue-by-plan mart built on March 3 reports March 1 as if subscription 2 had been on plus all along. A model can only reflect the source as it stood when the model ran, and a source that overwrites keeps 1 state.

A plain model over the source

• Holds the present state, 1 row per key
• Rebuilt from scratch each run
• Cannot answer what a row said last week

A snapshot of the source

• Holds every version of each row dbt has seen
• Grows each run by the rows that changed
• Answers what a row said at any moment since the first run

How dbt snapshot compares and writes rows

snapshots:
  - name: snap_subscriptions
    relation: source('billing', 'subscriptions')
    config:
      unique_key: subscription_id
      strategy: timestamp
      updated_at: updated_at

dbt snapshot reads the relation, compares it with the snapshot table by unique_key, and writes the difference. On the first run there is no snapshot table, so dbt creates it with every source row plus 4 columns of its own. On every later run, a key whose updated_at is newer than its open snapshot row's gets that row closed and a fresh row inserted; a key with no change is left alone; a key the snapshot has never seen is inserted as an open row. The strategy line names how dbt detects a change, and here it watches the updated_at column.

select
    subscription_id,
    user_id,
    plan_code,
    status,
    dbt_valid_from
from {{ ref('snap_subscriptions') }}
where dbt_valid_to is null

Run 1 writes 3 rows, every one open. Before run 2, subscription 2 moves from basic to plus with updated_at 2026-03-02 09:00:00; run 2 closes the basic row by setting its dbt_valid_to to that timestamp and inserts a plus row, so the table holds 4 rows. Subscriptions 1 and 3 never changed, and each keeps its single open row after both runs.

snap_subscriptions after run 2: 4 rows for 3 subscriptions, and the only closed row is the one that changed.

Reading history back

select
    subscription_id,
    plan_code
from analytics.snap_subscriptions
where dbt_valid_to is null

select
    subscription_id,
    plan_code
from analytics.snap_subscriptions
where dbt_valid_from <= timestamp '2026-03-01 23:59:59'
    and (
        dbt_valid_to > timestamp '2026-03-01 23:59:59'
        or dbt_valid_to is null
    )

The second query returns subscription 2 as basic, because its basic row was valid from February 14 until 09:00 on March 2, and the chosen moment falls inside that span. The first query returns it as plus. Both read the same table; the filter on the 2 validity columns picks which version. A snapshot has more than 1 row per key by design, so any join to it must pick a version first, or section 1's multiplication returns.

In the layers, a snapshot reads the source, so it sits beside staging, and an intermediate model reads it with ref('snap_subscriptions'), the way it would read a staging model. Marts that need history read that intermediate model; marts that need only the present can keep reading stg_billing__subscriptions. dbt snapshot runs only snapshots, and dbt build runs them in DAG order alongside seeds and models, so the snapshot refreshes before anything that refs it.

About this lesson

This section is part of the Models: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.