STAR vs SNOWFLAKE
SNOW-FLAKE SCHEMA DESIGN
Snow flake schema is just like star schema but the difference is, here one or more dimension tables are connected with other dimension table as well as with the central fact table. See the example of snowflake schema below.
Here we are storing the information in two dimension tables instead of one. We are storing the food type in one dimension (“type” table as shown below) and food in other dimension. This is a snowflake design. Type
| KEY | TYPE_NAME |
|---|---|
| 1 | Burger |
| 2 | Fries |
Food
| KEY | TYPE_KEY | NAME |
|---|---|---|
| 1 | 1 | Chicken Burger |
| 2 | 1 | Veggie Burger |
| 3 | 2 | French Fries |
| 4 | 2 | Twister Fries |
snow flaking actually increase the level of normalization in the data. This has obvious disadvantage in terms of information retrieval since we need to read more tables (and traverse more SQL joins) in order to get the same information. Example, if you wish to find out all the food, food type sold from store 1, the SQL queries from star and snowflake schemata will be like below
32.What is the difference between star flake and snow flake schema?
Star Schema
- facts and all the primary keys of your dimensional tables in Fact table.
- And fact tables primary is the union of its all dimension table key.
- In star schema dimensional tables are usually not in BCNF form.
- starflake schema is a combination of a star schema and a snowflake schema.
- The hierarchies of star schemas are denormalized, while the hierarchies of snowflake schemas are normalized.
- Starflake schemas are normalized to remove any redundancies in the dimensions.
SnowFlake
- Its almost like star schema but in this our dimension tables are in 3rd NF, so more dimensions tables. And these dimension tables are linked by primary, foreign key relation.
- snowflake schema is a logical arrangement of tables in a multidimensional database such that the entity relationship diagram resembles a snowflake shape.
- The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions.
33.Which schema is better – star or snowflake?
- The choice of a schema always depends upon the project requirements & scenarios.
- Since star schema is in de-normalized form, you require fewer joins for a query.
- The query is simple and runs faster in a star schema.
- Coming to the snowflake schema, since it is in normalized form,
- it will require a number of joins as compared to a star schema, the query will be complex and execution will be slower than star schema.
- Another significant difference between these two schemas is that snowflake schema does not contain redundant data and thus it is easy to maintain.
- star schema has a high level of redundancy and thus it is difficult to maintain.
- If the purpose of your project is to do more of dimension analysis, you should go for snowflake schema. For example, if you need to find out that “how many subscribers are tied to a particular plan are currently active?”– go with snowflake model.
- If the purpose of your project is to do more of metrics analysis, you should go with a star schema. For example, if you need to find out that “what is the claim amount paid to a particular subscriber?”– go with a star schema.
- we used snowflake schema because we had to do analysis across several dimensions and generate summary reports for the business.
- Another reason for using snowflake schema was it is less memory consumption