A snowflaked product dimension spans seven tables and every dashboard joins all of them — how would you restructure it?

## Read the situation before you redesign A seven-table dimension chain means every dashboard query carries six joins to reach labels that logically describe one product. The symptoms are predictable: queries that are long and copy-pasted, analysts who join at the wrong level and produce numbers nobody can reconcile, and dashboards that are slow and hard to reason about. The fix is a flattening, but it has to be done in an order that cannot break the fact table or the load. ## Step 1 — declare the target grain of the dimension The flat dimension must be **one row per product key**, because that is the key the fact table already carries. Saying this out loud matters: it fixes the uniqueness contract you will test, and it makes clear that no fact table is being rewritten. The fact's grain, measures and foreign keys are untouched by anything that follows. ## Step 2 — prove that flattening is safe This is the step candidates skip and the one that actually causes incidents. A hierarchy chain flattens correctly only if every parent table contributes at most one row per child. If a product can belong to two categories, the join emits two rows per product, and any fact measure aggregated through the flat dimension is counted twice. Test it explicitly before building anything: ```sql -- must return zero rows for a safe flatten SELECT product_key, COUNT(*) AS n FROM dim_product_flat GROUP BY product_key HAVING COUNT(*) > 1; ``` If a level fails this test, it is a multi-valued relationship, not a hierarchy level, and it must stay out of the flat dimension and be modelled with the construct meant for many-to-many attribution. ## Step 3 — audit what is actually used Before copying 120 columns forward, look at which attributes real queries reference. Deep snowflakes accumulate levels that were modelled because the source had them, not because anyone asks questions at that level. Carrying dead attributes forward doubles the cost of every future change to the dimension. Keep what is used, keep what is plausibly about to be used, and leave the rest reachable in the normalized layer. ## Step 4 — build flat as a view first, then materialize Start with a view so the change is cheap to revise, and materialize once the column list has settled: ```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, v.division_name FROM dim_product p LEFT JOIN dim_brand b ON b.brand_key = p.brand_key LEFT JOIN dim_category c ON c.category_key = b.category_key LEFT JOIN dim_department d ON d.department_key = c.department_key LEFT JOIN dim_division v ON v.division_key = d.department_key; ``` Use outer joins deliberately: with inner joins, a product whose brand row is missing disappears from the dimension entirely, and its facts silently drop out of every report. Outer joins keep the product and leave the label null, which is a visible defect rather than an invisible one — and it tells you to supply an "Unknown" default rather than a null. ## Step 5 — keep the normalized tables Do not delete the chain. It remains where the data is loaded, governed and tested, and it is the source of the flat table. The end state is the standard hybrid: normalized where you load, flat where you query. This also means upstream master-data ownership of any level is preserved unchanged, which removes the main political objection to the flattening. ## Step 6 — reconcile, then migrate consumers Run the same handful of business-critical aggregates against the old join chain and the new flat dimension and diff them: total rows in the dimension, distinct products, and two or three revenue-by-level totals. Any difference is either the fan-out you tested for, a null-versus-missing difference introduced by join type, or a genuine bug in the old queries — all three are worth knowing before anyone's dashboard changes. Then move consumers over, one dashboard at a time, rather than in a single cut. ## What the interviewer is testing That you separate the *shape* change from the *contract* change; that you test the one-row-per-key assumption instead of assuming it; that you know inner joins can silently drop dimension members; and that you preserve rather than demolish the layer other teams depend on. A candidate who answers only "denormalize it into one table" has the right instinct and none of the safeguards.