Sound familiar?

These are the problems we run into every day with the modern data stack. We built Trilogy to solve them. We made it open-source to see what you can build with it.

"I can't keep track of the latest table for where to get our KPIs"

— Everyone

With Trilogy's semantic layer, the database changes, but your quries don't. Stop dealing with data context rot.

"I'd like to add that drilldown to the dashboard, but it will make it too slow..."

— Dashboard Artist

Trilogy can automatically resolve drilldown against anything in the semantic model, seamlessly swapping from aggregate sources to your finest grain table.

"Our core processing has a ton of duplication..."

— Data Engineer

Mode your inputs and your outputs; let Trilogy build the middle. Avoid redundant ETL and slash costs.

How it actually works

1

Define concepts and sources

Foundations of your model; iterative and easy to adopt; the same syntax as you query in - and no YAML.

import station as start_station;
import station as end_station;

key ride_id int;
property ride_id.ride_start_time timestamp; # UTC start time
property ride_id.ride_end_time timestamp;   # UTC end time

auto ride_seconds <- date_diff(ride_start_time, ride_end_time, second);
auto ride_date <- date_trunc(ride_start_time, day);

datasource rides (
    ride_id: ride_id,
    start_time: ride_start_time,
    end_time: ride_end_time,
    start_station: start_station.id,
    end_station: end_station.id
)
grain (ride_id)
address rides;

# size our data
select count(ride_id) as ride_count; 

2

Query with the full flexibility of SQL

Fan out joins, foreign keys, and complex logic is resolved automatically through the model.

auto daily_rides <- count(ride_id) by ride_date;

SELECT
    day_of_week,
    rank start_station.id by avg(daily_rides) desc as daily_station_rank
HAVING
    daily_station_rank <5
;

3

Save data back

Support for the full data lifecycle.

PERSIST daily_ranking INTO tbl_dow_ranking FROM
SELECT
    day_of_week,
    start_station.id,
    daily_station_rank,
;

4

Reuse

Promote your new definitions up to your core model repos, deploy, and reuse everywhere.

import core_ride_metrics;

WHERE end_station.id in (start_station.id ? daily_station_rank <= 3)
SELECT
    ride_id,
    start_station.id,
    end_station.id,
    ride_seconds
ORDER BY
    ride_id asc
;

Install

SDK and CLI:

pip install pytrilogy

UI: Go to a hosted instance or run locally in docker.

Hello World

Save this command as hello_world.preql, then run: trilogy run hello_world.preql duckdb

# import from standard library / local files
# for types and fields resuse
import std.display;
# model logic is written first

# with a pure semantic model
const greeting <- 'Hello'; # comments describe fields
key noun string; # people, places, things

# that you bind to data
datasource things (
  thing:noun
)
grain (noun)
query '''
select unnest(['world', 'friend', 'visitor']) as thing
''';

# with reusable functions
def initcap(word)-> upper(substring(word, 1, 1)) || substring(word,2, len(word));

# and run queries in familiar SQL syntax
WHERE noun like '%world%' or noun = 'friend'
SELECT
  greeting || ' ' || @initcap(noun) || '!' as salutation,
  rank salutation by len(salutation) asc as brevity_rank,
  (len(salutation) / max(len(salutation)) by *)::float::percent as percent_of_longest
;