Relationships

Relationships define how datasets join together. They enable automatic join resolution: when a user selects fields from multiple datasets, Semaphor finds the shortest join path automatically.

Each relationship has two ends. from is the many side (many orders per customer); to is the side whose fields identify one row (one customer per id).

relationships:
  - name: orders_to_customers
    from: { dataset: orders, fields: [customer_id] }
    to: { dataset: customers, fields: [id] }
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true

Relationship Field Reference

Field	Type	Required	Description
`id`	string	No	Unique ID (auto-generated if omitted)
`name`	string	Yes	Descriptive name (e.g., `orders_to_customers`)
`description`	string	No	Explanation of the relationship
`aiContext`	string \| object	No	Context for agents, such as when to use this join
`from`	`{ dataset, fields }`	Yes	The many side: a dataset name and its join field names
`to`	`{ dataset, fields }`	Yes	The key side: a dataset name and the fields that identify one of its rows
`cardinality`	enum	Yes	`many_to_one`, `one_to_one`, or `many_to_many`
`defaultJoinType`	enum	Yes	`INNER`, `LEFT`, `RIGHT`, `FULL`
`isAutoJoin`	boolean	Yes	Must be `true` for automatic join resolution
`joinPriority`	number	No	Higher priority used first when multiple paths exist
`discoveredBy`	enum	Yes	`user` for hand-authored, `ai` for discovered, `fk_constraint` for a database foreign key, `auto` for import
`confidence`	enum	No	`high`, `medium`, `low`
`isActive`	boolean	Yes	Set to `false` to disable without deleting

fields name dataset fields by their name, not physical columns. Both ends list the same number of fields, in matching order.

Cardinality Guide

Cardinality	Meaning	Example (`from` → `to`)
`many_to_one`	Many `from` rows match one `to` row	`orders` → `customers`
`one_to_one`	Each row matches at most one row on the other side	`users` → `user_profiles`
`many_to_many`	Both sides can have multiple matches	`students` → `courses` (via bridge table)

There is no one_to_many. For a one-to-many join, such as orders to line items, put the many side in from: from: line_items, to: orders, many_to_one.

Setting cardinality correctly is important. Semaphor uses it for fan-out detection, which prevents the metric inflation that happens when aggregating across one-to-many joins.

For many_to_one, the to fields should equal a key of the to dataset: its primaryKey or one of its uniqueKeys. For one_to_one, both ends should equal a key of their dataset. many_to_many has no key check.

datasets:
  - name: customers
    label: "Customers"
    primaryKey: [id]
    # ...

relationships:
  - name: orders_to_customers
    from: { dataset: orders, fields: [customer_id] }
    to: { dataset: customers, fields: [id] }   # matches customers.primaryKey
    cardinality: many_to_one
    # ...

When the fields don't match a key, the save succeeds with a relationship_keys_unmatched warning. Joins through that relationship may repeat rows. To clear the warning, join on the key, or declare the fields you join on as a key with uniqueKeys when their values really are unique. See Keys.

Star Schema Example

A classic fact-and-dimension star schema with 3 relationships:

relationships:
  - name: orders_to_customers
    from: { dataset: orders, fields: [customer_id] }
    to: { dataset: customers, fields: [id] }
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true

  - name: orders_to_products
    from: { dataset: orders, fields: [product_id] }
    to: { dataset: products, fields: [id] }
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true

  - name: orders_to_stores
    from: { dataset: orders, fields: [store_id] }
    to: { dataset: stores, fields: [id] }
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true

Composite Key Joins

For joins on multiple columns, list all fields on both ends, in the same order:

relationships:
  - name: sales_to_targets
    from: { dataset: sales, fields: [region, product_id, quarter] }
    to: { dataset: targets, fields: [region, product_id, quarter] }
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true

Declare the matching key on targets (primaryKey: [region, product_id, quarter]) so the key check passes.

Multi-Hop Joins

Semaphor supports up to 2-hop joins. For example, if you define:

orders -> stores (1 hop)
stores -> countries (1 hop)

Then users can query revenue from orders grouped by countries.name (2 hops). Semaphor's BFS resolver will find the path: orders -> stores -> countries.

Relationship Field Reference

Cardinality Guide

Join Keys

Star Schema Example

Composite Key Joins

Multi-Hop Joins

On this page