Module 8: Project Kioskos Dbt Warehouse
Querying the final mart
Description
dbt build already rebuilt mart_daily_sales_obt in lesson 5. This lesson does what that mart exists to do: answer business questions with no JOIN written at all. You're going to query total revenue, the breakdown by category and by store, and the specific row that collapsed two orders into one — the same report, number for number, that data-modeling-for-analytics-guide already produced by hand in its own module 3 — now with a single SELECT over a single table, reproducible by anyone with dbt show.
Connection to the module. Lesson 5 confirmed the complete project runs with no errors. This lesson confirms something different: that the result — not just the absence of errors — is the one Kiosko needs. It's the same distinction module 3 already insisted on since its own mini-project: PASS=46 certifies the SQL is valid, never that the final number is correct — only a real query, with a known result to compare against, confirms that.
An analogy: the report that used to take four documents, now in one
Before this guide, answering "how much did Kiosko sell this week, by category?" required opening fact_orders, joining it mentally (or with a JOIN written by hand) against dim_store, dim_product, and dim_date, and trusting nobody wrote a join condition wrong. mart_daily_sales_obt is the same report, already assembled ahead of time — like the difference between asking an accountant to add up four different spreadsheets every time someone asks a question, versus having a single consolidated report that already integrated all four, ready to read directly.
Query 1: the total, reconciled against fact_orders
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 — the exact same total revenue, 106.15. Lesson 3 already previewed this figure; here's the complete explanation. fact_orders has 40 rows — one per order line — but mart_daily_sales_obt groups by day + store + product, a deliberately coarser grain. Find the specific row that collapsed:
dbt show --inline "select sale_date, store_id, product_id, quantity, revenue from {{ ref('mart_daily_sales_obt') }} where sale_date = date '2026-08-03' and store_id = 'S01' and product_id = 'P001'"What to expect.
Previewing inline node:
| sale_date | store_id | product_id | quantity | revenue |
| ---------- | -------- | ---------- | -------- | ------- |
| 2026-08-03 | S01 | P001 | 5 | 2.75 |
Two orders — ORD-1001 (3 units of P001 at 0.55, revenue = 1.65) and ORD-1008 (2 units of the same product, same store, same day, revenue = 1.10) — merged into a single row: 5 units (3 + 2), 2.75 revenue (1.65 + 1.10). This is the exact same pair of orders data-modeling-for-analytics-guide identified in its own module 3, now reproduced with a single dbt show, with no manual query against fact_orders.
Query 2: revenue by category
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"What to expect.
Previewing inline node:
| category | units | revenue |
| ----------- | ----- | ------- |
| beverages | 75 | 44.05 |
| electronics | 9 | 40.50 |
| snacks | 18 | 21.60 |
44.05 + 40.50 + 21.60 = 106.15 — the same total as always, broken down by category with mart_daily_sales_obt needing no additional JOIN at all: category already lives as its own, denormalized column, exactly a wide table's central point. Compare these numbers against what data-modeling-for-analytics-guide reported in its own module 2 (beverages: 44.05, electronics: 40.5, snacks: 21.6) — they match cent for cent, including the third category: snacks, not health-snacks. That match isn't a coincidence — it's lesson 3's point-in-time join honoring the history module 5 built: every Kiosko order happened between August 3 and 9, 2026, before P002's change (August 15), so the correct category for those sales was always snacks — the same one data-modeling-for-analytics-guide reported with its own point-in-time join.
Query 3: revenue by store
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:
| store_id | store_name | revenue |
| -------- | ------------- | ------- |
| S01 | Kiosko Centro | 38.30 |
| S02 | Kiosko Norte | 38.80 |
| S03 | Kiosko Sur | 29.05 |
38.30 + 38.80 + 29.05 = 106.15. These are the exact same three numbers fact_store_activity already reported, store by store, in lesson 3 — each store's last day's revenue_array_7d added up to exactly this. Two completely independent marts, built with different mechanisms (one with an aggregated wide table, the other with a cumulative table design), arriving at the same number: the same cross-confirmation module 3 already valued in its own mini-project.
Final check: the singular test that already ran in lesson 5
The reconciliation you just did by hand — mart_daily_sales_obt sums to the same thing as fact_orders — is already automated, since lesson 4, as tests/assert_obt_revenue_matches_fact_orders.sql. Running it in isolation confirms, with the same dbt test mechanism you already know since module 4, that this reconciliation doesn't depend on someone doing it by hand every time:
dbt test --select assert_obt_revenue_matches_fact_ordersWhat to expect.
1 of 1 START test assert_obt_revenue_matches_fact_orders ........................ [RUN]
1 of 1 PASS assert_obt_revenue_matches_fact_orders ............................... [PASS in 0.03s]
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1
Common mistakes
Seeing a comma as the decimal separator instead of a period, and thinking the number is wrong. What happens: someone runs this lesson's exact same query, on their own machine, and sees 106,15 instead of 106.15 — with a comma, not a period — in dbt show's output. Why it happens: dbt show renders decimal numbers using the operating system's locale setting — on a system configured with Spanish from Spain or Latin America (es_ES, es_CO, es_MX, any variant), the comma is the standard decimal separator, and the library dbt uses to format tables respects that setting. How to spot it: if you see commas where this lesson shows periods, and the number itself (106,15) matches the expected value (106.15) except for the symbol, it's not a data error — it's a formatting difference in your terminal. How to fix it: to reproduce this guide's exact format, run export LC_ALL=en_US.UTF-8 (or LC_NUMERIC=en_US.UTF-8, the specific variable controlling number formatting) before your dbt show commands — or, if you'd rather keep your locale setting, confirm the value by comparing digit by digit, not by the separator symbol.
Confusing "39 rows" with "an order got lost". What happens: someone, seeing n_rows = 39 in Query 1, panics thinking one of fact_orders's 40 orders disappeared along the way. Why it happens: every earlier mart in this project (dim_store, dim_date, fact_orders) preserved exactly 40 rows or fewer for dimension reasons, never from aggregation — this is the whole project's first mart with a deliberately coarser grain than "one order line." How to spot it: the correct check is never "does the row count match?" for an aggregated mart — it's "does the total revenue match?", exactly the question Query 1 and this lesson's singular test answer with evidence. How to fix it: before getting alarmed by a different row count, first ask yourself what the mart you're querying's declared grain is — mart_daily_sales_obt's is day + store + product, not order line, so a count below 40 is exactly what's expected when there's at least one combination with more than one order.
Querying mart_daily_sales_obt.category expecting to see health-snacks, because that's what P002's category is called today. What happens: someone who knows P002 changed from snacks to health-snacks on August 15, 2026 (module 5) expects to see health-snacks in mart_daily_sales_obt, and gets confused seeing snacks instead. Why it happens: it's easy to think of the OBT as a mirror of the products catalog's current state, instead of a report of the catalog as it was at the moment of each sale. How to spot it: check fact_orders's dates — Kiosko's 40 orders all happen between 2026-08-03 and 2026-08-09, before the August 15 change — so lesson 3's point-in-time join correctly resolves every one of those sales against the snacks version, the only one that existed on those dates. How to fix it: remember that mart_daily_sales_obt answers "what category did this product have when it sold?", not "what category does it have today?" — for that second question you need to query dim_product_snapshot filtered by dbt_valid_to IS NULL directly, not the OBT.
Exercises
Exercise 1 — Calculate the average revenue per row, and compare it against fact_orders. Using mart_daily_sales_obt and fact_orders, calculate SUM(revenue) / COUNT(*) on each table and compare the two results. Which do you expect to be higher, and why?
See solution
dbt show --inline "select
(select round(sum(revenue) / count(*), 4) from {{ ref('fact_orders') }}) as avg_per_fact_row,
(select round(sum(revenue) / count(*), 4) from {{ ref('mart_daily_sales_obt') }}) as avg_per_obt_row"mart_daily_sales_obt's average per row is higher, because the same total revenue (106.15) gets split across fewer rows (39, not 40) — the row that collapsed two orders sums their revenue into a single row, instead of splitting it between two. This doesn't mean revenue "grew" in any real sense: the total stays identical; what changes is how many rows divide that total when calculating an average — the same trap data-modeling-for-analytics-guide already warned about in its own module 3: an average calculated over tables with different grains isn't directly comparable.
Exercise 2 — Confirm the "current" category (health-snacks) never shows up in mart_daily_sales_obt. Write a query that confirms health-snacks doesn't show up in any row of mart_daily_sales_obt, and explain why that's correct given how the mart was built.
See solution
dbt show --inline "select count(*) as rows_with_health_snacks from {{ ref('mart_daily_sales_obt') }} where category = 'health-snacks'"The expected result is 0. mart_daily_sales_obt gets built by joining fact_orders against dim_product_snapshot with a point-in-time join — every line resolves against the product version that was real on that sale's exact date, never against "whatever's current today" — so the health-snacks category (P002's version that opened on August 15, 2026) could only show up if at least one order existed with order_ts >= 2026-08-15. No real Kiosko order falls after that date — the 40 orders run from August 3 to 9 — so health-snacks can never show up in this particular mart. This is the point-in-time join's central guarantee: the OBT answers "what category did the product have at the moment of the sale?", not "what category does the product have now?" — the same distinction data-modeling-for-analytics-guide already taught in its own module 5.
Exercise 3 — Argue why this lesson needed no new JOIN written at all. In 2-3 sentences, explain why this lesson's three queries — total revenue, by category, by store — needed no join against any other table, unlike the equivalent queries you'd need to write directly over fact_orders.
See solution
mart_daily_sales_obt already integrated, in lesson 3, everything a JOIN would have had to resolve at query time — category comes from dim_product_snapshot, store_name from dim_store, day_of_week from dim_date — so any question that only needs to group and sum those already-denormalized columns gets answered with a simple GROUP BY. Directly over fact_orders, the same question ("revenue by category") would have required a point-in-time JOIN against dim_product_snapshot, with its condition on order_ts, written into every query that needed it — exactly the cost a wide table exists to avoid, paid once when building the mart, instead of once per future query.
Summary and next step
In this lesson you queried mart_daily_sales_obt with three real business questions — total revenue, by category, by store — each answered with a single SELECT and no JOIN at all. You confirmed the total (106.15) matches fact_orders exactly, that the breakdown by category (beverages: 44.05, electronics: 40.50, snacks: 21.60) and by store (S01: 38.30, S02: 38.80, S03: 29.05) matches, cent for cent, what data-modeling-for-analytics-guide already published — including P002's category, which the point-in-time join resolves to the correct historical version — and you saw, with the singular test assert_obt_revenue_matches_fact_orders, that this reconciliation is already automated, with no dependence on anyone repeating it by hand.
Before moving on you should be able to: explain why mart_daily_sales_obt has 39 rows instead of 40, with that not meaning a lost order; and explain why P002's category shows up as snacks, not health-snacks, in any query over this mart.
Lesson 7 closes the complete ecosystem's circle: it names, one by one, the seven sibling guides that solve what this dbt project — complete, tested, documented — still leaves pending.
Resources
- dbt Developer Hub — "
dbt show," already cited in modules 3, 4, 5, 6, and 7, the reference for this lesson's central command. docs.getdbt.com/reference/commands/show. In English. data-modeling-for-analytics-guide— module 2 (revenue by category) and module 3 (mart_daily_sales_obt's collapsed row), the exact source of the numbers this lesson reconciles. This ecosystem's sibling guide.- Python Software Foundation —
localedocumentation, the general reference for why decimal number formatting depends on the system's locale setting, the exact cause of this lesson's first Common mistake. docs.python.org/3/library/locale.html. In English.