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.

Updated on: 15-Apr-2021


Kickstart Your Career

Get certified by completing the course

Get Started