Analytical Data Modeling
Choosing the shape of the tables people query, so that the questions the business asks are easy to write, correct by construction and affordable to run.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
What shape should the tables a consumer queries have, so that the questions the business actually asks are easy, correct and cheap to answer?
Analysts writing SQL by hand, a BI tool generating SQL nobody reads, a finance team closing a month against a definition they will defend to an auditor, a data scientist assembling features, and an agent turning a natural-language question into a query. All of them read the model and none of them read the pipeline that built it. The model is the interface.
The unit of this lesson is the table in the serving layer, and the decision it makes is what one row of that table represents. Everything else in the module — facts, dimensions, keys, history — is a consequence of answering that question deliberately instead of inheriting whatever shape the source happened to have.
Replicate the operational schema into the warehouse, table for table, and let analysts join their way to answers. Nothing has to be designed, the copy is mechanical, and for the first few months the analysts genuinely do get their answers. It is the cheapest possible starting point and it is right more often than modelling purists admit.
The operational schema is normalised for writes, so a simple question — revenue by country by month — becomes an eight-table join that every analyst writes slightly differently, and the four versions disagree by a percent or two that nobody can explain (Normalization: 1NF to BCNF).
- The operational schema is normalised for writes, so a simple question — revenue by country by month — becomes an eight-table join that every analyst writes slightly differently, and the four versions disagree by a percent or two that nobody can explain (Normalization: 1NF to BCNF).
- The application team splits
ordersintoordersandorder_headersduring a refactor. That is a correct operational change and it breaks every analytical query at once, because consumers were coupled to a schema nobody promised them (Schema Leakage). - Someone asks what a customer's pricing tier was at the moment they placed an order in March. The replicated table holds only the current tier; the March value was overwritten by an
UPDATEand is not recoverable from anywhere (Slowly Changing Dimensions). - Two analysts both compute "active customers". One counts customers with an order in the window; the other counts customers whose account is not closed. Both are defensible, both are shipped to executives, and there is no artefact in the platform that says which one the company means (The Metrics Layer).
- The question "revenue by product category" arrives, and the answer requires joining an order-level table to a line-level table. The join multiplies the order-level
shipping_feeby the number of lines, and revenue comes out high. Nothing errors (Grain: What Does One Row Represent?).
What is actually happening
- An analytical model is a denormalisation with a purpose. Operational schemas minimise redundancy because redundancy is a write problem: the same fact stored twice can disagree. Analytical schemas accept redundancy because the write is a controlled, once-daily, single-writer process, and the thing being minimised is instead join count and interpretive ambiguity (Denormalization on Purpose).
- The dominant shape is a small number of fact tables holding measurements of business processes, surrounded by dimension tables holding the descriptive context you filter and group by. That split is not aesthetic: facts grow with events and dimensions grow with entities, and the two have completely different volume, update and history behaviour (Fact Tables, Dimension Tables).
- The model encodes decisions that are otherwise made silently in each query: what one row means, which attributes are available to group by, whether history is preserved, and which measures are safe to sum. Every decision you do not encode gets made by whoever writes the next query, differently each time.
- A model is also a cost structure. Query cost in an analytical system is dominated by bytes scanned and bytes shuffled, and both are decided by how many rows the fact table has, how wide it is, and how many joins the typical question needs (Scan Cost).
- The model does not have to be a star. Wide flat tables, activity schemas, Data Vault and one-big-table designs all exist and all work in the contexts they were designed for. The star is dominant because it is the shape a human and a BI tool both find easy to reason about, and because its grain is explicit (Star Schema).
The model is a decision about which questions are cheap
Every analytical model makes some questions trivial and others expensive or impossible, and it does so before a single row is loaded. That is the whole content of the discipline: you are not organising data for its own sake, you are pre-paying for a set of questions.
The choice is usually presented as star versus snowflake versus one-big-table, which frames it as a taste question. It is not. It is a question about how many distinct question shapes you have, how often the descriptive attributes change, how many consumers must agree on definitions, and whether historical states of those attributes will be asked for.
The options below are all in production somewhere, at companies that made the right call for their situation. The criteria are the lesson; there is no winner, and any source that names one is selling a product.
How many distinct question shapes do you serve, and do any of them need the past state of a descriptive attribute?
when One source, few analysts, questions are about current state, and the operational schema is stable enough that a refactor is rare.
cost Analysts write long joins and disagree subtly; every production refactor is an analytics incident; historical questions about overwritten attributes are unanswerable. Buys zero modelling work and maximum freshness.
when Several question shapes, multiple sources, a BI tool generating SQL, and more than a couple of teams that must agree on what a metric means.
cost A transformation layer, tests, and a grain decision you cannot easily reverse. Buys short queries, predictable joins, explicit grain and a shared vocabulary (Star Schema).
when Dimension hierarchies are large, genuinely shared, and change often enough that storing them once matters — product taxonomies and geography are the recurring examples.
cost More joins per question and a harder model for humans and BI tools to browse. Buys a single place to correct a hierarchy (Snowflake Schema).
when One dominant question shape, a columnar engine, and consumers who benefit more from having no joins than from being able to restate an attribute.
cost Every attribute correction is a rewrite of history; the same attribute is duplicated across every table that carries it, so they can drift. Buys the simplest possible query and the best scan locality.
when The business is genuinely event-shaped, the questions are about sequences and funnels, and you need to reconstruct state at arbitrary points in time.
cost Every state question becomes a fold over history, which is more expensive and much harder to express. Buys the ability to answer questions you had not thought of (Event vs Snapshot Modeling).
Same data, four models, four sets of answerable questions
It helps to take one business — orders, customers, products, payments — and ask what each candidate model can and cannot answer. The interesting column is not "how fast" but "what becomes impossible", because impossibility is what you discover last.
Read the last column first. Every model in the table below answers the everyday questions; they differ in what they quietly refuse, and the refusal is never an error message. It is a query that returns a plausible number computed against information the model does not hold.
This table is also the argument for keeping raw and staging layers underneath whichever model you choose. The model is a bet on which questions matter, and the cheapest way to be wrong about that bet is to be able to build a second model from the same retained inputs (Keeping Raw History: The Recovery Position and the Liability).
| Model | One row is | Easy questions | Expensive questions | Silently unanswerable |
|---|---|---|---|---|
| Mirror of production | Whatever the source table's row was | Anything about current state, at full source detail | Anything needing several sources joined, or a wide scan across history | Any question about a past value of an overwritten column — returns today's value with no warning |
Star: fct_orders + dimensions | One order, at a declared grain | Revenue by any dimension attribute, over any period, grouped and filtered freely | Line-level questions — product mix within an order — which the grain cannot express | Line-level detail, and history of dimension attributes unless SCD2 was built |
Star at line grain: fct_order_lines | One line of one order | Product mix, basket composition, per-product margin, plus every order-level question via aggregation | Order-level measures need care: shipping_fee repeated on each line double counts if summed naively | Nothing structural — but order-level measures stored at line grain are a permanent trap (Grain: What Does One Row Represent?) |
Daily snapshot: account_balance_daily | One account on one day | "What was the balance on 14 March", trends, cohort states, point-in-time joins | Anything about what caused a change — the snapshot records state, not transitions | The order of two changes within a single day, and any change that was reverted before the snapshot ran (Snapshot Tables) |
Modelling backwards from the question
The characteristic failure of this discipline is designing from the source outward: look at what tables exist, copy them, tidy them, and hope the questions fit. It feels productive because there is always another source to onboard, and it produces a warehouse full of datasets with no consumer.
The alternative is mechanical. Take a real question, in the business's own words. Write the SQL you *wish* you could write. Then work out what tables would have to exist for that SQL to be legal and correct. That is the model, and it takes an afternoon rather than a quarter.
The SQL below is what an analyst wants to write for "revenue and order count by customer country and month, for the last year, excluding cancelled orders". Notice how much of the model is implied by six lines: a fact at order grain, a customer dimension carrying country, a date dimension carrying month, a status attribute that is filterable, and a decision about whether country means today's country or the country at the time of the order.
Enumerate the source systems. Ingest each one. Clean each one. Produce a staging model per source table. Then invite analysts to build what they need on top, and count the number of onboarded sources as progress.
Collect the questions the business asks now and the ones it will ask at the next board meeting. For each, write the target SQL. Derive the minimum set of facts and dimensions that makes all of them legal. Ingest only what those need, and let the remaining sources wait until a question requires them.
The set of ingestible sources is unbounded and the set of asked questions is small and knowable. Working source-outward optimises a quantity nobody consumes, and it defers the only irreversible decision — the grain of the fact tables — until after history has accumulated at whatever grain the sources happened to have.
1-- The question, as the business asks it:2-- "Revenue and order count by customer country and month,3-- last 12 months, excluding cancelled orders."4 5SELECT d.year_month,6 c.country,7 COUNT(*) AS orders,8 SUM(f.revenue) AS revenue9FROM fct_orders f10JOIN dim_date d ON d.date_key = f.date_key11JOIN dim_customer c ON c.customer_key = f.customer_key12WHERE d.date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'13 AND f.order_status <> 'cancelled'14GROUP BY 1, 2;15 16-- Six lines of SQL have just specified the entire model:17-- fct_orders one row per order (grain, declared)18-- .revenue additive over any grouping (measure type, declared)19-- .order_status a filterable fact attribute (degenerate dimension)20-- dim_date one row per calendar day (so months exist without date maths)21-- dim_customer one row per customer VERSION (or per customer — this is the SCD decision)22-- *_key surrogate keys, not source ids (so versions are joinable)The unresolved decision is dim_customer. If it holds one row per customer, country means "wherever they live today" and the March number changes when someone moves. If it holds one row per customer version, country means "where they lived when the order was placed" — and the join above needs a predicate it does not currently have (SCD Type 2 in Practice).
How to build it
Most important first.
- Start from the questions, not the sources. Write down the ten questions the business asks most, and for each one name the grain it needs, the attributes it groups by, and whether it needs the past state of those attributes. That list determines the fact tables, the dimensions and the history strategy, in that order.
- Declare the grain of every fact table in one sentence and put it in the table description. If the sentence needs an "and" or a "usually", the grain is not decided yet (Grain: What Does One Row Represent?).
- Model at the finest grain the source supports, then aggregate. A line-level fact can always answer an order-level question; an order-level fact can never answer a line-level one, and discovering that six months later means a backfill (Backfills).
- Put descriptive attributes in dimensions and keep facts numeric and narrow. A fact table that grows a
customer_countrycolumn has just made country history impossible to change and duplicated a value that belongs in one place (Dimension Tables). - Decide history per attribute, not per table. Most dimension columns can be overwritten; a small number cannot, and those are the ones a report will be run against a year from now (Slowly Changing Dimensions).
- Define each measure once, in a place that generates the SQL, so "revenue" cannot mean two things in two dashboards (The Metrics Layer).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- A model guarantees a shape, not a truth. Declaring the grain guarantees that a correct query is expressible; it does not guarantee that the rows loaded into it are complete or correct.
- A star schema guarantees that the fan-out behaviour of a join is predictable: fact-to-dimension on a unique dimension key produces exactly as many rows as the fact had. That guarantee evaporates the moment the dimension has duplicate keys (Surrogate Keys).
- Nothing in the model guarantees that a measure is additive.
SUM()will happily run over a ratio, a balance or a rate and return a number with no meaning (Fact Tables). - History is guaranteed only where you built it. A dimension with no versioning silently answers historical questions with today's answer, and reports no error while doing so (SCD Type 2 in Practice).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The strongest single check on a model is a grain assertion: a uniqueness test on the declared business key of every fact table, run on every load. It catches fan-out joins, non-idempotent re-runs and duplicate source events in one query, and it is the check that most directly protects the metrics people look at (Data Tests).
- Pair it with referential integrity: every dimension key present in the fact must exist in the dimension. A key that does not resolve produces silently dropped rows on an inner join, which looks like a business decline rather than a defect.
- Both checks miss the thing that matters most — whether the measure means what the dashboard label says. A
revenuecolumn that changed from gross to net passes uniqueness, passes referential integrity, reconciles against a source that also changed, and is wrong in every report (Semantic Changes).
- Modelling shifts work from query time to load time. A well-modelled fact table is fast to query and is only as fresh as the job that built it, which is a trade a consumer must be told about — the mart is always behind the source it derives from (Data Marts).
- Denormalised attributes freeze at load time. If
dim_customeris refreshed daily and a customer changed segment this morning, every query today returns yesterday's segment, correctly and invisibly. - The freshness a model can offer is bounded by its most expensive dependency. A fact table that joins five dimensions is no fresher than the slowest of the six inputs, and publishing an average freshness across them hides exactly the one that is stale (Freshness Monitoring).
- Adding a dimension attribute is the safe change: existing queries do not see it, and the column can be backfilled or left null for history. Say explicitly what the null means, because consumers will assume it means zero (Nullability & Defaults).
- Changing a grain is not a schema change, it is a new table. A fact table whose grain moves from order to order-line has invalidated every aggregate written against it, and the old queries still run (Breaking Schema Changes).
- The dangerous evolution is a definition change with no schema footprint: a filter added to the model that excludes cancelled orders, applied from today forward. The column list is identical, the history is now inconsistent, and no compatibility check exists that would fire (Semantic Changes).
- A model built as a deterministic function of retained raw data can be rebuilt from scratch. That property — not any specific schema — is what makes a modelling mistake survivable, and it is why raw retention is a modelling concern and not just a storage one (Keeping Raw History: The Recovery Position and the Liability).
- The one thing a rebuild cannot recover is history you never captured. If the source overwrote a dimension attribute and you did not version it, no re-run produces the March value, ever (SCD Type 2 in Practice).
- Rebuild into a new location and swap, so consumers never observe a half-built model. A remodelling that mutates the table people are querying turns a design improvement into an incident (Atomic Publish).
What can go wrong
- A fact table whose grain was never written down, so each analyst infers a different one from the column names.
- A dimension that gained duplicate rows during a re-run, fanning out every fact joined to it and inflating every measure downstream (Duplicate Rows).
- A model that answers today's questions perfectly and cannot answer any historical one, discovered at the first audit.
- An over-modelled platform: forty models, six layers and a lineage graph nobody can hold in their head, serving four dashboards (Data Platform Anti-Patterns).
- The mitigation failing: a metrics layer that defines revenue once, bypassed by an analyst who queried the fact table directly because the metrics layer was slow.
- "Dimensional modelling is obsolete now that storage is cheap." Storage was never the argument. The argument is that a declared grain and a shared set of dimensions make queries writable by humans and comparable across teams, and that argument is unaffected by storage price.
- "A wide flat table is simpler." It is simpler to query and harder to maintain: every attribute change becomes a rewrite of history, every attribute is duplicated across every table that carries it, and every consumer that assumed a column meant one thing has to be found by hand. The trade is maintenance, not elegance.
- "The warehouse should mirror production so the numbers match." Mirroring production guarantees your analytics break whenever production is refactored, and it still does not give you history.
- "We will model it properly later." Later means after a year of history exists in the wrong shape, which is the most expensive moment to do it (What Backfills Break).
Operating it
- Row count per fact table per load, against its own history. A model change that alters grain shows up here first, as a step change nobody announced (Volume Anomalies).
- Failed-key rate: the share of fact rows whose dimension key did not resolve. It should be flat and near zero; a rise means an upstream dimension is late or an id space changed.
- Query logs by table. A model nobody queries is cost with no consumer, and a model queried by eighty dashboards is one whose grain you may no longer change (Impact Analysis).
- At 10x volume, grain choices that were merely inelegant become expensive — a line-level fact where the questions are order-level now scans several times what it needs to.
- At 100x, dimension size starts to matter as much as fact size. A dimension too large to broadcast forces a shuffle join, which changes the runtime profile of every query that touches it (Broadcast Joins).
- Consumer count scales the *definitional* problem rather than the technical one. Four analysts can agree on what revenue means in a conversation; eighty cannot, and the model has to carry the agreement instead (The Metrics Layer).
- Fact row count times row width is the scan cost of every question asked at that grain. Choosing a finer grain than the questions need multiplies the cost of every query forever (Scan Cost).
- Each additional join in the typical question costs a shuffle or a broadcast. Denormalising a hot attribute into the fact removes that join and costs storage plus the ability to restate it (Broadcast Joins).
- Pre-aggregated marts trade storage and pipeline complexity for query cost, and are worth it exactly when many consumers ask a similar question repeatedly (Data Marts).
- The largest cost is usually rework: discovering the grain was wrong after a year of history exists, and paying for a full reprocess plus a migration of every downstream consumer (Compute Waste).
- A modelled warehouse costs a transformation layer, a set of tests, and the discipline to change it deliberately. It buys queries that are short enough to be right and a shared vocabulary. Small teams querying one source often should not pay it.
- Denormalisation buys query simplicity and speed and costs the ability to restate an attribute without rewriting history. Every denormalised column is a copy that can drift.
- Modelling for the questions you have makes the questions you do not have harder. The mitigation is keeping raw and staging layers so a new model is a new build, not a migration (Raw, Staging, Curated: Layers by Purpose).
Modeling lab — one grain, ten questions
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| 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. |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe separation of measurements from descriptive context, and the requirement to declare what one row means, hold in every analytical store. What varies is how much denormalisation the engine rewards — a columnar warehouse and a document store make very different trades here.
- WAREHOUSE-SPECIFICEngines that broadcast small dimensions cheaply make star schemas nearly free to join, so normalising dimensions costs little; engines without that optimisation punish the extra hop, which is why the same schema advice reverses between a columnar warehouse and a single-node engine reading remote files.
- SCALE-SPECIFICBelow a few million fact rows and a handful of analysts, querying replicated source tables is a legitimate model and a star schema is ceremony. The advice inverts once several sources must be joined or once history questions appear, because neither is expressible against a mirror of production.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns how a model change is versioned, reviewed, deployed and rolled back. A dimensional model is software with a release process, and treating it as configuration is how definition changes reach production unreviewed.