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.|
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
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