Star schema vs Snowflake Schema

Question

In Business Intelligence perspective this is a common question but I am looking for a statistical answer.

Can we take decision depending on relational database to go for one of these design? I mean, is there any mathematical ratio among data volume that suits one of the schema?

Answer 1

In relational databases there are fundamentally 2 types of schema (and i realise there are other edge cases): 3NF and Star schemas. 3NF are normally found in transactional systems and Star schemas in analytical schemas. In a star schema it is possible to create snowflakes off a dimension but this is normally bad practice and should be avoided. If you have a very specific use case and you have the knowledge and experience to know that the only way to solve it is with a Snowflake then thats fine - however building Snowflakes because you don't know how to design a Star schema is not going to end well! So a Star schema with a limited number of Snowflakes may be ok but a design that has a large number of Snowflakes is not a Snowflake schema - it's just a badly designed Star schema

Answer 2

Star Schema Vs SnowFlake Schema..What to choose: well this entirely depends on the project requirement and scenarios.

If we want to dive more into Dimensional Analysis then SnowFlake will be a good choice because as suggested in above answer, it mains referential integrity, does not contain data redundancy because of it normalised behaviour. For eg: if we want to find out who are the customers that are attracted towards a particular scheme started by the Bank.!!

If the purpose is more into Metric Analysis, then Star is the best option. For eg: if we want to find how much amount did the customer spend in a particular scheme weekly/monthly/quarterly/yearly basis..how much profit does the company made etc.

As suggested above, Star schema is less complex because of less no. of joins and runs much faster, query execution is much better as compared to snowflake.

But again, these are used according to the need of the project.

I hope this answer is helpful. any suggestions, guidance is highly, deeply appreciated... :)

Answer 3

Star schema stores de-normalised data while snowflake stores normalised data.

Usually, snow flake retains the referential integrity in the relational database, meaning you will have many dimensions linked by primary/foreign keys. On the other hand, the star schema will have a flat structure that merges all of the linked tables into one dimension.

Star schema is less complex and has much better performance than the snow flake schema. In BI perspective, star schema should be the way the go. Snow flake should only be used when necessary.