The pivot has to be earned
Every dimensional modeling course opens by telling you that analysts cannot query the transactional schema. Almost none of them make you try. So this one does. The question is the most boring one a marketplace ever asks: net revenue by month, by merchant tier, by product price band, settled orders only. It is not a trick question and there is nothing exotic in it. Against Bazaar's source schema it takes five joins, a date parsed out of a text column, a guard against a fan-out that silently inflates money, and a hand-written definition of revenue that lives only inside that one query.
Then the same question against the star: one view, no joins, nine lines, and the definition of revenue is a row in a table. That gap is the entire argument for sessions b6 and b7, and it is much more convincing when you have felt it than when somebody draws it on a slide.
sql/10_dim_ddl.sql and b7 ships sql/11 and sql/12.
Write-optimised is not read-optimised 7 min live
The source schema is not badly designed. It is designed for a different verb. An OLTP schema is optimised for writing one row correctly: one purchase, one payment attempt, one cart change, right now, while somebody is holding a phone. An analytical schema is optimised for reading millions of rows that are all shaped alike: ninety days of order lines, grouped six ways, scanned in full, approximately now. Those two goals pull in opposite directions on almost every decision you make, which is why one schema cannot serve both without one of the two users suffering.
LiveThe four costs, named4 min▶
When an analyst says "the database is hard to query", they almost never mean the SQL is hard. They mean one of these four things, and each one is a modeling problem rather than a skill problem:
- Join distance. The attribute you want to group by is three or four tables away from the number you want to sum. Merchant tier lives on
merchants; revenue lives onorder_items; getting them into one row costs a join for every hop. Every hop is a chance to pick the wrong key. - Buried time.
order_tsis TEXT. Month, weekday, hour, week-of-year, is-it-a-weekend: none of them exist as columns, so every analyst writes their ownstrftimeand their own weekend rule. Two dashboards that disagree about whether Saturday is a weekend day is a real incident, not a hypothetical. - Fan-out. Join order lines to payment attempts and every retried order is counted twice. The query looks completely reasonable, the number is wrong, and nothing errors. This is the single most expensive failure mode in analytics on a source schema.
- Private definitions. "Settled revenue" gets defined inside the query: which statuses count, whether discounts come off, whether refunds are netted. That definition is now stuck in one analyst's SQL file, so the next person writes a different one and both are defensible.
orders to hold a month column, or storing a revenue total on the header, makes the application worse to keep correct. The answer is a second shape, loaded from the first, which is exactly what b6 and b7 design.
Self-studyWhy "just add an index" does not fix it3 min read▶
The first instinct when analytical queries are slow on a source database is to add indexes. It helps a little and it makes the real problem worse.
- Indexes serve selective lookups, not scans. "Give me order 4711" is a lookup. "Sum ninety days of order lines, grouped six ways" reads most of the table, and reading most of a table through an index is slower than reading it directly.
- Every index taxes the writer. Bazaar's source schema declares seven indexes, deliberately short, because each one is extra work on every insert. Adding twenty indexes for analysts degrades the checkout path they are analysing.
- Indexes cannot invent a column. No index makes
month_nameexist, resolves a fan-out, or writes down what revenue means. Those are modeling gaps, and only a model fixes them.
The second instinct is a read replica. That is a genuinely good idea and it solves contention, not shape: the replica has exactly the same five joins and the same buried date. Session a4 on the leader track covers where each of these tools actually helps.
Grain, conformance, and the two schools 8 min live
Two ideas carry the rest of this track. Grain is the sentence "one row of this table is one ___", and the discipline is that you choose it from the question rather than inheriting it from the source. Conformance is what lets two different facts be sliced the same way: if fact_order_item and fact_cart both point at the same dim_merchant, with the same keys and the same attribute values, then "revenue by merchant" and "cart conversion by merchant" can sit on one slide without an argument.
LiveChoose the grain from the question, not from the source4 min▶
The failure that produces unusable analytical tables is inheriting the grain. Somebody joins five source tables, likes the result, saves it as a table, and now the grain is "whatever that join produced" - which nobody can state in a sentence, so nobody can tell whether summing a column is safe.
The worksheet below is the whole method, and it is deliberately boring. Write the question. Write the grain sentence. List the dimensions the question needs (they are the words after "by"). List the measures (the words after "how much" or "how many"). Only then decide which table answers it.
| Business question | One row is | Dimensions needed | Measures | Answered by |
|---|---|---|---|---|
| Net revenue by month, merchant tier and price band | one product line on one order | date, merchant, product | net_amount, quantity | fact_order_item |
| Which merchant lost the most revenue in the last fortnight | one product line on one order | date, merchant | net_amount | fact_order_item |
| What share of payment attempts were declined, and why | one payment attempt | date, hour, payment method | is_declined, declined_amount | fact_transaction |
| Did demand fall, or did checkout break | one cart | date, merchant, channel | converted_flag, cart_value | fact_cart beside fact_transaction |
| When is the peak hour for payments | one payment attempt | hour of day | attempt count | fact_transaction via dim_time_of_day |
Three things fall out of the worksheet immediately. The words after "by" are always dimensions. Any question whose grain sentence needs the word "and" is two questions. And the last row is the one that forces dim_time_of_day to be a separate dimension rather than columns on dim_date - b6 explains why.
LiveInmon and Kimball, as a trade-off rather than a winner4 min▶
Two schools, argued for thirty years, and the honest answer is that they optimise for different risks. Learn both positions properly, because you will be in a room where somebody asserts one of them as settled fact.
- Inmon: a normalized enterprise core, then dimensional marts on top. Load the warehouse in third normal form, integrated across every source, then build departmental star schemas from it. The core is the single place a fact is reconciled. You pay for it with a longer build, more hops from source to dashboard, and a modeling team that must understand the whole enterprise before it can ship anything.
- Kimball: dimensional from the start, with conformed dimensions as the integration mechanism. Model one business process at a time as a star, and make the dimensions conform so the stars line up into a bus matrix. You ship a usable mart in weeks. You pay for it with integration that lives in a discipline rather than in a schema, so conformance decays the moment nobody is guarding it.
- What they actually disagree about is where change is absorbed. Inmon absorbs change in a normalized core that is expensive to build and cheap to extend. Kimball absorbs change in dimensions that are cheap to build and need governance to stay conformed. That is a real trade-off about your organisation, not about SQL.
Where this course lands, and why: Bazaar is one business process family with one owner, so it goes Kimball-style, dimensional and conformed, and b6 to b8 build exactly that. If Bazaar had six source systems disagreeing about what a customer is, a normalized integration layer would earn its cost first. The modern dbt convention is quietly a compromise between the two: staging models that clean and rename one-to-one with the source, intermediate models that integrate, and marts that are dimensional. Same argument, new file names.
Self-studyWhere this course stops, honestly2 min read▶
This course designs the model: grain, keys, conformance, additivity, the DDL, the transform, the marts, the agent layer. It loads that model once, with a full refresh, because a full refresh keeps the modeling visible instead of burying it in merge logic.
Loading it incrementally is a different course. Incremental loads, MERGE statements, late-arriving facts, and the mechanics of applying a slowly-changing type-2 change on every run belong to learn-data-warehouse-with-phoebe (builder sessions 4 to 6). Those are loading problems. Grain, conformance and key strategy are modeling problems. Keeping the two apart is why both courses stay 45 minutes a session instead of 90.
The pipeline that runs the load on a schedule, with orchestration and monitoring, is learn-data-engineering-with-phoebe. Query tuning, partitioning and execution plans are out of scope everywhere in this course except the seven indexes b4 declared because the access paths obviously needed them.
Write the question against the source, and feel the cost ★ 14 min · everyone builds
These four editors run against Bazaar's eight source tables only - users, merchants, products, carts, cart_items, orders, order_items, transactions. No star, no views, no shortcuts. Exactly what an analyst gets when someone grants them read access to the application database.
Hit the fan-out first. Join order lines to payment attempts to keep only paid money. The query is reasonable, the SQL is valid, and the revenue number is inflated. Nothing warns you.
Dig the date out of a string. order_ts is TEXT. Month, hour and weekend do not exist as columns, so you write them - and so does everybody else, differently.
Assemble the real answer. Five joins, the guard, the month expression, a hand-rolled price band, and a definition of settled revenue that exists only in this query.
Count what you just did. Then hold the number 111,906.48 in your head, because the next section reproduces it in nine lines.
SELECT 'joined to transactions' AS method,
COUNT(*) AS rows_summed,
ROUND(SUM(oi.quantity * oi.unit_price - oi.discount_amt), 2) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN transactions t ON t.order_id = o.order_id
WHERE o.status IN ('paid', 'refunded')
UNION ALL
SELECT 'guarded with EXISTS',
COUNT(*),
ROUND(SUM(oi.quantity * oi.unit_price - oi.discount_amt), 2)
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status IN ('paid', 'refunded')
AND EXISTS (SELECT 1 FROM transactions t
WHERE t.order_id = o.order_id AND t.status = 'approved');
SELECT o.order_id,
o.order_ts,
typeof(o.order_ts) AS stored_as,
strftime('%Y-%m', o.order_ts) AS month_you_wanted,
CAST(strftime('%H', o.order_ts) AS INTEGER) AS hour_you_wanted,
CASE WHEN strftime('%w', o.order_ts) IN ('0', '6')
THEN 1 ELSE 0 END AS is_weekend_probably
FROM orders o
ORDER BY o.order_id
LIMIT 8;
SELECT
strftime('%Y-%m', o.order_ts) AS order_month,
m.tier AS merchant_tier,
CASE WHEN p.list_price < 25 THEN 'budget'
WHEN p.list_price < 80 THEN 'mid'
ELSE 'premium' END AS price_tier,
COUNT(DISTINCT o.order_id) AS orders,
SUM(oi.quantity) AS units,
ROUND(SUM(oi.quantity * oi.unit_price - oi.discount_amt), 2) AS net_revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN merchants m ON m.merchant_id = oi.merchant_id
JOIN users u ON u.user_id = o.user_id
WHERE o.status IN ('paid', 'refunded')
AND EXISTS (SELECT 1 FROM transactions t
WHERE t.order_id = o.order_id AND t.status = 'approved')
AND date(o.order_ts) BETWEEN '2026-04-01' AND '2026-06-29'
GROUP BY order_month, merchant_tier, price_tier
ORDER BY order_month, net_revenue DESC
LIMIT 10;
Six analysts, six definitions of revenue. A marketplace audited its reporting and found six live queries computing "revenue", differing on whether refunded orders counted, whether line discounts came off, whether the merchant's commission was netted, and whether the date came from the order or the payment. All six were written by competent people, all six were defensible, and the weekly business review had been comparing them to each other for a year. The fix was not better SQL. It was one table with the grain written down, the measure computed once at load, and the definition stored as a row - which is precisely what v_metric_definitions is for, and what the final editor below shows.
Your turn: the same question against the star ★ 10 min · build your own
These editors have the source tables and the star, the marts and the agent views loaded, so you can compare the two shapes in the same tab. The star is what b6 and b7 build; today you only need to see what it buys.
LiveQ1 · The same answer, in nine lines4 min▶
Run it and compare the output row by row with the 21-line version above. Identical numbers. Then read what is missing from the query: no joins, no date parsing, no fan-out guard, no CASE expression inventing a price band, and no private definition of revenue.
SELECT order_month_name, merchant_tier, product_price_tier,
COUNT(DISTINCT order_id) AS orders,
SUM(units) AS units,
ROUND(SUM(net_revenue), 2) AS net_revenue
FROM v_sales_line
WHERE is_paid = 1
GROUP BY order_month_name, merchant_tier, product_price_tier
ORDER BY order_month_name, net_revenue DESC
LIMIT 10;
Three things did the work, and each one has a session. The grain is stated: one row of v_sales_line is one product line on one order, so SUM(net_revenue) is safe and COUNT(*) is lines rather than orders. The dimensions are flat and wide, so merchant tier and price band are columns instead of joins - that is b6. The measure was computed once at load, so nobody re-derives it - that is b7.
SELECT 'fact_order_item' AS fact_table,
'one product line on one order' AS one_row_is,
COUNT(*) AS row_count
FROM fact_order_item
UNION ALL SELECT 'fact_transaction', 'one payment attempt', COUNT(*) FROM fact_transaction
UNION ALL SELECT 'fact_cart', 'one cart', COUNT(*) FROM fact_cart;
LiveQ2 · Slice two different facts the same way4 min▶
This is conformance doing its job. Revenue lives on fact_order_item, cart conversion lives on fact_cart, and both point at the same dim_merchant. Notice what the query does not do: it never joins the two facts to each other. Each is aggregated to a common grain first, then the two results are matched on the shared dimension value.
WITH sales AS (
SELECT m.tier AS merchant_tier, ROUND(SUM(f.net_amount), 2) AS net_revenue
FROM fact_order_item f
JOIN dim_merchant m ON m.merchant_sk = f.merchant_sk
WHERE f.is_paid = 1
GROUP BY m.tier
),
demand AS (
SELECT m.tier AS merchant_tier, COUNT(*) AS carts, SUM(c.converted_flag) AS converted
FROM fact_cart c
JOIN dim_merchant m ON m.merchant_sk = c.merchant_sk
GROUP BY m.tier
)
SELECT s.merchant_tier, s.net_revenue, d.carts, d.converted,
ROUND(d.converted * 100.0 / d.carts, 2) AS cart_conversion_pct
FROM sales s
JOIN demand d ON d.merchant_tier = s.merchant_tier
ORDER BY s.net_revenue DESC;
Try it against the source schema instead and you will need eight joins, two fan-out guards and a decision about which merchant a mixed cart belongs to. That last decision is a modeling choice, and the star has already made it once and written it down - rather than leaving it to whoever writes the query next.
SELECT metric_name, source_view, grain, sql_expression, caveat FROM v_metric_definitions ORDER BY source_view, metric_name;
Self-studyQ3 · Write the grain worksheet for a question of your own4 min▶
No SQL for this one. Take a question your business actually asks in a weekly meeting and fill in the five worksheet columns from Part 2: the question, the grain sentence, the dimensions, the measures, and which table answers it.
Two things usually happen. First, the grain sentence needs the word "and" - which means the question is two questions wearing one sentence, and the room has been arguing about the join rather than the answer. Second, the "answered by" column comes out blank, because no table in your warehouse states a grain at all. Both outcomes are useful, and both are the case for b6 and b7.
Bring the filled worksheet to b6. The dimensions you listed are the dimensions you will design, and the words you used for them are the naming decision session a5 turns into a standard.
Try it yourself - this week ◐ 20-30 min total
- Take the longest analytical query you own and count four things: joins, date expressions, fan-out guards, and definitions that exist only in that file. That count is your case for a model, in numbers a manager can read.
- Find one query in your codebase that joins two things at different grains without a guard. Run it with
COUNT(*)beside the money and check whether the row count exceeds the row count of the finest-grain table involved. If it does, you have found a live fan-out. - Write the grain sentence for the three most-used tables in your warehouse. If any of them needs the word "and", write down which two questions it is really answering.
- Pick one attribute two teams disagree about - customer segment, channel, region - and check whether both teams' tables use the same key and the same values for it. That is a conformance test, and it usually fails.
- Read the four numbered rules at the top of
sql/10_dim_ddl.sqlbefore b6. That file is what session b6 ships, and the rules are its spine.
Sources covered
Full source map, including what is deliberately out of scope, in materials/official-course-map.md. This page covers:
Three questions before you go 🎯 ◐ 90 seconds
1 · Joining order_items to transactions on order_id inflated the revenue number. Why?
Bazaar's 985 orders produced 1,060 payment attempts, because declines get retried, so the join multiplies the lines of every retried order. The SQL is valid and nothing errors, which is exactly why fan-out is expensive. The cheapest detector is to read the row count beside the money: no query summing order lines should ever exceed 1,434 rows.
2 · What does "conformed dimension" buy you?
Conformance is what makes "revenue by merchant" and "cart conversion by merchant" comparable numbers rather than two arguments. It is emphatically not permission to join two facts: you aggregate each fact to a common grain first, then match on the shared dimension value.
3 · Which of these is the honest statement of the Inmon versus Kimball argument?
The choice is about your organisation, not your SQL dialect. Bazaar is one process family with one owner, so this course goes dimensional and conformed. Six source systems disagreeing about what a customer is would justify a normalized integration layer first. The dbt staging / intermediate / marts convention is quietly a compromise between the two.