Chapter 6: Data Modeling

"Never model the data. Model the business process." — the discipline behind Ralph Kimball's four-step design method

Overview

This is the oldest material in the book and the most durable.

Dimensional modeling was formalized by Ralph Kimball in the 1990s, for hardware that no longer exists, using tools that are gone, against constraints that have relaxed by three orders of magnitude. It has outlived all of it. The star schemas at the center of a well-run Snowflake or BigQuery warehouse in 2026 are recognizably the same artifacts Kimball described in 1996, and the reason is that dimensional modeling was never really about the hardware. It was about how people ask questions.

That durability is the argument for spending four hours here. A well-modeled warehouse on mediocre infrastructure beats a badly modeled one on excellent infrastructure, every time, and the gap widens with age — because infrastructure gets replaced every few years and the model gets inherited. Chapter 1's Case Study 1 makes the point concretely: the metric definitions written in month two of Kestrel's data function survived three complete technology migrations and are still the most valuable artifact produced that year.

There is a second reason, less often stated. Modeling is where you find out that the business does not agree with itself. You cannot declare the grain of a fact table without deciding what an order is. You cannot define net_revenue_cents without resolving what happens to cancelled orders, partial returns, and shipping charges. Those questions have owners, the owners disagree, and the disagreements have been latent for years. Modeling surfaces them. That is uncomfortable and it is the job — and it is why §6.3's first step is choosing a business process rather than choosing tables.

One thing to set aside before we start. There is a decades-old argument between Kimball's dimensional approach and Bill Inmon's normalized enterprise warehouse, and it is a real argument with real substance on both sides. This book takes Kimball's side, and §6.2 says why, but the honest summary is that most modern platforms are hybrids: a normalized-ish silver layer that conforms and cleans, and a dimensional gold layer that people query. If you have heard that the argument was settled, it was not; it was absorbed.

In this chapter, you will learn to:

• Explain why the shape that makes a transactional database fast makes an analytical query slow, in terms of what each is optimizing.
• Apply Kimball's four-step design process, in order, to a business process you have not seen before.
• Declare the grain before choosing columns, and understand why reversing that order is the single most common cause of a warehouse that cannot answer questions.
• Distinguish additive, semi-additive, and non-additive measures and predict which aggregations each one breaks under.
• Choose between surrogate and natural keys with a clear statement of what each costs.
• Build a bus matrix and use it to find your conformed dimensions.
• Apply the seven patterns — degenerate, junk, role-playing, bridge, factless, periodic snapshot, accumulating snapshot — that cover the large majority of real requirements.
• Diagnose the eight common mistakes from the symptoms they produce in production.

Who needs this chapter: everyone, and it is on every learning path including Quick Start. If you read only four chapters of this book, this is one of them.

6.1 Why Model At All

A reasonable objection, and it is worth taking seriously before spending four hours: modern query engines are fast, storage is cheap, and you could simply copy the source tables into the warehouse and let analysts join them. Why introduce a modeling layer at all?

Three answers, in increasing order of importance.

Performance, which matters least. Star schemas are faster to query than normalized schemas because they require fewer joins and because query optimizers have been specifically tuned for the star pattern for thirty years. This was the original motivation and it is now the weakest one — modern columnar engines will happily join a dozen tables. Do not model for performance.

Comprehensibility, which matters a lot. Kestrel's source database has twelve tables with foreign keys in both directions, columns whose meaning depends on the value of other columns, and status enumerations that encode business process. An analyst asking "revenue by category last quarter" has to know that orders.status must be filtered, that order_items.discount_cents is subtracted rather than added, that a returned line appears in returns rather than being removed from order_items, and that products.category_id points at a hierarchy they must walk. A dimensional model puts all of that into the model once so that nobody has to know it again.

Semantic stability, which matters most. This is the one people underrate.

Source systems change because the application changes. A product team refactors the orders service, splits a table, renames a column, adds a status. Those are good changes and they will keep happening. A dimensional model is a stable interface over an unstable source. fct_order_item means the same thing this year as last year, even though the three tables underneath it were restructured in March, because the model is defined by the business process rather than by the current shape of the application.

That is the same argument as an API contract, and it deserves the same respect. Your consumers — analysts, data scientists, dashboards, other pipelines — are coupled to your model. Everything in Chapter 17 about data contracts applies here, pointed downstream instead of upstream.

🏭 From the Pipeline — The refactor that broke nothing

A retailer's engineering team split its monolithic orders table into orders, order_fulfillment, and order_payment_state — a genuinely good change, driven by a real scaling problem in the checkout path.

The data team was told two weeks in advance. They changed one file: the silver-layer model that reads the source. fct_order and fct_order_item were unchanged. Forty-one downstream objects — dashboards, dbt models, two ML feature sets — were unchanged. No consumer knew it happened.

A year earlier, the same team had no modeling layer; dashboards read source tables directly. A smaller source change — one column renamed — had broken nine dashboards and taken four days to chase down, because there was no list of who read what.

The modeling layer is where you absorb upstream change so your consumers do not have to. That is its most valuable property and it is invisible until the day it works.

6.2 Normalization, and Why Analytics Undoes It

To understand what dimensional modeling is doing, you need to know what it is a reaction against.

What normalization optimizes

Normalization organizes data to eliminate redundancy. Third normal form, informally: every non-key column depends on the key, the whole key, and nothing but the key.

The purpose is update integrity. If a product's name is stored in exactly one place, renaming it is one write and cannot produce inconsistency. If it were duplicated across six million order lines, renaming it would be six million writes and any failure partway through would leave the database disagreeing with itself.

That is exactly what a transactional system needs. Kestrel's checkout writes an order, its lines, and a payment in one transaction, touching a handful of rows. Normalization makes those writes small, fast, and safe.

What analytics needs instead

Analytical queries have the opposite shape:

The last row is the whole point. Analytical queries are dominated by scanning and joining, and every join costs something. A normalized model that makes writes safe makes reads expensive, and an analytical system does not need the write safety because it does not accept writes from users — it is rebuilt from a source of truth that has its own integrity guarantees.

So dimensional modeling deliberately denormalizes. dim_product carries the category name, the category hierarchy, the brand, and the supplier, all duplicated across rows, because duplication costs storage — which is cheap — and saves joins, which are not.

Star and snowflake

              STAR                                    SNOWFLAKE

     ┌────────────┐                           ┌────────────┐
     │dim_customer│                           │dim_customer│
     └─────┬──────┘                           └─────┬──────┘
           │                                        │
┌──────────┴──────────┐                  ┌──────────┴──────────┐
│                     │                  │                     │
│  fct_order_item     │                  │  fct_order_item     │
│  (the facts)        │                  │                     │
│                     │                  │                     │
└──────────┬──────────┘                  └──────────┬──────────┘
           │                                        │
     ┌─────┴──────┐                           ┌─────┴──────┐
     │dim_product │  ← category name,         │dim_product │  ← category_id only
     │            │    brand, supplier        └─────┬──────┘
     └────────────┘    all denormalized             │
                                              ┌─────┴──────┐
                                              │dim_category│
                                              └─────┬──────┘
                                                    │
                                              ┌─────┴──────────┐
                                              │dim_dept        │
                                              └────────────────┘

In words: a star schema has one fact table surrounded by dimension tables, each joined directly, with hierarchies flattened into the dimension. A snowflake schema normalizes those hierarchies into separate tables, so a product joins to a category which joins to a department.

Prefer the star. The snowflake saves a trivial amount of storage, adds joins to every query, and makes the model harder for a human to read — and the human is the constraint. The one situation where snowflaking is defensible is a dimension with a genuinely enormous, rapidly changing hierarchy where the duplication is large and the churn is high, which is rare.

📐 Design Decision — Kimball versus Inmon, and why this book picks one

Bill Inmon's position: build a normalized, enterprise-wide, integrated warehouse first — the single source of truth — and derive dimensional data marts from it. Ralph Kimball's position: build dimensional models directly, per business process, integrated by conformed dimensions, without an intervening normalized layer.

The case for Inmon, stated fairly: an enterprise-wide normalized model enforces integration at the point where the data enters, prevents the same entity being modeled inconsistently in two marts, and produces a durable representation independent of any particular reporting need. In a large, regulated, slow-moving organization this is genuinely valuable.

The case for Kimball, which is why this book uses it: it delivers value incrementally, business process by business process, rather than requiring an enterprise model before anything ships. The models are directly comprehensible to business users. And conformed dimensions provide integration without requiring the whole enterprise to be modeled first.

What choosing Kimball gives up: without discipline, per-process marts do drift apart, and conformed dimensions are a practice rather than a structural guarantee. The failure mode is real and this book's answer to it is §6.6's bus matrix plus the testing in Chapter 23.

What most modern platforms actually do is a hybrid that neither author would fully endorse: a silver layer that cleans and conforms without full normalization, and a dimensional gold layer. Chapter 34 formalizes it. The argument was not settled; it was absorbed.

6.3 The Four-Step Design Process

Kimball's four steps, in order. The order is the method. Almost every bad dimensional model is the result of doing these out of sequence — usually by starting from the available source columns and working backwards, which is step 4 first.

Step 1 — Select the business process

Not a report, not a table, not a department. A business process: an activity the business performs that generates measurable events.

Kestrel's business processes: placing an order, shipping an order, returning an item, browsing the site, counting inventory, running a promotion.

Why this framing and not "what report do they want": a report is one question. A business process generates the data to answer every question about that activity, including the ones nobody has asked yet. Model the process and you get the reports for free; model the report and you get one report and a request for another next week.

The most common error here is selecting a department instead of a process — "let's build the marketing data mart." Marketing is not a process; it asks questions about several processes (browsing, ordering, promotions), and modeling by department is how you get four teams each modeling "orders" slightly differently.

Step 2 — Declare the grain

The grain is what one row of the fact table represents, stated in one sentence, in business language.

One row per order line on an order placed by a customer.

That sentence is the most important artifact in this chapter, and it must be written before choosing dimensions or facts. Two consequences follow immediately, and both are the reason the order is not negotiable:

The grain determines which dimensions are available. At order-line grain you can attach product, because a line has one product. At order-header grain you cannot, because an order has many products.

The grain determines which measures are valid. At order-line grain, quantity and extended_price are fine. shipping_cost is not, because shipping is charged once per order and allocating it to lines requires a rule — and if you do allocate it, you must say which rule, in writing, forever.

Grain rules that are worth memorizing:

• Declare it in one sentence, in business language. If you cannot, you have not decided.
• Choose the finest grain the source supports. You can always aggregate up from atomic detail; you can never disaggregate. A summary table saves nothing worth the questions it forecloses.
• One fact table, one grain. Mixing grains is the mistake in §6.9 that produces numbers nobody can reconcile.
• Write it at the top of the model file, and test it. A declared grain with no test is a comment (Chapter 1's Case Study 2).

Step 3 — Identify the dimensions

Dimensions are the context of the event — the "by what" of every question. Revenue by category by month by channel by region.

The test for whether something is a dimension: can you imagine someone grouping by it or filtering on it? If yes, it is a dimension attribute.

At order-line grain, Kestrel's dimensions are: date, customer, product, promotion, channel, warehouse, and — via a degenerate dimension, §6.7 — the order number itself.

Step 4 — Identify the facts

Facts are the measurements: numeric, and consistent with the declared grain.

At order-line grain: quantity, unit_price_cents, extended_price_cents, discount_cents, tax_cents, cost_cents, net_revenue_cents.

The test for a fact: is it numeric, and does it make sense to add it up across rows? If it is numeric but not additive — a unit price, a percentage, a ratio — you can store it, and you must mark it, because someone will SUM() it. §6.4.

🧪 Try It — Four steps on an unfamiliar process

Do this for Kestrel's returns process, in fifteen minutes, before reading §6.8.

1. Business process: a customer returns an item.
2. Grain: write the one sentence. Careful — is it one row per return, or one row per returned line? A customer can return two of five items from one order. Which supports more questions?
3. Dimensions: list at least six. Include the one that is easy to forget — the reason for the return.
4. Facts: list them. Then find the trap: is the refunded amount always equal to what was originally paid for that line? What about a partial refund, a restocking fee, or a return of an item bought with a promotion?

That last question is where modeling stops being clerical. Write down what you would need to ask the business, and who you would ask.

6.4 Fact Tables

The three types

Transaction fact tables. One row per event, at the moment it happens. Insert-only, atomic, the most common and most useful type. fct_order_item, fct_shipment, fct_return.

Periodic snapshot fact tables. One row per entity per period, capturing state at that moment. Used when the level matters rather than the events: inventory on hand, account balances, subscriber counts. fct_inventory_snapshot at one row per product per warehouse per day.

Snapshots seem wasteful — you write a row for every product every day whether or not anything changed — and they are the right answer anyway, because the alternative ("reconstruct the level by summing all movements since the beginning of time") is expensive, fragile, and gets slower forever.

Accumulating snapshot fact tables. One row per entity, updated in place as it moves through a pipeline with a known set of milestones. One row per order, with columns for placed_at, paid_at, picked_at, shipped_at, delivered_at, filled in as they occur.

This is the exception to append-only, and it earns it: the table makes lag analysis trivial (delivered_at - shipped_at), which is otherwise a self-join across events. Use it only where the milestone set is known and stable.

Additivity — the property that breaks aggregations

This is where a modeling error becomes a wrong number on a dashboard, so it deserves care.

Additive measures can be summed across every dimension. quantity, extended_price_cents, net_revenue_cents. Most facts should be additive; design for it.

Semi-additive measures can be summed across some dimensions but not others — characteristically not across time. Inventory on hand is the standard example: summing across products and warehouses gives total inventory, and summing across days gives a number with no meaning. If you have 400 units on Monday and 400 on Tuesday, you have 400 units, not 800.

Non-additive measures cannot be summed at all. Ratios, percentages, and unit prices. Averaging an average is the classic error: the mean of per-store margin percentages is not the overall margin percentage unless every store has identical revenue.

⚠️ Failure Mode — The semi-additive measure in a BI tool

Nearly every BI tool defaults to SUM for any numeric column. A user drags on_hand_units onto a chart with a month on the axis and gets the sum of thirty daily snapshots — a number roughly thirty times the real inventory, and it looks like inventory grew enormously.

Nothing errors. The chart renders. The number is plausible in shape if not in magnitude, and if the person is looking at a trend rather than a level they may not notice the scale at all.

Four defenses, and you want at least the first two:

1. Name the column so the aggregation is obvious. on_hand_units_eod — end of day — is harder to sum thoughtlessly than inventory.
2. Store the additive version alongside. Keep units_received and units_shipped, which are additive across time, next to the semi-additive on_hand_units_eod. Then the sum question has a correct answer available.
3. Set the default aggregation in the semantic layer to LAST or AVG where the tool supports it.
4. Document it in the model, and test it — a test that the monthly sum of on_hand_units_eod is not being used as a metric definition.

The general rule: mark every non-additive and semi-additive measure explicitly, because the default behavior of every downstream tool is to add it up.

Fact table columns

A fact table row is mostly keys and numbers, and it should stay that way:

CREATE TABLE gold.fct_order_item (
    -- degenerate dimension: the business key, kept for traceability (section 6.7)
    order_id            BIGINT       NOT NULL,
    order_item_id       BIGINT       NOT NULL,   -- the grain key

    -- foreign keys to dimensions
    date_key            INTEGER      NOT NULL,   -- 20251128, not a DATE. Why: section 6.5
    customer_key        BIGINT       NOT NULL,
    product_key         BIGINT       NOT NULL,
    promotion_key       BIGINT       NOT NULL,   -- points at the "none" row, never NULL
    channel_key         SMALLINT     NOT NULL,
    warehouse_key       SMALLINT     NOT NULL,

    -- measures, all integer cents (Chapter 7 section 7.4)
    quantity            INTEGER      NOT NULL,
    unit_price_cents    BIGINT       NOT NULL,   -- NON-ADDITIVE. Do not SUM.
    extended_price_cents BIGINT      NOT NULL,   -- additive: quantity * unit_price
    discount_cents      BIGINT       NOT NULL,   -- additive, stored positive, subtracted
    tax_cents           BIGINT       NOT NULL,   -- additive
    cost_cents          BIGINT       NOT NULL,   -- additive
    net_revenue_cents   BIGINT       NOT NULL,   -- additive: extended - discount

    -- lineage
    _loaded_at          TIMESTAMP    NOT NULL,
    _source_lsn         BIGINT,

    PRIMARY KEY (order_item_id)
);

Four things in that definition are deliberate and worth carrying to your own models.

discount_cents is stored positive and documented as subtracted. Chapter 2 §2.2's sign-convention story is exactly what happens when this is left implicit. Pick a convention, write it in the column comment, and test it (discount_cents >= 0).

promotion_key is NOT NULL and points at a "none" row rather than being null when no promotion applied. Null foreign keys break inner joins and produce silently missing rows. Every dimension gets an explicit "not applicable" member with a reserved key. This is the single cheapest way to eliminate a whole class of join bug.

unit_price_cents is marked non-additive in a comment. Someone will sum it. The comment is not sufficient — the semantic layer and the tests are the real defenses — but it is free.

net_revenue_cents is stored, not computed at query time. It is derivable, which makes storing it redundant, and it is stored anyway because a definition that lives in one column is a definition that cannot drift across nineteen BI queries (Chapter 2's Case Study 2). Store the metric; do not make each consumer re-derive it.

6.5 Dimension Tables

Dimensions are wide, denormalized, and comparatively small. dim_product at Kestrel has 47,000 rows and perhaps forty columns; fct_order_item has 6,480,000 rows a year and about eighteen. That asymmetry is the whole shape of a star schema: spend columns freely in dimensions, spend them carefully in facts.

Surrogate keys

A surrogate key is a meaningless integer generated by the warehouse. A natural key is the business identifier from the source — product_id, sku, email.

Use surrogate keys for dimension primary keys. The reasons, in order of importance:

Type 2 history requires it. If dim_customer keeps a new row every time a customer's address changes (§6.5 below), then customer_id is no longer unique in the dimension and cannot be the key. This is the decisive reason, and it is why surrogate keys and slowly changing dimensions are always discussed together.

Source keys change. Systems get migrated, merged, and renumbered. A surrogate key insulates you.

Multi-source entities. If customers arrive from both the storefront and a acquired company's system, their natural keys collide or overlap. The surrogate key is where you resolve identity.

Performance, marginally. A 4-byte integer join beats a 60-character email join, though on a columnar engine the difference is smaller than it used to be.

What surrogate keys cost, honestly: an extra lookup on every load (you must resolve the natural key to the current surrogate), a debugging tax (customer_key = 88214 tells a human nothing), and the requirement to always carry the natural key alongside for traceability. Do carry it.

The one exception this book makes: dim_date uses an intelligent key, 20251128, an integer of the form YYYYMMDD. It violates the surrogate-key principle deliberately, and the payoff is that partition pruning, range filters, and human debugging all become trivial, while dates — uniquely among dimensions — never change their history.

Slowly changing dimensions

Attributes change. A customer moves; a product changes category. What happens to history?

Type 2 is the workhorse:

CREATE TABLE gold.dim_customer (
    customer_key     BIGINT      PRIMARY KEY,   -- surrogate
    customer_id      BIGINT      NOT NULL,      -- natural key, carried through
    email_hash       CHAR(64)    NOT NULL,      -- Chapter 31: not the raw email
    first_name       TEXT,
    last_name        TEXT,
    country_code     CHAR(2),
    region           TEXT,
    segment          TEXT        NOT NULL,      -- 'retail' | 'wholesale'
    marketing_opt_in BOOLEAN     NOT NULL,
    -- Type 2 machinery
    valid_from       TIMESTAMP   NOT NULL,
    valid_to         TIMESTAMP   NOT NULL,      -- '9999-12-31' for current
    is_current       BOOLEAN     NOT NULL,
    _loaded_at       TIMESTAMP   NOT NULL
);

A customer who moves from Colorado to Oregon gets two rows: one valid from signup to the move with region = 'CO', one from the move to 9999-12-31 with region = 'OR'. An order placed before the move joins to the first row, so a report of revenue by region reflects where the customer lived at the time of the order, which is almost always what the business means.

Chapter 20 implements this properly, including the parts that are fiddly: the "current" sentinel, the half-open interval convention, and what happens when a change arrives out of order.

📐 Design Decision — Type 2 everywhere, or only where asked?

The tempting position is to make every dimension Type 2, on the grounds that you cannot recover history you did not keep.

What Type 2 costs: the dimension grows without bound in proportion to attribute churn. Every load must detect changes and close out rows. Every fact load must resolve the correct historical surrogate key rather than the current one, which is harder and is where the bugs are. And every analyst query must decide between is_current = true and point-in-time joins — a decision they will get wrong sometimes.

This book's position: Type 2 on the attributes where history has a known business meaning — customer region and segment, product category and price — and Type 1 on the rest. Kestrel's dim_customer is Type 2 on region, segment, and marketing_opt_in, and Type 1 on name spelling corrections.

What that gives up: if someone later asks a question about the history of a Type 1 attribute, the answer is unavailable and unrecoverable. Mitigate by keeping bronze forever (Chapter 3 §3.3) — the raw history is there even when the dimension did not track it, and reconstructing is painful but possible. Without a raw layer, this decision is genuinely irreversible.

6.6 Conformed Dimensions and the Bus Matrix

Here is the mechanism that makes separate dimensional models add up to a coherent warehouse.

A conformed dimension is one shared, identically, across multiple fact tables. dim_product is used by fct_order_item, fct_shipment, fct_return, and fct_inventory_snapshot. Because they share it, you can compare across processes: units ordered against units shipped against units returned, by product category, in one query.

Without conformed dimensions you have data marts that cannot talk to each other, which is the failure Inmon's approach was designed to prevent and which Kimball's approach prevents by discipline instead. The discipline has a tool.

The bus matrix

Business processes down the side, dimensions across the top. Mark where each applies.

● = applies · ○ = applies only for logged-in sessions

This one table does four jobs, and it is the highest-value hour in this chapter:

It shows what to build. Each row is a fact table. Each column is a dimension.

It shows the build order. Dimensions used by the most processes get built first, because they are on the critical path for everything.

It reveals integration points. Two processes sharing a dimension can be compared. Two that share none cannot, and if the business wants to compare them, you have found a missing conformed dimension.

It is a communication tool. A business stakeholder can read it, and reading it prompts the question "why can't we break returns down by promotion?" — which is exactly the conversation you want before building rather than after.

🧱 Kestrel Platform — Increment 6: the model on paper

No code this chapter. Three documents in platform/docs/model/.

1. bus-matrix.md. Build the matrix above yourself, for Kestrel, from Chapter 1's description of the business. Aim for at least six processes and eight dimensions. Then mark, in a second colour or a footnote, the cells you are unsure about — those are questions for the business, and there should be several.

2. grain-declarations.md. One sentence per fact table, in business language. Six to eight of them. Then, for each, list two questions it can answer and one it cannot — the second list is what tells you whether the grain is right.

3. dim-customer.md. Design the customer dimension in full: every attribute, its type (0, 1, or 2), and — the part that matters — one sentence of justification for every Type 2 attribute, naming the business question that requires history. An attribute you cannot justify should be Type 1.

This is the increment people are most tempted to skip because nothing runs at the end of it. Chapter 19 builds these models in dbt and Chapter 20 implements the SCD logic; both are substantially easier if the decisions were made here rather than while writing SQL.

6.7 The Seven Patterns

Most modeling requirements are one of seven shapes. Recognizing them saves inventing a bad answer.

1. Degenerate dimension. A business key with no attributes of its own — an order number, an invoice number, a tracking number. It lives in the fact table as a plain column, with no dimension table, because there is nothing to put in one. Keep it: it is how a person traces a row back to the source system, and that is worth a column.

2. Junk dimension. A collection of unrelated low-cardinality flags — is_gift, is_expedited, is_first_order, payment_method — that would otherwise be four separate tiny dimensions or four columns cluttering the fact table. Combine them into one dimension with a row per observed combination. Four flags with 2, 2, 2, and 5 values give at most 40 rows.

3. Role-playing dimension. One dimension used several times in one fact table in different roles. fct_shipment has an order date, a ship date, and a delivery date — three joins to dim_date. Implement as views (dim_order_date, dim_ship_date) so column names disambiguate, rather than three copies of the table.

4. Bridge table. For genuine many-to-many between a fact and a dimension. An order can have several promotions; a product can have several categories. A bridge table sits between them with an allocation factor.

Bridge tables are where fan-out bugs live (Chapter 2 §2.5), and the allocation factor is why: if two promotions apply to one order line, and you join through a bridge without weighting, revenue doubles. The bridge must carry a weight that sums to 1.0 per fact row, and every query through it must multiply by that weight. Use a bridge only when you must, and test the weight sum.

5. Factless fact table. A fact table with no measures — only keys. It records that something happened, or that a relationship exists. A promotion-eligibility table records which products were eligible for which promotion on which day; the "measure" is the existence of the row.

The characteristic use is counting non-events: which eligible products were never ordered during the promotion. That question is unanswerable from an order fact table alone, because non-events leave no rows.

6. Periodic snapshot. §6.4. One row per entity per period. Inventory levels, balances, subscriber counts.

7. Accumulating snapshot. §6.4. One row per entity, updated as milestones complete. The order pipeline, a support ticket lifecycle, a loan application.

🎓 Interview Angle — "Design a schema for X"

A staple of data engineering interviews, and the failure mode is starting to name tables. Use the four steps out loud, in order:

"Let me start with the business process — I think there are actually three here: placing an order, shipping it, and returning an item. I'd model them as separate fact tables sharing conformed dimensions rather than trying to force them into one.

For order placement, the grain is one row per order line. I'm choosing line rather than order because it lets me attach product, which is most of the questions — and if I need order-level measures like shipping cost, those go in a separate order-header fact at order grain rather than being allocated down.

Dimensions: date, customer, product, promotion, channel. Order number stays in the fact as a degenerate dimension for traceability.

Facts: quantity, extended price, discount, tax, net revenue. Unit price I'd store but mark non-additive.

Two things I'd want to ask: is a customer's region at the time of the order the right basis for regional reporting — which decides whether the customer dimension is Type 2 — and can more than one promotion apply to a line, because that decides whether I need a bridge table."

The two questions at the end are what distinguishes a strong answer. They demonstrate that you know where the model's hard decisions are, and interviewers are usually more interested in that than in the schema.

6.8 Modeling Kestrel

The complete model, as a worked example.

The bus matrix, condensed

Six processes, eight dimensions, from §6.6.

The fact tables

Why both fct_order_item and fct_order. Order-level measures exist that cannot be honestly allocated to lines: shipping charge, order-level discount, and the payment fee. Rather than inventing an allocation rule and having every consumer inherit it silently, Kestrel keeps a second fact table at order grain. Consumers who need order-level measures use it; consumers who need line detail use the other; and joining them is a documented, tested operation rather than an improvised one.

This is a deliberate and slightly unfashionable choice — the alternative is one table with allocated measures, which is simpler for consumers. The reason to split is that an allocation rule is a business decision that will be revisited, and having it in one place, named, beats having it embedded in a column everyone sums.

The dimensions

The definitions that had to be resolved

Modeling forced five decisions that had been ambiguous for years. This is the part of the chapter that is really about organizations rather than schemas.

1. What counts as an order? Decision: an order enters fct_order_item when status first reaches paid. Orders in pending are not counted. Cancelled orders remain, with a is_cancelled flag, rather than being deleted — because "how many orders were cancelled" is a question, and deleted rows cannot answer it.

2. What is net_revenue_cents? extended_price_cents - discount_cents, excluding tax and shipping, on non-cancelled lines. Returns are not subtracted here; they are a separate fact, and net-of-returns revenue is a documented join. This is the definition from Chapter 1's Case Study 1, unchanged since.

3. Which region does a customer belong to? Their region at the time of the order, via the Type 2 dimension. Current region is available through is_current for questions that want it. Both are legitimate; naming which is the default is the point.

4. Do promotions apply per order or per line? Per line, and more than one can apply. Therefore a bridge table with an allocation weight. This decision alone justified a week of discussion and it was the right week.

5. When is a session over? Thirty minutes of inactivity, or midnight UTC, whichever comes first. Arbitrary, defensible, written down, and — the important part — the same rule everywhere, so two teams cannot compute different session counts.

🔐 Privacy & Governance — Modeling decisions that are also privacy decisions

Three things in Kestrel's model were shaped by Chapter 31's requirements, and they were cheap here and would have been expensive later.

dim_customer.email_hash rather than email. The raw address stays in bronze, access-controlled; the dimension carries a salted hash, sufficient for joining and matching and not sufficient for contacting anyone. Analysts who need to email customers use a separate, permissioned path.

The natural key is carried everywhere. customer_id appears on every dim_customer row and the degenerate order_id on every fact row. That traceability is what makes an erasure request a query rather than an investigation — you can find every row about a person.

dim_customer is Type 2, which means erasure has to delete history too. A Type 2 dimension keeps prior versions of a person's attributes, and an erasure request covers all of them. This is a real complication and it is much better to know about it while designing the dimension than while handling a request with a statutory deadline.

The general practice: ask the three privacy questions from Chapter 3 §3.4 during modeling, not after. Which attributes identify a person; what erasure would require; what retention applies. Ten minutes, at the point where the answers are still cheap to act on.

6.9 The Eight Common Mistakes

Diagnosed by the symptom they produce, because that is how you will meet them.

1. Mixed grain in one fact table. Symptom: totals that do not reconcile, and a WHERE clause everyone has to remember. Cause: order-level and line-level rows in the same table, usually because someone added shipping cost as a row rather than a column. Fix: separate tables per grain.

2. Grain chosen after columns. Symptom: a question the warehouse cannot answer that the source obviously can. Cause: the model was built from the available columns rather than from the process. Fix: redo step 2, and rebuild at the atomic grain.

3. Null foreign keys. Symptom: rows silently disappearing from reports that use inner joins. Cause: a nullable dimension key where no dimension member applied. Fix: a "not applicable" member with a reserved key, and NOT NULL on every dimension key.

4. Summing a non-additive measure. Symptom: a number that is wrong by roughly the number of rows. Cause: SUM(unit_price) or AVG(percentage). Fix: mark them, name them clearly, and define the correct aggregation in the semantic layer.

5. The fan trap. Symptom: revenue doubles when a dimension is added to a report. Cause: a one-to-many join fanning out fact rows — the promotions example from Chapter 2 §2.5. Fix: a bridge table with an allocation weight, or aggregate before joining.

6. The chasm trap. Symptom: a Cartesian product; numbers inflate by orders of magnitude. Cause: two fact tables joined through a shared dimension without aggregating first — "orders and shipments by customer" joined on customer_key alone. Fix: aggregate each fact to a common grain before combining, or use a FULL OUTER JOIN on the conformed keys.

7. Snowflaking by default. Symptom: every query has nine joins and analysts write to you instead of writing SQL. Cause: normalizing hierarchies out of habit. Fix: flatten into the dimension.

8. A model that mirrors the source. Symptom: every source refactor breaks the warehouse; the model has orders, order_items, payments with the same names and shapes as the application. Cause: skipping modeling and calling the copy a warehouse. Fix: model the process, not the tables. This is the most common mistake in the list and the hardest to argue against, because the copy works — right up until the refactor in §6.1's callout.

📏 Scale Note — how big each of these actually gets

A dimensional model's cost is dominated by one table, and it is not the one people worry about.

text object rows/year bytes/row size/year ───────────────────────────────────────────────────────────────────── dim_date 365 80 29 KB dim_product (SCD2) ~9,400 240 2.3 MB dim_customer (SCD2) ~380,000 310 118 MB fct_order (order grain) 2,400,000 120 288 MB fct_order_line (line) 6,480,000 90 583 MB fct_inventory_snapshot 51,465,000 90 4.6 GB <-- events (clickstream) 5,110,000,000 66.7 341 GB <--

The two flagged rows are periodic snapshots and event streams, and between them they are 99% of the bytes. Every dimension in the model together is under 121 MB, which is why the instinct to optimise dimensions is misplaced — and why a Type 2 dimension's history is nearly free until somebody puts a daily-changing attribute in it (Exercise 20.24's 9.3 million rows).

The transferable ratios:

text transaction fact : all dimensions ~5 : 1 snapshot fact : transaction fact ~8 : 1 (and it grows with SKUs x locations x DAYS) event stream : everything else ~60 : 1

The snapshot ratio is the one that surprises people, because a snapshot fact's row count is a product of three dimensions and time, and none of the three has to grow much for the product to explode. Doubling the SKU count doubles the snapshot and leaves every other row unchanged.

And at Kestrel's size none of this is a cost problem — the whole model is under 5 GB and costs about a dollar a month (Exercise 6.10). It is a modelling problem: a snapshot grain chosen without arithmetic is the fastest way to make a small business's warehouse look like a large one's.

6.10 Summary

Dimensional modeling has outlived the hardware, the tools, and the constraints it was designed for, because it was never about those. It is about how people ask questions, and that has not changed.

Model for semantic stability, not performance. Star schemas are faster than normalized schemas, and that is the weakest of the three reasons to build one. The strong reasons are comprehensibility — putting the business rules into the model once so nobody has to know them again — and stability: a dimensional model is a stable interface over an unstable source, absorbing upstream refactors so that forty downstream objects do not have to.

Normalization optimizes update integrity; analytics does not need it, because an analytical system does not accept user writes. So dimensional models denormalize deliberately, spending cheap storage to save expensive joins. Prefer the star to the snowflake — the human reading the model is the constraint.

The four steps are a method and the order is the method. Select a business process, not a report and not a department. Declare the grain in one sentence of business language, before choosing anything else — because the grain determines which dimensions are available and which measures are valid. Then identify dimensions (can you imagine grouping by it?) and facts (is it numeric and additive?). Choose the finest grain the source supports: you can always aggregate up, never disaggregate.

Three fact table types: transaction (one row per event, the default), periodic snapshot (one row per entity per period, for levels rather than events), and accumulating snapshot (one row per entity, updated through known milestones — the deliberate exception to append-only).

Additivity is where a modeling error becomes a wrong dashboard. Additive measures sum across everything; semi-additive measures do not sum across time (inventory on Monday plus inventory on Tuesday is not inventory); non-additive measures do not sum at all. Every downstream tool defaults to SUM, so mark them, name them so the correct aggregation is obvious, and store an additive alternative alongside where one exists.

Surrogate keys for dimensions, because Type 2 history makes the natural key non-unique — that is the decisive reason, and it is why surrogate keys and slowly changing dimensions are always discussed together. Carry the natural key alongside for traceability. dim_date is the deliberate exception, with an intelligent YYYYMMDD key.

Type 2 where history has a named business meaning, Type 1 elsewhere. Type 2 costs unbounded dimension growth, harder fact loads that must resolve the historical key, and a decision every analyst can get wrong. Keeping bronze forever is what makes this decision recoverable; without a raw layer it is genuinely irreversible.

Conformed dimensions are what make separate models add up to a warehouse, and the bus matrix is the tool: processes down, dimensions across. It shows what to build, in what order, where the integration points are, and — because a stakeholder can read it — it prompts the "why can't we break returns down by promotion?" conversation before you build rather than after.

Seven patterns cover most requirements: degenerate dimension (a business key with no attributes, kept for traceability), junk dimension (unrelated low-cardinality flags combined), role-playing dimension (one dimension joined several times, aliased by views), bridge table (genuine many-to-many, with an allocation weight that must sum to 1.0 and must be tested), factless fact table (for counting non-events, which no ordinary fact table can answer), periodic snapshot, and accumulating snapshot.

Modeling forces the business to agree with itself, and that is a feature. Kestrel's model resolved five long-latent ambiguities — what counts as an order, what net revenue means, which region a customer belongs to, whether promotions apply per line, and when a session ends — and each of those had been quietly costing something.

Eight mistakes, diagnosed by symptom: mixed grain (totals that will not reconcile), grain chosen after columns (a question the source can answer and the warehouse cannot), null foreign keys (rows silently vanishing from inner joins), summing a non-additive measure, the fan trap (revenue doubles when a dimension is added), the chasm trap (two facts joined through one dimension, inflating by orders of magnitude), snowflaking by default, and a model that mirrors the source — the most common, and the hardest to argue against, because the copy works until the refactor.

What's next

Part I ends here. Part II is storage, and it opens with Chapter 7 on relational databases from a data engineer's perspective — which is a different perspective from an application developer's, because you are usually a reader of someone else's transactional database. That changes what an index costs you versus costs them, why SELECT * at 09:00 on Black Friday is an outage you performed on yourself, what MVCC means for a long-running extract, and why money lives in integer cents.