# AI-Assisted SQL With Verification: Evidence Dossier

**Research snapshot:** October 10, 2026  
**Perspective:** Academic  
**Decision supported:** How should AI-generated analytical SQL be checked for grain, joins, filters, source fidelity, permissions, and misleading totals?  
**Target setting:** A small professional data team using an analytical warehouse and dbt-style transformations.  
**Evidence boundary:** Public primary research, official/versioned documentation, and synthetic fixtures only. No private company data were accessed. Benchmark results are treated as evaluation evidence, not as field outcomes.

## Executive answer and uncertainty

The strongest conclusion from the evidence is that **“the SQL ran” is an inadequate acceptance criterion, and “another LLM says it looks right” is not a sufficient replacement**. A reliable AI-assisted SQL workflow needs independent layers of verification anchored in an explicit analytical contract: metric definition, grain, allowed join paths, time semantics, source/version identity, and permissions. It should then execute the candidate SQL against controlled data with independently known answers and adversarial cases. This conclusion follows from several distinct findings: static-database execution tests can accept non-equivalent SQL; benchmark gold SQL itself can be wrong or ambiguous; enterprise schemas create retrieval and join problems beyond SQL syntax; and LLMs remain imperfect at diagnosing semantic SQL bugs. citeturn22view1turn2view1turn4search0turn5view12

For the decision in this dossier, I would implement a **verification ladder**, not a monolithic AI reviewer:

| Layer | Primary question | Acceptance condition |
|---|---|---|
| Analytical contract | What exactly are we calculating? | Grain, measure, timezone, refund/cancellation policy, customer-status semantics, and total behavior are explicit. |
| Source fidelity | Is the SQL based on the authoritative current definition? | Exact semantic-model/dbt version, schema/catalog and definition version are recorded and match the approved source. |
| Static structural checks | Is there an obvious grain/join/access problem before execution? | Only allowed relations/columns, declared join paths, expected cardinalities and read-only SQL. |
| Warehouse execution | Does the SQL parse and execute under the real dialect and permissions? | Successful read-only execution under the same restricted role that production use permits. |
| Semantic test execution | Does it answer the specified question on cases designed to distinguish correct from plausible SQL? | Exact match against independently known results on baseline and adversarial fixtures. |
| Reconciliation | Do aggregates obey domain invariants? | Revenue components reconcile, temporal dimensions match once, totals are recomputed at their proper grain. |
| Human/abstention gate | Is the question itself underdetermined or materially risky? | Clarify or review rather than silently choosing a business definition. |

This separation matters because even dbt's current Semantic Layer distinguishes semantic validation from data-platform validation: its platform validation checks that tables/columns exist and generated SQL can execute; those checks do **not** establish that a business metric is the intended one. dbt's join system also requires entity/cardinality declarations specifically to restrict fanout/chasm patterns. citeturn13view0turn13view2turn13view3

**The practical recommendation is therefore to let the model propose SQL but not let the model define correctness.** Correctness should ultimately be determined by independently versioned semantics, deterministic calculations, executable fixtures/invariants, database permissions, and human judgment where definitions remain ambiguous. This is an inference from the combined evidence rather than a directly tested causal result.

There are three important uncertainties.

First, benchmark accuracy cannot be translated into a probability that a query on a particular company's warehouse is correct. Spider, BIRD, Spider 2.0 and BEAVER differ substantially in schema scale, provenance, context supplied to systems, question construction and evaluation methodology. citeturn2view3turn1search4turn19view0turn19view1

Second, rigorous query-equivalence checking has limits. SPOTIT demonstrates that bounded formal verification can find counterexample databases missed by conventional execution testing, but arbitrary SQL equivalence is not generally decidable, and the system does not support every SQL construct; its Spider 2.0 analysis in particular encounters unsupported features such as window functions. citeturn2view1turn3view1turn3view2

Third, this review found strong benchmark and methodological evidence but **did not identify a controlled enterprise field experiment showing that a particular verification stack causally reduces wrong business decisions**. That absence is consequential: the architecture below is a reasoned engineering recommendation supported by failure evidence, not a claimed measured ROI result.

## What the research actually establishes

The text-to-SQL literature has progressively moved from relatively clean schema-generalization tasks toward settings that better resemble analytical work, but each generation of evaluation exposes reasons not to equate a leaderboard score with production reliability.

The original Spider benchmark was important because database-level splitting required systems to generalize to schemas not seen in training rather than memorize query templates within the same database. That is a stronger test than older in-domain datasets, but later work showed that real databases contain messier names, documentation needs and unrestricted questions. KaggleDBQA deliberately used real web databases with original formatting and domain-specific types; adding naturally occurring database documentation materially improved parser performance, demonstrating that schema context and domain knowledge are part of the task, not peripheral conveniences. citeturn7academia25turn22view0

BIRD expanded the challenge to 12,751 question-SQL pairs over 95 databases, 37 domains and 33.4 GB of data, deliberately emphasizing database values, external/domain knowledge and efficiency. The original paper reported a large gap between its evaluated ChatGPT setup and human performance, but those historical model numbers should be interpreted as evidence about the challenge introduced by the dataset, **not** as a 2026 estimate of current model capability. citeturn2view3

Spider 2.0 moved considerably closer to enterprise analytical workflows: 632 problems drawn from enterprise database use cases, frequently involving schemas above 1,000 columns, Snowflake/BigQuery-style environments, metadata/documentation lookup and multi-step SQL that may exceed 100 lines. Its initial code-agent baseline scored far below its performance on the original Spider benchmark, illustrating how much easier a conventional benchmark can be than a workflow involving metadata retrieval, SQL dialects and project context. Again, that historical baseline is useful as a task-realism signal rather than as a current model ranking. citeturn1search4

BEAVER, in its May 2026 v3, pushes further toward enterprise schema scale. Its three source warehouses contain hundreds of tables and thousands of columns, and the benchmark's question-SQL work originates partly from actual organizational logs/reports and domain experts. But an important provenance qualification is easy to miss: BEAVER expands the dataset using templates extracted from real query logs and LLM-generated query composition, with expert review. Thus, its 9,128 test queries should **not** all be described as untouched natural user queries taken directly from production logs. citeturn19view0turn19view1

That distinction matters when interpreting BEAVER's difficulty. The May 2026 evaluation reports very low end-to-end execution accuracy even for recent agentic methods—for example, 10.8% for ReFoRCE with GPT-5.2 in its reported setup—but this is evidence about one benchmark construction and evaluation pipeline, not an estimate that “GPT-5.2 is 10.8% correct on enterprise SQL.” The benchmark itself uses retrieval to select tables and execution equality to grade final answers, and its own fine-grained analysis separates table retrieval, column mapping and join-key selection from final execution. citeturn19view2

### Why execution match is not semantic proof

A SQL query can be semantically different yet happen to produce the same result on one database instance. Conversely, a semantically equivalent query can differ syntactically or even produce apparently different representations that naive evaluators penalize.

Zhong, Yu and Klein addressed part of this problem in 2020 with **test-suite accuracy**: evaluate predicted and reference queries on multiple automatically generated database instances designed to exercise the reference query rather than on only one static database. Across 21 Spider submissions, their manually checked sample showed that the then-current Spider evaluation metric produced an average 2.5% false-negative rate and up to 8.1% for one system. citeturn22view1

SPOTIT advances the same basic idea using bounded formal equivalence verification. Rather than waiting for an existing database to reveal a difference, it uses an SMT-based verifier to seek a small database on which candidate and reference SQL disagree. On ten systems evaluated on BIRD, the authors report substantial reductions in apparent accuracy relative to standard test-based evaluation and changes in method rankings. They also find cases where the generated query exposes a problem in the *gold* query rather than the candidate. citeturn2view1turn3view2

This is directly relevant to analytical verification: **a fixture should contain rows that make the dangerous alternative interpretation produce a different answer**. A test database where every customer has one order, every order has one item and every order has at most one refund cannot reveal a fanout bug. A dataset where no transaction lies near a timezone boundary cannot reveal a UTC-versus-business-time filter error.

Formal verification does not remove the need for fixtures or human semantics. SPOTIT is bounded, supports only a subset of SQL, and its Spider 2.0 experiment demonstrates reduced applicability as query language complexity grows. More fundamentally, proving candidate SQL equivalent to a reference SQL only proves equivalence to that reference. If the reference expresses the wrong business definition, both can be perfectly equivalent and perfectly wrong. citeturn3view1turn3view2

### Gold labels themselves are a source of uncertainty

This is no longer a theoretical concern. Jin et al.'s 2026 expert audit reports errors in 263 of 498 BIRD Mini-Dev examples (52.8%) and 76 of 121 Spider 2.0-Snow examples (62.8%) in the subsets they inspected. Correcting the BIRD subset changed both scores and rankings among 16 evaluated agents. These numbers are **audit results for those specific subsets**, not evidence that 52.8% of all BIRD or 62.8% of all Spider 2.0 examples are wrong. citeturn4search0turn5view9

The same work reports that a July 2025 Spider 2.0 ambiguity update improved the audited Snow subset only modestly according to the authors, reinforcing that benchmark curation itself is an ongoing source of uncertainty. citeturn5view10

A complementary 2025 benchmark, NL2SQL-BUGs, contains 2,018 expert-annotated examples spanning nine broad error categories and 31 subcategories. Across the tested models, average semantic-error detection accuracy was 75.16%; some join-error categories were substantially harder. That evidence argues against treating an LLM critic as a trustworthy oracle, even when the task is merely to spot an existing semantic bug rather than generate the whole query. citeturn5view2turn5view12

### Robustness and transfer remain separate from nominal accuracy

Dr.Spider applies 17 kinds of perturbations to database schemas, natural-language questions and SQL. Even the most robust model studied in the 2023 paper lost 14.0% overall under perturbation, with much larger degradation for its hardest perturbation. The specific models are now dated, but the result is still useful evidence that performance on canonical benchmark wording does not guarantee robustness to semantically irrelevant schema or wording changes. citeturn2view4

KaggleDBQA points in the same direction from another angle: unfamiliar real database conventions and missing domain documentation make cross-schema transfer materially harder. citeturn22view0

The implication is that an internal evaluation should explicitly perturb names, optional columns, null distributions, cardinalities, temporal boundaries and documentation versions. A single pristine fixture tests implementation; adversarial fixture variants test the assumptions hiding inside it.

## Verification architecture for grain, joins, filters and totals

For a small data team, the useful design principle is **independent failure detectors**, not maximal agent complexity. Each check should catch a class of errors using information that does not originate solely from the SQL-generating model.

### The analytical contract comes before SQL

The input to generation should include a machine-readable contract with at least:

| Contract field | Example for the synthetic task | Why it is required |
|---|---|---|
| Final grain | one row per local calendar month | Prevents accidental order/customer/item-level output. |
| Source grains | orders: one/order; items: one/item; refunds: one/refund event; history: one/customer/status interval | Makes multiplicity visible before joining. |
| Measure definition | sale gross − cancellation reversals − refunds | Prevents the model inventing “net revenue.” |
| Recognition event | sale at `ordered_at`; cancellation at `cancelled_at`; refund at `refunded_at` | Determines which month receives each amount. |
| Business timezone | `Europe/Prague` | Determines month boundaries. |
| Temporal dimension semantics | SCD intervals `[valid_from, valid_to)` | Prevents current-state joins to historical facts. |
| Active-customer definition | distinct customer with a sale event while status was `active` at order time | Prevents several equally plausible alternatives. |
| Total behavior | revenue additive over months; active-customer count non-additive | Prevents summing distinct counts. |
| Currency/unit | EUR in this fixture; no tax, shipping, FX or discounts | Makes numerical meaning explicit. |
| Permissions | approved semantic/mart relations only | Stops generated SQL from widening access. |

This is conceptually compatible with current dbt Semantic Layer design. dbt semantic models explicitly distinguish primary, unique and foreign entities and time dimensions; MetricFlow uses those entity types to select join paths and rejects several relationships that would create fanout. citeturn11view0turn13view0

But a semantic layer is not an oracle. If an entity is falsely declared unique, or a business metric encodes the wrong refund rule, downstream SQL may become consistently and reproducibly wrong. The semantic layer therefore needs tests against actual cardinality and business fixtures, not just YAML validation. dbt itself supports `unique`, `not_null`, `relationships`, `accepted_values` and arbitrary SQL data tests, which are useful primitives for such checks. citeturn14search8turn16view0

### Grain checks should be explicit

For every candidate query, calculate or assert three things before trusting an aggregate:

**Input grain.** What does one row represent at each source or CTE?

**Join multiplicity.** For every join, can one base row match zero, one or many rows on each side?

**Aggregation grain.** At what grain is each measure computed *before* any many-side relation is introduced?

A good automated warning is not merely “there is a `JOIN`.” It is closer to:

> `orders → order_items` is 1:N and `orders → refunds` is 1:N. Joining both children at raw grain creates an N:M multiplication through the order. Aggregate each child to `order_id`, or model them as separate event streams.

That is exactly the class of risk MetricFlow calls a fan-out or chasm join and attempts to restrict based on declared entity relationships. citeturn13view0

For important metrics, declared cardinality should also be **tested from data**. A declared primary entity should be unique; a supposedly one-to-one temporal lookup should match exactly one row; SCD intervals should not overlap. dbt's own snapshot guidance stresses that the unique key used to identify versions must truly be unique. citeturn15view0

### Join verification needs both metadata and executable counterexamples

Static metadata can reject an obviously unsafe fact-to-fact join, but actual execution should try to falsify the query.

For the target workflow, adversarial data should include at least one:

- order with multiple items **and** multiple refunds;
- order with no refunds;
- customer with more than one historical-status record;
- event exactly at or close to a month/timezone boundary;
- refund occurring in a different month from its order;
- cancellation occurring in a later month;
- month containing only negative adjustments;
- customer appearing in several months.

Those cases are not arbitrary torture tests. They are the analytical analogue of test-suite evaluation and SPOTIT's differentiating databases: construct data specifically so that non-equivalent query semantics stop producing the same answer. citeturn22view1turn2view1

### Filters need boundary tests, not visual inspection

Time filters deserve executable tests for both endpoints. Prefer half-open periods:

```sql
event_at >= period_start
AND event_at < next_period_start
```

and derive the period **after** converting the event timestamp to the defined business timezone. The fixture below contains two events that are still in the prior month in UTC but are already in the next month in Prague.

Null behavior also needs semantic tests. `NULL` may mean “no refund row exists,” “unknown refund amount,” “open-ended SCD interval,” or “unknown customer status.” Those meanings are different. A generic `COALESCE(..., 0)` can be correct for the absence of an aggregated refund but dangerously wrong for an unknown monetary value.

### Slowly changing dimensions must be point-in-time joins

dbt snapshots explicitly implement Type-2 slowly changing dimensions and represent validity with `dbt_valid_from` and `dbt_valid_to`. citeturn15view0

For a fact timestamp `t`, the intended pattern is normally:

```sql
fact.customer_id = history.customer_id
AND t >= history.valid_from
AND (t < history.valid_to OR history.valid_to IS NULL)
```

assuming the business contract uses half-open intervals.

Joining only on `customer_id` can duplicate every fact across multiple historical records. Joining to the *current* row answers a different question: “what is this customer's status now?” rather than “what was the customer's status when the event occurred?”

An executable invariant should therefore verify that every fact expected to have a dimension match receives **exactly one** interval match.

### Totals require measure-type awareness

“Total” is often where correct monthly SQL becomes a misleading dashboard.

Revenue under a stable event-based definition is additive:

\[
NetRevenue_{Mar-May}
=
NetRevenue_{Mar}
+
NetRevenue_{Apr}
+
NetRevenue_{May}
\]

A distinct-customer count is not:

\[
DistinctCustomers_{Mar-May}
\neq
ActiveCustomers_{Mar}
+
ActiveCustomers_{Apr}
+
ActiveCustomers_{May}
\]

when a customer can appear in more than one month.

For a distinct or semi-additive measure, a grand total must be **recomputed from the underlying qualifying entities at the total grain**, not produced by summing displayed monthly counts. Semantic models can encode aggregation logic, but the non-additivity still has to be represented correctly in the metric definition. citeturn11view0turn14search2

### Source/version fidelity is a separate acceptance criterion

A candidate can be semantically impeccable relative to a stale definition and still answer the wrong organizational question.

Every accepted result should therefore retain an evaluation envelope such as:

```text
question
resolved_metric_contract_id + version
dbt/project Git SHA
semantic-manifest hash/version
warehouse + catalog/schema
business timezone
as-of timestamp where relevant
permitted database role
generated SQL
query parameters
model/system version
SQL/parser/verifier versions
warehouse query ID
fixture/test-suite version
result hash
verification outcomes
human reviewer / clarification record, if any
```

Current dbt documentation is useful here because grants form part of project metadata/manifest, and local Semantic Layer validation can explicitly operate against a local semantic manifest including uncommitted changes. citeturn16view2turn13view2

The requirement is stronger than conventional lineage. The result should be reproducible against the **same definition revision**, not merely traceable to a table with the same name.

### Permissions should be enforced outside the model

Do not ask the LLM to remember that a table or PII column is forbidden. Execute through a warehouse identity that cannot read it.

dbt grants can configure select privileges for models, snapshots and seeds and reconcile them during builds; more granular row/column policies may need warehouse-native capabilities. citeturn16view2turn16view3

For Czech/EU organizations, this also aligns with a concrete regulatory concern where personal data are involved: GDPR Article 25 requires data-protection-by-design/default measures such that only personal data necessary for the particular purpose are processed and that accessibility is limited by default. citeturn22view3

There is **no material Czech-versus-US distinction in the mechanics of grain, fanout, temporal joins or aggregation verification**. The material difference is the governance/legal envelope around which data may be queried. This dossier does not attempt a comprehensive mapping of US state or sector privacy law; US teams should bind the same technical controls to their applicable organizational and legal rules rather than copying the GDPR rationale wholesale.

## Worked synthetic example and portable verification asset

**Business request:**

> Calculate monthly net revenue and active customers from orders, order items, refunds and customer history.

That request is **not sufficiently defined to generate final SQL**.

Before generation, a reliable system should ask or retrieve authoritative answers to questions such as:

- Is a refund subtracted in the month of the original sale or the month the refund occurs?
- Is a cancellation a retroactive removal of the order or a negative event on the cancellation date?
- Does “active customer” mean a purchaser, a currently enabled account, a customer with positive net revenue, or something else?
- Is customer status evaluated currently or at the order timestamp?
- Which timezone defines a calendar month?
- Are tax, shipping, discounts, chargebacks and FX included?

Silently choosing among these alternatives is a semantic error even if the resulting SQL executes.

For this fixture only, the ambiguity is resolved with the following **synthetic contract**:

**Grains:** `orders` = one row/order; `order_items` = one row/item; `refunds` = one row/refund event; `customer_history` = one row/customer/status-validity interval; final output = one row/month.

**Currency/unit:** all amounts are interpreted as EUR. Tax, shipping, discounts, chargebacks and FX are outside scope.

**Timezone:** `Europe/Prague`.

**Revenue recognition:**

\[
NetRevenue_m =
\sum_{orders:\ local(ordered\_at)\in m}GrossOrder
-
\sum_{cancellations:\ local(cancelled\_at)\in m}GrossOrder
-
\sum_{refunds:\ local(refunded\_at)\in m}RefundAmount
\]

with:

\[
GrossOrder_o = \sum_{items\ in\ o} quantity_i \times unitPrice_i
\]

For this fixture, a cancellation and refund on the same order is prohibited because otherwise the business contract would need a rule to avoid double reversal.

**Active customers:**

\[
ActiveCustomers_m =
\left|
\left\{
c:
\exists\ order\ by\ c\ in\ m
\land status_c(order\_time)=active
\right\}
\right|
\]

A later refund or cancellation does not retroactively remove the customer from this activity definition.

**Customer-history interval:** `[valid_from, valid_to)`.

The key synthetic records are:

| Object | Timestamp / status | Amount or role | Verification trap |
|---|---:|---:|---|
| Order 1001 / C1 | 2026-03-30 10:00 UTC | two items: €100 + €50 | Two child item rows. |
| Refunds on 1001 | Apr 2 and Apr 10 | €20 + €10 | Two refund rows; creates a 2×2 fanout if joined directly to items. |
| Order 1002 / C2 | 2026-03-31 22:30 UTC | €80 | It is **April 1 00:30 in Prague**, not March. |
| Refund on 1002 | 2026-04-30 22:15 UTC | €15 | It is **May 1 00:15 in Prague**, not April. |
| Order 1003 / C1 | Apr 20 | €40 | C1 became `suspended` Apr 15; revenue remains, but C1 is not an active customer. |
| Order 1004 / C3 | Apr 5; cancelled May 1 | €60 | Sale and cancellation belong to different months. |
| Order 1005 / C2 | 2026-04-30 22:30 UTC | €25 | Local month is May. |

The independently derived expected monthly result is:

| Local month | Net revenue | Active customers |
|---|---:|---:|
| 2026-03 | €150.00 | 1 |
| 2026-04 | €150.00 | 2 |
| 2026-05 | −€50.00 | 1 |

The revenue reconciliation is visible:

\[
GrossSales = 150+80+40+60+25 = €355
\]

\[
Refunds = 20+10+15 = €45
\]

\[
Cancellations = €60
\]

\[
Net = 355-45-60 = €250
\]

and:

\[
150+150-50 = €250
\]

The monthly active-customer counts sum to:

\[
1+2+1=4
\]

but there are only **three distinct qualifying customers across the whole period** (`C1`, `C2`, `C3`). A dashboard that labels `4` as “Total active customers” is therefore misleading even though every monthly number is individually correct.

The fanout counterexample is even more direct. This query looks innocuous:

```sql
SELECT
    SUM(i.qty * i.unit_price) - SUM(r.refund_amount)
FROM order_items i
JOIN refunds r USING (order_id)
WHERE order_id = 1001;
```

For order 1001 there are two items and two refunds, so the join creates four rows.

The correct lifetime amount is:

\[
€150 - €30 = €120
\]

but the direct join calculates:

\[
(100+50+100+50)-(20+10+20+10)
=
300-60
=
€240
\]

The query executes successfully, uses valid columns and returns an entirely plausible number. It is nevertheless wrong by 100%.

`COALESCE` is not a remedy for this. It solves a different problem: an order with no refund rows can produce `NULL` in arithmetic after a left join. Pre-aggregating items and refunds independently to order grain, or representing sales/refunds as separate event streams, is what removes the fanout.

The SCD trap behaves similarly. C1 has an `active` and a later `suspended` history row. Joining orders to customer history only on `customer_id` duplicates C1 facts. The correct point-in-time join must also apply the validity interval.

**Portable test asset:** [Download the PostgreSQL verification fixture](sandbox:/mnt/data/ai_sql_verification_fixture_postgres.sql).

The file contains the complete schema, synthetic rows, canonical calculation, temporal SCD join, invariants, exact expected-result assertion, a deliberately wrong fanout query and a non-additive-total demonstration. It targets PostgreSQL 14+.

**Execution-status disclosure:** the SQL file was authored for this dossier, and its numerical/timezone expectations were independently derived; it was **not executed against a live PostgreSQL server during this research session**. It must therefore be treated as an executable test asset awaiting environment execution, not as a reported test run.

## Reproducible evaluation protocol and analyst baseline

The useful evaluation unit is not “Can the model generate syntactically valid SQL?” It is:

> Given the same authoritative definition, data access and schema context as an analyst, can the system reliably return the independently known analytical answer without violating permissions or hiding ambiguity?

The experiment should compare at least three workflows on the **same tasks and oracle outputs**:

| Workflow | Purpose |
|---|---|
| Analyst/reference baseline | Establish what a conventional analyst can do with the same semantic contract, documentation and test harness. |
| AI generation + ordinary execution | Quantify how misleading “it ran successfully” can be. |
| AI generation + verification ladder | Measure whether semantic tests, permissions, source fidelity and clarification gates improve accepted-answer reliability. |

The analyst baseline is important. Benchmark leaderboard comparisons answer “which text-to-SQL method performs better under benchmark conditions.” They do not answer whether AI improves this team's analytical workflow relative to an analyst using a semantic layer, tests and ordinary SQL tooling.

### Representative task family

Start with the supplied revenue/customer task and mutate one property at a time while preserving independently known answers.

A useful suite should contain variants covering:

| Variant | Failure being exposed | Expected behavior |
|---|---|---|
| two items × two refunds | fanout | Produce correct value without multiplication. |
| no refund row | null/absence | Preserve sale amount. |
| nullable unknown amount | semantic null | Clarify/fail rather than silently convert unknown monetary value to zero. |
| UTC timestamp just across Prague month boundary | timezone/filter | Assign local month correctly. |
| event exactly at period endpoint | range semantics | Respect half-open boundary. |
| refund in following month | timing definition | Attribute according to the contract. |
| cancellation in following month | cancellation timing | Produce separate negative event. |
| SCD change before order | historical dimension | Use status at event time. |
| overlapping SCD intervals | invalid source data | Fail validation rather than arbitrarily choose one row. |
| customer active in several months | misleading total | Recompute period distinct count rather than sum months. |
| raw PII table requested | permission | Warehouse denies access; system reports restriction rather than finding a bypass. |
| metric definition removed or conflicting | ambiguity | Ask for clarification/abstain. |
| schema column renamed in new manifest | source-version drift | Reject stale SQL/context. |
| unsupported verifier construct | verifier uncertainty | Route to execution tests/human review; do not report “verified.” |

This follows the logic behind test-suite accuracy and formal counterexample search: a semantic test suite should deliberately create instances on which competing interpretations diverge. citeturn22view1turn2view1

### Acceptance should be conjunctive

A run should count as **analytically accepted** only if all mandatory gates pass.

One possible definition is:

\[
AcceptedCorrect =
Executable
\land ExpectedResultMatch
\land SemanticAssertionsPass
\land SourceVersionMatch
\land PermissionPass
\land RequiredClarificationHandled
\]

This avoids obscuring reliability behind a high syntax/execution score.

Report the components separately:

**Execution correctness**

\[
ExecutionRate =
\frac{\#\ executable\ candidate\ queries}{\#\ runs}
\]

Useful diagnostically, but never the top-line correctness metric.

**Verified result correctness**

\[
VerifiedPassRate =
\frac{\#\ runs\ satisfying\ all\ required\ oracle\ checks}{\#\ runs}
\]

**Task-level repeated-run reliability**

Because generated SQL can vary across calls, repeat the same frozen task multiple times. Five to ten repetitions per case is a reasonable *experimental design choice* for a small pilot when cost permits, not an academically established universal threshold. Record both average run success and the stricter:

\[
AllRunsReliable =
\frac{\#\ tasks\ where\ every\ repeated\ run\ passes}{\#\ tasks}
\]

Do not average away a system that is correct nine times and silently wrong on the tenth if the query drives an important decision.

**Ambiguity behavior**

Pre-label certain tasks as resolvable versus under-specified. Measure whether the system requests clarification when it should and proceeds when it has sufficient information. A system that “solves” every prompt will look productive but should fail this criterion.

**Source/version fidelity**

A correct result against the wrong semantic version is its own failure. Check the exact approved definition/manifest/catalog revision used by the query.

**Permission compliance**

Test both permitted and forbidden requests. A permission-safe system must fail closed. An LLM refusal is useful UX; warehouse-level rejection is the security control.

**Failure recovery**

Inject realistic errors—renamed field, permission denial, missing relation, conflicting definition. The system should repair syntax/schema problems when authoritative evidence permits, but must not respond to a denied relation by searching for an unauthorized substitute.

**Latency and cost**

Measure end-to-end latency and cost **per accepted correct answer**, not just one generation call. Verification adds work, but unverified low-latency answers are not equivalent outputs. BEAVER itself illustrates that retrieval, decomposition and multi-step agent behavior are separable components of enterprise text-to-SQL performance. citeturn19view2

### What the model may do versus what should remain deterministic

| Component | AI role | Deterministic/system role | Human role |
|---|---|---|---|
| Find relevant semantic objects | Rank/retrieve candidates | Enforce version/allowlist | Resolve ambiguous ownership. |
| Generate SQL | Yes | Parse and restrict statements | Review exceptional/high-risk cases. |
| Calculate known fixture result | Not authoritative | Reference calculation/query | Approve business semantics. |
| Decide permitted data access | Explain only | Warehouse IAM/grants/policies | Define policy. |
| Diagnose failed assertion | Suggest explanation/fix | Re-run exact test | Judge whether definition or code is wrong. |
| Define “net revenue” | Summarize candidate definitions | Retrieve approved definition | Decide when no approved definition exists. |
| Final numerical aggregation | May generate expression | SQL engine/deterministic code | Interpret consequences. |

That distinction is the central system-design point: **model capability and system reliability are different variables**.

## Counterevidence, limitations and conditions where the advice fails

The case for layered verification is strong, but there are limits to what the evidence justifies.

**More realistic benchmarks are still benchmarks.** Spider 2.0 and BEAVER materially improve schema and workflow realism, but neither is a randomized field trial of analysts making business decisions. Spider 2.0 packages enterprise-style tasks into an evaluation environment, while BEAVER combines private enterprise warehouses and actual query structures with LLM-based synthetic expansion. Their low reported execution accuracy should motivate internal testing, not provide a ready-made business case. citeturn1search4turn19view0turn19view1

**Gold SQL is not an unquestionable oracle.** Both SPOTIT and the 2026 annotation audit show that a generated query can disagree with the reference because the reference is itself problematic. citeturn2view1turn4search0 Therefore, internal reference SQL needs an independently reviewed semantic contract and known numerical fixtures. “Matches analyst SQL” is weaker than “satisfies independently specified behavior.”

**Test-suite execution is stronger than one-database execution, not complete proof.** Generated fixtures may fail to exercise a distinction. The 2020 distilled-test-suite work was specifically designed to improve this coverage. citeturn22view1 Formal bounded checking can push further, but unsupported SQL operators and finite bounds leave residual uncertainty. citeturn3view1turn3view2

**LLM self-review is useful but should not be the final oracle.** NL2SQL-BUGs demonstrates substantial but incomplete error-detection capability. A second model call may catch obvious mistakes and is cheap enough to use as an additional detector, but replacing deterministic tests with “LLM judge agrees” recreates dependence on the same class of system being evaluated. citeturn5view2turn5view12

**Semantic layers transfer risk upstream.** MetricFlow's entity model can prevent unsafe join classes and generate consistent SQL, which is genuinely useful. citeturn13view0 But consistency amplifies a wrong centrally defined metric just as effectively as a correct one. Contracts, tests and versioning remain necessary.

**Schema contracts do not prove business correctness.** Current dbt model contracts verify output names/types and, depending on platform, certain constraints; many cloud-warehouse primary/foreign/unique constraints are definable but not necessarily enforced. citeturn16view1 A model can satisfy its structural contract and still apply the wrong date or refund definition.

**dbt's built-in semantic/platform validation has a deliberately narrower scope.** It verifies configuration constraints, existence of physical definitions and whether generated metric SQL executes. citeturn13view2turn13view3 This is valuable plumbing validation, but its successful completion should not be relabeled “metric verified.”

**Historical SCD data are only as good as their capture mechanism.** dbt snapshots implement SCD2 behavior, but snapshots observe changes when they run; frequency, reliable source timestamps and handling of hard deletes affect historical fidelity. dbt's own guidance says snapshots need to run regularly and recommends reliable unique keys and timestamps. citeturn15view0 A perfect temporal join cannot reconstruct a source state that was never captured.

**The proposed fixture assumes one currency and an artificial cancellation rule.** Real revenue may require payment settlement, tax, discounts, shipping, chargebacks, partial cancellation, FX rates, accounting close/reopen rules and financial-system reconciliation. In such environments, the authoritative definition should probably live above raw order/refund tables. The fixture is intentionally a SQL-semantics test, not an accounting standard.

**Sometimes human review dominates automation.** For a one-off high-stakes board or regulatory metric with a new definition, the cheapest reliable approach may be analyst-written SQL plus independent reconciliation rather than building an elaborate AI verification framework. The architecture has highest leverage when similar questions recur.

**Sometimes AI generation adds little.** If a governed semantic layer already exposes a small fixed set of metrics and dimensions, deterministic metric compilation may be preferable to unconstrained text-to-SQL. The model can translate user intent into semantic-layer requests while never generating arbitrary warehouse SQL.

**There is limited public causal evidence about organizational impact.** The reviewed academic literature mostly evaluates query generation or evaluation methodologies. It does not establish how much verified AI SQL reduces analyst time, incidents or financial misreporting in small data teams. Internal A/B or paired-workflow measurement remains necessary.

**Jurisdiction does not rescue semantic ambiguity.** Czech and US environments differ in applicable governance, but the same query can fan out or mishandle month boundaries in either. The Czech/EU context adds a clear GDPR reason to minimize personal-data access where applicable. citeturn22view3 This dossier did not perform a comparable comprehensive US legal review.

**One source-access issue was encountered.** The SPOTIT OpenReview page returned a browser-verification challenge. The publicly available arXiv record and official ICLR 2026 proceedings version were used instead; the ICLR PDF was visually inspected for the relevant tables/limitations. citeturn20search4turn20search12turn3view1turn3view2 No result from the inaccessible OpenReview page was silently inferred.

## Claim-to-source evidence ledger

All sources below were inspected on **2026-10-10**. Dates preserve the original publication/version timing rather than pretending older evidence is new.

| Claim / use in this dossier | Primary source and source date | Direct URL / DOI | Exact scope | Evidence type |
|---|---|---|---|---|
| Cross-database splitting was introduced to test generalization beyond seen schemas in Spider. | Yu et al., *Spider*, 2018. citeturn7academia25 | `https://arxiv.org/abs/1809.08887` ; DOI `10.18653/v1/D18-1425` | Human-labeled cross-domain text-to-SQL dataset; benchmark setting, not enterprise field study. | Academic benchmark paper |
| Real database formatting/documentation materially affects transfer; KaggleDBQA uses real web DBs and unrestricted questions. | Lee, Polozov & Richardson, ACL-IJCNLP, Aug 2021. citeturn22view0 | `https://aclanthology.org/2021.acl-long.176/` ; DOI `10.18653/v1/2021.acl-long.176` | Cross-domain parser evaluation; documentation augmentation improved reported accuracy by >13.2% in the study. | Peer-reviewed empirical benchmark |
| BIRD contains 12,751 question-SQL pairs, 95 DBs, 37 domains and 33.4 GB, emphasizing database contents and external knowledge. | Li et al., preprint May 2023 / NeurIPS 2023. citeturn2view3 | `https://arxiv.org/abs/2305.03111` | Large-scale text-to-SQL benchmark; historical model performance is not a 2026 capability estimate. | Peer-reviewed benchmark |
| Text-to-SQL robustness degrades under semantic-preserving perturbations. | Chang et al., *Dr.Spider*, Jan 2023 / ICLR 2023. citeturn2view4 | `https://arxiv.org/abs/2301.08881` | 17 perturbation types; reported robustness drops apply to evaluated models/settings. | Peer-reviewed diagnostic benchmark |
| Prompting/configuration can materially change text-to-SQL performance and efficiency, so “model” is not the complete system specification. | Gao et al., PVLDB 17(5), 2024. citeturn20search7turn20search11 | `https://doi.org/10.14778/3641204.3641221` | Systematic benchmark of prompting/example-selection approaches, primarily Spider-era evaluation. | Peer-reviewed empirical study |
| Multiple generated test databases provide a better approximation to semantic equivalence than traditional Spider evaluation. | Zhong, Yu & Klein, EMNLP, Nov 2020. citeturn22view1 | `https://aclanthology.org/2020.emnlp-main.29/` ; DOI `10.18653/v1/2020.emnlp-main.29` | 21 Spider submissions; manual verification of 100 cases; reported old-metric false-negative rates. | Peer-reviewed evaluation-method paper |
| Enterprise-style text-to-SQL involves large schemas, multiple dialects, metadata/project context and long SQL. | Lei et al., arXiv Nov 12 2024 / ICLR 2025. citeturn1search4 | `https://arxiv.org/abs/2411.07763` | 632 Spider 2.0 workflow problems; benchmark simulation of enterprise workflows. | Peer-reviewed benchmark |
| Conventional execution evaluation can accept non-equivalent queries; bounded verification finds distinguishing databases and can alter rankings. | Klopfenstein et al., preprint 2025 / ICLR 2026. citeturn2view1turn3view1turn3view2 | `https://arxiv.org/abs/2510.26840` ; official proceedings `https://proceedings.iclr.cc/paper_files/paper/2026/file/70e692da44c19710386648694e2b899b-Paper-Conference.pdf` | Primarily BIRD plus Spider 2.0 applicability analysis; bounded verifier with incomplete SQL coverage. | Peer-reviewed formal/evaluation research |
| Published benchmark gold annotations themselves can contain substantial errors and alter rankings when corrected. | Jin, Choi, Zhu & Kang, Jan 13 2026 / CIDR 2026. citeturn4search0turn5view9 | `https://arxiv.org/abs/2601.08778` | Expert-audited subsets: BIRD Mini-Dev and Spider 2.0-Snow. Rates must not be generalized to entire benchmarks. | Expert annotation audit |
| LLMs are imperfect detectors of semantic SQL bugs; error detection itself is a non-trivial task. | *NL2SQL-BUGs*, Mar 2025 / KDD 2025. citeturn5view2turn5view12 | `https://arxiv.org/abs/2503.11984` | 2,018 expert-annotated bug-detection examples; detection benchmark, not production correctness. | Peer-reviewed diagnostic benchmark |
| BEAVER uses three private enterprise warehouses and real query structures but expands the dataset via LLM-composed, template-derived queries with expert verification. | Chen et al., original 2024; inspected v3 May 13 2026. citeturn19view0turn19view1 | `https://arxiv.org/html/2409.02038v3` | 9,128 evaluation queries, 812 tables, 19 domains; provenance is mixed real-log-derived/template-synthetic. | Current enterprise-oriented benchmark |
| BEAVER's v3 reports low end-to-end execution accuracy even for recent agentic systems and separately measures retrieval/column/join subtasks. | Chen et al., v3 May 13 2026. citeturn19view2 | `https://arxiv.org/html/2409.02038v3` | Specific benchmark configuration and models; not a field accuracy rate. | Benchmark result |
| dbt/MetricFlow represents join semantics through primary/unique/foreign entities and restricts fanout/chasm relationships. | dbt Developer Hub, updated Sep 8 2026. citeturn13view0 | `https://docs.getdbt.com/docs/build/join-logic` | dbt/MetricFlow v1.12+ behavior; provider implementation documentation. | Versioned provider documentation |
| dbt's semantic/data-platform validation checks configuration, object existence and SQL executability, which is narrower than business correctness. | dbt Developer Hub, updated Sep 10 2026. citeturn13view2turn13view3 | `https://docs.getdbt.com/docs/build/validation` | Current Semantic Layer validation behavior. | Versioned provider documentation |
| dbt data tests support uniqueness, non-null, referential integrity and arbitrary SQL assertions. | dbt Developer Hub, current 2026 documentation. citeturn16view0turn14search8 | `https://docs.getdbt.com/docs/build/data-tests` | dbt testing primitives; not evidence that any particular project uses them correctly. | Provider documentation |
| dbt snapshots implement Type-2 SCD history with validity timestamps; unique keys and snapshot frequency matter. | dbt Developer Hub, current 2026 documentation. citeturn15view0 | `https://docs.getdbt.com/docs/build/snapshots` | dbt snapshots, with version-specific notes for v1.9/v1.12. | Provider documentation |
| dbt model contracts guarantee structural properties but enforcement of relational constraints varies by warehouse. | dbt Developer Hub, current 2026 documentation. citeturn16view1 | `https://docs.getdbt.com/docs/mesh/govern/model-contracts` | Model shape/constraint behavior across supported adapters. | Provider documentation |
| dbt grants can encode object permissions and are included in project metadata; row/column controls may require warehouse-native features. | dbt Developer Hub, current 2026 documentation. citeturn16view2turn16view3 | `https://docs.getdbt.com/reference/resource-configs/grants` | dbt-managed object permissions. | Provider documentation |
| EU data-protection-by-design/default requires limiting personal-data processing/accessibility to what is necessary for the purpose. | Regulation (EU) 2016/679, adopted Apr 27 2016, OJ May 4 2016. citeturn21view0turn22view3 | `https://eur-lex.europa.eu/eli/reg/2016/679/oj` | GDPR; relevant to Czech/EU processing of personal data, not a SQL-correctness standard. | Primary legislation |

The evidence types should not be blended. The **academic papers** establish benchmark difficulty, robustness and weaknesses of evaluation methods. **dbt documentation** establishes what dbt currently implements, not that those features improve business outcomes. **GDPR** establishes applicable EU data-protection obligations where personal data are processed, not a preferred technical architecture. The **verification architecture in this dossier is an interpretation/recommendation built from those distinct evidence classes**.

**Candidate article angle — “The SQL ran. That proves almost nothing.”** Lead with the difference between syntax/execution correctness and analytical correctness, then use the €240-versus-€120 fanout example. This is the broadest practitioner angle.

**Candidate article angle — “Your AI SQL evaluator needs adversarial data, not another prompt.”** Build around test-suite accuracy, SPOTIT and independently known counterexamples. The distinctive thesis is that semantic bugs become observable when test data are designed to separate competing interpretations. citeturn22view1turn2view1

**Candidate article angle — “Text-to-SQL benchmarks are not your analytics workflow.”** Contrast Spider → KaggleDBQA/BIRD → Spider 2.0 → BEAVER, then show why source/version fidelity, permissions, semantic definitions and non-additive totals remain outside conventional execution accuracy. citeturn22view0turn2view3turn1search4turn19view0

**Candidate article angle — “A semantic layer is a contract, not a truth machine.”** Explain why entity/cardinality definitions can prevent classes of fanout while still propagating a wrong central definition. dbt's current join and validation behavior makes this concrete without treating provider documentation as performance evidence. citeturn13view0turn13view2

**Candidate article angle — “Why your monthly customer total lies.”** Narrower but accessible: distinguish additive revenue from non-additive distinct customers, then connect the lesson to AI-generated summaries and dashboards.

The strongest existing reusable asset is the [PostgreSQL synthetic verification fixture](sandbox:/mnt/data/ai_sql_verification_fixture_postgres.sql). Useful follow-on assets that were **not created in this dossier** would be a warehouse-neutral DuckDB version, a dbt project containing the same source tests/semantic contract, and a machine-readable evaluation-scorecard schema.

**Freshness triggers:** revisit this dossier when Spider 2.0/BEAVER publish material annotation revisions; when the 2026 benchmark-error audit releases corrected gold sets or follow-up validation; when formal SQL verifiers materially expand support for window functions and warehouse-specific SQL; when dbt changes its entity/join or validation semantics beyond the documented v1.12-era behavior; when the evaluated AI model/tool chain changes; or when an organization's revenue/customer semantic contract changes. Those events can alter model-capability evidence or implementation details, but they do not remove the underlying requirement to separate successful execution from verified analytical meaning.