33 min read

Singdata cz-cli Deterministic SQL: What the Command Layer Guarantees and What Your Query Must Earn

A practical guide to cz-cli deterministic SQL: what the command layer guarantees (envelopes, exit codes, profiles) and what your query must earn through unique ordering, fixed time windows, and audited non-deterministic functions.

Singdata cz-cli Deterministic SQL: What the Command Layer Guarantees and What Your Query Must Earn

A deterministic CLI is a reliable envelope, not a reliable answer. cz-cli's documented contract — fixed output shapes, fixed exit-code semantics, a fixed error format — governs how the tool reports what happened. It says nothing about whether the SQL you pass through it returns the same rows twice. That distinction is the working premise of this article, written for data engineers and platform leads wiring cz-cli into CI pipelines or agent loops. Everything below rests on the retrieved cz-cli documentation and on published SQL semantics; where the documentation is silent about a flag or subcommand, the text says so rather than assuming one exists.

Two Kinds of "Deterministic," and Why the Difference Decides Your Pipeline

"Deterministic" describes two independent contracts, and a pipeline breaks when its owner treats one as covering the other.

Command-layer determinism is a property of the tool's interface. The same invocation against the same profile yields the same envelope shape, the same exit-code meaning, and the same error format. This is what makes a CLI safe to wrap: the parser is written once and trusted.

Query-layer determinism is a property of the SQL and the engine. SQLite defines a deterministic function as one that "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 extends the idea to database state: deterministic functions return the same result "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 consequence is direct: a stable wrapper around an unstable query produces a confident, well-formed, wrong-again answer. An agent reading a clean {"data": …} envelope with exit code 0 has no signal that the payload changed, so the burden of proof sits with whoever wrote the query, not with the CLI.

The Command-Layer Contract: Envelopes, Exit Codes, and Profiles

The documented contract is narrow, which is what makes it usable.

Output is machine-readable by default: a {"data": …} envelope on success, a {"error": {"code", "message"}} envelope on failure. --format accepts json (default), pretty, table, csv, text, jsonl, and toon; --field <path> extracts a single field as a bare value, with paths such as data.workspace or count. Exit codes are 0 success, 1 business error, 2 usage error. Collapsing 1 and 2 into "non-zero means retry" will retry a malformed command indefinitely, because a usage error will not resolve on its own.

One documented wrinkle deserves a design decision. Under the row-oriented formats — text, table, csv, jsonl — a failure arrives as a single ERROR <code>: <message> line rather than JSON. Mix formats across consumers and you maintain two parsers and two failure paths; pick one format per consumer and test its failure branch explicitly.

On profiles, cz-cli login <label> runs browser OAuth, discovers instances and workspaces, and creates one profile per workspace. For CI, runtime CZ_* variables override the active profile — the README describes this as what makes cz-cli usable without a profiles.toml. Pin CZ_PROFILE explicitly: a pipeline relying on default_profile inherits whatever the last interactive user selected on that machine, while a pinned profile fails loudly when missing. Also pin the connection target (CZ_SERVICE, CZ_INSTANCE, CZ_WORKSPACE, CZ_SCHEMA) and credentials (CZ_PAT or CZ_USERNAME/CZ_PASSWORD).

The retrieved README does not document --write, -y, --sync, or --timeout. Any gate built on an undocumented flag should be confirmed against cz-cli <command> --help on the installed version first.

Sync, Async, and the Shape Your Consumer Expects

The documented execution model has one explicit fork: --async on sql and on agent run. On sql it submits the query and returns a job_id immediately; on agent run it returns a session id instead of waiting. Waiting appears to be the default, since --async is described as the opt-in that changes it.

The type distinction is the whole point. A synchronous call returns a result; an asynchronous call returns an identifier. A consumer that treats the identifier as the answer fails in a way that looks like success — a session id where a number should be. Downstream arithmetic then produces nonsense rather than an error.

Decide per call site whether the result is awaited or polled, record that decision, and have the consumer assert on the shape it expects. Checking that data contains the expected columns catches an id-in-place-of-result immediately; checking only the exit code does not.

The retrieved README lists a job command for "job execution details and performance profiles" but does not describe it as the polling counterpart to --async. Before publishing a polling loop, confirm the contract with cz-cli job --help on the installed version.

Where Reproducibility Actually Breaks: Four Layers, Four Signatures

Failures cluster into four layers, each with a distinct log signature.

Shell layer. command not found happens before cz-cli runs. It produces no envelope and no cz-cli exit code, so a parser handling only cz-cli output misclassifies it. Rule this out first in containers and runners whose PATH differs from the build environment.

Connection layer. The README states status exits 0 only when the connection works, making it a clean precondition gate. A non-zero exit here means the problem sits upstream of your SQL, and debugging the query is wasted effort.

Permission layer. --dangerously-skip-permissions exists for unattended agent run, which implies permission checks are active by default. A task that works interactively and fails unattended is usually a permission gap. Branch on the error envelope's code, not its message.

Query layer. This is where the substantive failures live, and they are documented in SQL references rather than in cz-cli. PostgreSQL is unambiguous on ordering: without a sort step, "the rows will be returned in an unspecified order … it must not be relied on" (Sorting Rows). The LIMIT page adds that without an ORDER BY constraining rows into a unique order, "you will get an unpredictable subset of the query's rows," and repeated executions of the same LIMIT query can return different subsets (LIMIT and OFFSET). Non-deterministic functions form the second cluster: random(), unseeded RAND, CURRENT_TIMESTAMP, NEWID, and 'now'-based date arithmetic. A filter like WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL '1 day' evaluates at run time, so a job starting at 01:58 and one starting at 02:02 legitimately cover different windows.

Model layer. agent run output is not a deterministic function. A study of five LLMs configured to be deterministic, across eight tasks and ten runs, 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). If a downstream step parses numbers out of agent prose, move the number to the SQL result and let the agent describe it.

An Illustrative Scenario: The Nightly Reconciliation That Drifts

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

A nightly job runs three steps through cz-cli: a transaction pull, an aggregation, and an agent run summary. Monday reports 1,204,338; Tuesday reports 1,204,301. Nothing changed and every step exited 0.

Three causes could each produce that gap, and from the output alone they are indistinguishable. First, the pull ends in LIMIT 10000 with no ORDER BY; if the day holds more than 10,000 rows, a different subset is summed each night. Second, the pull filters on created_at >= CURRENT_TIMESTAMP - INTERVAL '24 hours'; a 01:58 start and a 02:04 start cover windows offset by six minutes. Third, the agent narrative is parsed downstream, and the model's phrasing drifts while the aggregate holds.

Isolation is mechanical. Re-run the pull twice with a unique ORDER BY and a fixed window and compare row counts; if they match, the first two causes are eliminated. Then compare the stable aggregate against the narrative; if the aggregate holds and the narrative moves, the third cause is confirmed. The common misdiagnosis is to fix the query first, watch the drift persist, and conclude the database is unreliable — when the remaining cause sat in a different layer.

An Acceptance Checklist You Can Run Against Your Own Setup

Each item is something to verify in your own environment, not a claim about how any deployment behaves.

  • Connection precondition. Run cz-cli status and assert exit code 0 before any dependent step.

  • Pinned profile. Set CZ_PROFILE explicitly in CI; confirm the pipeline fails loudly, rather than switching workspaces silently, when it is absent.

  • Exit-code branching. Assert on 0, 1, and 2 separately; never parse prose to decide success.

  • Both output paths tested. For your chosen --format, exercise the success envelope and the failure line, including the row-oriented ERROR <code>: <message> form.

  • Flag availability. Run cz-cli <command> --help on the installed version and confirm every flag your pipeline depends on appears there.

  • Unique ordering. Require a unique ORDER BY on any query whose order or LIMIT matters — unique meaning the key distinguishes every row.

  • Non-deterministic function audit. Grep the query set for random(), unseeded RAND, CURRENT_TIMESTAMP, NEWID, and 'now'-based arithmetic; record an explicit decision for each hit.

  • Async decision. For every --async call site, state whether the result is awaited or polled, and assert on the expected shape.

  • Agent output handling. Keep numbers in SQL results and let the agent describe them rather than produce them.

FAQ

Does cz-cli guarantee the same answer twice? No. The command layer and the query layer are independent; a stable envelope says nothing about row stability.

Are --sync and --timeout documented? Not in the retrieved README. Waiting appears to be the default, with --async as the opt-in. Confirm against cz-cli sql --help on the installed version.

What does connected: false mean? It is a diagnostic field. Since status exits 0 only when the connection works, the exit code is the authoritative check.

Does --async change the result? It changes what you receive and when — an identifier rather than a result. Whether the eventual result is stable is a query-layer question.

Why is a scripted invocation more auditable than an interactive session? The invocation is a reviewable artifact with a fixed envelope, exit code, and error shape; an interactive session leaves no reproducible command record. That is a property of the interface, not a claim about any specific deployment.

What to Confirm Before You Rely on This

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

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, so confirm against what you actually run. One further boundary: Singdata's platform context is separate from cz-cli's documented behavior, and nothing here should be read as a claim about how any particular lakehouse engine resolves ordering or evaluates time functions. Pin the command layer, prove the query layer, and test both.

Previous posts

https://www.singdata.com/

https://www.singdata.com/company

https://singdata.com/trending/powerbi-date-difference-dax-calculations/