Validate Many-to-Many Queries with Control Totals to Prove No Double Counting

Learning outcomes

A bridge is safe only when its invariants are executable. AtlasMart therefore tests the base fact grain, joined cardinality, normalized weights, historical allocation distribution, grand-total reconciliation, temporal coverage, overlap absence, and negative cases. This acceptance suite is designed to fail loudly when a bridge load or BI query silently changes semantics.

1. Acceptance matrix

2. Executable acceptance script

import sqlite3# build_conn() seeds the deterministic Chapter 09 fixture.conn = build_conn()assert base_controls(conn) == (7,4,9,625,380,245)joined = conn.execute("""SELECT COUNT(*),SUM(f.extended_amount)FROM 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""").fetchone()assert joined == (11,900), joinedassert conn.execute("SELECT SUM(DISTINCT extended_amount) FROM fact_sales_history").fetchone()[0] == 375assert weight_interval_audit(conn) == []assert overlap_audit(conn) == []assert allocation_rows(conn) == EXPECTED_ALLOCassert round(sum(v for _,v in allocation_rows(conn)),2) == 625.00missing = conn.execute("""SELECT COUNT(*)FROM fact_sales_history fLEFT JOIN 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_toWHERE b.household_sk IS NULL""").fetchone()[0]assert missing == 0# Negative test 1: remove Ben's membership; coverage must fail and expose 200 GMV.bad = build_conn()bad.execute("DELETE FROM bridge_customer_household WHERE durable_customer_id='D-CUST-002'")miss = bad.execute("""SELECT COUNT(*),SUM(f.extended_amount)FROM fact_sales_history fLEFT JOIN 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_toWHERE b.household_sk IS NULL""").fetchone()assert miss == (1,200), miss# Negative test 2: corrupt a normalized weight set.bad2 = build_conn()bad2.execute("""UPDATE bridge_customer_householdSET allocation_weight=0.50WHERE durable_customer_id='D-CUST-001'  AND household_sk=1 AND effective_from='2026-01-01'""")assert weight_interval_audit(bad2) != []# Negative test 3: current membership can preserve 625 but distort history.current = conn.execute("""WITH current_bridge AS (  SELECT * FROM bridge_customer_household  WHERE effective_from <= '2026-09-20' AND '2026-09-20' < effective_to)SELECT h.household_code,ROUND(SUM(f.extended_amount*b.allocation_weight),2)FROM fact_sales_history fJOIN current_bridge b ON b.durable_customer_id=f.durable_customer_idJOIN dim_household h ON h.household_sk=b.household_skGROUP BY h.household_code ORDER BY h.household_code""").fetchall()assert round(sum(v for _,v in current),2) == 625.00assert current != EXPECTED_ALLOCprint('base_controls=', base_controls(conn))print('joined_counterexample=', joined)print('historical_allocation=', allocation_rows(conn))print('Chapter 09 acceptance: PASS')

Expected final line: Chapter 09 acceptance: PASS. The negative cases matter as much as the happy path: the suite proves it can detect missing coverage and broken normalization, and it demonstrates a subtle case where a wrong current-membership replay still conserves the grand total.

3. Production observability and lineage

Monitor relationship-row counts, weight-normalization failures, missing-membership amount, bridge overlap violations, allocated-vs-atomic reconciliation delta, and the age/version of membership data. Lineage should connect the source membership decision to bridge rows, allocation definition, transformed metric, and consuming report. A 0.00 reconciliation delta without bridge version metadata is not enough for audit.

4. Performance without sacrificing correctness

Bridge joins can expand rows. Index the relationship lookup columns appropriate to the engine and measure representative workloads, but never “optimize” by removing the temporal predicate or weight multiplication unless the business question changes. Pre-aggregated or semantic-layer views can simplify BI, provided their grain, freshness, and reconciliation contracts remain explicit.

This SQLite lab is intentionally tiny and in-memory; it provides no warehouse-scale latency or optimizer claim. Any production benchmark must disclose engine, data size, relationship cardinality distribution, indexes/partitioning, cache state, query corpus, and concurrency.

5. Change and rollback contract

A bridge policy change can restate historical allocated metrics without changing any atomic fact. Treat that as a semantic migration: version the rule, identify affected intervals/facts, calculate before/after controls, validate downstream reports, communicate ownership, and retain a rollback path. If the new bridge load fails coverage or normalization, keep the prior published version rather than partially exposing mixed semantics.

6. Cleanup/reset

The mandatory fixture is in-memory SQLite; closing the process discards it, and build_conn() restores the deterministic accepted state. No cloud account, production data, credentials, or destructive external cleanup is required.

Knowledge check

Summary and next step

Chapter 09 now has a measurable bridge contract: relationship expansion is visible, allocation is governed, temporal history is reconstructed as-of event time, and control totals prove conservation without confusing conservation with semantic correctness. Chapter 10 next separates transaction, periodic snapshot, and accumulating snapshot fact designs.

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.