Script datasources

A SQL file supplies a query; a Python script supplies an Arrow stream. Both can expose the same concepts and return the same result. Complete local setup first.

SQL query file

Download sales.sql and sql-script.preql into the same folder.

select 1 as sale_id, 'store' as channel, 120.0 as amount
union all select 2, 'web', 80.0
union all select 3, 'store', 50.0

The SQL file contains a single query with no trailing semicolon: Trilogy embeds it as a subquery.

key sale_id int;
property sale_id.channel string;
property sale_id.amount float;

datasource sales (
    sale_id: sale_id,
    channel: channel,
    amount: amount
)
grain (sale_id)
file `./sales.sql`;

select channel, sum(amount) as revenue
order by channel asc;

trilogy run sql-script.preql duckdb

Python Arrow producer

Download sales.py, python-script.preql, and trilogy.toml into the same folder. Keep uv on your PATH.

#!/usr/bin/env -S uv run
# /// script
# requires-python = ">=3.11"
# dependencies = ["pyarrow"]
# ///
import sys

import pyarrow as pa

table = pa.table({
    "sale_id": [1, 2, 3],
    "channel": ["store", "web", "store"],
    "amount": [120.0, 80.0, 50.0],
})
with pa.ipc.new_stream(sys.stdout.buffer, table.schema) as writer:
    writer.write_table(table)

The script writes only Arrow IPC to stdout; send diagnostic messages to stderr. Its inline dependency block lets uv install PyArrow.

Enable script execution in trilogy.toml:

[engine]
dialect = "duckdb"

[engine.config]
enable_python_datasources = true

key sale_id int;
property sale_id.channel string;
property sale_id.amount float;

datasource sales (
    sale_id: sale_id,
    channel: channel,
    amount: amount
)
grain (sale_id)
file `./sales.py`;

select channel, sum(amount) as revenue
order by channel asc;

trilogy run python-script.preql duckdb --config trilogy.toml

Both recipes return:

channel	revenue
store	170
web	80

See Python datasource syntax for root sources and freshness probes.