How do you model a fixed-depth product hierarchy like department, category and brand in a warehouse dimension?

## What a hierarchy is inside a dimension A hierarchy is an ordered set of levels in which each member rolls up to exactly one member of the level above: a product belongs to a brand, a brand to a category, a category to a department. Analysts use it as a **drill path** — start at department totals, drill to category, then to brand, then to individual product. In a dimensional model the dimension table is held at its lowest useful grain: one row per product. The higher levels are not separate tables in the default design; they are simply **descriptive attributes of that product row**. ## The flattened design ```sql CREATE TABLE dim_product ( product_key BIGINT PRIMARY KEY, product_id VARCHAR(40) NOT NULL, product_name VARCHAR(200), brand_key INTEGER, brand_name VARCHAR(100), category_key INTEGER, category_name VARCHAR(100), department_key INTEGER, department_name VARCHAR(100) ); ``` Every level of the drill path is a pair of columns — a stable key for grouping and a label for display. A department report and a brand report differ by one identifier: ```sql SELECT p.department_key, p.department_name, SUM(f.sales_amount) FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key GROUP BY p.department_key, p.department_name; ``` Swap `department_*` for `category_*` and you have drilled one level down. Nothing else in the query moves. ## Why this is the default Three things stay still. The **fact grain** is untouched — the hierarchy lives entirely on the dimension side. The **join count** stays at one per dimension however deep the hierarchy is. And the **query shape** is identical at every level, which is exactly what a BI tool needs in order to be told "these four columns, in this order, are a drill path". Filtering also works at any level without a join: `WHERE p.department_name = 'Footwear'` restricts a product-grain query to one branch. ## The two preconditions **Fixed depth**: every leaf is the same number of levels from the top. A three-level product tree where some products hang directly off a department is *ragged*, and flattening it leaves holes or forces padding. **Strict**: each member has exactly one parent. A product that legitimately belongs to two categories cannot be expressed by a single `category_name` column without choosing one and losing the other — that is a many-to-many, and it needs a bridge table instead. ## Pitfall: level labels are not unique across branches If two departments each contain a category named "Accessories", `GROUP BY category_name` collapses two unrelated branches into one row. The total is wrong and nothing errors. Fix it by grouping on the level's key column and carrying the name only for display, or by constructing a qualified label such as `Footwear > Accessories`. This is one of the most common silent reporting bugs in an otherwise sound model. ## Alternate hierarchies over the same dimension A dimension often supports more than one drill path. A date dimension typically carries both a calendar hierarchy (`day → month → quarter → year`) and a fiscal one (`day → fiscal_period → fiscal_quarter → fiscal_year`); a product may have a merchandising rollup and a different finance-reporting rollup. The flattened design handles this by simply adding a second set of level columns to the same row — one row per date, more columns. What you must **not** do is add a `hierarchy_type` column and duplicate rows: that breaks the one-row-per-member grain and every fact join fans out. ## When the hierarchy changes Re-parenting a category changes an attribute on the dimension row. Whether prior reports keep the old rollup or move to the new one is a slowly-changing-dimension decision and belongs to that topic; the relevant point here is that flattening keeps the hierarchy *inside* the dimension row, so it is versioned by whatever history policy that dimension already uses, with no separate hierarchy-history mechanism to maintain. ## When flattening stops working Non-strict membership (a member with several parents, or a fact that relates to several dimension members) calls for a bridge table with a group key. Variable depth or ragged branches call for a hierarchy bridge of ancestor–descendant pairs, or for padding the missing levels by repeating the lowest known value — with the phantom members that produces. Reach for those only when a precondition genuinely fails; the flattened columns are the design you should have to be argued out of. ## What interviewers listen for That you keep the dimension at leaf grain, that drilling is a `GROUP BY` change rather than a join change, that you can name the fixed-depth and strict preconditions unprompted, and that you spot the repeated-label trap.