Datasource Statements

Datasource statements are used to both define sources of data - the roots of a model - and to define targets for writing data back out.

Syntax

[root] [template] [partial] datasource name [extends base, ...] (bindings)
[grain (...)] [complete where condition]
[address ... | query '''...''' | file `...`]
[where condition] [incremental by ... | freshness by ...] [within ...]
[refresh `...`] [partition by ...] [assert condition ...] [state ...];

Concrete datasources require a location, either declared or inherited. A template datasource is reusable configuration and has no physical location.

Example

import std.money;
import nation as nation;
key id int;
property id.name string;
property id.address string;
property id.phone string;
property id.account_balance float::usd;
property id.market_segment string; # Capitalized; one of BUILDING | FURNITURE | MACHINERY | AUTOMOBILE | HOUSEHOLD
property id.comment string;
datasource customers (
    c_custkey:id,
    c_name:name,
    c_address:address,
    c_nationkey:nation.id,
    c_phone:phone,
    c_acctbal:account_balance,
    c_mktsegment:market_segment,
    c_comment:comment
)
grain (id)
address memory.customer;

Components

Identifier

The named alias of the datasource is used if the datasource is referenced in a persist, validate, or publish statement.

Column Assignment List

The column assignment list contains the binding of physical columns on the datasource to concepts.

A binding can take one of 3 forms:

customers(
    # a bare binding represents a mapping from "id" on the table to "id" as a concept
    id,
    # a colon binding maps "name" on the table to "user_name" as a concept
    name: user_name,
    # a function binding can reference a previously bound concept
    UPPER(user_name): upper_name,
    # a rawsql binding can reference a sql expression
)

Grain Clause

The grain clause defines unique identifiers for rows in the table. (A primary key, or composite primary key).

An empty grain means the table is a "heap" - there is nothing that uniquely identifies a row.

Queries on heaps will typically need to group before using any data.

Address/Query

Datasources can be bound directly to tables, raw SQL scripts, or sql or python files.

See also file datasources and Python datasources for refresh and execution options.

Table

The standard approach.

address database.my_fun_table

Query Binding

Triple quotes, surrounding a sql statement. Do not include semicolons or other statement terminators.

query '''
select fun_data
'''

Tips

Query sources are very useful for experimentation, injecting in values, and bespoke database SQL syntax

Table-Function Binding

A datasource address can call a database table function. Literal arguments are fixed; concept arguments are supplied by equality filters in the query:

key latitude float;
key longitude float;
key day int;
property <latitude, longitude, day>.temperature float;
datasource forecast (latitude, longitude, day, temperature)
grain (latitude, longitude, day)
address forecast_fn(latitude, longitude, unit := 'c');

where latitude = 40.0 and longitude = -73.0
select day, temperature;

This assumes forecast_fn exists in the target database. Bound arguments must name concepts in the datasource's bindings and each needs one pinned literal value. A range filter or a disjunction does not supply that argument; missing or conflicting values fail SQL generation. Named arguments use :=. Availability and table-function syntax depend on the target database.

File Binding

A SQL binding is equivalent to a query binding, but it will load the file from a relatively located sql script.

The same rules around terminators apply. The path is enclosed in backticks, not quotes.

file `./my_script.sql`

Python File Binding

For DuckDB, a python file with appropriate configuration can also be used as a source. The python file must be a UV style script (with appropriate headers) and emit a PyArrow table to stdout.

Enable enable_python_datasources = true under [engine.config] in trilogy.toml before running Python datasources. With the Python SDK, pass DuckDBConfig(enable_python_datasources=True) to the executor.

An example is given below.

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

import sys

import pyarrow as pa


def fibonacci(n: int) -> list[int]:
    if n <= 0:
        return []
    if n == 1:
        return [0]
    fibs = [0, 1]
    for _ in range(2, n):
        fibs.append(fibs[-1] + fibs[-2])
    return fibs


def emit(table: pa.Table) -> None:
    with pa.ipc.new_stream(sys.stdout.buffer, table.schema) as writer:
        writer.write_table(table)


if __name__ == "__main__":
    n = 20  # Keep small to avoid int overflow in pyarrow
    fibs = fibonacci(n)
    table = pa.table({"index": list(range(1, n + 1)), "fibonacci": fibs})
    emit(table)

It can then be used like

key fib_index int;
property fib_index.value int;

datasource fib_numbers(
    index:fib_index,
    fibonacci: value
)
grain (fib_index)
file `./fib.py`;


select
    sum(value) as total_fib;

Partial Sources and Scoped Completeness

Use ~concept on individual bindings that cover only part of the concept's domain. Use partial datasource when the whole source is a slice:

key record_id int;
property record_id.region string;
partial datasource eu_records (record_id, region)
grain (record_id)
complete where region = 'EU'
address eu_records;

complete where declares the scope in which this source is complete. It is not a filter applied to the stored rows; use where after the location if the source needs filtering. Queries must establish the appropriate scope to use a partial-only source. Validation checks containment when the columns needed to evaluate the claim are available. See Typing and Validation.

Combining Complete Partitions

When sibling sources together cover the declared discriminator domain, Trilogy can union them to answer a query over the full population:

key sale_id int;
property sale_id.market enum<string>['US', 'EU'];
partial datasource us_sales (sale_id, market)
grain (sale_id)
complete where market = 'US'
address us_sales;
partial datasource eu_sales (sale_id, market)
grain (sale_id)
complete where market = 'EU'
address eu_sales;
select sale_id, market;

The enum makes coverage provable: these two arms cover every permitted market. Two values of an unconstrained string would not establish complete coverage. Declared ranges can similarly establish a bounded domain. A query restricted to one arm's scope can use that arm alone.

Assertions

Assertions check a build before refresh or persist publishes it:

key record_id int;
property record_id.region string;
datasource records (record_id, region)
grain (record_id)
address records
assert count(record_id) >= 1
assert count(record_id) by region >= 0.75 * prior(count(record_id) by region)
;

prior(expr) evaluates the same expression on the previous asset. Every grouped aggregate in one assertion must use the same by grain, and the check may only read stored columns or row-level derivations of them. Here, each region must retain at least 75% of its previous row count. A disappearing group fails; a new group or unavailable prior baseline passes the relative check with a warning. Keep an absolute check for the first build.

A failed assertion prevents publication of the checked build. The storage backend determines the transaction or staging mechanism used. Partial targets with assertions need complete where to identify the population being checked.

validate datasource records; runs absolute checks against the published asset and reports prior-dependent checks as not run. The refresh command lists assertions in dry runs and supports an explicit per-datasource override for an expected change in population.

Templates and Composition

Share bindings, grain, and checks through a template and extends:

key item_id int;
def retains_rows(row_id, fraction = 0.75) ->
    count(row_id) >= fraction * prior(count(row_id));
template datasource guarded_items (item_id)
grain (item_id)
assert count(item_id) >= 1
assert @retains_rows(item_id)
;
datasource daily_items extends guarded_items ()
address daily_items;

Templates are never queried directly. A child can extend several templates or concrete datasources. Bindings and assertions accumulate; a child binding overrides the same inherited column. where and complete where conditions combine with AND. For single-valued settings such as grain or address, the child's value wins; conflicting base values require an explicit choice. Imported templates use the import alias, such as extends shared.guarded_items.