Why Direct Many-to-Many Joins Can Duplicate Facts and Corrupt Metrics

Learning outcomes

AtlasMart wants sales by household. A customer can legitimately belong to more than one household at the same business time, so joining the order-line fact directly through that relationship produces multiple rows for one measurement event. The database has not “made a mistake”; the query has crossed a many-to-many relationship without defining what repeated measures mean. This lesson makes that multiplication visible before introducing the repair.

1. Start from the unchanged atomic fact

The accepted sales fact grain is still one row per paid order line. Household membership is a separate relationship; it must not be pushed into the fact by cloning the sales row once per household because that would change the physical row population and invite accidental double counting.

The household bridge grain is one customer-to-household membership for one effective interval. Those two grains are compatible for an as-of relationship, but they are not the same grain.

2. Minimal bridge DDL and why the keys matter

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS bridge_category_ancestor;DROP TABLE IF EXISTS dim_category;DROP TABLE IF EXISTS bridge_customer_household;DROP TABLE IF EXISTS dim_household;DROP TABLE IF EXISTS fact_sales_history;CREATE TABLE fact_sales_history (  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  event_date TEXT NOT NULL,  durable_customer_id TEXT NOT NULL,  product_id TEXT NOT NULL,  quantity INTEGER NOT NULL,  extended_amount NUMERIC NOT NULL,  extended_cost NUMERIC NOT NULL,  PRIMARY KEY(order_id,line_no));CREATE TABLE dim_household (  household_sk INTEGER PRIMARY KEY,  household_code TEXT NOT NULL UNIQUE,  household_name TEXT NOT NULL,  household_type TEXT NOT NULL);CREATE TABLE bridge_customer_household (  durable_customer_id TEXT NOT NULL,  household_sk INTEGER NOT NULL REFERENCES dim_household(household_sk),  effective_from TEXT NOT NULL,  effective_to TEXT NOT NULL,  allocation_weight NUMERIC NOT NULL CHECK(allocation_weight > 0 AND allocation_weight <= 1),  membership_role TEXT NOT NULL,  CHECK(effective_from < effective_to),  PRIMARY KEY(durable_customer_id, household_sk, effective_from));CREATE INDEX ix_bridge_customer_time  ON bridge_customer_household(durable_customer_id,effective_from,effective_to);CREATE TABLE dim_category (  category_sk INTEGER PRIMARY KEY,  category_code TEXT NOT NULL UNIQUE,  category_name TEXT NOT NULL);CREATE TABLE bridge_category_ancestor (  descendant_category_sk INTEGER NOT NULL REFERENCES dim_category(category_sk),  ancestor_category_sk INTEGER NOT NULL REFERENCES dim_category(category_sk),  distance INTEGER NOT NULL CHECK(distance >= 0),  PRIMARY KEY(descendant_category_sk,ancestor_category_sk));

The fact keeps the durable customer identifier only because this chapter is isolating bridge mechanics from the customer SCD join already established in Chapter 07. In a production dimensional model, the chosen durable/history keys must follow the conformance contract. The bridge carries the multivalued membership and its temporal/allocation semantics; it does not own the sales measure.

3. Reproduce the bug: seven fact rows become eleven joined rows

SELECT f.order_id,f.line_no,f.event_date,f.durable_customer_id,       h.household_code,b.allocation_weight,f.extended_amountFROM fact_sales_history AS fJOIN bridge_customer_household AS b  ON b.durable_customer_id = f.durable_customer_id AND b.effective_from <= f.event_date AND f.event_date < b.effective_toJOIN dim_household AS h ON h.household_sk=b.household_sk-- Deliberately wrong for additive dollars:SELECT COUNT(*) AS joined_rows,       SUM(f.extended_amount) AS wrong_gmvFROM fact_sales_history fJOIN bridge_customer_household b  ON b.durable_customer_id=f.durable_customer_id AND b.effective_from <= f.event_date AND f.event_date < b.effective_to;

Expected evidence from the deterministic fixture is joined_rows = 11 and wrong_gmv = 900, even though the base control is seven rows and 625. O1001's two lines each match two household memberships, and O1005's two lines also match two memberships. Multiplication is therefore observable in row cardinality before it is visible in the money total.

4. Why DISTINCT is not a universal rescue

SUM(DISTINCT extended_amount) deduplicates equal numeric values, not duplicate business events. AtlasMart legitimately has multiple different order lines with the same extended amount (for example, 100 and 50 appear on more than one line). On this fixture, summing the distinct amount values returns 375 rather than 625. A SQL keyword cannot infer which repeated values are duplicates versus separate sales.

SELECT SUM(DISTINCT extended_amount) AS wrong_distinct_gmvFROM fact_sales_history;-- Expected in this fixture: 375, not 625.

5. Repair: allocate only when the business owns the rule

For the teaching question “allocate paid GMV across simultaneous households,” AtlasMart declares weights that sum to 1.0 for each customer membership interval. The query multiplies each atomic amount by the relationship weight, then groups. This creates an allocated analytical measure; it does not claim the customer literally paid fractional dollars from each household.

SELECT h.household_code,       ROUND(SUM(f.extended_amount*b.allocation_weight),2) AS allocated_gmvFROM fact_sales_history AS fJOIN bridge_customer_household AS b  ON b.durable_customer_id=f.durable_customer_id AND b.effective_from <= f.event_date AND f.event_date < b.effective_toJOIN dim_household AS h ON h.household_sk=b.household_skGROUP BY h.household_codeORDER BY h.household_code

The allocated household totals sum to 625. Reconciliation proves conservation of the base measure under the declared allocation policy. It does not prove that 60/40 is the right business policy—that requires an owner and definition.

6. Presence questions need different SQL

If the question is “which households participated in orders?” allocation may be irrelevant. Use distinct fact identifiers or pre-aggregated event counts according to the declared grain. Conversely, if the question is “how much GMV should each household receive credit for?”, a weighting policy is required. Mixing those intents is how bridge tables turn into hidden semantic bugs.

Knowledge check

Summary and next step

A bridge preserves legitimate multivalued relationships without changing the fact grain, but every query crossing it must state whether it is counting participation or allocating measures. The next lesson generalizes this into direct bridges, group bridges, memberships, and hierarchy bridges.

Authoritative references

• Kimball Group — Multivalued Dimensions and Bridge Tables — Primary reference for legitimate multivalued dimensional relationships, group keys, and effective dating when membership changes over time.
• Kimball Group — Design Tip #142: Building Bridges — Implementation-oriented discussion of bridge patterns for multivalued dimensions.
• Kimball Group — Design Tip #166: Potential Bridge Table Detours — Explains bridge usability tradeoffs and the over-counting risk when measures cross a multivalued relationship without allocation.
• Kimball Group — Ragged/Variable Depth Hierarchies — Reference for hierarchy bridge/closure-style modeling when fixed-depth attributes are insufficient.
• Kimball Group — The 10 Essential Rules of Dimensional Modeling — Reinforces keeping the fact grain intact and resolving legitimate multivalued relationships with a bridge instead of corrupting the measurement event.
• SQLite — CREATE TABLE — Constraint semantics used by the local deterministic bridge lab.
• SQLite — Foreign Key Support — Local referential-integrity behavior used by the bridge/member fixture.