x

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?

more ▼

asked Oct 19, 2009 at 09:23 AM in Default

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Oct 19, 2009 at 10:18 AM

Raj More gravatar image

Raj More
1.7k 80 82 84

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 03, 2009 at 07:42 PM

Alvin Ramard gravatar image

Alvin Ramard
207 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 19, 2009 at 04:50 PM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x108
x31
x19
x17

asked: Oct 19, 2009 at 09:23 AM

Seen: 4614 times

Last Updated: Oct 19, 2009 at 09:23 AM