Skip to content

Datasets, Joins & Filters

Each [[templates.scope.datasets]] entry loads one table or file from the scope’s datasource into the request’s environment. Together, its columns, joins and filters decide exactly what is copied. Joins and filters run at the source: only matching rows ever leave the datasource.

[[templates.scope.datasets]]
source = { kind = "table", name = "refund" }
columns = ["refund_id", "order_id", "created_on", "reason_code", "state", "amount"]
joins = [
{ source = { kind = "table", name = "orders" }, type = "inner", on = "refund.order_id = orders.order_id" },
]
filters.logic = "AND"
filters.conditions = [
{ raw = "state = 'APPROVED'" },
{ raw = "orders.channel IN ('WEB', 'APP')" },
{ raw = "created_on >= DATE [from_date] AND created_on <= DATE [to_date]" },
]
Field Required Notes
source.kind yes table, path or path_list (see below).
source.name yes The table name, S3 file path or SSH schema name, spelled exactly as in the datasource’s catalog.
columns yes The columns to load. Nothing else from the table or file is copied.
joins no Load-time joins that narrow which rows load.
filters no Row filters: logic plus a list of conditions.
dates no path_list only: the days of files to load (see below).
Datasource type kind name
Relational databases (Postgres, MySQL, MSSQL, Oracle, Snowflake, …) table The table name, e.g. refund or SHIPMENT. Use the casing the catalog shows.
File datasources (S3) path The file path relative to the datasource’s prefix, e.g. logs/auth-service.ndjson. One file.
SSH datasources path_list The schema’s table name, e.g. APP_LOGS. All the files that fit that schema load as one table. SSH only.

A kind that doesn’t suit the datasource makes the template fail its per-template check. On an SSH datasource, path fails with datasource "log-host" loads files through its schemas: use kind "path_list" with a schema name.

path loads one file from S3. An SSH datasource loads its files through its schemas, so an SSH dataset is always a path_list source and a path source is refused. A path_list source is an SSH schema: one catalog entry, named by the Table name the datasource accepts it under, loaded as one table from every file that fits it, whichever of the datasource’s Target patterns the files match (see Schemas In The Catalog, Dated Files and Loading Files Into A Request). The name is the schema’s, not a pattern, and a date token isn’t part of it. The name is matched without regard to case, and an earlier name of the schema still works after the schema is renamed. If the datasource doesn’t accept a schema of that name, creating a request from the template through the API fails with schema "APP_LOGS" not found in datasource "log-host"; in the dashboard a notice reads APP_LOGS was not found in log-host. Ask an administrator to update the template. and a request can’t be created from the template until it is updated (editing the template stays possible, and saving it removes that dataset). Use dates to load only some days of it:

source = { kind = "path_list", name = "APP_LOGS" }
columns = ["timestamp", "level", "message", "source_path", "file_date"]
dates = { from = "DATE [date_from]", to = "DATE [date_to]" }

dates takes a from and a to, both required and both included. Each is a YYYY-MM-DD date, fixed in the template, or a DATE [name] tag the requester fills in (see DATE parameters); from can’t be later than to. dates is only valid on a path_list source. Leave it out to load every file of the schema. A request created from the template keeps the exact list of files that matched when it was created. For how requesters pick dates, see Request Parameters For Dated Files.

For a table, the bare table name (SHIPMENT) is enough: the datasource’s configured schema decides where it is read from. On Oracle, names containing $ or # and mixed-case names (such as ShipmentLog) work too, as long as you copy them exactly as the catalog shows them.

An SSH dataset’s name is the Table name an administrator gave the schema in the datasource wizard, so the names in the wizard must be the names your templates use. The wizard only suggests a name taken from the pattern (such as APP_LOGS); if the suggestion differs from the name in your templates, rename the table on the wizard’s Columns step (see Configure SSH Datasources), or change name in the template. Importing a template doesn’t check the name; a request can’t be created until the datasource has a table of that name. After upgrading, when an administrator updates an SSH datasource and names its tables again, they must use the template’s names (see Updating An SSH Datasource After An Upgrade).

List every column a requester needs, plus every column the virtual table or a REF(...) reads, including join keys. The list is the boundary of the request: a column that isn’t listed is never copied into the environment.

Column names are case-sensitive in some engines. Copy them from the catalog. In a filter, wrap a case-exact or space-containing name in double quotes: "Order Status" = 4. Inside a TOML string, escape those quotes or write the string with single quotes: { raw = '"Order Status" = 4' }.

A join on a dataset only decides which of its rows load. The joined table’s columns are not added to the dataset, and the joined table is not queryable unless it is also listed as its own dataset.

joins = [
{ source = { kind = "table", name = "orders" }, type = "inner", on = "refund.order_id = orders.order_id" },
]
Field Notes
source The table to join, same shape as the dataset’s source.
type inner, left, right or cross.
on The join condition, using table.column names. Not used for cross.

Filters can then use the joined table’s columns, written table.column: orders.channel IN ('WEB', 'APP').

When every join is inner and the dataset loads only its own columns, the dataset is loaded as a semi-join: each of its rows loads at most once, however many joined rows match. Without that, a row matching three joined rows would load three times.

Pattern: Lookup tables that follow the main table

Section titled “Pattern: Lookup tables that follow the main table”

A common shape is one main table (refunds) filtered to a window, plus lookup tables (orders, shoppers) the requester needs alongside it. Loading the lookup tables in full would expose every order and shopper. Instead, give each lookup table an inner join back to the main table and repeat the main table’s filters, so it loads only the rows behind the selected refunds:

[[templates.scope.datasets]]
source = { kind = "table", name = "refund" }
columns = ["refund_id", "order_id", "created_on", "state", "amount"]
filters.logic = "AND"
filters.conditions = [
{ raw = "state = 'APPROVED'" },
{ raw = "created_on >= DATE [from_date] AND created_on <= DATE [to_date]" },
]
[[templates.scope.datasets]]
source = { kind = "table", name = "orders" }
columns = ["order_id", "shopper_id", "channel", "placed_on"]
joins = [
{ source = { kind = "table", name = "refund" }, type = "inner", on = "orders.order_id = refund.order_id" },
]
filters.logic = "AND"
filters.conditions = [
{ raw = "refund.state = 'APPROVED'" },
{ raw = "refund.created_on >= DATE [from_date] AND refund.created_on <= DATE [to_date]" },
]
[[templates.scope.datasets]]
source = { kind = "table", name = "shopper" }
columns = ["shopper_id", "shopper_ref", "region"]
joins = [
{ source = { kind = "table", name = "orders" }, type = "inner", on = "shopper.shopper_id = orders.shopper_id" },
{ source = { kind = "table", name = "refund" }, type = "inner", on = "refund.order_id = orders.order_id" },
]
filters.logic = "AND"
filters.conditions = [
{ raw = "refund.state = 'APPROVED'" },
{ raw = "refund.created_on >= DATE [from_date] AND refund.created_on <= DATE [to_date]" },
]

Joins can chain, as shopper → orders → refund does above. Each join’s on may use any table joined before it. Because all three datasets use the same [from_date] and [to_date], the requester enters the window once.

When the narrowing value comes from a dataset in a different datasource, a join can’t reach it. Use REF(...) instead.

filters.logic = "AND"
filters.conditions = [
{ raw = "channel IN ('WEB', 'APP')" },
{ raw = "coupon_code LIKE 'SPRING%'" },
{ raw = "status_code NOT IN (3, 8, 9)" },
{ raw = "placed_on >= DATE [from_date] AND placed_on <= DATE [to_date]" },
]
Field Notes
logic AND (every condition must hold) or OR (any condition). Set it explicitly. Use OR only when no condition holds a request parameter: parameter conditions are always added with AND. To express alternatives, prefer AND with one parenthesised OR condition.
conditions A list of conditions. Each is a raw expression (recommended) or a structured column / op / value.

A raw condition is a small SQL-like expression: a column, an operator and a value.

Operator Example
= != > >= < <= status_code = 4, state != 'DRAFT', total >= 100.50
IN / NOT IN status_code IN (1, 2, 4), region NOT IN ('NORTH', 'EAST')
LIKE coupon_code LIKE 'SPRING%'
IS NULL / IS NOT NULL cancelled_at IS NULL

Values can be:

  • numbers: 4, 100.50
  • quoted strings: 'APPROVED', 'WEB'
  • typed literals: DATE '2026-01-31', TEXT 'A-1042', TIMESTAMP '2026-03-14 09:05'
  • request parameters: DATE [from_date], TEXT [lookup_value], TIMESTAMP [incident_at] - 30m
  • dataset references, with IN and NOT IN only: member_no IN REF(member.member_no)

Combine comparisons with AND, OR and parentheses inside one condition. Wrap any condition that contains OR in parentheses, even when it is the only one. When a request is created in the dashboard, the conditions are joined into one expression, where AND binds tighter than OR, so an unwrapped a = 1 OR b = 2 next to another condition no longer means what it says:

filters.conditions = [
{ raw = "(username IN REF(member.username) OR client_ip IN REF(login_event.client_ip)) AND logged_at >= TIMESTAMP [incident_at] - 30m" },
]

DATE '...' is rendered for each engine (DATE '2026-01-31', or a CAST on MSSQL), so a template works the same against any database.

A column is a plain or dotted name (state, refund.state), never an expression: UPPER(state) = 'X' is rejected. Typed values and parameters are not allowed inside IN (...) lists.

A condition can also be written as fields instead of text:

filters.conditions = [
{ column = "status_code", op = "=", value = 4 },
]

Parameters, references, typed literals and AND/OR combinations are written as raw conditions, so the examples in this section use raw throughout.

With logic = "AND", a list of short conditions and a single long AND expression filter the same rows. Prefer the list:

  • It is easier to review and diff.
  • A condition holding only DATE parameters gets a Don’t filter by date (load all rows) checkbox on the request form. Keep each date window in its own condition so a requester can drop the window without dropping your other rules. See Request Parameters.