Difference between Star Schema and Snowflake Schema?

Star Schema

A star schema is a conference for organizing the records into dimension tables, fact tables, and materialized views. All of the information is stored in columns, and metadata is needed to identify the columns that function as multidimensional objects.

A star schema is a relational schema where a relational schema whose design describes a multidimensional data model. The star schema is the explicit data warehouse schema. It is called a star schema because the entity-relationship diagram of this schemas reproduces a star, with points, diverge from the main table. The middle of the schema includes a large fact table, and the points of the star are the dimension tables.

Snowflake Schema

The snowflake schema is a variant of the star schema model, where several dimension tables are normalized, further dividing the data into more tables. The resulting schema graph forms a shape similar to a snowflake.

The snowflake schema is an extension of the star schema where each point of the star breaks out into more points. It is known as snowflake schema because the diagram of snowflake schema simulates a snowflake. Snowflaking is an approach to normalizing the dimension tables in STAR schemas. When it normalizes some dimension tables entirely, the resultant structure resembles a snowflake with the fact table in the middle.

The difference between the snowflake and star schema models is that the dimension tables of the snowflake model can be kept in normalized form. Such a table is simple to maintain and also store storage space because a large dimension table can be completely large when the dimensional structure is involved as columns.

Because much of this space is redundant information, making a normalized structure will decrease the complete space requirement. However, the snowflake structure can decrease the effectiveness of browsing because more joins will be required to execute a query. Consequently, the system performance can be adversely impacted. Performance benchmarking can be used to decide what is good for this design.

Let us see the comparison between Star Schema and Snowflake Schema.

Star SchemaSnowflake Schema
It is a simple database design.It is a complex database design.
In a star schema, only a single join makes the relationship among the fact table and any dimension tables.A snowflake schema needed several joins to retrieve the data.
Cube processing is quicker.Cube processing can be slow because of the complex join.
It doesn't use normalization.It uses normalization and denormalization.
Star schema uses a fewer number of joins.Snowflake schema uses a large number of joins.

Updated on: 23-Nov-2021


Kickstart Your Career

Get certified by completing the course

Get Started