dbt Snapshots for SCD Type 2: Check vs Timestamp, and When to Hand-Roll

The first time a snapshot bit me, it was a subscription plan table. A customer moved from Pro to Enterprise on a Tuesday and back to Pro on Wednesday because sales had fat-fingered the upgrade. Our snapshot ran nightly at 2 a.m. The Wednesday run saw Pro, the Tuesday run saw Pro, and as far as our warehouse was concerned, the Enterprise day never happened.

Finance found it three months later while reconciling a commission payout. The fix was not a code change. There was nothing to fix. The data was gone, because a snapshot is a camera, not a log.

That is the thing I wish someone had said to me before I built my first one: dbt snapshots do not capture change, they capture observations of state. Everything else — strategy choice, column selection, run cadence — is downstream of that single limitation.

Here is how I pick a strategy, what each one costs, and the cases where I stop using dbt snapshot entirely.

What a snapshot actually writes

A snapshot table is your source table plus four columns: dbt_scd_id (a surrogate hash), dbt_updated_at, dbt_valid_from, and dbt_valid_to. The current row for each key has dbt_valid_to set to null.

flowchart TD
  run["Snapshot run at T"] --> cmp["Compare source row to current version"]
  cmp -->|"unchanged"| skip["Do nothing"]
  cmp -->|"changed"| close["Set dbt_valid_to = T on old row"]
  close --> ins["Insert new row, dbt_valid_to = null"]
  cmp -->|"key not seen before"| new["Insert first version"]
  cmp -->|"key missing from source"| policy["hard_deletes policy decides"]

Every branch is driven by comparison at run time. Nothing between runs is observable.

The merge itself is a MERGE against the snapshot table keyed on unique_key. That matters for cost: a snapshot of a fifty-million-row dimension reads all fifty million source rows and all current versions on every run, whether or not anything changed.

Timestamp strategy: cheaper, if you can trust the column

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

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

select
    customer_id,
    email,
    plan_name,
    account_manager_id,
    billing_country,
    source_updated_at
from {{ ref('stg_crm__customers') }}

{% endsnapshot %}

Timestamp strategy compares one column. If source_updated_at is newer than the stored dbt_updated_at, insert a new version. It is a single-column comparison, it produces a clean dbt_valid_from that reflects when the change actually happened in the source system rather than when your job ran, and it is dramatically cheaper on wide tables.

It is also entirely dependent on the source telling the truth. I have been burned by all four of these:

• Batch loaders that stamp updated_at with the load time, so every row looks changed on every run and your snapshot doubles nightly.
• Application code that updates a row without touching updated_at, usually an admin script or a data migration.
• Nullable updated_at on rows created before the column existed.
• Timezone-naive timestamps from a source that shifted from local time to UTC during a deployment, producing a batch of rows that appear to travel backwards.

Before I use timestamp strategy, I run this against a week of source data and read the output rather than glancing at it:

-- Does updated_at move independently of the row content?
-- If pct_changed_rows is ~100% every day, the loader is stamping it.
select
    date_trunc('day', source_updated_at) as day,
    count(*)                             as rows_touched,
    count(distinct customer_id)          as distinct_keys,
    round(100.0 * count(*) / (select count(*) from {{ ref('stg_crm__customers') }}), 1)
        as pct_of_table
from {{ ref('stg_crm__customers') }}
where source_updated_at >= current_date - 7
group by 1
order by 1;

If pct_of_table is near 100 every day, the column is a load timestamp and timestamp strategy will build you a very expensive copy of the same data over and over.

Check strategy: honest, expensive, and sensitive to schema drift

{% snapshot snap_products %}

{{
    config(
        target_schema='snapshots',
        unique_key='product_id',
        strategy='check',
        -- Name the columns. Do not use 'all'.
        check_cols=['product_name', 'category', 'list_price', 'is_active'],
        invalidate_hard_deletes=True
    )
}}

select
    product_id,
    product_name,
    category,
    cast(list_price as numeric(18, 2)) as list_price,
    is_active
from {{ ref('stg_erp__products') }}

{% endsnapshot %}

Check strategy compares column values directly. No trust in the source required. The cost is a wider comparison and, more importantly, a decision you have to make explicitly: which columns count as a change.

check_cols='all' is the option everyone reaches for and the one I have stopped using. Two reasons. First, adding a column to the source means every single row now differs from its stored version, so your next run writes a full new generation of history for a change that has nothing to do with the business. On a ten-million-row dimension that is a very memorable warehouse bill. Second, it makes noisy columns — last_login_at, sync_version, an ETL sequence number — into versioning triggers, and you end up with forty versions of a customer where three were real.

Name the columns. It is a five-minute decision that you make once and that documents what “the customer changed” means for your business.

The type-casting in the select matters more than it looks. Check strategy compares values, and a numeric(18,2) versus a float comparison across a warehouse type change will flag every row as different. Cast in the staging model, which is the same reason I snapshot staging rather than raw — the layering rule from my dbt project structure applies here too.

Handling deletes, which is where the defaults hurt

A row disappearing from a source is ambiguous. It might be a real delete, or the extract might have failed halfway and delivered a partial table. dbt’s default is to ignore it entirely: the last known version stays open with dbt_valid_to = null forever, so your “current customers” query silently includes people who churned.

{{
    config(
        target_schema='snapshots',
        unique_key='customer_id',
        strategy='timestamp',
        updated_at='source_updated_at',
        -- Newer dbt versions: 'new_record' keeps a tombstone row
        -- with dbt_is_deleted = true, which is far easier to query
        -- than an invalidated row with no marker.
        hard_deletes='new_record'
    )
}}

invalidate_hard_deletes=True (older syntax) closes the row out. hard_deletes='new_record' writes an explicit tombstone with dbt_is_deleted = true, which is what I want in almost every case, because “this customer was deleted on the 14th” and “we stopped seeing this customer on the 14th” are different facts and only one of them is recoverable from an invalidated row.

The dangerous interaction: if the upstream extract delivers an empty or partial table, delete detection will close out every row in your dimension. I gate the snapshot behind a row-count check for exactly this reason, in the same spirit as the threshold-based severity approach in a dbt testing strategy that catches regressions.

# models/staging/crm/_crm__models.yml
models:
  - name: stg_crm__customers
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 10000     # last known good was ~14k
          config:
            severity: error    # block the snapshot, not just warn

Run this test before the snapshot in your build order. A snapshot is one of the few dbt artifacts you cannot undo with a full refresh, which makes an error-severity gate in front of it worth the occasional false alarm.

Cadence, and the gap you are accepting

Snapshot frequency is a decision about how much change you are willing to lose. Nightly means you see the state of the world at 2 a.m. and nothing else. If a row changes twice between runs, you get the second value and no evidence of the first.

I write this down explicitly for every snapshot, in the model description:

snapshots:
  - name: snap_customers
    description: |
      SCD2 history of CRM customers. Runs hourly at :05.
      Resolution: 1 hour. Intra-hour changes are not captured.
      Owner: data-platform. Consumers: finance commission model.

Hourly on a small dimension is cheap and buys real resolution. On a fifty-million-row table it is not, and the honest answer is either “nightly, and we accept the gap” or “this source needs CDC, not snapshots.”

One more failure mode: a missed run does not just lose the change, it misattributes the timing. With check strategy, dbt_valid_from is the time of the run that noticed, not the time of the change. If your snapshot job was down for two days, every change from those two days gets stamped with the recovery run’s timestamp, and any analysis that joins on effective dates will attribute two days of activity to the wrong version. Timestamp strategy is immune to this, which is a real argument in its favor when the source column is trustworthy.

When to hand-roll instead

I stop using dbt snapshot when any of these are true.

The source already ships change events. If you have Debezium, Snowflake streams, or a Fivetran history table with a real change timestamp per event, the history already exists and is more complete than anything a nightly poll can produce. Building SCD2 from it is a window function, not a snapshot.

-- models/marts/core/dim_customers_history.sql
-- Build SCD2 from a CDC feed that already has one row per change.
with changes as (
    select
        customer_id,
        plan_name,
        billing_country,
        change_ts,
        operation                                -- 'I', 'U', 'D'
    from {{ ref('stg_cdc__customers') }}
),

versioned as (
    select
        customer_id,
        plan_name,
        billing_country,
        change_ts as valid_from,
        lead(change_ts) over (
            partition by customer_id order by change_ts
        ) as valid_to,
        operation
    from changes
)

select
    {{ dbt_utils.generate_surrogate_key(['customer_id', 'valid_from']) }} as customer_version_id,
    customer_id,
    plan_name,
    billing_country,
    valid_from,
    valid_to,
    valid_to is null and operation != 'D' as is_current
from versioned

This is reproducible from scratch, testable with fixtures, and does not carry the “cannot full-refresh without losing history” property that makes snapshots stressful. Snowflake users can wire the same pattern to streams and tasks for continuous capture.

You need bitemporal history. Snapshots give you one time axis: when the value was valid according to your observations. If you need both “what was true” and “what we believed at the time,” you need two pairs of dates, and no snapshot config gives you that.

The dimension is enormous and mostly static. A merge over a two-billion-row table nightly to catch four hundred changes is a bad trade. Use an incremental model against a filtered change window instead.

You need to backfill history. Snapshots cannot be rebuilt. If you have a year of source history sitting in raw and you need SCD2 over it, a hand-rolled model can produce it in one run. A snapshot can only start from today.

Where teams get this wrong

check_cols='all' on a table with an ETL metadata column. Every run versions every row. The snapshot grows linearly with run count, not with change count, and it takes months for anyone to notice because the numbers are still technically correct.

Snapshotting the raw source. No type casting, no soft-delete filter, and the raw column names are frozen into your history table forever. Snapshot the staging model.

Running dbt build --full-refresh in production. It drops and rebuilds the snapshot from current state. Every version before today is gone. Exclude snapshots from full-refresh commands in your production job and put the exclusion in the job definition, not in a person’s memory.

No delete policy. Rows churn out of the source and stay open forever. Every “current” query overcounts, quietly, in a direction nobody audits.

Assuming dbt_valid_from is when the change happened. With check strategy it is when your job noticed. Any analysis of time-to-upgrade or time-in-state built on check-strategy dates has run-schedule artifacts baked into it.

No row-count gate. One bad extract closes out the entire dimension. It is recoverable only if you also snapshot the snapshot, which nobody does.

FAQ

Should I use check or timestamp strategy by default?

Timestamp, if you have verified the source column moves only when the row content moves. Run the daily-percentage query above for a week first. If the column is a load timestamp or is ever null, use check with named columns.

Can I add a column to a snapshot later?

Yes, and dbt will add it to the table, but historical rows get null for it — you cannot reconstruct what that column held in the past. Add it to check_cols only if a change to it should genuinely create a new version.

What happens if I miss a week of snapshot runs?

You lose every intermediate state and, with check strategy, the recovery run stamps all those changes with its own timestamp. There is no repair. Alert on snapshot job success separately from the rest of your dbt run, because a silently skipped snapshot is invisible for months.

How do I query a snapshot as of a specific date?

Filter on the validity window, treating null dbt_valid_to as open:

select *
from {{ ref('snap_customers') }}
where dbt_valid_from <= '2026-03-01'
  and (dbt_valid_to > '2026-03-01' or dbt_valid_to is null);

Wrap that in a mart with an explicit is_current flag so analysts do not have to remember the null handling. Every project that skips this step eventually ships a report that double-counts.

Do snapshots belong in Slim CI?

No. Do not run snapshots in CI at all — they would write to a snapshot schema and pollute real history, or write to a scratch schema and tell you nothing. Exclude them by selector and validate the downstream models with Slim CI against a deferred production snapshot instead.

Is a snapshot the right place to detect data quality problems?

No. A snapshot faithfully records whatever the source said, including garbage, and it records it permanently. Test the staging model before the snapshot runs, so bad data fails the build rather than becoming a version.

What this means for your pipelines

Snapshots are the cheapest way to get SCD2 out of a source that only exposes current state, and that is exactly the situation most CRM, billing, and ERP tables put you in. Used inside that boundary they are excellent: a dozen lines of config, and you have history you did not have yesterday.

The boundary is what people miss. A snapshot’s resolution is your run interval, its accuracy depends on either a trustworthy updated_at or a deliberately chosen check_cols list, and its history is unrecoverable if you full-refresh it or if a bad extract triggers mass delete detection. Those are not bugs to work around, they are the shape of the tool.

So make the choice explicitly. Verify the timestamp column before you trust it. Name your check columns. Set a delete policy and gate the run behind a row-count test. Write the resolution in the description so the analyst who joins on effective dates in eight months knows what they are holding. And when the source starts emitting real change events, move to a hand-rolled model — at that point the snapshot is throwing away information you are already paying to collect.