Series: Subject: Topic:
Question: 53 of 135

What is the difference between star schema and snow flake schema? When we use those schema's?

Asked by: Interview Candidate | Asked on: Nov 23rd, 2006
Showing Answers 1 - 10 of 10 Answers
Rahul verma

Answered On : Nov 28th, 2006

Star Schema : Star Schema is a relational database schema for representing multimensional data. It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema are slicing down, performance increase and easy understanding of data.Snowflake Schema : A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. i.e dimension table hierachies are broken into simpler tables.In a star schema every dimension will have a primary key. In a star schema, a dimension table will not have any parent table. Whereas in a snow flake schema, a dimension table will have one or more parent tables. Hierarchies for the dimensions are stored in the dimensional table itself in star schema. Whereas hierachies are broken into separate tables in snow flake schema. These hierachies helps to drill down the data from topmost hierachies to the lowermost hierarchies.

Yes  1 User has rated as useful.
Login to rate this answer.

Star schema : In this star schema fact table in normalized format and dimension table is in de normalized format.  It also known as basic star schema.Snow flake schema: In this both dimension and fact table is in normalized format only. It is also knwon as Extended star schema.If u r taking the snow flake it requires more dimensions, more foreign keys, and it will reduse the query performance, but it normalizes the records.,depends on the requirement we can choose the schema

Login to rate this answer.

Answered On : Jan 4th, 2007

Both these schemas are generally used in Datawarehousing.Star schema as the name indicates resembles the form of star as there is only one fact table which is associated with numerous dimension tables (with the help of foreign keys).This schema depicts a high level of denormalized view of data.However in Snowflake schema the dimension tables are further normalized into different tables (fact table is single in this schema also).This schema stores data in a more normalized form .It depends on scenario as how much data is generally there in the datawarehouse,generally star schema is preferred.

Login to rate this answer.

Answered On : Jan 17th, 2007

Star schema:  it is a highy de-normilised techinque... in this one fact table is associated with n number of dimensions table... .. it looks like a star..snow Flake Schema:  If We apply normilised Princples to Star Schema Then It is Known As Snow Flake Schema.. In This Each Demsion Table Associated With Sub Dimenson Table..

Yes  1 User has rated as useful.
Login to rate this answer.

Answered On : Sep 26th, 2008

View all answers by lakshmib

Star Schema means A centralized fact table and surrounded by different dimensions Snowflake means In the same star schema dimensions split into another dimensions Star Schema contains Highly Denormalized Data Snow flake contains Partially normalized dataStar cannot have parent table But snow flake contain parent tables. Need to go for Star: Here Simply database Support drilling up options Need for 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 are present

Login to rate this answer.

Answered On : Dec 17th, 2008

View all answers by mri_sonam

Star Schema:Definition: The star schema is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. A single Fact table (center of the star) surrounded by multiple dimensional tables(the points of the star).Advantages:   Simplest DW schema    Easy to understand    Easy to Navigate between the tables due to less number of joins.    Most suitable for Query processing Disadvantages:   Occupies more space    Highly Denormalized Snowflake schema:Definition: A Snowflake schema is a Data warehouse Schema which consists of a single Fact table and multiple dimensional tables. These Dimensional tables are normalized.A variant of the star schema where each dimension can have its own dimensions.Advantages:    These tables are easier to maintain    Saves the storage space.Disadvantages:   Due to large number of joins, it is complex to navigate Starflake schema - Hybrid structure that contains a mixture of (denormalized) STAR and (normalized) SNOWFLAKE schemas.

Login to rate this answer.

Answered On : Apr 14th, 2009

View all answers by ranjandas123

1. Star shema is Demormalised data but in snowflack schema its normalised2. when when star schema splited into it become to snowflack schema3. Performace will be more on star schema but performance less in snoflack schema due to joins4. Star schema is simple but snowflack is complex5. Dimention table in star schema having no maste table but Dimention table in snowflack having so many maste table.6. Star schema having one fact table sarounded by dimetion table but incase of snowflack schema more than one fact table sarrounded by dimetion table.

Login to rate this answer.

Answered On : May 25th, 2009

View all answers by erpooja88

Accurate information is star schemas have more joins because it has only one dimension table per fact and so to retrieve more queries we have to use joins...

Login to rate this answer.

Answered On : Jun 23rd, 2009

View all answers by shakilag

Star Schema: A single fact table associated to N dimension tables. This schema is denormalised and hence has good performance as this involves a simple join rather than a complex query.Snowflake schema:A star schema in which dimensions are further normalised.As this schema is normalised and hence will give slower performance as this involves lot of complex joins (involves many dimensions).

Login to rate this answer.

Answered On : Jul 5th, 2013

Star schema: When dimension table contains less number of rows, we can go for Star schema.In this Both Dimension and Fact Tables are in De-Normalized form.Good for datamarts with simple relationships (1:1 or 1:many).Less no. of foreign keys and hence lesser query execution time.

snowflake schema: When dimension table is relatively big in size, snowflaking is better as it reduces space.Dimension Tables are in Normalized form but Fact Table is still in De-Normalized form.Good to use for datawarehouse core to simplify complex relationships (many:many).More foreign keys-and hence more query execution time.

Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions



twitter fb Linkedin GPlus RSS


Interview Question

 Ask Interview Question?


Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.