Basics - Difference between Snow flake and Star Schema. What are situations where Snow flake Schema is better

GeekInterview.com > Interview Questions > Data Warehousing > Basics

Go To First | Previous Question | Next Question

Basics | Question 41 of 112

Difference between Snow flake and Star Schema. What are situations where Snow flake Schema is better than Star Schema to use and when the opposite is true?

Total Answers and Comments: 6

Last Update: March 29, 2007

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

August 11, 2005 23:05:32

veepee

RE: Difference between Snow flake and Star Schema. What are situations where Snow flake Schema is better...

star schema and snowflake both serve the purpose of dimensional modeling when it come to datawarehouses.
star schema is a dimensional model with a fact table ( large) and a set of dimension tables ( small) . the whole set-up is totally denormalized.
however in cases where the dimension table are split to many table that is where the schema is slighly inclined towards normalization ( reduce redundancy and dependency) there comes the snow flake schema.

the nature/purpose of the data that is to be feed to the model is the key to your question as to which is better.

Is this answer useful? Yes | No

October 07, 2005 01:40:41

senkumar79

Member Since: October 2005 Contribution: 17

RE: Difference between Snow flake and Star Schema. Wha...

Star schema contains the dimesion tables mapped around one or more fact tables.

It is a denormalised model.

No need to use complicated joins.

Queries results fastly.

Snowflake schema

It is the normalised form of Star schema.

contains indepth joins ,bcas the tbales r splitted in to many pieces.We can easily do modification directly in the tables.

We hav to use comlicated joins ,since we hav more tables .

There will be some delay in processing the Query .

Is this answer useful? Yes | No

March 09, 2006 00:50:25

ravi kumar guturi

RE: Difference between Snow flake and Star Schema. Wha...

Star Schema means

A centralized fact table and sarounded by diffrent dimensions

Snowflake means

In the same star schema dimensions split into another dimensions

Star Schema contains Highly Denormalized Data

Snow flake contains Partially normalized

Star can not have parent table

But snow flake contain parent tables

Why need to go there Star:

Here 1)less joiners contains

2)simply database

3)support drilling up options

Why nedd to go Snowflake schema:

Here some times we used to provide seperate dimensions from existing dimensions that time we will go to snowflake

Dis Advantage Of snowflake:

Query performance is very low because more joiners is there

Enjoy n all the best

Is this answer useful? Yes | No

April 27, 2006 15:00:21

Sumit Banerjee

RE: Difference between Snow flake and Star Schema. What are situations where Snow flake Schema is better

veepee Wrote: star schema and snowflake both serve the purpose of dimensional modeling when it come to datawarehouses.
star schema is a dimensional model with a fact table ( large) and a set of dimension tables ( small) . the whole set-up is totally denormalized.
however in cases where the dimension table are split to many table that is where the schema is slighly inclined towards normalization ( reduce redundancy and dependency) there comes the snow flake schema.

the nature/purpose of the data that is to be feed to the model is the key to your question as to which is better.

Is this answer useful? Yes | No

December 14, 2006 23:38:32

sreedhar

RE: Difference between Snow flake and Star Schema. Wha...

Both represent the dimensional model, in case of star schema the dimensons does not split ....where as in the case of snowflake u can see the further split in dimension for eg: if u r using more than one telephone at ur desk and it is available to more than one and at the same time the telephone gives the facility of usage more than one member then in this case we need further split in the table, because we need in depth analysis..

Is this answer useful? Yes | No

March 29, 2007 05:28:58

Chris

RE: Difference between Snow flake and Star Schema. Wha...

Star Schemas generally contain fact tables with fully denormalised dimension tables connecting directly to the fact table. A snowflake schema is a modification of this approach whereby the dimension tables will contain elements of normalisation and be broken down into multiple tables. The main dimension will still link directly with the fact but it will also contain a "helper" dimension relationship. General rule of thumb is to avoid the snowflake as it goes against the goals of a data warehouse. There are however situations where the snowflake schema makes sense. Typical example is a dimension that contains customers. Lets say we have 50 attributes for customers and 80% of the time these are not captured. Then it is definitely a better design to snowflake the customer dimension. Typically though the snowflake is an exception to the rule and should only be implemented when absolutely necessary taking into consideration 2 goals - improve performance and increase understanding. If you can do that then absolutely the snowflake can be used.

Is this answer useful? Yes | No

Go To Top