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.



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.


Here is our latest 20+ SQL Interview Questions and Answers video :-


See for more stuffs on SQL Server Interview question

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

You can also buy our SQL Server interview question book.

Shiv Prasad Koirala

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

We are on Social