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:

footermeans
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`
CommandDoes
zsql results listEvery stored result of this project, newest first
zsql results show IDIts rows; --format, --head, --offset
zsql results export ID --out FILEIts rows to a file, in the format the extension says
zsql results sql IDThe statement it ran
zsql results rm ID...Remove results by id
zsql results purgeRemove 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