`timestamp` vs `check`: a snapshot's two strategies

Description

Lesson 2 showed you the boolean question a snapshot asks the warehouse on every run — did this row change? — but left an earlier, more important question open: how does dbt decide, exactly, whether something changed? dbt-core ships with two different ways to answer that question, declared with the strategy key. This lesson runs both — for real, with real data — so this guide's choice (strategy: timestamp, the one you're going to configure in lesson 4) isn't a value copied without understanding, but a decision informed by evidence.

Connection to the module. Lesson 2 gave you the general "compare and archive" mechanism. This lesson answers the question that mechanism needs before it can work: compare, how, exactly? Lesson 4 is going to declare dim_product_snapshot with the strategy this lesson justifies with evidence — not before.

An analogy: the guard who checks the clock-in stamp, versus the one who checks the face

Go back to lesson 2's guard, comparing the new photo against the one on file. There are two completely different ways for that guard to decide "this person changed something relevant":

The first is to look at a date stamp the person themselves carries — an ID card with a "last updated" field, which HR updates every time it corrects something on that person's profile. The guard doesn't need to compare face against face, hair against hair: they only compare the new card's date against the date already on file. If it's more recent, something changed — with no need to know what. That's the timestamp strategy: it trusts that the source itself brings a column saying when something last changed, and it compares only that date.

The second is to not trust any card, and instead directly check the features the guard cares about: is the hair color the same as last time? Does the recorded height match? The guard compares, field by field, the old file against the person standing in front of them — with no need for any date stamp, because the comparison itself is the source of truth. That's the check strategy: it compares, column by column, the archived value against the current value, and if any of the columns you asked it to watch differs, it considers the row changed.

Worked example: the same question, two ways to answer it

Pick up lesson 2's scenario — P002 already archived as snacks/0.60/2026-08-01, and the source now bringing health-snacks/0.68/2026-08-15. Here's how each strategy answers "did it change?":

-- what's already archived
create table snapshotted as
select 'P002' as product_id, 'snacks' as category, 0.60 as unit_cost,
       date '2026-08-01' as product_updated_at, date '2026-08-01' as dbt_updated_at;

-- what the source brings in this run
create table source_now as
select 'P002' as product_id, 'health-snacks' as category, 0.68 as unit_cost,
       date '2026-08-15' as product_updated_at;

-- the TIMESTAMP strategy's question: is the date more recent?
select
    s.product_id,
    src.product_updated_at as source_updated_at,
    s.dbt_updated_at        as snapshotted_updated_at,
    (src.product_updated_at > s.dbt_updated_at) as row_has_changed
from snapshotted s join source_now src on s.product_id = src.product_id;

-- the CHECK strategy's question: is any of these columns different?
select
    s.product_id,
    s.category   as old_category,   src.category   as new_category,
    s.unit_cost  as old_unit_cost,  src.unit_cost  as new_unit_cost,
    (src.category  is distinct from s.category
     or src.unit_cost is distinct from s.unit_cost) as row_has_changed
from snapshotted s join source_now src on s.product_id = src.product_id;

What to expect.

-- TIMESTAMP
┌────────────┬────────────────────┬─────────────────────────┬──────────────────┐
│ product_id │ source_updated_at  │ snapshotted_updated_at  │ row_has_changed  │
├────────────┼────────────────────┼─────────────────────────┼──────────────────┤
│ P002       │ 2026-08-15         │ 2026-08-01              │ true             │
└────────────┴────────────────────┴─────────────────────────┴──────────────────┘

-- CHECK
┌────────────┬──────────────┬───────────────┬────────────────┬────────────────┬──────────────────┐
│ product_id │ old_category │ new_category  │ old_unit_cost  │ new_unit_cost  │ row_has_changed  │
├────────────┼──────────────┼───────────────┼────────────────┼────────────────┼──────────────────┤
│ P002       │ snacks       │ health-snacks │           0.60 │           0.68 │ true             │
└────────────┴──────────────┴───────────────┴────────────────┴────────────────┴──────────────────┘

Both strategies reach the same conclusion — row_has_changed = true — but by completely different paths: timestamp never looks at category or unit_cost, only the date; check never looks at any date, only the values of the columns you told it to watch. This match isn't a coincidence — Kiosko updates product_updated_at exactly when something real changes — but it's exactly the kind of assumption worth examining before trusting it blindly, and that's what the rest of this lesson does.

Real verification: strategy: check actually run, over an isolated snapshot

The two queries above are a plain-SQL simulation — useful for understanding the logic, but not the same as watching dbt actually make that decision. An isolated snapshot, separate from dim_product_snapshot (which you're only going to declare in lesson 4), with strategy: check over the same two versions of P002, run twice:

# demo snapshot, NOT part of this module's final project
snapshots:
  - name: dim_product_check_demo
    relation: source('demo_raw', 'products')
    config:
      unique_key: product_id
      strategy: check
      check_cols:
        - category
        - unit_cost

What to expect (first run, over the snacks/0.60 version).

1 of 1 OK snapshotted main.dim_product_check_demo .............................. [OK in 0.07s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

┌────────────┬──────────┬───────────┬────────────────────────────┬──────────────┐
│ product_id │ category │ unit_cost │       dbt_valid_from       │ dbt_valid_to │
├────────────┼──────────┼───────────┼────────────────────────────┼──────────────┤
│ P002       │ snacks   │       0.6 │ 2026-08-12 18:58:14.713882 │ NULL         │
└────────────┴──────────┴───────────┴────────────────────────────┴──────────────┘

Stop here, because this is the lesson's central finding: dbt_valid_from does not say 2026-08-01 (the data's real date) — it says 2026-08-12 18:58:14.713882, the exact instant this command was run on this machine, hour, minutes, and microseconds included. This isn't a bug — it's strategy: check's documented behavior when you don't explicitly give it an updated_at column: with no source date to hold on to, dbt uses the run's moment (run_started_at internally) to mark when it opened each row.

What to expect (second run, over the health-snacks/0.68 version).

1 of 1 OK snapshotted main.dim_product_check_demo .............................. [OK in 0.12s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

┌────────────┬───────────────┬───────────┬────────────────────────────┬────────────────────────────┐
│ product_id │   category    │ unit_cost │       dbt_valid_from       │        dbt_valid_to        │
├────────────┼───────────────┼───────────┼────────────────────────────┼────────────────────────────┤
│ P002       │ snacks        │       0.6 │ 2026-08-12 18:58:14.713882 │ 2026-08-12 18:58:29.053896 │
│ P002       │ health-snacks │      0.68 │ 2026-08-12 18:58:29.053896 │ NULL                       │
└────────────┴───────────────┴───────────┴────────────────────────────┴────────────────────────────┘

strategy: check does detect the change correctly — two rows, row_has_changed worked exactly as in the simulation above — but look at the date columns: 2026-08-12 18:58:14.713882 and 2026-08-12 18:58:29.053896 are, precisely, the instant this specific demo ran on this machine — fifteen seconds' difference between one run and the next, because that's how long it took to type the command in between. If you ran this exact same demonstration on your own machine, at a different moment, you'd see completely different numbers.

Why this guide chooses timestamp, with evidence and not by habit

There's the concrete, not abstract, reason dim_product_snapshot (lesson 4 onward) uses strategy: timestamp and not strategy: check: this guide promises, in every lesson, a "What to expect" block with literal, byte-for-byte reproducible output on any machine. strategy: check with no explicit updated_at column breaks that promise at the root — every run stamps the system's real clock time, different each time — exactly the kind of clock function (implicit CURRENT_TIMESTAMP) this guide's design forbids in any block that feeds a "What to expect."

strategy: timestamp, on the other hand, never queries the system clock to decide dbt_valid_from: it always uses the value of the column you configure with updated_at — product_updated_at, in Kiosko's case. Since that column is a fixed Kiosko data point (2026-08-01, 2026-08-15), the result is exactly the same no matter which machine, or what time of day, you run dbt snapshot. You're going to confirm this with real evidence in lesson 5: when you declare dim_product_snapshot with strategy: timestamp, dbt_valid_from is going to show 2026-08-01 — the data's date, not the date you ran the command, even if you run it weeks after this guide was written.

Worth saying, to keep this complete: strategy: check also accepts an optional updated_at, and if you configured it, it would stop depending on the system clock just like timestamp does. But doing so would be redundant in Kiosko's case — if you already have a reliable date column like product_updated_at, using strategy: timestamp directly is the simplest way to take advantage of it — and check shows its real value in the opposite case: when the source doesn't bring any reliable date column, and the only way to detect a change is to compare the values themselves, column by column. Kiosko isn't in that case — that's why this guide uses timestamp — but it's important that you recognize when check would be the right choice in a different project.

Decision table: when to use each strategy

Common mistakes

Assuming check is "safer" because it compares more things. What happens: someone, seeing that check examines business columns directly, concludes it's the more rigorous option, and that timestamp is a "trimmed down" or less reliable version. Why it happens: comparing more columns feels, intuitively, more thorough than comparing a single date. How to spot it: if the source reliably updates product_updated_at every time something changes — Kiosko's case — timestamp detects exactly the same changes check would detect comparing column by column, with less configuration and no need to enumerate every business column by hand. How to fix it: no strategy is "safer" in the abstract — the right question is whether the source brings a reliable date column or not. If it does, timestamp is simpler and just as correct; if it doesn't, check is the only viable option.

Copying this lesson's check demo into Kiosko's real project. What happens: someone, after running this lesson's demo, leaves snapshots/dim_product_check_demo.yml inside kiosko_analytics/, thinking it's part of the project. Why it happens: the demo uses syntax almost identical to what you're going to use in lesson 4 for the real snapshot, and it's easy to confuse "illustrative example" with "project piece." How to spot it: if in lesson 8 you run dbt ls --select snapshot:* and see more than one snapshot, something was left over. How to fix it: this module's final project has exactly one snapshot, dim_product_snapshot, with strategy: timestamp — this lesson's demo is, on purpose, an isolated experiment, so you see the difference with your own eyes, not a permanent piece of kiosko_analytics/.

Thinking the type WARNING you're going to see in lesson 5 has anything to do with the strategy choice. What happens: someone, seeing a WARNING about data types in lesson 5, suspects they picked the wrong strategy in this lesson. Why it happens: any WARNING near the word "timestamp" invites suspicion of the strategy with the same name. How to spot it: lesson 5 explains that WARNING in detail — it has to do with product_updated_at's data type (DATE, not TIMESTAMP), not with whether you chose timestamp or check as strategy. How to fix it: mentally separate these two concepts — strategy: timestamp is the comparison criterion (this lesson); SQL's TIMESTAMP data type is a column type (lesson 5). They share a name, not a meaning.

Exercises

Exercise 1 — Predict check without updated_at, run twice over UNCHANGED data. Without running anything, predict: if you ran this lesson's dim_product_check_demo demo twice in a row, with no change to products_v1.csv in between, how many rows would the table have at the end? Would dbt_valid_from of the single row change at all?

Exercise 2 — Design check_cols to also watch product_name. If Kiosko decided a product name change (for instance, renaming "Energy Bar" to "Energy Bar Max") should also be historized, what would you change in this lesson's demo check configuration?

config:
  unique_key: product_id
  strategy: check
  check_cols:
    - category
    - unit_cost
    - product_name

Exercise 3 — Argue why this lesson's reproducibility WARNING is more serious than an ordinary WARNING. In 2-3 sentences, and using what you already know from module 1 about analytics engineering as a discipline, explain why dbt_valid_from depending on the system clock — as you saw with check with no updated_at — is a more serious problem than a simple cosmetic detail.

Summary and next step

This lesson ran both strategies over P002's same real change and found the difference that matters: strategy: timestamp always uses the source's date (product_updated_at), fully reproducible; strategy: check with no explicit updated_at uses the system clock, hour and microseconds included, different on every run. That difference, verified with real evidence and not assumed, is the concrete reason dim_product_snapshot is going to use strategy: timestamp from lesson 4 onward.

Before moving on you should be able to: explain, without looking at this lesson's table, when each strategy is the right choice; and describe precisely what it means for strategy: check with no updated_at to "not be reproducible."

Lesson 4 finally declares this module's real snapshot: dim_product_snapshot, with strategy: timestamp, updated_at: product_updated_at, unique_key: product_id — and explains, with the same evidence you already started seeing here, why it points at the raw source and not at stg_products.

Resources

• dbt Developer Hub — "Add snapshots to your DAG," strategies section (timestamp and check), including the mention that check accepts an optional updated_at. docs.getdbt.com/docs/build/snapshots. In English.
• dbt Developer Hub — "Snapshot configurations," the complete reference for check_cols, including the special value 'all' to watch every column without listing them. docs.getdbt.com/reference/resource-configs/check_cols. In English.
• data-modeling-for-analytics-guide, lesson "The problem: dim_product isn't static" (module 4) — the manual comparison between products_v1 and products_v2 with <>, the same criterion the check strategy automates. Sibling guide in the same ecosystem.