Module 8 — Project: Kiosko's first dbt warehouse

Description

The seven earlier modules built, piece by piece, a real dbt project: the minimal anatomy (module 1), sources and staging (module 2), the star schema with ref() (module 3), a fifteen-data_tests suite (module 4), a snapshot that historizes dim_product with SCD type 2 (module 5), an incremental model with proven idempotency (module 6), and a reusable macro with automatically generated documentation and lineage (module 7). By the end of module 7, kiosko_analytics/ had seven models, one snapshot, fifteen data_tests, and seven commits in its history — a complete project, but one that only covers a portion of the dimensional warehouse data-modeling-for-analytics-guide designed: fact_orders, dim_store, and dim_date, nothing more.

This module closes that gap. data-modeling-for-analytics-guide designed and verified, by hand, five more pieces of the same warehouse: dim_category (the normalized product category in its own table), dim_order_flags (a junk dimension with payment method and channel), fact_sessions (an accumulating snapshot of the session funnel), fact_store_activity (a cumulative table design with 7- and 30-day rolling windows), and mart_daily_sales_obt (the wide table a BI team queries without writing a single JOIN). That SQL is already validated — this module doesn't redesign anything, it ports it to the same dbt project, with the exact same pattern modules 1 through 7 already taught you: ref(), materializations, data_tests:, nothing new to learn about dbt itself.

At the end, you're going to run dbt build once over the complete project — twelve models, one snapshot, thirty-three data_tests — and you're going to confirm, with a final query over mart_daily_sales_obt, that Kiosko now has a complete dimensional warehouse, versioned, tested, and documented, reproducible by anyone with a single command.

Connection to the previous module. Module 7 closed with an explicit promise: "module 8 is going to add four new marts... reusing exactly the same pattern modules 1 through 7 already validated — ref(), materializations, tests, documentation —, not a new pattern to learn from scratch." This module delivers on that promise to the letter. The calculate_revenue macro you extracted in module 7 stays alive inside fact_orders.sql, untouched by this module; module 4's fifteen data_tests and module 5's snapshot don't change either — this module adds on top of a foundation that already works, never rebuilds it.

An analogy: the move that brings the missing boxes

Modules 2 through 7 each used their own version of the warehouse analogy: the first complete inventory, the second floor on proven foundations, the quality inspector, the archivist, the line with a new procedure, the inventory with every shelf labeled. This module is the final move: the company that moved into this new warehouse, module by module, still has five boxes stored in the old one — the ones data-modeling-for-analytics-guide carefully packed, but never brought to this building. This module makes that last trip: it brings the five remaining boxes, arranges them on the shelves that already exist, with the same numbering and the same labeling system as the rest of the warehouse, and closes the move with a final inventory that walks the complete building, end to end, in a single pass.

What you're going to build in this module

You're going to port data-modeling-for-analytics-guide's five remaining marts to the kiosko_analytics/ project, assemble the complete project, and run it end to end:

1. The business brief: why Kiosko needs these five pieces inside the same versioned project, not as loose scripts.
2. dim_category.sql — the normalized product category (snowflake schema), with category_id/category_name, built over stg_products with the same ROW_NUMBER() OVER (ORDER BY ...) syntax you already used for dim_date.
3. dim_order_flags.sql — the junk dimension, six fixed rows (payment_method × channel), with no source() at all — a domain declared directly in SQL.
4. fact_sessions.sql — the funnel's accumulating snapshot, over the 32 events this project already declared as a source since module 2.
5. fact_store_activity.sql — the cumulative table design with rolling windows, solved with a single-SELECT SQL window function (no loop, no Python script at all).
6. mart_daily_sales_obt.sql — the final wide table, joining fact_orders + dim_store + dim_product_snapshot (point-in-time join) + dim_date.
7. dbt build run over the complete project — twelve models, one snapshot, thirty-three data_tests, all in a single pass.
8. The closing mini-project: the complete, end-to-end flow, with the guide's eighth and final commit, and the map toward the ecosystem's sibling guides.

flowchart TD
    A["state at the end of module 7\n7 models, 1 snapshot, 15 data tests, 7 commits"] --> B["M8 L2: the brief\nthe 5 missing pieces"]
    B --> C["M8 L3: dim_category, dim_order_flags,\nfact_sessions, fact_store_activity,\nmart_daily_sales_obt"]
    C --> D["M8 L4: the complete project\n12 models, _models.yml, dbt ls"]
    D --> E["M8 L5: dbt build end-to-end\nPASS=46"]
    E --> F["M8 L6: final query\nover mart_daily_sales_obt"]
    F --> G["M8 L7: the ecosystem map\n7 sibling guides"]
    G --> H["M8 L8: final mini-project\neighth commit, complete guide"]

This module's 8 lessons

#	Lesson	What it solves
1	Module introduction	This map.
2	The brief: Kiosko needs a versioned warehouse	Why data-modeling's five pieces have to live inside the dbt project.
3	Porting the rest of data-modeling's marts	The five new pieces, each with an already-known module's pattern.
4	Assembling the complete project structure	The final file tree, the complete _models.yml, dbt ls over each new mart.
5	Running dbt build end to end	The complete command, sources → snapshot → twelve models → thirty-three tests, PASS=46.
6	Querying the final mart	mart_daily_sales_obt: revenue by category, by store, reconciled against fact_orders.
7	What Kiosko still needs	The explicit map toward the ecosystem's seven sibling guides.
8	Project: Kiosko's first dbt warehouse	The closing mini-project — the whole guide, end to end, eighth commit.

This module's boundary

This module designs no new dimensional model — every mart you're going to port was already designed, justified, and verified in data-modeling-for-analytics-guide (dim_category in its module 3, dim_order_flags in its module 7, fact_sessions and fact_store_activity in its module 6, mart_daily_sales_obt in its module 3). Here, that already-validated SQL becomes dbt .sql files, with ref(), materializations, and data_tests: — the same translation work this guide's modules 2 and 3 already did with fact_orders, dim_store, and dim_date. It also doesn't build any scheduler, any distributed cluster, any native time-travel catalog, any streaming pipeline, or any data governance system — lesson 7 names, one by one, the ecosystem guides that solve each of those pieces on top of what this dbt project leaves pending.

Why the order matters: staging and sources don't change

A detail worth flagging before lesson 3: none of the five new pieces needs a new source(). dim_category gets built over stg_products (already exists since module 2); fact_sessions gets built over stg_events (also since module 2, though no earlier mart had used it yet); fact_store_activity and mart_daily_sales_obt get built over fact_orders, dim_store, and dim_date (module 3's three marts); and dim_order_flags needs no source data at all — it's a fixed domain, declared directly in SQL, six rows that never change. The staging layer you built in module 2 — and haven't touched again since — turns out to be exactly enough for everything that's left. That's, precisely, the proof that separating sources, staging, and marts into layers (module 2's rule) was the right decision from the start: a whole module's worth of new work, and the staging layer didn't need a single line changed.

Common mistakes

Thinking porting a mart means copying the Python SQL literally, with no translation. What happens: someone opens data-modeling-for-analytics-guide's lesson that built, say, fact_store_activity with a Python loop (for day in DAYS: for store_id in STORES: ...), and tries to reproduce that same loop inside a dbt .sql file. Why it happens: the sibling guide used Python to teach the cumulative table design's mechanism step by step — building each row with an individual INSERT — and that same material also includes a second, 100% SQL version, with a window function. It's easy not to notice which of the two versions is the portable one. How to spot it: dbt has no mechanism at all for running a Python loop inside a .sql model — models are, always, a single SELECT, never a sequence of imperative statements. How to fix it: every time a source mart uses a loop or a sequence of steps, look for (or build) the equivalent declarative version, with a single query — this module's lesson 3 does exactly that with fact_store_activity, using array_agg(...) OVER (...) instead of a loop.

Assuming porting the marts requires reinstalling something or changing dbt_project.yml. What happens: someone, seeing this module adds five new models, expects to have to declare something extra in dbt_project.yml — a new folder, a specific materialization setting — before writing the first .sql file. Why it happens: every earlier module added, at some point, a new folder to the project (snapshots/ in module 5, tests/ and macros/ in module 4), so it seems reasonable to expect the same here. How to spot it: check dbt_project.yml's models: block since module 3 — it already declares +materialized: table as the default for all of models/marts/, exactly what the five new marts need, with no change at all. How to fix it: this module's five new files all live inside models/marts/ — the same folder that's already existed since module 3 — there's no need to touch dbt_project.yml at all.

Believing the order you write the five new files in matters. What happens: someone assumes they have to write dim_category.sql before mart_daily_sales_obt.sql because, in a dimensional warehouse, a dimension "goes before" a fact. Why it happens: in a star-schema diagram, dimensions are usually drawn before the central fact, and it's easy to carry that visual order over to the order of writing files. How to spot it: ref() — as you already know since module 3 — is what determines the real execution order, not the order you wrote the files in your editor. mart_daily_sales_obt.sql can compile with no error at all even if you write it first, because dbt doesn't execute anything until you run dbt run or dbt build. How to fix it: write the five files in whatever order feels clearest to you pedagogically (lesson 3 presents them from the simplest dimension to the most complex) — the real execution order gets resolved by dbt on its own, from the ref()s you declare, exactly the guarantee module 3 already gave you.

Exercises

Exercise 1 — Trace each new mart's dependency before writing a single line. Based only on this module's Description, for each of the five new marts, name which existing model or models (stg_products, stg_events, fact_orders, dim_store, dim_date, dim_product_snapshot) it depends on.

See solution

dim_category depends on stg_products. dim_order_flags depends on no existing model (fixed domain). fact_sessions depends on stg_events. fact_store_activity depends on dim_store and fact_orders. mart_daily_sales_obt depends on fact_orders, dim_store, dim_product_snapshot, and dim_date — the complete project's mart with the most dependencies, consistent with being the piece that brings everything together into a single wide table.

Exercise 2 — Predict how many models the complete project is going to have by the end of this module. Module 7 closed with seven models (stg_orders, stg_events, stg_stores, stg_products, dim_store, dim_date, fact_orders). This module adds five new marts. How many total models should dbt build report in its Found ... models line?

See solution

Twelve models: the seven that already existed, plus dim_category, dim_order_flags, fact_sessions, fact_store_activity, and mart_daily_sales_obt. The snapshot (dim_product_snapshot) counts separately, in its own category — lesson 5 confirms this exact number with dbt build really run.

Exercise 3 — Argue why this module doesn't need to re-explain what an accumulating snapshot or a junk dimension is. In 2-3 sentences, explain why this module's Description can name fact_sessions and dim_order_flags with no need to re-justify, from scratch, why each one's grain is what it is.

See solution

data-modeling-for-analytics-guide already devoted complete modules to justifying each of these patterns — the accumulating snapshot in its module 6, the junk dimension in its module 7 — with Kimball's theory, the matching analogy, and executed evidence for why the chosen grain is the correct one. This guide respects that boundary explicitly: here the model is already designed and verified, and the only new work is translating it into a dbt .sql file with ref() and data_tests: — repeating the complete justification would be, precisely, the same mistake this guide's design warned about from the start: reopening a debate the sibling guide already closed.

Summary and next step

This module closes the whole guide: you're going to port five marts data-modeling-for-analytics-guide already designed and verified — dim_category, dim_order_flags, fact_sessions, fact_store_activity, mart_daily_sales_obt — to the same kiosko_analytics/ project, with the exact same pattern modules 1 through 7 already taught you. No staging piece needs to change, dbt_project.yml needs no new adjustment, and the final result is a project with twelve models, one snapshot, and thirty-three data_tests, run end to end with a single command.

Before moving on to lesson 2 you should be able to: name the five marts this module is going to port and which existing model each one depends on; and explain why none of them needs a new source().

Lesson 2 starts at the beginning: the business brief, in the words of someone at Kiosko who needs these five pieces inside the same versioned project the rest of the team already uses every day.

Resources

• dbt Developer Hub — "About dbt build," already cited in modules 5 and 6, this module's central reference for the complete project's execution order. docs.getdbt.com/reference/commands/build. In English.
• data-modeling-for-analytics-guide — modules 3, 6, and 7, the exact source of the SQL this module ports, with no model redesigned. This ecosystem's sibling guide.
• dbt Developer Hub — "How we structure our dbt projects," already cited in modules 2 and 3, again confirming why the staging layer needs no change to support five new marts. docs.getdbt.com/best-practices/how-we-structure/4-marts. In English.