Running a query
zsql sql prints a statement. zsql run plans the same statement, runs it on the datasource the planner chose, prints the rows, and keeps them under ~/.zsql/results.
It is local only. The server does not run queries: it has no database driver in it. zsql run is the command line on your own machine, using the same connection that zsql catalog uses, and it is how you find out whether a model answers the question correctly rather than merely plausibly.
$ zsql run 'contact subchannel id, contacts'
Contact Subchannel ID Contacts
───────────────────── ────────
SC1 1530
SC4 1238
SC5 872
SC2 769
SC6 356
SC3 235
6 rows (complete) · cs (duckdb) · 0.1 s · r-20261024-22503a2d
The argument is an expression line, or --spec FILE for a JSON spec, with --context FILE for a security context. The footer names the row count, whether the result is complete, the datasource and adapter, the time and the result id.
Where the rows go
~/.zsql/results/<project>/<xx>/<key>/
manifest.json
rows.jsonl.gz
Outside the repository, and never deployed. Rows are gzipped JSON lines; the manifest is written last and is the commit marker, so an interrupted run leaves no half-result that a later read could mistake for a whole one.
The manifest carries the columns with their field uids and types, the statement, the expression line, the datasource, the cap, whether the result is complete, and the fields the query touched.
The cache
A second identical run answers from the store:
6 rows (complete) · cs (duckdb) · 0.1 s · r-20261024-22503a2d · cached 0 s ago, saved 0.1 s; --fresh to run again
The key is a digest of the datasource uid, the adapter, the connection identity (the username, account, profile, project or client id — whichever the adapter has), the SQL as sent, and the query’s security context. Three consequences worth knowing:
- It is keyed on the statement, not the spec. Two specs that plan to the same statement share a result, and a model edit that does not change the statement keeps its cache.
- It is keyed on the context. A result planned for one caller is never served to a caller whose policies would have produced a different statement.
- No secret is in the key. The connection identity is who and where, not the credential.
cache_ttl on the datasource sets how long a result answers for; the default is 24 hours, and 0 keeps nothing. --fresh runs anyway and replaces the stored result.
Limits
The default cap is 10,000 rows, pushed into the statement as a limit rather than applied after the fact. The statement asks for one more than the cap so truncation is detected rather than guessed at, and the footer says which it got:
| footer | means |
|---|---|
6 rows (complete) | the whole answer |
500 rows (your limit 500) | your --limit, which may or may not be the whole answer |
10000 of more (--limit 50000 or --all) | the cap cut it, and here are the two ways to ask for the rest |
zsql run 'revenue by region' --limit 500 # a different cap
zsql run 'revenue by region' --all # no limit; held in memory
zsql run 'revenue by region' --timeout 30 # statement timeout, seconds
--all means exactly that, and holds the whole result in memory, so it is the flag to think about before using on a fact table.
Output
zsql run '...' --format json # table (default), json, jsonl, csv
zsql run '...' --out rows.csv # the format its extension says
zsql run '...' --head 20 # print at most this many
zsql run '...' --quiet # no footer, no warnings
--format never changes with where stdout goes, so a pipe gets what you asked for and nothing else. A table on a terminal prints 50 rows unless --head says otherwise; the rest are in the store, and zsql results show pages through them.
In the table form, a null is ∅ — so an empty measure is visibly a null rather than a blank cell or a zero. Numbers are right-aligned, a cell longer than forty characters is cut with an ellipsis, and the column widths come from the first two hundred rows. The json, jsonl and csv forms carry the value as it is, so nothing is reformatted for a program.
zsql results
$ zsql results list
id when rows complete datasource query
r-20261024-22503a2d 2026-10-24T11:18:43Z 6 yes cs contact subchannel id, contacts
1 results under /home/ajo/.zsql/results/customer-service; `zsql results show <id>`, `zsql results purge`
| Command | Does |
|---|---|
zsql results list | Every stored result of this project, newest first |
zsql results show ID | Its rows; --format, --head, --offset |
zsql results export ID --out FILE | Its rows to a file, in the format the extension says |
zsql results sql ID | The statement it ran |
zsql results rm ID... | Remove results by id |
zsql results purge | Remove every result of this project; --yes to skip the prompt |
zsql results sql is the one to reach for when a number looks wrong:
$ zsql results sql r-20261024-22503a2d
-- r-20261024-22503a2d on cs (duckdb)
SELECT
T0."contact_subchannel_id" AS "Contact Subchannel ID",
count(*) AS "Contacts"
FROM
cs_contact_f T0
GROUP BY
T0."contact_subchannel_id"
LIMIT 10001
That is the statement as sent, cap included, so the question “is the model wrong or is the SQL wrong” is answerable from the store rather than by re-deriving it.
JSON
--agent run, or --json where a command has it, answers with one document: the columns, the rows, the manifest and the cache state.
{
"cache": {"hit": true, "age_s": 11},
"columns": ["Contact Subchannel ID", "Contacts"],
"manifest": {
"id": "r-20261024-22503a2d",
"adapter": "duckdb",
"datasource_uid": "cs",
"expr": "contact subchannel id, contacts",
"complete": true,
"cap": 10000,
"cache_ttl_secs": 86400,
"columns": [
{"name": "Contact Subchannel ID", "field_uid": "contact-subchannel-id", "data_type": "string", "source": "model"},
{"name": "Contacts", "field_uid": "contacts", "data_type": "bigint", "source": "model"}
]
}
}
Storage format
Parquet, DuckDB and DataFusion were each measured as the store’s format and deferred: gzipped JSON lines is what a result of this size costs least to write and read, and nothing in the command yet asks for a query over stored results. The format is versioned in the manifest, so changing it later is a format bump rather than a migration.
Next steps
- Querying from the CLI:
sql,explain,exploreand the repl - Connecting to a warehouse: what a run needs to open
- The expression language: the line
runtakes