1. Back To Blog

SQL Server interview questions: -What is the difference between star schema and snow flake design?

• By Shiv Prasad Koirala in SQL
• Jul 28th, 2012
• 1793
• 0

Star schema consists of fact and dimension tables. The fact tables have the measures and dimension tables give more context to the fact tables.

 

In the below figure "Star design" you can see we have four dimension tables and each one of them are referencing the fact tables for measure values. The references between dimension and fact tables are done using simple foreign key relationships.

 

Figure: - Star design

 

Snow flake design is very much similar to star design. The exception is the dimension table. In snow flake dimension tables are normalized as shown in the below figure "Snow flake design". The below design is very much similar to the star design shown previously but the products table and vendor tables are separate tables.

 

The relationship is more of a normalized format. So summing in other words Star design is pure denormalized design while snow flake can have normalized dimension tables.

 

Figure: - Snow flake design

 

Summing up with a comparison sheet.

 

	Snowflake Schema	Star Schema
Normalization	Can have normalized dimension tables.	Pure denormalized dimension tables.
Maintenance	Less redundancy so less maintenance.	More redundancy due to denormalized format so more maintenance.
Query	Complex Queries due to normalized dimension tables.	Simple queries due to pure denormalized design.
Joins	More joins due to normalization.	Less joins.
Usage guidelines	If you are concerned about integrity and duplication.	More than data integrity speed and performance is concern here.

 

Also see the following video on SQL Server interview question : -Explain inner joins, left joins and right joins

 

 

See for more stuffs on SQL Server Interview question

 

Regards,

 

Click to view more from author's on SQL Server Interview question

 

You can also buy our SQL Server interview question book by clicking here