Data Modeling Lab
Orders, customers, products and payments. Choose the grain of your fact table and decide whether history is preserved, then read which business questions that model answers, which it answers wrongly, and which it cannot answer at all.
The middle column is the whole lesson. A model that cannot answer a question is a design problem you discover immediately, because the query fails or the column is not there. A model that answers wronglyreturns a number of the right magnitude, in the right units, on the right chart, and it will be quoted in a meeting. Nothing in the warehouse will ever tell you which of the two you built.
Fact grain — what one row is
Dimension history
About this grain
The default for commercial reporting. Everything below the order — which products, in what quantities — has been aggregated away and cannot be recovered.
Your model: one row per one order, with dimensions overwritten in place.
4
Answers it correctly
4
Answers it wrongly
2
Cannot answer it
Answers it correctlyThe model contains what the question needs, at the resolution it needs.
Answers it wronglyA number comes back. It is plausible, it is wrong, and nothing raises an error. This is the category that costs money.
Cannot answer itThe information was aggregated away before this model existed and no query can recover it. The most honest failure of the three.
The lab
Build the model itself — facts, dimensions, measures and history handling — and run the same questions against it.
Modeling lab — one grain, ten questions
Modeling lab — one grain, ten questions
Pick the grain of the fact table. The questions do not change; what the table can honestly say about them does.
Fact grain
The transaction and its total. The individual products are gone.
Answered
3
With care
1
Confidently wrong
4
Unanswerable
2
4 of these questions get an answer at this grain that is wrong, and none of them raise an error. That is the whole difficulty: the unanswerable ones announce themselves, and these do not.
| Business question | At this grain | Why |
|---|---|---|
What was revenue by country last month? Wants: One order, or one order line — either works, provided the measure is additive at that grain and is not summed twice. | answered | The measure is additive at this grain and each order is counted once. |
What is the average order value? Wants: One order. An average over lines answers a different question entirely. | answered | The denominator is orders, which is exactly what one row is. |
What was revenue by customer country at the time of each order? Wants: One order, joined to the version of the customer that was current when the order was placed. | WRONG no history | With a Type 1 dimension every historical order is attributed to the customer's current country. A customer moving from Poland to Germany silently rewrites last year's regional reports, and last month's report no longer reproduces. |
What is net revenue after refunds? Wants: One order, with refunds either netted into the measure or held as a separate signed fact at the same grain. | answered | Refunds net into the measure, or sit beside it as a signed fact at the same grain. |
What was the total account balance on each day last year? Wants: One account-day. A balance is a state, not an event, and cannot be reconstructed by summing transactions unless every transaction since account opening is retained. | WRONG | Summing transactions per day gives the daily change in balance, not the balance. The chart has the right shape and the wrong y-axis. |
What is month-three retention by signup cohort? Wants: One user-month of activity, joined to the user's signup month. | WRONG | Users who were active but did not buy are invisible, so retention is understated by exactly the non-buyers. |
What share of sessions ended in a purchase? Wants: One session — which requires a session window over events, because no source system emits a session. | unanswerable | No source system emits a session. Without a session window over events there is no denominator to divide by. |
Which products are most often bought together? Wants: One order line, with the order key retained so lines can be grouped back into baskets. | unanswerable | The most instructive failure in this lab: the model is not wrong, it is at the wrong resolution, and no query can recover what was aggregated away. |
What was yesterday's revenue, asked at 06:00 this morning? Wants: One order, in a period that is not yet closed. | with care | The grain is right and the period is not closed. Orders that happened yesterday and arrive later today are still missing at 06:00. |
What was global revenue, across markets that bill in different currencies? Wants: One order, with both the transaction amount and the converted amount stored, plus the rate and the date the rate applied. | WRONG no history | Converting at query time with today's rate makes every historical report change daily. Converting once with no record of the rate makes the number unreproducible. Both pass every type check there is. |
answeredThe grain is the thing the question is about.
with careIt works, and there is one specific way to get it wrong.
WRONGIt returns a plausible number that is not the answer, and nothing raises.
unanswerableThe resolution needed was aggregated away. No query recovers it.
SIMPLIFIEDA single fact table against ten questions. A real model has several, and a question that one answers badly another may answer exactly — which is the argument for more than one fact table, not for a finer one.
Where to go next
Grain, properly →
The one field that decides whether an aggregate over your table is meaningful.
Slowly changing dimensions →The mechanism behind the history toggle, and why it has to exist before the change happens.
Model a whole platform →Six sources, six consumers, and the ordered questions that decide the model.