Module 8: Project Kioskos Dbt Warehouse
Porting the rest of data-modeling's marts
Description
This is the lesson where lesson 2's brief turns into real code. You're going to write five new .sql files, all inside models/marts/ — the same folder that's already had dim_store, dim_date, and fact_orders since module 3 — each applying a pattern from a module you already know: ref() for dependencies, table materialization (the project default since module 3), and a declarative query that produces, number for number, the same result data-modeling-for-analytics-guide already verified by hand.
Connection to the module. Lesson 2 explained why these five pieces need to live inside kiosko_analytics/. This lesson solves the how, mart by mart, in the same order lesson 1 presented them: from the simplest dimension (dim_category) to the most complete mart (mart_daily_sales_obt). Each one runs and gets verified individually — dbt run --select <name> — before lesson 5 runs them all together with dbt build.
An analogy: five puzzle pieces, each already assembled in its own box
Lesson 2 compared the five pieces to boxes still missing from a move. This lesson is the moment of opening each box, one by one, and putting its contents on the right shelf. No box arrives empty or half-assembled — each one already has, inside, the complete design data-modeling-for-analytics-guide verified; this lesson's work is, precisely, deciding which shelf each one goes on (ref() to which existing model) and with what label (data_tests:, lesson 4 completes that part). No box needs repacking from scratch.
Piece 1: dim_category — the normalized category, over stg_products
data-modeling-for-analytics-guide, in its module 3, normalized category out of dim_product with ROW_NUMBER() OVER (ORDER BY category) over a DISTINCT subquery. That same logic, here, rests on stg_products — the staging model that's already existed since module 2 — instead of a table loaded by hand in Python:
-- models/marts/dim_category.sql
select
row_number() over (order by category) as category_id,
category as category_name
from (select distinct category from {{ ref('stg_products') }}) as tNotice a detail lesson 2 already hinted at: stg_products reads from source('kiosko_raw', 'products'), and that source has pointed at products_v2.csv since module 5 changed the external_location — the same real P002 change (snacks → health-snacks) the snapshot historizes. As a result, dim_category reflects the current catalog, not products_v1.csv's original one.
Run the model individually:
dbt run --select dim_categoryWhat 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')
1 of 1 START sql table model main.dim_category ................................. [RUN]
1 of 1 OK created sql table model main.dim_category ............................ [OK in 0.06s]
Finished running 1 table model in 0 hours 0 minutes and 0.14 seconds (0.14s).
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1
Verify the content:
dbt show --inline "select * from {{ ref('dim_category') }} order by category_id"What to expect.
Previewing inline node:
| category_id | category_name |
| ----------- | -------------- |
| 1 | beverages |
| 2 | electronics |
| 3 | health-snacks |
Three categories, not four. data-modeling-for-analytics-guide (module 3, over products_v1.csv) also reported three distinct categories, but one of them was snacks, not health-snacks — the name changed because this project reads the catalog after the P002 change module 5 already applied. This difference isn't a translation error: it's the correct, expected consequence of dim_category, unlike the original example, being built over data that already reflects August 15, 2026. If dim_category had been built over dim_product_snapshot instead of stg_products, there would be four categories — beverages, electronics, snacks (P002's closed version) and health-snacks (the current one) — because the snapshot keeps the complete history. This lesson chooses stg_products, the current catalog, on purpose: dim_category answers "what categories exist today?", not "what categories ever existed?" — that second question belongs to the snapshot, not to this dimension.
Piece 2: dim_order_flags — the junk dimension, with no source at all
data-modeling-for-analytics-guide, in its module 7, built dim_order_flags as the complete cartesian product of payment_method (cash, card, wallet) and channel (in_store, app) — six fixed rows, precomputed once, with no dependency on any source data. That same idea, in dbt, gets declared with a VALUES clause directly in the SELECT, with no ref() or source() at all:
-- models/marts/dim_order_flags.sql
select *
from (
values
(1, 'cash', 'in_store'),
(2, 'cash', 'app'),
(3, 'card', 'in_store'),
(4, 'card', 'app'),
(5, 'wallet', 'in_store'),
(6, 'wallet', 'app')
) as t(flag_key, payment_method, channel)This is the only one of the five marts that depends on no other Kiosko model at all — the same reason data-modeling-for-analytics-guide called it a junk dimension: the complete domain (three payment methods, two channels) is known ahead of time, so there's no need to read it from any file.
dbt run --select dim_order_flagsWhat to expect.
1 of 1 START sql table model main.dim_order_flags .............................. [RUN]
1 of 1 OK created sql table model main.dim_order_flags ......................... [OK in 0.05s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1
dbt show --inline "select * from {{ ref('dim_order_flags') }} order by flag_key" --limit 10What to expect.
Previewing inline node:
| flag_key | payment_method | channel |
| -------- | -------------- | -------- |
| 1 | cash | in_store |
| 2 | cash | app |
| 3 | card | in_store |
| 4 | card | app |
| 5 | wallet | in_store |
| 6 | wallet | app |
Six rows, exactly the same cartesian product data-modeling-for-analytics-guide built with a double for loop in Python — the VALUES clause is its declarative equivalent: instead of iterating over two lists and accumulating rows in a loop, you directly declare the complete table, all at once.
Piece 3: fact_sessions — the accumulating snapshot, over stg_events
data-modeling-for-analytics-guide, in its module 6, built fact_sessions with the MAX(CASE WHEN event_type = '...' THEN event_ts END) pattern grouped by session_id, over Kiosko's 32 canonical events — the same ones this project has already declared as source('kiosko_raw', 'events') since module 2. The only piece that module had to add was a session-to-store mapping, because events doesn't carry store_id in any column:
-- models/marts/fact_sessions.sql
with kiosko_sessions as (
select distinct session_id
from {{ ref('stg_events') }}
),
session_store_map as (
select
session_id,
case mod(cast(substr(session_id, 6) as integer) - 1, 3)
when 0 then 'S01'
when 1 then 'S02'
when 2 then 'S03'
end as store_id
from kiosko_sessions
)
select
e.session_id,
m.store_id,
min(cast(e.event_ts as date)) as session_date,
max(case when e.event_type = 'page_view' then e.event_ts end) as view_ts,
max(case when e.event_type = 'add_to_cart' then e.event_ts end) as add_to_cart_ts,
max(case when e.event_type = 'purchase' then e.event_ts end) as purchase_ts,
max(case when e.event_type = 'purchase' then true else false end) as is_converted
from {{ ref('stg_events') }} as e
inner join session_store_map as m
on e.session_id = m.session_id
group by e.session_id, m.store_idTwo pieces of this file are worth pausing on. First, session_store_map reproduces the same fixed, deterministic mapping data-modeling-for-analytics-guide declared (SESS-01 -> S01, SESS-02 -> S02, SESS-03 -> S03, SESS-04 -> S01, rotating), but as a SQL expression instead of a Python function: substr(session_id, 6) extracts the two digits after SESS- (DuckDB indexes strings from 1, so position 6 is the first digit), cast(... as integer) - 1 converts it to the same zero-based index mod(..., 3) uses, and the CASE translates the result (0, 1, or 2) to the matching store — the same arithmetic STORE_ROTATION[(session_number - 1) % 3] did in Python, now expressed in pure SQL, with no external data at all.
Second, this is the whole project's first mart that uses stg_events — modules 3 through 7's four marts (dim_store, dim_date, fact_orders) never needed it. The staging model has existed since module 2, tested with unique/not_null over event_id since then, but until this lesson nobody had consumed it with a real ref().
dbt run --select fact_sessionsWhat to expect.
1 of 1 START sql table model main.fact_sessions ................................ [RUN]
1 of 1 OK created sql table model main.fact_sessions ........................... [OK in 0.06s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1
dbt show --inline "select count(*) as total_sessions, count(view_ts) as viewed, count(add_to_cart_ts) as added_to_cart, count(purchase_ts) as purchased from {{ ref('fact_sessions') }}"What to expect.
Previewing inline node:
| total_sessions | viewed | added_to_cart | purchased |
| --------------- | ------ | -------------- | --------- |
| 17 | 17 | 9 | 6 |
Seventeen sessions, the exact same funnel data-modeling-for-analytics-guide reported: 17 views, 9 added to cart (52.9%), 6 purchases (35.3% total conversion). The store mapping also matches, session by session — SESS-01 -> S01, SESS-02 -> S02, SESS-03 -> S03, the same rotation:
dbt show --inline "select session_id, store_id, session_date, is_converted from {{ ref('fact_sessions') }} order by session_id" --limit 5What to expect.
Previewing inline node:
| session_id | store_id | session_date | is_converted |
| ---------- | -------- | ------------ | ------------ |
| SESS-01 | S01 | 2026-08-03 | True |
| SESS-02 | S02 | 2026-08-03 | False |
| SESS-03 | S03 | 2026-08-04 | True |
| SESS-04 | S01 | 2026-08-04 | False |
| SESS-05 | S02 | 2026-08-04 | False |
Piece 4: fact_store_activity — the cumulative table design, with no loop at all
This is the most interesting translation of the five. data-modeling-for-analytics-guide, in its module 6, built fact_store_activity with a Python loop that processed one day at a time, taking yesterday's row's array and prepending today's value (list_prepend) — the real production mechanism behind Zach Wilson's cumulative table design. That same guide also included a second, 100% SQL version, that recalculates the same result with a window function in a single SELECT, and verified, with zero differences, that both give exactly the same result. That second version — not the loop — is the one that gets ported to dbt, because a dbt model is always a single declarative query, never a sequence of imperative steps:
-- models/marts/fact_store_activity.sql
with days as (
select unnest(
generate_series(date '2026-08-03', date '2026-08-09', interval 1 day)
)::date as activity_date
),
stores as (
select store_id from {{ ref('dim_store') }}
),
daily_store_revenue as (
select
s.store_id,
d.activity_date,
coalesce(round(sum(f.revenue), 2), 0.0) as daily_revenue
from stores as s
cross join days as d
left join {{ ref('fact_orders') }} as f
on f.store_id = s.store_id
and cast(f.order_ts as date) = d.activity_date
group by s.store_id, d.activity_date
)
select
store_id,
activity_date,
daily_revenue,
list_reverse(array_agg(daily_revenue) over (
partition by store_id order by activity_date
rows between 6 preceding and current row
)) as revenue_array_7d,
len(list_filter(
list_reverse(array_agg(daily_revenue) over (
partition by store_id order by activity_date
rows between 6 preceding and current row
)),
x -> x > 0
)) as active_days_7d,
list_reverse(array_agg(daily_revenue) over (
partition by store_id order by activity_date
rows between 29 preceding and current row
)) as revenue_array_30d,
len(list_filter(
list_reverse(array_agg(daily_revenue) over (
partition by store_id order by activity_date
rows between 29 preceding and current row
)),
x -> x > 0
)) as active_days_30d
from daily_store_revenue
order by store_id, activity_dateThree CTEs do the work. days generates Kiosko's seven fixed weekdays with generate_series — the same deterministic range generator dim_date already uses since module 3, here scoped to the week that has data instead of the complete month. stores takes dim_store's three stores (ref(), not a list repeated by hand). daily_store_revenue cross-joins both (CROSS JOIN) to guarantee one row for every store-and-day combination — including days with no sale at all — and uses LEFT JOIN against fact_orders with COALESCE(..., 0.0) so a day with no orders produces 0.0, never NULL — exactly the same business reason ("S03 sold nothing on August 5, and that's a real fact, not missing data") the sibling guide already explained.
The final SELECT is where the cumulative table design's translation lives: array_agg(daily_revenue) OVER (PARTITION BY store_id ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) builds, for every row, the array of the last 7 days up to that date — the same result list_prepend produced day by day, but calculated all at once, with no intermediate state to carry between rows. list_reverse(...) flips the order so the most recent value comes first (the same "today up front" convention data-modeling-for-analytics-guide already adopted), and list_filter(..., x -> x > 0) + len(...) count how many of those values are strictly positive — active_days, not the array's raw length. The same formula repeats with 29 PRECEDING for the 30-day window.
dbt run --select fact_store_activityWhat to expect.
1 of 1 START sql table model main.fact_store_activity .......................... [RUN]
1 of 1 OK created sql table model main.fact_store_activity ..................... [OK in 0.06s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1
dbt show --inline "select store_id, activity_date, active_days_7d, active_days_30d from {{ ref('fact_store_activity') }} where activity_date = date '2026-08-09' order by store_id"What to expect.
Previewing inline node:
| store_id | activity_date | active_days_7d | active_days_30d |
| -------- | -------------- | --------------- | ---------------- |
| S01 | 2026-08-09 | 7 | 7 |
| S02 | 2026-08-09 | 7 | 7 |
| S03 | 2026-08-09 | 6 | 6 |
S01 and S02 active all seven days of the week; S03 with six, the same day with no sales (2026-08-05) data-modeling-for-analytics-guide already identified. active_days_30d is identical to active_days_7d in all three rows — not because the calculation is wrong, but because Kiosko only has seven days of history available: no 30-day window can have more elements than the days that exist, exactly the same honest observation the sibling guide already made with evidence.
Piece 5: mart_daily_sales_obt — the final wide table
The last piece brings four already-existing models together into a single, deliberately denormalized table: fact_orders (the fact), dim_store and dim_date (dimensions already conformed since module 3), and dim_product_snapshot joined with a point-in-time join (the same pattern data-modeling-for-analytics-guide taught in its own module 5, and that this module's lesson 2 already explained why it replaces a flat dim_product):
-- models/marts/mart_daily_sales_obt.sql
select
cast(f.order_ts as date) as sale_date,
dd.day_of_week,
dd.is_weekend,
dd.month,
dd.quarter,
dd.year,
ds.store_id,
ds.store_name,
ds.city,
p.product_id,
p.product_name,
p.category,
p.unit_cost,
sum(f.quantity) as quantity,
round(sum(f.revenue), 2) as revenue
from {{ ref('fact_orders') }} as f
inner join {{ ref('dim_store') }} as ds on f.store_id = ds.store_id
inner join {{ ref('dim_product_snapshot') }} as p
on f.product_id = p.product_id
and f.order_ts >= p.dbt_valid_from
and f.order_ts < coalesce(p.dbt_valid_to, timestamp '9999-12-31')
inner join {{ ref('dim_date') }} as dd on cast(f.order_ts as date) = dd.calendar_date
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
order by sale_date, store_id, product_idThe JOIN against dim_product_snapshot is the piece that does have a literal equivalent in data-modeling-for-analytics-guide — the same point-in-time join from its own module 5 (BETWEEN valid_from AND COALESCE(valid_to, ...)), here expressed with the half-open interval dbt already generates on its own (dbt_valid_from/dbt_valid_to, explained in this guide's module 5, lesson 7): f.order_ts >= p.dbt_valid_from confirms that product version already existed at the moment of the sale; f.order_ts < COALESCE(p.dbt_valid_to, TIMESTAMP '9999-12-31') confirms that version hadn't closed yet. None of this filters by "today's current version" — that would be the mistake this lesson's Common mistakes section already warns about — every fact_orders line joins against the product version that was real on that specific sale's date, regardless of which version is current at the moment someone runs this model. The rest is the same query: four JOINs, a GROUP BY with the thirteen non-aggregated columns, the same grain — day + store + product — that collapses duplicate orders into a single row.
dbt run --select mart_daily_sales_obtWhat to expect.
1 of 1 START sql table model main.mart_daily_sales_obt .......................... [RUN]
1 of 1 OK created sql table model main.mart_daily_sales_obt ..................... [OK in 0.05s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1
dbt show --inline "select count(*) as n_rows, sum(revenue) as total_revenue from {{ ref('mart_daily_sales_obt') }}"What to expect.
Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
| 39 | 106.15 |
39 rows, not 40 — and 106.15 revenue, the same exact total as always. Lesson 6 explains in detail which row collapsed and why; for now, the figure worth keeping in mind is that total revenue never changed, even though the row count did — the same distinction data-modeling-for-analytics-guide already insisted on: losing a row to aggregation isn't the same as losing revenue to a bug.
Diagram: the five pieces and what they depend on
flowchart TD
stg_products["stg_products"] --> dim_category["dim_category (3 rows)"]
stg_events["stg_events"] --> fact_sessions["fact_sessions (17 rows)"]
dim_store["dim_store"] --> fact_sessions
dim_store --> fact_store_activity["fact_store_activity (21 rows)"]
fact_orders["fact_orders"] --> fact_store_activity
fact_orders --> mart_daily_sales_obt["mart_daily_sales_obt (39 rows)"]
dim_store --> mart_daily_sales_obt
dim_date["dim_date"] --> mart_daily_sales_obt
dim_product_snapshot["dim_product_snapshot"] --> mart_daily_sales_obt
literal["six VALUES rows\n(no ref/source)"] --> dim_order_flags["dim_order_flags (6 rows)"]
dim_order_flags is, on purpose, the only node with no incoming arrow from the project — the same fact Piece 2 already explained: a junk dimension declares its complete domain directly, with no dependency on any source data.
Common mistakes
Writing ref('dim_product') in mart_daily_sales_obt.sql, expecting it to exist. What happens: someone, following the memory of data-modeling-for-analytics-guide (where dim_product does exist as a flat table), writes {{ ref('dim_product') }} instead of {{ ref('dim_product_snapshot') }} with the point-in-time join. Why it happens: this module's lesson 2 already explained why, but the habit of naming the product dimension dim_product — the name the sibling guide uses — is hard to break. How to spot it: dbt fails immediately with Compilation Error because dim_product doesn't exist as a resource in this project — an early, clear error, not a silently incorrect result. How to fix it: this project never built an un-historized dim_product — module 5's snapshot replaces that role completely; always use ref('dim_product_snapshot') with the point-in-time join (dbt_valid_from/dbt_valid_to against order_ts) whenever you need a product's version that was real at the moment of each sale.
Joining against dim_product_snapshot by product_id alone, with no date condition, and duplicating rows in the OBT. What happens: someone joins fact_orders directly against the complete dim_product_snapshot, comparing only f.product_id = p.product_id, with no condition on order_ts added, expecting every product_id to show up just once. Why it happens: dim_product_snapshot looks, at first glance, like any other dimension — one row per product — and it's easy to forget it actually has one row per product version. How to spot it: if P002 shows up with two different categories in the same mart_daily_sales_obt row (snacks and health-snacks at once, in separate rows for the same day and store), the JOIN is multiplying rows against the two archived versions. How to fix it: any JOIN against a historized table needs, besides the business key, a condition that narrows the date of interest to a validity range — f.order_ts >= p.dbt_valid_from AND f.order_ts < COALESCE(p.dbt_valid_to, TIMESTAMP '9999-12-31'), exactly as this lesson's JOIN applies it.
Confusing array_agg(...) OVER (...) with a normal aggregation, and forgetting the PARTITION BY. What happens: someone writes array_agg(daily_revenue) OVER (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), with no PARTITION BY store_id, expecting the array to get calculated separately for each store. Why it happens: it's easy to think of activity_date as if it already identified a unique row, forgetting the three stores share the same seven dates. How to spot it: without PARTITION BY store_id, revenue_array_7d's array would mix revenue from different stores in the same window — for example, day 3's array could combine values from S01 and S02 if their rows end up interleaved by the global ORDER BY activity_date. How to fix it: any window function calculating "this specific entity's last N values" needs PARTITION BY over the column identifying that entity — store_id here — exactly as module 6's incremental fact_orders pattern already required, though without a window that time.
Exercises
Exercise 1 — Rebuild dim_category without looking at the example, and confirm the same result. Write dim_category.sql's SELECT from memory, run dbt run --select dim_category, and confirm with dbt show that you get the same three categories (beverages, electronics, health-snacks) in the same order.
See solution
If you correctly reproduced the ROW_NUMBER() OVER (ORDER BY category) pattern over a SELECT DISTINCT category FROM {{ ref('stg_products') }} subquery, the result should be exactly the same: three rows, category_id 1/2/3 matching beverages/electronics/health-snacks in alphabetical order. If you get four categories instead of three, check that the subquery has DISTINCT — without it, ROW_NUMBER() would number every individual product, not each distinct category, the same mistake data-modeling-for-analytics-guide already warned about in its own normalization lesson.
Exercise 2 — Calculate by hand which store SESS-09 maps to, using session_store_map's formula. Without running any query, apply mod((session_number - 1), 3) to SESS-09 and confirm your result against fact_sessions.
See solution
SESS-09 has session number 9. mod(9 - 1, 3) = mod(8, 3) = 2, which the CASE translates to S03. Confirming with a query: dbt show --inline "select session_id, store_id from {{ ref('fact_sessions') }} where session_id = 'SESS-09'" should return S03 — the same store data-modeling-for-analytics-guide already reported for that session.
Exercise 3 — Explain why fact_store_activity needs a CROSS JOIN between stores and days, instead of deriving the days directly from fact_orders. In 2-3 sentences, explain what problem the CROSS JOIN solves that a query that just grouped fact_orders by store and day wouldn't solve.
See solution
If daily_store_revenue got built by grouping fact_orders directly by store_id and date, any store-and-day combination with no sale at all — like S03 on 2026-08-05 — would simply not show up in the result, instead of showing up with daily_revenue = 0.0. The CROSS JOIN between stores (the three known stores) and days (the week's seven fixed days) guarantees the 21 combinations always exist, and the LEFT JOIN + COALESCE against fact_orders fills revenue in with 0.0 when there's no order to sum — exactly the same guarantee the 7/30-day window needs to work correctly: without that zero-revenue row, S03's array would have one fewer element than expected, and active_days_7d would miscount how many days had passed.
Summary and next step
In this lesson you wrote the five .sql files that were missing: dim_category (3 rows, over stg_products), dim_order_flags (6 rows, with no ref() at all), fact_sessions (17 rows, the complete funnel over stg_events), fact_store_activity (21 rows, the cumulative table design solved with a single-SELECT SQL window function), and mart_daily_sales_obt (39 rows, 106.15 revenue). Each one ran individually with dbt run --select, and each result matches, number for number, what data-modeling-for-analytics-guide already verified — with dim_category's single expected difference, which reflects the current product catalog, not the original one.
Before moving on you should be able to: explain why fact_store_activity needed no Python loop at all, just a window function; and name the only one of the five pieces that depends on no other model in the project.
Lesson 4 steps back from the code and looks at the complete project: the final file tree, _models.yml with the five new pieces' descriptions and data_tests, and dbt ls confirming the dependency graph ended up exactly as this lesson's diagram predicted.
Resources
data-modeling-for-analytics-guide— module 3 (dim_category), module 6 (fact_sessions,fact_store_activity), module 7 (dim_order_flags) — the exact source of every pattern this lesson translated. This ecosystem's sibling guide.- DuckDB — window functions documentation, including the
ROWS BETWEEN ... PRECEDING AND CURRENT ROWsyntaxfact_store_activityrests on. duckdb.org/docs/current/sql/functions/window_functions. In English. - DuckDB — list functions documentation (
list_reverse,list_filter,array_agg), the basis for translating the cumulative table design into pure SQL. duckdb.org/docs/current/sql/functions/list. In English. - DuckDB —
generate_seriesdocumentation, the deterministic range generatordim_datealready used since module 3, here applied to Kiosko's seven weekdays. duckdb.org/docs/current/sql/functions/nested#range-functions. In English.