The star schema

You have seen the picture — one table in the middle, a handful of tables around it, a line from each to the middle one — and it looks obvious enough that the actual decisions inside it stay invisible. What the picture does not explain is why anyone would deliberately write a shop’s name, and its region, and its country, into every row of a table describing that shop’s sales, instead of keeping each of those facts in one place the way a textbook on normal forms would tell you to. That repetition is not sloppiness. It is what buys the middle table its shape, and once you see the trade it is making, the rest of the picture stops needing an explanation.

The grain decides everything else#

Before any table gets named, one sentence has to be settled: what does a single row in the fact table mean? One line on one receipt. One receipt. One day, per product, per shop. Each of those is a different grain, and a fact table only has one grain at a time — everything else about the model is downstream of picking it.

The grain decides which dimensions are even allowed to attach: a dimension has to be true at the level the fact row describes, or the join says something false. It also decides which measures can be summed. A quantity sold adds up cleanly across every row you group — by shop, by day, by product, it does not matter, the total is still a total. A price or a percentage does not survive the same treatment; summing a column of prices and calling it a total price answers a question nobody asked. Some measures sit between the two: a running balance, for instance, sums sensibly across shops or products on a given day, but not across days, because the last day’s balance is the one that is still true — that is what makes a measure semi-additive rather than fully additive, and the grain is what tells you which one you are holding.

Mixing two grains in one fact table is how this goes wrong quietly. Load a table with both per-line rows and per-receipt subtotal rows, and a query that sums the amount column double-counts every receipt it touches — each number in the table is correct on its own, and the total built from all of them is not.

-- Grain: one product line on one receipt — one date, one shop, one
-- customer, one product, whatever quantity was bought together.
CREATE TABLE fact_sales (
  date_key     INT NOT NULL REFERENCES dim_date(date_key),
  product_key  INT NOT NULL REFERENCES dim_product(product_key),
  shop_key     INT NOT NULL REFERENCES dim_shop(shop_key),
  customer_key INT NOT NULL REFERENCES dim_customer(customer_key),
  quantity     INT NOT NULL,
  revenue      NUMERIC NOT NULL
);

The grain is written as a comment because nothing in the column list states it. Four foreign keys and two measures would describe an entirely different table if the first line said “one row per day per shop” instead.

Why the dimensions stay denormalised#

Look at the shop dimension in the figure above: name, region, city, country, format, size class, manager, the year it opened — eight columns, all sitting in one table, all describing the same shop. A region name gets written once per shop, and every shop in that region writes the same region name again. Textbook normalisation would pull region into its own table and leave a reference behind. A star schema deliberately does not, and the reason is what that repetition buys.

It buys one join instead of a chain of them. A query that filters by region and groups by shop format joins the fact table to dim_shop once and reads both columns off the row it lands on, because both already live there. And it buys a query that reads the way the question was asked — filter on the dimension, group by the dimension, no intermediate table standing between the question and the column that answers it.

What it costs is the plain cost of any denormalisation: the same value stored many times, once per row that shares it, and a rename that has to touch every one of those rows rather than one. Rename a region and a star schema updates it everywhere it was written; a normalised region table would need one row changed.

That second shape — the one that keeps region in its own table and references it — is a snowflake schema: a star whose dimensions have themselves been normalised into further tables. The name describes the picture: what was one box per dimension grows branches, the way a snowflake’s arms do. Nothing about the fact table changes; only how far a dimension’s own attributes have been split out changes.

Neither shape is a mistake. A star schema spends storage and update cost to keep every query short; a snowflake schema spends joins to avoid ever writing the same value twice. Which one is worth it depends on how a dimension actually behaves — and ignoring that question is exactly how the next section goes wrong.

Normalising the dimension away#

A well-meaning engineer looks at the product dimension and notices that category is repeated on every product row that shares one. It looks like exactly the kind of duplication a normal-forms course spent a semester telling them to remove, so they remove it: category becomes its own table, product keeps a foreign key to it, and the change ships as an obvious improvement.

The uncomfortable part is that the engineer was not wrong about the duplication — category really is repeated, many times over. What they were wrong about is which cost that duplication is worth paying. The normal form that is right for the system taking orders, where an update has to touch one row and stay consistent, is not the normal form that is right for the system answering questions about millions of them, where a query that used to join straight from the fact table to the dimension now reads through an extra table it never had to before.

There is a case on the other side: normalising a dimension is sometimes the right call. What makes it right is not the discomfort of seeing a word twice — it is a dimension whose values genuinely change independently and often enough that the update cost is real, not hypothetical. A product’s category changing every few years is not that. A shop’s manager changing every few months, tracked in its own small table other systems also reference, might be.

A star schema answers questions by scanning most of a table rather than seeking a handful of rows in it, which is the normal case for a report and the exception the article on why your index is not being used is written about. And a warehouse is one stop on a longer road: the persistence path holds the rest of it, in the order that makes each piece explain the next.