dbt Snapshots: Tracking Slowly Changing Dimensions Over Time

Most source tables only show you the current state of the world — a customer’s row shows their address right now, with no memory of what it was six months ago. That’s fine until someone asks “which region was this customer in when they placed order #4821,” and the honest answer is that the data needed to answer that question was overwritten the moment the customer updated their address. dbt snapshots solve this by capturing point-in-time history of a table’s changes, implementing what’s classically called a Type 2 Slowly Changing Dimension.

The Problem Snapshots Solve

Consider a customers source table that gets overwritten in place whenever a customer updates their profile:

Day 1: customer_id=42, region='West', updated_at='2026-01-01'
Day 45: customer_id=42, region='East', updated_at='2026-02-15'   ← same row, overwritten

Without snapshots, only the current state (region='East') is ever visible. Any historical analysis — “what percentage of West-region orders came from customers now in the East region” — is simply unanswerable, because the history was destroyed the moment the row was updated.

How a Snapshot Works

A snapshot is defined as a SQL select statement, run on a schedule, that dbt compares against the last captured version of each row. If a row has changed, dbt closes out the old version and inserts a new one — never deleting history.

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

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

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

{% endsnapshot %}

dbt snapshot

Each run compares the source table’s current state against what’s already captured, and the resulting snapshots.customers_snapshot table gains four extra columns dbt manages automatically:

Querying Historical State

Once snapshotted, answering “what was true at a point in time” becomes a straightforward filter:

select *
from {{ ref('customers_snapshot') }}
where customer_id = 42
  and '2026-02-01' between dbt_valid_from and coalesce(dbt_valid_to, current_timestamp)

This returns exactly the version of customer 42’s row that was current on February 1st — region='West', in the earlier example — even though the source table itself has long since been overwritten with the current East-region value.

Two Snapshot Strategies: timestamp vs check

Timestamp strategy (shown above) relies on a reliable updated_at column that changes whenever the source row changes. It’s the preferred strategy whenever such a column exists and is trustworthy.

Check strategy compares specific columns directly, useful when there’s no reliable updated-at timestamp to lean on.

{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='check',
    check_cols=['region', 'email', 'plan_tier'],
  )
}}

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

Here, dbt compares the listed columns between runs and creates a new snapshot version only if one of them actually changed — useful when a source table’s updated_at gets bumped on every sync regardless of whether meaningful data changed, which would otherwise create a new snapshot version on every single run even when nothing relevant actually changed.

Snapshot Frequency and Granularity

Snapshots only capture the state that existed at the time the snapshot ran — if a customer’s region changes twice between two scheduled snapshot runs, only the second change is captured; the intermediate state is lost forever. This means snapshot frequency needs to match how granular your historical analysis actually needs to be.

# Typical production cadence — run before the main model build
dbt snapshot
dbt run

A snapshot run every hour captures much finer-grained history than one run nightly — the tradeoff is purely about how much historical resolution your analysis actually requires versus the compute cost of running snapshots more frequently.

Referencing Snapshots From Models

Once created, a snapshot is referenced with ref() exactly like any other model — the fact that it’s a snapshot rather than a regular model is invisible to the calling SQL.

select
  o.order_id,
  o.order_date,
  s.region as customer_region_at_order_time
from {{ ref('stg_orders') }} as o
left join {{ ref('customers_snapshot') }} as s
  on o.customer_id = s.customer_id
  and o.order_date between s.dbt_valid_from and coalesce(s.dbt_valid_to, current_timestamp)

This join correctly attributes each historical order to the customer’s region as it was at the time of that order, not their current region — the entire point of capturing history in the first place.

Common Mistakes

Choosing timestamp strategy without verifying the updated_at column is trustworthy. If the source’s updated_at doesn’t actually change reliably whenever a row changes, the timestamp strategy will silently miss updates — check strategy is the safer fallback in that case.

Snapshotting too infrequently for the analysis that’s needed. If stakeholders need to reconstruct daily-granularity history but snapshots only run weekly, most of that history is simply unrecoverable — there’s no way to snapshot retroactively.

Forgetting that deleted source rows aren’t automatically marked invalid without the invalidate_hard_deletes config — without it, a row deleted from the source can appear to remain “current” in the snapshot forever.

{{
  config(
    ...,
    invalidate_hard_deletes=True,
  )
}}

Summary

Snapshots are the mechanism that lets a warehouse answer “what was true then,” not just “what’s true now” — essential for any analysis that depends on historical accuracy rather than the current, overwritten state of mutable source data.