Relationships

Define relationships between models for automatic joining.

Runnable Examples (Executed in CI)

These examples run as part of the docs test suite. They use a small in-memory DuckDB dataset.

Doctest fixture (models/semantic_layer.yml)

models:
  - name: customers
    sql: |
      select * from (values
        (1, 'Alice Johnson', 'North'),
        (2, 'Bob Smith', 'South'),
        (3, 'Carol Davis', 'North')
      ) as t(id, name, region)
    primary_key: id
    dimensions:
      - name: region
        type: categorical
        sql: region
    relationships:
      - name: orders
        type: one_to_many
        foreign_key: customer_id

  - name: orders
    sql: |
      select * from (values
        (1, 1, 120.00, 'completed'),
        (2, 2, 80.00,  'completed'),
        (3, 1, 50.00,  'pending'),
        (4, 3, 200.00, 'completed')
      ) as t(id, customer_id, amount, status)
    primary_key: id
    dimensions:
      - name: status
        type: categorical
        sql: status
    metrics:
      - name: revenue
        agg: sum
        sql: amount
    relationships:
      - name: customers
        type: many_to_one
        foreign_key: customer_id

Query From Either Side (Bidirectional)

From orders to customers:

uvx sidemantic query "SELECT customers.region, orders.revenue FROM orders ORDER BY customers.region" --models ./models

region,revenue
North,370.00
South,80.00

From customers to orders:

uvx sidemantic query "SELECT customers.region, orders.revenue FROM customers ORDER BY customers.region" --models ./models

region,revenue
North,370.00
South,80.00

Filter Through a Relationship

uvx sidemantic query "SELECT orders.revenue FROM orders WHERE customers.region = 'North'" --models ./models

revenue
370.00

Relationship Types

many_to_one

Many records in THIS model → one record in OTHER model:

models:
  - name: orders
    table: orders
    primary_key: order_id
    relationships:
      - name: customer
        type: many_to_one
        foreign_key: customer_id  # Column in orders table

SQL: LEFT JOIN customers ON orders.customer_id = customers.customer_id

Meaning: Many orders belong to one customer.

one_to_many

One record in THIS model → many records in OTHER model:

models:
  - name: customers
    table: customers
    primary_key: customer_id
    relationships:
      - name: orders
        type: one_to_many
        foreign_key: customer_id  # Column in orders table (the OTHER table)

SQL: LEFT JOIN orders ON customers.customer_id = orders.customer_id

Meaning: One customer has many orders.

one_to_one

One record in THIS model → one record in OTHER model:

models:
  - name: orders
    table: orders
    primary_key: order_id
    relationships:
      - name: invoice
        type: one_to_one
        foreign_key: order_id  # Column in invoice table

SQL: LEFT JOIN invoice ON orders.order_id = invoice.order_id

Meaning: One order has one invoice.

many_to_many

Many-to-many joins are supported via an explicit junction table. Define the junction model and reference it with through and foreign key fields:

Note

Many-to-many join inference requires through, through_foreign_key, and related_foreign_key. Without these, the join path can’t be derived.

models:
  - name: orders
    table: orders
    primary_key: order_id
    relationships:
      - name: products
        type: many_to_many
        through: order_items
        through_foreign_key: order_id
        related_foreign_key: product_id

  - name: order_items
    table: order_items
    primary_key: order_item_id
    dimensions:
      - name: order_id
        type: numeric
        sql: order_id
      - name: product_id
        type: numeric
        sql: product_id

  - name: products
    table: products
    primary_key: product_id

Bidirectional Relationships

Define from both sides for flexibility:

models:
  - name: orders
    primary_key: order_id
    relationships:
      - name: customer
        type: many_to_one
        foreign_key: customer_id

  - name: customers
    primary_key: customer_id
    relationships:
      - name: orders
        type: one_to_many
        foreign_key: customer_id

Now you can query from either direction:

SELECT orders.revenue, customers.name FROM orders
SELECT customers.name, orders.revenue FROM customers

Multi-Hop Joins

Define relationships and query across 2+ models automatically:

models:
  - name: orders
    relationships:
      - name: customer
        type: many_to_one
        foreign_key: customer_id

  - name: customers
    relationships:
      - name: region
        type: many_to_one
        foreign_key: region_id

  - name: regions
    table: regions
    primary_key: region_id

Query spans 2 hops automatically:

# orders -> customers -> regions
layer.sql("""
    SELECT orders.revenue, regions.country
    FROM orders
""")

Relationship Properties

name: Name of the related model
type: many_to_one, one_to_many, one_to_one, or many_to_many
foreign_key: The foreign key column name (defaults to {name}_id for many_to_one)
primary_key: (Optional) Primary key in related table (defaults to related model's primary_key)
through: Junction model name for many_to_many
through_foreign_key: Junction FK pointing to this model
related_foreign_key: Junction FK pointing to the related model

Python API

from sidemantic import Model, Relationship

orders = Model(
    name="orders",
    table="orders",
    primary_key="order_id",
    relationships=[
        Relationship(name="customer", type="many_to_one", foreign_key="customer_id"),
        Relationship(name="order_items", type="one_to_many", foreign_key="order_id")
    ]
)

customers = Model(
    name="customers",
    table="customers",
    primary_key="customer_id",
    relationships=[
        Relationship(name="orders", type="one_to_many", foreign_key="customer_id")
    ]
)

Symmetric Aggregates

Fan-out (one-to-many) joins can double count metrics from the “one” side. Sidemantic uses symmetric aggregates to protect metric correctness when fan-out is present.

See Fan-out & Symmetric Aggregates for the full mental model, validation workflow, and performance tradeoffs.

You’ll typically see a compiled aggregate shaped like:

sum(distinct ...)

Best Practices

Tip: Understanding Relationship Types

many_to_one: FK is in THIS table (e.g., orders.customer_id)
one_to_many: FK is in the OTHER table (e.g., customers ← orders.customer_id)
one_to_one: FK is in the OTHER table, expects one record

Warning: Primary Keys Required

Set primary_key on all models involved in relationships:

models:
  - name: orders
    table: orders
    primary_key: order_id  # Required!

Tip: Define Both Sides

For maximum flexibility, define relationships from both models:

# orders -> customers (many-to-one)
- name: orders
  relationships:
    - name: customer
      type: many_to_one
      foreign_key: customer_id

# customers -> orders (one-to-many)
- name: customers
  relationships:
    - name: orders
      type: one_to_many
      foreign_key: customer_id

Common Patterns

One-to-Many

# Order has many items
- name: orders
  primary_key: order_id
  relationships:
    - name: order_items
      type: one_to_many
      foreign_key: order_id

# Item belongs to order (inverse: many-to-one)
- name: order_items
  primary_key: item_id
  relationships:
    - name: order
      type: many_to_one
      foreign_key: order_id

Many-to-One

# Order belongs to customer
- name: orders
  primary_key: order_id
  relationships:
    - name: customer
      type: many_to_one
      foreign_key: customer_id

# Customer has many orders (inverse: one-to-many)
- name: customers
  primary_key: customer_id
  relationships:
    - name: orders
      type: one_to_many
      foreign_key: customer_id

One-to-One

# Order has one invoice
- name: orders
  primary_key: order_id
  relationships:
    - name: invoice
      type: one_to_one
      foreign_key: order_id

# Invoice belongs to order (inverse: many-to-one, but unique)
- name: invoice
  primary_key: invoice_id
  relationships:
    - name: order
      type: many_to_one
      foreign_key: order_id

Troubleshooting

Warning: "No join path found"

Ensure you've defined relationships between the models:

models:
  - name: orders
    relationships:
      - name: customer
        type: many_to_one
        foreign_key: customer_id

Warning: Wrong foreign_key

If joins aren't working, check the foreign_key:

many_to_one: Column in THIS table
one_to_many/one_to_one: Column in OTHER table