Skip to main content

Installation

Installation

Quick Start

Install the package. cli adds the rich terminal experience, serve adds the local model server used by the UI.

pip install "pytrilogy[cli,serve]"

Or with uv, to put the trilogy command on your path without managing a virtualenv yourself:

uv tool install "pytrilogy[cli,serve]"

Then run a query. DuckDB ships with the package, so there is nothing else to configure:

trilogy run "SELECT 1 + 1 -> two;" duckdb

  two
 =====
  2

From here, try it on real data, write your first script, or jump straight into Trilogy Studio.

Python

Trilogy's reference implementation is in Python, installable as the pytrilogy package. There is also an IDE, Trilogy Studio, available as public hosting or a docker image.

# core library, includes DuckDB + CLI by default
pip install "pytrilogy[cli,serve]"
# for strictly library usage, the raw package has less CLI dependencies
pip install "pytrilogy"
# warehouse drivers, as needed
pip install "pytrilogy[bigquery]"
pip install "pytrilogy[snowflake]"
pip install "pytrilogy[postgres]"
# extras combine
pip install "pytrilogy[cli,serve,bigquery]"

Available extras: cli, serve, ai, analysis, report, bigquery, snowflake, postgres, odbc.

Once installed, the pytrilogy package can be used as a CLI or imported/used in other python interfaces.

Tips

Trilogy language files are suffixed .preql, such as my_model.preql.

Hello World - CLI

Save the following code in a file named hello.preql

key sentence_id int;
property sentence_id.word_one string; # comments after a definition
property sentence_id.word_two string; # are syntactic sugar for adding
property sentence_id.word_three string; # a description to it

# comments in other places are just comments

# define our datasources as queries in duckdb
datasource word_one(
    sentence: sentence_id,
    word:word_one
)
grain(sentence_id)
query '''
select 1 as sentence, 'Hello' as word
union all
select 2, 'Bonjour'
''';

datasource word_two(
    sentence: sentence_id,
    word:word_two
)
grain(sentence_id)
query '''
select 1 as sentence, 'World' as word
union all
select 2 as sentence, 'World'
''';

datasource word_three(
    sentence: sentence_id,
    word:word_three
)
grain(sentence_id)
query '''
select 1 as sentence, '!' as word
union all
select 2 as sentence, '!'
''';

# an actual select statement
SELECT
    --sentence_id,
    word_one || ' ' || word_two ||  word_three as hello_world, # outputs must be named, trailing commas are okay
WHERE
    sentence_id = 1
;
# semicolon termination for all statements

Run the following from the directory the file is in.

trilogy run hello.preql duckdb

Hello World - Python

Python scripts will use the following core models:

Environments, to manage the semantic models.

Dialects, to bind to particular backends.

Executors, to run queries.

The easiest way to get started is to use the Environment.from_file method to load a model from a file, and then use the Dialects.DUCK_DB.default_executor method to create an executor for the DuckDB backend. Note that a select query being parsed from a file will not run until explicitly called by an executor.

from trilogy import Environment, Dialects, Executor
from pathlib import Path

path = Path(__file__).parent / 'hello.preql'

env = Environment.from_file(path)


exec:Executor = Dialects.DUCK_DB.default_executor(environment=env)

results = exec.execute_text('''SELECT
	--sentence_id,
	word_one || ' ' || word_two as hello_world, # outputs must be named, trailing commas are okay
WHERE
	sentence_id = 1
;''')

for result_set in results:
    for row in result_set.fetchall():
        print(row)

Tips

Core imports for programmatic authoring can be found in trilogy.authoring - this is the stable public interface.

Real Data in Two Commands

1 + 1 isn't very interesting. trilogy ingest points the CLI at a file - local or remote, CSV, TSV, or Parquet - and writes a model for it.

We will use Seattle's daily weather from 2012 through 2015.

trilogy ingest "https://cdn.jsdelivr.net/npm/vega-datasets@3.2.1/data/seattle-weather.csv" duckdb

Trilogy reads the file, verifies that date is a unique grain, and infers types from the values:

Detected potential unique key combinations: [['date']]
Using verified unique grain: ['date']
Detected enum type for 'weather': enum<'drizzle', 'fog', 'rain', 'snow', 'sun'>

That lands in raw/seattle_weather.preql - trilogy ingest writes to raw/ next to the nearest trilogy.toml, or under the current directory when there is no config yet. Pass -o to send it somewhere else.

# Datasource ingested from https://cdn.jsdelivr.net/npm/vega-datasets@3.2.1/data/seattle-weather.csv
key date date;

properties date (
    precipitation float,
    temp_max float,
    temp_min float,
    wind float,
    weather enum<string>['drizzle', 'fog', 'rain', 'snow', 'sun'],
);

root datasource seattle_weather (
    date,
    precipitation,
    temp_max,
    temp_min,
    wind,
    weather,
)
grain (date)
file `https://cdn.jsdelivr.net/npm/vega-datasets@3.2.1/data/seattle-weather.csv`;

Now we can answer the question - does it rain in Seattle?

--import prepends the import so an inline query can reach the model:

trilogy run --import raw.seattle_weather "select weather, count(date) as days order by days desc;" duckdb

  weather │ days
 ═════════╪══════
  rain    │ 641
  sun     │ 640
  fog     │ 101
  drizzle │ 53
  snow    │ 26

It sure does, almost half the time.

Some callouts here: because the grain was verified, count(date) is a guaranteed correct row count.

weather is a typed enum, so will catch types before you hit the DB.

trilogy run --import raw.seattle_weather "select count(date ? weather = 'sunny') as sunny_days;" duckdb

Syntax error in stdin: Comparison `local.weather = 'sunny'` can never match
enum field 'local.weather', which contains only these values: 'drizzle',
'fog', 'rain', 'snow', 'sun'. It is always false and should be removed.

Push a little further with a filtered aggregate. The ? operator filters a single expression's input, so differently-filtered measures can share one query:

trilogy run --import raw.seattle_weather "select date.year, count(date ? weather = 'sun') as sunny_days, round(avg(temp_max),1) as avg_high order by date.year asc;" duckdb

  date_year │ sunny_days │ avg_high
 ═══════════╪════════════╪══════════
  2012      │ 118        │ 15.3
  2013      │ 173        │ 16.1
  2014      │ 187        │ 17.0
  2015      │ 162        │ 17.4

See the query examples for more of this.

Trilogy Studio

Trilogy Studio is the browser-based IDE for Trilogy - editor, autocomplete, charts, and dashboards over DuckDB, BigQuery, or Snowflake.

Three options for usage are below, from simplest to most customized.

Hosted

Nothing to install. Open the public instance, or the demo to land in a preloaded model with the guided tour.

Serve a Local Model

If your model already lives on disk, the CLI can serve it to the hosted UI:

trilogy serve <directory>

This blocks, serving the directory as a web service and opening the Studio app pointed at it.

Tips

This loads the UI over the network. If you run your own Studio instance, point at it by setting the studio URL in trilogy.toml.

Docker

For a completely offline setup - or to pin a version - build the image from the trilogy-studio-core repo. The container runs the FastAPI resolution service and statically serves the frontend, with no telemetry enabled by default.

git clone https://github.com/trilogy-data/trilogy-studio-core.git
cd trilogy-studio-core
docker build -t trilogy-studio:latest .
docker run -p 8080:80 trilogy-studio:latest

On PowerShell, chain with ; rather than &&:

docker build -t trilogy-studio:latest . ; docker run -p 8080:80 trilogy-studio:latest

The container listens on port 80; the command above publishes it as http://localhost:8080. Change the left-hand side of -p to use a different host port.

Tips

There is no prebuilt image on a public registry yet, so build from a checkout of the repo.

Public Models

A curated list of public models is available via the trilogy-public-models repository. These models can be directly imported and used to query these common data models.

pip install trilogy-public-models


from trilogy_public_models.bigquery import usa_names

# alternative
from trilogy_public_models import get_executor

engine = get_executor('bigquery.usa_names')

Next Steps