Skip to content
AI Engineer Guide.

concept · data · intermediate · concept 4 of 17

Data modelling

0 of 17 complete

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

One grain, then facts, dimensions and the metricInteractive
signalbypass

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
Step 1/5 · State the grain

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.

  1. Write the grain as a sentence before creating a table, for example one row per order line per day.
  2. Separate measured events from descriptive entities, so facts hold keys and numbers while dimensions hold attributes.
  3. Assign keys deliberately: surrogate keys for dimensions, natural keys for deduplication, and never a key that changes meaning.
  4. Decide how attribute changes are handled, whether overwrite, type two history or a snapshot, and apply it consistently.
  5. Document the join paths and their cardinality so consumers know which joins fan out.
  6. Test uniqueness at the declared grain on every build, because a duplicate key invalidates every downstream sum.

Trade-offs

ChoiceGood forCostFails when
Normalised modelIntegrity and low duplicationMany joins for simple questionsAnalysts rebuild the same joins incorrectly
Star schemaFast, predictable analyticsDenormalised attributes and ETL upkeepSource attributes change constantly and history matters
One wide tableSimplest possible queriesUpdate cost and inconsistent grainsMetrics at different grains get mixed in one row
Type two historyPoint-in-time correctnessMore rows and harder joinsNobody filters on the validity window
Trade-off mapInteractive

Good for

Integrity and low duplication

Cost

Many joins for simple questions

Fails when

Analysts rebuild the same joins incorrectly

  1. Analysts rebuild the same joins incorrectly
  2. Source attributes change constantly and history matters
  3. Metrics at different grains get mixed in one row
  4. 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