Organizing analytical tables
Both schemas center on a fact table of measurable events surrounded by dimension tables that add context. They differ in how the dimensions are normalized.
Star schema
- A central fact table links directly to denormalized dimension tables.
- The diagram looks like a star with the fact in the middle.
- Queries need fewer joins, so it is fast and simple for analysts.
Snowflake schema
- Dimensions are normalized into sub tables, for example splitting a product dimension into product, category, and supplier tables.
- Saves some storage and reduces redundancy.
- Requires more joins, which can slow queries and complicate BI tools.
Choosing between them
- Prefer star for most analytics because read speed and simplicity usually beat saving storage.
- Use snowflake when dimensions are large, shared, and change in ways that make redundancy costly.
Key idea
A star schema keeps dimensions flat for fast queries, while a snowflake normalizes them to cut redundancy at the cost of joins.