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 - often worse - returns misleading results.

Trilogy solves this by automatically generating joins based on common concepts bound in your metadata layer.

That means:

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

Tips

Many semantic layers resolve joins automatically; Trilogy has a particularly flexible resolution engine that can dynamically assemble any required join path from the higher-level concept bindings, with less boilerplate and more flexibility than a static PK/FK mapping.

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 the semantic layer resolves the join automatically. For simple cases, this is a nice-to-have; for complex join and relationship patterns, it can be even more efficient.

Defining Relationships in Trilogy

In Trilogy, you use concepts as a logical layer that ties together whatever physical assets you have (tables, typically).

Example:

key customer_id int;
property customer_id.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 presence of the customer_id concept on both tables automatically creates a link
• Any query pulling in customer info alongside orders will join the two automatically.

Resolution

Trilogy tries to find the minimum (cost-based) spanning set of sources that cover your output requests.

For this input:

select 
  order_id,
  customer_name
;

Trilogy expands this internally to:

SELECT o.id, c.name
FROM customers c
INNER JOIN orders o  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

That was a simple case.

Automatic resolution provides more value when we have 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

Not all customers might have orders; we can capture that using the ~ partial modifier.

import dates as ship_date;

datasource orders (
    order_id,
    ~customer_id,
    ship_date:?ship_date

)
grain (order_id)
address tbl_orders;

With that modified binding, we'd then need to start with our complete customer set to return all customers; then, for those with orders, pull those in; and, for those with ship dates, pull in the months.

SELECT o.id, c.name, d.month
FROM customers c
LEFT OUTER JOIN orders o 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

Tips

We'll be honest - most simple examples are just as fast to write in SQL. But the intermediate layer starts to pay off as you add in more aggregate tables, refactor your warehouse, or build partial materialized views.