What is difference between Star and Snowflake schema ?

                    Are you looking for the details about star schema and snowflake schema? If so, then you reached the right place. In this article, we will see details about it along with the differences between the Star & Snowflake schema.

A) What is star schema? 

                    The star schema is the simplest schema used to develop dimensional data marts and the data warehouse star schema consists of one or more fact tables referencing the multiple numbers of dimension tables.

                    The star schema separates business process data into facts and dimensions facts holds measurable, quantitative data on the other hand dimensions provides descriptive attribute related to fact data.

                   1) Benefits:- The star schema is denormalized and the benefits of star schema are the queries are simple, business logic reporting is simplified, better query performance,                                         improved performance for aggregation operation.

                   2) Disadvantages:- star schema is not flexible for complex analytical needs. It does not support many to many relationships. The data integrity is not well enforced due to the denormalized state.

B) Snowflake schema 

                   The Snowflake schema is a logical arrangement of tables in a multidimensional database with entity relationships that resemble a snowflake shape.

                  Snowflake schema has centralized fact tables and those are connected to multiple dimensions.

                  The snowflake schema is similar to the star schema but in the Snowflake schema dimensions are normalized into multiple related tables.

                 1) Benefits:- Below are the benefits of snowflake schema Better storage savings due to normalization. optimization of some OLAP database models with snowflake schema.

                 2) Disadvantages:- Complexity in SQL query due to normalization. Data loads into the snowflake schema must be highly controlled and managed.

C) Difference between snowflake and star schema 

                   The snowflake and star schema are similar in nature. However, the Snowflake schema is normalized for some dimensions. On other hand, the logical dimensions are denormalized in the star schema.

                             Learn more about snowflake here