Difference Between Star Schema and Snowflake Schema

Introduction

The most common multidimensional data models used in a data warehouse are the star and snowflake schemas. The most important distinction between Star schema and Snowflake schema is that Star schema does not employ normalisation, but Snowflake schema does to avoid data redundancy. Schema creation necessitates the use of fact and dimension tables. To fully comprehend the difference between a fact and a dimension table, you may refer to our previously published article.

Definition of Star Schema

The star schema is a typical modelling paradigm in which the data warehouse is made up of a fact table and a single table for each dimension. The schema resembles a star, with the dimension table around the core fact table in an outspread form. The dimensions in the fact table are linked to the dimensions in the dimension table via a primary key and a foreign key.

We're making a schema that incorporates the sales of a firm that makes electrical appliances. The following dimensions are designed for sales: time, item, branch, and location. A primary fact table for sales is included in the schema, which includes keys to each of the four dimensions as well as two measures: dollar-sold and units-sold. The production of dimension identifiers such as time key and item key through the system reduces the size of the fact table.

Snowflake Schema Definition

Snowflake schema is a kind of star schema that incorporates dimensional tables in a hierarchical format. The fact table in this design is made up of different dimension and sub-dimension tables that are linked together by main and foreign keys. It was given the name snowflake because of its structure, which is similar to that of a snowflake.

It employs normalisation, which divides the data into many tables. As a consequence of the separation, there is less duplication and memory waste is avoided. A snowflake schema is easier to maintain, but it's more difficult to develop and comprehend. It may also impair browsing efficiency by necessitating additional joins to conduct a query.

Comparison Table Between Star Schema and Snowflake Schema

Conclusion

The data warehouse is designed using the Star and Snowflake schema. Both have advantages and disadvantages, with the snowflake schema being easier to maintain, reducing duplication and so using less space, but being more difficult to create. Star schema, on the other hand, is easy to grasp and construct, with fewer joins and simple queries, but it has significant drawbacks, such as data redundancy and integrity.

Although the usage of the snowflake schema reduces duplication, it is not as popular as the star schema, which is often used in data warehouse architecture.