Chapter 09 · Bridge Tables and Many-to-Many Analytical Relationships
Temporal Membership in Bridges, Effective Dating, and Historical Reconstruction
Effective-date bridge membership with half-open intervals so historical facts use the relationship that was true at event time rather than today’s current membership.
Learning outcomes
Household membership changes independently of sales. If AtlasMart joins every historical fact to the memberships that are current today, the overall 625 can still reconcile while historical credit moves between households. Temporal correctness therefore needs more than a final control total: it needs as-of membership reconstruction at the event's business time.
Use half-open effective intervals to resolve bridge membership deterministically at event time.
Explain why current-state joins can preserve a grand total while corrupting historical distribution.
Detect overlapping relationship windows and missing temporal coverage.
Distinguish business-effective time from load time and restatement time.
Document the effect of backdated membership corrections on downstream allocated metrics.
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.
An as-of bridge lookup and an SCD as-of dimension lookup solve related temporal problems but are separate relationships. A fact can resolve the correct customer version and still use the wrong household membership if the bridge is joined only to current rows.
1. Half-open intervals remove boundary ambiguity
AtlasMart uses
effective_from <= event_date AND event_date <
effective_to. D-CUST-001's 60/40 memberships end at 2026-09-19, and the
100% family membership begins at 2026-09-19. Therefore O1001 on
September 18 uses 60/40, while O1003 on September 19 uses the
new 100% relationship. No fact at the boundary can match both
versions if the windows are non-overlapping.
| Customer | Effective interval | Household(s) | Weights |
|---|---|---|---|
| D-CUST-001 | 2026-01-01 ≤ t < 2026-09-19 | H-FAMILY-A; H-BUSINESS-A | 0.60; 0.40 |
| D-CUST-001 | 2026-09-19 ≤ t | H-FAMILY-A | 1.00 |
| D-CUST-002 | 2026-01-01 ≤ t | H-FAMILY-B | 1.00 |
| D-CUST-004 | 2026-01-01 ≤ t | H-FAMILY-C; H-SHARED-C | 0.50; 0.50 |
2. Historical as-of join
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 |
3. Controlled failure: join all history to current memberships
If “current” means memberships valid on 2026-09-20, D-CUST-001 has only H-FAMILY-A. Reapplying that current relationship to O1001 moves its historical 50 of business-household attribution into family attribution. The grand total remains 625 because current weights still normalize to 1.0; only the distribution is wrong.
| Household | Correct historical | Wrong current-membership replay |
|---|---|---|
| H-BUSINESS-A | 50 | 0 |
| H-FAMILY-A | 225 | 275 |
| H-FAMILY-B | 200 | 200 |
| H-FAMILY-C | 75 | 75 |
| H-SHARED-C | 75 | 75 |
| Grand total | 625 | 625 |
This is why temporal acceptance needs expected grouped results or historical samples, not only a total-sum assertion.
4. Detect overlapping bridge windows
SELECT a.durable_customer_id,a.household_sk, a.effective_from,a.effective_to, b.effective_from,b.effective_toFROM bridge_customer_household aJOIN bridge_customer_household b ON a.durable_customer_id=b.durable_customer_id AND a.household_sk=b.household_sk AND a.rowid < b.rowid AND a.effective_from < b.effective_to AND b.effective_from < a.effective_to;-- Expected: zero rows for the accepted fixture.An overlap on the same relationship member creates two simultaneously valid versions and can multiply even before considering multiple households. A database CHECK constraint can validate each row's start/end order, but cross-row non-overlap usually needs load logic, exclusion constraints where supported, or explicit tests.
5. Backdated corrections and restatement policy
Suppose stewardship later learns that the 60/40 split should have become 70/30 on September 10. That is not merely a current-state update; it changes the relationship that historical facts should use after September 10. AtlasMart must decide whether published historical allocations are restated, versioned, or frozen. Record the correction effective time, ingestion time, approver, affected fact window, and reconciliation result.
Do not use load timestamp as the business effective time unless the business contract explicitly says “membership becomes effective when the warehouse learns it.” Chapter 07 made the same distinction for customer SCD history; bridges require the same discipline.
6. Coverage audit: every allocatable fact must resolve
SELECT f.order_id,f.line_no,f.durable_customer_id,f.event_date,f.extended_amountFROM 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;-- Expected: zero rows for this allocatable fixture.If the business allows customers outside any household, then zero rows is not the right invariant. In that case define whether the metric excludes, quarantines, or routes those amounts to an explicit unallocated member.
Knowledge check
Check your understanding
- Why can a current-membership replay still reconcile to 625?
- What predicate implements AtlasMart half-open membership intervals?
- Why is a row-level CHECK insufficient to prevent temporal overlap?
- What does a backdated membership correction potentially require?
- What should a coverage audit prove?
Review the answers
1. The current weights still sum to one, so no money is lost, but historical attribution is shifted between households.
2. effective_from <= event_date AND event_date < effective_to.
3. It can validate one row’s start/end order but cannot generally compare the interval against other rows for the same relationship.
4. A governed restatement/version policy, impact analysis, replay of affected facts, and reconciliation.
5. That each fact requiring allocation resolves to the relationship set mandated by the metric contract, or is explicitly handled by the missing-membership policy.
Summary and next step
Temporal bridges reconstruct relationships as they were at business time. The final lesson turns all chapter invariants into one executable acceptance suite.
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.