52 min read

Singdata cz-cli Deterministic SQL: What "Deterministic" Actually Means at the Command Layer

A layer-by-layer look at cz-cli determinism: the documented command contract (envelopes, exit codes, profiles), where query-level determinism actually comes from, and a testable checklist for agent pipelines.

Singdata cz-cli Deterministic SQL: What "Deterministic" Actually Means at the Command Layer

A shell script that calls the same tool with the same arguments on two consecutive nights and gets two different answers is not usually a broken script. It is usually a script that has been told the tool is deterministic and has not checked which layer that claim belongs to. cz-cli's documented properties — a fixed output envelope, a fixed exit-code scheme, a fixed error shape — describe the command layer. They say nothing about whether the SQL you pass through it returns the same rows twice. That gap is where agent pipelines quietly lose their footing, and it is the subject of everything below: the command-layer contract, query-layer determinism, the execution model, the failure modes that actually appear in logs, and a checklist you can run against your own setup.

One scoping note up front. This article works from the cz-cli README as retrieved. Several flags that commonly appear in CLI tooling — --write, -y, --sync, --timeout — are not documented there, and this article does not treat them as existing. Where the documentation is silent, the text says so and points you at cz-cli <command> --help.

Two Kinds of "Deterministic" and Why the Distinction Matters

The word "deterministic" gets applied to two different contracts, and conflating them is the single most common reason a pipeline that looked stable in testing drifts in production.

At the command layer, determinism means the interface behaves predictably: the same command with the same flags against the same connection profile produces the same envelope shape, the same exit code semantics, and the same error format. This is a property of the tool's design. It is what makes a CLI safe to wrap in automation, because your parser can be written once and trusted.

At the query layer, determinism means the same SQL against the same data state produces the same result set. This is a property of the SQL and the engine, not of the tool that submitted it. SQLite's documentation puts the definition plainly: a deterministic function "always gives the same answer when it has the same inputs," while a non-deterministic function "might give different answers on each invocation, even if the arguments are always the same" (SQLite, Deterministic SQL Functions). Microsoft's SQL Server documentation extends the same idea to the database state: deterministic functions "always return the same result any time they're called with a specific set of input values and given the same state of the database," whereas nondeterministic functions may differ "even if the database state that they access remains the same" (Microsoft Learn).

The operational consequence follows directly. A perfectly deterministic CLI wrapper around a non-deterministic query still yields a non-deterministic pipeline. The wrapper guarantees that you will reliably learn what the query returned. It does not guarantee that what the query returned is the same thing it returned last time. An agent that reads the stable envelope, sees a well-formed success, and treats the payload as authoritative has no signal that the payload changed — and a reconciliation job built that way will report a variance it cannot explain, because every layer it was told to trust did exactly what it promised.

So the two questions to keep separate are: does the tool behave the same way every time I call it, and does my query return the same rows every time it runs? The rest of this article answers the first from documentation and the second from SQL semantics.

The Command-Layer Contract: Output, Exit Codes, and Guardrails

The documented output contract is narrow and specific, which is what makes it useful.

Output is machine-readable by default. On success you get a {"data": …} envelope; on failure you get a {"error": {"code", "message"}} envelope. The --format flag accepts json (the default), pretty, table, csv, text, jsonl, and toon. The --field <path> flag extracts a single field as a bare value, with paths like data.workspace, sessions, or count.

Exit codes are documented as 0 for success, 1 for a business error, and 2 for a usage error. That three-way split matters more than it looks: 1 and 2 are different classes of problem, and a wrapper that collapses them into "non-zero means retry" will retry a malformed command forever.

There is one documented wrinkle worth building around. Under the row-oriented formats — text, table, csv, jsonl — a failure is emitted as a single ERROR <code>: <message> line rather than JSON, "so one parser handles both." If you switch formats for human readability in one place and machine parsing in another, you now have two parsers and two failure paths. Pick one format per consumer and test the failure branch of that format explicitly.

On profiles, cz-cli login <label> performs browser OAuth, discovers your instances and workspaces, and creates one connection profile per workspace. profile use selects the active profile. For CI, the runtime environment variables override the active profile, which the README describes as what makes cz-cli usable in CI without a profiles.toml. The relevant ones for determinism are CZ_PROFILE (which profile to use, overriding default_profile), CZ_FORMAT (default --format value), the credential variables CZ_PAT / CZ_USERNAME / CZ_PASSWORD, and the connection-target variables CZ_SERVICE / CZ_PROTOCOL / CZ_INSTANCE / CZ_WORKSPACE / CZ_SCHEMA / CZ_VCLUSTER.

CZ_PROFILE is the one to pin. A pipeline that relies on default_profile inherits whatever the last interactive user selected on that machine. A pipeline that sets CZ_PROFILE explicitly fails loudly when the profile is missing instead of silently querying a different workspace.

On guardrails: the retrieved README does not document a --write flag or a -y flag. It does document --dangerously-skip-permissions for unattended agent run use, and it documents --dry-run and --batch on sql. If your runbook assumes a write-confirmation flag exists, confirm it against cz-cli <command> --help on the installed version before you build a gate around it. Treating an undocumented flag as established is exactly the kind of assumption that produces a pipeline which passes review and fails at 2 a.m.

Sync, Async, and the Execution Contract

The documented execution model has one explicit fork and several silences.

sql accepts --async, and the feature list describes "async polling for long-running jobs." agent run accepts --async to return a session id immediately instead of waiting. Waiting appears to be the default behavior for both, since --async is described as the opt-in that changes it.

The supplied documentation does not name a --sync flag, does not document a --timeout flag, and does not document a job status / job result subcommand pair. There is a job command listed under "Job execution details and performance profiles," but the README does not describe it as the polling counterpart to --async. Do not publish a polling loop against a subcommand whose contract you have not read; check cz-cli job --help and cz-cli sql --help on the installed version first.

Why this matters for determinism is straightforward once you state it plainly. A synchronous call returns a result. An asynchronous call returns an identifier. Those are different types, and a consumer that treats the identifier as the answer has a failure mode that looks nothing like a wrong result — it looks like a successful run that produced a session id where a number should be. If your agent pipeline mixes the two, the failure is silent and the downstream arithmetic is nonsense rather than absent.

The practical rule: decide per call site whether the result is awaited or polled, write that decision down, and make the consumer assert on the shape it expects. A consumer that checks data contains the columns it needs will catch an id-in-place-of-result immediately. A consumer that checks only the exit code will not.

Where Deterministic Pipelines Actually Break

The failure modes that break deterministic pipelines fall into four layers, and they are worth separating because each one produces a different signature in your logs.

Shell layer. command not found happens before cz-cli runs at all. In a container image built without the binary, or a PATH that differs between the build and the runner, this is the first thing to rule out. It produces no envelope and no cz-cli exit code, so a parser that only handles cz-cli output will misclassify it.

Connection layer. The README states that status exits 0 only when the connection works. That is a clean, testable precondition: run cz-cli status and check the exit code before any pipeline step that depends on the connection. A non-zero exit here means the problem is upstream of your SQL, and debugging the query is wasted effort.

Permission layer. The README documents --dangerously-skip-permissions for unattended agent run, which implies that permission checks are active by default in unattended contexts. A task that works interactively and fails unattended is usually a permission gap, not a query bug. The error envelope's code field is the thing to branch on; the message is for humans.

Query layer. This is where the interesting failures live, and they are documented in the SQL references rather than in cz-cli.

Unordered results come first. PostgreSQL's documentation is unambiguous: if sorting is not chosen, "the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on" (PostgreSQL 18, Sorting Rows). The LIMIT/OFFSET page is blunter still: without an ORDER BY that constrains rows into a unique order, "you will get an unpredictable subset of the query's rows," and "it is even possible for repeated executions of the same LIMIT query to return different subsets of the rows of a table" (PostgreSQL 18, LIMIT and OFFSET). The same page notes that the optimizer takes LIMIT into account when generating plans, so different LIMIT values can yield different row orders. This is not a bug in the engine; it is the documented consequence of not specifying an order. A LIMIT without a unique ORDER BY is the most common source of drifting totals in otherwise correct pipelines.

Non-deterministic functions come second. SQLite lists random(), changes(), last_insert_rowid(), and sqlite3_version() as examples, and treats date/time functions using the string 'now' or the localtime/utc modifiers as non-deterministic (SQLite). Microsoft's list includes CURRENT_TIMESTAMP and NEWID, and notes that RAND is deterministic only when a seed parameter is specified (Microsoft Learn). A filter like WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL '1 day' evaluates at run time, so a job that runs at 01:58 and a job that runs at 02:02 can legitimately cover different windows. That is correct SQL and a non-deterministic pipeline.

Model layer. agent run sits in the same pipeline as your SQL, and the model behind it is not a deterministic function. A study of five LLMs configured to be deterministic, applied to eight tasks across ten runs in both zero-shot and few-shot settings, found "accuracy variations up to 15% across naturally occurring runs with a gap of best possible performance to worst possible performance up to 70%," and reported that "none of the LLMs consistently delivers repeatable accuracy across all tasks, much less identical output strings" (arXiv:2408.04667). The authors note that sharing preliminary results with insiders suggested non-determinism may be essential to efficient compute use via co-mingled data in input buffers, so the behavior is unlikely to disappear. If a pipeline step depends on the agent's natural-language output being identical across runs, that step is not deterministic regardless of how stable the CLI wrapper is.

An Illustrative Scenario: The Nightly Reconciliation That Drifts

The following is a constructed example for reasoning about failure attribution. It is not a report of any customer's experience, and the numbers are illustrative only.

Consider a nightly reconciliation job that runs three steps through cz-cli: a SQL query that pulls the day's transactions, a SQL aggregation that totals them, and an agent run step that summarizes the result into a report. On Monday the total is 1,204,338. On Tuesday it is 1,204,301. Nothing in the pipeline changed, and every step exited 0.

Three separate causes could each produce that drift, and the point of the example is that they are indistinguishable from the output alone.

Cause one: unordered rows feeding a limit. The transaction query ends in LIMIT 10000 with no ORDER BY. Per PostgreSQL's documentation, repeated executions can return different subsets. If the table holds more than 10,000 rows for the day, the total legitimately differs between runs because a different 10,000 rows were summed. The query is syntactically fine and the engine did what it was told.

Cause two: a run-time filter. The query filters on created_at >= CURRENT_TIMESTAMP - INTERVAL '24 hours'. The Monday run started at 01:58 and the Tuesday run at 02:04, so the two windows are offset by six minutes and cover slightly different sets of rows. The totals differ by roughly the volume of transactions in those six minutes — which, in this illustration, is close to the observed gap.

Cause three: a varying agent summary. The agent run step reads the aggregate and writes a narrative. Even with the aggregate held constant, the model's output can vary across runs, and if any downstream consumer parses a number out of that narrative rather than out of the SQL result, the reported total can drift for reasons that have nothing to do with the data.

Attribution requires isolating the layers. Re-run the transaction query twice with a unique ORDER BY and a fixed time window and compare the row counts; if they match, cause one and cause two are eliminated. Then compare the aggregate to the agent's narrative; if the aggregate is stable and the narrative is not, cause three is confirmed. Without that isolation, a team will typically fix the query first, find the drift persists, and conclude the database is unreliable — when the remaining cause was in a different layer the whole time.

An Acceptance Checklist for Agent Setups

Each item below is something you can test in your own environment rather than a claim about how any specific deployment behaves.

  • Connection precondition. Run cz-cli status and assert exit code 0 before any pipeline step. The README documents exit 0 only when the connection works, so this is a clean gate.

  • Pinned profile. Set CZ_PROFILE explicitly in CI rather than relying on default_profile. Confirm the pipeline fails, rather than silently switching workspaces, when the profile is absent.

  • Exit-code branching. Assert on 0, 1, and 2 separately. Do not parse prose to decide whether a run succeeded.

  • Both output paths tested. For whichever --format you use, test the success envelope and the failure line. If you use a row-oriented format, confirm your parser handles the single ERROR <code>: <message> line.

  • Flag availability. Run cz-cli <command> --help on the installed version and confirm every flag your pipeline depends on appears there. This is the check that catches undocumented assumptions before they reach production.

  • Unique ordering. Require a unique ORDER BY on any query whose row order or LIMIT matters. "Unique" means the sort key distinguishes every row; a non-unique key leaves ties to implementation-dependent order.

  • Non-deterministic function audit. Grep your query set for random(), RAND without a seed, CURRENT_TIMESTAMP, NEWID, and date/time functions using 'now' or timezone modifiers. For each hit, decide whether run-to-run variation is intended. If it is not, replace the function with a parameter bound at pipeline start.

  • Async decision. For every --async call site, state whether the result is awaited or polled, and assert on the shape the consumer expects. A consumer that checks for the result columns will catch an id-in-place-of-result immediately.

  • Agent output handling. If a downstream step parses numbers out of agent run output, move the number to the SQL result and let the agent describe it rather than produce it.

FAQ

Does cz-cli have a --sync flag? The supplied README does not name one. Waiting appears to be the default behavior, with --async as the opt-in that changes it. Confirm against cz-cli sql --help and cz-cli agent run --help on the installed version.

Is --timeout documented? Not in the supplied README. If your pipeline needs a timeout, check the current command help and documentation rather than assuming a flag name.

What does connected: false mean in practice? Treat it as a connection-state signal to verify with cz-cli status. Since status exits 0 only when the connection works, the exit code is the authoritative check; the field is a diagnostic.

Does deterministic CLI output make my query results deterministic? No. The two layers are independent. The envelope guarantees the shape of what you receive, not the stability of what it contains. A query without a unique ORDER BY, or one that filters on CURRENT_TIMESTAMP, is not guaranteed to return the same rows across runs, even when every call returns a well-formed {"data": …} envelope with exit code 0.

Does --async change the result? It changes what you receive and when. An async call returns an identifier rather than a result, so the consumer's contract is different. Whether the eventual result is stable is a query-layer question, not an execution-model one.

What to Confirm Before You Rely on This

The cz-cli README as retrieved documents the output envelopes, the --format and --field flags, the exit-code scheme, the row-oriented ERROR line, --async on sql and agent run, the profile model, and the CZ_* runtime environment variables. It does not document --write, -y, --sync, --timeout, or a job status / job result pair. This article has not treated any of those as established, and you should not either.

The authority for your installed version is cz-cli <command> --help and the project's current documentation at github.com/clickzetta/cz-cli. Version drift is real — the npm package listing for the macOS ARM64 binary shows version 2.0.0, while the README's pinning example uses 0.1.0 — so confirm against what you actually run, not against a blog post.

One further boundary: Singdata's platform context is separate from cz-cli's documented behavior. Nothing in this article should be read as a claim about how any particular lakehouse engine resolves ordering, evaluates time functions, or executes agent steps. Those are properties of the engine and the model, and they need to be verified in your own environment against your own data.

Previous posts

1. https://singdata.com/trending/natural-language-analytics-tools-permission-controls/

2. https://singdata.com/trending/vector-index-row-level-security-layout

3.https://singdata.com/trending/text-to-sql-hallucination/