1. Back To Blog

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

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.

 

sql

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.

 

sql

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

Shiv Prasad Koirala

Visit us @ www.questpond.com or call us at 022-66752917... read more

We are on Social