The untidy shape that answers faster

Why does the arrangement that looks less orderly return answers more quickly?

Anyone trained on database design learns that repetition is a defect. If every product row repeats its brand name, and every brand row repeats its category name, you normalise: pull the repeating thing into its own table and point at it. Each fact recorded once, cleanly.

Then you look at a working data warehouse and find a product dimension with brand, category, department and supplier all sitting redundantly in the same wide table, repeated across millions of rows — and it answers questions faster than the tidy version. That is not a small anomaly to explain away. It suggests the tidiness rule was never a rule about tables; it was a rule about something else, which no longer applies here.

So ask the sharper question: what problem does normalisation actually solve?

Update anomalies. If a brand's name appears in a million rows and the brand is renamed, you must change a million rows, and if you change only some, the database now holds two contradictory answers. Normalising to one row means one update, and inconsistency becomes structurally impossible.

Now hold that against a warehouse. How are its rows changed? Mostly they are not. Facts are loaded and thereafter read; dimension attributes change slowly and are handled deliberately, often by adding a new version of the row rather than overwriting. The anomaly normalisation exists to prevent is close to absent — and yet you are still paying its price, in joins, on every single query.

That is the whole reversal in one line: normalisation is a write-time protection charged at read time. In a system that reads a thousand times per write, the accounting comes out the other way round.

Then what does the price consist of? Suppose you ask for revenue by product category. In the snowflaked shape, the query walks fact to product to brand to category — a chain, and each link must be resolved before the next can be. In the star, the category is a column on the product dimension: fact to product, one hop, and category is simply there.

Two costs are hidden in that chain. The obvious one is the work of the joins themselves. The subtler one is the optimiser's job: the number of possible orderings of a join grows explosively with the number of tables, so a planner given five tables has vastly more ways to be wrong than one given two. Star schemas are not merely faster to execute; they are far more predictably fast, because there is less for the planner to get wrong.

There is a third reason, less discussed and quite practical. Business intelligence tools generate SQL from a model. Given a star, the generated query is nearly always fact-plus-dimensions and comes out the same shape every time. Given a snowflake, the generated SQL varies with which attribute the user dragged in, and performance varies with it.

Now the honest correction, because the classic argument is often overstated. The storage saving from snowflaking is usually negligible on a modern columnar engine: repeated values compress extremely well under dictionary and run-length encoding, so a "wasteful" repeated category string may cost almost nothing on disk. And the join penalty has narrowed too — engines with broadcast joins and runtime filtering handle small dimension tables well. So the modern case for the star is less about raw speed than it was in the 1990s, and more about comprehensibility and predictability. Anyone quoting a large fixed speed multiple is quoting an old benchmark.

Where does snowflaking still earn its place? When a dimension is genuinely enormous, when a hierarchy is shared across several fact tables and must not drift between them, or when the hierarchy itself changes often enough that maintaining it in one place is worth a join. Those are real cases; they are just not the default one.

Three refinements.

First, the misconception that a star schema is normalisation done badly. It is normalisation deliberately not applied, for a system whose read-to-write ratio inverts the reason for it. The transactional system upstream should still be fully normalised — it is where the updates happen, and that is exactly where anomalies would bite.

Second, the star's real product is not speed but a stable interface. Any question about the business becomes the same shape of query: pick the fact table, join the dimensions you filter or group by, aggregate. A user who learns that shape once can ask anything. A snowflake requires knowing the topology of the hierarchy before you can write the query at all.

Third, this is not an all-or-nothing choice. Many warehouses snowflake one troublesome hierarchy and keep everything else flat, which is fine — the shapes describe individual dimensions, not a doctrine the whole model must obey.

• Slowly changing dimensions: how the star handles attribute change without normalising.
• Why columnar compression makes redundant dimension attributes nearly free to store.
• One big table designs, which push denormalisation further still, and where they stop working.

Every term the collection defines is gathered in the glossary.