Comparison
Snowflake schemavsStar schema
Snowflake schema
getting the product's category name means joining the fact to product, then product to subcategory, then subcategory to category.
A star whose dimensions have been normalised into their own sub-tables, so hierarchies are separate rows rather than repeated columns. It saves storage and makes a hierarchy edit a single update, at the price of more joins in every query and more ways for an analyst to get one wrong. On modern columnar storage the space saving is close to irrelevant, which is why the star usually wins and the snowflake survives mainly where a hierarchy is genuinely volatile.
Full entry →Star schema
every dimension is one join away from the fact, so no query needs more than a single hop to get a label.
A central fact table surrounded by fully denormalised dimensions, each reachable in one join. It is the default shape for analytical modelling because it is predictable to write, cheap to plan and easy for a BI tool to reason about. The redundancy inside the dimensions is the deliberate trade — you accept repeated country names in exchange for never chaining joins in a report.
Full entry →