- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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?
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.
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.|
- Difference Between Star and Snowflake Schema
- Difference between Star schema and Snowflake schema in SQL Server
- What is Snowflake Schema?
- Difference Between Schema and Instance
- What is Star Schema?
- Difference between Schema and Database in MySQL?
- Difference between _SYS_BIC and _SYS_BI schema in SAP HANA
- Authoring schema vs Physical schema in SAP HANA
- What is instance and schema (DBMS)?
- Difference between Star and Mesh Topology
- Difference between Star and Ring Topology
- Checking all tables and schema in SAP HANA
- Difference between Star topology and Bus topology
- Checking SAP HANA Schema owner name
- Using Schema mapping in SAP HANA