Star Schema Snowflake Schema - differences

 

 When to use star schema and when to go for snowflake ?

 If you have a few dimensions   and low cardinality (less unique values in dims ) , but you require fast query execution, a star schema is the right choice. 

 However, if you have several dimensions and high cardinality, a snowflake schema will be a better scheme

.NO	Star Schema	Snowflake Schema
1.	In star schema, The fact tables and the dimension tables are contained.	While in snowflake schema, The fact tables, dimension tables as well as sub dimension tables are contained.
2.	Star schema is a top-down model.	While it is a bottom-up model.
3.	Star schema uses more space.	While it uses less space.
4.	It takes less time for the execution of queries.	While it takes more time than star schema for the execution of queries.
5.	In star schema, Normalization is not used.	While in this, Both normalization and denormalization are used.
6.	It’s design is very simple.	While it’s design is complex.
7.	The query complexity of star schema is low.	While the query complexity of snowflake schema is higher than star schema.
8.	It’s understanding is very simple.	While it’s understanding is difficult.
9.	It has less number of foreign keys.	While it has more number of foreign keys.
10.	It has high data redundancy.	While it has low data redundancy.

 

Basis of Distinction	Star Schema	Snowflake Schema
Definition and Meaning	Both fact tables and dimension tables are present in a star schema.	Dimension tables,  sub-dimension tables, and fact tables are all included in a snowflake schema.
Type of Model	The star schema is a top-down type of model.	The snowflake schema is a bottom-up type of model.
Space	Star schema uses more space compared to Snowflake Schema.	Snowflake schema uses less space comparatively.
Joint Relations	In a star schema, relationships between tables are represented by a single join, resulting in a simple data structure for fast query performance and easy data analysis.	The snowflake schema has a complex data structure with multiple levels of relationships between tables, represented by multiple joins. This can make the data structure more difficult to understand and result in slower query performance.
Response Time for Queries	Star schemas have faster query execution times due to a single join of a fact table and its attributes in dimensional tables.	Snowflake schemas require complex joins between tables, which can slow down query processing and impact other OLAP products.
Normalization	In a star schema, dimension tables are not organized in a normalized form. They are typically denormalized and contain multiple levels of information about a particular subject in a single table.	Dimension tables in snowflake schema are normalized.
Design Complexity	Has a simpler design compared to snowflake schema.	More complex design compared to star schema.
Query Complexity	Star schemas have simpler query design due to the fact the table is joined to only one level of dimensional tables.	Snowflake schemas, on another hand, have a more complex query design due to the need for multiple joins between the fact table and its dimensional tables. This leads to additional overhead in query writing.
Understanding Complexity	It is simpler to understand compared to snowflake schema.	More complex to understand compared to star schema.
Foreign Keys	Have a lesser number of foreign keys.	Comparatively has more foreign keys.
Data Redundancy	The star schema stores redundant data in the dimension tables.	The snowflake design fully normalizes the dimension tables and prevents data redundancy,
Advantages	Simple and easy-to-understand data structure. Fast query performance due to the single join between the fact table and its dimensional tables. Suitable for large volumes of data Good for ad-hoc querying and data analysis.	Normalized data structure reduces redundancy and increases data integrity. Allows for more complex relationships between data. Allows for easier data maintenance and management. Good for more structured predictable querying.
Disadvantages	The star schema has a limited ability to depict complex relationships between data. Can suffer from data redundancy and decreased data integrity. May not be suitable for smaller volumes of data.	The more complex data structure can be harder to understand and work with. Multiple joins between tables can result in slower query performance. Requires more storage and processing resources due to the larger number of tables.

logical data model vs. physical data model diff

 

 

	Logical data model	Physical data model
Platform-dependent database	No.	Yes.
Data structure	Entities, attributes, PKs, and FKs.	Database tables, rows, PKs, FKs, and data types.
Programmatical features	No.	Triggers and stored procedures.
Objective	Visualize business logic with data structures.	Organize data structure for database design.
Creators	Business analysts and data architects.	Software developers, programmers, and database administrators.
Complexity	Simple.	Complex.
When to use	To understand enterprise systems and business rules.	To plan, implement, and optimize data storage when you’re developing applications.