Demo - Exploring the TPC-DS Dataset

This demo uses the popular TPC-DS dataset, often used to benchmark performance for databases.

We’ll use it a little differently here. DuckDB can generate a representative data warehouse from TPC-DS using a simple extension, and we’ll use that to explore Trilogy syntax. You can read more about Trilogy and our use of this benchmark here.

Tips

This demo uses a backing service that may take a few seconds to cold-start; follow-up queries should be fast. This service uses DuckDB. Trilogy is database-agnostic: the same syntax works on any backend, such as Postgres, BigQuery, or Snowflake.

Our First Queries

TPC-DS provides a nice, tidy warehouse: 17 dimension tables and 7 fact tables.

Dimensions

call_centercatalog_pagecustomercustomer_addresscustomer_demographicsdate_dimhousehold_demographicsincome_banditempromotionreasonship_modestoretime_dimwarehouseweb_pageweb_site

Fact Tables

catalog_salescatalog_returnsinventorystore_salesstore_returnsweb_salesweb_returns

That’s a lot to work with! Fortunately, someone has already defined a Trilogy model for TPC-DS here, and we’ll be using that directly.

Tips

Can't wait? Hop over to this overview to read more about the basic semantic model.

Let’s focus on Trilogy imports. These typically map closely to fact tables—by design. Queries often center on fact tables, so they’re natural entry points for modeling.

We’ll hide the import statements from the queries shown here—you can assume they’re already loaded into the environment.

customerstore_salesweb_salescatalog_sales

Tips

In the section below, you have the opportunity to run real Trilogy queries. Click the pulsing 'run' button below each query to see results and SQL.

Basic Select

A basic select from our customer import to see what our names and IDs look like. Click the SQL tab to see the generated SQL.

select 
    customer.id, 
    customer.full_name,
limit 5;

Another View, Similar Data

The semantic model encodes relationships and supports imports; a hierarchical selection like this - "store_sales.customer" is referencing the customer model imported and related to the store_sales model. Click through to the SQL after running this query and you'll see that you've just done your first join between two tables - congratulations!

# not all orders have a valid customer ID, so let's just look at the ones that do
where store_sales.customer.id is not null
select
    store_sales.customer.id, 
    store_sales.customer.full_name,
    store_sales.ticket_number, 
limit 5;

Sales By Customer

This becomes more relevant as we start to pull in fact information. Just like SQL, you can define new information in a query, such as creating a total_sales calculation. All derived values must have an explicit name and are then reusable further on in the query. Checking the SQL here will show you that you've now moved onto referencing 3 tables!

where store_sales.customer.state is not null
select 
    store_sales.customer.id,
    store_sales.customer.first_name,
    store_sales.customer.state,
    sum(store_sales.sales_price) as total_sales
order by
    total_sales desc
limit 10;

The Model

Earlier we mentioned a model that allows us to run these queries. Let’s take a closer look.

Modeling is the heart and art of Trilogy; it’s where we define names and relationships that enable intuitive access to data.

Models define and bind concepts, and they often import other models (role-playing dimensions, etc.). They can be 1:1 with tables, but don’t have to be, which is useful for refactoring, extending, or simplifying logic.

Tips

Trilogy uses the same language for querying and modeling. You can define or extend a model as part of an ad hoc workflow - just like SQL. A model defines the relationship between Trilogy concepts and the underlying data. It acts as a contract to generate queries. In fact, models can be used for validation - the trilogy integration command will validate that the database conforms to your model. You can use this in authoring to ensure you have represented the database; and in production to catch drift.

Here’s the store_sales model we’ve been querying from. The full TPC-DS model includes many such files.

import std.money; # rich currency types like usd

import item as item;
import date as date;
import date as return_date;
import time as time;
import time as return_time;
import customer as customer;
import customer as return_customer;
import promotion as promotion;
import customer_demographic as customer_demographic;
import store as store;
import store as return_store;

# you can interleave modeling and queries;
select 1 -> example;


key ticket_number int;
properties <ticket_number,item.id> (
    quantity int,
    sales_price numeric::usd,
    list_price numeric::usd,
    ext_sales_price numeric::usd,
    ext_wholesale_cost numeric::usd,
    ext_list_price numeric::usd,
    ext_discount_amount numeric::usd,
    coupon_amt numeric::usd,
    net_profit numeric::usd,
    is_returned bool,
    net_paid numeric::usd,
    return_amount numeric::usd?,
    return_net_loss numeric::usd?,
);

# and derive calculated metrics
auto profit <- ext_list_price - ext_wholesale_cost - ext_discount_amount + ext_sales_price;

# bind defined values to a table 
datasource store_sales (
    SS_SOLD_DATE_SK: date.id,
    SS_SOLD_TIME_SK: time.id,
    SS_CUSTOMER_SK: customer.id,
    SS_CDEMO_SK: customer_demographic.id,
    SS_TICKET_NUMBER: ticket_number,
    SS_ITEM_SK: item.id,
    SS_SALES_PRICE: sales_price,
    SS_LIST_PRICE: list_price,
    SS_EXT_SALES_PRICE: ext_sales_price,
    SS_EXT_LIST_PRICE: ext_list_price,
    SS_EXT_WHOLESALE_COST: ext_wholesale_cost,
    SS_EXT_DISCOUNT_AMT: ext_discount_amount,
    SS_NET_PROFIT: net_profit,
    SS_PROMO_SK: promotion.id,
    SS_QUANTITY: quantity,
    SS_COUPON_AMT: coupon_amt,
    SS_STORE_SK: store.id,
    SS_NET_PAID: net_paid,
)
# the grain is used to resolve queries appropriately
grain (ticket_number, item.id)
address memory.store_sales
;

# multiple tables can be used to source metrics
datasource store_returns(
    SR_RETURNED_DATE_SK: return_date.id,
    SR_RETURN_TIME_SK: return_time.id,
    SR_ITEM_SK: ~item.id,
    SR_CUSTOMER_SK: return_customer.id,
    SR_RETURN_AMT: return_amount,
    SR_TICKET_NUMBER: ~ticket_number,
    SR_STORE_SK: return_store.id,
    SR_NET_LOSS: return_net_loss,
    # capture if there is a row in this table
    bool(return_time.id): is_returned,
)
grain (ticket_number, item.id)
address memory.store_returns;

If you look at the top, there are imports. These are the key to reuse in Trilogy: they enable composition of models.

Returns by Different State

The same model can be bound in different contexts, like the customer model being reused across two customer sources. Checking SQL here will show four different tables, two of which are reused with different foreign key relations. You're picking up this data model quickly - nice work!

where store_sales.return_customer.state is not null 
    and store_sales.return_customer.state != store_sales.customer.state
select
    store_sales.customer.state,
    sum(store_sales.sales_price) as total_sales,
    store_sales.return_customer.state,
order by
    total_sales desc
limit 10;

In fact, if we drill into customers, we'll see that model itself has an import. That entire extended model has been imported twice: once for the customer who bought and once for the one that returned, enabling the same logic to be reused across both keys easily.

Beyond reuse, SQL can benefit from abstraction.

Datasources can be evolved and split transparently to a consuming query as you refactor your tables.

We can even use this to dynamically swap in aggregates when we compute a higher-level table with the same outputs (such as when a session runs a persist to store a query output).

import customer_demographic as demographics;

key id int;
unique property id.text_id string;
properties id (
    last_name string,              # Customer last name (not guaranteed unique; weak for identity)
    first_name string,             # Customer first name (display only; low analytical value alone)
    preferred_cust_flag string,    # 'Y'/'N' indicator for preferred/loyalty status (use for segmentation, LTV analysis)
    birth_day int,                 # Day of birth (1–31; combine with month/year for full DOB)
    birth_month int,               # Month of birth (1–12)
    birth_year int,                # Year of birth (used for age calculation, cohorting)
    birth_country string,          # Country of birth (demographic segmentation; may differ from residence)
    salutation string,             # Title (e.g., 'Mr.', 'Ms.', 'Dr.'; mostly presentation, sometimes demographic proxy)
    email_address string,          # Contact email (quasi-unique, but not reliable as a key; can change over time)
    login string,                  # Account username/login (more stable than email, often used for authentication)
    last_review_date string,       # Last date customer submitted a review (string in TPC-DS; should be cast to date)
                                   # Useful for engagement/recency metrics
);

# avoid having to recalculate this in queries
property id.full_name <- concat(salutation, ' ', first_name, ' ', last_name);

property id.birth_date <- cast(
    concat(cast(birth_year as string), 
        '/', cast(birth_month as string), 
        '/', cast(birth_day as string)
    ) as date
);



datasource customers (
    C_CUSTOMER_SK: id,
    C_CUSTOMER_ID: text_id,
    C_LAST_NAME: last_name,
    C_FIRST_NAME: first_name,
    C_CURRENT_ADDR_SK: address_id,
    C_CURRENT_CDEMO_SK: demographics.id,
    C_PREFERRED_CUST_FLAG: preferred_cust_flag,
    C_BIRTH_COUNTRY: birth_country,
    C_SALUTATION: salutation,
    C_EMAIL_ADDRESS: email_address,
    C_BIRTH_DAY: birth_day,
    C_BIRTH_MONTH: birth_month,
    C_BIRTH_YEAR: birth_year,
    C_LOGIN: login,
    C_LAST_REVIEW_DATE_SK:last_review_date,
)
grain (id)
address memory.customer;

key address_id int;
properties address_id (
    street string,
    city string,
    state string?,
    zip string,
    county string,
    country string,
    gmt_offset float,
);


datasource customer_address(
    CA_ADDRESS_SK: address_id,
    CA_STREET_NAME: street,
    CA_CITY: city,
    CA_STATE: state, # Two-character state code; e.g., CA for California, MA for Massachusetts
    CA_ZIP: zip,
    CA_COUNTY: county,
    CA_COUNTRY:country,
)
grain (address_id)
address memory.customer_address;

Derived Concepts and Filtering

Let’s dig into derivation, aggregation, and filtering—starting with store_sales.

Examples

State Aggregation

As an example, if we move our aggregation up to the state level, we can reuse our total_sales and a new customer_count to calculate average sales.

select 
    store_sales.customer.state,
    sum(store_sales.sales_price) as total_sales,
    count(store_sales.customer.id) as customer_count,
    total_sales / customer_count as average_sales_per_customer
order by
    average_sales_per_customer desc
limit 10; 

Mixed aggregate

Often, we need to compare concepts at different levels of aggregation. Our queries so far have not specified a level, which means they are implicitly grouped by all dimensions in the query. We can combine different levels by explicitly aggregating "by" a concept. Tip: we've commented out some output fields with -- to streamline the result set; these are included in the projection but not returned in the final query.

import std.display;
select 
    --store_sales.customer.id,
    store_sales.customer.first_name,
    --store_sales.customer.state,
    sum(store_sales.sales_price) as total_sales,
    --sum(store_sales.sales_price) by store_sales.customer.state as total_state_sales,
    (total_sales / total_state_sales)::percent as fraction_of_total_state_sales
HAVING
    store_sales.customer.id is not null
order by
    fraction_of_total_state_sales desc
limit 10;

Filtering

The where clause comes first in idiomatic Trilogy, reducing the size of the query space before other operations are applied. (It'll accept a where clause after the select, if you prefer that). A where condition applies to the entire query.

import std.display;
WHERE store_sales.customer_demographic.education_status = 'College'
SELECT
    --store_sales.customer.id,
    store_sales.customer.first_name,
    store_sales.customer.state,
    sum(store_sales.sales_price) as total_sales,
    --sum(store_sales.sales_price) by store_sales.customer.state as total_state_sales,
    (total_sales / total_state_sales)::percent as fraction_of_total_state_sales
order by
    store_sales.customer.state asc,
    fraction_of_total_state_sales desc
limit 10;
    

Nuanced Filter

When that's too blunt an instrument, selection can be further refined in two ways. A filtered concept can be created by using a where clause - "filter store_sales.sales_price where store_sales.customer_demographic.education_status = 'College'", for example - and the output of a selection can be filtered via the HAVING clause (just like SQL). So to see the percentage of all state sales that comes from college- educated people, we can leave the global space unfiltered, filter sales inline, and then further refine our output rows. Tip: the "?" syntax, e.g., store_sales.sales_price ? store_sales.customer_demographic.education_status = 'College', can provide more concise inline filtering if you're okay with shorthand!

import std.display;
SELECT
    store_sales.customer.state,
    store_sales.customer_demographic.education_status,
    sum(filter store_sales.sales_price where store_sales.customer_demographic.education_status = 'College') as college_sales,
    sum(store_sales.sales_price ? store_sales.customer_demographic.education_status = 'College') as college_sales_alt,
    --sum(store_sales.sales_price ) by store_sales.customer.state as total_state_sales,
    (college_sales / total_state_sales)::percent as fraction_of_total_state_sales
HAVING
    store_sales.customer_demographic.education_status = 'College'
order by
    store_sales.customer.state asc,
    fraction_of_total_state_sales desc
limit 10;
    

Find the average per-state rank by total store sales of items

More complicated derivations can be done by creating a virtual collection of rows [like a CTE in SQL], called a rowset. A rowset is defined by prefixing "with <name> as" or "rowset <name> <-" before a normal select query and can then be queried directly to access the outputs under the alias of the rowset name. Rowsets can be thought of as reusable CTEs, though there are some differences - defined properties that are not used may not ever be materialized, and rowsets can be used in more places than CTEs. You shouldn't need to use rowsets as often as you need to use CTEs, but they're an option.

with ranked_states as
select 
    store_sales.item.name,
    store_sales.customer.state,
    rank store_sales.item.name
        over store_sales.customer.state 
        order by sum(store_sales.sales_price) by store_sales.item.name, store_sales.customer.state  desc 
    as sales_rank;

select 
    ranked_states.store_sales.item.name,
    avg(ranked_states.sales_rank)-> avg_sales_rank,
    max(ranked_states.sales_rank)-> max_sales_rank,
    min(ranked_states.sales_rank)-> min_sales_rank
order by 
    avg_sales_rank desc
limit 10
;

Sandbox

Ready? Let's have you try out some queries!

Tips

For basic queries, Trilogy should be almost identical to SQL. When in doubt, try the SQL syntax!

Can you answer these questions? (click to show a possible answer)

• What were the sales by year in the state of CA? >
• What was the average yearly sales for the state of CA? >
• Which customer demographics had the most store sales in 2001, and what were the sales within that demographic in Hawaii? >
• What was the most popular item bought by customers in Massachusetts and Kentucky by customers with more than 5 orders? >

The following concepts are predefined for you and can be referenced by name.

Concept Search

There are more concepts available than we can reasonably show you. Search this box for inspiration:

Warning

Stick to querying concepts with the same root for now—for example, store_sales.x and store_sales.y. Or, if you're feeling adventurous, read on to find out how to merge models to enable cross-namespace querying.

select 1 -> one;

Extending Models

We've had some fun with store sales, but what about the rest of the dataset?

A typical Trilogy script will be based on importing existing models - and these models may themselves have imports. We've seen how store_sales ingests the same customer model under different role-playing dimensions.

But sometimes you need to merge data in unexpected ways. SQL is flexible; you can pick arbitrary keys to merge on.

If you have two models you want to connect, how can you do that?

Hello Joins My Old Friend

Trilogy does have joins. But if you don't have tables, what are joins?

In Trilogy, a join represents a semantic set operation. You can declare two independent concepts to be either two partial sets of a larger whole - a full outer join - which we call a "union join".

Or you can declare that one set of concepts is a partial subset of the other—a left outer join, which we call "subset".

You may also - for diagnostic or ad hoc reasons - want to connect fields on unnatural keys. This provides a query-scoped means to do that.

Tips

Joins can operate on expressions! union join x + 1 = x is a valid way to produce combinations across values within one concept range.

Since there is no FROM clause, joins in Trilogy are traditionally floated to the top (alongside where) as they represent a mutation of the search space that the select is projecting out of.

As an example, you probably didn't have your semantic model set up to answer the question "what customers in this income range in Edgewood have the same demographic grouping as people who returned items they bought in our physical stores?"

A join lets you easily express this ad hoc intersection, just as you would with SQL.

.trilogy

import customer as customer;
import store_sales as ss;

where
    customer.current_address.city = 'Edgewood'
    and ss.return_customer_demographic.sk is not null
    and customer.current_household_demographics.income_band.lower_bound between 38128 and 88128 
    and ss.is_returned
subset join ss.return_customer_demographic.sk = customer.current_demographics.sk
select
    --customer.sk,
    customer.id,
    concat(coalesce(customer.last_name, ''), ', ', coalesce(customer.first_name, '')) as customername,
    --ss.ticket_number,
    --ss.item.sk,
order by
    customer.id asc nulls first
limit 100
;

Tips

Query-level joins persist just for the scope of a query. They are useful exploratory tools, but when you are structuring models for consumption you'll typically want to avoid them in favor of upfront semantic equivalence modeling and partial bindings.

Model Merges

If a concept connection should persist in a reusable way beyond a query, you can use a model-level merge. Model-level merges are conceptually identical to joins, but apply by default to all selects using the model and have modified syntax as standalone statements.

This tells Trilogy that these two fields are the "same". For example, if you have a sales dataset and a holidays dataset, they might have the following fields:

Sales: 'order_date', 'ship_date', 'returned_date', 'order_id'

Holidays: 'date', 'holiday_name'

You want to know what sales were ordered on holidays - you would merge the holidays date into the sales.order_date, and you could now easily query select holiday_name, count(order_id).

If you wanted to see orders that shipped on a holiday, you'd merge them on ship date - and if you wanted to be able to query both, you could import the holidays dataset under two different names and merge them independently.

Model merges use the same modifier syntax as dataset bindings: merge a into ~b; means that a is a subset of b (similar to a subset join).

Tips

Uniquely, model merges support merge a into b; as an exact equivalence operation—anything that requests a or b can be satisfied from tables bound to both. Be careful with this! Two concepts need to have exactly identical ranges for this to be valid.

Merge on one: MERGE <concept1> into <modifiers?><concept2>

Merge on many: MERGE <namespace1>.* into <modifiers?><namespace2>.*

Let's try both out:

Union Join

A UNION JOIN connects otherwise unrelated concepts for one query and preserves values from both domains. Here, store and web sales are joined by year, and coalesce produces one reporting-year column.

UNION JOIN web_sales.date.year = store_sales.date.year
SELECT
    coalesce(store_sales.date.year, web_sales.date.year) as report_year,
    count(store_sales.ticket_number) as store_order_count,
    count(web_sales.order_number) as web_order_count
HAVING
    store_order_count>0 and web_order_count>0
order by report_year asc;

Model Merge

A standalone MERGE clause can be used to combine two (or all) concepts across two models, enabling them to be queried seamlessly. The syntax is merge <left> into <modifiers?><right>, where the left and right are the concepts to be merged. The ~ symbol in this example is an optional modifier to indicate that the values being merged in might not be complete - for example, not every date might be in store_sales.date - and so querying for the root concept should use a left outer join across to the other models.

MERGE store_sales.date.* into ~date.*;
MERGE web_sales.date.* into ~date.*;

SELECT
    date.year,
    count(web_sales.order_number) as web_order_count,
    count(store_sales.ticket_number) as store_order_count
HAVING
    web_order_count>0 or store_order_count>0
ORDER BY 
    date.year asc
LIMIT 100;

Tips

Multiple merge statements can be defined between two models; queries will merge across as many concepts as are referenced in the query.

Saving Results / ETL

Imagine you want to materialize a query to power a dashboard. The preferred pattern is to describe that result as a managed datasource and let Trilogy decide when it needs to be rebuilt.

Mark externally managed, canonical inputs as root. For each derived datasource, use freshness by to identify a watermark Trilogy can compare with its upstream inputs. The datasource remains part of the semantic model, while its physical table is treated as a cache that can be recreated whenever the source watermark advances.

Tips

Trilogy also offers imperative updates and external calls; you can mix and match declarative, asset-based refreshes and explicit execution, depending on what fits your use case best.

key customer_id int;
property customer_id.sold_at datetime;
property <customer_id, sold_at>.sales_price float;

root datasource raw_sales (
    customer_id,
    sold_at,
    sales_price
)
grain (customer_id, sold_at)
address raw.sales;

auto latest_sale <- max(sold_at) by customer_id;
auto total_sales <- sum(sales_price) by customer_id;

datasource customer_sales (
    customer_id,
    latest_sale,
    total_sales
)
grain (customer_id)
address analytics.customer_sales
freshness by latest_sale;

Run refresh against one file or a directory of models:

trilogy refresh .
trilogy refresh . --dry-run
trilogy refresh . --interactive

refresh scans the model dependency graph, compares datasource watermarks, and rebuilds only stale managed assets. For append-oriented models, use incremental by instead of freshness by.

Tips

In practice, most warehouses will have a finite set of 'roots' and a number of caches derived from them that are refreshed on some cadence to drive reporting, analytics, and performance. Trilogy allows you to explicitly define those caches as datasources and manage them through the refresh command.

persist is still useful when you intentionally want to save the output of a one-off query. For repeatable pipelines, prefer a managed datasource plus refresh: the definition, lineage, and freshness policy then live together in the model.

Agents

The topic du jour. We won't disappoint: Trilogy works great with agents.

Just point Claude Code or any other harness at the Trilogy CLI. trilogy agent-info is designed to give it a self-contained guide, and the global --output-format json --agent options provide machine-readable output with agent-appropriate failure behavior. Studio also offers MCP servers for clients that prefer a tool protocol over a subprocess.

The CLI also includes a native agent loop with a curated Trilogy toolset. From a configured Trilogy workspace, pass it an outcome rather than a single query:

trilogy agent "analyze sales trends and create a dashboard"
trilogy agent -i "investigate the drop in web sales"

Conversations are saved by default. The CLI prints a session ID when it returns control, so a later command can continue with the same context:

trilogy agent --list-sessions
trilogy agent --resume last "now break that out by month"
trilogy agent -r <session-id> "chart the result"

Provider and model defaults belong in the [agent] section of trilogy.toml; --provider and --model override them for one invocation. API keys are read from the provider's environment variable. Add context files with --context <path>, use --no-save for an ephemeral session, and run trilogy agent-info for the complete agent-oriented CLI and language reference.

You can experiment with querying the TPC-DS dataset - as a one-shot query - below!

Show me store sales by year

Want to Learn More?

For a deeper dive into the language and philosophy, head over to concepts.

For an assortment of related reading, head over to the blog page.

For the background on why Trilogy exists, head over to the thesis page.

Can't get enough of benchmarks? Head over to the tpc-ds blog.

For more details on the Trilogy ecosystem, go to the product pages:

• Trilogy-NLP
• Transform
• Studio