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 BYJU’S to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2024, GATE Admit Card, GATE Application Form, GATE Syllabus, GATE Cutoff, GATE Previous Year Question Paper, and more.
Comments