Difference between Snowflake Schema and Fact Constellation Schema

Snowflake Schema:
Snowflake Schema is a type of multidimensional model. It is used for data warehouse. In snowflake schema contains the fact table, dimension tables and one or more than tables for each dimension table. Snowflake schema is a normalized form of star schema which reduce the redundancy and saves the significant storage. It is easy to operate because it has less number of joins between the tables and in this simple and less complex query is used for accessing the data from database.

Fact Constellation Schema:
The fact constellation schema is also a type of multidimensional model. The fact constellation schema consists of dimension tables that are shared by several fact tables. The fact constellation schema consists of more than one star schema at a time. Unlike the snowflake schema, the planetarium schema is not really easy to operate, as it has multiple numbers between tables. Unlike the snowflake schema, the constellation schema, in fact, uses heavily complex queries to access data from the database.

Let’s see the difference between Snowflake Schema and Fact Constellation Schema:



S.NO Snowflake Schema Fact Constellation
1. Snowflake schema contains the large central fact table, dimension tables and sub dimension tables. While in fact constellation schema, dimension tables are shared by many fact tables.
2. Snowflake schema saves significant storage. While fact constellation schema does not save storage.
3. The snowflake schema consists of one star schema at a time. Whereas the fact constellation schema consists of more than one star schema at a time.
4. In snowflake schema, tables can be maintained easily. In fact constellation schema, the tables are tough to maintain.
5. Snowflake schema is a normalized form of star schema. While fact constellation schema is a normalized form of snowflake schema and star schema.
6. Snowflake schema is easy to operate as compared to fact constellation schema as it has less number of joins between the tables. Fact constellation schema is not easy to operate as compared to snowflake schema as it has multiple number of joins between the tables.
7. In snowflake schema, to access the data from database simple and less complex query is used. While in fact constellation schema, to access the data from database heavier complex query is used.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.