Kimball dimensional modelling: the star schema, explained by what it costs

Also called: star schema, dimensional modelling, fact and dimension tables, the Kimball method

The most widely used way to model a warehouse, and the one most often got wrong in the first week — because two of its decisions are made early and cannot be cheaply undone.

What it is

A Kimball model is built out of two kinds of table. A fact table holds the things you measure: an order line, a payment, a meter reading, a support ticket. A dimension table holds the things you slice by: customer, product, date, store, employee. Draw one fact with its dimensions around it and you get the shape everybody calls a star schema.

What makes it the default is not elegance. It is that every business intelligence tool in existence — Power BI, Tableau, Looker, Excel’s own pivot tables — expects this shape and is slower, or simply worse, on anything else. Choosing Kimball means the tooling is on your side.

Ralph Kimball’s own framing is worth keeping: the warehouse is modelled around business processes, not around departments and not around source systems. One star per process. Sales is a process. Returns is another. The marketing department is not a process, and a star built for a department is a star that has to be rebuilt when the org chart moves.

How the tables are actually shaped

The grain: the decision everything else rests on

The grain of a fact table is the answer to what does one row mean? — and it has to be a sentence, not a shrug. “One row is one line of one order” is a grain. “Sales data” is not.

It is decided first because everything downstream assumes it. Which dimensions can attach, which measures can be added up, whether a count means orders or items — all of it follows from the grain. Declare it in writing before a single column is created, and put it in a comment at the top of the table.

The fact table

Narrow and long. Foreign keys to its dimensions, the numbers being measured, and as little else as possible:

FactSales
  SalesKey        BIGINT IDENTITY   -- surrogate, not the source's key
  DateKey         INT      FK -> DimDate
  CustomerKey     INT      FK -> DimCustomer
  ProductKey      INT      FK -> DimProduct
  OrderNumber     NVARCHAR  -- a degenerate dimension: no table of its own
  Quantity        INT       -- additive
  LineTotal       DECIMAL   -- additive
  UnitPrice       DECIMAL   -- NOT additive: summing these means nothing

• Additive measures can be summed across every dimension. Quantity, revenue, cost.
• Semi-additive measures sum across everything except time. An account balance: adding January’s to February’s is nonsense, but adding two accounts’ balances is fine.
• Non-additive measures cannot be summed at all. A unit price, a percentage, a ratio. Average them, or store the numerator and denominator separately and divide at the end.

The dimension table, and how much history it keeps

Wide and short, and deliberately denormalised: a product dimension carries its subcategory and category as columns rather than as joins to two more tables. Normalising them back out is called snowflaking, and it makes every report pay for the tidiness.

The second unavoidable decision is what happens when an attribute changes — a customer moves house. That is the slowly changing dimension question, and in practice it has two answers worth having:

• Type 1 overwrites. The old value is gone. Right when only the current value ever matters, and when keeping the old one would be noise.
• Type 2 adds a new row and closes the old one, usually with ValidFrom, ValidTo and IsCurrent columns. History survives, so a report about last year keeps saying what it said last year. This is the one people mean when they say “SCD”.
• Types 3 and 6 exist — a previous-value column, and a hybrid of 1 and 2 — and are far rarer than the literature suggests. Reach for them only when you can say out loud which question they answer.

Surrogate keys, and the row that catches the unknown

Every dimension gets a meaningless integer key of its own rather than reusing the source system’s. Two reasons that matter: type 2 history needs several rows for one customer, so the source’s key is no longer unique; and when a second source system arrives with its own customer IDs, the warehouse already has a key that belongs to neither.

Give every dimension an “unknown” row at key -1. A fact whose customer cannot be resolved then points at that instead of being dropped, and the row is still counted in the total — which is how you find out the lookup is broken, rather than quietly reporting a smaller number.

When it fits, and when it does not

Kimball is a good default and a bad universal. It is the right answer surprisingly often and the wrong one in ways that only show up months in.

Those two lists are Netune’s own judgement of this methodology, copied from the rule file it scores a real database with.

What it costs

The mistakes that are expensive

Getting the grain wrong, then building on it

A fact table built at order level when the questions are about products cannot answer them, and no amount of clever SQL recovers the detail that was never stored. Going the other way — a grain finer than anything asked for — costs storage and is otherwise harmless, so when in doubt, go finer.

The tell is a fact table nobody can describe in one sentence. If two people give two different answers to “what is one row?”, stop building.

Letting the star snowflake

Source systems are normalised, so a straight copy gives you Product → Subcategory → Category as three tables, and reports start joining dimension to dimension. The whole point of the star is that they never have to. Flatten the hierarchy into the child dimension — ProductCategoryName as a column on DimProduct — and accept the repetition.

The exception is a parent that a fact or a bridge points at directly. That one is used at its own grain and keeps its table, even while its attributes are also folded into the children.

Keeping type 2 history of somebody else’s housekeeping

This one is quiet and expensive. If a type 2 dimension watches every column for changes, and the source carries a ModifiedDate or a rowversion, then every batch that touches the row opens a new version of it. After a year the dimension holds a detailed history of the source system’s maintenance schedule and almost nothing about the business.

So the change test has to be restricted to the attributes that actually describe the business. Netune marks those columns tracked and writes the others into the load anyway — they are stored, just not watched — and names the ones it ignored in a comment, so the decision is visible rather than mysterious.

How it compares

Kimball or Galaxy?

There is no real choice here; Galaxy is what Kimball becomes when you model a second process. One fact table is a star, several sharing the same dimensions is a constellation. The work it adds is organisational rather than technical: every process has to agree on what a customer is.

Kimball or Data Vault?

Count your source systems, and ask whether you must be able to prove what arrived. One system and reporting questions: Kimball, and you will be finished far sooner. Several systems that disagree, schemas that keep changing, or an audit requirement: Data Vault 2.0 — and then a star on top of it anyway, because nobody reports off a raw vault.

Kimball or One Big Table?

One Big Table is a star with the joins already done, which makes it faster to query and impossible to correct in one place. For a single process on a columnar engine it is a reasonable trade. For several processes sharing customers and products it is a trap: the same customer’s details end up in three wide tables that disagree.

Questions people ask