Seeder question: What are the differences between snowflake- and star- schemas when designing databases for datawarehousing / OLAP. What performance issues should be considered when implementing them in SQL Server?
Answer by Raj More ·
A star schema has one FACT table at the center and Dimension tables surrounding it - one completely denormalized table per relationship.
A snowflake has some level of normalization.
So you can have a
Fact-Product-ProductCategory in a snowflake, whereas you would have a
Fact-Product in a star schema.
In most cases, when you have multiple FACT tables you may need different levels of normalizations which is when the snowflake design becomes very useful.
Answer by David 1 ·
Star schema is a pattern that arises whenever a table has multiple foreign keys. It is a significant concept in database design and optimisation because of the common requirement to filter the (usually much larger) referencing table based on values in the tables being referenced.
"Snowflake" is sometimes used to describe a schema which consists of more than a simple star pattern.
Answer by Alvin Ramard ·
In simple terms, the Star schema has one, or more, fact table(s) connected to a number of dimension tables. Each "dimension" column in the fact table(s) connect to only one dimension table. The dimension tables only connect to the fact tables.
A Snowflake schema can be thought of as a Star schema in which the dimensions tables have been normalized to a higher degree to split the columns into multiple tables, hopefully, producing a reduction in repeated data. Each "dimension" column in the fact table(s) still connect to only one dimension table, but that dimension table now connects to more tables.