Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 41 ยท Sources, Seeds and Snapshots

Snapshots: Tracking Changing Data

Learn how dbt snapshots record how rows change over time using dbt_valid_from and dbt_valid_to, configure them in YAML, run dbt snapshot, and see the mechanism in SQL.

Most application databases keep only the current state of a row. When a customer moves from Toronto to Lyon, the old city is overwritten and gone. That is fine for the application, but analysts often need history: "which region was this customer in when they placed that order last year?" A snapshot is dbt's answer. It records every change to a table's rows by keeping old versions with validity dates, a pattern known as a type 2 slowly changing dimension (SCD2).

The idea in one picture

Source table today:

id name city
2 Nico Lyon

The snapshot table keeps every version:

id name city dbt_valid_from dbt_valid_to
2 Nico Toronto 2025-01-01 2025-03-01
2 Nico Lyon 2025-03-01 null

The row that is current has dbt_valid_to equal to null. Older rows have an end date. Each time you run dbt snapshot, dbt compares the source with the snapshot and records what changed.

Defining a snapshot

In dbt 1.9 and later, snapshots are defined in YAML in the snapshots/ folder:

YAML
# snapshots/customers_snapshot.yml
snapshots:
  - name: customers_snapshot
    relation: source('shop', 'customers')
    config:
      schema: snapshots
      unique_key: id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: invalidate
  • relation is the source or model to watch (any ref() or source()).
  • unique_key identifies a row, so dbt can match old and new versions.
  • strategy is how changes are detected (timestamp or check, see the next lesson).
  • updated_at is the column the source updates whenever a row changes.
  • schema is where the snapshot table goes. Keep snapshots in their own schema.

Older projects (and many tutorials) use a Jinja block instead, which still works:

SQL
{% snapshot customers_snapshot %}

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

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

{% endsnapshot %}

Running a snapshot

Terminal
dbt snapshot
dbt snapshot --select customers_snapshot
dbt build            # includes snapshots in DAG order

The first run creates the snapshot table with every source row as a current version. Later runs add new versions and close old ones. dbt adds four columns:

Column Meaning
dbt_scd_id A unique id for each version
dbt_updated_at The timestamp used to detect the change
dbt_valid_from When this version became current
dbt_valid_to When it stopped being current (null if current)

Watching the mechanism

dbt generates a MERGE or UPDATE plus INSERT, depending on the adapter. Here is the same logic written as plain SQL, run in two "snapshot runs" against a small table. First, the setup and run 1. Then the source changes (Nico moves city, a new customer appears), and run 2 records both:

SQL Two snapshot runs in SQL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Nico has two rows: the Toronto version closed on 2025-03-01 and the Lyon version, current. Yusuf did not change, so he still has one row. Rosa is new, so she has one current row. Running the same snapshot again with no changes would do nothing, which means snapshots are idempotent.

Querying history

Because every version has a validity window, "what was true on date X" is a range filter:

SQL Point-in-time lookup ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The pattern valid_from <= date AND (valid_to > date OR valid_to IS NULL) is the one to memorize. Joining orders to this snapshot on the order date tells you where the customer lived when they ordered, which the current table cannot.

Important facts about snapshots

  • They cannot be rebuilt. A snapshot only knows about changes that happened while it was running. If you lose the table, you lose history that the source no longer has. Treat the snapshot schema like production data and never --full-refresh it carelessly.
  • Run them regularly. A change that happens and is overwritten between two runs is never seen. Run snapshots at least as often as you need history granularity, often hourly or daily.
  • Snapshot the source, not a transformed model. Select from the raw table (with select * or a close copy), so history reflects the real data and not your logic of the day.
  • They are not tests of data quality. Snapshots record whatever the source says, including mistakes.

Common mistakes

  • A unique_key that is not actually unique, which creates duplicate versions.
  • Using timestamp strategy when the source's updated_at is not reliably updated (the next lesson covers check).
  • Putting snapshots in the same schema as models, then dropping that schema in dev.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you recognize the current version of a row in a snapshot?

    The current version has no end date, so dbt_valid_to is null.
  2. 2. What does a snapshot let you do that the source table cannot?

    Snapshots keep old versions of changed rows with validity dates, so you can query past states.
  3. 3. Why is it risky to delete a snapshot table?

    Snapshots record changes as they happen. Past versions are not recoverable from the source later.
  4. 4. Which key must a snapshot define so dbt can match versions of the same row?

    unique_key identifies the row across versions.

Summary

  • Snapshots implement type 2 slowly changing dimensions: changed rows get a new version, and old versions are closed with dbt_valid_to.
  • Define them in YAML (dbt 1.9+) or in {% snapshot %} blocks, then run dbt snapshot.
  • Query history with valid_from <= date AND (valid_to > date OR valid_to IS NULL).
  • Run them regularly and protect the snapshot schema: history cannot be recreated.

Next: Snapshot Strategies, Hard Deletes and SCD2