Difference between Star schema and Snowflake schema in SQL Server

MS SQL ServerMicrosoft TechnologiesDatabase

Star schema is relational schema which is follow the concept of facts and dimensions. Fact table is used to store the event like login and dimension tables are used to store the reference data of the partitural event. In star schema , tables are completely denormalized because of this query performance time is very fast.

Snowflaking is a method of normalizing the dimension tables in a STAR schema. When we normalize all the dimension tables entirely, the resultant structure resembles a snowflake with the fact table in the middle. In the snowflake schema , one fact table is linked to multiple dimension table and each dimension table is linked to another dimension table. In snowflake schema , table are in normalized form.

Sr. No.KeyStar SchemaSnowflake Schema

1

Basic 

Star schema is relational schema which is follow the concept of facts and dimensions

A snowflake schema is an extension of the star schema 

2

Database Type

Work best in any data warehouse/ data mart

Better for small data warehouse/data mart.

3

Data redundancy 

High Data redundancy 

Low data redundancy 

4

Response time 

It is very fast 

It is slower than star schema 

5

Normalization 

Tables in database are not normalized.

Tables in database are normalized.


raja
Published on 21-Jan-2020 13:58:49
Advertisements