Use this guide when deciding where join logic and join guidance should live in Zenlytic. There are two separate questions to answer:
Structure: the actual join definition, such as
sql_on, cardinality, and join type.Prose: natural-language guidance about valid paths, fan-out risk, conditional joins, and granularity mismatches.
The short version: put join structure in relationships, add table-specific judgment in a view description or zoe_description, keep system_prompt.md limited to universal rules, and use skills only for recurring multi-step workflows.
Decision rule
Is it the join structure, especially if it is non-obvious or non-default? Use
relationships.Is it prose judgment about one table or view? Use that view's
descriptionorzoe_description.Is it a universal rule that applies across all questions and views? Use
system_prompt.md, sparingly.Is it a complex, recurring, situational workflow? Use a skill.
Hierarchy at a glance
Surface | Level | When it loads | What it holds | Status |
| Structural / global | Retrieved as model context when relevant | The join itself: | Recommended |
View | View | When the view is in context | Valid and invalid paths, fan-out, conditional joins, granularity guidance | Recommended |
| Workspace | Always | Universal join-routing rules only | Use sparingly |
Skill | On demand | When the situation matches | A complex, recurring, multi-step join workflow | For workflows only |
Topics / | Legacy | n/a | Legacy join context | Avoid for new joins |
1. Use relationships for join structure
relationships are the recommended home for new structured joins. They live on the model file, such as models/<model>.yml, and are retrieved as model context when relevant. See the relationships documentation for the full reference. Relationships replace the older pattern of relying on topics or the identifiers block for join definitions.
Define a relationship when the join is:
Non-obvious: column names do not match, type casting is needed, or multiple columns must line up.
Non-default: the cardinality or join type is something other than
many_to_oneandleft_outer.Multi-column, self-join, or repeated join: for repeated joins to the same table, define a second relationship.
Obvious foreign-key-to-primary-key joins usually do not need to be defined. For example, orders.customer_id to customers.customer_id can often be inferred automatically. Over-documenting obvious joins can degrade performance, so less is more. If Zoë is choosing the wrong path, an explicit relationship can still help pin it down.
version: 1
type: model
name: my_model
connection: my_connection
relationships:
- from_table: orders
join_table: customers
relationship: many_to_one # many_to_one (default) | one_to_one | one_to_many | many_to_many
join_type: left_outer # left_outer (default) | inner | full_outer
sql_on: ${orders.customer_id} = ${customers.customer_id}
For multi-column joins, use a folded block (>) so the multi-line condition stays valid YAML:
relationships:
- from_table: inventory
join_table: warehouse_costs
relationship: many_to_one
join_type: left_outer
sql_on: >
${inventory.warehouse_id} = ${warehouse_costs.warehouse_id}
AND ${inventory.product_id} = ${warehouse_costs.product_id}
2. Use view descriptions for prose judgment
Relationships define structure. Descriptions explain judgment. Put table-specific join guidance on the relevant view.
Good uses for view-level prose:
Which join paths are valid or invalid for common questions.
Fan-out pitfalls on a specific one-to-many relationship.
Conditional joins, where a join is used only to apply a filter.
Granularity mismatches, such as daily data joined to hourly data. Relationships cannot solve that by themselves, so tell Zoë to aggregate to a common level in CTEs before joining.
Use description for user-facing context and zoe_description for agent-only instructions. See the view documentation for view-level settings. View description and zoe_description support up to 10,000 characters. Field description and zoe_description are capped at 1,024 characters; see the field documentation for field-level settings.
# on the line_items view
description: "Line-item detail. One row per product per order."
zoe_description: >
Join to orders via order_id (many_to_one). This view is one-to-many from
orders, so SUM measures here fan out order-level columns. Aggregate
line_items in a CTE before joining back to orders.
3. Use system_prompt.md only for universal rules
Put join rules in system_prompt.md only when they apply across all questions and views. The system prompt is always loaded, so keep it lean. Field-specific and view-specific notes belong on the view instead.
4. Use skills only for recurring multi-step workflows
Skills are not a structural home for joins. Do not put a sql_on definition in a skill. Skills are loaded on demand when the situation matches, so they are best for recurring, situational workflows that are too involved for a short description.
A join concern belongs in a skill only when all of these are true:
It is a multi-step pattern, not a single join condition.
It is recurring, meaning users ask for it repeatedly.
It is situational, so it should not live permanently in the system prompt.
It cannot be captured well by a relationship plus a short
zoe_description.
A classic example is a time-granularity mismatch that requires a CTE-first pattern, such as blended CAC where daily spend and hourly sessions must each be aggregated before joining.
# /data_model/skills/blended-cac/SKILL.md
name: blended-cac
description: How to compute blended CAC across spend and sessions.
Spend (daily) and sessions (hourly) are at different grains and cannot be
joined directly. Aggregate spend to daily in one CTE, sessions to daily in
another, join on date, then divide spend by new customers.
Anti-pattern: Do not put a single fan-out warning or join path into a skill. That belongs on the view's zoe_description. Skills earn their place only when the workflow is too involved for prose and too situational for the system prompt.
Related resources
Intercom Help Center articles:
Connecting Tables - Joins and Identifiers for existing join setup guidance.
Understanding topics in Zenlytic for legacy topic context.
Who can access Context Manager from chat? for permissions around editing model context.
Git and data model deployment troubleshooting if model changes do not appear after deployment.
Docs:
Bottom line
Define join structure in relationships only when it is non-obvious or non-default. Add judgment as prose on the view. Reserve system_prompt.md for universal rules. Use skills only for involved, recurring join workflows. Treat topics and identifiers as legacy and avoid them for new joins.