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:
- The member’s account, matched by member number, username or email.
- The member’s full sign-in history from a second database, with source and event-type codes resolved to labels.
- 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_methodFROM login_event AS eJOIN event_source AS s ON s.source_id = e.source_idJOIN event_type AS t ON t.type_id = e.type_idORDER 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:
- Approved refunds for damaged or late orders within the window.
- The orders those refunds belong to, and the shoppers who placed them, narrowed by joins back to the refunds.
- 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) shoppersfrom refund rjoin orders o on o.order_id = r.order_idjoin shopper s on s.shopper_id = o.shopper_idwhere r.state = 'APPROVED'group by strftime(CAST(r.created_on AS TIMESTAMP), '%Y-W%V'), r.approved_byorder 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 variation: one lookup by a typed value
Section titled “A variation: one lookup by a typed value”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.
Loading These Files
Section titled “Loading These Files”- Dashboard: Data Requests → From Template → Import TOML, as an organization admin. Leave out
[defaults],ownerandorganization: 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.tomlat startup need a[defaults]block withownerandorganization. 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.