Star Schemas and the Cost of Normalizing a Dimension

A product dimension in third normal form occupies five tables: product, subcategory, category, brand, supplier. A query that groups revenue by category joins all five before it can group. The star schema version puts category name, brand name and supplier name in the product dimension itself, so the same query joins one table. That is the whole trade, and the reason it is a trade rather than a clear win is that the five-table version has exactly one place to record that two hundred products moved from one category to another, while the wide version has two hundred.

Two schemas for the same facts

A star schema has one fact table surrounded by dimension tables that reference it through single-column foreign keys. Every dimension is one table, regardless of how many hierarchical levels it describes. A snowflake schema normalizes those levels into separate tables, and the fact table’s keys point at the lowest level.

The distinction is not about how many tables exist in total. It is about what a fact row touches. In a star, a query that filters on category and groups by brand reads two dimension columns from one table and never joins a second dimension. In a snowflake, the same query traverses product → subcategory → category, three joins deep, before it can evaluate the predicate. For a fact table of a hundred million rows, each extra join is another hash build or another random lookup, and the join order chosen by the optimizer determines whether the dimension filter is applied before or after the fact scan.

The normalized form has real virtues that are usually dismissed too quickly. It stores each category name once, so a rename is one row update rather than a scan of the product dimension. It enforces that a product cannot sit in two categories, because the foreign key allows one. It makes the hierarchy depth variable — a category can have five levels in one branch and two in another, which a fixed set of columns in a wide dimension cannot represent without nulls.

The star’s virtues are equally concrete. Dimension tables are small relative to facts, usually by two or more orders of magnitude, so their redundancy is cheap in bytes. The join graph is shallow and predictable, which makes the optimizer’s job easy and makes query plans stable across statistics changes. And the dimension attributes sit next to the key that the fact table already carries, so a filter on a dimension attribute becomes a scan of a small table producing a key list, which then drives an efficient fact scan or a semi-join.

Why the cost model shifted toward stars

The textbook argument for normalization was written for row-store engines where a row read costs a page fetch and joins are nested-loop index lookups. Columnar storage changed the arithmetic in two ways.

First, a column that is never selected is never read. In a row store, widening the product dimension to include category, brand and supplier names makes every row of the dimension wider, so every scan of that table touches more pages. In a column store, the unused columns are separate physical regions and contribute nothing to the scan. Redundancy in a column store costs storage and compression opportunity, not read amplification.

Second, dictionary encoding absorbs much of the repetition. A category name repeated across two hundred thousand product rows compresses to a dictionary of a few dozen distinct values plus a run-length or bit-packed index. The wide column’s cardinality is what matters for size, not the row count. A dimension with a low-cardinality attribute stored wide is nearly free on disk and, once the dictionary is decoded, cheap to filter.

What does not become free is the join. A columnar engine can execute a star join by building a hash table on the filtered dimension keys and probing it with the fact table’s key column, which is a sequential pass over one fact column. That is close to the cost of a single-table scan plus a hash probe per row. Three snowflake levels mean three such passes or one larger join tree, and the fact table’s key column gets probed more than once.

The counterweight is dimension size. When a dimension grows past the point where it no longer fits comfortably in memory — a customer dimension with tens of millions of rows and forty attributes — the wide form’s advantage narrows, because the hash build itself dominates. At that point the choice is usually a mini-dimension or a junk dimension rather than a snowflake, since both solve the width problem without reintroducing join depth.

Keeping a wide dimension correct

The update argument against stars is often overstated but not empty. A category rename in a normalized schema is one UPDATE on one row. In a star, the same rename touches every product row in the category, and if the dimension is slowly changing the rename may need new versions rather than in-place updates.

Two properties make this manageable. The first is that dimension maintenance is a batch operation on a small table, not a transactional workload on a large one; rewriting a million-row dimension is minutes of work, while rewriting a hundred-million-row fact table is not. The second is that the rename is a single statement over a filtered set, which in a column store rewrites only the columns being changed:

-- one statement, one column set, over a small table
UPDATE dim_product
SET category_name = 'Home & Garden'
WHERE category_name = 'Household';

The real risk is not the update cost but the drift. A wide dimension holds copies of attributes whose authoritative source is elsewhere, and nothing enforces that the copy matches. A normalized schema cannot drift from itself. This is why the dimension’s load process has to be the only writer, and why a comparison job that reconciles distinct attribute values against the source system earns its place. The same reasoning applies to the fact table’s own keys: if a dimension row is added by hand, the fact rows that should have referenced it do not exist.

A second risk is the hierarchy that changes shape. A product that was in “Kitchen” and moves to “Home” gets a new row in a type 2 dimension, and the old fact rows keep pointing at the old row — which is correct if the question is “what category was it in when it sold” and wrong if the question is “what category is it in now”. A wide dimension makes the first question easy and the second question a lookup against the current version, which is a different join. A snowflake with a separate category table answers the second question without a version lookup and cannot answer the first at all.

Mixed approaches and where they break

Practice rarely picks one form for the entire warehouse. The common arrangement keeps the dimension wide for attributes that are queried with the fact table and leaves genuinely separate entities in their own tables, joined only when a report needs them. A supplier is often its own dimension even in a star schema, because supplier attributes are many and are not used to filter sales facts; the fact table then carries two foreign keys, one to product and one to supplier, and the schema is still a star by join depth.

The arrangement that causes trouble is a partial normalization where the hierarchy tables are separate but the fact table references an intermediate level. Filtering by the top-level category then requires a join that the optimizer may place after the fact scan, and the resulting plan reads every fact row instead of the small fraction that the category selects. The symptom is a query that is correct and an order of magnitude slower than its sibling that filters on a column stored on the dimension.

The way to decide between the two forms is to count joins on the hot path rather than tables in the schema. A dimension with three attributes used in filters and one hierarchy used in one annual report belongs in one wide table; the annual report can afford the join. A dimension with forty attributes of which five are used constantly and thirty-five rarely is a candidate for splitting by usage rather than by normal form, and that split is a modelling decision about access patterns, not about redundancy.

Neither form is more correct in the abstract. Codd’s argument for normalization was about update anomalies in an operational system where every write must be correct at commit. A warehouse dimension is written by one controlled process and read by many analytic queries, and the anomaly that matters there is a query that reads the wrong version of an attribute — which the wide table makes easier to answer correctly, provided the version semantics are decided explicitly.