Project: Kiosko's first dbt warehouse

Description

It's time to bring this module's seven lessons — and, with them, this whole guide's eight complete modules — together into a single, end-to-end flow. Lessons 2 and 3 wrote the five marts that were missing: dim_category, dim_order_flags, fact_sessions, fact_store_activity, mart_daily_sales_obt. Lesson 4 declared their eighteen new data_tests and confirmed the complete project structure. Lesson 5 ran dbt build over the whole project for the first time — PASS=46. Lesson 6 queried the result with real business questions. Lesson 7 closed the map toward the ecosystem's seven sibling guides. This mini-project rebuilds that complete sequence, with no pause, confirms the thirty-three inherited data_tests are still green, that fact_orders still has 40 rows and 106.15 revenue, and that mart_daily_sales_obt produces, with a single command, the same final report data-modeling-for-analytics-guide built by hand, module by module, throughout that entire guide.

And it closes with this whole body of work's final piece: Kiosko project's eighth and final version-control commit, on top of the seven modules 1 through 7 left behind.

Connection to the module. This mini-project introduces no new concept — it's the complete synthesis of lessons 2 through 7, run end to end over the real project. And, unlike the seven earlier mini-projects, this one doesn't close just a module — it closes the whole guide: kiosko_analytics/ ends this lesson as a real dbt project, with twelve models, one snapshot, thirty-three tests, generated documentation, and eight commits that tell, one by one, the complete story of how it got built.

An analogy: the finished move, with the keys handed over

This module's lesson 2 compared the pending work to five boxes still missing from a move. This mini-project is the day the move really finishes: every box is in place, every shelf has its label, the final inventory matches the blueprint — and someone takes the last walk through the complete building, from the entrance to the last room, confirming everything works before handing over the keys. That final walk, the one that confirms the whole building — not just the last room that got furnished — is still standing, is exactly what this mini-project does.

The material: the eight files that changed in this module

If you already completed lessons 2 through 7 in order, kiosko_analytics/ already has these eight files. If you're jumping straight into this mini-project, this is the complete material that needs to be in place before starting.

The five new marts, in models/marts/ (complete content in lesson 3):

• dim_category.sql
• dim_order_flags.sql
• fact_sessions.sql
• fact_store_activity.sql
• mart_daily_sales_obt.sql

models/marts/_models.yml, updated with the five new pieces' descriptions and data_tests (complete content in lesson 4), and two new singular tests in tests/:

• tests/assert_unique_store_activity_date.sql
• tests/assert_obt_revenue_matches_fact_orders.sql

No file in models/staging/kiosko/, macros/, snapshots/, or dbt_project.yml changed in this module — the same observation lesson 1 already made: a whole module's worth of new work, with not a single line touched of what modules 1 through 7 already built and tested.

Why this lesson doesn't delete kiosko.duckdb either

Just like modules 5, 6, and 7's mini-projects, this one also doesn't start with rm -f kiosko.duckdb. The reason is the same those three already explained: dim_product_snapshot is the result of a sequence of runs — products_v1.csv followed by products_v2.csv — not just the source's current state. This mini-project works over the warehouse that already exists, exactly as it was left at the end of lesson 7.

The reference solution, verified

Part 1 — dbt build, over the complete project

dbt build --vars '{"run_date": "2026-08-09"}'

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 12 models, 33 data tests, 1 snapshot, 4 sources, 502 macros

Concurrency: 4 threads (target='dev')

[... sources, staging, snapshot, dim_category, dim_order_flags, fact_sessions,
     dim_store, dim_date, fact_orders, fact_store_activity, and mart_daily_sales_obt,
     in the same DAG order as lesson 5 ...]

[... 33 data tests, all PASS ...]

Finished running 1 incremental model, 1 snapshot, 7 table models, 33 data tests, 4 view models in 0 hours 0 minutes and 0.67 seconds (0.67s).

Completed successfully

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

46 resources, 46 PASS, zero errors — the exact same number lesson 5 already confirmed. If this part fails, don't move on to Part 2 — go back to the lesson matching whichever specific resource is failing: any of the five .sql files (lesson 3), or _models.yml/the two singular tests (lesson 4).

Part 2 — Confirming fact_orders didn't change

dbt show --inline "select count(*) as n_rows, sum(revenue) as total_revenue from {{ ref('fact_orders') }}"

What to expect.

Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
|     40 |         106.15 |

40 rows, 106.15 revenue — the exact same number since module 3, confirmed again after the project doubled its number of models. No new mart in this module touched the original sales fact, not even by accident.

Part 3 — The final query: mart_daily_sales_obt's complete report

This is the piece that sums up all this guide's work — the same report data-modeling-for-analytics-guide built by hand, now reproducible by anyone with Part 1's two commands and this query:

dbt show --inline "select count(*) as n_rows, sum(revenue) as total_revenue from {{ ref('mart_daily_sales_obt') }}"
dbt show --inline "select category, sum(quantity) as units, round(sum(revenue),2) as revenue from {{ ref('mart_daily_sales_obt') }} group by category order by revenue desc"
dbt show --inline "select store_id, store_name, round(sum(revenue),2) as revenue from {{ ref('mart_daily_sales_obt') }} group by store_id, store_name order by store_id"

What to expect.

Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
|     39 |         106.15 |

Previewing inline node:
| category    | units | revenue |
| ----------- | ----- | ------- |
| beverages   |    75 |   44.05 |
| electronics |     9 |   40.50 |
| snacks      |    18 |   21.60 |

Previewing inline node:
| store_id | store_name    | revenue |
| -------- | ------------- | ------- |
| S01      | Kiosko Centro |   38.30 |
| S02      | Kiosko Norte  |   38.80 |
| S03      | Kiosko Sur    |   29.05 |

39 rows, 106.15 total revenue, broken down by category and by store — the exact same numbers lesson 6 already verified, confirmed again as part of this closing mini-project.

Part 4 — Confirming the project's complete graph

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.assert_obt_revenue_matches_fact_orders
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.relationships_fact_sessions_store_id__store_id__ref_dim_store_
kiosko_analytics.marts.relationships_fact_store_activity_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

Twenty-one nodes — not eleven, like in module 3's original tree, and not the same number as in any earlier module: always use the real number your own run gives you, never copy it from another module. The difference against module 3's eleven nodes is indirect test selection, the same one module 7 already warned about: dbt ls --select +fact_orders doesn't just bring fact_orders's direct ancestors — it also brings any test that references, in its own definition, some node that's already in the selected set. relationships_fact_sessions_store_id... and relationships_fact_store_activity_store_id... get in this way: both compare against ref('dim_store'), and dim_store is a direct ancestor of fact_orders — so dbt includes them, even though neither fact_sessions nor fact_store_activity is, itself, an ancestor of fact_orders.

Part 5 — Regenerating the complete documentation

dbt docs generate --vars '{"run_date": "2026-08-09"}'

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 12 models, 33 data tests, 1 snapshot, 4 sources, 502 macros

Concurrency: 4 threads (target='dev')

Building catalog
Catalog written to /path/to/your/kiosko_analytics/target/catalog.json

Confirm mart_daily_sales_obt ended up documented, with its complete lineage, inside manifest.json:

import json

manifest = json.load(open("target/manifest.json"))
node = manifest["nodes"]["model.kiosko_analytics.mart_daily_sales_obt"]

print("Description:", node["description"])
print("Depends on:", node["depends_on"]["nodes"])

What to expect.

Description: Daily sales OBT: fact_orders + dim_store + dim_product_snapshot (point-in-time join) + dim_date, all flattened, day + store + product grain.
Depends on: ['snapshot.kiosko_analytics.dim_product_snapshot', 'model.kiosko_analytics.fact_orders', 'model.kiosko_analytics.dim_store', 'model.kiosko_analytics.dim_date']

Four direct dependencies — one snapshot and three models — exactly what lesson 3's diagram predicted: the whole project's mart with the most dependencies, and now that claim is confirmed by reading the real artifact, not just the diagram.

Part 6 — The project's eighth and final commit

git status --short

What to expect.

 M models/marts/_models.yml
?? models/marts/dim_category.sql
?? models/marts/dim_order_flags.sql
?? models/marts/fact_sessions.sql
?? models/marts/fact_store_activity.sql
?? models/marts/mart_daily_sales_obt.sql
?? tests/assert_unique_store_activity_date.sql
?? tests/assert_obt_revenue_matches_fact_orders.sql

One modified file (_models.yml, with lesson 4's eighteen new entries) and seven new, still-untracked files — five marts, two singular tests. No complete new folder, unlike modules 4 and 5 — this module extends two folders that already existed (models/marts/, tests/), with no structure added to the project.

git add models/marts tests
git commit -m "Module 8: port dim_category, dim_order_flags, fact_sessions, fact_store_activity and mart_daily_sales_obt; run dbt build end-to-end"

What to expect.

[master b8c9d0e] Module 8: port dim_category, dim_order_flags, fact_sessions, fact_store_activity and mart_daily_sales_obt; run dbt build end-to-end
 8 files changed, 143 insertions(+), 1 deletion(-)
 create mode 100644 models/marts/dim_category.sql
 create mode 100644 models/marts/dim_order_flags.sql
 create mode 100644 models/marts/fact_sessions.sql
 create mode 100644 models/marts/fact_store_activity.sql
 create mode 100644 models/marts/mart_daily_sales_obt.sql
 create mode 100644 tests/assert_obt_revenue_matches_fact_orders.sql
 create mode 100644 tests/assert_unique_store_activity_date.sql

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

git log --oneline
git status

What to expect.

b8c9d0e (HEAD -> master) Module 8: port dim_category, dim_order_flags, fact_sessions, fact_store_activity and mart_daily_sales_obt; run dbt build end-to-end
a7b8c9d Module 7: extract calculate_revenue macro, document models and columns, generate dbt docs
f6a7b8c Module 6: make fact_orders incremental with delete+insert, prove idempotency
e5f6a7b 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

Eight commits. Each one documents, in one line, a real, verifiable milestone: the project's scaffolding, the staging layer, the star schema, the test suite, the automated SCD type 2, the conversion to incremental, the macro and the documentation, and now the complete dimensional warehouse. Any teammate who clones this repository today and runs git log --oneline can read, with no ambiguity and with nobody to ask, the complete story of how kiosko_analytics/ got built, from an empty folder to a whole dimensional warehouse.

Diagram: the complete guide, end to end

flowchart TD
    A["Module 1: dbt_project.yml, profiles.yml\nfirst model, dbt run -> PASS=1"] --> B["Module 2: sources.yml, 4 staging models\ndbt run --select staging -> PASS=4"]
    B --> C["Module 3: ref(), dim_store, dim_date, fact_orders\ndbt run -> PASS=7, 106.15"]
    C --> D["Module 4: 7 new data_tests over fact_orders\ndbt test -> PASS=15"]
    D --> E["Module 5: dim_product_snapshot, SCD type 2\ndbt snapshot x2 -> P002 historized"]
    E --> F["Module 6: incremental fact_orders\nproven idempotency -> PASS=23"]
    F --> G["Module 7: calculate_revenue, docs, lineage\ndbt docs generate -> manifest.json"]
    G --> H["Module 8: 5 remaining marts\ndbt build -> PASS=46"]
    H --> I["mart_daily_sales_obt\n39 rows, 106.15, by category and store"]
    I --> J["8 commits, complete guide"]

Common mistakes

Running dbt docs generate with no --vars, and running into the same old error. What happens: someone, in Part 5 of this mini-project, forgets --vars '{"run_date": "..."}' and sees the exact same Required var 'run_date' not found they already know from modules 6 and 7. Why it happens: it's the same old lapse, now over a project with five more models — nothing in this module's structure changed that operational requirement. How to spot it: the error message is identical, word for word, to earlier modules'. How to fix it: module 7's lesson 7 already fully explained why this behavior is correct and expected as long as the project runs by hand — this module's lesson 7 names, precisely, the guide (airflow-and-declarative-orchestration-guide) that solves this once and for all.

Thinking PASS=46 certifies the complete warehouse is ready for production. What happens: someone sees PASS=46 WARN=0 ERROR=0 and concludes Kiosko's project is already ready to operate in the real business, with nothing more to add. Why it happens: forty-six green resources feels exhaustive, after eight complete modules of work. How to spot it: review this module's lesson 7 — seven complete boundaries (orchestration, distributed computing, native time travel, streaming, governance, performance SQL, production Python) remain unsolved, each needed before this project operates with no constant human supervision, over real data, at real scale. How to fix it: PASS=46 certifies the project, as it's defined today, is correct and reproducible — not that it's ready to run with no human watching, over production volumes, forever. That's, precisely, the distinction that closes this whole guide.

Forgetting tests/ in the git add, and losing the two new singular tests. What happens: someone runs git add models/marts without also adding tests, and the two new files (assert_unique_store_activity_date.sql, assert_obt_revenue_matches_fact_orders.sql) end up left out of the eighth commit. Why it happens: most of this module's visible work lives in models/marts/ — five new files, against only two in tests/ — so it's easy to focus on the bigger folder and forget the smaller one. How to spot it: git status after the commit would still show the two tests/ files as ??, untracked. How to fix it: as this mini-project does, add both complete folders (git add models/marts tests, not a list of specific .sql files) — the same habit modules 3, 4, and 7's mini-projects already insisted on.

Exercises

Exercise 1 — Reproduce the complete mini-project from module 7's state. If you have access to a copy of the project as it was left at the end of module 7 (before this module's lesson 2), apply lesson 3's five pieces and lesson 4's _models.yml without looking at the material, and reproduce this mini-project's six parts. Confirm you land at exactly PASS=46 in the final dbt build and at 39 rows / 106.15 in mart_daily_sales_obt.

See solution

If you followed lessons 2 through 7's sequence, the result should be exactly the same: dbt build --vars '{"run_date": "..."}' ends at PASS=46 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=46, with fact_orders showing 40 rows and 106.15 revenue, and mart_daily_sales_obt showing 39 rows with the same total. If the number doesn't match, first check that fact_store_activity.sql has PARTITION BY store_id in all four window functions — lesson 3's most common mistake — and that mart_daily_sales_obt.sql joins dim_product_snapshot with the point-in-time join (order_ts >= dbt_valid_from AND order_ts < COALESCE(dbt_valid_to, ...)), not with a current-version filter.

Exercise 2 — Simulate what would happen if a teammate cloned the complete repository today, from the first commit through the eighth. With all eight commits already made, list, in order, the commands that person would need to run to reach exactly the same state you're in right now — from git clone to the final query over mart_daily_sales_obt.

See solution

1. git clone <url> — brings the complete source code: eight commits, twelve models, one snapshot, thirty-three data_tests.
2. Create their own profiles.yml (it's not in the repository, per module 1's .gitignore) pointing at a local kiosko.duckdb.
3. pip install dbt-core dbt-duckdb (or activate a virtual environment with both already installed).
4. Replicate this module's lesson 5's two-run dbt snapshot sequence — first with _sources.yml pointing at products_v1.csv, then at products_v2.csv — to rebuild dim_product_snapshot's complete history.
5. dbt build --vars '{"run_date": "..."}' — rebuilds the complete warehouse from scratch: sources, staging, snapshot, the twelve models, the thirty-three tests.
6. dbt show --inline "select ... from {{ ref('mart_daily_sales_obt') }}" — the final query, producing the same sales report, without having written a single line of this project themselves.

This six-step sequence is, precisely, the whole reason for this entire guide's eight commits: anyone with the repository reaches the same complete, versioned, tested, documented dimensional warehouse, with no dependency on you explaining anything to them in person.

Exercise 3 — Argue, in 2-3 sentences, why this mini-project — and not lesson 5 — is the whole guide's correct close. What does this mini-project add that dbt build on its own (lesson 5) hadn't demonstrated yet?

See solution

Lesson 5 demonstrated the project runs correctly, end to end, with PASS=46 — but a green PASS, as several modules in this guide already insisted on, certifies the SQL is valid, never that the result is correct for the business. This mini-project adds the missing piece: the final query over mart_daily_sales_obt, reconciled number for number against what data-modeling-for-analytics-guide already published, and the eighth commit that leaves the complete work versioned and available to any teammate. Closing with business evidence and with version control — not just with a command that ran with no errors — is, precisely, the analytics engineering discipline this guide's module 1 promised to teach from its very first line.

Summary and next step

In this mini-project you rebuilt Kiosko's complete project with dbt build (PASS=46), confirmed fact_orders stays exactly the same (40 rows, 106.15), and produced mart_daily_sales_obt's final report — 39 rows, 106.15 revenue, broken down by category and by store — with a single dbt show. You confirmed the complete graph with dbt ls --select +fact_orders (21 nodes, via indirect test selection), regenerated the project's complete documentation, and inspected mart_daily_sales_obt's real lineage in manifest.json. You closed with the project's eighth and final commit.

With this you close module 8, and with it, the complete dbt-analytics-engineering-guide. You now have a kiosko_analytics/ project with twelve models, one snapshot, thirty-three data_tests, two reusable macros, complete model- and column-level documentation, and eight commits in its history — each documenting, in one line, a real, verifiable milestone, from the first empty dbt_project.yml to a whole dimensional warehouse, reproducible by anyone with a single command.

Where you go next. This guide gave you the versioned transformation layer — sources, staging, ref(), tests, snapshots, incrementals, macros, docs, dbt build — with dbt as the vehicle. This module's lesson 7 already named, one by one, the seven sibling guides that solve what's left: airflow-and-declarative-orchestration-guide to orchestrate this same dbt build in production, spark-and-distributed-processing-guide for when volume exceeds what in-process DuckDB can hold, lakehouse-and-iceberg-guide for native storage time travel, streaming-with-kafka-and-flink-guide for real-time CDC, data-reliability-and-governance-guide for contracts published at organization scale, advanced-sql-querying-guide for deep performance, and python-for-data-engineering-guide for production Python. Every pattern you learned across these eight modules — the same ref(), the same data_tests:, the same snapshot — is the one a production dbt project uses at any scale. Choose the guide that solves the problem in front of you today, and keep building from there.

Resources

• dbt Developer Hub — "About dbt build," already cited in modules 5, 6, 7, and in this module's lesson 5, now as a summary of everything this mini-project puts into practice over the complete project. docs.getdbt.com/reference/commands/build. In English.
• dbt Developer Hub — "About documentation" and "Manifest," already cited in module 7, again confirming the complete artifact structure this mini-project inspects over twelve models. docs.getdbt.com/docs/collaborate/documentation · docs.getdbt.com/reference/artifacts/manifest-json. In English.
• Git — official git log documentation, already cited in the seven earlier modules, including the format options (--oneline) used in Part 6 of this mini-project, now over the guide's complete eight commits. git-scm.com/docs/git-log. In English.
• data-modeling-for-analytics-guide — the complete sibling guide, source of the whole dimensional model this entire guide — its eight modules — turned into a versioned, tested, documented dbt project.