6030笔记 - Star schema VS Snowflake schema - 《SQL 学习笔记》

Content: Star Schema Vs Snowflake Schema

originate from https://techdifferences.com/difference-between-star-and-snowflake-schema.html

Content: Star Schema Vs Snowflake Schema

Definition
Comparison
Conclusion

1. Definition

star schema

comprises of a fact table with a single table for each dimension. The schema imitates a star, with dimension table presented in an outspread pattern encircling the central fact table. The dimensions in fact table are connected to dimension table through primary key and foreign key.

Star Schema Example Diagram
Cons: Redundancy
For example, two cities can be of same state and country, so entries for such cities in the location dimension table will create redundancy among the state and country attributes.

snowflake schema

Snowflake schema is the kind of the star schema which includes the hierarchical form of dimensional tables. In this schema, there is a fact table comprise of various dimension and sub-dimension table connected across through primary and foreign key to the fact table.

It uses normalization which splits up the data into additional tables. The splitting results in the reduction of redundancy and prevention from memory wastage. A snowflake schema is more easily managed but complex to design and understand. It can also reduce the efficiency of browsing since more joins will be required to execute a query.

Snowflake Schema Example Diagram

the location dimension table involves the attributes location_key, street, and city_key, and city_key is linked to city dimension table containing the city, state and country attribute. Here state attribute can also further normalized.

2.Comparison

BASIS FOR COMPARISON	STAR SCHEMA	SNOWFLAKE SCHEMA
Structure of schema	Contains fact and dimension tables.	Contains sub-dimension tables including fact and dimension tables.
Use of normalization	Doesn’t use normalization.	Uses normalization and denormalization.
Ease of use	Simple to understand and easily designed.	Hard to understand and design.
Data model	Top-down	Bottom-up
Query complexity	Low	High
Foreign key join used	Fewer	Large in number
Space usage	More	Less
Time consumed in query execution	Less	More comparatively due to excessive use of join.

3.Conclusion

Star and Snowflake schema is used for designing the data warehouse. Both have certain merits and demerits where snowflake schema is easy to maintain, lessen the redundancy hence consumes less space but complex to design. Whereas star schema is simple to understand and design, uses less number of joins and simple queries but have some issues such as data redundancy and integrity.

However, use of snowflake schema minimizes redundancy, but it is not popular as star schema which is used most of the time in the design of data warehouse.