Chapter 09 · Bridge Tables and Many-to-Many Analytical Relationships
Weighting/Allocation Factors and When Allocated Measures Are Business Rules, Not Technical Tricks
Treat allocation weights as governed business semantics, compare correct 60/40 attribution with a technically convenient 50/50 assumption, and reconcile allocated measures to atomic evidence.
Learning outcomes
A weighting column can make a duplicated total reconcile, but that does not make the weight “correct.” AtlasMart therefore treats allocation as a metric-definition decision with an owner, scope, effective interval, denominator, and reconciliation test. This lesson contrasts the approved 60/40 historical rule with a technically convenient equal split that conserves dollars while changing business meaning.
Explain allocation weight as a business rule rather than a database optimization.
Validate normalization of weights independently for each effective membership set.
Compare weighted sums, unweighted sums, and equal-split assumptions on the same atomic facts.
Keep allocated metrics distinguishable from original atomic measures.
Define ownership, rounding, null/missing-member, and restatement policy for production use.
Chapter 09 starts from the accepted Chapter 08 state. Canonical paid sales remain seven order-line facts, four paid orders, nine units, 625 paid GMV, 380 cost-at-sale, and 245 gross profit. Customer history keeps Chapter 07's half-open business-effective intervals; Chapter 08's date-role, junk, degenerate, mini-dimension, and inferred-member patterns remain unchanged. Chapter 09 adds a synthetic customer-to-household many-to-many relationship solely to teach bridge semantics. The bridge does not alter the grain of fact_sales_history, does not rewrite customer surrogate keys, and does not redefine the accepted sales measures.
The mandatory lab uses Python's standard-library
sqlite3 module and synthetic local data. Record
the actual Python and SQLite versions before execution.
Allocation weights, household membership, household types, and
effective dates are explicit AtlasMart teaching contracts, not
universal business rules. The local fixture proves relational
and arithmetic semantics in one process; it does not prove
distributed concurrency, cloud optimizer behavior, CDC
ordering, production privacy policy, or performance at
warehouse scale.
Conservation is necessary for an allocated additive metric but not sufficient for semantic correctness. Two different weight schemes can both sum to the same 625 and still answer different business questions.
1. Write the metric contract before SQL
| Field | AtlasMart teaching contract |
|---|---|
| Metric | allocated_paid_gmv_by_household |
| Atomic source | fact_sales_history.extended_amount |
| Fact grain | one paid order line |
| Relationship | customer ↔ household as of event_date |
| Allocation rule | use governed membership weights valid at event time |
| Normalization | weights sum to 1.0 per customer membership interval |
| Rounding | round only presentation output; reconcile unrounded arithmetic |
| Owner | synthetic Revenue Analytics steward |
| Control | allocated total = atomic paid GMV = 625 |
The allocated metric is a derived interpretation of atomic GMV. Keep the atomic amount available so future policy changes can be restated without reconstructing source evidence from rounded allocations.
2. Validate weights as data, not comments
SELECT durable_customer_id,effective_from,effective_to, SUM(allocation_weight) AS weight_sumFROM bridge_customer_householdGROUP BY durable_customer_id,effective_from,effective_toHAVING ABS(SUM(allocation_weight)-1.0) > 0.000001;-- Expected: zero rows.This test is necessary only for the AtlasMart allocation contract. Some legitimate bridges are non-allocating membership sets and should not be forced to sum to 1.0. The rule follows the metric semantics, not the table name.
3. Controlled counterexample: “equal split is objective”
Before 2026-09-19, D-CUST-001 is governed 60% H-FAMILY-A and 40% H-BUSINESS-A. Replacing those values with 50/50 still conserves the 125 GMV from O1001, but shifts 12.50 from family to business attribution. The total looks perfect while the business definition is wrong.
| Rule | H-FAMILY-A from O1001 | H-BUSINESS-A from O1001 | Total |
|---|---|---|---|
| Approved 60/40 | 75.00 | 50.00 | 125.00 |
| Convenient 50/50 | 62.50 | 62.50 | 125.00 |
Therefore a reconciliation test must be paired with definition/version tests. “It adds up” catches arithmetic loss/gain; it does not certify policy.
4. Numerator and denominator lineage
Allocation weights should be reproducible from a governed source or explicit stewardship decision. If a 60/40 value came from contribution percentage, account ownership, contract share, or modeled attribution, record that provenance. Do not silently derive it from unrelated technical characteristics such as row counts or ingestion order.
If the allocation itself is a ratio, store the reproducible numerator/denominator when practical rather than only a rounded percentage. That supports audit, precision, and future recomputation.
5. Rounding and numerical stability
At large scale, allocating currency at row level can produce fractional cents. Define whether rounding occurs per atomic fact, per household rollup, or only in presentation; those choices can produce different totals. For this integer-dollar fixture, no fractional-cent problem appears, but the course contract still recommends reconciling at full engine precision and rounding only the published result unless finance policy requires otherwise.
6. Missing membership is not a zero weight
If a fact has no valid bridge row, silently dropping it makes allocated totals appear smaller. Assigning a fake weight to an arbitrary household hides data quality. The safe options are policy-dependent: quarantine, an explicit Unknown/Unallocated member, or a controlled residual bucket. Whatever the choice, publish the unresolved amount and keep reconciliation visible.
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| Household | Historical allocated GMV |
|---|---|
| H-BUSINESS-A | 50.00 |
| H-FAMILY-A | 225.00 |
| H-FAMILY-B | 200.00 |
| H-FAMILY-C | 75.00 |
| H-SHARED-C | 75.00 |
| Control total | 625.00 |
Knowledge check
Check your understanding
- Why is a 50/50 split not automatically neutral?
- What two validations should accompany an allocated metric?
- Should every bridge weight sum to 1?
- Why keep atomic GMV after publishing allocated GMV?
- What should happen to sales with no valid membership?
Review the answers
1. It is still a business attribution rule and can shift credit even when the grand total remains unchanged.
2. Arithmetic reconciliation to atomic controls and semantic/version validation of the allocation rule.
3. No. Only bridges used for a normalized allocation contract require that invariant.
4. It preserves replay/restatement capability when allocation policy changes.
5. Follow an explicit policy such as quarantine or unallocated/unknown handling and expose the unresolved amount rather than silently dropping it.
Summary and next step
Weights are governed metric semantics, not a cleanup trick. The next lesson adds the temporal dimension: which memberships and weights were valid when each fact happened?
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.