Snowflake Schema: Trading Query Speed for Storage
A snowflake schema saves storage by normalizing a star schema's dimensions into smaller, related tables. It's used in data warehouses to reduce redundancy, but the extra joins required can slow down queries, making it a trade-off against a simpler star schema.
The mental model
A snowflake schema is a star schema that has been put on a diet. It saves storage space by breaking down large dimension tables into smaller, more normalized tables, but this efficiency comes at the cost of slower, more complex queries due to the increased number of joins required.
How it works
Like a star schema, a snowflake schema has a central fact table containing measurements (e.g., sales amount). However, its dimension tables are normalized. Low-cardinality attributes—columns with many repeated values like 'Category Name' or 'Brand Name'—are split into their own separate lookup tables. This creates chains of tables branching off the main dimensions, making the entity-relationship diagram resemble a snowflake.
When to use it
Use a snowflake schema when storage costs are a primary concern and your dimension tables are very large with highly redundant data. It enforces data integrity by avoiding duplication, as an update to a category name only needs to happen in one place in the small category dimension table. It's a holdover from a time when disk space was far more expensive.
When not to use it
Avoid this schema when query performance is paramount. Modern analytical databases are often optimized for the simpler, denormalized structure of a star schema. The performance penalty from the additional joins in a snowflake schema can be significant, often outweighing the storage savings, especially with modern data compression techniques.
One canonical example
Imagine a star schema with a fact_sales table and a dim_product table. The dim_product table might contain product_id, product_name, category_name, and supplier_name. In a snowflake schema, you would normalize dim_product. It would only contain product_id, product_name, category_id, and supplier_id. Two new tables would be created: dim_category (with category_id, category_name) and dim_supplier (with supplier_id, supplier_name). A query for sales by category now requires joining three tables instead of two.
Interview question
What is the primary trade-off when implementing a snowflake schema compared to a star schema?
- a.It offers a simpler data model but requires more complex ETL processes.
- b.It provides better data integrity at the expense of increased data redundancy.
- c.It allows for faster data loading but limits analytical flexibility.
- d.It reduces storage space but leads to slower query execution.Correct
Why? this is the answer
The card states that a snowflake schema "saves storage space by breaking down large dimension tables" but "comes at the cost of slower, more complex queries due to the increased number of joins required." Option B is incorrect because a snowflake schema reduces data redundancy through normalization, thereby enhancing data integrity, not increasing redundancy.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data warehouse — each one lists the topics its interview covers.
See open roles