What a dbt snapshot actually does

Description

dbt snapshot isn't magic — it's a fairly concrete SQL pattern, executed by dbt on your behalf, every time you run the command. This lesson opens that box, the same way module 4 opened the box of unique and not_null with dbt compile: instead of accepting "dbt historizes SCD type 2" as a magic phrase, you're going to see, with real SQL you can run yourself, the exact question a snapshot asks the warehouse every time it runs, and the two actions it can take depending on the answer.

Connection to the module. Lesson 1 gave you the complete analogy — the security camera that archives every frame — and the equivalence table with data-modeling-for-analytics-guide. This lesson is the technical foundation the following five lessons rest on: without understanding that a snapshot is, underneath, "a comparison between what's already archived and what just arrived, followed by closing one row and opening another," declaring strategy: timestamp in lesson 4 would feel like copying syntax instead of applying a mechanism you already understand.

An analogy: the guard who compares the new photo against the one on file

Think of an access control system with facial recognition. Every time someone passes the camera, the system doesn't just save the new photo — it first compares it against the photo already on file for that person. If the two photos are "the same" by the system's criterion (same face, no relevant changes), nothing happens: the file stays as it was. If it detects a real change — the person grew a beard, changed their hair color, whatever criterion the system is configured to watch — the system doesn't delete the old photo: it archives it with an "until when this photo was current" mark, and adds the new photo to the file, marked as current from now on.

A dbt snapshot does exactly this, every time it runs. It compares the source's current state — products_v2.csv, in this module's case — against the last frame archived in the snapshot table. If, by the configured criterion (the strategy, lesson 3's topic) the row didn't change, it does nothing. If it changed, it executes two actions in the same step: it closes the old row (gives it a dbt_valid_to date) and opens a new row (with dbt_valid_from set to now, dbt_valid_to blank). It's, precisely, the same "close before opening" pattern data-modeling-for-analytics-guide already built by hand with UPDATE followed by INSERT, and later automated with MERGE INTO — a dbt snapshot is that same automation, now behind a single terminal command.

Worked example: the exact question, in plain SQL

Before declaring any real dbt snapshot — that starts in lesson 4 — it's worth seeing the question a snapshot asks the warehouse, in isolation from any dbt syntax. Imagine there's already an archived frame of P002 — the snacks/0.60 version, product_updated_at = 2026-08-01 — and the source now brings a new version: health-snacks/0.68, product_updated_at = 2026-08-15. Here, in plain SQL, is the question a snapshot with strategy: timestamp would ask of those two states:

-- what's already archived (the "file")
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 (the "new photo")
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 exact question the timestamp strategy asks:
-- is the source's date MORE RECENT than the already-archived date?
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;

What to expect.

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

row_has_changed = true. That single boolean column is, precisely, the entire decision a snapshot with strategy: timestamp needs to make: if the date the source brings is more recent than the date already on file, the row changed, and dbt executes the close-and-open. If it were false — as it would be comparing products_v1 against itself — dbt touches nothing.

Diagram: the two actions a snapshot executes when it detects a change

flowchart TD
    A["dbt snapshot runs"] --> B["Reads the current source\n(the snapshot's source or ref)"]
    B --> C["Compares each row against\nthe last archived frame"]
    C -->|"row_has_changed = false"| D["Does nothing\n(the row stays current as is)"]
    C -->|"row_has_changed = true"| E["Action 1: CLOSES the old row\nUPDATE dbt_valid_to = now"]
    E --> F["Action 2: OPENS the new row\nINSERT with dbt_valid_from = now,\ndbt_valid_to = NULL"]
    C -->|"new product_id,\nnever seen before"| G["Single action: OPENS the row\n(nothing to close)"]

Notice the diagram's third branch, the one you haven't seen with real data yet: if a product_id shows up for the first time — say Kiosko adds a P005 to the catalog — a snapshot detects it and simply opens it, with nothing to close, because there's no previous frame of that product. Kiosko's four products already exist from this module's first run (lesson 5), so this branch doesn't activate with this module's data — but it's part of the same mechanism, and it's worth recognizing.

The three columns dbt generates, and what each one guarantees

Every row a snapshot archives — in the first run and in any later run — gains three columns you never declare by hand in your SELECT:

• dbt_valid_from — since when this version of the row is current. Under the timestamp strategy (the one you're going to use in this module), its value comes straight from the updated_at column you configure — never from the system clock. Lesson 4 comes back to this point in more detail, because it's central to this module being reproducible.
• dbt_valid_to — until when it was. NULL while the row is still current; a real value from the moment a newer version replaces it. It's exactly the automatic equivalent of valid_to in data-modeling-for-analytics-guide — and its absence (IS NULL) is the automatic equivalent of is_current = true.
• dbt_scd_id — a unique key, computed by dbt, for each version of each row. Unlike dbt_valid_from/dbt_valid_to, it has no sibling column in data-modeling-for-analytics-guide's dim_product_scd — that guide used a sequence (CREATE SEQUENCE product_key_seq) to achieve something similar, a unique identifier per version. dbt_scd_id solves the same problem — "I need a primary key for each row in the history, not just for each product" — with no sequence for you to declare: dbt derives it automatically from unique_key and dbt_valid_from, guaranteeing every combination of "this product, in this validity range" gets its own identifier.

Going deeper: why dbt compile doesn't show you this logic

If you already know dbt compile --select <name> from module 4 — where it let you read the exact SELECT behind a generic test — it's reasonable to expect it to work the same way over a snapshot. It doesn't, and it's worth understanding why. Try it:

dbt compile --select dim_product_snapshot
cat target/compiled/kiosko_analytics/snapshots/dim_product_snapshot.yml/dim_product_snapshot.sql

The file you're going to find (once you declare the snapshot in lesson 4) is just this:

select * from 'raw_data/kiosko/products_v1.csv'

No comparisons, no dbt_valid_from, none of the close-and-open logic you saw in this lesson's worked example. The reason: dbt compile only translates the snapshot's query — the SELECT that defines where the new data comes from, the only thing you write — into final SQL. The comparison, close, and open logic doesn't live in that SELECT; it lives inside dbt-core's snapshot materialization, a set of internal macros dbt executes in real time when you run dbt snapshot, building and discarding temporary working tables that never get saved as a readable .sql file. The boolean question you saw in this lesson's worked example — source.updated_at > snapshotted.dbt_updated_at — is, precisely, what those internal macros compute, but there's no compiled file where you can read it directly, unlike a generic test.

This isn't an arbitrary limitation: it means that, to understand what a snapshot is going to do before running it, the source of truth isn't a compiled file — it's the official documentation of the strategy you chose (lesson 3), plus the behavior you confirm by running it yourself, exactly what this module's lessons 5 and 6 have you do.

Common mistakes

Looking for the comparison SQL in target/compiled/, as if it were a generic test. What happens: someone, following module 4's habit, runs dbt compile --select dim_product_snapshot expecting to find the close-and-open logic in the resulting file. Why it happens: module 4 established the habit of "if I want to see what something in dbt does, I compile it and read the file" — a correct habit for tests, but not for snapshots. How to spot it: a snapshot's compiled file is always just the source's SELECT, with no comparison — if you expected more, review this lesson's Going deeper section. How to fix it: for snapshots, the source of truth on exact behavior is dbt's official documentation (linked in Resources) combined with the evidence of running it yourself — not a compiled file.

Thinking a snapshot "knows" on its own when to run. What happens: someone assumes that, once declared, dim_product_snapshot updates automatically every time products_v2.csv changes, with no one running any command. Why it happens: the security-camera analogy can suggest, if pushed too far, a continuous, automatic process. How to spot it: if you change products_v2.csv and query dim_product_snapshot without having run dbt snapshot again, you're going to see exactly the same content as before the change — the snapshot doesn't watch anything on its own. How to fix it: a snapshot only compares and archives at the exact instant someone — a person, or (outside this guide's scope) an orchestrator — runs dbt snapshot. Between one run and the next, the snapshot table is just as static as any normal table.

Confusing "row_has_changed = false" with "the product doesn't exist in the source." What happens: someone interprets an unchanged row (row_has_changed = false) to mean that product disappeared from the source, instead of it simply having no real change. Why it happens: "false" sounds, in everyday language, like absence, not "present but with no news." How to spot it: in this lesson's worked example, if you compare P001 between products_v1 and products_v2 (neither changes), the boolean question gives false — and P001 is still perfectly present in both sources, no drama at all. How to fix it: row_has_changed = false means, exactly, "this row is still the same by the configured criterion" — nothing more. What happens when a product truly disappears from the source is a scenario this guide doesn't cover with real data (Kiosko's catalog doesn't lose any product in this module), but lesson 3 mentions it as part of the boundary of what dbt's two strategies do and don't watch on their own.

Exercises

Exercise 1 — Repeat the boolean comparison for P001 (no changes). Using the same SQL pattern from the worked example, but with P001's real values (beverages, 0.40, product_updated_at = 2026-08-01 in both versions), confirm with a query that row_has_changed gives false.

create table snapshotted_p001 as
select 'P001' as product_id, 'beverages' as category, 0.40 as unit_cost,
       date '2026-08-01' as product_updated_at, date '2026-08-01' as dbt_updated_at;

create table source_now_p001 as
select 'P001' as product_id, 'beverages' as category, 0.40 as unit_cost,
       date '2026-08-01' as product_updated_at;

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_p001 s
join source_now_p001 src on s.product_id = src.product_id;

┌────────────┬────────────────────┬─────────────────────────┬──────────────────┐
│ product_id │ source_updated_at  │ snapshotted_updated_at  │ row_has_changed  │
├────────────┼────────────────────┼─────────────────────────┼──────────────────┤
│ P001       │ 2026-08-01         │ 2026-08-01              │ false            │
└────────────┴────────────────────┴─────────────────────────┴──────────────────┘

Exercise 2 — Explain what would happen if the archived dbt_updated_at were, by mistake, a future date. Without running anything yet, reason it out: if P002's already-archived frame had dbt_updated_at = 2026-09-01 (a made-up date, later than any of Kiosko's real changes), what would happen when comparing against products_v2.csv (product_updated_at = 2026-08-15)?

Exercise 3 — Argue, in your own words, why this lesson's diagram has three branches, not two. In 2-3 sentences, explain what the third branch (new product_id) guarantees that the other two don't cover, and why that branch doesn't activate with Kiosko's data in this module.

Summary and next step

This lesson opened the mechanism behind the security-camera analogy: a snapshot compares, on every run, the source's current state against the last archived frame, and if it detects a change, executes two actions — closing the old row, opening the new one — in the same step. You saw, in plain SQL, the exact boolean question the timestamp strategy computes, and the three columns dbt generates automatically (dbt_valid_from, dbt_valid_to, dbt_scd_id), each with its specific purpose.

Before moving on you should be able to: explain, from memory, the two actions a snapshot executes when it detects a change; and say why dbt compile doesn't help you read a snapshot's comparison logic, unlike a generic test.

Lesson 3 digs into the exact criterion that decides "this row changed" — with both actually run, you're going to compare the timestamp strategy (the question you already saw here) against the check strategy (a different question, column by column), and you're going to understand why this guide chooses the first one for dim_product_snapshot.

Resources

• dbt Developer Hub — "Add snapshots to your DAG," metadata columns section (dbt_valid_from, dbt_valid_to, dbt_scd_id) — the official reference for what this lesson explains with its own SQL. docs.getdbt.com/docs/build/snapshots. In English.
• dbt Developer Hub — "dbt compile," already cited in module 4, relevant again here to confirm, by contrast, what it does and doesn't let you see about a snapshot. docs.getdbt.com/reference/commands/compile. In English.
• data-modeling-for-analytics-guide, lesson "Implementing SCD type 2 with MERGE INTO" (module 4) — the same close-and-open pattern, written by hand with explicit SQL, before this module automates it. Sibling guide in the same ecosystem.