Model Relationships

Relationships define how semantic models are connected to each other through join conditions. When a metric query references dimensions or metrics from related models, the Semantic Layer automatically generates the appropriate JOIN clauses in the compiled SQL. All relationship endpoints are served at the base path /api/v1/relationships.

Relationship Types

Type	Description	Example
`ONE_TO_ONE`	One record on the left matches exactly one on the right	User to UserProfile
`ONE_TO_MANY`	One record on the left matches many on the right	Customer to Orders
`MANY_TO_ONE`	Many records on the left match one on the right	Orders to Customer
`MANY_TO_MANY`	Many-to-many through a bridge table	Products to Categories

Join Types

Type	Description
`INNER`	Only matching rows from both models
`LEFT`	All rows from the left model, matching from the right
`RIGHT`	All rows from the right model, matching from the left
`FULL`	All rows from both models
`CROSS`	Cartesian product of both models

Endpoints

Method	Path	Description
`GET`	`/api/v1/relationships`	List all relationships for the tenant
`GET`	`/api/v1/relationships/:relationshipId`	Get a relationship by ID
`GET`	`/api/v1/relationships/by-model/:modelId`	Get relationships for a specific model
`POST`	`/api/v1/relationships`	Create a new relationship
`PUT`	`/api/v1/relationships/:relationshipId`	Update a relationship
`DELETE`	`/api/v1/relationships/:relationshipId`	Delete a relationship

Relationship Structure

A relationship connects a source model to a target model through join keys.

Field	Type	Required	Description
`name`	String	Yes	Unique relationship name
`sourceModelId`	UUID	Yes	Source (left) model ID
`targetModelId`	UUID	Yes	Target (right) model ID
`relationshipType`	Enum	Yes	ONE_TO_ONE, ONE_TO_MANY, MANY_TO_ONE, MANY_TO_MANY
`joinType`	Enum	Yes	INNER, LEFT, RIGHT, FULL, CROSS
`sourceKey`	String	Yes	Join column on the source model
`targetKey`	String	Yes	Join column on the target model
`condition`	String	No	Additional join condition (SQL expression)
`description`	String	No	Human-readable description

Example: Create Relationship

Request:

{
  "name": "orders_to_customers",
  "sourceModelId": "550e8400-e29b-41d4-a716-446655440000",
  "targetModelId": "660e8400-e29b-41d4-a716-446655440001",
  "relationshipType": "MANY_TO_ONE",
  "joinType": "LEFT",
  "sourceKey": "customer_id",
  "targetKey": "id",
  "description": "Links orders to their customer records"
}

Response:

{
  "id": "770e8400-e29b-41d4-a716-446655440002",
  "name": "orders_to_customers",
  "sourceModelId": "550e8400-e29b-41d4-a716-446655440000",
  "targetModelId": "660e8400-e29b-41d4-a716-446655440001",
  "relationshipType": "MANY_TO_ONE",
  "joinType": "LEFT",
  "sourceKey": "customer_id",
  "targetKey": "id",
  "description": "Links orders to their customer records",
  "createdAt": "2026-02-12T10:30:00Z",
  "updatedAt": "2026-02-12T10:30:00Z"
}

How Relationships Are Used

When a metric query references dimensions from a related model, the query compiler automatically traverses the relationship graph and adds JOIN clauses.

Step	Description
1	User requests metrics from Model A with dimensions from Model B
2	Compiler looks up relationships between Model A and Model B
3	If a direct relationship exists, a single JOIN is added
4	If only an indirect path exists (A to C to B), multiple JOINs are added
5	The join type and keys from the relationship definition are used

Querying Across Models

A metric query that spans multiple models:

{
  "modelId": "550e8400-e29b-41d4-a716-446655440000",
  "metrics": ["total_revenue"],
  "dimensions": ["customer_name", "customer_region"],
  "filters": [
    {
      "field": "customer_region",
      "operator": "=",
      "value": "US"
    }
  ]
}

If customer_name and customer_region are dimensions on a related model, the compiler generates:

SELECT c.name AS customer_name, c.region AS customer_region,
       SUM(o.order_total) AS total_revenue
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'US'
GROUP BY c.name, c.region

Best Practices

Define relationships with the correct cardinality to ensure accurate aggregations
Use LEFT join type by default to avoid dropping rows when the target has no match
Name relationships descriptively (e.g., orders_to_customers not rel_1)
Add conditions for complex joins that require more than a simple key match
Avoid circular relationship chains that could cause infinite recursion in path traversal

Metric Versioning API Reference