dbt Snapshots: dbt snapshot Playbook (dbt developer hub)

If you need reliable SCD type 2 history without hand-rolled MERGEs, use a dbt snapshot. With dbt snapshots you define a select on a source table, choose a timestamp strategy or a check strategy, and dbt manages inserts/updates so you can track changes as rows change over time. This article explains how snapshots work in dbt, how to configure them in a snapshot file, how to choose a strategy, the gotchas (delete handling, late data, performance), and how to test. For theory on slowly changing dimensions, see Slowly Changing Dimensions Type 2 Explained; we stay implementation-first here.

What are snapshots in dbt?

A snapshot in dbt is code that captures a point-in-time record of rows from a source table and maintains historical data as those rows change over time. In short, a dbt snapshot materializes a snapshot table with validity windows so you can query as-of states and analyze change over time.

High level, dbt snapshots work by:

• Materializing a snapshot table with SCD columns (dbt_valid_from, dbt_valid_to, dbt_scd_id).
• Comparing current rows from your query to the latest open version for each unique_key.
• Inserting a new version when a change is detected, and closing the prior version.

This is how dbt snapshots work across adapters—no warehouse-specific sql MERGE logic from you.

Quickstart: create a snapshot

Place a snapshot file at snapshots/orders.sql in your dbt project, then run dbt snapshot. This minimal snapshot configuration is production-ready.

{% snapshot orders_timestamp_scd %}

{{
  config(
    target_schema='snapshots',            -- where the snapshot table lives
    unique_key='order_id',               -- stable business key
    strategy='timestamp',                -- timestamp strategy
    updated_at='updated_at',             -- column on source table
    invalidate_hard_deletes=True         -- close records if the source row disappears
  )
}}

-- Select only the columns you need to version
select
  o.order_id,
  o.customer_id,
  o.status,
  o.total_amount,
  o.updated_at
from {{ source('app', 'orders') }} o

{% endsnapshot %}

Or use the check strategy to detect row changes by column comparison or hash:

{% snapshot orders_check_scd %}

{{
  config(
    target_schema='snapshots',
    unique_key='order_id',
    strategy='check',                    -- check strategy
    check_cols=['status', 'total_amount']
  )
}}

select
  o.order_id,
  o.customer_id,
  o.status,
  o.total_amount,
  o.updated_at
from {{ source('app', 'orders') }} o

{% endsnapshot %}

After the first run, dbt will maintain dbt_valid_from and dbt_valid_to to represent the validity window for each version. The snapshot configuration above is clear, minimal, and safe for production.

How snapshots work under the hood

On each run, dbt executes your select query, compares current rows to the open versions in the target snapshot, and performs adapter-specific MERGE/INSERTs. dbt uses the unique_key to match logical rows. With the timestamp strategy, dbt reads updated_at to decide if a row is newer. With the check strategy, dbt compares the configured columns or a computed hash to decide if anything changed. dbt uses these rules to insert a new version and close the previous one with an end timestamp. This is how a snapshot in dbt abstracts cross-warehouse behavior without you writing warehouse-specific sql.

If you want the formal reference of how dbt snapshots work and every parameter, the dbt developer hub is canonical.

Choosing a strategy: timestamp vs check

Rule of thumb: prefer the timestamp strategy when available; use the check strategy when you must control exactly which attributes constitute a change.

Snapshot configuration options you actually need

Core options you’ll set repeatedly:

• unique_key: Stable business key. Never change it after go-live.
• strategy: timestamp or check.
• updated_at: Required for timestamp strategy. Must be non-null and monotonic.
• check_cols: Array of columns or 'all'. Avoid volatile fields (e.g., updated_at itself, load times).
• invalidate_hard_deletes: True to close versions when a row disappears from the source table (see delete handling below).
• target_schema: Keep snapshots in their own schema for clarity and permissions.
• pre-hook / post-hook: Run setup/teardown sql around the snapshot transaction if you need audit stamps or session settings.

Other knobs exist, but these carry 95% of the value. For full reference, the dbt developer hub details every parameter. dbt provides two built-in strategies and consistent semantics across adapters; dbt uses adapter-specific DML to implement them.

Config examples you can lift

{% snapshot subscriptions_scd %}
{{
  config(
    target_schema='snapshots',
    unique_key='subscription_id',
    strategy='check',
    check_cols=['plan_id','is_active','price_cents'],
    invalidate_hard_deletes=True,
    tags=['history'],
    post_hook=["insert into ops.audit_log(event, at) values ('snapshot subscriptions', current_timestamp)"]
  )
}}
select * from {{ ref('stg_subscriptions') }}
{% endsnapshot %}

Detecting row changes reliably

Reliable change detection starts with the right inputs:

• Timestamp strategy: The updated_at column must reflect real business changes. If upstream runs backfills, ensure they also update updated_at or you’ll miss changes.
• Check strategy: Limit to semantic attributes. If a column is noisy (e.g., last_viewed_at), exclude it. Consider hashing for wide rows: check_cols=["md5(concat_ws(':', col1,col2,col3))"].
• Type consistency: Normalize datatypes so equality comparisons are stable (cast numerics, trim strings).
• Timezone: Align to UTC and normalize timestamps before comparisons to avoid spurious changes when timestamps cross DST boundaries.
• Don’t mutate definitions: If you ever change which columns you compare or switch strategies, treat it as a breaking change. Create a new snapshot instead of altering an existing one.

In both strategies, you’re asking dbt to detect meaningful differences and track changes with correct validity windows. Be explicit about the business signal.

Deletes, soft-deletes, and late-arriving data

• Hard delete handling: With invalidate_hard_deletes=True, when a row disappears from the current query, dbt closes the open version by setting dbt_valid_to. No tombstone row is added.
• Soft-deletes: If the source marks records deleted (e.g., is_deleted), include that column and its transitions will be captured as normal versions.
• Late-arriving updates: If downstream writes backfill older values with a new updated_at, the snapshot records a version at run time, not the historical effective date—unless you project a business-effective date in your select and use it downstream.

Performance playbook for big sources (e.g., 40M+ orders)

Snapshots are efficient, but poorly scoped inputs can be expensive. Practical tips:

• Project only necessary columns: Include the unique key, the change-detection columns, and attributes you actually need in history. Extra columns slow comparisons.
• Filter when possible: If your source table has cold, immutable partitions, filter them out. Example: only snapshot the last 3 years when you know older orders never change.
• Cluster/partition the snapshot table: On warehouses that support it, cluster by unique key and partition by dbt_valid_from to speed both maintenance and downstream analytics.
• Schedule cadence: Don’t run every minute if the source only mutates hourly. Align frequency to upstream writes to reduce cost.
• Stable hashing (check strategy): Build a deterministic hash expression that ignores nullability quirks and formatting differences.
• Push filters upstream: If you’re wrapping a staging model, do the reductions there so the snapshot’s sql is lean and easy to optimize.

Testing snapshots

Two kinds of tests matter: structural and logical. For structure, lean on dbt Tests:

-- snapshots/schema.yml
version: 2
snapshots:
  - name: orders_timestamp_scd
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [order_id, dbt_valid_from]
      - not_null:
          column_name: order_id

For logic, assert no overlapping validity windows and that only one open version exists per key:

-- tests/no_overlaps_orders.sql
select order_id
from {{ ref('orders_timestamp_scd') }}
group by order_id
having count_if(dbt_valid_to is null) > 1
   or sum(
        case when dbt_valid_to is null then 0 else 
          case when lead(dbt_valid_from) over (partition by order_id order by dbt_valid_from) < dbt_valid_to then 1 else 0 end
        end
      ) > 0

Also assert that the latest snapshot row matches the current source:

-- tests/snapshot_matches_current_orders.sql
with cur as (
  select order_id, status, total_amount
  from {{ source('app','orders') }}
),
last as (
  select order_id, status, total_amount
  from {{ ref('orders_timestamp_scd') }}
  qualify row_number() over (partition by order_id order by dbt_valid_from desc) = 1
)
select l.order_id
from last l
full join cur c using (order_id, status, total_amount)
where c.order_id is null or l.order_id is null

Keep snapshot tests near the snapshot so future refactors don’t break assumptions silently. If you want a deeper tour of testing patterns and strategies, see our complete dbt tests guide.

Workflow: running, building, and selecting

• What are snapshots in dbt? They are versioned tables driven by your select that record how rows change over time. See the examples above.
• Build vs snapshot: dbt build runs models, tests, seeds, and snapshots in dependency order. If you only want snapshots, run dbt snapshot.
• Run one snapshot: dbt snapshot --select orders_timestamp_scd (or select by tag/path).
• How often to run: Match the update frequency of the source table and downstream SLAs. Hourly is common; daily can be fine for dim-like data. Add snapshots to your dag before models that depend on them.
• Selection tips: Use --select path:snapshots/orders.sql, --select tag:history, or the node name to run a single dbt snapshot.

Storage, naming, and organization

• Schema strategy: Use a dedicated target_schema for all snapshots (e.g., snapshots) so permissions and retention are clear.
• Naming: Prefix with the domain and strategy, e.g., orders_timestamp_scd or subscriptions_check_scd so intent is obvious.
• Directory layout: By default, dbt looks in the snapshots directory. You can store snapshots in another directory by setting snapshot-paths in dbt_project.yml.
• Snapshot table location: Control it with target_schema (and target_database if supported) per snapshot or via a package-level config block.

Real-world gotchas (and fixes)

• Volatile check columns: Including transient columns (e.g., pageview counters) in a check strategy will explode history. Limit to business attributes you truly need to track changes.
• updated_at isn’t reliable: If upstream fails to move it on true changes, you’ll miss versions. Switch to check strategy or request upstream fixes.
• Warehouse-specific null semantics: Equality on nulls behaves differently across engines. Normalize with coalesce and casting in your select.
• Changing unique_key: Don’t. Treat it as immutable. If business keys change, create a new snapshot (new name) and deprecate the old one.
• Timezones: Standardize to UTC for timestamps and comparisons.
• Deletes not captured: If rows can be removed upstream, ensure invalidate_hard_deletes=True is set; otherwise, open versions stay open forever.

FAQ

What is the difference between snapshot and model in dbt?

A model transforms data into a current-state table or view. A dbt snapshot manages versioned history for rows from a select statement. You’ll often feed a dimension model from a snapshot to get as-of analysis.

Does dbt build include snapshots?

Yes. dbt build includes seeds, models, tests, and snapshots in the correct order.

What is dbt and why is it used?

dbt is the standard framework for version-controlled transformations, testing, and documentation. It brings software practices to analytics engineering with code-reviewed sql and declarative DAGs. Explore our dbt topic hub and the dbt tutorial for a fast start.

Can I store my snapshots in a directory other than the snapshot directory in my project?

Yes. Set snapshot-paths in dbt_project.yml to point to a different directory:

snapshot-paths:
  - data_history

Could you explain why you are doing a slow changing dimension type 2 (the functionality of a snapshot) on a fact table?

Sometimes facts are mutable (e.g., order status, refund amount, subscription state). If downstream metrics need the full lifecycle, a snapshot on that fact stabilizes history. Beware row explosion: filter to attributes that change meaningfully, and consider summarizing changes in downstream dbt models.

Do hooks run with snapshots?

Yes. pre-hook and post-hook run for snapshots. Keep them idempotent and lightweight; they execute within the snapshot run’s transaction scope on most adapters.

Do you have any dbt tutorial recommendations?

Start with our hands-on dbt Tutorial for Beginners, then level up with dbt Macros & Jinja Tips and compare runtimes in dbt Cloud vs Core.

How do I run one snapshot at a time?

Use selection syntax: dbt snapshot --select path:snapshots/orders.sql, --select tag:history, or --select orders_timestamp_scd.

How often should I run the snapshot command?

Align to upstream mutation frequency and reporting needs. If the source table updates hourly, snapshot hourly; if it changes only after nightly batch loads, daily is fine. Over-scheduling doesn’t increase accuracy—just cost.

End-to-end example: from snapshot to dimension

One common pattern is to expose the “current” and “as-of” views from your snapshot for easy joins:

-- models/dim_order_status.sql
with latest as (
  select * from {{ ref('orders_timestamp_scd') }}
  qualify row_number() over (partition by order_id order by dbt_valid_from desc) = 1
)
select * from latest

Need help with macro patterns, Jinja control flow, or selection syntax around snapshots? See our Jinja & Macros guide. For interview-style questions you might face about snapshots and scds, review 30 dbt Interview Questions and Answers.

Strategy notes and best practices

• Prefer timestamp strategy when a clean upstream column exists; otherwise reach for check strategy.
• Keep snapshot inputs minimal and stable; move denormalization to downstream dbt models.
• Document assumptions and test the no-overlap invariant.
• If you need a primer on slowly changing dimensions (scds) or patterns beyond type 2 scd, jump to our SCD guide linked above.

Adapter behavior notes

Under the hood, dbt uses MERGE or equivalent upserts. Different warehouses may show slight differences in DML cost or concurrency behavior, but the snapshot contract remains the same. If you’re choosing between adapters or runtimes, compare options in dbt Cloud vs Core.

Summary: dbt snapshots are the pragmatic SCD type 2 mechanism built into dbt. They let you configure, run, and test history capture with a few lines of code, while the framework handles the sql details and storage of your snapshot table. For general context on dbt and related topics, see the dbt topic hub. Finally, drill the skills with our free graded practice exercises at /practice.