Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Difference between Star Schema and Snowflake Schema?
Star Schema
A star schema is a conference for organizing the records into dimension tables, fact tables, and materialized views. All of the information is stored in columns, and metadata is needed to identify the columns that function as multidimensional objects.
A star schema is a relational schema where a relational schema whose design describes a multidimensional data model. The star schema is the explicit data warehouse schema. It is called a star schema because the entity-relationship diagram of this schemas reproduces a star, with points, diverge from the main table. The middle of the schema includes a large fact table, and the points of the star are the dimension tables.
Snowflake Schema
The snowflake schema is a variant of the star schema model, where several dimension tables are normalized, further dividing the data into more tables. The resulting schema graph forms a shape similar to a snowflake.
The snowflake schema is an extension of the star schema where each point of the star breaks out into more points. It is known as snowflake schema because the diagram of snowflake schema simulates a snowflake. Snowflaking is an approach to normalizing the dimension tables in STAR schemas. When it normalizes some dimension tables entirely, the resultant structure resembles a snowflake with the fact table in the middle.
The difference between the snowflake and star schema models is that the dimension tables of the snowflake model can be kept in normalized form. Such a table is simple to maintain and also store storage space because a large dimension table can be completely large when the dimensional structure is involved as columns.
Because much of this space is redundant information, making a normalized structure will decrease the complete space requirement. However, the snowflake structure can decrease the effectiveness of browsing because more joins will be required to execute a query. Consequently, the system performance can be adversely impacted. Performance benchmarking can be used to decide what is good for this design.
Let us see the comparison between Star Schema and Snowflake Schema.
| Star Schema | Snowflake Schema |
|---|---|
| It is a simple database design. | It is a complex database design. |
| In a star schema, only a single join makes the relationship among the fact table and any dimension tables. | A snowflake schema needed several joins to retrieve the data. |
| Cube processing is quicker. | Cube processing can be slow because of the complex join. |
| It doesn't use normalization. | It uses normalization and denormalization. |
| Star schema uses a fewer number of joins. | Snowflake schema uses a large number of joins. |
570 Views
