What does a dbt snapshot do, and which dbt_ columns does it add?

## The problem snapshots solve Most source systems store only current state. An `orders` table has one row per order with a `status` column overwritten in place: `pending` becomes `packed` becomes `shipped`. If the warehouse reloads that table nightly, then on Tuesday nobody can answer "how many orders were still pending on Monday morning?" The row still exists; only its latest value survives. A dbt snapshot is dbt's built-in way to accumulate that history. It turns a mutable source table into a history table where each distinct version of a row is its own row, tagged with the window during which that version was the truth. That shape is a Type 2 slowly-changing dimension, and a snapshot is dbt's concrete implementation of it. ## Defining one A snapshot is a `.sql` file under the `snapshots/` path, wrapping a `select` in a `{% snapshot %}` block with a `config()`: ```sql {% snapshot orders_snapshot %} {{ config( target_schema='snapshots', unique_key='id', strategy='timestamp', updated_at='updated_at' ) }} select * from {{ source('shop', 'orders') }} {% endsnapshot %} ``` `unique_key` identifies the business entity — the thing whose history you are tracking. `strategy` tells dbt how to detect that a row changed: `timestamp` trusts a column that moves on every update, `check` compares the values of listed columns. The select should stay a plain `select *` from the source. Any filtering or business logic you bake in gets frozen into history permanently, and you cannot re-derive it later. (dbt 1.9 also allows defining snapshots in YAML and makes `target_schema` optional; the block form above remains valid and is what most existing projects use.) ## The four dbt_ columns - **`dbt_valid_from`** — when this version became effective. Under the `timestamp` strategy it is the source `updated_at` of that version; under `check` it is the time the snapshot ran. - **`dbt_valid_to`** — when this version stopped being effective. **NULL on the current version**, which is how you filter to "today's state". - **`dbt_updated_at`** — the timestamp dbt used when writing the row, from the same source as `dbt_valid_from`. - **`dbt_scd_id`** — a generated hash that uniquely identifies this one version row. Because versions are contiguous, one closed version's `dbt_valid_to` equals the next version's `dbt_valid_from`, so every instant maps to exactly one row per `unique_key`. ## What a run actually does The first run creates the table with one row per source row, all open (`dbt_valid_to` NULL). Every later run compares the current source against the open rows in the snapshot: - key present, no change detected → nothing happens; - key present, change detected → the open row's `dbt_valid_to` is set, and a new open row is inserted; - key not previously seen → a new open row is inserted; - key that vanished from the source → by default nothing happens, and its version stays open (see the hard-deletes discussion in this topic). Crucially, dbt never rewrites the value in an existing closed row. The table only grows. ## Running and consuming `dbt snapshot` executes only snapshots. `dbt build` executes seeds, models, snapshots and tests together in dependency order, which is the usual production invocation. Downstream models reference a snapshot exactly like a model: ```sql select * from {{ ref('orders_snapshot') }} where dbt_valid_to is null ``` for current state, or with a predicate on `dbt_valid_from`/`dbt_valid_to` to reconstruct what a row looked like on a given date. Snapshots also belong in your `schema.yml` — a `unique` test on `dbt_scd_id` and a `not_null` test on `dbt_valid_from` are cheap guards. ## What a snapshot is not It is not an incremental model: incremental materialization appends or merges new *facts*, while a snapshot versions *the same entity over time*. It is not change data capture either — it only sees what the source holds at the moment it runs, so its history resolution is bounded by how often you schedule it. And unlike a model, its content is accumulated state rather than something dbt can regenerate from source, which is why the snapshot table deserves the same care as production data.