Everything so far in this series has been one-to-many, and cleanly so: one order to many order lines, one account to many months, one order to many shipments. Every fact row had exactly one of each dimension it referenced. That assumption holds until it doesn't — a single insurance claim can carry three diagnosis codes, a bank account can have two joint owners, a sales transaction can be attributed to more than one promotion at once. None of those are edge cases to shrug off; they're a genuinely different relationship shape, and modeling them like a one-to-many relationship produces numbers that are quietly, confidently wrong.
This is the pattern Ralph Kimball himself used a healthcare example to introduce, for good reason — it's where the problem is most obvious and the fix is most instructive.
The 60-second recap
- Fact tables hold events. Transaction, periodic snapshot, accumulating snapshot (updated in place across milestones), factless (no measures — row existence is the fact).
- Grain = the exact definition of one fact row, stated as a sentence, before anything else. Different real questions at different grains get different fact tables.
-
SCD Type 2 preserves history with
valid_from/valid_to/is_currentrows, so a fact always joins to the dimension row that was true when the event happened. - Part 3 added late-arriving fact handling and named semi-additive measures — summable across some dimensions, not across time.
New for this article: dimensions that are legitimately multivalued for a single fact row, and the specific trap that shows up the moment you try to sum a dollar amount through one.
Meet Meadowlark Health
Meadowlark Health processes insurance claims. The detail that makes this article necessary: a single claim routinely carries more than one diagnosis code (ICD-10 codes — E11.9 for Type 2 diabetes, I10 for hypertension, and so on), because patients frequently present with more than one condition at a visit. The fact table Meadowlark needs is fact_claim_line — one row per billed service line on a claim — and every one of those rows can legitimately be about two or three diagnoses at once, not one.
That's the whole problem in one sentence: the fact-to-diagnosis relationship is many-to-many, and a standard dimensional model has no native way to express that.
The fixed-width hack, again
The instinctive fix looks exactly like the one flagged as a mistake in the fulfillment article, just wearing a different column name:
-- Don't do this
CREATE TABLE fact_claim_line (
claim_line_sk BIGSERIAL PRIMARY KEY,
claim_id TEXT,
member_sk BIGINT REFERENCES dim_member(member_sk),
provider_sk BIGINT REFERENCES dim_provider(provider_sk),
service_date_sk INT REFERENCES dim_date(date_sk),
diagnosis_1_sk INT REFERENCES dim_diagnosis(diagnosis_sk),
diagnosis_2_sk INT REFERENCES dim_diagnosis(diagnosis_sk),
diagnosis_3_sk INT REFERENCES dim_diagnosis(diagnosis_sk),
billed_amount NUMERIC(10,2)
);
This breaks for the same structural reason shipment_2_carrier did in the last article: it's a fixed guess at a variable-length list. A claim with a fourth diagnosis has nowhere to go. WHERE diagnosis_1_sk = :code OR diagnosis_2_sk = :code OR diagnosis_3_sk = :code has to be repeated at every column, forever, and gets silently wrong the day someone adds a diagnosis_4_sk and forgets to update every query that predates it.
The bridge table
Kimball's own name for the fix, when he first wrote it up using this exact example, was the Diagnosis Group table — what's generally called a bridge table today. Instead of the fact row pointing at diagnosis codes directly, it points at a group, and the group resolves to however many diagnoses actually apply:
CREATE TABLE fact_claim_line (
claim_line_sk BIGSERIAL PRIMARY KEY,
claim_id TEXT,
member_sk BIGINT REFERENCES dim_member(member_sk),
provider_sk BIGINT REFERENCES dim_provider(provider_sk),
service_date_sk INT REFERENCES dim_date(date_sk),
diagnosis_group_sk INT NOT NULL REFERENCES bridge_diagnosis_group(diagnosis_group_sk),
billed_amount NUMERIC(10,2)
);
CREATE TABLE bridge_diagnosis_group (
diagnosis_group_sk INT NOT NULL,
diagnosis_sk INT NOT NULL REFERENCES dim_diagnosis(diagnosis_sk),
weighting_factor NUMERIC(4,3) NOT NULL, -- fractions of this claim attributable to this code, summing to 1.0 per group
PRIMARY KEY (diagnosis_group_sk, diagnosis_sk)
);
One diagnosis_group_sk can resolve to one row in the bridge (a claim with a single diagnosis) or several (a claim with three). Adding a fourth diagnosis to a future claim needs nothing more than another bridge row with the same group key — no schema change, no new column, no query that has to be found and patched.
Why the weighting factor exists: you cannot just join and sum
Here's the trap the bridge table alone doesn't save you from. Say claim line CL_1001 has billed_amount = 500.00 and three diagnoses in its group. Join the fact straight to the bridge and group by diagnosis:
-- Wrong: naive join, no weighting
SELECT d.diagnosis_code, SUM(f.billed_amount) AS total_billed
FROM fact_claim_line f
JOIN bridge_diagnosis_group b ON b.diagnosis_group_sk = f.diagnosis_group_sk
JOIN dim_diagnosis d ON d.diagnosis_sk = b.diagnosis_sk
WHERE f.claim_line_sk = :cl_1001
GROUP BY d.diagnosis_code;
-- E11.9: $500.00
-- I10: $500.00
-- Z79.4: $500.00 <- $1,500 conjured out of a $500 claim
The join fans one fact row out into three rows — one per bridge match — and billed_amount comes along unchanged on each of them. Sum across diagnoses and you've invented a thousand dollars that never existed. This is the exact failure mode every source on this pattern warns about, and it's not a hypothetical: it's what happens the first time anyone builds this query without knowing the bridge is there.
The weighting factor exists to fix exactly this — multiply, don't just sum:
-- Correct: multiply by the weighting factor before summing
SELECT d.diagnosis_code, SUM(f.billed_amount * b.weighting_factor) AS allocated_billed
FROM fact_claim_line f
JOIN bridge_diagnosis_group b ON b.diagnosis_group_sk = f.diagnosis_group_sk
JOIN dim_diagnosis d ON d.diagnosis_sk = b.diagnosis_sk
WHERE f.claim_line_sk = :cl_1001
GROUP BY d.diagnosis_code;
-- E11.9: $250.00 (0.5 * 500)
-- I10: $150.00 (0.3 * 500)
-- Z79.4: $100.00 (0.2 * 500)
Now the three rows sum back to exactly $500. As long as every group's weighting factors sum to 1.0, this generalizes cleanly to SUM(billed_amount * weighting_factor) across every claim at once, grouped however you like by diagnosis attributes — the allocation math holds up in aggregate, not just for one claim examined by hand.
Where the weighting factor breaks
This is worth stating plainly rather than leaving implicit, because it's the part that isn't obvious until it bites: the weighting factor correctly answers "how much is attributable to diagnosis X," but it does not correctly answer "how much is attributable to claims with both diagnosis X and diagnosis Y." Ask for the combined total across two specific codes together, and the weighted sum double-counts any claim that carries both — because each code's allocated share was computed independently, not jointly. There's no clean fix inside the weighting-factor pattern itself for that specific question; it requires a different query shape (typically: find the claims meeting both conditions first via EXISTS/INTERSECT, then sum their full billed_amount once, unweighted). If your organization asks combination-of-codes questions often, that's a sign the weighting factor alone won't cover everything you need, not that it's implemented wrong.
One more legitimate variant worth knowing: you can deliberately drop the weighting factor if what you actually want is an impact report — "total billed amount touched by any contagious diagnosis," where a claim with two contagious codes intentionally counting twice reflects "this much billing activity involved a contagious condition" rather than "this much money was caused by it." That's a real, valid report shape. It just has to be labeled as one, clearly, so nobody downstream mistakes an intentionally inflated total for a financial figure.
Not every bridge needs a weighting factor
Meadowlark also has group health plans where one policy covers multiple dependents — another genuine many-to-many, member-to-policy this time instead of claim-to-diagnosis. But nobody's summing a dollar amount across dependents the way they sum billed amount across diagnoses; the question is usually just "who's covered under this policy," a membership list, not an allocation.
CREATE TABLE bridge_policy_member (
policy_sk BIGINT NOT NULL REFERENCES dim_policy(policy_sk),
member_sk BIGINT NOT NULL REFERENCES dim_member(member_sk),
relationship TEXT NOT NULL -- SUBSCRIBER / SPOUSE / DEPENDENT
-- no weighting_factor: nothing numeric fans out through this bridge
);
The distinction worth keeping straight: a weighting factor is only needed when a numeric measure from the fact table would otherwise double-count as it fans out through the bridge. A bridge used purely to list or filter membership — no fact-table measure passing through it — doesn't need one. Don't add a weighting factor out of habit; add it when there's a SUM() that would otherwise lie.
Kimball's own bank-account example follows the same membership shape as the policy/dependent case — multiple customers jointly owning one account — and it comes with a related wrinkle worth flagging: a bridge table often needs to sit on top of Type 2 dimensions on both sides. If a dependent is added to a policy mid-year, or a diagnosis code's own description gets revised by a coding-standard update, the bridge row needs to point at whichever dimension row (policy_sk, diagnosis_sk) was actually valid on the date the relationship applied — the same "as-of" logic from the SCD2 sections of Parts 1 and 2, just one join further away.
Factless facts, revisited: a bridge without any fact at all
Two earlier articles used factless facts for coverage/eligibility — Bean & Stalk's drink availability, Tabby's feature entitlements. Meadowlark has a version that's also genuinely many-to-many: which providers are in-network for which plan, as of which date. A provider can be in-network for several plans; a plan covers several providers. No dollar amount is attached to the relationship itself — it either holds on a given day or it doesn't:
CREATE TABLE fact_network_coverage (
coverage_sk BIGSERIAL PRIMARY KEY,
provider_sk BIGINT NOT NULL REFERENCES dim_provider(provider_sk),
plan_sk BIGINT NOT NULL REFERENCES dim_plan(plan_sk),
effective_date_sk INT NOT NULL REFERENCES dim_date(date_sk),
UNIQUE (provider_sk, plan_sk, effective_date_sk)
-- factless: the row's existence is the fact
);
This isn't a bridge table in the strict Kimball sense — it's a factless fact that happens to resolve a many-to-many relationship on its own, because neither side needs to be the fact table's single grain-defining dimension. Worth noticing that "bridge table" and "factless fact" are answers to two different questions — how do I represent a multivalued dimension attached to a fact versus how do I record that something was true without a number attached — and they can combine, as they do here, without either one being a special case of the other.
A real query
Total billed amount by diagnosis category, correctly allocated, for claims in the last quarter:
SELECT
d.diagnosis_category,
SUM(f.billed_amount * b.weighting_factor) AS allocated_billed
FROM fact_claim_line f
JOIN bridge_diagnosis_group b ON b.diagnosis_group_sk = f.diagnosis_group_sk
JOIN dim_diagnosis d ON d.diagnosis_sk = b.diagnosis_sk
JOIN dim_date dt ON dt.date_sk = f.service_date_sk
WHERE dt.quarter = 3 AND dt.year = 2026
GROUP BY d.diagnosis_category
ORDER BY allocated_billed DESC;
Members covered under a given policy, as of today (membership bridge, no allocation needed):
SELECT m.member_name, bp.relationship
FROM bridge_policy_member bp
JOIN dim_member m ON m.member_sk = bp.member_sk
WHERE bp.policy_sk = :policy_sk;
Notice the second query never touches a weighting factor at all — a plain join is correct here, because nothing numeric is fanning out. That contrast is the whole lesson of this article in two queries.
Common mistakes
Joining fact to bridge and summing without multiplying by the weighting factor. The single most common way this pattern gets implemented wrong — the join looks completely correct, the query runs without error, and the total is simply too large. Nothing about the SQL signals the bug; only the number does.
Adding a weighting factor to a bridge that doesn't need one. A pure membership bridge with a
weighting_factorcolumn invites someone to multiply by it out of habit, which — if it doesn't sum to something meaningful per group — introduces a new bug in the other direction.Asking a combination-of-codes question against a weighted single-code answer. Covered above: "billed for X" and "billed for X and Y together" are different questions, and the weighting factor only correctly answers the first one.
Forgetting the fixed-width hack is the same mistake as
shipment_2_carrier. Any time a schema has_1,_2,_3suffixed columns for "as many as we've seen so far," that's a bridge table that hasn't been built yet.Pointing a bridge at a Type 1 dimension when history matters. If policy membership or diagnosis descriptions change over time and the bridge points at the current row regardless of when the relationship applied, historical reports quietly use today's data for yesterday's events — the same SCD1-where-you-needed-SCD2 mistake from every earlier article in this series, one hop further away.
Exercises
Hints hidden; full solutions in solutions.sql in the companion repo.
1. A claim line has a single diagnosis. Does it still need a diagnosis_group_sk pointing at the bridge table, or can it point directly at dim_diagnosis?
Hint
Consider what happens to every downstream query if some claim lines use one pattern and others use a different one. A single-diagnosis claim can be modeled as a group of size one — same bridge, same query shape, weighting_factor = 1.0. Consistency usually wins over the minor storage savings of a special case.
2. Write the query for the "impact report" described above: total billed amount touched by any claim carrying a diagnosis in the "contagious" category, intentionally double-counting claims with more than one such diagnosis. Label the output so it can't be mistaken for the allocated total.
Hint
Join fact to bridge to dim_diagnosis, filter to the contagious category, and sum billed_amount unweighted — same shape as the first "wrong" query in this article, except this time the double-counting is the intended output, not a bug. Alias the column something like impact_billed_amount_do_not_reconcile_to_gl to make the intent unmissable.
3. Write the query that correctly answers "total billed for claims carrying both E11.9 and I10," avoiding the weighting-factor trap described in this article.
Hint
Find the set of diagnosis_group_sk values present in the bridge for both codes (an INTERSECT or a self-join with a HAVING COUNT(DISTINCT diagnosis_sk) = 2), then sum the fact table's billed_amount unweighted for claim lines in that set — once per claim, not once per diagnosis.
What's next
Every pattern so far — transaction facts, periodic snapshots, accumulating snapshots, factless facts, bridge tables — has had a textbook-correct answer once you knew which tool fit. Part 5, the last in this series, doesn't. It's a deliberately unresolved case: three stakeholders, three defensible grains, three different numbers, and no answer in any book. That's where the actual judgment this series has been building toward gets tested.
Resources
- Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — the original source for the Diagnosis Group / weighting-factor pattern used throughout this article.
- Kimball Group — Multivalued Dimensions and Bridge Tables — the canonical short reference, including the Type 2 dimension interaction noted above.
- Kimball Group — Design Tip #166: Potential Bridge Table Detours — the usability and over-counting caveats covered in this article, from the source.
Top comments (0)