Comparison
NormalisationvsSnowflake schema
Normalisation
you store the customer's address in exactly one table so that changing it cannot leave four stale copies behind.
Structuring tables so each fact is stored once, with relationships expressed by keys rather than repetition. It makes writes correct by construction: there is no second copy to forget. The cost is joins on the read path, which is why every normalised schema eventually meets a report that needs six of them.
Full entry →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 →