SNAPSHOTS

YOUR FIRST SNAPSHOT

A snapshot is a .sql file in your snapshots/ directory: a {% snapshot %} block wrapping a config and a plain select. Here is a complete, working one:

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

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

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

{% endsnapshot %}

Run it:

$ dvt snapshot

1 of 1 START snapshot snapshots.customers_snapshot ............... [RUN]
1 of 1 OK snapshotted snapshots.customers_snapshot ............... [SUCCESS 42 in 1.32s]

Finished running 1 snapshot in 1.32s.

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

The first run creates snapshots.customers_snapshot on your default target and copies every row in. Every run after that compares the source against what the snapshot already knows and records only what changed. Schedule it — daily is typical — and history accumulates on its own.

WHAT LANDS IN THE TABLE

The snapshot table has every column your select produced, plus four meta columns the engine maintains for you:

So when customer 1 upgrades from trial to paid, the snapshot ends up holding both versions — the old one closed out, the new one open:

customer_id | status | updated_at | dbt_valid_from | dbt_valid_to
------------+--------+------------+----------------+--------------
1           | trial  | 2026-01-02 | 2026-01-02     | 2026-01-15     ← history
1           | paid   | 2026-01-15 | 2026-01-15     | null           ← current

Downstream models ref() a snapshot like any model. The current state of the world is simply the rows that were never closed out:

-- models/silver/current_customers.sql
select *
from {{ ref('customers_snapshot') }}
where dbt_valid_to is null

THE TIMESTAMP STRATEGY

The example above uses strategy='timestamp': you name a column (updated_at) that reliably moves forward whenever the row changes, and the engine records a new version whenever it sees a fresher value for a given unique_key. It is the cheapest strategy — one column comparison — and the one to prefer whenever your source has a trustworthy last-modified column.

"Trustworthy" is the operative word: if an upstream process updates rows without touching updated_at, those changes are invisible to this strategy. That is what check is for.

THE CHECK STRATEGY

No reliable timestamp? strategy='check' compares the actual column values between runs and records a version whenever any of the listed check_cols differ:

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

{{
    config(
      target_schema='snapshots',
      unique_key='product_id',
      strategy='check',
      check_cols=['price', 'status']
    )
}}

select * from {{ source('analytics', 'products') }}

{% endsnapshot %}

A change to price or status creates a new version; a change to any other column does not. check_cols='all' watches every column — convenient, but the comparison cost grows with table width, so on wide tables name the columns you actually care about.

YAML-DEFINED SNAPSHOTS

The engine also supports dbt's newer YAML shape: define the snapshot in a .yml file and point it straight at a relation — no {% snapshot %} block, no SQL file at all for the simple select-everything case:

# snapshots/orders_snapshot.yml
snapshots:
  - name: orders_snapshot
    relation: source('analytics', 'orders')
    config:
      schema: snapshots
      unique_key: order_id
      strategy: timestamp
      updated_at: updated_at

Both shapes are first-class in DVT — including in dvt flip-target-to, which recognizes block snapshots and bare-query YAML-defined snapshots alike (more on that below). Pick one style per project and stay consistent.

RUNNING SNAPSHOTS

dvt snapshot                                  # every snapshot in the project
dvt snapshot --select customers_snapshot      # just this one
dvt snapshot --select tag:nightly             # by tag
dvt snapshot --empty                          # zero-row dry run: builds/validates
                                              # the table, records nothing
dvt build                                     # seeds + models + snapshots + tests,
                                              # DAG order

Selection is dbt's node-selection syntax, the same one dvt run uses. dvt build places snapshots into the full DAG, so a snapshot that reads a model runs after that model builds. Like every graph verb, dvt snapshot starts from a passing dvt parse — a broken project refuses with the parse error, never a half-run.

One honest warning about state: a snapshot table is accumulated history, so the engine deliberately protects it — --full-refresh does not rebuild snapshots the way it rebuilds incremental models. Dropping a snapshot table is a manual act, and it destroys history that cannot be recomputed from the source. Treat snapshot tables like production data, because they are.

WHERE SNAPSHOTS RUN: ALWAYS THE DEFAULT TARGET

Snapshots are a pure pass-through to the engine: they execute on your default target, in its native SQL dialect, through the official adapter. Only models can be federated in DVT — f_table and f_incremental are model materializations, and there is no federated snapshot.

So what if the data you want history for lives on another engine? Land it first with a federated model, then snapshot the landed copy — federation does the cross-engine move, and the snapshot stays pure dbt:

-- models/bronze/bronze_crm_customers.sql
-- Oracle data, landed on the default target by federation
{{ config(materialized='f_table') }}

select customer_id, status, updated_at
from {{ source('crm', 'customers') }}


-- snapshots/crm_customers_snapshot.sql
-- snapshots the landed copy — runs after the model in dvt build
{% snapshot crm_customers_snapshot %}
{{
    config(
      target_schema='snapshots',
      unique_key='customer_id',
      strategy='timestamp',
      updated_at='updated_at'
    )
}}
select * from {{ ref('bronze_crm_customers') }}
{% endsnapshot %}

SNAPSHOTS SURVIVE A TARGET FLIP

Snapshot SQL is written in your default target's dialect — which used to make it the thing a warehouse migration silently broke. dvt flip-target-to treats snapshots as a first-class path class, read from your project's snapshot-paths: when you flip the default target, every snapshot is transpiled to the new engine's dialect exactly like a model, with the same per-file report.

The transpile is surgical. A {% snapshot %}block's own Jinja — the tags, the strategy, unique_key, updated_atconfig — carries semantics, not dialect, so it comes back byte-for-byte. Only the SQL body inside the block is translated, and the file is refused untouched rather than written if the Jinja doesn't survive intact. YAML-defined snapshots, whose .sql is a bare query, translate like a plain model file. And the flip report tallies per class — a project can flip every model and still be broken by one untranslatable snapshot, so snapshots get their own line in the summary.

One thing the flip deliberately does notdo: move your data. The history rows accumulated on the old engine stay on the old engine — a flip rewrites code, and migrating a snapshot table's accumulated state is a decision you make explicitly, not a side effect. Plan that copy as its own step before pointing your schedule at the new target.

HONEST NOTES ON ENGINE DIFFERENCES

Snapshot semantics are identical everywhere — the same strategies, the same meta columns. The physics follow the adapter, and a few differences are worth knowing:

• ▸Each adapter implements the snapshot materialization with its own SQL — merge on engines that have it, delete+insert where they don't. Same result, different plans and different costs.
• ▸Timestamp comparison follows the engine's timestamp semantics. Mixed timezones or truncated precision in updated_at can hide or duplicate changes — keep the column UTC and at consistent precision, especially if the source engine differs from the target.
• ▸check strategy costs one column-by-column comparison per run. Columnar warehouses shrug at it; row stores feel check_cols='all' on wide tables.
• ▸Rows deleted from the source are ignored by default — the snapshot keeps the last version open. The hard_deletes config ('invalidate' or 'new_record') changes that, at the cost of a full existence check per run.

CONFIG REFERENCE