Module overview: from a star schema you trust to a star schema that remembers its past

Description

Module 4 closed with 15 data_tests running green over seven models — four staging views, three marts materialized as tables — a suite verified against both healthy data and deliberately broken data, and four commits in kiosko_analytics/'s history. You trust the project: you know that if something breaks, dbt test warns you with a specific row, in a specific table, for a specific reason.

But there's a question none of the fifteen tests you already wrote can answer: what did dim_product look like last week? Since module 2, stg_products — and anything that depended on it — always reflects products_v1.csv's current state. If that file changed tomorrow, stg_products would show the new value immediately, with no trace left of the previous value. There's no bug in that — it's exactly what's expected of a view, the live window you already met in module 3 — but it's a real limitation: a product catalog that only knows its present can't answer any question about its past.

data-modeling-for-analytics-guide already solved this problem — by hand. It historized dim_product with SCD type 2, first with two hand-ordered statements (UPDATE followed by INSERT), then with MERGE INTO, the statement DuckDB designed specifically to automate that pattern. You ran that MERGE twice over the same real Kiosko change: P002 (Energy Bar) has its unit_cost go up (0.60 → 0.68) and its category change (snacks → health-snacks) on August 15, 2026. In the end, dim_product_scd had five rows — four products, one of them with two versions — each with its exact validity range.

This module automates that exact same pattern, over the same real change, with the tool dbt built for this: the snapshot. You're going to declare dim_product_snapshot, run dbt snapshot a first time over the unchanged catalog, switch the source to the catalog with P002's real change, and run dbt snapshot a second time — and you're going to see, with the literal output of both runs, that dbt produces the same final result as the manual MERGE INTO: two versions of P002, each with its validity range, without you having written a single line of UPDATE or INSERT.

Connection to the module. Module 4 protected what already existed — tests on columns that don't change shape. This module adds something Kiosko's project never had: memory. dim_product_snapshot is going to coexist with the seven models you already know, without touching them — fact_orders still has 40 rows, 106.15 revenue, and its 15 data_tests are still green, exactly as module 4 left them — and you're going to confirm it in this module's lesson 8.

An analogy: the security camera, not the one-time photo

Module 3 already used the photography analogy to explain the table materialization: dbt runs your SELECT once, at the moment you run dbt run, and saves that result — a still photo, taken a single time, that doesn't change until you decide to take another one. A dbt snapshot is different, and it's worth a new analogy so you don't confuse the two: it isn't a one-time photo, it's a security camera, the kind that records a frame every so often and archives all of them, deleting none.

Every time you run dbt snapshot, the camera takes a new frame of Kiosko's product catalog and compares it against the last archived frame. If nothing changed, nothing happens — the frame archive doesn't grow for no reason. If something changed — P002's price, its category — the camera doesn't erase the old frame and replace it with the new one: it archives both, each with the exact timestamp at which it stopped being the current frame. Over time, the complete frame archive lets you reconstruct, for any moment in the past, exactly what the catalog looked like at that instant — the same question dim_product, with no snapshot, could never answer.

That camera, stated without the metaphor, is exactly the archivist data-modeling-for-analytics-guide described when it closed its own SCD type 2 module: someone you used to hire by the job — running UPDATE and INSERT by hand, in the right order, every time a product changed — and that this module hires full-time. Every time you call them (dbt snapshot), they compare the new folder against the old one and archive only the change.

This module's map

Lesson    Question it answers
────────  ──────────────────────────────────────────────────────────
L1        (this one) What this module is about, and how it relates
          to data-modeling's manual MERGE INTO
L2        What a dbt snapshot does, exactly -- the compare-and-
          archive mechanism behind the command
L3        timestamp vs check: the two strategies dbt offers to
          decide "did this row really change?"
L4        Configuring dim_product_snapshot: unique_key, strategy,
          updated_at, and why source() and not stg_products
L5        Running dbt snapshot for the first time, over the
          unchanged catalog
L6        Switching the source to the catalog with P002's real
          change, and running dbt snapshot a second time
L7        Querying dim_product_snapshot: the current version, and
          the version as of any past date
L8        Project: Kiosko's complete snapshot, verified alongside
          the rest of the project, and the fifth commit

The progression repeats the pattern you already know from modules 2, 3, and 4: first the mechanism, in isolation (lessons 2 and 3), then the concrete configuration applied to Kiosko (lesson 4), then the real execution in two acts — before and after the change (lessons 5 and 6), then how to read the result with judgment (lesson 7), and last the project that confirms none of the above broke (lesson 8).

What you're going to build, previewed

By the end of this module you're going to have a new file, snapshots/dim_product_snapshot.yml, with this exact configuration:

Key	Value	What it decides
relation	source('kiosko_raw', 'products')	where the snapshot reads from every time it runs — the raw source, not stg_products
unique_key	product_id	which column identifies "the same product" from one run to the next
strategy	timestamp	how it decides whether a row changed — by comparing a date column, not column by column
updated_at	product_updated_at	the date column that comparison uses — the data's date, never the system clock's

And you're going to run dbt snapshot twice, over exactly the same change data-modeling-for-analytics-guide already used:

	Run 1 (lesson 5)	Run 2 (lesson 6)
Source	products_v1.csv	products_v2.csv
P002.category	snacks	health-snacks
P002.unit_cost	0.60	0.68
P002.product_updated_at	2026-08-01	2026-08-15
Total rows in dim_product_snapshot	4	5
P002 rows	1 (open)	2 (one closed, one open)

At the end, SELECT * FROM dim_product_snapshot WHERE product_id = 'P002' is going to return exactly two rows — one with dbt_valid_from = 2026-08-01 and dbt_valid_to = 2026-08-15 (the snacks/0.60 version, already closed), another with dbt_valid_from = 2026-08-15 and dbt_valid_to = NULL (the health-snacks/0.68 version, current). Name that equivalence now, because you're going to come back to it in every lesson of this module: dbt_valid_from/dbt_valid_to/dbt_scd_id are the automatic equivalents — generated by dbt, with you never defining them — of the valid_from/valid_to/is_current columns data-modeling-for-analytics-guide wrote by hand. dbt_valid_to IS NULL means exactly the same thing is_current = true meant there.

The boundary: what does NOT belong in this module

A dbt snapshot historizes with metadata columns over an ordinary DuckDB table — it isn't a native property of the storage format. dbt_valid_from, dbt_valid_to, and dbt_scd_id are columns the snapshot materialization adds and maintains by convention — DuckDB, as an engine, knows nothing special about them; to DuckDB they're three more columns. There's another family of solutions to the same problem — native time travel in the table format, where the storage engine itself (Apache Iceberg, Delta Lake) versions every write with no external tool having to maintain "valid from/to" columns — and that family is lakehouse-and-iceberg-guide's complete territory, the sibling guide that picks up exactly where this module ends. This module doesn't implement it; it names the difference precisely where relevant (lesson 7) and moves on.

Nor does any of the following belong in this module — it arrives in the following ones: incremental models so fact_orders doesn't get rebuilt whole on every run (module 6), transformation macros and generated documentation (module 7), or real CDC or streaming over the product catalog — this module's whole flow is still batch, over fixed files you change by hand from one run to the next, never over a continuous stream (streaming-with-kafka-and-flink-guide's territory).

Common mistakes

Thinking a snapshot is "just another table," the same as dim_store or fact_orders. What happens: someone, seeing dim_product_snapshot show up as a queryable table in kiosko.duckdb, treats it exactly like any mart — for instance, expecting dbt run to rebuild it, or a normal --full-refresh to empty it and refill it from scratch with nothing lost. Why it happens: dim_product_snapshot really is a real table, queryable with normal SQL, and that surface similarity to dim_store or fact_orders invites treating it the same. How to spot it: if you run dbt run (without dbt snapshot) expecting that to update the product history, nothing is going to happen — a snapshot lives under its own command, with its own lifecycle, which this module covers lesson by lesson. How to fix it: a snapshot runs with dbt snapshot, never with dbt run — they're two different commands, for two different resource types, even though both end up creating tables in the same warehouse.

Expecting this module to re-justify why dim_product needs historizing. What happens: someone who didn't go through data-modeling-for-analytics-guide looks, in this module, for a complete explanation of what SCD type 2 is and why it matters. Why it happens: it's reasonable to expect every module to be self-contained. How to spot it: if you look here for a formal definition of SCD type 2 or a business justification for historizing dim_product, you're not going to find it with the same detail — this guide assumes you already saw it. How to fix it: this module implements with dbt what data-modeling-for-analytics-guide already designed and justified by hand; if any of the theory isn't familiar, that's the guide to review first — here the focus is exclusively how it gets automated with snapshots.

Assuming the snapshot needs stg_products, because it already exists since module 2. What happens: someone, used to module 3's marts being built with ref() over staging models, writes relation: ref('stg_products') instead of relation: source('kiosko_raw', 'products'). Why it happens: ref() is the mechanism you've already used over and over since module 3, and it seems natural to keep following that same pattern. How to spot it: lesson 4 explains, with evidence, why this guide — following dbt's official recommendation — snapshots the raw source, not a transformed model; if you build your own project and snapshot ref('stg_products'), it's going to work mechanically, but you're going to lose an important guarantee lesson 4 explains in detail. How to fix it: wait for lesson 4 for the full explanation; for now, it's enough to know this module's pattern points at the source, not at the staging model.

Exercises

Exercise 1 — Match dbt's columns to data-modeling's. Without having read any of this module's lessons yet, fill in the table: which dbt_valid_from/dbt_valid_to/dbt_scd_id column corresponds to valid_from/valid_to/is_current from data-modeling-for-analytics-guide? (Hint: dbt_scd_id doesn't have a direct equivalent among those three — think about what new guarantee it might be giving.)

See solution

dbt_valid_from corresponds to valid_from (since when this version of the row is current); dbt_valid_to corresponds to valid_to (until when it was, NULL if it's still current); and the absence of a value in dbt_valid_to (IS NULL) corresponds exactly to is_current = true. dbt_scd_id has no direct equivalent in data-modeling-for-analytics-guide's table, because it solves a different problem: it's a unique key, generated by dbt, for each version of each row — a kind of automatic product_key, with you never having to declare a sequence (CREATE SEQUENCE) the way data-modeling-for-analytics-guide did by hand. This module's lesson 2 comes back to this in more detail.

Exercise 2 — Predict the final row count. products_v1.csv has 4 products. Only P002 changes, a single time, between products_v1 and products_v2. Without looking at this lesson's "What you're going to build" table, predict how many rows dim_product_snapshot is going to have after this module's two dbt snapshot runs.

See solution

5 rows: the four original products, plus one additional row for P002 (its second version). P001, P003, and P004 never change between products_v1 and products_v2, so each keeps exactly one row — the one created in the first run — only P002 ends up with two, one closed (dbt_valid_to = 2026-08-15) and one current (dbt_valid_to = NULL). The same result, number for number, data-modeling-for-analytics-guide already confirmed with its manual MERGE INTO.

Exercise 3 — Explain, in your own words, the difference between the security-camera analogy and module 3's photography one. In 2-3 sentences, using both analogies, explain why a dbt snapshot isn't the same as a table materialized with materialized: table, even though both end up storing physical data in the warehouse.

See solution

A materialized table (table) is a one-time photo: every dbt run replaces it completely with the SELECT's most recent result, keeping no trace of the previous version — perfect for representing something's current state, like dim_store or fact_orders. A snapshot is a security camera: every dbt snapshot replaces nothing, it adds a new frame only if something changed, and it keeps every previous frame — the central difference is that a normal table forgets its past on every run, while a snapshot exists, precisely, to never forget it.

Summary and next step

This module closes the distance between "Kiosko's project reflects the present" and "Kiosko's project remembers its past." You saw the map of the 8 lessons, the complete configuration you're going to declare (unique_key, strategy, updated_at, pointing at the raw source), and the exact result you're going to confirm twice: dim_product_snapshot with 5 rows, P002 historized in two versions — the same result, number for number, data-modeling-for-analytics-guide already produced by hand with MERGE INTO.

Before moving on you should be able to: name the equivalence between the three columns dbt generates automatically (dbt_valid_from, dbt_valid_to, dbt_scd_id) and the three data-modeling-for-analytics-guide wrote by hand (valid_from, valid_to, is_current); and explain, without looking at the code yet, why a snapshot runs under its own command (dbt snapshot), not dbt run.

Lesson 2 opens up the complete mechanism: what a dbt snapshot does, step by step, every time it runs — the technical answer behind this lesson's security-camera analogy.

Resources

• dbt Developer Hub — "Add snapshots to your DAG," this whole module's central reference: what a snapshot is, its two strategies, and the metadata columns it generates. docs.getdbt.com/docs/build/snapshots. In English.
• data-modeling-for-analytics-guide, module 4 (module-04-slowly-changing-dimensions) — the design and manual implementation of SCD type 2 over the same P002 change this module automates. Sibling guide in the same ecosystem.
• Kimball Group — "Slowly Changing Dimension Type 2" — the formal definition data-modeling-for-analytics-guide already cited, and this module assumes known. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English.