Automatic Joins

Automatically Generate Joins with Trilogy’s Semantic Layer

Writing SQL joins by hand is one of the most repetitive and error-prone parts of analytics work. Miss a join condition, forget a column alias, or mix up table relationships, and your query either fails silently or - perhaps worse - returns misleading results.

Trilogy’s semantic layer solves this by automatically generating joins based on relationships you define once in your data model. That means:

• No repeated join boilerplate
• Type-safe joins (column mismatches are caught early)
• Easier query reuse across your team

In this guide, you’ll:

• Understand what a semantic layer is in SQL
• See how Trilogy models relationships
• Write a query with no manual join clauses
• Try it yourself in Trilogy Studio

What is a Semantic Layer?

A semantic layer is a central definition of your business entities (tables, views, or APIs) and how they relate. Instead of remembering:

SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id

…you define once:

“Orders belong to Customers via customer_id → id.”

From then on, you can just write:

SELECT orders.id, customers.name

…and Trilogy inserts the correct join automatically.

Defining Relationships in Trilogy

In Trilogy, you use concepts to model your tables and their relationships.

Example:

key customer_id int;
property customer_name string;

key order_id int;

datasource orders (
    order_id,
    customer_id
)
grain (order_id)
address tbl_orders;

datasource customers (
    id:customer_id,
    name:customer_name
)

grain (customer_id)
address tbl_customer;

Here:

• Customer and Order are reusable concepts
• The join definition tells Trilogy once how these two are related
• Any query using both concepts will get the join automatically

Query Without Joins

Once your concepts are defined, you can query them without writing JOIN manually:

select 
  order_id,
  customer_name
;

Trilogy expands this internally to:

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id

Benefits:

• Faster to write
• Fewer errors
• Easy to refactor if join keys change—update in one place

The Fun Cases

Of course, this is easy - the fun cases come with partial values or nullability. Let's add a ship dimension, but not every order has shipped

key date date;
property date.month int;

datasource dates (
    date,
    month
)
grain (date)
address dim_date;

Our extended order table might look like

import dates as ship_date;

datasource orders (
    order_id,
    customer_id,
    ship_date:?ship_date

)
grain (order_id)
address tbl_orders;

The ? indicates that the binding is potentially nullable. Trilogy will then use a outer join or full join as appropriate for the query.

SELECT o.id, c.name, d.month
FROM orders o
JOIN customers c ON o.customer_id = c.id
LEFT OUTER JOIN dates d on o.ship_date = d.date

When Automatic Joins Shine

This approach is especially valuable when:

• Your schema has many-to-many relationships
• You frequently query the same joins (e.g., orders → customers → products)
• You’re onboarding new analysts who don’t know the schema deeply
• You want to avoid copy-paste join logic across dashboards, ETL jobs, or ad-hoc queries