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_ipFROM 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""" 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. |
Naming
Section titled “Naming”- 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.
Writing The SQL
Section titled “Writing The SQL”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, withJOINs (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 |
Example: an aggregate report
Section titled “Example: an aggregate report” 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) refundedfrom refundwhere state = 'APPROVED'group by strftime(CAST(created_on AS TIMESTAMP), '%Y-%m'), reason_codeorder by 1 asc"""When The Report Is Built
Section titled “When The Report Is Built”- 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.
hidden: only the report
Section titled “hidden: only the report”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 droppedselect * from shipmentwhere shipment_id in (select shipment_id from delivery_attempt where result = 'FAILED')
-- Traceable: both tables are trimmed to the report's rowsselect distinct shipment.*from shipmentjoin 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.
Where requesters and approvers see it
Section titled “Where requesters and approvers see it”- 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.datasetsunchanged.
The Environment Is Sealed After Loading
Section titled “The Environment Is Sealed After Loading”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.