Cardinality

Understand relationship cardinality types.

Overview

Cardinality defines the relationship type between two tables. It tells the planner how rows in one table relate to rows in another.

Cardinality Types

many_to_one

Most common type. Many rows in left table match one row in right table.

Example: Many orders belong to one customer

orders_customer:
  left: Orders
  right: Customer
  sql: left.customer_id = right.id
  cardinality: many_to_one

Use cases:

• Fact table to dimension table
• Child to parent relationships
• Foreign key relationships

one_to_many

One row in left table matches many rows in right table.

Example: One customer has many orders

customer_orders:
  left: Customer
  right: Orders
  sql: left.id = right.customer_id
  cardinality: one_to_many

Use cases:

• Parent to child relationships
• One-to-many associations
• Reverse of many_to_one

one_to_one

One row in left table matches one row in right table.

Example: One user has one profile

user_profile:
  left: User
  right: User Profile
  sql: left.id = right.user_id
  cardinality: one_to_one

Use cases:

• User profiles
• Settings tables
• Extension tables

many_to_many Not Supported

⚠️ 0sql does not support many_to_many relationships.

Workaround: Create a junction/bridge table and use two relationships:

# Instead of many_to_many:
# Users <-> Roles

# Create:
# Users -> UserRoles -> Roles

users_user_roles:
  left: Users
  right: User Roles
  sql: left.id = right.user_id
  cardinality: one_to_many

user_roles_roles:
  left: User Roles
  right: Roles
  sql: left.role_id = right.id
  cardinality: many_to_one

Choosing Cardinality

Fact to Dimension (many_to_one)

sales_customer:
  left: Sales          # Fact table (many rows)
  right: Customer      # Dimension table (one row per customer)
  sql: left.customer_id = right.id
  cardinality: many_to_one

Dimension to Dimension (one_to_one)

customer_address:
  left: Customer       # One customer
  right: Address       # One address per customer
  sql: left.address_id = right.id
  cardinality: one_to_one

Parent to Child (one_to_many)

customer_orders:
  left: Customer       # One customer
  right: Orders         # Many orders per customer
  sql: left.id = right.customer_id
  cardinality: one_to_many

Measure Expansion

The Double-Counting Problem

When you join a fact table to a dimension through a many_to_one relationship and then to another table, measures might be counted multiple times:

Orders (fact) → Customer → Customer Address
   100 rows       50 rows      50 rows

If you query Total Revenue grouped by Address City, each order amount could be counted once (correct) or multiple times (incorrect) depending on the join path.

Using allow_measure_expansion

Set allow_measure_expansion: true when you’re certain the join path won’t cause double-counting:

# Safe: Customer has exactly one address
customer_address:
  left: Customer
  right: Customer Address
  sql: left.address_id = right.id
  cardinality: many_to_one
  allow_measure_expansion: true  # Measures can flow through this join

When to Use

Example: Safe Expansion

datasource: warehouse

# Orders to Customer: standard fact-to-dimension
orders_customer:
  left: Orders
  right: Customer
  sql: left.customer_id = right.id
  cardinality: many_to_one

# Customer to Address: each customer has one address
# Safe to expand measures through this join
customer_address:
  left: Customer
  right: Customer Address
  sql: left.address_id = right.ca_address_sk
  cardinality: many_to_one
  allow_measure_expansion: true

With this configuration, you can query Total Revenue by Address City and get correct results.

Automatic Behavior

• one_to_one relationships: Measures always expand (bidirectional)
• many_to_one without flag: Only dimensions expand, measures stop
• many_to_one with allow_measure_expansion: true: Measures expand through

Internal Behavior Notes

Best Practices

1. Use many_to_one for fact-to-dimension relationships
2. Match database relationships - Cardinality should reflect actual data
3. Avoid many_to_many - Use junction tables instead
4. Use allow_measure_expansion sparingly - Only when you’re certain it won’t cause double-counting
5. Document relationships - Add comments explaining why expansion is safe

Next Steps

• Join types
• Relationships guide