Thesis
Thesis
SQL isn't perfect. People come at it all the time. But it's been the enduring, undefeated champion of analytics. With good reason! SQL is the most natural, expressive way to do data operations. And that's why attempts to replace it have missed; they've been going for specific syntactical concerns - pipeline notation, dataframes, etc - from people that perhaps fundamentally don't like SQL trying to make it into something it's not. (there's room for some syntactic sugar - DuckDB has done well with friendly SQL)
We love SQL. And so we're going after what we think is really wrong with it for analytics - the tight coupling of semantic meaning to physical tables, joins, and storage details, forcing them to change in lockstep. SQL for transactional systems has it's own set of warts; for analytics, it's the sprawl and context rot that kills. Finding the table, writing the query, making sure it's efficient, redoing it the next month, detecting and handling upstream data drift; contracts, decentralization - that's the pain.
Trilogy solves this by making semantics first-class while preserving the declarative workflow that makes SQL powerful. Less duplicated logic, fewer decisions made with bad data, safer schema evolution, and more time spent on getting value from data instead of mechanical plumbing.
The Problem
SQL excels at defining relational algebra. That's critical, but not completely sufficient analytics needs. When you need an answer, which physical table currently stores the data is a chore; whether a metric now comes from a replicated, aggregated, or backfilled source is a landmine. You care about the meaning of the data and the transformations you want to express - those are relational, yes, but often unbound from a specific table. Do you use the cube or raw line data? V1 or V2?
Data teams hit walls with the same problems at scale:
- Business logic gets duplicated across dashboards, reports, notebooks, and ad hoc queries
- Lineage and governance become harder as logic spreads through raw SQL
- Schema changes break downstream work or produce silently stale results
- Grain mistakes and accidental duplication lead to bad decisions
- Teams spend too much time focusing on data infrastructure instead of acting on insight.
Teams can try to solve this with software engineering patterns, semantic layers, BI tools, or new query languages. But analytics has a constraint that application developers do not face in the same way: data has gravity. The physical layer matters. Warehouses, tables, partitions, and execution engines are real constraints, and SQL has been the best tool to deal with those - native to warehouses; meeting data where it is, and playing to their strengths.
The Solution
Trilogy separates those concerns.
Trilogy includes a lightweight built-in semantic language for defining business concepts directly alongside queries. That makes it possible to move intent, relationships, and grain out of repeated query text and into reusable definitions. Queries stay declarative and familiar, while the compiler handles the physical details that create so much SQL sprawl.
Trilogy compiles to raw SQL and works with your existing databases, so adoption does not require replacing your warehouse or committing to a closed execution model.
This gives you:
- Static typing
- Automatic join inference where relationships are explicitly modeled
- Grouping and filtering with grain awareness
- Null and aggregation safety
- Declarative asset creation and updates
- A path from exploration to production in the same language
Design Principles
- Declarative first — express the answer you want, not the warehouse plumbing needed to get it
- Close to SQL — preserve the familiarity and flexibility that made SQL durable
- Safe by default — model grain, nullability, relationships, and aggregation semantics explicitly
- Reusable — define logic once and use it across many queries and assets
- Incremental to adopt — start with one domain, one model, or one workflow without rebuilding your stack
What Does It Look Like?
Trilogy looks like SQL! That makes it easy to try, easy to adopt, easy for agents to write.
It just drops a few things that you always risked getting wrong anyway - what keys to join on; whether you need to handle nulls; and where the data should actually come from. You don't get any value out of writing those; they are just landmines you can get wrong.
We can remove that burden at the language level.
WHERE
SELECT
# FROM - moved to semantic layer
# JOIN - moved to semantic layer
# GROUP BY - resolved automatically
# QUALIFY - no special handling required for window functions
HAVING
ORDER BY
Tips
SQL is already declarative! You're relying on the database to optimize your query. Trilogy takes this further - write the business question without worrying about warehouse plumbing and be confident it will keep working. You can peek under the hood; we hope you never have to.
Deeper Example
# models can be imported and reused
import store_sales as store_sales;
# where clause idiomatically comes first, but can be run later for familiarity
WHERE
store_sales.date.year=2001
and store_sales.date.month_of_year=1
and store_sales.item.current_price > 1.2 *
avg(store_sales.item.current_price) by store_sales.item.category
# select looks normal
SELECT
store_sales.customer.state,
count(store_sales.customer.id) as customer_count
# having filters the output of select
HAVING
customer_count>10
# order by is normal
ORDER BY
customer_count asc nulls first,
store_sales.customer.state asc nulls first
LIMIT 100;
A Fair SQL Comparison
SELECT
a.ca_state AS state,
COUNT(*) AS customer_count
FROM customer_address a
INNER JOIN customer c
ON a.ca_address_sk = c.c_current_addr_sk
INNER JOIN store_sales s
ON c.c_customer_sk = s.ss_customer_sk
INNER JOIN date_dim d
ON s.ss_sold_date_sk = d.d_date_sk
INNER JOIN item i
ON s.ss_item_sk = i.i_item_sk
WHERE d.d_month_seq = (
SELECT DISTINCT d_month_seq
FROM date_dim
WHERE d_year = 2001
AND d_moy = 1
)
AND i.i_current_price > 1.2 * (
SELECT AVG(j.i_current_price)
FROM item j
WHERE j.i_category = i.i_category
)
GROUP BY
a.ca_state
HAVING COUNT(*) >= 10
ORDER BY
customer_count ASC NULLS FIRST,
a.ca_state ASC NULLS FIRST
LIMIT 100;
Not bad, honestly! SQL is designed to read easily, and it mostly succeeds.
What we can drop are:
- Physical tables and join paths
- Repeated subquery structure
- Manual grouping mechanics
- Logic that is hard to share, review, and evolve safely
And bonus: if we can satisfy a query from an aggregate, we can do that automatically with the same semantic layer.
# what do we need to know?
key order_id int;
properties order_id {
order_time datetime # placed time
}
key product_id string; # SKU; ABC-123 for example
properties <order_id,product_id> {
qty float, # number ordered
}
# how is it stored
datasource orders (
id: order_id
submit_time: order_time
)
grain (order_id)
address tbl_order;
# check our bindings; the physical layout is checked against logical model
validate orders;
# Joins are implicitly based on shared keys
datasource orders_items (
order_id
product_id,
qty,
)
grain (order_id)
address tbl_order_items_final;
# queries do not need to specify tables
select
order_time::date as order_date,
avg(sum(qty) by order_id) avg_order_product_count,
avg(sum(qty) by order_id, product_id) avg_order_per_product_count
;
# refactor when convenient
datasource order_denormalized (
order_id
order_time
product_id,
qty,
)
grain (
order_id, product_id
)
address tbl_order_denormalized;
# build our table
refresh order_denormalized;
# same query, no joins needed, will hit denormalized.
select
order_time::date as order_date,
avg(sum(qty) by order_id) avg_order_product_count,
avg(sum(qty) by order_id, product_id) avg_order_per_product_count
;
Why don't we all just use SQL, again?
SQL is fantastic. It is portable, declarative, expressive, and deeply integrated into every serious data system, from DuckDB to distributed clusters spanning the world. It's not going anywhere.
But if you're doing data work on a data warehouse, you can move faster. That's what Trilogy offers.
Other Options
Many tools recognize the same pain, but struggle in similar ways:
- They move too far away from SQL, losing the syntax everyone knows
- They treat semantics as an external modeling workflow rather than a core part of exploration that can organically evolve to production
- They improve syntax - such as moving the table earlier, or adding a dot - without really improving reuse, safety, or schema resilience
- They stay focused on metrics or BI use cases rather than the broader surface area of analytics logic
Trilogy's bet is different:
- stay close to SQL
- keep the workflow declarative
- make semantics explicit
- let modeling and querying happen together
Related Projects
We're not alone in thinking SQL can be improved. Related projects include:
Trilogy stands apart by staying close to SQL while making semantics, reuse, and safety more central to the language itself.
How do you get started?
Using Trilogy starts small: define a thin semantic layer around one domain, workflow, or dataset. Use our import tools to bootstrap that. Then start running queries as you, a user, or you, an agent. Tweak and improve the model. Share it with others.
You do not need to model everything up front.
You can start close to the consumption layer, use Trilogy for query authoring and reusable definitions, and then push backward into ETL or transformation workflows over time. At every step, you can integrate with the systems you already use.
Unlike most semantic layers, Trilogy does not require a completely separate modeling workflow before you can do useful work. You can define and extend models inline as part of exploration.
You do not need a separate file, tool, or phase just to get started. You can model and query in one place.
Read more in the concepts and references section to learn how Trilogy works under the hood and the nuances of query design and setup.
Usage
You can: