Thesis

Trilogy adds a semantic layer inline to SQL, bridging the gap between semantic layers for access - Looker, Tableau, AtScale, etc - and the authoring/exploration layer. Trilogy looks like SQL; see below for a side by side of a TCP-DS query in SQL and Trilogy.

Trilogy

import store_sales as store_sales;
import item as item;

merge store_sales.item.id into item.id; # merge models for this query to get avg price of all items, not just sold items

SELECT
    store_sales.customer.state,
    count(store_sales.customer.id) as customer_count
WHERE
    store_sales.date.year=2001 
    and store_sales.date.month_of_year=1 
    and store_sales.item.current_price > 1.2 * avg(item.current_price) by item.category
HAVING
    customer_count>10
ORDER by
    customer_count asc nulls first,
    store_sales.customer.state asc nulls first
;

SQL

SELECT a.ca_state state,
       count(*) cnt
FROM customer_address a ,
     customer c ,
     store_sales s ,
     date_dim d ,
     item i
WHERE a.ca_address_sk = c.c_current_addr_sk
  AND c.c_customer_sk = s.ss_customer_sk
  AND s.ss_sold_date_sk = d.d_date_sk
  AND s.ss_item_sk = i.i_item_sk
  AND 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 cnt NULLS FIRST,
         a.ca_state NULLS FIRST
LIMIT 100;

So why not just use SQL?

SQL is fantastic.

SQL has been the de-facto language for working with data for decades. Data professionals can use a common, declarative syntax to interact with anything from local file based databases to global distributed compute clusters.

But SQL solves the wrong problem for the modern data stack.

SQL is a declarative language for reading and manipulating data in tables in SQL databases. This is a perfect fit for an application interacting with a datastore.

But in data warehouses, a table is a leaky abstraction. Users don't care about tables; tables are a means to an end. They want the data, and the table is a detail. Tables will be replicated; aggregated; cached - and the user spends all their time on the container, not the product.

TIP

SQL is a language for getting data out of tables in a databaase. Trilogy is a language for getting/transforming data in a [warehouse/lake/mart], with all the evolution, deprecation, and change that implies. The tables will change, but your query doesn't need to.

Trilogy puts the answers first

An intuitive query for data should be oriented around the outputs, not where it happens to be.

Seeing revenue by product line is a goal; the table that contains the products and the table that contains revenue are implementation details.

This is what you want to see in your output:

select
    product_line,
    revenue,
    city_name
;

But this is what you have to write

SELECT
    product_line,
    sum(revenue),
    city_name
FROM fact_revenue_latest
INNER JOIN dim_city on fact_revenue_latest.city_id = dim_city.city_id
GROUP BY 
    product_line,
    city_name

Over time, growing data teams tend towards SQL sprawl - duplicative, hard to follow, brittle adhoc scripts and pipelines - which are critical to the company. Fortune 500 companies spend millions of dollars trying to reverse engineer the original intent of SQL to document dataflow or lineage, or to refactor business logic when moving to a new database.

How Trilogy solves this

Trilogy separates declared conceptual manipulation (ex: [Profit] = [Revenue] - [Cost]) from the database that stores columns and runs queries. This semantic layer isn't a new concept, but Trilogy puts at close as possible to the SQL itself, in a familiar form - you define the semantic layer with the same language you use to query it, meaning adhoc extension and iteration is easy.

These concepts and their derivation are strongly typed and can be statically analyzed and tested against a given set of datasources to prove the correctness for a given expression of business logic. These two definitions - the business logic and the access layer - can then be independently evolved and validated over time.

SQL

USE AdventureWorks;

SELECT 
    t.Name, 
    SUM(s.SubTotal) AS [Sub Total],
    STR(Sum([TaxAmt])) AS [Total Taxes],
    STR(Sum([TotalDue])) AS [Total Sales]
FROM Sales.SalesOrderHeader AS s
    INNER JOIN Sales.SalesTerritory as t ON s.TerritoryID = t.TerritoryID
GROUP BY 
    t.Name
ORDER BY 
    t.Name

Trilogy

import concepts.sales as sales;

select
    sales.territory_name,
    sales.sub_total,
    sales.total_taxes,
    sales.total_sales,
order by
    sales.territory_name desc;

How does it work?

The example above cheats a little - the statement import concepts.sales as sales; is bringing in a model definition.

As a semantic layer, Trilogy requires some up-front binding to the database before the first query can be run. The cost to model the data is incurred infrequently, and then the savings are amortized over every single user and query.

TIP

Models can be defined, extended and bound to a table in-line; you don't need a separate file/definition to get started. Unlike other semantic layers, Trilogy supports - and encourages - adhoc extension and iteration.

Read me in the concepts and references section to learn how Trilogy works under the hood, and the nuances of query design and setup.

Usage

Trilogy is designed to be easy to learn and use, and to be able to be incrementally adopted. It can be run directly as a CLI, in a GUI, or compiled to SQL and run in standard SQL tooling.

Head over to the demo to see how this semantic layer is defined and run some example queries.