Relationships & Joins

Relationships connect two Semantic Data Objects so that a single query can combine their fields. When a query selects, filters, groups, or sorts fields that live on more than one data object, the query engine walks the relationships defined in the model to build the joins that bring those tables together. You do not write SQL joins or ON clauses in the request — you select the fields you want, and the join path is derived from the relationships in the model.

A relationship carries the two endpoint data objects, one or more join criteria (the field-to-field conditions that form the join), a join operator per criterion, a cardinality hint, and a join type. Most of this is defined once in the model. At query time you influence traversal with join_path_plan — whether to join every related table or only the minimal set needed to answer the query.

The most important thing to know: joins happen because of relationships. If two selected data objects have no relationship path between them, the query fails. Getting cardinality and join criteria right in the model is what keeps results from multiplying or dropping rows.

Relationships, their join criteria, join operators, cardinality, and join type are all defined in the Semantic Data Model as SemanticRelationship entities. Each relationship names a left and a right data object, a list of criteria, and (optionally) a cardinality and join type; the default join type is Auto. Relationships that already exist between Data Cloud objects are imported into the model automatically when the model is created. For the authoring shape and the full field list, see Semantic Relationship in the Authoring API.

You reference relationships only indirectly — by selecting fields from related data objects. When you supply the model inline in the request (rather than by ID), the relationship definitions appear under semanticRelationships on the model, which is why the query examples below show that structure inline.

Each relationship declares one or more criteria. A criterion pairs a leftSemanticFieldApiName with a rightSemanticFieldApiName and compares them with a joinOperator. When joinOperator is omitted, the criterion defaults to Equals. The supported operators are Equals, EqualsIgnoreCase, GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual, NotEquals, and NotEqualsIgnoreCase.

1{
2  "semantic_model": {
3    "apiName": "C360_Semantic_Model_Extended_b0d2",
4    "semanticRelationships": [
5      {
6        "apiName": "Contact_Point_Phone_Case",
7        "leftSemanticDefinitionApiName": "Contact_Point_Phone",
8        "rightSemanticDefinitionApiName": "Case",
9        "criteria": [
10          {
11            "leftSemanticFieldApiName": "Party",
12            "rightSemanticFieldApiName": "Account",
13            "joinOperator": "EqualsIgnoreCase"
14          }
15        ],
16        "cardinality": "ManyToMany"
17      }
18      // ...
19    ]
20    // ...
21  }
22}

Performance: Prefer Equals over EqualsIgnoreCase and NotEqualsIgnoreCase when the join keys are already stored in the same case. Case-insensitive operators wrap both sides of the join in a case-folding function, which prevents a hash join and increases join cost.

Performance: Range and inequality join operators (GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual, NotEquals) can multiply rows and cannot use a hash join. Prefer an equality (Equals) criterion where the data model allows it.

Each side of a criterion is typed by leftFieldType / rightFieldType, which controls how the engine resolves that operand:

• TableField — a physical data-object field. This is the default when the field type is omitted.
• SemanticField — a model-level calculated dimension or measure, referenced by its API name.
• Formula — an inline relationship formula (see Relationship formulas).

A single relationship can mix reference types across its criteria. The example below joins on one TableField-to-TableField criterion and one TableField-to-SemanticField criterion, where the right side names a calculated field.

1{
2  "semantic_model": {
3    "apiName": "Sales",
4    "semanticRelationships": [
5      {
6        "apiName": "relationship",
7        "leftSemanticDefinitionApiName": "SemanticAccount__dlm",
8        "rightSemanticDefinitionApiName": "SemanticContact__dlm",
9        "joinType": "Left",
10        "criteria": [
11          {
12            "leftSemanticFieldApiName": "semantic__Id__c",
13            "leftFieldType": "TableField",
14            "rightSemanticFieldApiName": "semantic__Id__c",
15            "rightFieldType": "TableField"
16          },
17          {
18            "leftSemanticFieldApiName": "Semantic_KQ_Id__c",
19            "leftFieldType": "TableField",
20            "rightSemanticFieldApiName": "calcForJoinApiName",
21            "rightFieldType": "SemanticField"
22          }
23        ]
24      }
25    ]
26    // ...
27  }
28}

cardinality tells the engine how records on one side of a relationship map to records on the other, which guides join optimization and deduplication. Valid values are ManyToMany (the default when omitted), ManyToOne, OneToMany, and OneToOne. Declaring a OneToOne or ManyToOne relationship lets the engine skip deduplication for that join; an inaccurate cardinality can cause row multiplication or missing rows. A relationship can also list multiple criteria that must all hold for two records to join.

1{
2  "semanticModel": {
3    "semanticRelationships": [
4      {
5        "apiName": "MediaBuy_Site",
6        "leftSemanticDefinitionApiName": "MediaBuy",
7        "rightSemanticDefinitionApiName": "Site",
8        "cardinality": "ManyToOne",
9        "criteria": [
10          {
11            "leftSemanticFieldApiName": "MediaBuySiteId",
12            "rightSemanticFieldApiName": "SiteId"
13          },
14          {
15            "leftSemanticFieldApiName": "MediaBuyDataSourceObject",
16            "rightSemanticFieldApiName": "SiteDataSourceObject"
17          }
18        ]
19      }
20      // ...
21    ]
22    // ...
23  }
24}

Performance: Declare OneToOne or ManyToOne cardinality when it is accurate. The engine can then skip row deduplication for that join, giving faster queries; an inaccurate cardinality can multiply or drop rows.

joinType sets the join strategy for a relationship. The user-settable values are Auto (the default when unset), Left, and Inner. With Auto, the engine resolves the concrete strategy at query time based on the fields the query touches. You can also pin a relationship to Left or Inner; a pinned relationship always joins with that strategy.

A query must use one style consistently: you cannot mix Auto relationships with pinned (Left/Inner) relationships along the join path — the query is rejected. Pinning join types also narrows what the query can do. When every relationship a query traverses is pinned to Left or Inner, these features are not supported: level-of-detail (LOD) expressions, forecasts, advanced dimension filters, table calculations, and row count. Use Auto relationships for queries that need them. Auto relationships, in turn, cannot be combined with smart totals (grand totals and subtotals).

Joins inside a logical view are defined by the logical view itself with explicit join types, not through top-level semanticRelationships. Use a relationship to connect a logical view to a data object. The example below fixes a Left join between a data object and a logical view.

1{
2  "semantic_model": {
3    "apiName": "Sales",
4    "semanticRelationships": [
5      {
6        "apiName": "relationship",
7        "leftSemanticDefinitionApiName": "SemanticAccount2__dlm",
8        "rightSemanticDefinitionApiName": "logicalTable",
9        "joinType": "Left",
10        "criteria": [
11          {
12            "leftSemanticFieldApiName": "Semantic_KQ_Id__c",
13            "rightSemanticFieldApiName": "SemanticAccount__dlm_Semantic_KQ_Id__c"
14          }
15        ]
16      }
17    ]
18    // ...
19  }
20}

join_path_plan, set under options on the structured query, controls how many related tables the engine brings into the join. JOIN_ALL_TABLES joins every table reachable through relationships, even when the query only selects fields from some of them. JOIN_MINIMAL_TABLES joins only the tables required to answer the query. The example below requests the all-tables plan; switch the value to JOIN_MINIMAL_TABLES to restrict the join to the minimal set.

1{
2  "structured_semantic_query": {
3    "fields": [
4      {
5        "expression": {
6          "table_field": {
7            "name": "Account Name",
8            "table_name": "AccountSemanticLayer__dll"
9          }
10        }
11      }
12    ],
13    "options": {
14      "join_path_plan": "JOIN_ALL_TABLES",
15      "detailed_rows": true
16    }
17  }
18  // ... relationship defined in the model
19}

Performance: JOIN_MINIMAL_TABLES joins only the tables needed to answer the query, reducing scanned rows and intermediate result size. Use JOIN_ALL_TABLES only when you specifically need every related table in the result.

Performance: Set disallow_cross_join to true to reject a query that would otherwise produce an unintended cross join (Cartesian product).

A union combines tables vertically — appending the rows of several data objects that share a schema — unlike a relationship join, which combines tables horizontally by matching keys. A union is defined inside a logical view, not as a query construct or a top-level relationship. For the union example and its model shape, see Logical Views.

When a criterion side has leftFieldType or rightFieldType set to Formula, the corresponding leftSemanticFieldApiName / rightSemanticFieldApiName slot carries an inline formula expression instead of a field name. This joins tables whose columns do not match exactly — for example, normalizing case or extracting a substring before comparing. The formula is a row-level expression (no aggregation) written in the TUA dialect, using [Table].[Field] references. The example below joins with a LessThan operator where the right operand is UPPER([SemanticContact__dlm].[Semantic_KQ_Id__c]).

1{
2  "semantic_model": {
3    "apiName": "Sales",
4    "semanticRelationships": [
5      {
6        "apiName": "relationship",
7        "leftSemanticDefinitionApiName": "SemanticAccount__dlm",
8        "rightSemanticDefinitionApiName": "SemanticContact__dlm",
9        "joinType": "Left",
10        "criteria": [
11          {
12            "leftSemanticFieldApiName": "Semantic_KQ_Id__c",
13            "leftFieldType": "TableField",
14            "rightSemanticFieldApiName": "UPPER([SemanticContact__dlm].[Semantic_KQ_Id__c])",
15            "rightFieldType": "Formula",
16            "joinOperator": "LessThan"
17          }
18        ]
19      }
20    ]
21    // ...
22  }
23}

For the relationship criteria fields (leftSemanticDefinitionApiName, rightSemanticDefinitionApiName, criteria, joinOperator, leftFieldType, rightFieldType, cardinality, joinType), see the authoring entity in Related. For the full request schema, see Request Reference.

• A join path must exist between all queried tables. If the selected data objects are not connected through relationships, the query fails.
• Only one relationship is allowed between any pair of tables. Multiple relationships between the same two tables are rejected.
• Self-joins must be defined explicitly. When a table name and its alias resolve to the same table, the relationship connecting them must be declared.
• Join criteria operands must have matching data types. The left and right expressions of a criterion must be the same data type.
• Textual operators require textual operands. Case-insensitive and contains-style comparisons apply only when both operands are text.
• Join criteria operands cannot be aggregated. A criterion cannot reference an aggregate expression, and inline relationship formulas must be row-level (no aggregations, LOD expressions, or table calculations) and cannot reference other on-the-fly calculated fields.
• The relationship graph must be acyclic. Cycles in the set of relationships are rejected.

• Semantic Relationship — Authoring API for defining relationships, join criteria, operators, cardinality, and join type.
• Dimensions, Measurements & Semantic Data Objects — The data objects that relationships connect and how to select their fields.
• Calculated Fields — Calculated dimensions and measures referenced by SemanticField-typed join criteria.
• Filtering — How filters interact with joins across related tables.
• Logical Views — Standard, union, and custom SQL logical views, including the union example.
• Request Reference — Complete field schema for query options and the model.

j

Get Started

Authoring API

Semantic Query API

Overview

Dimensions, Measurements, and Data Objects

Relationships and Joins

Parameters

Calculated Fields

Functions and Formulas

Filtering

Grouping

Aggregation and Totals

Sorting and Limiting

Predict and Forecast

Logical Views

Shared Dimensions

Currency and Fiscal Handling

SemanticQueryRequest Fields

Semantic Metric Query API