Notesstorage🌱 seedling

Star Schema

Also: Dimensional Modeling · Dimensional Model · Fact Table · Dimension Table · Snowflake Schema · Kimball Model

A warehouse schema with one huge fact table of events at a fixed grain in the centre, holding foreign keys and numeric measures, surrounded by small, descriptive dimension tables. Normalising the dimensions further gives a snowflake schema.

Why it exists

Analysts slice measures (revenue, quantity, duration) by attributes (product category, store region, quarter). A layout where every attribute is one join from the facts makes those queries simple to write and fast to run.

How it works

  • Grain first: decide exactly what one fact row represents, e.g. one item on one receipt.
  • Fact table: billions of rows of foreign keys plus measures. Measures accumulate, so wide tables with hundreds of columns are common.
  • Dimension tables: who, what, where, when. Small, wide and descriptive (product name, brand, category).
  • Queries filter on dimension attributes, join to the facts, and aggregate the measures.
  • Snowflake schema: dimensions normalised into sub-dimensions (product → category), trading duplication for extra joins.

Trade-offs

Star Snowflake
Joins per query one per dimension more, one per normalised level
Duplication attribute values repeat in dimensions less
Ease for analysts high lower

In practice

  • Dimensional modelling's main advocate, Ralph Kimball, advised avoiding snowflakes: a flattened dimension holds the same information, and dimensions are tiny next to the facts.
  • With Column-Oriented Storage, very wide denormalised tables ("one big table") are also common, since unused columns cost nothing to scan.

Pitfalls

  • A mixed or undeclared grain: some rows per order, some per item, so sums double-count.
  • Normalising dimensions out of OLTP habit (Normalization) and paying for extra joins on every query.

Recall

These cards are also in your review deck, scheduled with spaced repetition.