Database Datasources

Database datasources bind concepts to tables or queries in a connected database engine (e.g. DuckDB, BigQuery, Snowflake, Postgres).

Table Address

The most common form — points to a physical table in the connected database.

key customer_id int;
property customer_id.name string;
property customer_id.account_balance float;

datasource customers (
    c_custkey: customer_id,
    c_name: name,
    c_acctbal: account_balance,
)
grain (customer_id)
address database.customers;

The address format is schema.table (or database.schema.table for engines that support three-part names).

Identifiers can be quoted with backticks if they contain spaces or special characters. abc.def-235$$-syntax.table.

Inline Query

A triple-quoted SQL block can be used as the datasource body. Useful for views, unions, or bespoke SQL that is not worth materialising.

datasource recent_orders (
    order_id,
    customer_id,
    total,
)
grain (order_id)
query '''
    select order_id, customer_id, total
    from orders
    where created_at >= current_date - interval 90 day
''';

Tips

Inline queries are useful for experimentation, injecting literal values, and working around database-specific SQL syntax that Trilogy does not yet model.

SQL File Binding

A .sql file located relative to the current model file can be used in place of an inline query. The same rules apply — no trailing semicolon inside the file.

datasource customer_ltv (
    customer_id,
    lifetime_value,
)
grain (customer_id)
file './customer_ltv.sql';

Incrementality

Database datasources support incremental refresh via a freshness by clause, which names the concept used as a high-water mark.

datasource events (
    event_id,
    event_time,
    event_type,
)
grain (event_id)
address analytics.events
freshness by event_time;

When trilogy refresh is run, Trilogy checks the current maximum value of event_time in the target and only processes new rows.