Relationships
Define connections between models in your semantic layer
Introduction to Relationships
Relationships define how different semantic models connect to each other, effectively specifying join conditions for queries. Defining these connections is essential for enabling analyses that combine data from multiple related models.
By establishing relationships, you empower the semantic layer to understand the logical links between your data entities. This allows metrics, dimensions, and filters to reference fields across different models seamlessly, provided the necessary joins are included in the query.
Here’s the basic structure for defining relationships within a model’s YAML configuration:
The relationships
key defines a list of connections from the current model to other models.
Defining Relationships
Each relationship specifies the connection details between the current model and another (related
) model.
Relationship Fields
Field | Required | Description |
---|---|---|
name | Yes | The name of the related semantic model this relationship connects to. |
source_col | Yes | The name of the join key column in the current model. |
ref_col | Yes | The name of the join key column in the related model. |
description | Yes | A human-readable description explaining the relationship’s business context. This is mandatory. |
type | No | Specifies the join type. Must be one of: LEFT , INNER , RIGHT , FULL OUTER . Defaults to LEFT if omitted. |
cardinality | No | Describes the numerical link between records. Must be one of: one-to-one , one-to-many , many-to-one , many-to-many . Optional. |
Understanding Join Keys
source_col
: Refers to the column in the current model’s underlying table/view that holds the key value. Often a foreign key.ref_col
: Refers to the column in the related model’s underlying table/view that holds the corresponding key value. Often a primary key.
Example: Orders to Customers
Assuming an orders
model needs to link to a customers
model:
Relationship Details
Accurately defining join types and cardinality is crucial for correct query generation and results.
Join Types (type
)
This field dictates the SQL join behavior when this relationship is used in a query:
LEFT
(Default): Includes all records from the current model (where the relationship is defined) and matching records from the related model. Fields from the related model will beNULL
if no match is found.INNER
: Includes only records where a match exists in both models based on the join keys.RIGHT
: Includes all records from the related model and matching records from the current model. Fields from the current model will beNULL
if no match is found. (Use with caution, oftenLEFT
from the other model is clearer).FULL OUTER
: Includes all records from both models. Fields will beNULL
where no match exists in the corresponding model.
Cardinality (cardinality
)
This describes the expected numerical relationship between records connected by the join keys:
one-to-one
: Each record in the current model maps to at most one record in the related model, and vice-versa (e.g.,users
touser_profiles
).one-to-many
: Each record in the current model can map to multiple records in the related model (e.g.,customers
toorders
).many-to-one
: Multiple records in the current model can map to a single record in the related model (e.g.,orders
tocustomers
). This is the inverse ofone-to-many
.many-to-many
: Records in one model can map to multiple records in the other, and vice-versa (e.g.,products
toorders
). This typically requires a junction model.
Modeling Many-to-Many Relationships
Direct many-to-many links are best handled via an intermediate junction model. Each primary model then defines a one-to-many
relationship to the junction model. The junction model itself defines many-to-one
relationships back to each primary model.
Example: Products and Orders via order_items
Junction
Using Relationships in Queries
Once defined, relationships enable referencing fields (dimensions, measures) from related models within metrics or filters using dot notation: relationship_name.field_name
.
Example: Cross-Model Metric Reference
Best Practices & Considerations
Accurate Keys
Ensure source_col
and ref_col
correctly identify the join keys in the respective models. Mismatches lead to failed or incorrect joins.
Correct Cardinality & Type
Define cardinality
and type
accurately based on the data’s structure and intended join logic. Defaults are convenient but should be verified.
Mandatory Descriptions
Always provide clear, mandatory description
s explaining the business purpose of the relationship. This is crucial for understanding the model.
Bidirectional Definitions
For clarity and completeness, consider defining relationships in both directions between connected models (e.g., orders -> customers
and customers -> orders
).
Junction Models for M:N
Use explicit junction models to resolve many-to-many relationships clearly. Avoid attempting direct many-to-many definitions.
Consistency
Use consistent naming conventions for relationships and related fields across your models.
Comprehensive Example
Illustrating bidirectional relationships and referencing related data.