Star schema vs Snowflake schema

PDF

Star and snowflake are most common types of dimensional modeling. Always a debating question  in the data warehousing context is which one works better? You will here arguments favouring both sides, however the question is incomplete without mentioning the system/business. The decision whether to employ a star schema or a snowflake schema should consider the relative strengths of the database platform in question and the query tool to be employed.

Star Schemas

The star schema is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of one or more fact tables and the points of the star are the dimension tables.

Snowflake Schema

The snowflake schema is a variation of the star schema used in a data warehouse. The snowflake schema (sometimes callled snowflake join schema) is a more complex schema than the star schema because the tables which describe the dimensions are normalized.

Star vs Snowflake

 

Snowflake Schema Star Schema
Which Data warehouse? Good to use for small datawarehouses/datamarts Good for large datawarehouses
Normalization(dim table)
 3 Normal Form 2 Normal Denormalized Form
Ease of Use More complex queries and hence less easy to understand Less complex queries and easy to understand
Ease of maintenance/change No redundancy and hence more easy to maintain and change Has redundant data and hence less easy to maintain/change
Query Performance More foreign keys-and hence more query execution time Less no. of foreign keys and hence lesser query execution time
 
Home Star schema vs Snowflake schema

BI & DW - Interview questions | Insomnia articles | Job interview questions with answers