Star Schema vs Snowflake Schema
People think a snowflake schema is better because it stores less. It splits dimensions into more tables, so each name sits in one place. A star schema keeps each dimension in one wide table. That means fewer joins and simpler queries, which is why most teams pick the star.
Star Schema
One fact table in the middle. Each dimension table joins straight to it.
A star schema puts one fact table in the middle and dimension tables around it. Each dimension joins straight to the fact table with one key. Dimensions are not split further, so each one is a single wide table. Queries need only one join per dimension. Drawn out, it looks like a star.
Snowflake Schema
Like a star, but the side tables are split up. Each part is a small table.
A snowflake schema is a star schema whose dimensions are split into more tables. A product table might point to a separate table of product groups. That table might point to a table of shop floors. This stops the same names being stored many times. The cost is more joins in every query. Drawn out, the branches look like a snowflake.
In one line
- Star Schema
- One fact table in the middle. Each dimension table joins straight to it.
- Snowflake Schema
- Like a star, but the side tables are split up. Each part is a small table.