DimensionalSample
Snowflake schema
A star whose dimensions are normalised into chains of smaller tables. Less repetition, more joins.
Try this
- 01Ask “Revenue by department” and note the joins needed: three hops to reach department_name.
- 02Switch to “Collapsed to star” and ask again: the same question now needs one join, and the derived columns appear in italics on dim_product.
- 03Click dim_category: it no longer touches the fact table, only its neighbours in the chain.
Notation
- Fact table
- Same as in a star: measurements at a declared grain.
- Normalised dimension
- A hierarchy split into one table per level (product → category → department) so each label is stored once.
- Outrigger
- A dimension table reached through another dimension rather than directly from the fact.
- Collapse
- Flattening a chain back into one wide dimension turns the snowflake into a star.
Use it when
- Large, deep hierarchies that change often and would repeat heavily if flattened
- Hierarchies shared by several dimensions
- Storage or update cost matters more than query simplicity
Avoid it when
- Analysts write their own SQL (more joins, more mistakes)
- The BI tool expects a star
- Columnar storage already compresses repeated labels well
More in Dimensional
Star schema
One fact table of measurements in the middle, surrounded by denormalised dimension tables that describe them. Every question is one join away.
Galaxy schema
Several fact tables sharing conformed dimensions (also called a fact constellation). Different processes can be compared on the same date, product and store.