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.

relationships:
  - name: orders_to_customers
    sourceDataset: orders
    sourceFields: [customer_id]
    targetDataset: customers
    targetFields: [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`)
`sourceDataset`	string	Yes	Dataset name where the join starts
`sourceFields`	string[]	Yes	Column(s) in the source dataset
`targetDataset`	string	Yes	Dataset name being joined to
`targetFields`	string[]	Yes	Column(s) in the target dataset
`cardinality`	enum	No	`one_to_one`, `one_to_many`, `many_to_one`, `many_to_many`. Defaults to `one_to_one`
`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 AI-discovered, `fk_constraint` for database FK
`confidence`	enum	No	`high`, `medium`, `low`
`isActive`	boolean	Yes	Set to `false` to disable without deleting
`description`	string	No	Explanation of the relationship

Cardinality Guide

Cardinality	Meaning	Example
`one_to_one`	Each source row matches exactly one target row	`users` to `user_profiles`
`one_to_many`	Each source row can match multiple target rows	`orders` to `line_items`
`many_to_one`	Multiple source rows match one target row	`orders` to `customers`
`many_to_many`	Both sides can have multiple matches	`students` to `courses` (via bridge table)

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

Star Schema Example

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

relationships:
  - name: orders_to_customers
    sourceDataset: orders
    sourceFields: [customer_id]
    targetDataset: customers
    targetFields: [id]
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true
 
  - name: orders_to_products
    sourceDataset: orders
    sourceFields: [product_id]
    targetDataset: products
    targetFields: [id]
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true
 
  - name: orders_to_stores
    sourceDataset: orders
    sourceFields: [store_id]
    targetDataset: stores
    targetFields: [id]
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true

Composite Key Joins

For joins on multiple columns, list all fields:

relationships:
  - name: sales_to_targets
    sourceDataset: sales
    sourceFields: [region, product_id, quarter]
    targetDataset: targets
    targetFields: [region, product_id, quarter]
    cardinality: many_to_one
    defaultJoinType: LEFT
    isAutoJoin: true
    discoveredBy: user
    isActive: true

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 orders.amount grouped by countries.name (2 hops). Semaphor's BFS resolver will find the path: orders -> stores -> countries.