Relationships and joins

Declare joins between models so a single query can span orders, customers, and more

Joins let one model reference the dimensions and measures of another, so a query can span tables — for example, group order revenue by customer country. This page covers how to declare joins and why the relationship matters. Joins are defined under a model's flax: key.

#Declaring a join

A join names the model to join to, how to match rows (sql_on), the SQL join type, and the cardinality (relationship).

models:
  - name: orders
    flax:
      primary_key: order_id
      joins:
        - name: customers
          type: left
          relationship: many_to_one
          sql_on: ${orders.customer_id} = ${customers.customer_id}

In sql_on, ${model.column} references a column on a specific model. Once the join exists, a query on orders can select customers.country as a dimension and still aggregate orders.total_revenue.

#Join types

The type maps directly to the SQL join the compiler emits:

Type Meaning
left Keep all base rows; null out unmatched joined columns (the default).
inner Keep only rows that match on both sides.
right Keep all joined rows.
full Keep rows from both sides.

Flax only emits a join when the query actually references the joined model, so declaring joins has no cost on queries that don't use them.

#Relationships and fan-out

The relationship field declares cardinality: one_to_one, many_to_one, one_to_many, or many_to_many. This is not cosmetic — a one_to_many or many_to_many join can duplicate base rows (fan-out), which would inflate a plain SUM.

To keep totals correct, declare a primary_key on the base model. Flax then computes a symmetric aggregate — summing each base row exactly once despite duplication — instead of returning a wrong number.

Important

Symmetric aggregation over a fan-out join is currently implemented for the Postgres and embedded DuckDB dialects. On warehouses without it, a query that would fan out is blocked rather than returning an inflated total. See SQL compilation.

#Next steps