Skip to content

Worked Examples

These templates show the shapes most template files take. Copy one and change the datasource, table and column names to match your catalog.

Example 1: Support Investigation For One Member

Section titled “Example 1: Support Investigation For One Member”

A support engineer gets a ticket: a member couldn’t sign in at a given time. The template asks for one lookup value and the incident time, then loads:

  1. The member’s account, matched by member number, username or email.
  2. The member’s full sign-in history from a second database, with source and event-type codes resolved to labels.
  3. Log lines for that member from several log files in an S3 bucket, within 30 minutes of the incident.

Three datasources are involved: accounts-db and events-db (relational), and service-logs (an S3 file datasource).

[[templates]]
name = "support-sign-in-failures"
title = "Support: sign-in failure investigation"
description = """\
One member, one incident: enter the member number, username or email, and the time of the incident. \
Loads the account, the member's full sign-in history, and the auth, billing and edge-proxy log lines \
for that member within 30 minutes of the incident."""
business_justification = "Investigate a member's reported sign-in failure."
risk_level = "medium"
validity_period = "3d"
# 1. Resolve the value the requester typed to one member.
[[templates.scope]]
datasource = "accounts-db"
[[templates.scope.datasets]]
source = { kind = "table", name = "member" }
columns = ["member_id", "member_no", "username", "email", "full_name", "is_active", "updated_at"]
filters.logic = "AND"
filters.conditions = [
{ raw = "member_no = TEXT [lookup_value] OR username = TEXT [lookup_value] OR email = TEXT [lookup_value]" },
]
# 2. That member's sign-in history, with code tables joined into a readable report.
# No time filter, so the requester can see events around a wrongly reported time too.
[[templates.scope]]
datasource = "events-db"
virtual_table.name = "SIGN_IN_HISTORY"
virtual_table.sql = """
SELECT
e.member_no,
s.label AS source,
t.label AS event_type,
e.occurred_at,
e.outcome,
e.client_os,
e.client_ip,
e.mfa_method
FROM login_event AS e
JOIN event_source AS s ON s.source_id = e.source_id
JOIN event_type AS t ON t.type_id = e.type_id
ORDER BY e.occurred_at DESC
"""
[[templates.scope.datasets]]
source = { kind = "table", name = "login_event" }
columns = ["member_no", "source_id", "type_id", "occurred_at", "outcome", "client_os", "client_ip", "mfa_method"]
filters.logic = "AND"
filters.conditions = [{ raw = "member_no IN REF(member.member_no)" }]
[[templates.scope.datasets]]
source = { kind = "table", name = "event_source" }
columns = ["source_id", "label"]
[[templates.scope.datasets]]
source = { kind = "table", name = "event_type" }
columns = ["type_id", "label"]
# 3. Log lines around the incident, narrowed by the datasets above.
[[templates.scope]]
datasource = "service-logs"
[[templates.scope.datasets]]
source = { kind = "path", name = "logs/auth-service.ndjson" }
columns = ["logged_at", "level", "service", "member_no", "username", "client_ip", "request_id", "msg"]
filters.logic = "AND"
filters.conditions = [
{ raw = "member_no IN REF(member.member_no) AND logged_at >= TIMESTAMP [incident_at] - 30m AND logged_at <= TIMESTAMP [incident_at] + 30m" },
]
[[templates.scope.datasets]]
source = { kind = "path", name = "logs/billing-api.ndjson" }
columns = ["logged_at", "level", "service", "member_no", "username", "client_ip", "request_id", "msg"]
filters.logic = "AND"
filters.conditions = [
{ raw = "member_no IN REF(member.member_no) AND logged_at >= TIMESTAMP [incident_at] - 30m AND logged_at <= TIMESTAMP [incident_at] + 30m" },
]
# The edge proxy doesn't know the member number: match by username or by an IP the member signed in from.
[[templates.scope.datasets]]
source = { kind = "path", name = "logs/edge-proxy.ndjson" }
columns = ["logged_at", "level", "service", "member_no", "username", "client_ip", "request_id", "msg"]
filters.logic = "AND"
filters.conditions = [
{ raw = "(username IN REF(member.username) OR client_ip IN REF(login_event.client_ip)) AND logged_at >= TIMESTAMP [incident_at] - 30m AND logged_at <= TIMESTAMP [incident_at] + 30m" },
]

What this template uses:

Feature Where Reference
Several datasources in one template three [[templates.scope]] entries File layout
One TEXT input matching three columns member filter TEXT parameters
References across datasources member_no IN REF(member.member_no) REF(...)
Chained references and OR with parentheses edge-proxy log filter Chaining references
A time window around one timestamp TIMESTAMP [incident_at] - 30m / + 30m TIMESTAMP offsets
File datasets kind = "path" Tables and files
A lookup report, source tables left queryable SIGN_IN_HISTORY, datasets unset (all) Virtual tables
Multi-line description """\ with line-end backslashes Multi-line strings

The requester fills in two fields: Lookup value and Incident at. Datasets load in dependency order: member, then login_event, then the log files.

Example 2: Periodic Report With Drill-Down

Section titled “Example 2: Periodic Report With Drill-Down”

A finance analyst needs a weekly report of approved refunds by approving agent, with the option to drill from a report line into the orders and shoppers behind it, and no further. The template asks for a date window and loads:

  1. Approved refunds for damaged or late orders within the window.
  2. The orders those refunds belong to, and the shoppers who placed them, narrowed by joins back to the refunds.
  3. A grouped report, with every source table trimmed to the rows behind the report.
[[templates]]
name = "refunds-by-agent-weekly"
title = "Approved refunds by agent and week"
description = """\
Approved refunds for damaged or late orders, counted and summed by week and approving agent. \
The refund date window is a request parameter. Drill into the orders and shoppers behind \
each line; nothing outside the report is loaded."""
business_justification = "Weekly finance review of refund volumes per agent."
risk_level = "low"
validity_period = "7d"
[[templates.scope]]
datasource = "shop-postgres"
virtual_table.name = "REFUNDS_BY_AGENT_WEEK"
virtual_table.sql = """
select strftime(CAST(r.created_on AS TIMESTAMP), '%Y-W%V') refund_week, r.approved_by,
count(r.refund_id) refunds, sum(r.amount) refunded, count(distinct s.shopper_id) shoppers
from refund r
join orders o on o.order_id = r.order_id
join shopper s on s.shopper_id = o.shopper_id
where r.state = 'APPROVED'
group by strftime(CAST(r.created_on AS TIMESTAMP), '%Y-W%V'), r.approved_by
order by 1 asc
"""
virtual_table.datasets = "report_rows"
[[templates.scope.datasets]]
source = { kind = "table", name = "refund" }
columns = ["refund_id", "order_id", "created_on", "reason_code", "state", "amount", "approved_by"]
filters.logic = "AND"
filters.conditions = [
{ raw = "state = 'APPROVED'" },
{ raw = "reason_code IN ('DAMAGED', 'LATE')" },
{ 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", "total"]
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.reason_code IN ('DAMAGED', 'LATE')" },
{ 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", "signed_up_on"]
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.reason_code IN ('DAMAGED', 'LATE')" },
{ raw = "refund.created_on >= DATE [from_date] AND refund.created_on <= DATE [to_date]" },
]

What this template uses:

Feature Where Reference
A date window the requester can opt out of DATE [from_date] / DATE [to_date] in its own condition DATE parameters
Lookup tables narrowed by chained inner joins orders, shopper Lookup tables that follow the main table
An aggregate report in DuckDB SQL strftime(CAST(... AS TIMESTAMP), '%Y-W%V') Writing the SQL
Source tables trimmed to the report’s rows virtual_table.datasets = "report_rows" report_rows

Every source table is read in the report’s top-level FROM through joins, so each one stays traceable and is trimmed rather than dropped. To give the analyst the report and nothing else, change the last key to virtual_table.datasets = "hidden".

A report can also be keyed on a value the requester types, rather than a date window. For a per-warehouse report, replace the date conditions with a TEXT parameter on the main table, and on each lookup table that joins to it:

filters.conditions = [
{ raw = "warehouse.warehouse_code = TEXT [warehouse_code]" },
]

Example 3: Dated Log Files For The Days The Requester Picks

Section titled “Example 3: Dated Log Files For The Days The Requester Picks”

An SSH log host writes one file per host and day, such as staging/web1/app-logs-1/app.2026-11-02.log.gz. The template loads two log schemas, each as one table, and asks the requester only for the first and last day. The application logs use the requester’s days. The access logs are fixed to one week in the template.

[[templates]]
name = "app-logs-by-day"
title = "Application logs for the requested days"
description = "Application logs for the days the requester enters, and the access logs for the first week of November 2026."
business_justification = "Incident investigation."
risk_level = "medium"
validity_period = "1d"
[[templates.scope]]
datasource = "log-host"
[[templates.scope.datasets]]
source = { kind = "path_list", name = "APP_LOGS" }
columns = ["timestamp", "level", "message", "source_path", "file_date"]
dates = { from = "DATE [date_from]", to = "DATE [date_to]" }
[[templates.scope.datasets]]
source = { kind = "path_list", name = "ACCESS_LOGS" }
columns = ["remote_addr", "timestamp", "path", "status", "source_path", "file_date"]
dates = { from = "2026-11-01", to = "2026-11-07" }

What this template uses:

Feature Where Reference
An SSH schema loaded as one table kind = "path_list" with the schema’s table name Tables and files
Days the requester enters DATE [date_from] / DATE [date_to] in dates DATE parameters
Days fixed in the template YYYY-MM-DD bounds in dates Tables and files
The file’s own columns source_path and file_date, added to every table Loading Files Into A Request

The requester fills in one Dates row (see Request Parameters For Dated Files). Each name must be a schema the datasource accepts, written as its Table name (here APP_LOGS and ACCESS_LOGS). For how date tokens, accepted schemas and the loaded files work, see Dated Files and Schemas In The Catalog.

  • Dashboard: Data Requests → From Template → Import TOML, as an organization admin. Leave out [defaults], owner and organization: the templates land in the active organization, owned by you, and a later re-import updates them in place. See Import & Export Request Templates.
  • Startup file: deployments that also load a request_templates.toml at startup need a [defaults] block with owner and organization. See the startup-file notes in Import & Export Request Templates before relying on it.

Each datasource the file names (accounts-db, events-db, service-logs, shop-postgres, log-host) must already exist in the organization with exactly that name, or that template is skipped.