Difference Between Star Schema and Snowflake Schema

Both of these are multidimensional models deployed for a data warehouse. There is a primary difference between Star Schema and Snowflake Schema. The Star Schema contains the dimension tables and fact tables- but the Snowflake schema contains sub-dimension tables as well, along with both of these. In this article, we will discuss more ways in which both of them vary. Read ahead to know more.

What is a Star Schema?

It is a multidimensional model type that one can use in the case of a data warehouse. A typical Star Schema contains both- the dimensional tables and the fact tables. It also makes use of a fewer number of foreign-key joins. In simpler words, this type of schema leads to the formation of a star with dimension tables and fact tables.

What is a Snowflake Schema?

It is also a multidimensional model type that one can use in the case of a data warehouse. A typical Snowflake Schema contains all three- dimension tables, fact tables, and sub-dimension tables. In simpler words, this type of schema leads to the formation of a snowflake with dimension tables, fact tables, and also sub-dimension tables.

Difference Between Star Schema and Snowflake Schema

Parameters Star Schema Snowflake Schema
Definition and Meaning A star schema contains both dimension tables and fact tables in it. A snowflake schema contains all three- dimension tables, fact tables, and sub-dimension tables.
Type of Model It is a top-down model type. It is a bottom-up model type.
Space Occupied It makes use of more allotted space. It makes use of less allotted space.
Time Taken for Queries With the Star Schema, the process of execution of queries takes less time. With the Snowflake Schema, the process of execution of queries takes more time.
Use of Normalization The Star Schema does not make use of normalization. The Snowflake Schema makes use of both Denormalization as well as Normalization.
Complexity of Design The design of a Star Schema is very simple. The designing of a Snowflake Schema is very complex.
Query Complexity It is very low in the case of a Star Schema. It is comparatively much higher in the case of a Snowflake Schema.
Complexity of Understanding It is very easy to understand a Star Schema. It is comparatively more difficult to understand a Snowflake Schema.
Total Number of Foreign Keys The total number of foreign keys is less in the case of a Star Schema. The total number of foreign keys is more in the case of a Snowflake Schema.
Data Redundancy Data redundancy is comparatively higher in Star Schema. Data redundancy is comparatively lower in Snowflake Schema.

Keep learning and stay tuned to get the latest updates on GATE Exam along with Eligibility Criteria, GATE Application Form, Syllabus, GATE Cut off, Previous Year Question Papers, and more.

Leave a Comment

Your Mobile number and Email id will not be published.

*

*