A relationship is a join with a name, keys and a cardinality. Declare one and Kater knows how two models connect: it keeps measures from double counting through the join, leaves the join out when nothing uses it, and lets the field picker add fields across it. In the Shared models editor you add, change and check joins on a diagram, and the editor writes the RELATIONSHIP for you.

Example

One order has one customer, so the join from orders to customers is many_to_one:

1
FROM kater_dataset.orders AS o
2
-- @label Customer
3
LEFT JOIN RELATIONSHIP('customer', kater_dataset.customers AS c ON o.customer_id = c.customer_id, 'many_to_one')

A join can match on several keys and carry its own description and AI context. These come from the Marketing Sessions model:

1
FROM semantic.ga4_sessions AS sessions
2
-- @label Session activity
3
-- @description Adds engagement, page-view, and event-value facts for the same session, client key, and stream.
4
LEFT JOIN RELATIONSHIP('session_activity', semantic.fct_velir_ga4__sessions AS facts ON sessions.session_key = facts.session_key
5
AND sessions.client_key = facts.client_key
6
AND sessions.stream_id = facts.stream_id, 'one_to_one')
7
-- @label Lifecycle profile
8
LEFT JOIN RELATIONSHIP('lifecycle_profile', semantic.ga4_client_lifecycle AS lifecycle ON sessions.lifecycle_identity = lifecycle.lifecycle_identity, 'many_to_one')
9
-- @label Daily campaign performance
10
-- @ai_context Full-data profiling on 2026-09-12 found unique target keys after lowercasing. ...
11
LEFT JOIN RELATIONSHIP('daily_campaign_performance', semantic.paid_media_daily_utm AS paid ON sessions.session_start_date = paid.union__date_day
12
AND LOWER(sessions.session_source) = LOWER(paid.union__utm_source)
13
AND LOWER(sessions.session_medium) = LOWER(paid.union__utm_medium)
14
AND LOWER(sessions.session_campaign) = LOWER(paid.union__utm_campaign), 'many_to_one')

Syntax

Where joins are allowed

The joins diagram

Open a semantic model in Shared models. The joins diagram sits above the rows. It comes from the model’s SQL, so it draws without the warehouse.

• The base card, at the left: the source the model starts from, with its alias, Base, its fields in the model and how many joins start there.
• A card per joined source, at the right: its label and alias, its fields in the model, and the keys it matches on.
• A chip on each line: the join’s label and its cardinality, with a check mark once the join is checked on the warehouse.
• New joins have a dashed line, and their card is marked New.
• A many to many join has a dashed amber line and a warning mark on its chip.
• Controls: zoom out, zoom in and Fit at the bottom left, and the key at the bottom right.

Click a chip or a line to open the join in the side panel. Click a card to open the source.

The join panel

Every change shows at once in the diagram and the panel, and saves itself. Edit › Undo takes it back exactly. Changing the keys keeps the join’s name, label, description and the fields that read its alias.

Keys written as expressions

A key such as LOWER(sessions.session_source) shows as written. The panel says how many pairs are expressions, such as “3 pairs are written as expressions.”, and Edit in SQL opens the SQL pane at those lines. Edit expression keys in the SQL.

What the Measures line says

Add a source

Check a join on the warehouse

A cardinality is what you declare. The check tests it against your data. It runs counting queries on your warehouse: how many rows match, how many rows each one matches, and whether the joined side is unique on the keys.

• One join. Open the join and select Check again. A join you add is checked at once.
• Every join. Select Run › Check every join, or Model › Check every join.
• Read the answer. “Checked today at 10:42 AM. 18,603 of 18,603 sessions match. Each matches 1 profile.” A join that leaves rows without a match says so: “6,497 of 18,603 sessions match, 35%. 12,106 sessions have no daily campaign row. Their paid fields are empty.”
• Checks are kept. A check stays until the model’s SQL changes, so a second look shows at once.
• Stop. While a check runs, the panel says what it counts, with Stop.

A check that finds a closer match

When the data fits a closer cardinality than the one declared, the check says so. In Client Analysis Inputs, client_mapping is declared Many to many. The check finds that stg_velir_ga4__user_id_mapping has 8,615 rows and 8,615 Client Keys, so each session matches at most 1 row. Select Change to Many to one, or Keep Many to many.

The closer match matters for measures. Across a many to one join, measures keep their formula as written. Across many to many, each measure is worked out on its own rows first.

Remove a join

Select Remove join at the bottom of the join’s panel. When fields read the joined source, the panel asks first and names them: “Remove Lifecycle profile? 12 fields read lifecycle: First Seen Start Date, first_seen_month, lifetime_sessions and 9 more. They go with the join.” Select Remove join and 12 fields to remove the join and those fields in one change, or Keep it. Undo brings back both.

From a terminal or your agent

Your agent and the CLI write joins through the same operation as the editor. Save the edit as a JSON file:

[
  {
    "kind": "add_relationship",
    "model": "kater_dataset.customers",
    "name": "customer",
    "alias": "c",
    "keys": [{"field": "o.customer_id", "other_field": "customer_id"}],
    "cardinality": "many_to_one",
    "label": "Customer"
  }
]

kater model edit semantic.shop \
  --path connections/duckdb_sample/modeling/kater_objects/semantic_models/shop.sql \
  --relationships-file customer.json --write

• add_relationship takes the other model and its keys, and optionally name, alias, required, cardinality and label.
• update_relationship changes the label, required or cardinality of a relationship, by its name. To change its model or keys, remove it and add it again.
• remove_relationship takes the join out. It is refused while the SELECT still reads the other model’s alias.

Without --write, the command returns the plan and changes nothing.

Unused joins are left out

Relationships are kept only when needed. If no field, filter, grouping or other join uses the target, the compiler removes the join. That can also remove its effect on rows: rows it filtered out, or rows it repeated. The warning relationship_pruned_row_effect says so. An unused LEFT join declared many_to_one or one_to_one has no such effect and gets no warning.

When the join’s effect on rows must always apply, turn on Join even when no field from the source is in use in the join’s panel. In SQL that is REQUIRED_RELATIONSHIP, and in an edit "required": true.

How measures stay correct

A measure totals its own rows: the rows of the sources its formula reads. Other joins decide which of those rows count, and let you group or filter by the joined columns. They never multiply them. Two shipments for one order never double that order’s amount.

• A join that cannot repeat rows stays as written. That is a join declared many_to_one or one_to_one toward the joined side, a source grouped or made DISTINCT on every key, or a one-row source.
• Any other join counts each row once. Kater evaluates it as a semi-join on the measure’s rows.
• Plain aggregates have no such guard. A raw SUM over a join, outside a measure, counts each row once for each match. Aggregate the joined side in a CTE first, or declare a measure.
• A raw aggregate over a join gets a note. A SUM or COUNT that is not in a MEASURE, such as one in a CTE, in a SELECT that joins other sources, gets the information note possible_fan_out. It asks you to check the join. A measure gets no note, because it counts each row once in each group. See Measures across joins and unions.
• Declaring a cardinality changes no data. The compiler trusts what you declare: many_to_one does not remove duplicate keys. Check the join on the warehouse, and say what you found in -- @ai_context, as the example does.

Joins the field picker writes

When you add a field from another model in a query’s field picker, Kater writes the join for you only when one relationship path leads there: a direct relationship or one through a single model in between, with proven keys. It never guesses from matching column names. Otherwise it stops with needs_relationship or ambiguous_relationship, and you write the join in SQL.

Common errors