How do you roll up facts through a ragged, variable-depth org hierarchy in a star schema?

## What makes a hierarchy ragged Two distinct problems get the same label. **Variable depth**: one branch of the org chart runs CEO → VP → director → manager → employee, another runs CEO → VP → employee. **Ragged**: a member skips a level that its siblings have — a country with no state level between it and its cities. Both defeat the flattened design, where each level is a fixed column, because there is no single correct number of columns. The question analysts actually ask is a rollup: *total the spend for everything under this VP*, where "under" means at any depth. Storing and editing the tree itself is an operational concern with its own patterns; the warehouse problem is different — make that rollup a single, non-recursive join against a fact table. ## The hierarchy bridge The dimensional answer materialises every path in the tree as a row: ```sql CREATE TABLE bridge_org_hierarchy ( ancestor_key BIGINT NOT NULL, descendant_key BIGINT NOT NULL, depth_from_ancestor SMALLINT NOT NULL, is_top_flag CHAR(1) NOT NULL, is_leaf_flag CHAR(1) NOT NULL, PRIMARY KEY (ancestor_key, descendant_key) ); ``` For a tree CEO → VP → Manager → Analyst, the VP contributes four rows: (VP, VP, 0), (VP, Manager, 1), (VP, Analyst, 2), and the CEO contributes rows to all of them at depths 1, 2, 3. The **self row at depth 0** is essential: without it, a rollup for a node excludes that node's own facts, which is a classic off-by-one bug that shows up as a manager's personal expenses vanishing from their department's total. The flags are query conveniences. `is_top_flag` marks pairs whose ancestor is the topmost node, so a whole-enterprise total needs one predicate rather than knowing the root's key. `is_leaf_flag` marks pairs whose descendant has no children, useful when a report should list only the individuals rather than the intermediate managers. ## The rollup query Facts stay joined to their own node — one expense row belongs to exactly one cost centre, and nothing about the tree changes the fact grain: ```sql SELECT SUM(f.amount) FROM fact_expense f JOIN bridge_org_hierarchy b ON b.descendant_key = f.org_key WHERE b.ancestor_key = :selected_org_key; ``` One predicate, whole subtree, any depth, no recursion, and the same query text works for the CEO, a VP, or a single analyst. Add `AND b.depth_from_ancestor = 1` for direct reports only, or group by `b.depth_from_ancestor` to see the shape of the spend by level. Note what does **not** happen: because a fact joins to many bridge rows only when you *don't* pin the ancestor, an unfiltered `fact JOIN bridge` fans out just like a multi-valued bridge — every fact appears once per ancestor above it. That is correct if you are producing a rollup total for every node at once; it is a double-count if you thought you were listing facts. Pin the ancestor, or accept and label the fan-out. ## The padding alternative The simpler option is to keep fixed level columns and **pad**: for a branch that is only three levels deep in a six-level model, repeat the lowest real value into the remaining columns. Every branch now has six columns filled, so the flattened drill path works, and every BI tool understands it without configuration. The cost is phantom members — a report at level five shows a repeated director's name as though it were a real level-five entity — and a hard ceiling: a reorganisation that adds a seventh level requires a schema change. Padding is a reasonable choice for a shallow, stable, mildly ragged tree such as a geography with occasional missing states. It is a poor choice for a real org chart. ## Cost and maintenance A hierarchy bridge holds the sum of every node's path length — roughly *nodes × average depth*, which is far smaller than people expect for a wide, shallow tree and grows uncomfortably for a deep one. It is derived data: it is **rebuilt**, not incrementally patched, whenever the tree changes, because a single re-parenting invalidates every pair on the moved subtree's paths. For most organisations a full rebuild per load is trivial and far safer than trying to patch pairs. ## Reorganisation history The hard part is not the tree, it is the tree's history. Last quarter's report rolled up under last quarter's org chart; rebuilding the bridge restates it under today's. If reproducibility matters, effective-date the bridge rows and constrain queries to a point in time — the dating mechanics belong to the slowly-changing-dimension topic, but the decision is yours to raise here. Many teams deliberately choose the opposite policy — *always report under the current structure* — because managers want their current team's history. Either is defensible; having no stated policy is not, and it is the answer an interviewer is listening for. ## What interviewers listen for That you reach for an ancestor–descendant pair bridge rather than trying to fit a variable tree into level columns, that you include the depth-0 self row and can say why, that you keep the fact joined to its own node, and that you volunteer the reorganisation-history question before being asked.