Mini-project: Kiosko's SCD type 2 in dbt

Description

It's time to bring this module's seven lessons together into a single flow, start to finish, and confirm something you've only checked in pieces so far: that adding historizing to products didn't break absolutely anything modules 1 through 4 already built. This mini-project runs Kiosko's complete project with dbt build — models, snapshot, and tests, in a single command — confirms the 15 data_tests inherited from module 4 are still green, and confirms fact_orders still has exactly 40 rows and 106.15 revenue, with no relationship to the product snapshot at all.

And it closes with this module's new piece: Kiosko project's fifth commit in version control, on top of the four modules 1, 2, 3, and 4 left.

Connection to the module. This project introduces no new concept — it's the complete synthesis of lessons 2 through 7, run end to end over the project's final state: dim_product_snapshot with 5 rows, P002 historized in two versions.

An analogy: the complete inventory, with a new cabinet on the shelf

Modules 2, 3, and 4 already used the warehouse analogy for their own mini-projects: the first complete inventory (module 2), the second floor built over already-tested foundations (module 3), the inspector who checks the whole shift (module 4). This mini-project adds a different piece to that same warehouse: not a new shelf with new merchandise, but a filing cabinet — a piece of furniture that replaces nothing that already existed, it only stores, sorted by date, the earlier versions of what you already had. The rest of the warehouse — the dim_store and fact_orders shelves, the staging layer — stays in exactly the same place, with no one having moved it an inch.

Why this lesson does NOT delete kiosko.duckdb before starting

Modules 2, 3, and 4's lesson 8 started their reconciliation by deleting kiosko.duckdb and rebuilding everything from scratch (rm -f kiosko.duckdb, followed by dbt run). This lesson, on purpose, does not do that — and it's worth explaining why before moving on, because it's a real difference between how models behave and how a snapshot behaves.

A model (dim_store, fact_orders) is pure in a specific sense: its content depends only on its dependencies and its SELECT, never on its own history — deleting it and rebuilding it with dbt run always produces the same result. A snapshot does not have that property: dim_product_snapshot with 5 rows is the result of a sequence of runs — lesson 5's, over products_v1.csv, followed by lesson 6's, over products_v2.csv — not just the source's current state. If you deleted kiosko.duckdb right now and ran dbt snapshot just once — with the source already pointing at products_v2.csv, the state lesson 6 left it in — you'd get a table with 4 rows, not 5: the four products, each with a single version, the most recent one — you'd lose, with no warning message at all, P002's complete history that you built over two separate runs.

This is an important property to remember about any snapshot in production: rebuilding its table from scratch doesn't reproduce its history, only its most recent state. That's why this mini-project works on top of the warehouse that already exists, exactly as lesson 6 left it — with the two runs already applied — instead of rebuilding it from scratch the way earlier modules did.

The reference solution, verified

Part 1 — Confirm the inherited state before touching anything

Before running any new command, confirm dim_product_snapshot is still at lesson 6's exact result:

import duckdb
con = duckdb.connect("kiosko.duckdb")
print(con.sql("select count(*) as total_rows from dim_product_snapshot"))
print(con.sql("select count(*) as p002_rows from dim_product_snapshot where product_id = 'P002'"))

What to expect.

┌────────────┐
│ total_rows │
├────────────┤
│          5 │
└────────────┘

┌───────────┐
│ p002_rows │
├───────────┤
│         2 │
└───────────┘

If this doesn't match, don't move on to Part 2 — go back to lesson 5 or 6, whichever run didn't get applied correctly.

Part 2 — dbt build: models, snapshot, and tests, in a single command

dbt build

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 1 snapshot, 15 data tests, 4 sources, 501 macros

Concurrency: 4 threads (target='dev')

1 of 23 START sql table model main.dim_date ..................................... [RUN]
2 of 23 START sql view model main.stg_events .................................... [RUN]
3 of 23 START sql view model main.stg_orders .................................... [RUN]
4 of 23 START sql view model main.stg_products ................................... [RUN]
1 of 23 OK created sql table model main.dim_date ................................ [OK in 0.11s]
2 of 23 OK created sql view model main.stg_events ................................ [OK in 0.09s]
4 of 23 OK created sql view model main.stg_products .............................. [OK in 0.10s]
5 of 23 START sql view model main.stg_stores ..................................... [RUN]
6 of 23 START snapshot main.dim_product_snapshot ................................. [RUN]
3 of 23 OK created sql view model main.stg_orders ................................ [OK in 0.10s]
[WARNING]: Data type of snapshot table timestamp columns (TIMESTAMP) doesn't match derived column 'updated_at' (DATE). Please update snapshot config 'updated_at'.
5 of 23 OK created sql view model main.stg_stores ................................ [OK in 0.11s]
6 of 23 OK snapshotted main.dim_product_snapshot .................................. [OK in 0.15s]
15 of 23 START sql table model main.dim_store ..................................... [RUN]
15 of 23 OK created sql table model main.dim_store ................................ [OK in 0.01s]
16 of 23 START sql table model main.fact_orders ................................... [RUN]
16 of 23 OK created sql table model main.fact_orders ............................... [OK in 0.02s]
[... 15 data tests, all PASS, the same count as module 4 ...]

Finished running 1 snapshot, 3 table models, 15 data tests, 4 view models in 0 hours 0 minutes and 0.46 seconds (0.46s).

Completed successfully

Done. PASS=23 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=23

23 resources, 23 PASS, zero errors. Notice something important about the execution order: dbt build runs dim_product_snapshot alongside the staging models, not before or after everything else as a separate block — because dim_product_snapshot depends directly on source('kiosko_raw', 'products'), not on any model, so dbt schedules it as soon as the DAG allows, in parallel with stg_orders, stg_events, and the rest. This is the same benign WARNING from lessons 5 and 6, and it's still not a problem: dbt build runs exactly the same 15 data_tests from module 4 (none new — this module added no tests over the snapshot), and they're all still PASS.

Confirm dim_product_snapshot's count once more, now that it ran inside a complete dbt build:

con = duckdb.connect("kiosko.duckdb")
print(con.sql("select count(*) as total_rows from dim_product_snapshot"))

What to expect.

┌────────────┐
│ total_rows │
├────────────┤
│          5 │
└────────────┘

Still at 5 — dbt build ran dbt snapshot internally, compared against what was already archived, found no new change (the source is still on products_v2.csv, unmodified), and added no row. If this part fails, don't move on to Part 3 — go back to the lesson matching whichever specific resource is failing.

Part 3 — Confirm fact_orders has no relationship to the snapshot

This is this mini-project's central check: fact_orders never uses dim_product_snapshot — it still joins stg_orders only with dim_store and dim_date, exactly as module 3 left it — so adding historizing to products shouldn't have changed absolutely anything about its result.

print(con.sql("select count(*) as n_rows, sum(revenue) as total_revenue from fact_orders"))

What to expect.

┌────────┬────────────────┐
│ n_rows │ total_revenue  │
├────────┼────────────────┤
│     40 │         106.15 │
└────────┴────────────────┘

40 rows, 106.15 revenue — the exact same numbers from module 4 (and module 3, and data-modeling-for-analytics-guide). Nothing this module built touched the sales fact table, not even by accident. This is exactly what's expected of good layer design: a product catalog's history and the sales transaction record are completely separate responsibilities, and verifying it with a concrete number — not just the absence of errors — is the same discipline module 3 already insisted on in its own mini-project.

Part 4 — Inspecting the complete DAG with dbt ls

dbt ls --select dim_product_snapshot fact_orders

What to expect.

kiosko_analytics.dim_product_snapshot
kiosko_analytics.marts.fact_orders

Two resources, with no dependency relationship between them — dbt ls --select +fact_orders would still show exactly the same tree you already know since module 4, with no trace of dim_product_snapshot in it. Confirm it:

dbt ls --select +fact_orders

What to expect.

kiosko_analytics.marts.dim_date
kiosko_analytics.marts.dim_store
kiosko_analytics.marts.fact_orders
kiosko_analytics.staging.kiosko.stg_orders
kiosko_analytics.staging.kiosko.stg_stores
source:kiosko_analytics.kiosko_raw.orders
source:kiosko_analytics.kiosko_raw.stores
kiosko_analytics.marts.accepted_values_fact_orders_product_id__P001__P002__P003__P004
kiosko_analytics.assert_no_negative_revenue
kiosko_analytics.marts.is_positive_fact_orders_quantity__True
kiosko_analytics.marts.is_positive_fact_orders_unit_price__False
kiosko_analytics.marts.not_null_fact_orders_order_id
kiosko_analytics.staging.kiosko.not_null_stg_orders_order_id
kiosko_analytics.staging.kiosko.not_null_stg_stores_store_id
kiosko_analytics.marts.relationships_fact_orders_store_id__store_id__ref_dim_store_
kiosko_analytics.marts.unique_fact_orders_order_id
kiosko_analytics.staging.kiosko.unique_stg_orders_order_id
kiosko_analytics.staging.kiosko.unique_stg_stores_store_id

Eighteen resources, not eleven. The eleven-resource tree was module 3's — before any data_test existed over fact_orders; module 4 added seven new resources on top of that same tree: the six column data_tests (unique/not_null on order_id, accepted_values on product_id, relationships on store_id, is_positive on quantity and on unit_price) plus the singular test assert_no_negative_revenue. Add it up: module 3's eleven plus module 4's seven gives exactly eighteen — and that number didn't change in this module, because dim_product_snapshot doesn't show up anywhere: fact_orders never references it.

Part 5 — The final query: P002's complete history

Close with the query that sums up all this module's work — the same one you already saw in lesson 6, now as the final confirmation that the complete project, run end to end with dbt build, produces the correct result:

dbt show --inline "select product_id, category, unit_cost, dbt_valid_from, dbt_valid_to from {{ ref('dim_product_snapshot') }} order by product_id, dbt_valid_from"

What to expect.

Previewing inline node:
| product_id | category      | unit_cost | dbt_valid_from | dbt_valid_to |
| ---------- | ------------- | --------- | --------------- | ------------- |
| P001       | beverages     |      0.40 |      2026-08-01 |               |
| P002       | snacks        |      0.60 |      2026-08-01 |    2026-08-15 |
| P002       | health-snacks |      0.68 |      2026-08-15 |               |
| P003       | beverages     |      0.35 |      2026-08-01 |               |
| P004       | electronics   |      2.10 |      2026-08-01 |               |

Five rows, P002 with two versions — the same result, number for number, data-modeling-for-analytics-guide produced by hand with MERGE INTO, now reproducible by anyone with three dbt commands (dbt snapshot, change external_location, dbt snapshot again) instead of a hand-written MERGE.

Part 6 — The project's fifth commit

Check what changed since module 4's commit:

git status --short

What to expect.

 M models/staging/kiosko/_sources.yml
?? raw_data/kiosko/products_v2.csv
?? snapshots/

One modified file (_sources.yml, with products's external_location now pointing at products_v2.csv) and two new, still-untracked paths (products_v2.csv, the catalog with P002's real change; snapshots/, with dim_product_snapshot.yml). Notice products_v1.csv does not show up in this list — it's still exactly as it was since module 2, with no change — and neither does kiosko.duckdb — module 1's .gitignore is still doing its job, with you never having touched it.

git add models/staging/kiosko/_sources.yml raw_data/kiosko/products_v2.csv snapshots
git commit -m "Module 5: snapshot dim_product with SCD type 2 over the P002 change"

What to expect.

[master e5f6a7b] Module 5: snapshot dim_product with SCD type 2 over the P002 change
 3 files changed, 15 insertions(+), 2 deletions(-)
 create mode 100644 raw_data/kiosko/products_v2.csv
 create mode 100644 snapshots/dim_product_snapshot.yml

(The commit's short identifier, e5f6a7b in this example, is going to be different on your machine — as you already saw in earlier modules, it's a hash generated from the exact content and the moment of the commit.) Confirm the complete history and a clean working tree:

git log --oneline
git status

What to expect.

e5f6a7b (HEAD -> master) Module 5: snapshot dim_product with SCD type 2 over the P002 change
d4e5f6a Module 4: add data tests to fact_orders, a custom generic test and a singular test
c3d4e5f Module 3: rebuild dim_store, dim_date and fact_orders with ref()
a1b2c3d Module 2: declare Kiosko sources and build the staging layer
af0b710 First dbt project: kiosko_analytics scaffolding

On branch master
nothing to commit, working tree clean

Five commits, each documenting a real, verifiable milestone: the project's scaffolding, the staging layer, the star schema, the test suite, and now the first time Kiosko's project remembers its own past.

Diagram: the mini-project's complete flow

flowchart TD
    A["state at the end of module 4\n7 models, 15 data tests, 4 commits"] --> B["dim_product_snapshot.yml (lesson 4)\nstrategy=timestamp"]
    B --> C["dbt snapshot #1 over products_v1.csv (lesson 5)\n4 rows, all open"]
    C --> D["products_v2.csv + external_location (lesson 6)"]
    D --> E["dbt snapshot #2 (lesson 6)\n5 rows, P002 with 2 versions"]
    E --> F["currency and point-in-time queries (lesson 7)"]
    F --> G["dbt build -> PASS=23\n(7 models + 1 snapshot + 15 tests)"]
    G --> H["fact_orders still at 40 rows, 106.15\n(no relationship to the snapshot)"]
    H --> I["git add + commit -> fifth commit"]

Common mistakes

Running rm -f kiosko.duckdb out of habit from earlier modules, and losing P002's history. What happens: someone, following modules 2, 3, and 4's mini-project pattern, deletes kiosko.duckdb before this mini-project, without reading the "Why this lesson does NOT delete kiosko.duckdb" section's explanation. Why it happens: it's the same first step that worked perfectly three modules in a row — nothing in the workflow warns, on its own, that this time is different. How to spot it: after a dbt build over a freshly deleted database, dim_product_snapshot has 4 rows, not 5, and P002 shows up with a single version (health-snacks/0.68), with no trace of the snacks/0.60 version. How to fix it: if this happened to you, you have to reproduce lessons 5 and 6's complete sequence — first with external_location pointing at products_v1.csv, dbt snapshot, then switching to products_v2.csv, dbt snapshot again — to rebuild the complete history; there's no single-command shortcut that rebuilds it, precisely because the history depends on the sequence, not just the final state.

Interpreting PASS=23 as confirmation that the snapshot is well designed. What happens: someone sees PASS=23 in dbt build and considers the module closed, without running Part 5's specific check — querying dim_product_snapshot directly and counting its rows. Why it happens: 23 green lines feel exhaustive, but, as modules 2, 3, and 4 already insisted, PASS only confirms the SQL is syntactically valid and the declared tests pass — not that the snapshot captured the correct history. How to spot it: dbt build would report PASS=23 with the same confidence if dim_product_snapshot had 4 rows instead of 5 (the previous mistake's scenario) — no test in this module checks P002's exact version count. How to fix it: this mini-project's Part 5 — the direct query, with the explicit row count — is the real check, not PASS=23 by itself.

Adding a data_tests over dim_product_snapshot expecting the module to ask for it. What happens: someone, used to module 4, tries to declare unique or not_null over dim_product_snapshot's columns in a new _models.yml inside snapshots/. Why it happens: every module so far added new tests to what it built, so it seems natural to repeat the pattern. How to spot it: this lesson's dbt build count says 15 data tests, exactly module 4's same number — this module, on purpose, adds no new test. How to fix it: it isn't a mistake to add your own tests over the snapshot as a personal exercise — for instance, unique over dbt_scd_id would be a reasonable check — but it isn't part of this module's contract; if your dbt build count doesn't match 15 data tests, check whether you added something extra before assuming something's broken.

Exercises

Exercise 1 — Reproduce the complete mini-project from module 4's state. If you have access to a copy of the project exactly as it was at the end of module 4 (before this module's lesson 4), rebuild lessons 4 through 7 without looking at the material — declare the snapshot, run both versions, query the result — and confirm you land exactly at PASS=23 in dbt build and 5 rows in dim_product_snapshot.

See solution

If you followed lessons 4 through 7's sequence, the result should be exactly the same: dbt build ends at PASS=23 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=23, and dim_product_snapshot has 5 rows, P002 with 2. If the number doesn't match, first verify you ran dbt snapshot twice, with the external_location change in between, in that exact order — the most common mistake is running dbt snapshot only once after already switching to products_v2.csv, which gives a correct frame but with no history at all, because there was never a prior run over products_v1.csv to compare against.

Exercise 2 — Confirm with dbt ls that the snapshot doesn't block anything in the rest of the project. Temporarily break snapshots/dim_product_snapshot.yml (for instance, with a YAML indentation error), and run dbt build. What happens to the rest of the project's 22 resources?

See solution

dbt build (or even dbt parse) fails immediately with a YAML schema validation error, before attempting to build any resource — unlike a compilation error in a single model (which only affects that model and whatever depends on it), a malformed YAML keeps dbt from finishing parsing the complete project, so no resource runs, not even ones with no relationship to the snapshot at all. This is different from the behavior you already saw in module 3 (a cascading SKIP only for models depending on the one that failed) — a project-level syntax error is more serious than a logic error inside a single model. Fix the YAML before continuing.

Exercise 3 — Argue why this mini-project is the right starting point for module 6. In 2-3 sentences, explain what guarantees having dim_product_snapshot already built, tested inside dbt build, and versioned gives you, before module 6 starts turning fact_orders into an incremental model.

See solution

Module 6 is going to change how fact_orders gets rebuilt on every run — from "complete table every time" to "only the new partition" — a change that directly touches the materialization of a model you've already used since module 3. Having dim_product_snapshot already built and confirmed as completely independent of fact_orders (this mini-project's Part 3) means any change module 6 makes to fact_orders's incrementality has no way to affect, or be affected by, the product history — two pieces of the project that can each evolve at their own pace, with no interference between them.

Summary and next step

In this mini-project you confirmed the complete Kiosko project — seven models, one snapshot, fifteen data_tests — runs end to end with a single command (dbt build, PASS=23), that dim_product_snapshot keeps the complete history from the two earlier runs (5 rows, P002 with 2 versions), and that fact_orders didn't change at all (40 rows, 106.15 revenue) — the separation of responsibilities between "the product catalog remembers its past" and "the sales record stays the same" was verified with concrete numbers, not just the absence of errors. You closed the module with the project's fifth commit.

With this you close module 5. You now have a kiosko_analytics/ project with eight more resources than at the end of module 4 — seven unchanged models, one new snapshot with real history — fifteen inherited data_tests with no change, and five commits in its history, each documenting a real, verifiable milestone.

Where you go next. Module 6 changes the question: so far, fact_orders gets rebuilt whole every time you run dbt run — seven staging rows read again, two JOINs recalculated from scratch, every time. With forty rows, that was never a real performance problem; but the pattern doesn't scale if Kiosko had millions of orders instead of forty. You're going to meet incremental models — the is_incremental() macro, the append/delete+insert/merge strategies — and you're going to turn fact_orders so it only processes the new data partition on each run, proving with evidence that running it twice over the same date doesn't duplicate any row. dim_product_snapshot doesn't change at all from here on — it stays exactly as this module left it, the whole project's first resource to remember its own past.

Resources

• dbt Developer Hub — "About dbt build," already cited in this module's design, confirms the exact execution order (sources → snapshots → models → tests, in DAG order) you verified in Part 2. docs.getdbt.com/reference/commands/build. In English.
• dbt Developer Hub — "Add snapshots to your DAG," again the module's central reference, now cited as a summary of everything this mini-project puts into practice. docs.getdbt.com/docs/build/snapshots. In English.
• Git — official git log documentation, including the formatting options (--oneline) used in this mini-project's Part 6, already cited in modules 1 through 4. git-scm.com/docs/git-log. In English.