Star and snowflake schemas

Why is a star a split by rate of change, and when is snowflaking a dimension worth it?

Star schema

A star schema has one central fact table and several dimension tables around it, joined by foreign keys. Picture a star: the fact is the center and each dimension is a point.

-- Fact: one row per order line (the grain)
CREATE TABLE fact_sales (
  date_key     INT,      -- FK to dim_date
  product_key  INT,      -- FK to dim_product
  customer_key INT,      -- FK to dim_customer
  order_number VARCHAR,  -- degenerate dimension (see Measures)
  quantity     INT,
  net_amount   DECIMAL(12,2)
);
-- Dimensions: small, descriptive
-- dim_product(product_key, name, category, brand, ...)
-- dim_customer(customer_key, name, region, segment, ...)
-- dim_date(date_key, date, month, quarter, year, ...)

The derivation nobody states: a rate-of-change split

A star is a rate-of-change split.

Measures change once per event, and are never revised.
Descriptive context (who, what, where) changes rarely, is shared across many events, and is the vocabulary in which people write filters.

Two populations with different mutation rates and different reader access patterns belong in different tables. This is the same instinct as separating a log from an index, hot data from cold data on different storage tiers, and a memtable (the in-memory write buffer of a database) from its on-disk files.

Everything else attributed to the star follows from the split rather than justifying it:

Dimension tables are small, so joins are broadcast instead of shuffled (moved across the network).
Dimension filters prune fact partitions before reading them (dynamic partition pruning).
Query optimizers acquired star-schema detection as a named feature.

That last point has a consequence: part of the star's measured advantage is co-evolution, not intrinsic virtue. The shape and the optimizer were fitted to each other. Be careful to credit the model with only what belongs to the model, and not what belongs to the planner.

The star also forces one decision at load time that will dominate the next five sections. A fact stores a key to one version of each dimension member. So load time is when you commit to as-was or as-is semantics (explained in As-was or as-is).

Snowflake

A snowflake schema normalizes the dimensions themselves: dim_product points to dim_category, which points to dim_department. The branches look like a snowflake.

The space argument for snowflaking is dead for the reasons above, and the argument against it (more joins, slower queries) is weaker than people claim for the same reasons. So decide on the surviving criterion:

A geography hierarchy maintained by one team and referenced by the customer, store and supplier dimensions is a genuinely shared entity. Snowflaking it gives one place to fix a country rename.
A product category that exists only inside the product dimension is not a shared entity. Splitting it buys nothing but one more join and a chance for a consumer to pick the wrong join path.

The real cost of snowflaking is not machine cost. It is path ambiguity. More tables mean more ways to write a join that is syntactically valid and semantically wrong. That is a human error rate, and it is the dominant term.

Case, resolvedConstructed case · Nadia at Fernwood Outfitters

Monday counted twice

The marketing dashboard summed a daily_sales table in which some days carried an extra 'ALL' subtotal row, and the team argued over whether to normalize or denormalize it. Where it started: Grain and table shape →

The subtotal rows. She declares the grain of daily_sales in one sentence: "one row per product per day." The ALL rows violate it, so she deletes them from the table. Totals now come from SUM, never from a stored row. Revenue on the dashboard drops, and part of the 14% disappears.

The argument about normalizing. She asks her colleagues for the reason behind each request. "Save space": 100 GB of order lines is $2.30 per month, and dictionary encoding already shrinks repeated attributes. "Joins are slow": the product and customer dimensions are small and are broadcast. Neither reason holds. The only real question is: who owns the definition of category? She finds that two pipelines both compute it. She makes one pipeline the owner, and the other reads from it. She also keeps the order-line table at the finest grain the shop emits, and builds the daily total as an aggregate that can be traced back to it.