Data warehousing - Differences between star and snowflake schema

Differences between Star and Snowflake Schema.

Star schemaSnowflake 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.
Data warehousing - Explain the use of lookup tables and Aggregate tables
se of lookup tables and Aggregate tables - At the time of updating the data warehouse, a lookup table is used.......
Data warehousing - What is real time data-warehousing?
What is real time data-warehousing? - The combination of real-time activity and data warehousing is called real time warehousing.......
Data warehousing - What is conformed fact? What is conformed dimensions use for?
What is conformed fact? What is conformed dimensions use for? - Allowing having same names in different tables is allowed by Conformed facts......
Post your comment