The star schema

A bookshop wants sales by product and region. One product line on one receipt is the event it records; product and shop tables give that event names to group by. Before choosing between a flat shop dimension and separate region table, decide what each sales row means. Otherwise a report can double-count correct-looking rows. This bookshop is an illustrative example.

The figure sketches the four joins to dimensions. It omits the receipt ID and line number that distinguish two fact rows with the same dimension keys; the SQL example below includes them.

The grain decides everything else#

The grain is the sentence that says what one fact row represents. Here it is one product line on one receipt. A receipt subtotal or a daily product total has a different grain and belongs in another fact table or a separate aggregation. Kimball’s grain rule requires choosing this level before dimensions and measures.

Each dimension key must describe the event at the chosen grain. Measures also need an aggregation rule. Revenue from separate product lines can be summed by shop, day or product without counting one line twice. Unit price and percentage are different: summing their values does not give total revenue or an overall rate. A snapshot of an account balance can be summed across accounts for one date, but summing the same account’s balances across dates counts holdings repeatedly. That makes the balance semi-additive. Kimball’s measure rules spell out this time boundary.

In the example above, £40 and £60 are the two lines; £100 is the receipt subtotal. Summing all three rows gives £200 for a £100 sale. The values are individually correct, but they cannot share a fact table used for an unfiltered line-level sum.

-- Grain: one product line on one receipt, identified by receipt and line.
CREATE TABLE fact_sales (
  receipt_id   BIGINT NOT NULL,
  line_number  INT NOT NULL,
  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,
  PRIMARY KEY (receipt_id, line_number)
);

The receipt ID and line number make this example’s grain explicit and unique. A second line could have the same product, customer, shop and date keys, so those dimension keys alone do not identify a line. The dimension tables referenced here are omitted for brevity.

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.

A query that filters by region and groups by shop format can join the fact table to dim_shop once when both attributes live there. If region lives in a separate table, a SQL query using it may need another join. That is a query-shape difference, not a measured speedup: indexes, table sizes, caching and the engine’s optimiser still decide execution cost.

A flat dimension may store a region name on many shop rows. Correcting a current name can then require several updates; a separate region table may need one. Historical dimensions may deliberately preserve old labels, so a rename policy is a modelling decision rather than an automatic bulk update.

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 always best. A flat dimension may make authoring reports easier; a snowflake may reduce repeated values. Microsoft’s Power BI guidance treats the choice as dependent on data volume and model usability. Its product-specific performance observations should not be turned into a guarantee for every relational engine.

Normalising the dimension away#

Consider a proposed change: move category from each product row into a separate category table and leave a key on the product. The following questions must be answered before claiming a benefit or regression.

The choice is between costs that can be inspected. Splitting category can reduce duplicated names and simplify some updates. Keeping it on product can make category reports easier to express. A migration should compare both the data-loading rule and the reports that use the attribute. The OLTP/OLAP comparison separates this schema decision from the separate row-versus-column storage question.

If an attribute changes independently and multiple models reuse it, a separate table may be useful. If history matters, simply overwriting a label in either shape may answer yesterday’s question incorrectly. Test that rule with sample changes before deciding from an assumed update frequency.

A star schema does not require every query to scan most of the fact table. Filters, indexes and storage layout can change the plan; the index-plan guide shows why the engine might choose a scan or an index. The persistence path connects this schema to the surrounding storage questions.

The schema also needs a loading rule. The warehouse reload experiment shows why replacing one date’s rows must undo the deletion when the new insert fails.