Why does a bridge-table join double-count fact measures, and how does a weighting factor fix it?

## The arithmetic of the fan-out Joining through a bridge is a one-to-many join, and a one-to-many join duplicates the left-hand row. Take one order line of $300 credited to a group of three sales reps: ```text fact_order_line: line_key=1 rep_group_key=77 amount=300.00 bridge_rep_group: (77, rep=Ann) (77, rep=Bob) (77, rep=Cara) join result: line 1 / Ann / 300.00 line 1 / Bob / 300.00 line 1 / Cara / 300.00 ``` `SUM(amount)` over that result is $900. Nothing is broken — the join did exactly what a relational join does — but the number is not revenue. This is the single most common defect in a model that uses bridges, and it usually surfaces as "the rep report totals more than the company total". ## Two legitimate answers, not one The fix is not always to divide. There are two distinct, correct report shapes and the business question decides which you want. **Unallocated, or impact, reporting.** Each member gets the *full* measure. "Ann touched $300 of revenue, Bob touched $300, Cara touched $300" is a true and useful statement about influence, workload or exposure — a hospital analyst genuinely wants "total charges for visits involving diabetes", not "one fifth of the charges". The rule is that this column is correct **per member** and meaningless **across members**: summing it is what produces the $900. **Allocated reporting.** Each member gets a share. The bridge row carries a `weighting_factor`, the factors within a group sum to exactly 1.0, and the query multiplies: ```sql SELECT r.rep_name, SUM(f.amount * b.weighting_factor) AS allocated_amount FROM fact_order_line f JOIN bridge_rep_group b ON b.rep_group_key = f.rep_group_key JOIN dim_rep r ON r.rep_key = b.rep_key GROUP BY r.rep_name; ``` Now Ann, Bob and Cara get $100 each and the column adds to $300 — it re-totals to the company figure and can be safely rolled up to region, year or anything else. ## Where the weights come from Three sources, in increasing order of usefulness and of political difficulty. **Equal split** — 1/n for a group of n — is the default and needs no business input. **Source-supplied shares** — an ownership percentage on an account, a commission split recorded in the CRM — are the best case, because the business already decided. **Modelled shares** — a diagnosis-severity weighting, a marketing attribution curve — are a business rule that someone must own, sign off and version; the warehouse should implement it, not invent it. Whatever the source, three constraints hold. Store the factor in an exact decimal type, not a float, so the sums are reproducible. Enforce non-negativity. And test the invariant continuously: ```sql SELECT rep_group_key, SUM(weighting_factor) AS total_weight FROM bridge_rep_group GROUP BY rep_group_key HAVING SUM(weighting_factor) <> 1.0; ``` Any row this returns is a group whose allocated measures will not re-total. Equal splits over 3 members are the classic offender if you round 0.333333 three times and lose a cent — either use enough decimal places for the tolerance you accept, or assign the rounding remainder deterministically to one member. ## Not every question needs the bridge The cheapest defence is to notice that a report asking only for a total does not need the members at all. `SELECT SUM(amount) FROM fact_order_line` is exactly right and cannot fan out, because the bridge is not in the query. A model that publishes the bridge should also publish the plain fact-grain totals, so analysts are not tempted to reach through the bridge for a number that never depended on it. ## Counts and distinct counts Measures are not the only thing that inflates. `COUNT(*)` over a bridged join counts rows, not events — three for one order line. If the question is "how many deals did Ann work on", `COUNT(DISTINCT f.line_key)` is the right expression, and it is correct without weighting, because a deal is either hers or not. Weighting applies to shareable quantities such as amounts, not to event counts, and applying a weight to a count produces a fractional deal that means nothing. ## Making it safe for consumers Deciding correctly and then leaving the choice implicit is a half-fix. In a published mart, expose the two shapes as **separately named columns or measures** — `revenue_allocated` and `revenue_impact`, never both called `revenue` — and document that only the allocated one is additive across the bridged dimension. Where a semantic layer supports it, define the measure once with the weighting built in, so the multiply is not something each analyst must remember. Most bridge-related incidents are not modelling errors; they are naming errors that let someone sum a column that was never meant to be summed. ## What interviewers listen for That you explain the fan-out as ordinary join behaviour rather than a bug, that you name both the impact and the allocated shape instead of assuming allocation is always right, that you state the sum-to-one invariant and how you test it, and that you mention distinct counts.