What is Star Schema and Snowflake Schema in Data Warehousing?
Can you please answer this question what is the major difference between star schema and snowflake schema in data warehousing?
Sart schema and Snowflake schemas play an important role in Data Warehouse.
- Start Schema: One fact table in the middle and surrounded by many dimensional tables like a star and the dimensional tables are denormalized. Which means there will not be an extended dimension for the dimensional tables.
Here the Sope of analysis will be less.
- Snowflake Schema: One fact table in middle surrounded by the dimensional tables and dimensional tables are normalized. Which means there will be extended dimensions for the dimensional tables in the star.
The scope of analysis will be more here.
Let us see the major difference between star schema and snowflake schema.
Star Schema Model:
Star schema is a join between one fact table and multiple dimensional tables like a star here dimensional tables are in denormalized form.
Since the dimensions are in denormalized form sometimes it may cause data integrity and storage issues.
Here fact tables refer to dimensional tables and dimensional tables will not refer other dimensional tables.
Snowflake Schema Model:
Snkowflake schema is a schema which comes in the dimensional modelling.
This is used to design data warehouse and data marts.
In snowflake schema we use fact tables along with dimensional tables.
Like star schema here also fact tables in the middle and dimensional tables at the edge. Fact tables refer to dimensional table and dimensional tables can refer to dimensional tables. This feature is not available in star schema.