Snowflake Schema

Start from a flat dimension and look for columns that depend on another column rather than on the dimension's key. In dim_product, category_name depends on subcategory_name: every product in Laptops sits in Computers. That dependency is a many-to-one hierarchy, and snowflaking moves each level to its own table with a surrogate key, leaving only the key of the parent level on each child table. The product row keeps subcategory_key; the subcategory row keeps category_key.

The fact table does not change. It still carries 1 foreign key per dimension and the measures at its grain, so fact grain and conformed dimensions work as before, and the slowly changing dimension rules of dimensional modeling still apply. Only the distance from the fact table to an attribute grows: a category is now 3 tables away.

The best-known snowflake schema in the field is a benchmark. The TPC-DS specification, the Transaction Processing Performance Council's decision support benchmark, states in clause 1.3.3 that its schema is a snowflake schema: 7 fact tables and 17 dimension tables, each dimension with a single column surrogate key. Its household_demographics dimension points to an income_band table, and customer points to customer_address and customer_demographics. The fact tables also carry those keys directly, a hybrid that keeps the most common filters 1 join away while the hierarchy stays normalized.

The 2 models answer the same questions from the same fact table. They differ only in how dimensions are stored. A star schema keeps every dimension as 1 wide, denormalized table joined directly to the fact table. A snowflake schema splits some dimensions into normalized chains. A real model is often a mix: a flat date and customer dimension beside 1 snowflaked product or geography hierarchy is still called a snowflake schema.

The star wins on everything a reader of the data feels: fewer joins, simpler SQL, fewer tables in the BI tool, and hierarchies that can drill from category to product inside 1 table. The snowflake wins on what the maintainer of the data feels: a category rename touches 1 row instead of thousands, a level shared by several dimensions has 1 owner, and the tables mirror a normalized source. For the star schema's own design rules, see the star schema guide; the star schema vs snowflake schema comparison walks through the same retail model built both ways.

Snowflake a hierarchy when a level is a real entity with a life of its own, and keep it flat when it is only a label.

The level is maintained separately. A merchandising team owns the category tree and, several times a year, renames categories or moves subcategories between them. As its own table, each change is 1 row with 1 owner and a clear audit trail; flattened, each change rewrites every product row beneath it, and a Type 1 overwrite on 50,000 products is a large update for a 1 word rename.

The level is shared. A geography that runs from city to region to country serves the customer and store dimensions, and the supplier dimension as well. Held once in dim_city and referenced by all 3, it cannot drift; copied into 3 flat dimensions, it is 3 copies to keep in step.

The dimension is very large and part of it is sparse or volatile. A customer dimension with 200 million rows and 40 demographic columns that change monthly, filled for only a fraction of customers, is cheaper to maintain with those columns split into their own table. Kimball's name for the rapidly changing case is a mini-dimension, which is SCD Type 4. Its key goes on the fact table beside the customer's rather than on the customer row, which keeps it 1 join away.

The source already arrives normalized and nobody queries the raw layer directly. Keep the normalized tables where the data is maintained, and publish a flat, star-shaped dimension built from them for every reader. That pattern gives the snowflake's single copy and the star's single join at the same time, and it is how most modern teams end up with a snowflake schema at all.

An outrigger is a dimension joined to another dimension rather than to the fact table. It is a single, limited normalization, not a whole hierarchy split into levels. The classic case is a date: an employee dimension has a hire date, and analysts want hires by fiscal quarter. Instead of copying 30 calendar columns into dim_employee, the hire date becomes a key into the date dimension, relabelled with columns such as Hire Date Fiscal Year.

Margy Ross's Kimball Group Design Tip 105 says outriggers are acceptable in moderation but should be the exception rather than the rule. The Kimball technique entry adds the alternative to consider first: when 2 dimensions are correlated, carry both as separate foreign keys on the fact table, and let the fact table relate them. A bank account's open date can be an outrigger; a customer's current segment usually belongs on the fact row, where it records the segment at the time of each event.

The same sources tell you to avoid full snowflakes. The Kimball technique entry gives 2 reasons: business users find them hard to understand and navigate, and they can hurt query performance. An interviewer who hears you call a single date outrigger a snowflake schema will ask which of the 2 you mean.

Columnar cloud warehouses changed the arithmetic in favour of the star. Google's BigQuery documentation recommends denormalizing with nested and repeated fields for performance and notes that a star schema is already optimized for analytics, so flattening it further may not change much. Microsoft's Power BI guidance says that the benefits of a single model table generally outweigh those of several, and recommends folding a snowflaked product dimension, with its subcategory and category tables, into 1 model table.

The optimizer can also remove some of the cost of a normalized design. Snowflake the database performs join elimination: when a query does not use any column of a joined table, it can drop the join, but only when the primary or unique keys and the foreign keys involved are declared with the RELY property, and it trusts you to keep those constraints true. A flattening view over a snowflake then costs nothing for queries that never read the category, as long as the keys are declared and correct.

Keep hierarchies normalized in the staging or intermediate layer, where 1 row per category is the easiest thing to keep correct, and publish flat dimensions in the layer that dashboards and analysts read. In a dbt project that is an intermediate model per level and 1 dimension model that joins them.

History is where a snowflake gets harder, and it is the point interviewers probe most. Suppose dim_product keeps Type 2 history and so does dim_subcategory. When a subcategory moves to a new category, it gets a new row with a new subcategory_key. Every product row still points to the old key, so to show the move in product history, each of those products needs a new Type 2 version pointing at the new key. 1 change in the category tree fans out into as many new product rows as the subcategory holds.

To stop the fan-out, point the product at the subcategory's durable business key instead of its versioned key, and resolve the version at query time by date, which keeps the tables independent at the cost of a range join. Or treat the upper levels as Type 1 and let history live only at the product level. Or flatten the hierarchy into dim_product and let 1 Type 2 process version the whole row, which is what a star schema does anyway. The slowly changing dimensions guide covers the Type 2 mechanics each of these relies on.

The 2 share a word and nothing else. A snowflake schema is a way to arrange fact and dimension tables. In Snowflake the cloud data platform, a schema is a namespace: the documentation defines it as a logical grouping of database objects such as tables and views, each schema belonging to a single database, so a table's full name reads database.schema.table.

A Snowflake database schema can hold a star or snowflake schema just as easily as a data vault or 1 wide table. Searches for a Snowflake data model or a Snowflake database schema usually mean the dimensional design, and the design decision is the same on any columnar warehouse: flat dimensions by default, normalized levels where a hierarchy is maintained on its own.

Interviewers rarely ask for a definition. They ask for a model, say for an online retailer, and watch what you do with the category tree and the geography. A strong answer draws a star, names the grain of the fact table, and snowflakes only the hierarchy with a real owner, saying why in 1 sentence: the category tree is maintained by another team and changes monthly, so it gets its own table, and a flat view serves the dashboards.

The follow-ups come from the trade-offs on this page: how many joins revenue by category takes, what a category rename costs in each design, how Type 2 history behaves across the levels, and whether storage justifies the split. The questions in the data modeling interview questions and the star schema interview questions are the ones asked in real data engineering interviews. To practise drawing the model itself, the data modeling practice problems run on the same canvas as the retail example, and the data modeling round guide covers how the round runs and what it asks for.