Star Schemas: Designing a Data Warehouse Around How Businesses Actually Ask Questions
An explanation of dimensional modelling — fact and dimension tables, grain, and the three flavours of slowly changing dimensions — and why it still underpins most analytical databases.
Why normalise for transactions but denormalise for analysis
A transactional (OLTP) database is normalised for good reason: when a customer changes their address, you want to update exactly one row in exactly one table, not chase the same fact across dozens of duplicated copies. But that same normalisation becomes a liability the moment you try to run analytics, because answering 'total revenue by customer region, product category, and month, for the last two years' against a fully normalised schema means joining across eight or ten tables, and doing that for every dashboard query is both slow and cognitively hostile to whoever has to write the SQL.
The star schema, popularised by Ralph Kimball, resolves this by deliberately denormalising for read performance and query simplicity. At the centre sits a fact table — one row per business event, such as a single line item on an order — surrounded by dimension tables that describe the who, what, where and when of that event: a customer dimension, a product dimension, a date dimension, a store dimension. Drawn out, the fact table in the middle with dimensions radiating outward looks exactly like a star, which is where the name comes from, and a query against it usually needs only a handful of straightforward joins from the fact table out to whichever dimensions the question touches.
Grain: the single most important design decision
Before naming a single column, a dimensional model requires picking the grain of the fact table — the precise, unambiguous statement of what one row represents. 'One row per order' and 'one row per order line item' sound similar but produce very different tables: at order grain, a $150 order with three items is one row with a total; at line-item grain, it's three rows, one per product, each with its own quantity and price. Choosing too coarse a grain (order-level) makes it impossible to later ask 'which products sell best,' because that information was thrown away at load time; choosing appropriately fine grain (line-item) preserves the ability to re-aggregate up to any level later, at the cost of a bigger fact table.
The rule of thumb in dimensional modelling is to build fact tables at the lowest grain the business can conceivably need, because you can always aggregate up from fine-grained data but you can never disaggregate coarse data back down. A retail chain that stores only daily-store-level sales totals will never be able to answer 'what was our best-selling hour on Black Fridays,' no matter how sophisticated the analytics team becomes later, because that resolution was discarded at ingest.
Fact tables: additive measures and foreign keys
A well-designed fact table is mostly foreign keys pointing out to dimension tables, plus a small number of numeric measures — the things you actually want to sum, average, or count: quantity sold, revenue, discount amount, cost. The best measures are fully additive, meaning you can sum them across any dimension without producing nonsense — revenue summed across products, customers, and time all give sensible answers. Some measures are only semi-additive: an account balance can be summed across accounts at a point in time, but summing it across time makes no sense (you don't add Monday's balance to Tuesday's balance to get a weekly total) — these require special handling, typically taking a snapshot or an average rather than a naive sum.
Fact tables in mature warehouses are often enormous — billions of rows for a large retailer's line-item history — which is precisely why they're kept narrow: no descriptive text, no redundant attributes, just keys and numbers. All the descriptive, slowly changing context — a customer's name, a product's category, a store's region — lives in the dimension tables instead, kept comparatively small and rich with attributes that analysts filter and group by.
Slowly changing dimensions: keeping history honest
Dimension attributes change over time — a customer moves city, a product gets reclassified into a new category — and how you handle that change materially affects historical reporting, which is the problem slowly changing dimensions (SCDs) solve. Type 1 simply overwrites the old value with the new one, which is appropriate when history genuinely doesn't matter (correcting a typo in a customer's name), but it means any historical report re-run after the change will show the new value even for past periods, silently rewriting history.
Type 2 is the more common and more powerful pattern for anything you actually care about tracking historically: instead of overwriting, you insert a new row for the customer with the new attribute value, close out the old row with an end date, and mark which row is current. A sales report from three years ago will then correctly show the customer's region as it was at the time of the sale, because the fact table's foreign key points to the specific dimension row version that was active then, not to whatever the customer's current region happens to be today. Type 3 is a middle ground used more rarely: it adds a 'previous value' column alongside the 'current value' column in the same row, which works only when you need to compare exactly one prior state against the current one, rather than an arbitrarily long history — useful for something like tracking a single prior sales territory assignment, but it doesn't scale to attributes that change repeatedly.
Frequently Asked Questions
What does 'grain' mean in a fact table and why does it matter so much?
Grain is the precise definition of what a single row in the fact table represents, such as one order line item; getting it too coarse means you permanently lose the ability to analyse at a finer level later, since you cannot disaggregate summarised data.
What's the difference between a fact table and a dimension table?
Fact tables hold numeric, mostly additive measures about business events plus foreign keys, and tend to be very long and narrow; dimension tables hold the descriptive attributes (who, what, where, when) that give those events context, and tend to be shorter but wider.
Why is Type 2 slowly changing dimension the most common choice?
Type 2 preserves full history by inserting a new row for every change rather than overwriting the old value, so historical reports correctly reflect the dimension's state at the time of each fact, which Type 1's overwrite approach cannot do.
Can a star schema handle a dimension that relates to a fact many-to-many, like a product having several categories at once?
That case typically needs a bridge table between the fact and the dimension to represent the many-to-many relationship correctly, since a plain star schema's single foreign key per dimension assumes each fact links to exactly one dimension row.