When to use snowflake and when star schema?

This question is also very common on interviews, but it is also essential part of Data Warehouse design.

Star schema

In star schema dimensions that reflect a hierarchy are flattened into a single table.

For example in a star schema Product Dimension would have columns like Product Line, Model, Inventory in one denormalized table, but in relational database this hierarchy most likely will be normalized with multiple tables with one-to-many relationships.

Snowflake schema

A snowflake schema does not flatten a hierarchy dimension into a single table. It would, instead, have two or more tables with a one-to-many relationship. Dimensions here are more in normalized structure.

Good example when to use snowflake schema is when we have Company with many types of products. Some of the products will have few attributes some other products will have a lot of attributes. Best practice here is to create main dimension for products that will contain common attributes like Name, Price, Product Number etc. and then to create additional sub dimensions for each distinct product like Groceries Dimension, Hardware Dimension, Books Dimension etc.
The sub dimensions must contain a foreign key of the main Product dimension table.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.