Ingest

trilogy ingest bootstraps a model directly from data you already have. It connects to a warehouse or reads a file, introspects the schema, and writes Trilogy datasource definitions to disk. This is typically the first thing you run in a new environment, and the fastest way to add a new table to an existing model.

Ingest is a scaffolding step, not a runtime dependency. It writes .preql files you own and edit afterwards - rerunning it overwrites the generated file, so keep hand edits in a separate file that imports the generated one.

Usage

trilogy ingest <sources> [dialect] [options] [conn_args...]

Arguments

Argument	Description
sources	Comma-separated list of warehouse table names or file paths/URLs. Required unless --all is passed.
dialect	Database dialect. Optional - falls back to engine.dialect in trilogy.toml, and defaults to duckdb when every source is a file.
conn_args	Connection arguments, as key value pairs or key=value tokens.

Options

Option	Description
-o, --output PATH	Output directory for generated scripts.
-s, --schema TEXT	Schema/database to ingest from. Table mode only.
--all	Ingest every table in the database. Table mode; omit sources.
--config PATH	Path to a trilogy.toml configuration file.
--fks TEXT	Explicit foreign keys, as table.column:ref_table.column, comma-separated.
--infer-level [off/fast/full]	Foreign-key inference. off disables it, fast matches on column names only, full also verifies matches by sniffing values. Default full.
-e, --env TEXT	Set env vars as KEY=VALUE, or pass an env file path.
--name TEXT	Override the generated datasource name. Single source only.

Where Output Lands

Generated files are written to raw/, resolved in this order:

1. --output if passed.
2. raw/ next to the --config file, if passed.
3. raw/ next to the nearest discovered trilogy.toml.
4. raw/ under the current working directory.

The directory is created if it does not exist. One file is written per source, named after the table or the file stem: raw/orders.preql.

Table Mode

Point ingest at tables in a configured warehouse. Connection arguments are key=value pairs - a bare connection string is not accepted.

# DuckDB file on disk
trilogy ingest orders,customers duckdb path=shop.duckdb

# Postgres, restricted to one schema, into a chosen directory
trilogy ingest customers postgres -s public -o raw/ host=localhost port=5432 username=app password=secret database=shop

With the dialect and connection set in trilogy.toml, the arguments collapse to just the table list:

trilogy ingest orders,customers

To pull in an entire database at once, use --all and omit the source list:

trilogy ingest --all

Tips

--all reads the dialect from trilogy.toml. If you need to pass the dialect positionally as well, pass an empty source list so the dialect is not consumed as a table name: trilogy ingest "" duckdb path=shop.duckdb --all.

Use trilogy database list to see what is available before choosing tables.

File Mode

Sources ending in .csv, .tsv, or .parquet are read as files rather than tables. Local paths and remote URLs both work - remote schemes are https://, http://, gs://, gcs://, s3://, az://, abfs://, and abfss://.

File ingest always runs through DuckDB, so the dialect argument is optional and DuckDB is selected automatically. Passing a non-DuckDB dialect alongside a file source is an error.

# Local CSV - dialect inferred
trilogy ingest ./data/orders.csv

# Remote parquet, with an explicit datasource name
trilogy ingest https://example.com/data/events.parquet --name events

# Public GCS bucket
trilogy ingest gs://my-bucket/sales.parquet -o raw/

Local paths are resolved to absolute paths in the generated file, so the model does not depend on the directory you happened to run ingest from.

Mixing tables and files in one call is supported under DuckDB, which can read attached tables and files through the same executor.

What Gets Generated

For an orders.csv with order_id, customer_id, amount, order_date, ingest produces:

# Datasource ingested from /abs/path/orders.csv
key order_id enum<bigint>[1, 2, 3];

properties order_id (
    customer_id enum<bigint>[10, 11],
    amount float,
    order_date date,
);

root datasource orders (
    order_id,
    customer_id,
    amount,
    order_date,
)
grain (order_id)
file `/abs/path/orders.csv`;

Three things are inferred along the way:

Grain. Ingest looks for column combinations that are unique across the sampled rows and picks the narrowest verified one, emitting it as key plus a grain (...) clause. A verified grain is what makes count(order_id) a guaranteed row count rather than an approximation.

Types. Column types come from the source schema. Low-cardinality string and integer columns are additionally narrowed to enum<...> types, which lets Trilogy reject impossible comparisons at parse time instead of returning an empty result from the warehouse.

Traits. Recognizable columns pick up semantic traits - a city column becomes string::city and pulls in import std.geography;.

Generated datasources are marked root, meaning source of truth. That is what trilogy refresh compares derived assets against.

Foreign Keys

Linking datasources is what turns a pile of tables into a model that can answer cross-table questions without hand-written joins.

By default (--infer-level full) ingest infers foreign keys automatically: candidate matches are found by column name, then verified by sniffing values for overlap. fast skips the value check; off disables inference entirely.

Explicit --fks always override inferred relationships:

trilogy ingest orders,customers duckdb path=shop.duckdb --fks orders.customer_id:customers.customer_id

Either way, the child datasource gains an import and the column is rebound to the parent's key:

# Datasource ingested from orders
import customers as customers;

key order_id enum<bigint>[1, 2, 3];

properties order_id (
    amount float,
    order_date date,
);

root datasource orders (
    order_id,
    customer_id: customers.customer_id,
    amount,
    order_date,
)
grain (order_id)
address orders;

Note that customer_id moved out of the local properties - it is now customers.customer_id, so a query joining orders to customer attributes resolves without a join clause.

Each relationship is reported as it is applied:

FK orders.customer_id -> customers.customer_id [inferred (exact), overlap=100%, complete]

complete means the child covers every parent key; partial means it may not, which Trilogy accounts for when choosing join strategies. Explicit --fks start out conservative and are promoted to complete when value sniffing confirms full coverage.

Full Example

Bootstrapping a TPC-DS model, wiring store_sales to each of its dimensions:

trilogy ingest store_sales,date_dim,time_dim,item,customer,customer_demographics,household_demographics,customer_address,store,promotion --fks=store_sales.ss_sold_date_sk:date_dim.d_date_sk,store_sales.ss_sold_time_sk:time_dim.t_time_sk,store_sales.ss_item_sk:item.i_item_sk,store_sales.ss_customer_sk:customer.c_customer_sk,store_sales.ss_cdemo_sk:customer_demographics.cd_demo_sk,store_sales.ss_hdemo_sk:household_demographics.hd_demo_sk,store_sales.ss_addr_sk:customer_address.ca_address_sk,store_sales.ss_store_sk:store.s_store_sk,store_sales.ss_promo_sk:promotion.p_promo_sk

After Ingesting

Query the generated model straight away with --import, which prepends an import to an inline query:

trilogy run --import raw.orders "select customers.name, sum(amount) as revenue order by revenue desc;" duckdb

Or inspect what the model exposes:

trilogy explore raw/orders.preql

Troubleshooting

Connection argument '...' has no value - connection arguments must be key value or key=value. A bare connection string is not accepted; use path=shop.duckdb or host=... port=....

File ingest requires the duckdb dialect - a file source was combined with a non-DuckDB dialect. Drop the dialect argument and let it default.

Pass either explicit SOURCES or --all, not both - a positional dialect was read as the source list. Pass "" as the sources argument, or set the dialect in trilogy.toml.

--name can only be set when ingesting a single source - --name renames one datasource; drop it when ingesting several.

No foreign keys detected - column names may not match across tables. Pass them explicitly with --fks.