Data Modeling
What does one row mean, who is allowed to change that answer, and what breaks downstream when they do?
Who this mechanism is for: a developer who writes code, calls APIs and knows basic SQL (SELECT, JOIN, GROUP BY, SUM), but has never designed a data warehouse or studied dimensional modeling. You do not need any database-internals knowledge. Every technical term is explained the first time it appears, and again in the glossary at the end. Unlike most technical topics, this mechanism is about meaning and agreement between people. The code is easy. The hard part is that nothing in the machine tells you when a model is wrong.
The story we will follow
Nadia is an analytics engineer at Fernwood Outfitters, an online shop that sells camping gear: tents, stoves, boots, sleeping bags. Fernwood keeps its data in a warehouse (a database built for analysis, not for running the shop). Owen is the finance controller. He reads the revenue numbers in the monthly board report. Sales, marketing, support and finance all build their own dashboards on the same warehouse.
During one quarter Nadia will meet eleven problems, one at the start of each article below. Each problem looks like a bug. None of them is a bug. In every case the machine did exactly what it was told, and the number on the screen was still wrong or disputed. At the end of each article, we go back and solve her problem with the mechanisms of that part.
This is the main idea of this whole mechanism:
How this mechanism is organized
- 1
A layer with no oracle
Why is data modeling a different kind of problem from the storage and execution problems below it? What are the four basic ideas it reduces to?
Case$4.20M or $4.79M?
- 2
Grain and table shape
What is one row? When is it worth splitting tables, and when is that advice out of date?
CaseMonday counted twice
- 3
Conformed dimensions
How do many teams make their numbers comparable without N² private translations?
CaseGeorgia in Europe, Georgia in Asia
- 4
Time and keys
Which version of a customer does an old sale belong to, and who decides?
CaseLast year's report changed after the fact
- 5
Measures
Which numbers can be summed, over which axes, and what table shape follows?
CaseAverage margin 30%, or 14%?
- 6
Many-to-many
Why do totals become too large after a correct-looking join, and what is an honest fix?
CaseA leaderboard larger than total revenue
- 7
Binding styles
Who pays for the join: the modeler once, or every reader forever?
CaseThree systems, three addresses, one auditor
- 8
Meaning and identity
How do we stop every team from defining "active customer" differently, and how do we decide that two records are one person?
CaseThree numbers for active customers
- 9
Hierarchies and nested data
How do you store trees and arrays, and what does each choice cost on a change?
CaseA reorganization that touches 6,000 rows
- 10
Pipelines and events
Where do you checkpoint a pipeline, and what changes if events, not tables, are the record?
CaseA 45-day bug and 30 days of source files
- 11
What data modeling forbids
What does the whole layer forbid, what do the escapes cost, and which laws repeat?
CaseNever two numbers again?
After that: a summary, hands-on labs, interview questions and a glossary.
One request: read the "Compression" sentence at the end of each major section. It is the shortest honest statement of what the section teaches.
What do I do, and what does each option cost?
Can I see it myself?
SCD Type 2: as-was against as-is, and a late arrival
Keep a customer's history with SCD type 2, answer as-was and as-is, place a late fact, and count the cost of a late correction.
SimulatePythonAverage of ratios against ratio of sums
Compute a margin rate as an average of daily ratios and as a ratio of sums, at day, month and year level.
SimulatePython with NumPyReproduce a fan trap and a chasm trap
Sum a measure across a join to a finer grain and across two independent branches, then fix both by aggregating first.
Break itPythonCost of a dimension change: star against OBT
Rename one product category in a star and in one big table: one dimension row against a 20-million-row rewrite.
MeasurePython with DuckDBThe definition-variant experiment
Count "active customers in September" three reasonable ways and trace each difference to an unwritten edge case.
SimulatePython with DuckDBEntity resolution: blocking and the threshold
Find typo duplicates among 22,000 customers: blocking cuts the pairs to compare, and the threshold trades false merges for misses.
SimulatePython
Can I reason about it under questioning?
Interview questions
Short questions on analytical data modeling, such as grain, star schemas, conformed dimensions, slowly changing dimensions, measures, many-to-many relationships, modeling styles and semantic layers, each with an answer you could give aloud and a link to the full explanation.
Scenarios
Longer problems on analytical data modeling, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.
The tensions this mechanism takes a position on. Each recurs across mechanisms.
Precompute vs. compute-on-read
For example: materialized views, ELT vs. ETL, schema-on-read.
Early binding vs. late binding
When structure is committed; determines where the failure surfaces, at write time or read time.
Mutability vs. immutability
In-place update vs. append-only + GC; buys reproducibility and cheap concurrency, pays in space and janitorial work.
Read-optimized vs. write-optimized
For example: B-tree vs. LSM, merge-on-read vs. copy-on-write.
Coupling vs. coordination cost
For example: 2PC vs. sagas, schema contracts vs. schema-on-read.
Show all 6 trade-offsShow fewer
Ideas this mechanism meets that keep coming back, mechanism after mechanism.
Costs are conserved
Costs are conserved, never removed.
Granularity mismatch
Granularity mismatch creates amplification.
Coordination scales with parties
Coordination cost scales with the number of parties who must agree, not with the volume of data.
Immutable storage grows indirection
An immutable substrate grows an indirection layer plus a collector.
Independence is assumed
Independence is assumed everywhere and is true almost nowhere.