Why it matters
Most reporting bugs are modelling bugs. A table that mixes order headers and order lines will sum shipping cost once per line, and no amount of dashboard care fixes it. Modelling forces the team to declare what a row represents, which attributes belong to it, and how it connects to other entities. Those declarations are what make joins safe and metrics reproducible across analysts.
Mental model
A model is a map with a declared scale. A fact table is a list of measured events at that scale; dimensions are the legend that names the places. The metaphor breaks because maps do not change while businesses do, so the model needs a rule for history, not just a snapshot.
How it works
Step 1 of 5
State the grain
One sentence: one row per order line. It decides which columns are measures, which are attributes, and which joins are allowed at all. An ambiguous grain is inherited by every sum built above it.
Active layers
- Declared grain
Hover a layer to read what it holds.
Next: Keep facts and attributes apart
- Grain
- one row per order line
- Joins
- many facts to one
- Test
- unique at the key
Figure. One model at a declared grain, from the grain sentence to the metric. Fact rows carry keys and measures; the three dimension tables are one band and attach to the join independently, so each key lane is a lookup rather than a stage. History handling, surrogate-key generation and column-level tests are omitted.
What to notice. The dimension bands sit below the fact because they are listed after it, not because they load after it: they are peers, and the order among them carries no meaning. What the figure makes visible is that the grain is declared above every band, so a join that fans out changes the meaning of the metric while leaving the query's text unchanged. Price is a dimension attribute only while it is the current price; the price paid is a measure and stays on the fact row.
Text version
A vertical stack for one model. A declared grain, one row per order line, feeds a fact table holding keys and measures. The fact's foreign keys enter a Dimensions group holding three peer tables, Customer, Product and Date. The fact and each dimension then attach to a Join paths band by separate dashed key lanes labelled fact rows, customer, product and date key. Join paths feed a star query model, which produces a metric by summing at the grain. Five steps cover stating the grain, separating facts from attributes, attaching each dimension independently, answering at the grain, and testing uniqueness at the declared key.
- Write the grain as a sentence before creating a table, for example one row per order line per day.
- Separate measured events from descriptive entities, so facts hold keys and numbers while dimensions hold attributes.
- Assign keys deliberately: surrogate keys for dimensions, natural keys for deduplication, and never a key that changes meaning.
- Decide how attribute changes are handled, whether overwrite, type two history or a snapshot, and apply it consistently.
- Document the join paths and their cardinality so consumers know which joins fan out.
- Test uniqueness at the declared grain on every build, because a duplicate key invalidates every downstream sum.
Trade-offs
| Choice | Good for | Cost | Fails when |
|---|---|---|---|
| Normalised model | Integrity and low duplication | Many joins for simple questions | Analysts rebuild the same joins incorrectly |
| Star schema | Fast, predictable analytics | Denormalised attributes and ETL upkeep | Source attributes change constantly and history matters |
| One wide table | Simplest possible queries | Update cost and inconsistent grains | Metrics at different grains get mixed in one row |
| Type two history | Point-in-time correctness | More rows and harder joins | Nobody filters on the validity window |
Good for
Integrity and low duplication
Cost
Many joins for simple questions
Fails when
Analysts rebuild the same joins incorrectly
- Analysts rebuild the same joins incorrectly
- Source attributes change constantly and history matters
- Metrics at different grains get mixed in one row
- Nobody filters on the validity window
Figure. Each choice with what it buys, what it costs, and the case where it loses.
What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.
Text version
Four trade-offs: Normalised model is good for Integrity and low duplication, costs Many joins for simple questions, and fails when Analysts rebuild the same joins incorrectly. Star schema is good for Fast, predictable analytics, costs Denormalised attributes and ETL upkeep, and fails when Source attributes change constantly and history matters. One wide table is good for Simplest possible queries, costs Update cost and inconsistent grains, and fails when Metrics at different grains get mixed in one row. Type two history is good for Point-in-time correctness, costs More rows and harder joins, and fails when Nobody filters on the validity window
Failure modes
- Grain collision. Two rows per key appear after a join and totals double. Debug: group by the declared key and count rows greater than one.
- Mixed grain. Header and line attributes sit in one table and per-order metrics overcount. Debug: compare a count of orders with a count of distinct order ids in the same table.
- Unhandled attribute change. A customer's region is overwritten and last quarter's revenue moves to the new region. Debug: re-run a historical report and compare against a stored snapshot.
In production
Test uniqueness and referential integrity at the declared grain on every run. Publish the grain and join cardinality beside the table so consumers do not guess, and alert when a key test starts failing.
Common misconceptions
People think: Normalisation and dimensional modelling are opposites.
Actually: They answer different questions: normalisation protects write integrity, dimensional modelling makes read patterns explicit. Warehouses usually use both.
People think: A wide table removes the need for modelling.
Actually: It removes joins, not grain. Mixed-grain columns still double count and still need a declared meaning.
Interview angle
Lead with the grain and the questions the model must answer, then choose normalised or dimensional form and justify it. Mention slowly changing attributes, key choice and the uniqueness test. A weak answer draws a star schema without saying what one fact row represents.
Sources
- Kimball Group, data warehouse resources — fact and dimension modelling, grain and slowly changing dimensions.
- PostgreSQL documentation, Data Definition — constraints and keys that enforce a model.
- dbt documentation — modelling layers and tests in an analytics project.