Table Queries & Visual Query Builder

Table Queries — the library, the visual builder & the SQL editorEvery query is a file in the project folder. Build it by clicking or by typing — it is the same file either way. A query in ATX AnalyticsTable is not a string buried in a workbook. It is a file in the project's own queries/ folder — a .sql file when it was typed, a .visual.json file when it was built by clicking — and because it is a file it is listed in a sidebar, organised into real folders, moved with a rename that Git records, pinned into sheets, scheduled, and reviewed as a diff. The library is the third tab in the sidebar beside Tables and Git, and everything else in this chapter is a way of putting something into it or getting something out of it. Two editors write to the same library: a visual builder for the common shape of a question, and a SQL editor for everything else. Both record which connection the query was written against, so a colleague who pulls the project folder runs it against the right database rather than whichever one happens to be selected.

What it does

A saved-query library in the sidebar — filter box, real folders, drag to reorganise, and a properties pane showing the file path, the connection, every sheet the query is pinned into and every schedule that refreshes it
Queries stored as .sql and .visual.json files under the project's queries/ folder, so the library travels with the project and diffs like the rest of it
A visual builder over any table: three tabs — SELECT, WHERE, ORDER BY — drag a column in, choose a function, add an alias, and watch the generated SQL underneath change as you go
Automatic GROUP BY: the moment the first aggregate appears, every already-selected non-text column is switched to GROUP BY, and nothing you chose by hand is overridden
Computed columns for anything the builder has no widget for, placed into SELECT, WHERE, ORDER BY or GROUP BY
A SQL editor bound to a named connection, with Run in a new tab (Ctrl+Enter) or in place (Ctrl+Shift+Enter), paged results, and Save straight back over the open file
Named parameters in SQL — ${name}, $(name) and $name — resolved from the project's parameters before the statement reaches the database
Results cached into the project's own DuckDB on demand, so paging over a cloud warehouse is billed once instead of once per page
_SQL grid formulas that push SUM, AVG, COUNT, MIN, MAX and SUMIF down to the database instead of pulling the rows up to count them
A Pinned Queries board listing every pin with its sheet, its cell, whether it is live or static, and the schedule that refreshes it
A background-job monitor that names each running job, shows the SQL behind it, and can stop any one of them without leaving a half-filled tab

How it works

A saved query is a file. A .sql file carries a short comment header — @connection, @connectionId, @schema — which the editor strips before you see it and rewrites when you change the connection. A .visual.json file carries the builder's state and the SQL it generated, so the query can be reopened for editing rather than only re-run.
The connection is resolved by id first and then by name. Ids are per-machine because connections live in a per-user vault, so a colleague who pulls the git-tracked queries/ folder has different ids — the name is what survives the trip.
A query naming a connection this machine does not have refuses to open rather than falling back to whatever is selected. The wrong database usually answers instead of erroring, which is the failure this rule exists to prevent.
In the builder, columns are dragged or clicked into whichever of SELECT, WHERE and ORDER BY is open. A function turns a column into SUM, AVG, MIN, MAX, COUNT, UPPER, LOWER or an explicit GROUP BY; an alias renames it; the Generated SQL preview below is live and can be copied out.
The connection picker in the SQL editor has three states, not two: nothing chosen, Local DuckDB chosen deliberately, or a named connection. Run refuses while nothing is chosen, so "unchosen" is never quietly read as "the local database".
Parameters are substituted as bare values before the query is sent. A reference that resolves to nothing stops the run before the database sees it and names the parameter, because DuckDB would otherwise report a missing bind with no clue which one.
Save result to Local DuckDB runs the source query once, writes the rows into the project database, and rewrites the tab's SQL to read the cache — Overwrite replaces the table, Append adds rows with no de-duplication.
Pinning puts a query into the grid as a live preview (a read-only page of the result) or a static snapshot (the whole result materialised into cells). The Pinned Queries board is where you see which is which, and the query monitor is where you watch either one load.

Practices that keep it reliable

Quote your parameters yourself — substitution inserts a bare value, so >= ${day} is arithmetic and >= '${day}' is a date
Keep query names stable: saving over an existing path keeps the file's identity, so pins and schedules follow it instead of being orphaned
Explore with a preview pin, produce with a static one — and materialise into the local DuckDB before a formula, a transform or a pipeline has to read every row
Reach for a _SQL formula rather than pinning a million rows to add them up; the aggregate belongs on the server, not in the sheet

In a regulated environment

The queries folder is plain text inside the project repository, so a changed WHERE clause on a production report is a reviewable diff with an author and a date rather than a mystery.
No credential ever enters a query file. It records a connection's name, id and schema; the password stays in the encrypted vault, which is what makes the folder safe to commit.
Library paths are validated rather than trusted: a drive letter, a leading separator or a .. segment is refused, names are sanitised for the filesystem that will actually hold them, and a folder with anything in it cannot be deleted.
The AI agent's SQL is held to a stricter contract than a person's: SELECT-only, classified rather than pattern-matched, and separately barred from DuckDB's reach into the filesystem and the network — no read_csv, no ATTACH, no INSTALL, no getenv.
Formula evaluation is batched, capped per recalculation and isolated per request, so one bad formula errors in its own cell rather than failing the sheet.

Screens

SQL — the editor, the three sidebar panels, the saved-query library, Run, and the query monitor with its stop button.
The library — real folders, a filter box, and a properties pane naming the file, its connection, its pins and its schedules.
The visual builder — drag a column into SELECT, WHERE or ORDER BY and read the SQL it writes underneath.
The SQL editor — bound to a named connection, results in their own tabs, and Save writing back over the open file.
Pinned Queries — every pin, the sheet and cell it landed in, live or static, and the schedule that refreshes it.
Background jobs — what is running, the SQL behind it, and a stop button that leaves the sheet as it was.

Related guides

SQL reporting

Glossary terms on this page

AI agents: definition

ATX Institute on LinkedIn

This site needs JavaScript for the full experience. Every page above is reachable without it.