Data Platform & Analytics · Chapter 2 of 16
Modelling, and why it still decides everything
The least fashionable skill in the field and the one that determines whether the platform is usable in three years.
Storage is cheap and compute is elastic, which has produced a widespread belief that modelling no longer matters — land everything raw and figure it out later. What actually happens is a warehouse nobody can navigate, six tables that all claim to be the customer, and an analyst population that has quietly given up and gone back to spreadsheets.
The layers worth keeping separate
| Layer | What it holds | The rule |
|---|---|---|
| Raw | Source data, unmodified, append-only | Never transform here. It is your ability to reprocess |
| Staging | Lightly cleaned, typed, deduplicated | One staging model per source table, no business logic |
| Core | Conformed business entities — customer, order, account | One definition per concept. This is the whole point |
| Marts | Shaped for a consumer — finance, growth, a dashboard | Denormalise freely here; it is cheap and it is meant to be disposable |
The two questions that expose a bad model
- “How many places define active customer?” If the answer is more than one, every number in the business is negotiable and every meeting starts with a reconciliation.
- “Can I recompute last year's report and get last year's number?” If dimensions are overwritten in place, the answer is no, and every historical comparison is quietly wrong. This is what slowly-changing dimensions exist for, and it is routinely skipped because it is tedious.
Grain is the thing to get right first
State the grain of every table in one sentence — one row per order line per day. Almost every subtle data bug is a grain mistake: a join that fans out, a sum that double-counts, a metric that changes when a dimension gains a row.
If a table's grain cannot be stated in a sentence, it is not modelled, it is accumulated.
Grain: the one concept that prevents the most damage
The grain of a table is what one row means. One order. One order line. One order line per day. It sounds trivial and it is the source of the most expensive silent errors in analytics, because getting it wrong produces numbers that are wrong without being obviously wrong.
The failure, worked
You have an orders table at one row per order, with a total. You join customer support tickets to it because someone wants revenue by support-contact status.
An order with three tickets now appears three times. The join succeeded, every row is valid, the row count went up by a plausible amount, and revenue is now overstated.
Nothing errors. No test fails unless someone wrote one asserting the grain. Distribution monitoring will not catch it — the values are all real values. The dashboard is simply wrong, and it stays wrong until someone reconciles against finance.
The defence is one line and almost nobody writes it: a uniqueness test on the grain key of every table you build. If the table is one row per order, assert that order ID is unique. It costs nothing, it never has a false positive, and it catches this entire class.
State the grain in the model's description too — the first sentence, always. Half the joins that break grain are done by someone who could not tell what a row meant.
Handling change: the choice that gets made by accident
| Approach | What happens when a customer moves city | Use when |
|---|---|---|
| Overwrite | History is rewritten — last year's sales now show the new city | The attribute is a correction, not a change. Fixing a typo |
| Add a row with dates | Last year's sales stay attributed to the old city | The default for anything you report on over time. Costs a join on date range |
| Keep a 'previous value' column | You can see one step back, not the full history | Rarely worth it — it looks cheap and satisfies almost no real question |
| Snapshot the whole table daily | Everything reconstructible, storage grows linearly | Underrated. On cheap columnar storage this is often the right answer and is far simpler than the alternatives |
The decision is usually made by accident — the pipeline overwrites because that is what a straightforward upsert does, and nobody chose it. Then a year later someone asks a historical question and the answer no longer exists. Ask which attributes need history before the first load, because you cannot recover it afterwards.
Check yourself