When is snowflaking a dimension into normalized sub-tables actually the right call?

## Start from what the snowflake actually buys A normalized dimension buys exactly two things: each label is stored once, and each hierarchy level exists as an addressable entity in its own right. Everything else — joins, comprehension, load ordering — it costs. So the question is only ever: is one of those two benefits load-bearing for *this* dimension? ## Condition 1 — a very large dimension with a wide, highly repeated block The storage argument fails on ordinary dimensions because they are small. It does not fail on all of them. Consider a customer dimension of 300 million rows carrying a 40-column demographic and geographic block whose values take only a few thousand distinct combinations. Splitting that block into its own table and leaving a key behind removes a genuinely large amount of duplicated data, and it also decouples updates to the block from updates to the customer row. This is a scale threshold, not a principle: run the numbers before you invoke it. (The related pattern of a separately-keyed attribute cluster is a dimension-design pattern in its own right and is covered under degenerate, junk and mini-dimension patterns; here the point is simply that size can flip the trade-off.) ## Condition 2 — the level is a governed entity someone else masters Some hierarchy levels are not really attributes of the dimension; they are entities the enterprise manages separately. A chart of accounts, a regulatory product classification, an organizational unit tree published by HR. When an upstream master-data system owns that list, keeping it as one reference table gives you a single place to load it, test it and prove that every mart uses the same version of it. Flattening it into six different dimensions makes divergence possible and hard to detect. Note the boundary: making sure two marts *mean the same thing* by a shared dimension is conformance, a separate subject. Here the argument is narrower — a governed level is easier to maintain as one table. ## Condition 3 — different change cadence or its own history If a product's description changes weekly but its department assignment changes twice a decade, versioning them together means rewriting department history for description edits. Keeping the slow, governed level in its own table with its own effective dating is often simpler and produces much less history churn. The mechanics of that versioning belong to slowly-changing-dimension design; the shape decision belongs here. ```sql -- normalized reference level, loaded and versioned on its own cadence CREATE TABLE dim_department ( department_key BIGINT PRIMARY KEY, department_code VARCHAR(20), department_name VARCHAR(100) ); ``` ## Weak reasons candidates offer - *"It is more normalized, so it is better design."* Normalization protects write integrity under concurrent updates. A mart has one writer and read-only consumers; the goal is read clarity. - *"It saves storage."* True in the trivial sense and immaterial on any normal dimension. Compute the number before you claim it. - *"The hierarchy is deep."* Depth is a reason to model the hierarchy carefully, not automatically a reason to split it into tables. A ten-level fixed hierarchy is still fine as ten columns. - *"Our tool prefers it."* Some presentation tools do model levels explicitly, and that is a legitimate constraint to respect — but let the tool's requirement be the stated reason, not a claim about modelling purity. ## The hybrid that usually wins In practice you rarely have to choose globally. Keep the normalized reference tables in the layer where data is integrated and governed — that is where the maintenance and ownership benefits live — and publish a flattened, wide dimension to the consumption layer, materialized or as a view: ```sql CREATE VIEW dim_product_flat AS SELECT p.product_key, p.sku, p.product_name, b.brand_name, c.category_name, d.department_name FROM dim_product p JOIN dim_brand b ON b.brand_key = p.brand_key JOIN dim_category c ON c.category_key = b.category_key JOIN dim_department d ON d.department_key = c.department_key; ``` Analysts see one table; the loader keeps one authoritative copy of each label. The one caution: flattening only works if each sub-table contributes at most one row per parent. If a product can belong to several categories, the join multiplies rows and inflates any measure joined through it — that case is a multi-valued relationship needing a different construct, not a flattening. ## How to answer Name the conditions, state that they are quantitative rather than aesthetic, and finish with the hybrid. An answer that says "never snowflake" is as unconvincing as one that snowflakes by reflex.