Skip to content

Virtual Tables

A virtual table is a SQL report the request’s environment builds from the datasets it just loaded. Use one when the useful shape of the data is a join, a lookup of codes to labels, or an aggregate, rather than raw tables. You can also choose whether requesters see the source tables at all, or only the report.

Each [[templates.scope]] entry can declare one virtual table, over that entry’s datasets:

[[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.client_ip
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
"""
virtual_table.datasets = "all"
[[templates.scope.datasets]]
source = { kind = "table", name = "login_event" }
columns = ["member_no", "source_id", "type_id", "occurred_at", "client_ip"]
[[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"]

Write the virtual_table.* keys right after datasource, before the first [[templates.scope.datasets]] header (see TOML syntax tips).

Field Required Notes
virtual_table.name yes The table name requesters query.
virtual_table.sql yes One SELECT over this scope entry’s datasets.
virtual_table.datasets no What requesters can query besides the report: all (default), report_rows or hidden. See below.
  • Letters, digits and underscores, starting with a letter or underscore: SIGN_IN_HISTORY, REFUNDS_BY_AGENT_WEEK.
  • It must not match a dataset or another virtual table in the same request.
  • Naming it after what it shows, in upper case, makes it easy to tell apart from the source tables in the Explorer.

The report runs on DuckDB inside the request’s environment, after loading, not on the source database. Write DuckDB SQL even when the source is Oracle or MSSQL.

Allowed

  • One SELECT, with JOINs (inner, left, right, self-joins), WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT, LIMIT, UNION, and subqueries.
  • Old-style comma joins: FROM orders o, shopper s WHERE s.shopper_id = o.shopper_id.
  • CAST(...), CASE, COALESCE, CURRENT_DATE, arithmetic, comparisons, and these functions: abs, avg, ceil, ceiling, concat, concat_ws, count, date_part, date_trunc, floor, greatest, least, length, lower, lpad, ltrim, max, min, nullif, pow, power, replace, round, rpad, rtrim, sign, sqrt, strftime, strptime, substr, substring, sum, timezone, trim, upper.

Rejected when the file is loaded

  • WITH (CTEs), SELECT ... INTO, and locking clauses (FOR UPDATE).
  • Table functions and file readers (read_csv, read_parquet, …), and any function not listed above.
  • Schema-qualified functions.
  • A table that isn’t one of this scope entry’s datasets.

Referencing datasets. Use each dataset’s source.name. Case doesn’t matter: refund and REFUND both read the REFUND dataset. Every column the SQL reads must be in that dataset’s columns list. Missing columns aren’t caught when the file is loaded, only when the report is built.

No parameters. DATE [from_date] and other parameters are not filled in here. Put the window on the dataset’s filters: the report sees only the rows that loaded.

Porting Oracle SQL. Spell out what Oracle does implicitly:

Oracle DuckDB
TO_CHAR(created_on, 'YYYY-MM') strftime(CAST(created_on AS TIMESTAMP), '%Y-%m')
TRUNC(created_on) date_trunc('day', created_on)
SYSDATE CURRENT_DATE
virtual_table.name = "REFUNDS_BY_MONTH"
virtual_table.sql = """
select strftime(CAST(created_on AS TIMESTAMP), '%Y-%m') refund_month, reason_code,
count(refund_id) refunds, sum(amount) refunded
from refund
where state = 'APPROVED'
group by strftime(CAST(created_on AS TIMESTAMP), '%Y-%m'), reason_code
order by 1 asc
"""
  • The report is built once, when the request is activated, after every dataset in its scope entry has loaded. It doesn’t change for the life of that activation.
  • If a dataset in the scope entry fails to load, the report is skipped. If the SQL fails, the error appears in the request’s load status next to any dataset errors.
  • In the Explorer, the report is listed with its columns and its SQL definition. Natural-language queries also receive its definition.
  • On a request created from a template, the virtual table is read-only. Requesters and approvers see it exactly as the template defines it.

Source Tables Behind The Report: virtual_table.datasets

Section titled “Source Tables Behind The Report: virtual_table.datasets”

By default, a requester can query both the report and every dataset it was built from. virtual_table.datasets restricts the datasets to the rows the report uses, or hides them entirely:

virtual_table.datasets = "hidden" # "all" (default) | "report_rows" | "hidden"
Value Report Source datasets in this scope entry
all (or unset) Queryable Queryable, every loaded row
report_rows Queryable Queryable, but only the rows the report was built from, with all their listed columns
hidden Queryable Dropped. Only the report can be queried

The setting applies to one scope entry. Other datasources in the same request keep their own setting, and datasets without a virtual table are unaffected.

Use hidden when the requester should see the report and nothing else, for example a summary built from person-level tables.

  • The source datasets are always loaded, so the report can be built, and then dropped.
  • They are dropped even if the report fails, so a failed report never exposes them.
  • The Explorer’s Data Scope card, SQL autocomplete and natural-language queries offer only the report. Status and row counts list only the report, plus any dataset that failed to load so its error stays visible.

report_rows: drill down into exactly the rows behind the report

Section titled “report_rows: drill down into exactly the rows behind the report”

Use report_rows when requesters need to drill from a report line into its underlying records, but nothing beyond them. Each source dataset keeps every listed column, but only the rows the report reads. Rows that feed an aggregate count as report rows: behind a count(*) of 12, the 12 rows stay.

Rows are traced through the report’s top-level FROM and WHERE. A dataset that can’t be traced is dropped, never kept in full:

Report SQL Result
Dataset read in the top-level FROM, directly or through joins (including left joins and self-joins) Trimmed to the report’s rows
Dataset read only inside a subquery: a derived table (select ... from x), EXISTS, IN (select ...) That dataset is dropped
Dataset not read by the report at all That dataset is dropped
Report uses HAVING, LIMIT / OFFSET, or UNION / INTERSECT / EXCEPT Every dataset is dropped
Report fails Every dataset is dropped

To keep a table traceable, read it with a join in the top-level FROM, and add DISTINCT if the join repeats rows:

-- Not traceable: delivery_attempt is read only inside a subquery, so it is dropped
select * from shipment
where shipment_id in (select shipment_id from delivery_attempt where result = 'FAILED')
-- Traceable: both tables are trimmed to the report's rows
select distinct shipment.*
from shipment
join delivery_attempt on delivery_attempt.shipment_id = shipment.shipment_id and delivery_attempt.result = 'FAILED'

Likewise, don’t wrap a table in (select distinct * from x) just to remove duplicates. Datasets narrowed by inner joins already load each row once (see joins), and the wrapper hides the table from tracing.

The request’s status shows the row count each dataset kept.

  • The request page shows approvers the report and which source tables the requester can reach.
  • In the dashboard’s scope form, the setting is the Selected tables in the Explorer control. On a request created from a template it is read-only, set by the template.
  • Export and re-import keep virtual_table.datasets unchanged.

Whatever the setting, every request environment refuses queries while its data is loading. Once loading ends, it is sealed: file and network access is switched off and the configuration is locked. A requester’s SQL can’t read files or reach other systems, and can’t switch that back on. If sealing fails, the environment refuses all queries instead of running unsealed.