Difference Between Star Schema and Snowflake Schema
Star Schema, as the name suggests, it can appear to be in a star shape with a single fact table in the middle and a set of dimension tables connected to it. Similarly, Snowflake Schema, as the name suggests, it appears to be in a snowflake shape with a single fact table in the middle and a set of dimension tables connected to it as well as other dimension tables in the system. As Star Schema has unformatted or non-normalized data, it can have repetitive data, and that leads to inconsistency of data. On the other hand, Snowflake Schema’s data are normalized, and so it is more consistent and redundant.
What is Star Schema?
It is the simplest data warehouse schema. As its name suggests, its structure is like a star. In this schema, the fact table is present in the center, and the number of dimension tables is associated with it through foreign key, and the dimension table contains the set of attributes. In this schema, every dimension is represented with a single dimension table only, but the dimension tables are not joined with each other. Its advantages include slicing down, easy understanding of data, optimal disk usage, and performance increase. Let us see an example for a better understanding.
Example:
Consider a refrigerator manufacturing company, and we need to create a schema for the sales of this refrigerator manufacturing company. Sales will have the following dimensions:
- Item
- Location
- Branch
- Time
The schema has a fact table at the center for sales which would contain keys to associate with each dimension, having two measures, i.e. units sold and dollars sold.
What is Snowflake Schema?
Snowflake Schema is the extension of the star schema. It adds additional dimensions to it. As its name suggests, it looks like a snowflake. In this schema, the dimension tables are normalized, i.e. data is split into additional tables. Splitting the table reduces redundancy and memory wastage. It has the hierarchical form of dimensional tables. The dimension and sub-dimension tables are associated with the primary and foreign keys in the fact table. It is easier to implement and uses less disk space. As it has multiple tables, the performance of the query is reduced. More maintenance is required because there are more lookup tables. Let us see an example for a better understanding.
Example:
Considering the same example as above of refrigerator manufacturing company, in the snowflake schema, the fact table is the same as in star schema, but the major difference is in the definition or layout of dimension tables.
In this schema, the single dimension table of the item has been normalized and has been split, and a new supplier table has been created, including information on the type of supplier. Similarly, the dimension table of location is normalized, and data is split into a new city table containing details of the particular city.
Head to Head Comparison Between Star Schema and Snowflake Schema (Infographics)
Below are the top 9 differences between Star Schema vs Snowflake Schema.
Key Differences Between Star Schema and Snowflake Schema
Let us see some major differences between Star Schema vs Snowflake Schema.
Star Schema
- Hierarchies of dimension in star schema are stored in a dimension table.
- It contains a central fact table encircled by a dimension table.
- In this, a single join associated the fact table with a dimension table.
- It has a simple design.
- The data structure is denormalized.
- The query executes at a faster rate.
- In this cube, the processing is faster.
- It has more redundant data.
- It uses simple queries.
- Star schema is easy to understand.
- In a star schema, more space is consumed.
Snowflake Schema
- Hierarchies in a snowflake schema are stored in separate tables.
- It also contains a fact table surrounded by a dimension table, and these dimension tables are further surrounded by a dimension table.
- In this schema, many joins are necessary for fetching the data.
- It has a complex design.
- The data structure is normalized in the snowflake schema.
- The query executes comparatively slower than the star schema.
- In the snowflake schema, cube processing is slower.
- It contains less redundant data.
- It uses complex queries.
- The snowflake schema is comparatively difficult to understand that star schema.
- In the snowflake schema, less space is consumed.
Star Schema vs Snowflake Schema Comparison Table
Let us discuss the comparisons between Star Schema vs Snowflake Schema.
| Characteristic |
Star Schema |
Snowflake Schema |
| Maintenance/Change | It has more redundant data, and hence it is more difficult to change or maintain | This schema is easier to change and maintain due to less redundancy |
| Understandability | The complexity of the query is less, and hence it is easy to understand | Queries applied are more complex and hence difficult to understand |
| Query Execution Time | It has less foreign keys, and hence the query execution is faster and takes lesser time. | Due to more foreign keys, the query execution time is more, or the query executes slowly. |
| Type of Data Warehouse | Better for datamarts having single relationship, i.e. one to one or one to many | Better for complex relationships, i.e. many to many relationships |
| Number of Joins | It has more number of joins | It has less number of joins |
| Dimension Table | It has only one dimension table for each dimension | It has one or more dimension table for a single dimension |
| Usability | If the size of the dimension table is less, i.e. less number of rows, then star schema is preferred. | Good to use when the size of the dimension table is bigger |
| Normalization and Denormalization | Both the fact table and dimension tables are denormalized. | A fact table is denormalized, while dimension table is normalized |
| Data Model | It follows a top-down approach | It follows a bottom-up approach |
Conclusion
In this article, we discuss the Star Schema vs Snowflake Schema in detail. These schemas are used to represent the data warehouse. They are similar in some aspects and different in others. Snowflake is the extension of the star schema. When data is more, then snowflake is preferred as it reduces redundancy, but the star is comparatively more popular than the snowflake schema.
Recommended Articles
This is a guide to Star Schema vs Snowflake Schema. Here we also discuss the Star Schema vs Snowflake Schema key differences with infographics and comparison table. You can also go through our other suggested articles to learn more –
- Encoding vs Decoding – Top Differences
- Functional Testing vs Non-Functional Testing
- Smoke Testing vs Sanity Testing
- Snowflake Schema
All in One Software Development Bundle (600+ Courses, 50+ projects)
4.6 (3,144 ratings)
View Course
600+ Online Courses
3000+ Hours
Verifiable Certificates
Lifetime Access
