Relationships and Grain
The two concepts behind every modeling bug
Relationships (1:*, 1:1, many-to-many) define how filters flow; grain defines what a row means. Most modeling bugs are a grain or a relationship-direction mistake.
What you will learn
- Classify relationship cardinalities and their filter semantics
- Diagnose grain violations that distort aggregates
- Introduce bridge tables for many-to-many
What is this?
Every model is animated by relationships — cardinality (1:1, 1:*, *:*) and filter direction — plus a grain contract on each table. Filters flow from the one-side (dimension) to the many-side (fact), which is what makes a slicer answer a question. Break the grain or point a relationship wrong and every downstream number silently lies.
Why do you need it?
- Counts double through many-to-many paths; revenue overstates.
- Grain mistakes are the #1 cause of 'the dashboards disagree' escalations.
- Understanding cardinality predicts what a filter can and cannot do.
- A bridge table is the standard pattern for legit many-to-many.
Mental model
Family trees and census rows
Relationships and grain
A census row = one person (grain). A family tree says who's whose parent (relationships). Ask 'how many children live in this house' — if you join the family tree greedily, you count aunts twice. The tree's links and the census's rows must both be right; same with models.
How it works
- 1:* — a dimension row relates to many fact rows; filters flow dim→fact.
- 1:1 — one-to-one; used rarely, often for attribute splitting.
- *:* — many rows to many rows; requires a bridge table to behave.
- Filter direction: dim→fact is the norm; bidirectional adds cycles to watch.
- Grain: uniqueness per key is the guarantee; test with COUNT vs DISTINCT.
Cardinality and filter flow.
Examples
Fixing double counts with a bridge
The trap: fact_orders.cust_id → dim_customer.cust_id (1:*) Each order = 1 customer, good. The many-to-many case (products × campaigns): fact_orders ↔ fact_campaigns (many:many via campaign_id) -> double-counts. Fix with a bridge: dim_bridge_campaign_order (campaign_id, order_id) + 1:* relationships from the bridge to each fact. Slicing by campaign filters exactly the orders in it.A bridge table turns a many-to-many into two clean 1:* relationships, so counts stay honest.
In production
- An ecommerce model uses a bridge to slice sales by marketing campaigns attended to.
- A portfolio model bridges securities to accounts to avoid double-counting holdings.
- Role-playing dates use separate 1:* relationships with USERELATIONSHIP to resolve ambiguity.
Best practices
- Declare grain + cardinality on every table and test uniqueness.
- Default to one-way (dim→fact) filtering except proven bidirectional needs.
- Use bridge tables for every legitimate many-to-many.
- Add relationship-design to model reviews — it's the highest-leverage outcome.
Common mistakes
- Configuring 1:* where the data is actually *:* ('to make it work').
- Bidirectional filtering everywhere, creating ambiguity and slowness.
- A fact with duplicated key rows (grain drift) inflating counts.
- Joining dims to dims, spawning fan-out traps.
Debugging tips
- Counts too high → suspect a *:* path; test with DISTINCT keys per side.
- Slicer filters only one chart → relationship missing or wrong direction.
- Agreement failure between two reports → same grain? same bridge?
Interview checkpoint
Hands-on lab
- Recreate the one-to-many filter flow
- Reproduce and fix the fan-out trap with a bridge
- Validate grain with uniqueness test
Prove Relationship-and-Grain Bugs in SQL
45 min · intermediate
Quick quiz
Which cardinality requires a bridge table to avoid double counting?
A filter on a dimension flows to all related fact rows automatically.