1h 9m read

Verifiable SQL: How to Prove a Query Returned the Right Answer

A practical framework for verifiable SQL: the audit packet artefacts reviewers can demand, failure modes that survive execution, a pre- and post-production review process, row-level security verification, and an acceptance checklist for AI-generated SQL.

Verifiable SQL: How to Prove a Query Returned the Right Answer

A query that runs is a receipt: it proves something happened. A verifiable query is a bank statement: someone else can audit it line by line and reach the same number. Most enterprise SQL never gets past the receipt stage. The query returns rows, the dashboard renders, the pipeline goes green — and nobody can demonstrate that the number on screen answers the question anyone actually asked.

The stakes have shifted. Analysts now write less SQL by hand and accept more of it from language models, where a syntactically perfect query can be confidently wrong. Regulated workloads demand that a reviewer, an auditor, or a successor engineer reconstruct how a figure was produced months after the fact. And permission systems mean the same SQL text can legitimately return different rows for different people.

This article defines what verifiability means for SQL, sets out the artefacts a reviewer can demand, catalogues the failure modes that survive a successful execution, and gives a review process you can run before and after production. It closes with an acceptance checklist for AI SQL systems and an honest scope for cryptographic query proofs — which solve a narrower problem than most vendors imply.

What Makes SQL Verifiable — and How It Differs from Valid or Executable

Three properties get conflated in everyday data work, and separating them explains most silent failures.

Executable means the engine accepted the statement. Types resolved, objects existed, the parser was satisfied. This is the weakest signal available: it tests the query against the engine's grammar, not against your intent.

Valid means the statement is well-formed against the schema — columns exist, joins reference real keys, aggregates are legal in the grouping context. Validity is a property of the SQL text plus the catalog. It says nothing about whether the result answers the question.

Verifiable means a second party can independently confirm that the result matches the intended question, under a known data snapshot and a known permission context. Verifiability is not a property of the SQL text alone. It is a property of the evidence trail around the query.

The distinction matters because execution success and correctness are genuinely different properties. Alrashed et al. formalize text-to-SQL verification as a standalone task — deciding whether a candidate query semantically satisfies a natural-language request without access to ground-truth labels — and describe the central problem as deceptive failures: generating syntactically valid SQL that executes but fails to capture the user's intent. Their framing is worth taking seriously outside the AI context, because human analysts produce the same class of error.

One caveat keeps the concept honest. The same paper notes that formally proving semantic correctness is computationally intractable for many SQL fragments. Practical verifiability is therefore about evidence and reproducibility, not mathematical proof. You are not trying to demonstrate that a query is correct in all possible worlds; you are trying to make it cheap for a competent reviewer to confirm that it is correct in this one, against this snapshot, for this user.

The Audit Packet: Artefacts a Reviewer Can Demand

The practical unit of verifiability is a packet of artefacts that, taken together, lets someone reconstruct the answer without re-deriving your reasoning from scratch. No single item is sufficient. The framework below is a recommended practice, not an industry standard — but each item earns its place by closing a specific gap.

Query text with parameter bindings resolved. The SQL as written plus the values substituted at runtime. A parameterized query that filtered on region = ? is not auditable until you know which region. Store the bound statement, not the template.

The execution plan. The plan exposes which tables were scanned, which joins were applied, which filters were pushed down, and whether a predicate was silently dropped. It is the difference between what you asked for and what the engine did.

Schema and metric-definition snapshot. Column meanings drift. A revenue column that excluded refunds last quarter may include them now. Pinning the schema version and the business definition in force at execution time prevents a reviewer from validating the query against today's semantics and reaching the wrong conclusion.

Permission context. The role that executed the query, plus any row-access or masking policies in effect. As the next sections show, this is not a security footnote — it changes the row set, and therefore the answer.

Data snapshot or as-of timestamp. Results are only reproducible against a fixed state of the data. Without a snapshot identifier or a consistent as-of time, a re-run produces a different number and the review stalls on an unresolvable discrepancy.

Result fingerprint. Row count plus a hash or checksum over the result set. The fingerprint is cheap to store and lets you detect drift without retaining the full output — useful when the result itself is sensitive.

The packet's value is compositional. The plan without the snapshot tells you what the engine did to data you can no longer identify. The snapshot without the permission context tells you what data existed, not what the requester was allowed to see. The fingerprint without the query text detects that something changed but not what.

Failure Modes Where a Query Runs but Returns the Wrong Answer

The following examples are illustrative constructions, not observed incidents. Each shows how executable SQL produces a wrong answer that looks plausible on a dashboard.

Grain errors. Aggregating at the wrong level of detail. Summing an order-line table and calling the result "order revenue" double-counts shipping and tax lines attached to the same order. The query runs, the number is large and stable, and it reconciles against nothing. The audit packet catches it through the plan: a reviewer sees the aggregation applied directly to the line-level table with no preceding roll-up to order grain.

Fan-out from joins. Joining a one-to-many relationship before aggregating multiplies the rows being summed. A customer table joined to an orders table and then summed by customer produces a total inflated by the number of orders per customer. Educational treatments of this pattern describe it plainly: joining two tables before aggregating can silently multiply the rows being summed. The plan reveals the join order; a cardinality check on the join key reveals the multiplication.

NULL semantics. NULL is not a value, and aggregates treat it inconsistently. Databricks' NULL semantics documentation shows the behaviour concretely: count(*) does not skip NULLs while count(age) does, so the same table yields 7 for one and 5 for the other. max(age) excludes NULLs from computation, and on an empty input set count(*) returns 0 while max returns NULL. A dashboard showing "average age" computed over a column with missing values is reporting the average of the non-missing subset — which may be exactly what you want, or may be a silent bias. The audit packet catches it only if the query text and the null-handling convention are both recorded.

Time-zone handling. PostgreSQL's date/time documentation is explicit: timestamp with time zone values are stored internally as UTC and converted to the session's TimeZone setting for display, while timestamp without time zone carries no zone information at all. A daily revenue report grouped by a timestamp without time zone column will bucket transactions differently depending on where the writer's clock was set. The same query text, run by two teams in different regions, produces two different daily totals. The packet catches it through the schema snapshot: the column type is recorded, and the reviewer can see that no explicit zone conversion was applied.

Metric-definition mismatch. Two teams compute "active user" differently — one counts any login in 30 days, the other requires a qualifying action. Both queries are correct against their own definitions. The dashboard that blends them is not. The packet catches it through the metric-definition snapshot, which forces the definition into the record rather than leaving it in someone's head.

Permission leakage. A query returns rows the requester should not see because a policy was bypassed, a view was not secured, or the query ran under a service account with broader access than the caller. The aggregate looks right; the row set underneath it is wrong. This failure mode gets its own section below, because it is a correctness problem disguised as a security problem.

A Review and Testing Process for Before and After Production

The process below is about producing evidence, not ceremony. Run the pre-production steps once per query or model change; run the post-production steps continuously.

Prerequisites

A fixture dataset with known answers, a low-privilege test role, and a place to store the audit packet. Without the fixture you cannot test correctness; without the low-privilege role you cannot test permissions; without storage the packet evaporates.

Before production

  1. Capture intent. Write the business question in plain language next to the SQL. If the two cannot be reconciled by a colleague, stop.

  2. Declare the grain. State the level of detail the result represents — one row per order, per customer per day, per SKU per region. Check that every join preserves it.

  3. Check join cardinality. For each join, confirm whether the key is unique on the joined side. A one-to-many join before aggregation is a fan-out waiting to happen.

  4. Review NULL and time-zone handling. Decide explicitly whether NULLs should be included, and confirm every timestamp comparison uses a zone-aware type or an explicit conversion.

  5. Sign off the metric definition. Record which definition of each metric the query implements, and who approved it.

  6. Test with a low-privilege role. Execute the query as a restricted user and confirm the row set matches what that user should see — not what the developer sees.

  7. Run a known-answer test. Execute against the fixture and compare to the expected result. This is the checkpoint that catches grain and fan-out errors before they reach a dashboard.

After production

  • Log the result fingerprint on every scheduled run. Row count plus checksum, stored alongside the snapshot identifier.

  • Alert on row-count drift beyond a threshold you set deliberately. A sudden change in row count usually means a join changed cardinality or a filter stopped matching.

  • Re-execute periodically against a pinned snapshot to confirm the query still returns the same fingerprint. If it does not, either the data moved or the semantics did — and you need to know which.

  • Gate schema and policy changes through review. A column rename or a new row-access policy can change results without touching a single line of query text.

Common failure modes in the process itself

The most frequent failure is skipping the low-privilege test because the developer's account is convenient. The second is treating the fixture test as a one-time gate rather than a regression check that runs whenever the query changes. The third is logging fingerprints without the snapshot identifier, which makes drift undiagnosable.

Next steps

Adopt the audit packet for your ten most-cited metrics first. Those are the numbers that get quoted in board decks, and they are where a reviewer's time buys the most assurance.

Why Permission and Row-Level Security Verification Is Part of Correctness

A query that returns the right aggregate over the wrong row set is not correct. It is a correctness failure that happens to look like a security issue, and treating it purely as a security concern means the correctness dimension never gets tested.

Row-level security makes the point concrete. Snowflake's row access policy documentation describes the mechanism: when a policy is attached to a database object, Snowflake creates a dynamic secure view — a secure inline view — of that object at query runtime. Where nested views exist, policies are applied in sequential order from left to right. The consequence is that the same SQL text returns different rows for different roles, by design.

That is the correct behaviour, and it is also why permission context belongs in the audit packet. A reviewer validating a result against the full table will conclude the query is wrong; a reviewer validating it against the caller's permitted row set will conclude it is right. Neither can decide without knowing which role executed it and which policies were in force.

Snowflake's Access History documentation shows that this context is recordable: the policies_referenced column reports the policies under enforcement when a query runs, including intermediate objects protected by row access or masking policies. The evidence exists; the question is whether your process captures it.

The compliance framing is not decorative. GDPR Article 32 requires controllers and processors to implement appropriate technical and organisational measures, and specifically includes "a process for regularly testing, assessing and evaluating the effectiveness of technical and organisational measures for ensuring the security of the processing." A permission policy that has never been tested against a low-privilege role is an untested measure. Verifying that the policy produces the intended row set is the test.

Keep the scope tight: Article 32 sets a standard of appropriateness relative to risk, not a specific testing cadence or a mandated artefact list. The audit packet is one way to satisfy the testing obligation; it is not the only one, and the regulation does not prescribe it.

An Acceptance Checklist for AI SQL and Natural-Language Analytics Systems

Text-to-SQL systems fail differently from hand-written SQL, and the verification literature suggests what to demand from them. Use the criteria below as buyer-testable questions, and validate any vendor performance claim in your own environment rather than accepting it as established.

Reported BIRD-Dev execution accuracy (%, by backbone) Horizontal bar chart comparing reported execution accuracy on the BIRD-Dev benchmark. PV-SQL reaches 65.12% with GPT-4o. VET reaches 70.93% with GPT-4o and 68.38% with DeepSeek-Chat. Each value comes from its own paper's evaluation setup. Reported BIRD-Dev execution accuracy (%, by backbone). horizontal bar data: PV-SQL (GPT-4o) 65.12; VET (DeepSeek-Chat) 68.38; VET (GPT-4o) 70.93.Source: PV-SQL (Findings of ACL 2026) and VET (Findings of ACL 2026) 2026. Reported BIRD-Dev executionaccuracy (%, by backbone) PV-SQL (GPT-4o) 65.12 VET(DeepSeek-Chat) 68.38 VET (GPT-4o) 70.93 Source: PV-SQL (Findings of ACL 2026) and VET(Findings of ACL 2026) (2026)

Execution accuracy on BIRD-Dev as reported by each paper, with the backbone model each figure was measured on. These are benchmark results on specific datasets and model backbones under the papers' own settings; they are not guarantees of accuracy on another schema or workload, and the two systems were not evaluated against each other in a single controlled comparison.

Does it expose the generated SQL, the plan, and the data snapshot? Without all three, the audit packet cannot be assembled and the output is unverifiable by construction.

Does it abstain when confidence is low? Alrashed et al. describe selective generation, where the system uses verification signals to abstain rather than answer, transforming silent failures into explicit abstentions that alert the user. A system that always returns an answer has no mechanism for signalling uncertainty. Ask whether abstention is available and how it is surfaced.

Does it probe the database to resolve value ambiguity? PV-SQL (Tian & Zhang, Findings of ACL 2026) generates probing queries to retrieve concrete records, resolving ambiguities in value formats, column semantics, and inter-table relationships before committing to the final SQL. The paper reports that its rule-based verifier achieves 99.39% precision on constraint extraction — a figure about the extractor's precision, not about end-to-end query correctness, and worth reading in that scope.

Does it show an executable reasoning trace? VET (Wang et al., Findings of ACL 2026) reformulates text-to-SQL as a sequence of executable steps, each run against the real database so intermediate results serve as verification feedback. The paper reports 70.93% execution accuracy on BIRD with GPT-4o and 37.04% on Spider 2.0-lite with DeepSeek-Chat under matched, training-free settings. Those are benchmark results on specific datasets and backbones; they are claims to reproduce, not guarantees.

Does it enforce the caller's permission context rather than a service account's? A system that executes generated SQL under a privileged service account can return rows the user should never see. Test this with a restricted role, not with an administrator.

How good is the verification signal itself? Alrashed et al. report that their verification methods flagged 64% of errors from a state-of-the-art generator — a useful signal, not a complete one. The same work audited benchmark labels and found that over two-thirds of "generator failures" on the benchmarks studied stemmed from flawed labels rather than generator errors. That finding cuts both ways: it means benchmark accuracy understates real system quality, and it means your own evaluation set may be wrong. Verify your ground truth before you use it to judge a vendor.

Do Cryptographic Query Proofs Matter for Ordinary Enterprise Analytics?

Cryptographic query proofs solve a real problem, but it is narrower than the phrase suggests.

vSQL reported server vs. client time (seconds, log scale) Grouped bar chart on a logarithmic axis. For TPC-H query #19, server time is 4,892 s and client verification is 0.162 s. For query #5, server time is 5,069 s and client verification is 0.398 s. vSQL reported server vs. client time (seconds, log scale). grouped bar data: TPC-H #19: Server time (s) 4892, Client verification (s) 0.162; TPC-H #5: Server time (s) 5069, Client verification (s) 0.398.Source: vSQL: Verifying Arbitrary SQL Queries over Dynamic Outsourced Databases 2017. vSQL reported server vs.client time (seconds, logscale) Server time (s) Clientverification(s) 0 2500 5000 7500 10000 4892 0.162 TPC-H #19 5069 0.398 TPC-H #5 Source: vSQL: Verifying Arbitrary SQL Queries overDynamic Outsourced Databases (2017)

Server (prover) time and client (verification) time for TPC-H queries #19 and #5, as reported in vSQL's comparison table. Server time dominates by three to four orders of magnitude; the chart uses a logarithmic axis so both quantities are visible. Figures are from a single-paper evaluation on TPC-H-style workloads and do not describe a governed enterprise warehouse.

The setting is specific: a data owner outsources storage to an untrusted server and wants to verify that query results are correct without re-executing the query. The mechanism, at a high level, is a digest held by the client, a proof returned alongside the result, and a verification algorithm that checks the proof against the digest. IntegriDB (Zhang, Katz & Papamanthou) describes a data owner, a client, and a server, with the owner running setup and sending the digest to the client while the server holds the database and authentication information.

The reported figures come with conditions worth preserving. IntegriDB reports small proofs (a few KB) that depend only logarithmically on database size, verification in tens of milliseconds, and feasible server computation (under a minute) for tables with 10⁵ entries. It supports a rich subset of SQL — multidimensional range queries, JOIN, SUM, MAX/MIN, COUNT, AVG, and limited nesting — and the paper reports support for 12 of the 22 TPC-H queries. On TPC-H query #19, the paper's table reports a 232 ms verification time and a 184.16 KB proof, alongside a 25,272.76 s setup time and 6,422.13 s prover time. Those setup and prover costs are part of the result, not an implementation detail.

vSQL (Zhang et al.) reports support for all TPC-H queries, with server time linear in the size of the query's inputs and outputs and independent of the circuit representation. Its comparison table shows server times of 4,892 s for query #19 and 5,069 s for query #5, with client verification of 162 ms and 398 ms respectively.

Spitz (Zhang et al., PVLDB 13(12)) observes that most existing work on verifiable queries focuses on OLTP workloads, and that while general OLAP queries can be made verifiable — for example by using fully homomorphic encryption — they involve complex cryptographic operations and incur significant overhead.

For most enterprise analytics, the practical answer is the audit packet and the review process, not cryptographic proofs. The setup and prover costs above are measured in hours for single queries, and the trust model — an untrusted server you cannot audit — does not describe a team querying its own governed warehouse. Where the cryptographic approach earns its cost is in outsourced-database and multi-party settings, where the party answering the query is genuinely not trusted by the party asking it.

Conclusion

Verifiability is a property of the evidence trail, not of the SQL text alone. Executable, valid, and verifiable are three different tiers, and most production queries only clear the first.

Two artefacts are worth adopting first. The audit packet — query text with bindings, plan, schema and metric snapshot, permission context, data snapshot, and result fingerprint — makes any query reviewable after the fact. The acceptance checklist for AI SQL systems makes the same standard testable before you buy.

Neither requires cryptographic infrastructure. Both require that someone decide, in advance, what evidence a reviewer will need.

FAQ

Is a query plan alone sufficient evidence? No. The plan shows what the engine did, but not against which data snapshot, under which permissions, or with which metric definition. It is one artefact in the packet.

Can result fingerprints be compared across schema changes? Not directly. A fingerprint is only meaningful against a known schema and metric-definition snapshot. If a column's meaning changed, a matching fingerprint may indicate coincidence rather than correctness, and a differing one may reflect the schema change rather than a data problem. Compare fingerprints only within a pinned schema version.

Do row-level security policies affect query performance? Snowflake's documentation notes that the platform must scan columns the policy is bound to, even when those columns are not referenced in the query, and recommends policies with fewer subqueries, limited joins, and simplified WHERE conditions. The practical implication for verification is that the plan will show scans you did not write — which is expected, and worth recording in the packet so a reviewer does not mistake it for a stray predicate.

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/

Related Articles