question

Kev Riley avatar image
Kev Riley asked

What are the differences between snowflake and star schemas?

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?

database-designdata-warehouseolapbusiness-intelligence
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Raj More avatar image
Raj More answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Alvin Ramard avatar image
Alvin Ramard answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.