dbt Snapshots for Slowly Changing Dimensions

A normal dbt model overwrites its own history every time it runs — query it today and you see today's version of the row, with no record of what it looked like last month. Snapshots exist for the opposite need: when a row's previous values matter, not just its current ones. That is a slowly changing dimension, and dbt snapshots are the built-in way to track it.

Why you need history at all

If a customer's plan_tier changes from "starter" to "enterprise," a normal model just shows "enterprise" — every historical order now looks like it was placed by an enterprise customer, even the ones placed while they were on starter. Attribution, cohort analysis, and any "what did this look like at the time" question needs the actual value at each point in time, which means keeping every version of the row instead of overwriting it.

Defining a snapshot

Snapshots live in the snapshots/ directory, use a {% snapshot %} block instead of a plain SELECT, and require a strategy for detecting change — either timestamp (compare an updated_at column) or check (compare a list of columns directly, useful when the source has no reliable timestamp).

{% snapshot customers_snapshot %}

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

select customer_id, plan_tier, email, updated_at
from {{ source('app', 'customers') }}

{% endsnapshot %}

Running dbt snapshot compares the current source rows to what is already in the snapshot table. Unchanged rows are left alone; changed rows get their old version closed out and a new version inserted — this is the classic SCD Type 2 pattern, and dbt manages the bookkeeping for you.

What dbt adds automatically to every snapshot table

Four extra columns appear in every snapshot regardless of strategy: dbt_scd_id (a unique hash for the row version), dbt_updated_at, dbt_valid_from, and dbt_valid_to. A NULL dbt_valid_to means that row version is currently active; querying "what was the plan_tier for this customer on March 1st" becomes a straightforward range filter on those two columns.

select customer_id, plan_tier
from {{ ref('customers_snapshot') }}
where '2026-03-01' >= dbt_valid_from
  and ('2026-03-01' < dbt_valid_to or dbt_valid_to is null)

Choosing timestamp vs. check strategy

Use timestamp whenever the source reliably updates an updated_at column on every change — it is cheaper to compute and less prone to false positives. Use check (with an explicit check_cols list, or check_cols='all') when the source has no trustworthy timestamp, common with legacy systems or CSV drops. `check_cols='all'` is convenient but means any column addition upstream — even one you do not care about — triggers a new snapshot row, so an explicit column list is usually safer.

Wrapping up

Reach for a snapshot specifically when you need to answer "what did this look like at time X," not just "what does it look like now" — anything else is over-engineering a plain model. Point snapshots at raw sources, run them on a schedule tight enough to catch every real change, and query the `dbt_valid_from`/`dbt_valid_to` columns for point-in-time analysis once the history exists.