Data warehousing - Differences between star and snowflake schema
|
Differences between Star and Snowflake Schema.| Star schema | Snowflake schema | | A star schema model can be depicted as a simple star: a central table contains fact data and multiple tables radiate out from it, connected by the primary and foreign keys of the database. | The snowflake schema represents a dimensional model which is also composed of a central fact table and a set of constituent dimension tables which are further normalized into sub-dimension tables. | | They are not very easy to maintain or change as it has redundant data. | They are easy to maintain and change as there is no redundancy. | | It has lower query complexity and is easy to understand. | It has a lot of complex queries and is not very easy to understand. | | The query execution time is faster as there are less number of foreign keys. | The query execution time is slow as there a lot of foreign keys. | | They can be used for datamarts as they have simple relationships. | They can be used for the datawarehouses to simplify the complex relationships. | | They have very few joins. | They have a high number of joins. | | It contains only a single dimension table for each dimension. | It has more than one dimension table for each dimension. | | Whenever the dimension table contains a less amount of rows the star schema is used. | Whenever the dimension table is large in size snowflake schema is used as it helps in reducing the space. | | It uses the top down approach. | It uses the bottom up approach. | | The dimension and the fact tables are both in the denormalized form. | The dimension table is in the normalised form but the fact table is in the denormalized form. |
|
