Snapshots

Analogy categoryAnalogies

What Snapshots Solve: Slowly Changing Dimensions

Source systems typically only store the current state of a row — if a customer's subscription plan changes from 'basic' to 'premium', the source table simply overwrites the plan value, losing all history of what it used to be. A dbt snapshot solves this by periodically checking a source table for changes and, when it detects one, inserting a new row into a persistent snapshot table rather than overwriting, while marking the old row as expired. This is a Type 2 slowly changing dimension (SCD Type 2) pattern: every version of a row is preserved with valid-from and valid-to timestamps, letting you answer questions like 'what plan was this customer on last quarter' even though the source system never stored that.

🏏

Cricket analogy: This is like a scorecard system that, instead of overwriting a player's team affiliation when they're transferred mid-season, keeps a historical record showing they played for Mumbai Indians until a certain date and Chennai Super Kings after, so old match reports stay accurate.

Configuring a Snapshot: Strategies and Metadata Columns

Snapshots live in the snapshots/ directory as .sql files using a snapshot block, configured with a target_schema, a unique_key, and a change-detection strategy. The timestamp strategy compares an updated_at column against what's already recorded, flagging a change when the source's updated_at is newer. The check strategy, used when no reliable updated_at exists, instead compares a specified list of columns (or all columns via check_cols='all') row-by-row for any difference. When dbt snapshot runs and detects a change, it automatically adds dbt_valid_from and dbt_valid_to columns — the currently active version of a row always has dbt_valid_to as NULL.

🏏

Cricket analogy: This is like an umpire's review system using ball-tracking timestamps (timestamp strategy) when available, but falling back to comparing multiple camera angles frame-by-frame (check strategy) when no reliable single timestamp exists to settle a close run-out call.

sql

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

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

select
    subscription_id,
    customer_id,
    plan_name,
    status,
    updated_at
from {{ source('billing', 'subscriptions') }}

{% endsnapshot %}

Running Snapshots and Querying History

Snapshots are executed with the dbt snapshot command, separately from dbt run, and are typically scheduled to run before or alongside your regular model runs so the history capture stays up to date. Once populated, querying a snapshot table for the current state of every entity is a simple filter on dbt_valid_to is null; querying the state as of a specific past date uses a range filter on dbt_valid_from and dbt_valid_to bracketing that date, which is exactly how you'd build a point-in-time report like 'what was our MRR by plan tier at the end of last quarter.'

🏏

Cricket analogy: This is like a cricket board running its official records-update process (dbt snapshot) as a separate task from publishing today's match report (dbt run), then later querying 'who held the number one ODI ranking on this exact date last year' from the preserved history.

Snapshots must be run separately with dbt snapshot — they are not built by dbt run. A common pattern is dbt snapshot && dbt run (or two separate scheduled jobs) so history is captured before the rest of the DAG builds on top of it.

Never change a snapshot's strategy, unique_key, or column selection after it's already been running in production — doing so can silently break change detection or duplicate rows, since dbt compares against history that was captured under the old configuration. If you must change it, treat it as a new snapshot and migrate history deliberately.

Snapshots implement Type 2 slowly changing dimensions, preserving every historical version of a row instead of overwriting it.
The timestamp strategy compares an updated_at column; the check strategy compares specified columns row-by-row.
dbt automatically adds dbt_valid_from and dbt_valid_to; the current version of a row has dbt_valid_to is null.
Snapshots run via the separate dbt snapshot command, not dbt run.
Point-in-time queries filter dbt_valid_from/dbt_valid_to to bracket the date of interest.
Never change a snapshot's strategy or unique_key after it's live in production without deliberate migration.
Snapshots are essential when the source system only stores current state and history isn't otherwise recoverable.