Bridge Tables & Many-to-Many Dimensions: Modeling Hierarchies and Multi-Valued Attributes

bridge table is the piece of a star schema you reach for the moment a relationship refuses to be many-to-one. A textbook fact row joins to exactly one row in each dimension — one order, one customer, one date. But reality is full of relationships that are many-to-many: a bank account can be owned by several customers, a customer can hold several accounts; a hospital visit can carry several diagnoses; a product can wear several category tags; an employee can sit at any depth beneath a manager in an org chart. A bridge table is the associative table you slot between the two sides so the model can express "many on both ends" without either overwriting data or silently multiplying your numbers.

The reason interviewers keep asking about bridges is that they sit exactly where correctness breaks. Join a fact to a dimension through a bridge naively and a joint account's balance shows up once per owner, so the bank's total balance inflates — the classic double-counting fan trap. This guide walks the four ideas an interviewer will actually probe — the fact-to-dimension many-to-many and where the bridge goes, the weighting or allocation factor that makes shared measures sum correctly, multi-valued attributes and multi-valued dimensions, and hierarchy bridges / closure tables for variable-depth trees like org charts and bills of materials — and pairs each with a Solution-Tail interview answer: SQL, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the bridge-table practice library →, rehearse the join shapes on the many-to-many practice set →, and harden your tree queries on the recursive-hierarchy practice set →.

---

On this page

• Why bridge tables exist — the many-to-many problem
• Bridge tables between a fact and a dimension
• Weighting factors — allocation without double-counting
• Multi-valued attributes & multi-valued dimensions
• Hierarchy bridges & closure tables for ragged trees
• Cheat sheet — bridge & hierarchy recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why bridge tables exist — the many-to-many problem

A star schema assumes many-to-one — a bridge is what you add when both sides are "many"

The one-sentence invariant: a bridge table is an associative table placed between two entities that have a many-to-many relationship, holding one row for each valid pair of keys. Everything about bridges follows from the fact that a normal dimensional join cannot store "many on both ends." A fact table carries a foreign key to each dimension; that foreign key holds exactly one value, so the fact-to-dimension grain is many facts to one dimension member. The instant a single fact (or a single dimension member) legitimately relates to several members on the other side, that single-valued foreign key runs out of room.

Where many-to-many shows up in real models.

• Shared ownership. One bank account, several account holders (a joint account); one insurance policy, several covered members; one property, several owners.
• Multi-valued classification. One patient visit, several diagnoses; one support ticket, several tags; one product, several categories or attributes.
• Ragged hierarchies. One employee reports through a chain of managers of unknown length; one assembly contains sub-assemblies to arbitrary depth (a bill of materials).
• The symmetry that hurts. In every case the relationship is many-to-many both ways — a customer also holds many accounts — so neither side can host the foreign key.

The three wrong answers a bridge replaces.

• Overwrite (lose data). Put a single customer_key on the account and you can record only one owner; the second holder vanishes. Correct total, wrong coverage.
• Grain explosion (duplicate facts). Duplicate the fact once per owner and you have changed the grain of the fact table; every downstream SUM now double-counts unless every query remembers to guard against it.
• Comma-list / repeating groups (unqueryable). Stuff "C1,C2" into one column, or add owner_1, owner_2, owner_3 columns. Both are anti-patterns: you cannot join, filter, or aggregate on them, and the fixed columns cap the cardinality arbitrarily.

What a bridge actually is.

• An associative table at the grain of the relationship. One row per related pair — (account_key, customer_key) — and nothing else except optional attributes of the relationship (a weighting factor, an effective-date range, a role like "primary owner").
• A join hub, not a fact. A bridge usually has no additive measures of its own; it exists to be joined through. Its own grain is "one row per pair," which is exactly what lets you fan a query out and — with care — fan it back in.
• The place correctness is won or lost. Because a query passes through the bridge, the bridge is where you attach the weighting factor that prevents double-counting and the flags that let you pick a rollup level.

What interviewers listen for.

• Do you name the relationship as many-to-many both ways before proposing a table? — the framing that earns trust.
• Do you put the bridge at the grain of the relationship ("one row per account-customer pair"), not at the grain of a fact? — the core idea.
• Do you volunteer the double-counting trap and the weighting factor that fixes it before being asked? — senior signal.
• Do you distinguish a bridge (resolves M:N, no measures) from a many-to-many fact (has its own measures) and a junk dimension (collapses low-cardinality flags)? — vocabulary that separates levels.

Worked example — the moment a single foreign key runs out of room

Detailed explanation. The cleanest way to feel why bridges exist is to try to model a joint account without one and watch the model fail. We have three accounts and three customers; account A3 is a joint checking account owned by two people. A single customer_key on the account dimension can hold only one of them, so the model is forced to either drop an owner or invent repeating columns.

Question. Three accounts (A1, A2, A3) and three customers (C1 Ada, C2 Linus, C3 Grace). A1 is owned by C1 alone, A2 by C2 alone, and A3 jointly by C1 and C2. Show why a single owner_customer_key column on dim_account cannot represent this, and state the grain of the table that can.

Input.

account	type	owners
A1	checking	C1
A2	savings	C2
A3	joint checking	C1, C2

Code.

-- ATTEMPT (broken): one owner column on the account dimension
CREATE TABLE dim_account (
    account_key      INT PRIMARY KEY,
    account_type     TEXT,
    owner_customer_key INT   -- can hold ONE value; A3 has two owners
);

-- The fix: an associative bridge at the grain of the relationship
CREATE TABLE bridge_account_customer (
    account_key   INT NOT NULL,
    customer_key  INT NOT NULL,
    PRIMARY KEY (account_key, customer_key)   -- one row per (account, customer) pair
);

INSERT INTO bridge_account_customer (account_key, customer_key) VALUES
    (1, 1),           -- A1 -> C1
    (2, 2),           -- A2 -> C2
    (3, 1), (3, 2);   -- A3 -> C1 and C2  (the joint account)

Step-by-step explanation. The dim_account.owner_customer_key column is single-valued, so A3 can name only one of its two owners — the model is already lossy before a single query runs. Moving ownership out to bridge_account_customer changes the grain to "one row per account-customer pair," so A3 simply gets two rows. The composite primary key (account_key, customer_key) both enforces that each pair appears once and documents the grain. Nothing about the account or the customer is stored here — only the fact that they are related.

Output.

bridge row	account_key	customer_key	meaning
1	1	1	A1 owned by C1
2	2	2	A2 owned by C2
3	3	1	A3 co-owned by C1
4	3	2	A3 co-owned by C2

Rule of thumb. If a foreign-key column would ever need to hold more than one value, the relationship is many-to-many and belongs in a bridge whose grain is "one row per related pair."

---

2. Bridge tables between a fact and a dimension

The account↔customer bridge — how a fact reaches a many-to-many dimension, and the trap on the way

Once the bridge exists, the interesting question is how a fact measured at one grain (a daily account balance) is reported along a dimension it relates to many-to-many (the customers who own the account). The fact stays at its natural grain — one row per account per day — and the customer is reached by hopping through the bridge. Get the join path right and you can slice balance by customer; get it wrong and the bank's numbers inflate.

The join path, one hop at a time.

• Fact → account. fact_balance carries account_key at its own grain (account × day). This is a clean many-to-one join, exactly as in any star schema.
• Account → bridge. bridge_account_customer explodes each account into one row per owner. This is where a single fact row can fan into several.
• Bridge → customer. The bridge's customer_key reaches dim_customer for names, segments, and other attributes.
• The shape. fact → dim_account → bridge → dim_customer is the canonical four-table path for a fact reported through a many-to-many dimension.

Two places to put the bridge (and why it matters).

• Directly between two dimensions — bridge_account_customer links dim_account and dim_customer. Simple, and the pattern shown here.
• Between a dimension and a "group" — Kimball's multivalued-dimension bridge: the account points at a customer_group_key, and the bridge maps each group to its members. This lets many accounts that share the exact same owner set reuse one group, shrinking the bridge — worth it when groups repeat heavily.
• The rule. Use a direct bridge when pairs are mostly unique; introduce a group key when the same set of members recurs across many parents.

The double-counting trap, stated precisely.

• A fan-out join multiplies the measure. Joining fact_balance through the bridge turns A3's one balance row into two rows (one per owner). SUM(balance) across the exploded result now counts A3's balance twice.
• It is grain, not a bug in SQL. The join is doing exactly what you asked; the result set's grain became "balance per account-owner," so a naive total over it is meaningless.
• When it is actually fine. A per-customer coverage or impact report — "which customers are exposed to this account" — deliberately wants the full value against each owner. The trap only bites when you then sum across customers expecting the bank total.

Worked example — slicing balance by customer through the bridge

*Detailed explanation. * The everyday query is "total balance held by each customer." It reads naturally — join the fact to the account, hop the bridge to the customer, group by customer — and it is correct per customer. The subtlety is only in what happens when you sum those per-customer numbers back up, which the next section fixes with a weight. Here we establish the honest per-customer view and expose the inflated grand total.

Question. With balances A1 = 1000, A2 = 4000, A3 = 600, report total balance per customer by joining fact_balance through the bridge. Then sum those per-customer totals and compare to the true bank total of 5600.

Input.

account_key	balance
1 (A1)	1000
2 (A2)	4000
3 (A3)	600

Code.

SELECT  c.customer_key,
        c.name,
        SUM(f.balance) AS balance_by_customer
FROM        fact_balance          f
JOIN        bridge_account_customer b ON b.account_key = f.account_key
JOIN        dim_customer          c ON c.customer_key = b.customer_key
GROUP BY    c.customer_key, c.name
ORDER BY    c.customer_key;

Step-by-step explanation. The join fact_balance → bridge fans A3's single 600 row into two rows — one for C1, one for C2. C1 therefore accumulates A1 (1000) + A3 (600) = 1600, and C2 accumulates A2 (4000) + A3 (600) = 4600. Both per-customer numbers are legitimate: C1 really does have access to 1600 of balance. But summing the customer column gives 1600 + 4600 = 6200, which overshoots the real 5600 by exactly A3's 600 — the joint balance counted once per owner.

Output.

customer	accounts summed	balance_by_customer
C1 Ada	A1 + A3	1600
C2 Linus	A2 + A3	4600
grand total (naive)	—	6200 ✗ (true = 5600)

Rule of thumb. A fact summed through a bridge is correct along the grouped dimension but not across it — the per-customer numbers are trustworthy, the grand total is inflated by every shared parent.

SQL interview question on a fact-to-dimension bridge

Question. You are handed fact_balance(account_key, balance), bridge_account_customer(account_key, customer_key), and dim_customer(customer_key, name). An analyst wrote SELECT SUM(balance) FROM fact_balance JOIN bridge_account_customer USING(account_key) to get total bank balance and got 6200 instead of 5600. Explain the defect and write a query that returns the correct bank total while still supporting a per-customer breakdown.

Solution Using a pre-aggregated fact to protect the grain

Code.

-- Correct bank total: aggregate the fact at its OWN grain, never through the bridge
SELECT SUM(balance) AS bank_total
FROM   fact_balance;                     -- 1000 + 4000 + 600 = 5600

-- Per-customer breakdown: fan through the bridge on purpose, but label it "exposure"
SELECT  c.name,
        SUM(f.balance) AS customer_exposure   -- intentionally counts shared balance per owner
FROM        fact_balance           f
JOIN        bridge_account_customer b ON b.account_key = f.account_key
JOIN        dim_customer           c ON c.customer_key = b.customer_key
GROUP BY    c.name;

Step-by-step trace.

step	rows	balance seen
1. fact at native grain	A1, A2, A3	1000, 4000, 600
2. SUM without joining bridge	3 rows	5600 (correct total)
3. join bridge (exposure query)	A1→C1, A2→C2, A3→C1, A3→C2	4 rows
4. group by customer	C1=1600, C2=4600	per-customer exposure

1. The bank total must be computed before the bridge fans the fact out; SUM(balance) over fact_balance alone touches each account once, so A3's 600 is counted once → 5600.
2. The moment the bridge joins in, A3 becomes two rows, so any total over that result double-counts by design.
3. The per-customer query keeps the fan-out, but you name the measure customer_exposure so no one mistakes it for an additive, cross-customer total.
4. Two questions, two grains, two queries — the fix is recognizing they are different questions, not forcing one query to answer both.

Output:

result	value	grain
bank_total	5600	fact grain (correct)
exposure C1	1600	per account-owner
exposure C2	4600	per account-owner

Why this works — concept by concept:

• Grain discipline — the fact's additive grain is "one row per account"; any SUM that must equal the true total has to be taken at that grain, before a bridge multiplies rows.
• Fan-out is intentional — joining through the bridge is the right move for a per-customer exposure view; it is only wrong when you then aggregate across the fanned dimension.
• Name the measure by its grain — calling the fanned number customer_exposure rather than balance stops a downstream consumer from summing it into a meaningless total.
• Two questions, two queries — "what is the bank total" and "what is each customer exposed to" have different grains and deserve separate SQL rather than one query patched to fake both.
• Cost — the total is O(fact rows); the exposure query is O(fact rows × avg owners per account), bounded by the bridge fan-out factor.

SQL
Topic — bridge-table
Bridge-table join and grain problems

Practice →

Modeling Topic — many-to-many Many-to-many relationship modeling problems

Practice →

---

3. Weighting factors — allocation without double-counting

An allocation factor makes a shared measure sum correctly — one column that turns a fan trap into a clean total

The fix for the double-counting you just saw is a single extra column on the bridge: a weighting factor (also called an allocation factor) that says what fraction of a parent's measure belongs to each related member. Store weights that sum to 1.0 per parent, multiply the measure by the weight in your aggregate, and the shared value is split instead of duplicated — so a cross-member total lands exactly on the true whole.

The mechanism.

• A weight per bridge row. bridge_account_customer gains a weighting_factor column. For a sole-owner account it is 1.0; for a 50/50 joint account each owner's row carries 0.5.
• The invariant. For any given parent (account), the weights across all its bridge rows sum to exactly 1.0. This is the property that guarantees the allocated total equals the original.
• The corrected expression. Replace SUM(f.balance) with SUM(f.balance * b.weighting_factor). Now A3's 600 contributes 300 to C1 and 300 to C2 — 600 in total, not 1200.
• Where weights come from. Ownership percentages, headcount splits, revenue-share agreements, or a default equal split (1.0 / number_of_owners) when no business rule exists.

Two legitimate reports, two different measures.

• Impact / coverage report (unweighted). Uses the raw measure against every member — "how much balance does each customer have access to." Correct per member; must not be summed across members.
• Allocated report (weighted). Uses measure × weight — "how much of the balance is attributable to each customer." Sums correctly to the whole and is the one finance signs off on.
• Ship both, label both. Interviewers love that you keep them distinct: an impact number and an allocated number answer different business questions and both are valid.

Traps around weighting.

• Weights that don't sum to 1.0. If a parent's weights sum to 0.9 or 1.1, your allocated total drifts below or above the truth; enforce the invariant with a validation query or a CHECK on a materialized rollup.
• Weighting a non-additive measure. Allocation is meaningful for additive measures (balance, revenue). A weighted average of a ratio (e.g. interest rate) needs a weighted-average formula, not a straight measure × weight sum.
• Missing bridge rows. An account with no bridge rows drops out of every through-the-bridge query entirely; guard with an outer join or a data-quality check for orphan accounts.

Worked example — the same query, corrected by a weight

Detailed explanation. We take the exact per-customer query from section 2 and add * b.weighting_factor. A3's 600 now splits 300/300 across its two owners, so the per-customer numbers change and — crucially — their sum collapses onto the real 5600. This is the whole trick: one column, one multiplication.

Question. Add a weighting_factor to the bridge (sole accounts 1.0; A3 split 0.5/0.5) and report allocated balance per customer. Confirm the allocated total equals the true bank total of 5600.

Input.

account_key	customer_key	weighting_factor
1 (A1)	1 (C1)	1.0
2 (A2)	2 (C2)	1.0
3 (A3)	1 (C1)	0.5
3 (A3)	2 (C2)	0.5

Code.

SELECT  c.name,
        SUM(f.balance * b.weighting_factor) AS allocated_balance
FROM        fact_balance            f
JOIN        bridge_account_customer  b ON b.account_key = f.account_key
JOIN        dim_customer             c ON c.customer_key = b.customer_key
GROUP BY    c.name
ORDER BY    c.name;

Step-by-step explanation. For C1: A1 contributes 1000 × 1.0 = 1000 and A3 contributes 600 × 0.5 = 300, so C1 = 1300. For C2: A2 contributes 4000 × 1.0 = 4000 and A3 contributes 600 × 0.5 = 300, so C2 = 4300. The joint account's 600 is now split rather than duplicated, so 1300 + 4300 = 5600 — exactly the bank total. The only change from the section-2 query is the * b.weighting_factor factor inside the SUM.

Output.

customer	allocated_balance	contribution from A3
C1 Ada	1300	300
C2 Linus	4300	300
allocated total	5600 ✓	600 (split, not doubled)

Rule of thumb. When weights on a bridge sum to 1.0 per parent, SUM(measure × weight) is safe to total across the many-to-many dimension; without the weight, it never is.

SQL interview question on allocation

Question. Marketing wants "attributed revenue per customer" from fact_revenue(account_key, revenue) through the account↔customer bridge, and the CFO insists the per-customer numbers must add up to total revenue. Some accounts are joint. Write the query, and describe how you would validate that the weighting factors are trustworthy.

Solution Using weighted allocation with a weight-integrity check

Code.

-- Attributed revenue that sums to the whole
SELECT  c.customer_key,
        c.name,
        SUM(f.revenue * b.weighting_factor) AS attributed_revenue
FROM        fact_revenue            f
JOIN        bridge_account_customer  b ON b.account_key = f.account_key
JOIN        dim_customer             c ON c.customer_key = b.customer_key
GROUP BY    c.customer_key, c.name;

-- Weight-integrity check: every account's weights must sum to 1.0
SELECT  account_key,
        SUM(weighting_factor) AS weight_sum
FROM        bridge_account_customer
GROUP BY    account_key
HAVING      ABS(SUM(weighting_factor) - 1.0) > 0.0001;   -- rows here are BAD

Step-by-step trace.

account	owners	weights	revenue	contribution split
A1	C1	1.0	1000	C1 += 1000
A2	C2	1.0	4000	C2 += 4000
A3	C1, C2	0.5, 0.5	600	C1 += 300, C2 += 300

1. The allocation query multiplies each account's revenue by the owner's weighting_factor, so a joint account's revenue is divided among owners instead of replicated.
2. Because weights per account sum to 1.0, the sum of all attributed_revenue equals SUM(revenue) over the fact — the CFO's invariant holds by construction.
3. The integrity check groups the bridge by account_key and flags any account whose weights do not sum to 1.0 (within a tiny epsilon for floating point); an empty result means the weights are trustworthy.
4. Run the check as a data-quality test in CI or the pipeline so a bad weight fails the build rather than silently skewing attributed revenue.

Output:

result	value
attributed C1	1300
attributed C2	4300
sum attributed	5600 (= total revenue)
integrity check rows	0 (all accounts sum to 1.0)

Why this works — concept by concept:

• Weighting factor — a per-relationship fraction stored on the bridge; multiplying the measure by it converts duplication into division.
• Sum-to-one invariant — enforcing that each parent's weights total 1.0 is precisely what makes the allocated grand total equal the un-allocated one.
• Allocation vs impact — weighted gives attributable revenue that adds up; unweighted gives exposure per member; naming them apart prevents a wrong total.
• Weight integrity as a test — a HAVING SUM(weight) <> 1.0 query turns a subtle correctness property into an automatable data-quality gate.
• Cost — one extra multiply per bridged row, O(fact rows × fan-out); the integrity check is O(bridge rows) and runs once per load, not per query.

Allocation
Topic — allocation-factor
Allocation-factor and weighted-rollup problems

Practice →

SQL Topic — bridge-table Bridge-table double-counting problems

Practice →

---

4. Multi-valued attributes & multi-valued dimensions

One member, many values — a multi-valued dimension attaches several attribute values through a bridge

The other half of the bridge story is the multi-valued dimension: a single dimension member that legitimately carries several values of one attribute. A customer belongs to several marketing segments; a patient visit carries several diagnoses; a product wears several category tags; a job posting lists several required skills. The wrong instinct is a comma-separated string or a fixed set of segment_1..segment_n columns; the right pattern is a bridge from the member to a value dimension.

The shape of a multi-valued dimension.

• Member → bridge → value dimension. dim_customer → bridge_customer_segment → dim_segment. The bridge holds one row per (customer_key, segment_key) pair — the same associative grain as before, now between a dimension and its multi-valued attribute.
• Optional group dimension. When many members share the exact same set of values (thousands of customers all tagged {premium, digital}), insert a segment_group between the member and the bridge so identical sets are stored once. The customer points at a segment_group_key; the bridge maps group → segments.
• Optional weight. Just like ownership, a multi-valued attribute can carry a weight (primary vs secondary diagnosis, a confidence score), reusing the allocation machinery from section 3.

Querying a multi-valued dimension correctly.

• Filtering is easy and safe. "Customers in the premium segment" is a straightforward join dim_customer → bridge → dim_segment WHERE segment = 'premium'; filtering does not double-count.
• Counting members needs COUNT(DISTINCT). "How many customers per segment" fans a customer into one row per segment; counting customers across all segments must use COUNT(DISTINCT customer_key) or you count a two-segment customer twice.
• Summing a member's measure needs a weight. If you attach a customer-level measure (lifetime value) and report it by segment, you are back in double-counting territory — allocate with a weight exactly as in section 3.

Multi-valued attribute vs the alternatives.

• vs comma-list. A 'premium,digital' string can't be joined, indexed, or filtered without fragile string matching; a bridge makes every value a first-class, queryable row.
• vs pivoted columns. is_premium, is_digital boolean columns work only for a small, fixed, rarely-changing value set; a new segment means a schema change. A bridge scales to any number of values with no DDL.
• vs a junk dimension. A junk dimension collapses several low-cardinality, unrelated flags into one row; it is not for a single attribute with many values that relate a member to a growing list. Different tool, different problem.

Worked example — customers with several segments

Detailed explanation. We tag customers with marketing segments through a bridge. C1 Ada is {premium, digital}, C2 Linus is {retail}, C3 Grace is {premium, retail}. The example shows that filtering by segment is safe, but counting customers across segments needs COUNT(DISTINCT) because a multi-segment customer appears in several bridge rows.

Question. Given the segment tags above, (a) list customers in the premium segment, and (b) count distinct customers per segment. Show why a plain COUNT(*) on a cross-segment total would over-count.

Input.

customer_key	segment_key
1 (C1)	premium
1 (C1)	digital
2 (C2)	retail
3 (C3)	premium
3 (C3)	retail

Code.

-- (a) customers in the 'premium' segment  (filtering is safe)
SELECT  c.name
FROM        dim_customer          c
JOIN        bridge_customer_segment b ON b.customer_key = c.customer_key
JOIN        dim_segment           s ON s.segment_key = b.segment_key
WHERE       s.segment_key = 'premium';

-- (b) distinct customers per segment  (COUNT DISTINCT guards the fan-out)
SELECT  s.segment_key,
        COUNT(DISTINCT b.customer_key) AS customers
FROM        bridge_customer_segment b
JOIN        dim_segment           s ON s.segment_key = b.segment_key
GROUP BY    s.segment_key;

Step-by-step explanation. Query (a) filters the bridge to premium rows and returns C1 and C3 — filtering never duplicates because we are selecting members, not aggregating a measure across the fan-out. Query (b) groups by segment; premium has two rows (C1, C3), retail has two (C2, C3), digital has one (C1). COUNT(DISTINCT customer_key) counts members correctly within each segment. If you instead summed those segment counts to get "total customers" you would get 2 + 2 + 1 = 5 for only 3 real customers — because C1 and C3 each sit in two segments — which is why a cross-segment headcount must COUNT(DISTINCT customer_key) over the whole set, not add the per-segment counts.

Output.

segment	customers	members
premium	2	C1, C3
digital	1	C1
retail	2	C2, C3

Rule of thumb. Filtering a multi-valued dimension is free; counting members across it always needs COUNT(DISTINCT), and summing a member measure across it always needs a weight.

SQL interview question on a multi-valued dimension

Question. Each product has one or more category tags via bridge_product_category, and fact_sales(product_key, revenue) records sales at the product grain. A PM asks for "revenue by category." Some products have several categories. Give the report that (a) shows revenue exposure per category and (b) also produces category revenue that sums to total sales, and explain the difference.

Solution Using COUNT(DISTINCT) plus weighted category allocation

Code.

-- (a) exposure: full product revenue against every category it belongs to
SELECT  cat.category_name,
        SUM(f.revenue) AS revenue_exposure     -- sums > total sales if products are multi-category
FROM        fact_sales            f
JOIN        bridge_product_category b ON b.product_key = f.product_key
JOIN        dim_category          cat ON cat.category_key = b.category_key
GROUP BY    cat.category_name;

-- (b) allocated: split each product's revenue evenly across its categories
WITH cat_counts AS (
    SELECT product_key, COUNT(*) AS n_categories
    FROM   bridge_product_category
    GROUP  BY product_key
)
SELECT  cat.category_name,
        SUM(f.revenue * 1.0 / cc.n_categories) AS allocated_revenue
FROM        fact_sales            f
JOIN        bridge_product_category b ON b.product_key = f.product_key
JOIN        cat_counts            cc ON cc.product_key = f.product_key
JOIN        dim_category          cat ON cat.category_key = b.category_key
GROUP BY    cat.category_name;

Step-by-step trace.

product	categories	revenue	exposure adds	allocated adds
P1	electronics	1000	electronics += 1000	electronics += 1000
P2	electronics, home	600	each += 600	each += 300

1. The exposure query (a) sums full product revenue against every category, so P2's 600 lands in both electronics and home — great for "which categories touch this revenue," but the category column now totals more than sales.
2. The allocated query (b) derives n_categories per product in a CTE, then divides revenue by that count — an equal-split weighting factor computed on the fly (1.0 / n_categories).
3. Because each product's split weights sum to 1.0, the allocated category revenue sums back to total sales exactly, satisfying "must add up."
4. The two answers are both correct for different questions: exposure measures reach, allocation measures attributable revenue.

Output:

category	revenue_exposure (a)	allocated_revenue (b)
electronics	1600	1300
home	600	300
column total	2200 (> 1600 sales)	1600 (= total sales)

Why this works — concept by concept:

• Multi-valued dimension — modeling category as a bridge, not a column, lets a product carry any number of categories and keeps every one queryable.
• Exposure vs allocation — exposure keeps the full measure per value (reach); allocation splits it (attribution); each answers a real, distinct question.
• On-the-fly weight — 1.0 / n_categories is a default equal-split weighting factor derived when the bridge has no stored weight, reusing the section-3 machinery.
• DISTINCT for headcounts — whenever the deliverable is a count of members rather than a measure, COUNT(DISTINCT member_key) is the fan-out guard.
• Cost — the CTE is O(bridge rows) to compute counts; the allocation join is O(fact rows × avg categories per product).

Modeling
Topic — many-to-many
Multi-valued dimension modeling problems

Practice →

SQL Topic — bridge-table Bridge-table aggregation problems

Practice →

---

5. Hierarchy bridges & closure tables for ragged trees

A closure table stores every ancestor-descendant pair — one join rolls up a fact at any level of a ragged tree

The hardest many-to-many is a member relating to itself at unknown depth: an org chart, a bill of materials, a category tree. An adjacency list (employee.manager_key) captures each parent-child edge but can't be flattened by a fixed number of joins because the depth is variable — a ragged hierarchy. The bridge that solves this is a closure table (Kimball calls it a hierarchy bridge): one row for every ancestor-descendant pair, including each node to itself, tagged with the depth between them.

What a closure table holds.

• One row per ancestor→descendant pair. For every node, a row to each of its descendants at any depth, plus a depth-0 self row. E1's rows reach E1 (0), E2 (1), E3 (1), E4 (2), E5 (2).
• A depth column. depth (a.k.a. distance) is the number of edges between ancestor and descendant; the self row has depth 0. It lets you query "direct reports only" (depth = 1) or "everyone below" (depth >= 1).
• Optional flags. is_leaf, is_root, or Kimball's lowest_flag mark where a node sits, which is handy for stopping double-counts when a fact can attach at multiple levels.
• No measures. Like every bridge, it stores structure, not facts; you join facts through it.

Building and using it.

• Build with a recursive CTE. Walk the adjacency list from every node downward, accumulating depth; the anchor emits self rows at depth 0 and the recursive member extends each path by one edge.
• Roll up a subtree with one join. To total a measure for "everyone under E2," join the fact on descendant and filter ancestor = E2. One join replaces an unbounded chain of self-joins.
• Direct vs full rollup by depth. WHERE ancestor = X AND depth = 1 gives direct children; dropping the depth filter (or depth >= 0) gives the whole subtree including X itself.

Traps in hierarchy rollups.

• Double-counting across ancestors. Every node has multiple ancestors (itself, its manager, its manager's manager). If you join a fact to the closure table and forget to fix a single ancestor, each fact row is counted once per ancestor — a massive over-count. Always filter or group by one ancestor level.
• Facts attached at inner nodes. If measures live only at leaves, a plain descendant rollup is clean; if inner nodes also carry measures (a manager has personal sales), the depth-0 self rows correctly include them — which is exactly why the self row exists.
• Cycles. A malformed hierarchy with a cycle makes the recursive CTE loop; guard with a depth cap or a visited-path check, and validate that the source is a true tree/DAG.

Worked example — building a closure table from an org chart

Detailed explanation. We have a five-person org: E1 (CEO) manages E2 and E3; E2 manages E4 and E5. The adjacency list stores only the direct manager_key. A recursive CTE expands it into a closure table of every ancestor-descendant pair with depth, which is the structure we then roll facts up through.

Question. From dim_employee(employee_key, manager_key) with edges E1→E2, E1→E3, E2→E4, E2→E5, build the closure table of (ancestor, descendant, depth) including depth-0 self rows.

Input.

employee_key	manager_key
E1	(none)
E2	E1
E3	E1
E4	E2
E5	E2

Code.

WITH RECURSIVE closure AS (
    -- anchor: every node is its own ancestor at depth 0
    SELECT  employee_key AS ancestor,
            employee_key AS descendant,
            0            AS depth
    FROM    dim_employee

    UNION ALL

    -- recursive: extend each known pair down one edge
    SELECT  c.ancestor,
            e.employee_key AS descendant,
            c.depth + 1    AS depth
    FROM        closure       c
    JOIN        dim_employee  e ON e.manager_key = c.descendant
)
SELECT ancestor, descendant, depth
FROM   closure
ORDER  BY ancestor, depth, descendant;

Step-by-step explanation. The anchor emits five self rows at depth 0 (E1→E1, …, E5→E5). The recursive member joins each existing pair's descendant to employees who report to it, emitting a new pair with depth + 1: from E1→E1 it finds E2 and E3 (depth 1), then from E1→E2 it finds E4 and E5 (depth 2), and independently E2→E2 yields E2→E4 and E2→E5 (depth 1). Recursion stops when no node reports to the current descendants (leaves E3, E4, E5). The result is every ancestor-descendant pair with its depth.

Output.

ancestor	descendant	depth
E1	E1	0
E1	E2	1
E1	E3	1
E1	E4	2
E1	E5	2
E2	E2	0
E2	E4	1
E2	E5	1
E3	E3	0
E4	E4	0
E5	E5	0

Rule of thumb. A closure table trades storage (roughly nodes × average depth rows) for O(1)-join rollups at any level — build it once per hierarchy load, query it cheaply forever.

SQL interview question on hierarchy rollup

Question. Using the closure table above and fact_sales(employee_key, sales) with E1=0, E2=100, E3=200, E4=50, E5=70, return total sales for the entire subtree under a given manager (including the manager's own sales). Show the result for E2, and explain how you avoid counting a person's sales once per ancestor.

Solution Using a descendant join filtered to one ancestor

Code.

-- Subtree rollup: everyone at or below the chosen manager
SELECT  cl.ancestor      AS manager_key,
        SUM(f.sales)     AS subtree_sales
FROM        closure     cl
JOIN        fact_sales   f ON f.employee_key = cl.descendant
WHERE       cl.ancestor = 'E2'          -- one ancestor => no cross-ancestor double count
GROUP BY    cl.ancestor;

-- Direct reports only would add:  AND cl.depth = 1

Step-by-step trace.

closure row (ancestor=E2)	descendant	sales joined
E2 → E2 (depth 0)	E2	100 (manager's own)
E2 → E4 (depth 1)	E4	50
E2 → E5 (depth 1)	E5	70

1. Filtering ancestor = 'E2' selects exactly the rows whose descendants form E2's subtree: E2 (self, depth 0), E4, and E5.
2. Joining fact_sales on descendant attaches each person's sales to precisely one row, because within a single ancestor each descendant appears once.
3. SUM(f.sales) totals 100 + 50 + 70 = 220 — the manager's own sales plus every report at any depth.
4. The double-count trap is avoided by fixing a single ancestor; had we joined the fact to the whole closure table and grouped by employee, each person's sales would be added once per ancestor above them (E4's 50 would count under E4, E2, and E1). Pinning one ancestor per query is the discipline.

Output:

manager	subtree_sales	includes
E2	220	E2 (100) + E4 (50) + E5 (70)

Why this works — concept by concept:

• Closure table — precomputing every ancestor-descendant pair turns a variable-depth walk into a flat join, so any subtree is reachable in one hop.
• Depth column — depth = 1 isolates direct reports, depth >= 0 takes the full subtree; one table answers both without restructuring.
• Self rows — the depth-0 row is what folds a manager's own measure into the subtree total, so inner-node facts are not lost.
• One ancestor per query — fixing a single ancestor is the guard that stops a fact from being counted once per level of the tree above it.
• Cost — rollup is O(descendants of the chosen node), a single indexed join; the build is O(nodes × average depth) done once per load, not per query.

Hierarchy
Topic — recursive-hierarchy
Recursive-hierarchy and org-chart rollup problems

Practice →

Modeling Topic — closure-table Closure-table and ragged-hierarchy problems

Practice →

---

Cheat sheet — bridge & hierarchy recipes

Bridge table DDL (grain = one row per pair).

CREATE TABLE bridge_account_customer (
    account_key      INT NOT NULL,
    customer_key     INT NOT NULL,
    weighting_factor NUMERIC(5,4) NOT NULL DEFAULT 1.0,
    PRIMARY KEY (account_key, customer_key)
);

Weighted rollup (sums correctly across the M:N dimension).

SELECT c.name, SUM(f.balance * b.weighting_factor) AS allocated
FROM   fact_balance f
JOIN   bridge_account_customer b ON b.account_key = f.account_key
JOIN   dim_customer c            ON c.customer_key = b.customer_key
GROUP  BY c.name;

Weight-integrity check (each parent's weights sum to 1.0).

SELECT account_key, SUM(weighting_factor) AS w
FROM   bridge_account_customer
GROUP  BY account_key
HAVING ABS(SUM(weighting_factor) - 1.0) > 0.0001;   -- rows = bad

Multi-valued dimension count (guard the fan-out).

SELECT s.segment_key, COUNT(DISTINCT b.customer_key) AS customers
FROM   bridge_customer_segment b
JOIN   dim_segment s ON s.segment_key = b.segment_key
GROUP  BY s.segment_key;

Closure table build (recursive CTE over an adjacency list).

WITH RECURSIVE closure AS (
    SELECT employee_key AS ancestor, employee_key AS descendant, 0 AS depth
    FROM   dim_employee
    UNION ALL
    SELECT c.ancestor, e.employee_key, c.depth + 1
    FROM   closure c
    JOIN   dim_employee e ON e.manager_key = c.descendant
)
SELECT * FROM closure;

Subtree rollup (one join, one fixed ancestor).

SELECT SUM(f.sales) AS subtree_sales
FROM   closure cl
JOIN   fact_sales f ON f.employee_key = cl.descendant
WHERE  cl.ancestor = :manager_key;      -- add AND cl.depth = 1 for direct reports

Pattern picker.

Situation	Pattern
Fact relates M:N to a dimension	Bridge between fact's dim and the other dim
Shared measure must sum to the whole	Bridge + weighting_factor (sums to 1.0)
One member, many attribute values	Multi-valued dimension bridge
Same value set reused by many members	Add a group dimension
Variable-depth tree (org / BOM)	Closure table + recursive CTE
Count members across a bridge	COUNT(DISTINCT member_key)

---

Frequently asked questions

What is a bridge table in dimensional modeling?

A bridge table is an associative table placed between two entities that have a many-to-many relationship, holding one row for each valid pair of keys — for example one row per (account_key, customer_key) when accounts and customers own each other many-to-many. It stores structure, not measures: you join through it to reach a dimension a fact relates to on both ends. Optional columns on the bridge describe the relationship itself, such as a weighting factor or an effective-date range.

How do you avoid double-counting through a bridge table?

Double-counting happens because joining a fact through a bridge fans one fact row into several, so a plain SUM counts a shared value once per related member. Fix it by either aggregating the fact at its own grain before the bridge (for a true grand total), or by adding a weighting factor that sums to 1.0 per parent and summing measure × weight. For headcounts rather than measures, use COUNT(DISTINCT member_key) so a member in several groups is counted once.

What is a weighting (allocation) factor?

A weighting or allocation factor is a fraction stored on each bridge row that says what share of a parent's measure belongs to that related member. The weights for any one parent must sum to exactly 1.0 — a 50/50 joint account gives each owner 0.5 — so that SUM(measure × weight) across the many-to-many dimension lands on the true total instead of a duplicated one. It lets you produce an "allocated" report that adds up alongside an unweighted "impact" report that measures reach.

What is a multi-valued dimension?

A multi-valued dimension is a dimension member that carries several values of one attribute — a customer in several marketing segments, a visit with several diagnoses, a product with several category tags. You model it with a bridge from the member to a value dimension (dim_customer → bridge_customer_segment → dim_segment) instead of a comma-separated string or fixed attr_1..attr_n columns. Filtering on it is safe; counting members across it needs COUNT(DISTINCT), and summing a member measure across it needs a weight.

What is a closure table and when do I use one?

A closure table (or hierarchy bridge) stores one row for every ancestor-descendant pair in a tree, including each node to itself at depth 0, tagged with the depth between them. Use it for variable-depth, ragged hierarchies — org charts, bills of materials, category trees — where an adjacency list can't be flattened by a fixed number of joins. You build it once with a recursive CTE, then roll a fact up for any subtree with a single join filtered to one ancestor, avoiding an unbounded chain of self-joins.

Bridge table vs junk dimension vs many-to-many fact — how do I tell them apart?

A bridge resolves a many-to-many relationship and carries no measures; you join through it. A junk dimension collapses several low-cardinality, unrelated flags (yes/no indicators, small enums) into one dimension to avoid littering the fact with columns — it is not about many-to-many. A many-to-many fact (a factless or transaction fact) records the relationship as an event and may carry its own additive measures. Reach for a bridge when you need to attach a dimension that relates many-to-many; reach for a factless fact when the relationship itself is the thing you are measuring or counting.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every idea above, from the account-customer bridge and the weighting factor that stops double-counting to the multi-valued dimension and the closure-table subtree rollup, maps to a hands-on practice room where you write the SQL against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how do you model a many-to-many between a fact and a dimension without double-counting?" holds up under a senior interviewer's depth probes.

Practice bridge-table problems now →
Many-to-many modeling drills →