Difference Between Star and Snowflake Schema

In this post, we will understand the difference between star schema and snowflake schema.

Star Schema

• Hierarchies of dimensions are stored in a dimensional table.

• It contains a fact table that is surrounded by dimension tables.

• In this schema, a single join creates the relationship between a fact table and any dimension tables.

• It is a simple database design.

• It has high levels of data redundancy.

• The processing of cube is quick.

• A single dimension table contains the aggregated data.

• It is a de-normalized data structure.

• The queries run quickly in comparison to other schema.

• It uses start join query optimization technique. Hence, the queries perform well.

• Tables can be connected with multiple dimensions.

Snowflake Schema

• Hierarchies of dimensions are divided into separate tables.

• One fact table is surrounded by a dimension table which in turn is surrounded by other dimension tables.

• It requires multiple joins to fetch the data.

• It has a complex database design.

• It is a normalized data structure.

• It has low-level data redundancy.

• The data is split into different dimension tables.

• The processing of cube is slow due to complex joins in the schema.

• It can be represented using a centralized fact table, which may not be connected to multiple dimensions.