# Evidence Dossier: AI-Assisted SQL With Verification

**Research snapshot:** October 10, 2026  
**Perspective:** Practitioner  
**Decision supported:** How should a small data team verify AI-generated analytical SQL for grain, joins, filters, source fidelity, and misleading totals before trusting the result?  
**Scope:** Analytical warehouses and dbt-style transformations; synthetic orders/order-items/refunds/customer-history case; public evidence and synthetic fixtures only.

## Decision answer and uncertainty

The practical answer is: **do not make “does the SQL run?” the acceptance test, and do not make “a second LLM says it looks right” the acceptance test either.** Treat generated SQL as an untrusted implementation of an already-defined analytical contract.

For a small data team, I would use a verification gate with five layers:

| Gate | Question | Preferred evidence |
|---|---|---|
| **Metric contract** | What exactly is being calculated, at what grain, as of what time, under which business definitions? | Human-owned definition, semantic model/metric spec, explicit unresolved ambiguities |
| **Source fidelity** | Is the query using the authoritative tables/models, versions, fields, keys and permissions? | dbt DAG/catalog, pinned model/version or commit, contracts/tests, freshness metadata |
| **Structural correctness** | Can joins, grouping, filters, null handling, SCD logic or date boundaries change multiplicity or inclusion? | Cardinality assertions, pre-aggregation, row-count/sum invariants, query review |
| **Result verification** | Does execution produce the known result on adversarial fixtures and reconcile to independent totals? | Unit fixture, independent control query, row samples, reconciliation |
| **Decision gate** | Is residual ambiguity or failed evidence important enough to stop automation? | Clarification, abstention or human sign-off |

This separation matters because **execution success and analytical correctness are different properties**. PostgreSQL, for example, correctly executes a join by producing one output row for *every* matching pair of rows; if two supposedly independent one-to-many tables are joined before aggregation, the database is doing exactly what was requested when it multiplies rows. citeturn17view0 Likewise, SQL's null and date semantics can produce perfectly valid results that differ materially from an analyst's intended metric. citeturn17view2turn17view4turn17view1

Current text-to-SQL evidence reinforces that distinction. Spider 2.0 deliberately moves from toy schemas toward enterprise workflows involving hundreds of tasks, large schemas, metadata, multiple dialects and project code; its March 2025 v2 paper reported an o1-preview-based code agent solving 21.3% of the 632 tasks, despite much higher results on earlier benchmarks. citeturn18view1 BEAVER's May 2026 preprint, constructed from anonymized enterprise warehouses, reports 9,128 question-SQL pairs across 812 tables and says evaluated agentic systems using GPT-5.2 reached 10.8% end-to-end accuracy, rising to 30.1% when given oracle subtask information. citeturn18view2 These are benchmark results, **not estimates of the failure rate in your warehouse**, but they are strong evidence against assuming that fluent SQL generation implies reliable enterprise analysis.

The opposite evidence is also important. ReViSQL reports 93.2% execution accuracy on an **expert-verified BIRD Mini-Dev** set after training on verified examples and using execution-based reconciliation; its authors report that correcting training data alone improved single-generation accuracy by 8.2–13.9 percentage points under their setup. citeturn18view3 The right conclusion is therefore not “LLMs cannot do SQL.” It is that **reliability is highly conditional on the definitions, data quality, context, evaluation procedure and verification system wrapped around generation**.

There is substantial uncertainty in public benchmark numbers themselves. A January 2026 audit reports annotation error rates of 52.8% in BIRD Mini-Dev and 62.8% in the audited Spider 2.0-Snow set and found that correcting BIRD examples materially altered both measured performance and leaderboard rankings. citeturn18view4 FLEX, a peer-reviewed NAACL 2025 evaluation study, similarly found that simple execution-based evaluation generates false positives and negatives; its proposed richer evaluation raised agreement with expert judgments from 0.62 to 0.8704 Cohen's κ in its experiments. citeturn18view0 Therefore, leaderboard execution accuracy should be treated as evidence about a benchmark, not as a correctness guarantee for a business metric.

**My recommended operating rule:** AI may draft SQL and help diagnose failed checks. Deterministic SQL/database checks establish structural facts. Authoritative metadata establishes source fidelity. Humans own ambiguous metric semantics and material exceptions. A result is trusted only when all relevant layers agree.

## Evidence synthesis around the decision

The central failure mode is not necessarily malformed SQL. It is **well-formed SQL implementing an unstated or incorrect interpretation**.

A business request such as “monthly net revenue and active customers” does not uniquely determine a query. At minimum, it leaves open whether revenue is recognized at order creation, payment, shipment or completion; whether a later refund reduces the refund month or rewrites the original order month; whether cancelled orders contribute before cancellation; whether “active customer” means current customer status, status at order time or merely a customer who transacted; which business timezone defines a month; whether late-arriving records restate history; and which version of the underlying model is authoritative.

An AI system cannot discover those choices from SQL syntax. When metadata is insufficient, generating *something* is the wrong behavior. Research on Reliable Text-to-SQL explicitly targets this problem: the authors argue that ambiguity and insufficient schema context can make plausible alternative interpretations possible, and their framework introduces abstention and human interaction during uncertain schema linking. citeturn15view1turn18view5 The study is specifically about schema linking and should not be overgeneralized into a guarantee for full analytical correctness, but its architectural principle is directly useful: **uncertainty should sometimes stop generation rather than merely lower a confidence score.**

For this dossier's synthetic question, these are the minimum authoritative inputs I would require before accepting SQL:

| Contract field | Worked definition | Must clarify when absent |
|---|---|---|
| Output grain | One row per Europe/Prague calendar month | Yes |
| Gross-revenue event | Eligible order at `ordered_at_utc` | Yes |
| Eligible order | `status <> 'cancelled'` | Yes; production statuses may be more complex |
| Line value | `quantity × unit_price − COALESCE(discount_amount, 0)` | Yes if NULL's meaning is undocumented |
| Refund policy | Refund reduces **refund-event month** | Yes; backdating is also plausible |
| Net revenue | `gross recognized in month − refunds processed in month` | Yes |
| Active customer | Customer placed an eligible order and SCD status was `active` **at order time** | Yes |
| Customer-history validity | Half-open interval `[valid_from, valid_to)` | Yes |
| Business timezone | `Europe/Prague` | Yes |
| Currency | Single synthetic currency in fixture | Real data needs explicit FX/currency policy |
| Authoritative sources | Orders, line items, refunds, customer-status history at specified model versions | Yes |
| Data cutoff | Same reproducible snapshot/as-of point for all controls | Yes |

This is the key distinction between a **metric contract** and a schema contract. dbt model contracts can preflight that generated model columns have expected names and data types and can enforce selected constraints on supported platforms. citeturn17view7turn16view6 That is valuable, but it does not prove that a column named `net_revenue` contains the intended net revenue. dbt's own model-version documentation explicitly notes that a calculation can change materially without changing a column's name, type or other enforceable structural characteristics. citeturn17view6turn19view1

Likewise, source fidelity needs its own check. dbt versioned models let consumers pin a particular version; an unpinned reference can resolve to a newer `latest` version when maintainers move that pointer. citeturn17view5 Therefore a reproducible AI-SQL result should record not just “used `dim_customers`,” but enough identity to establish *which definition* was queried: model version where available, deployed project/commit state, environment, relation, relevant semantic-definition version, source snapshot/as-of time and SQL dialect.

A semantic layer can reduce the amount of semantics the generator has to recreate. Current dbt documentation describes semantic models as metadata-backed nodes linked by entities and requires an aggregation time dimension plus entity definitions for join keys. citeturn19view5 That is useful leverage: if “net revenue” is already a governed metric, an AI assistant should preferentially invoke or compile that definition rather than re-derive it from raw tables. But a semantic layer does **not eliminate verification**. Its model, entities and metric definitions can themselves be wrong, stale or incompatible with the user's intended question. It changes the trusted surface; it does not remove one.

The four technical error classes in the research question behave differently:

**Grain errors** occur when a query aggregates at the wrong unit or combines relations before reducing them to compatible grains. The strongest check is explicit: write down the intended primary key of every intermediate relation, then assert it. “One row per order” should be empirically tested with `count(*)`, `count(distinct order_id)` or a duplicate-returning query before that CTE participates in a higher-level aggregation.

**Join errors and fanout** are multiplicity problems. PostgreSQL's join semantics make the mechanism unambiguous: each left row produces one output row for every matching right row. citeturn17view0 Joining `orders → order_items` and `orders → refunds` simultaneously therefore multiplies facts whenever an order has multiple rows on both sides. `SUM(DISTINCT amount)` is not a general solution: two legitimate line items can have the same amount. The robust pattern is usually to aggregate independent many-side facts to the common grain first, then combine them.

**Filter, null and time errors** need tests targeted at boundary values, not just “normal” examples. PostgreSQL documents that ordinary comparisons involving NULL yield unknown, that `BETWEEN` includes both endpoints and that almost all aggregates except `count` return NULL on an empty input. citeturn17view2turn17view3turn17view4 PostgreSQL also makes timezone a parameter of timestamp truncation: for `timestamp with time zone`, `date_trunc` is evaluated with respect to the selected timezone, defaulting to the session timezone unless one is explicitly provided. citeturn17view1 An AI query that relies on whatever session timezone happens to be configured is therefore less reproducible than one whose business timezone is explicit.

**Slowly changing dimensions** add temporal multiplicity. dbt snapshots are explicitly an implementation of Type-2 slowly changing dimensions, retaining successive versions with validity metadata. citeturn19view4 An unrestricted join from an order to customer history can therefore match every historical version of the customer. The verification invariant should normally be stronger than “the join works”: for the intended as-of time, an eligible fact must match **exactly one** history version. Half-open intervals, `valid_from <= event_time AND event_time < valid_to`, avoid double matching at adjacent version boundaries; using inclusive `BETWEEN` at both ends is unsafe because SQL `BETWEEN` includes both endpoints. citeturn17view3

A small team can encode many of these controls in dbt rather than relying on reviewer memory. dbt includes generic `unique`, `not_null`, `accepted_values` and `relationships` data tests, and treats a test as passing when the assertion query returns no violating rows. citeturn19view3 Its unit tests can apply static input fixtures to SQL logic before production materialization and specifically support testing join logic; dbt recommends them for date math, tricky logic, prior bugs, unseen edge cases and high-criticality models. citeturn16view4turn19view2 This is much closer to the verification requirement here than asking a model to “review your answer carefully.”

The safest execution environment is also deliberately constrained. At minimum, the SQL-generation identity should not have arbitrary write permission. PostgreSQL's `READ ONLY` transaction mode disallows ordinary `INSERT`, `UPDATE`, `DELETE`, `MERGE`, DDL, grants and several other mutating operations. citeturn19view0 Platform-specific warehouse roles should additionally limit which schemas/columns the assistant is allowed to inspect. Read-only protects data integrity; it **does not prove query correctness or authorization to disclose every readable value**, so permissions and analytical correctness remain separate gates.

## Concrete verification workflow for a small data team

I would operationalize the process as the following review sheet. It works whether the SQL came from an LLM, a BI tool, an analyst or a code agent; that is deliberate. The generator should not get a weaker standard because it is AI, nor a stronger standard merely because it is AI.

| Stage | Required artifact or check | Failure action |
|---|---|---|
| **Define decision** | State the question and what decision the answer will support. | Stop if nobody can say what the result means. |
| **Freeze metric contract** | Grain, dimensions, numerator/denominator, event timestamps, timezone, status policy, refund/cancellation policy, SCD as-of rule, currency and cutoff. | Clarify before SQL generation if two materially different definitions remain valid. |
| **Resolve authoritative data** | Identify preferred marts/semantic metrics, tables, columns, join keys, source lineage and model versions. | Abstain if authoritative source cannot be identified. |
| **Check permissions** | Execute only with permitted read-only identity or isolated fixture; never broaden access merely so the model can “figure it out.” | Escalate to data owner. |
| **Annotate grains** | For every source/CTE: “one row per ___”; identify its key. | Rewrite or add an explicit test if key is uncertain. |
| **Annotate join cardinality** | Mark each join `1:1`, `many:1`, `1:many` or `many:many expected`. | Any unexplained many:many is a stop condition. |
| **Measure multiplicity** | Compare pre/post-join rows, distinct keys and additive control columns. | Pre-aggregate, deduplicate under a documented rule or fix join. |
| **Audit predicates** | Explicitly inspect status exclusions, NULL behavior, date start/end, timezone, late data and outer-join filters. | Add boundary fixture and rerun. |
| **Audit SCD joins** | Assert one valid version per fact at the chosen event timestamp; use non-overlapping validity intervals. | Stop on zero or >1 matches unless policy explicitly permits it. |
| **Run deterministic fixture** | Test ordinary cases plus fanout, NULL, boundary time, cancellation, multiple refunds and SCD boundary. | Generated query must match known expected rows. |
| **Run independent control totals** | Calculate components by a structurally different route, ideally before the joins under suspicion. | Investigate any unexplained difference. |
| **Inspect row samples** | Trace several output contributions back to raw/source rows, especially around boundaries. | Fix interpretation or query. |
| **Verify final grain** | Uniqueness at declared output grain plus expected row count/date coverage. | Reject result. |
| **Record provenance** | SQL/hash, inputs, versions/commit, environment/dialect, timezone, execution/as-of timestamp, test results. | Do not label result reproducible until recorded. |
| **Materiality review** | Human review for externally reported, financially material, regulated or unfamiliar metrics. | Human sign-off or abstention. |

The important design choice is that the **control calculation should not merely repeat the same implementation**. For monthly revenue, a useful independent check is to calculate gross order revenue by pre-aggregating items to orders, calculate refund totals independently from the refunds table, and only subtract the two monthly aggregate series at the end. A generated production query can use a different optimized structure, but both routes should reconcile.

The non-AI baseline is straightforward: a competent analyst receives the same written metric contract and authoritative schema, writes one candidate query plus independent controls, runs the same fixture, and signs off the discrepancies. This baseline is more meaningful than comparing “LLM SQL vs handwritten SQL” without verification because a human can make exactly the same fanout or definition error. **The evaluation target is verified analytical correctness, not authorship.**

For evaluating an AI-assisted workflow over time, I would keep a small golden set drawn from the team's own recurring analytical patterns. Each case should have executable fixtures and expected results, not merely a reference SQL string. Track at least: final-result correctness, component/control reconciliation, source/version fidelity, ambiguity detection, permission compliance, reproducibility, number of human interventions, recovery after a failed check, and elapsed/cost-to-verified-answer. Public benchmark evidence itself warns against treating SQL-string or simplistic execution equivalence as sufficient evaluation: FLEX documents false positives and false negatives in execution accuracy, and recent audit work finds extensive benchmark annotation problems. citeturn18view0turn18view4

Latency and compute are real trade-offs. A full independent re-query can approach the cost of answering twice, particularly over large facts. The practical compromise is to make cheap structural checks universal, run static adversarial unit fixtures in development/CI, and reserve expensive full-data reconciliations for higher-risk outputs or sampled windows. dbt currently recommends unit tests in development or CI and, in dbt v2, documents an experimental local DuckDB execution path for static unit fixtures specifically to avoid unnecessary warehouse compute. citeturn19view2 No universal cost saving should be inferred from that documentation; warehouse size, cache behavior and query architecture dominate actual cost.

**Clarification, abstention or human review should be mandatory** when the metric definition admits multiple materially different interpretations; an authoritative source or key is unknown; an apparently many-to-many join has no documented bridge/allocation semantics; the SCD join has zero or multiple valid versions; a source/model version has changed without an accepted semantic diff; a deterministic fixture or independent total fails; required data is outside the requester's permissions; the requested time basis/timezone is unknown; the SQL depends on silent treatment of NULLs or missing data; or a material decision would rest on an unfamiliar metric with no validated historical controls. Adaptive-abstention research provides empirical support for making “do not answer yet” an explicit text-to-SQL system capability rather than treating it as failure. citeturn15view1turn18view5

## Worked synthetic example and portable test asset

The following example is intentionally synthetic. **It is not presented as an observed LLM error rate or customer result, and no AI model was benchmarked on it.** I authored both the fixture and the deliberately bad query to exercise known SQL failure modes; the queries were genuinely executed locally during this research against **SQLite 3.46.1**.

The complete portable package contains:

`fixture.sql` — DDL and synthetic rows  
`correct_query.sql` — verified query under the stated contract  
`wrong_query.sql` — intentionally wrong but executable query  
`checks.sql` — diagnostic checks  
`verify_ai_sql.py` — standard-library-only runnable harness  
`README.md` — execution notes

**[Download the synthetic SQL verification fixture and runnable harness](sandbox:/mnt/data/ai_sql_verification_fixture.zip)**

The harness creates an in-memory database, loads the fixture, enables SQLite `PRAGMA query_only=ON`, executes the checks and asserts expected results. It accessed no external system or private data.

The worked metric contract is:

\[
\text{gross}_m =
\sum_{\substack{o\ \text{not cancelled}\\
\operatorname{PragueMonth}(o.ordered\_at)=m}}
\sum_{i\in items(o)}
(q_i p_i-\operatorname{COALESCE}(d_i,0))
\]

\[
\text{refunds}_m =
\sum_{\operatorname{PragueMonth}(r.refunded\_at)=m} r.amount
\]

\[
\boxed{\text{net revenue}_m=\text{gross}_m-\text{refunds}_m}
\]

and

\[
\text{active customers}_m =
\left|
\left\{
c:
\begin{array}{l}
c\text{ has an eligible order in }m,\\
status_c(o.ordered\_at)=\text{active}
\end{array}
\right\}
\right|.
\]

Customer status is looked up using the SCD interval

\[
valid\_from \le ordered\_at < valid\_to
\]

with an open-ended final version allowed.

The fixture deliberately contains two UTC orders straddling the Prague month boundary, multiple line items, multiple refunds, a cancelled order, an inactive customer, a NULL discount, customers with multiple history versions and an order occurring exactly at an SCD transition.

The verified result is:

| Prague month | Gross revenue | Refunds in event month | Net revenue | Active customers |
|---|---:|---:|---:|---:|
| 2026-01 | 120 | 0 | **120** | **1** |
| 2026-02 | 285 | 30 | **255** | **3** |
| 2026-03 | 50 | 5 | **45** | **1** |

The calculation is deliberately simple enough to audit. February, for example, has gross eligible order revenue of:

\[
100+75+100+10=285
\]

and February refund events of:

\[
20+10=30
\]

therefore:

\[
285-30=\boxed{255}.
\]

The intentionally wrong query is structurally recognizable:

```sql
SELECT
    date(datetime(o.ordered_at_utc, '+1 hour'), 'start of month') AS month,
    SUM(
        oi.quantity * oi.unit_price
        - COALESCE(oi.discount_amount, 0)
        - COALESCE(r.amount, 0)
    ) AS misleading_net_revenue,
    COUNT(DISTINCT o.customer_id) AS misleading_active_customers
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o.order_id
LEFT JOIN refunds AS r
  ON r.order_id = o.order_id
JOIN customer_history AS ch
  ON ch.customer_id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY 1;
```

It executes successfully.

It returns:

| Prague order month | Misleading “net revenue” | Misleading “active customers” |
|---|---:|---:|
| 2026-01 | **160** | 1 |
| 2026-02 | **635** | **4** |
| 2026-03 | **100** | 1 |

The database has not failed. The query has.

For order `1002`, there are two item rows, two refund rows and two C1 history versions. The raw join therefore creates:

\[
2 \times 2 \times 2=\boxed{8\ rows}
\]

before aggregation. PostgreSQL's documented join semantics explain why this multiplicity is expected behavior, not an engine anomaly. citeturn17view0 The query also lacks the temporal predicate restricting `customer_history` to the version valid at order time, and it counts customer C2 even though C2 is inactive under the metric contract.

The check that exposes the fanout is intentionally boring:

```sql
SELECT
    o.order_id,
    COUNT(*) AS joined_rows,
    COUNT(DISTINCT oi.item_id) AS distinct_items,
    COUNT(DISTINCT r.refund_id) AS distinct_refunds,
    COUNT(DISTINCT ch.valid_from_utc) AS history_versions
FROM orders o
JOIN order_items oi
  ON oi.order_id = o.order_id
LEFT JOIN refunds r
  ON r.order_id = o.order_id
JOIN customer_history ch
  ON ch.customer_id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY o.order_id;
```

A second invariant checks SCD cardinality under the proper predicate:

```sql
SELECT
    o.order_id,
    COUNT(ch.customer_id) AS scd_matches
FROM orders o
LEFT JOIN customer_history ch
  ON ch.customer_id = o.customer_id
 AND ch.valid_from_utc <= o.ordered_at_utc
 AND (
      ch.valid_to_utc IS NULL
      OR o.ordered_at_utc < ch.valid_to_utc
 )
WHERE o.status <> 'cancelled'
GROUP BY o.order_id;
```

Every eligible order in the fixture must return exactly `1`. The deliberately placed order exactly at a status transition validates why the right end is exclusive. Using inclusive `BETWEEN` at both adjacent history records could match both because SQL `BETWEEN` includes endpoints. citeturn17view3

The NULL case is similarly revealing. One line has a NULL `discount_amount`, with the contract explicitly defining that as zero discount. Across the whole fixture:

\[
\sum(qp-d)=555
\]

when NULL is not handled, because the affected expression becomes NULL and `SUM` ignores that input, whereas:

\[
\sum(qp-\operatorname{COALESCE}(d,0))=655.
\]

PostgreSQL documents both the null-comparison behavior and the non-null behavior of common aggregates; relying on implicit NULL treatment is therefore something the metric contract should make explicit. citeturn17view2turn16view7

The fixture also proves that **a different business definition can be internally consistent without being the requested one**. Suppose refunds are retrospectively assigned to the original order month rather than the month in which the refund occurred. The same underlying events then produce:

| Month | Event-month net used in worked contract | Net with refunds backdated to order month |
|---|---:|---:|
| 2026-01 | **120** | **100** |
| 2026-02 | **255** | **270** |
| 2026-03 | **45** | **50** |

Neither series can be selected by looking at SQL syntax. This is a semantic decision. A system that was asked merely for “monthly net revenue” should therefore **ask, retrieve an authoritative metric definition or abstain**, rather than silently choosing one.

The fixture's timezone implementation has an important limitation. SQLite lacks the IANA timezone functionality expected of analytical warehouses, so for this local portable fixture only I use `+1 hour`; every relevant timestamp falls in January–March 2026 while Prague is on UTC+01:00. **That shortcut must not be copied into production across DST.** A production warehouse should use named `Europe/Prague` timezone semantics and add tests around DST and month changes. PostgreSQL 18 explicitly supports a timezone argument for truncating `timestamp with time zone`, which avoids depending on session defaults. citeturn17view1

The actual harness run completed all assertions:

```text
PASS: correct monthly result matches the metric contract
PASS: intentionally wrong query executes and is caught by expected-result comparison
PASS: fanout diagnostic exposes multiplicative joins
PASS: SCD2 half-open validity predicate gives exactly one version per eligible order
PASS: NULL-handling check exposes a silent undercount
PASS: month-boundary checks match the fixture's Prague-local contract
PASS: alternative refund timing differs, proving clarification is semantic
ALL CHECKS PASSED
SQLite version: 3.46.1
```

That result demonstrates only that the supplied synthetic tests behave as documented. It is **not** evidence that this query structure automatically fits a real company's revenue accounting.

## Alternatives, counterevidence and conditions where the advice fails

There are four reasonable architectures, and they should not be conflated.

**Free-form text-to-SQL over warehouse tables** maximizes flexibility but leaves the model responsible for retrieving the right schema, resolving business semantics, selecting joins and implementing the calculation. Spider 2.0 and BEAVER both provide evidence that enterprise-scale context and schema reasoning remain hard under benchmark conditions. citeturn18view1turn18view2 I would reserve this mode for exploration or for cases with strong downstream verification, rather than treating a successful execution as publishable truth.

**AI over curated marts/views** removes choices the model should not be making. If a model sees `fct_orders`, `fct_refunds` and a well-defined customer-status dimension rather than dozens of raw operational tables, retrieval and join ambiguity decline. This does not need AI-specific infrastructure; good warehouse modeling is itself a reliability mechanism. The trade-off is coverage: questions outside the modeled domain still require escalation or new transformation logic.

**AI over a semantic/metrics layer** is preferable when the requested metric already exists. Current dbt semantic models explicitly encode entities, dimensions and time semantics used for metric calculation. citeturn19view5 In that situation, AI's job can shift from inventing SQL semantics toward selecting the appropriate governed metric and dimensions. I would generally choose this route for recurring executive or financial-style KPIs. It sacrifices some open-ended flexibility in exchange for a much smaller interpretation surface.

**Analyst-written SQL with the same verification harness** is the appropriate baseline. This is important because AI verification is not justified merely by showing that humans also make mistakes. The question for a data leader is whether AI reduces time-to-verified-answer, expands useful coverage or lowers analyst toil **without weakening the acceptance criteria**. That requires an internal evaluation against the team's normal analyst workflow, not a public text-to-SQL leaderboard.

There is strong counterevidence to any claim that complex SQL generation is inherently unreliable. ReViSQL's 2026 results demonstrate very high performance on a cleaned, expert-verified BIRD subset when verified data and inference-time reconciliation are used. citeturn18view3 Conversely, the benchmark-audit literature makes it risky to interpret any single benchmark score as deployment readiness: expert audits have found high annotation-error rates capable of changing system rankings. citeturn18view4 The two findings are compatible. Better models and better verified data can substantially improve capability, while correctness in a particular warehouse still depends on local semantics and evidence.

The proposed workflow also has limitations.

It will not catch an incorrect **business definition that every test encodes consistently**. If finance actually wants refunds backdated but the metric contract, golden fixture and SQL all use refund-event month, every automated check can pass while the answer remains organizationally wrong. This is why metric ownership sits outside SQL verification.

Control totals are not independent evidence when they reuse the same flawed transformations. A check against `SUM(net_revenue)` from a mart does not validate that mart's definition. For high-value metrics, at least one control should originate from a different decomposition or authoritative source.

Small fixtures can miss real-data pathologies: duplicate ingestion, currency conversion, late-arriving refunds, missing history intervals, daylight-saving transitions, precision/rounding behavior and malformed source values. dbt data tests complement fixtures by testing properties of materialized data, while unit tests exercise modeled logic on controlled edge cases. citeturn19view3turn19view2 Both are useful because neither subsumes the other.

A source can also be structurally stable while changing semantically. dbt's current documentation explicitly warns that calculation changes can leave model shape unchanged. citeturn17view6 Thus schema contracts alone cannot establish source/version fidelity. For metrics that matter, store definition identity alongside query provenance.

Read-only execution prevents many mutation classes but not expensive queries, data leakage through authorized-but-inappropriate reads, or incorrect analysis. PostgreSQL's documentation itself describes read-only as a high-level restriction rather than prevention of every underlying disk write. citeturn19view0 Warehouse-specific quotas, row/column policies, query limits and data-governance controls remain outside this fixture.

There is little reason to expect a fundamental Czech-versus-US difference in these SQL reliability principles. The material difference is **business-time semantics**: a Czech operation may choose `Europe/Prague`; a US operation may have multiple local zones, headquarters time or customer-local time. Those are metric-definition choices, not locale-specific SQL truths. Timezone must therefore be explicit in either context. Privacy, employment and sector regulation can introduce different governance requirements, but they were outside this technical research scope and no jurisdiction-specific legal conclusion is claimed here.

Two source-access limitations are worth making explicit. The official CIDR page surfaced for the 2026 benchmark-annotation work but timed out during inspection, so no substantive claim here relies on that page; the accessible arXiv v3 is cited instead. The ACM landing page for the Reliable Text-to-SQL DOI also did not render through the research tool; substantive descriptions here therefore rely on the accessible arXiv manuscript, while the DOI is reported as publication metadata rather than as independently inspected page content.

## Claim-to-source dossier

All web sources below were inspected on **2026-10-10**. “Provider documentation” means documentation of a technology's behavior or supported feature, not independent proof that adopting that technology improves business outcomes.

| Claim supported | Original source and direct URL / DOI | Source date | Exact scope | Evidence type |
|---|---|---|---|---|
| Enterprise text-to-SQL is materially harder than classic benchmarks; Spider 2.0 contains 632 enterprise-style workflow tasks and v2 reports 21.3% success for its o1-preview code-agent setup. | Lei et al., *Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows*: https://arxiv.org/html/2411.07763v2 | 2025-03-17, arXiv v2 | Spider 2.0 benchmark; enterprise-style SQL workflows, not a production failure-rate estimate. | Primary empirical preprint citeturn18view1 |
| BEAVER uses data derived from private enterprise warehouses; 9,128 pairs, 812 tables, 19 domains; reported GPT-5.2 agentic accuracy 10.8% and 30.1% with all oracle subtask hints. | Chen et al., *BEAVER: An Enterprise Benchmark for Text-to-SQL*: https://arxiv.org/html/2409.02038v3 | 2026-05-13, arXiv v3 | Benchmark based on anonymized enterprise sources plus expert-verified synthesis. Current manuscript contains placeholder PVLDB metadata, so treated here as preprint evidence. | Primary empirical preprint citeturn18view2 |
| High performance is possible on expert-verified text-to-SQL data: ReViSQL reports 93.2% on expert-verified BIRD Mini-Dev and improvements from cleaned training data. | Zhu et al., *ReViSQL: Achieving Human-Level Text-to-SQL*: https://arxiv.org/html/2603.20004v1 | 2026-03-20 | BIRD-derived verified benchmark and authors' ReViSQL system; does not establish arbitrary enterprise correctness. | Primary empirical preprint citeturn18view3 |
| Benchmark annotations themselves can be unreliable; audited BIRD Mini-Dev and Spider 2.0-Snow error rates reported as 52.8% and 62.8%, with ranking changes after correction. | Jin et al., *Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards*: https://arxiv.org/html/2601.08778v3 | 2026-01-19, v3 | Expert audit of selected BIRD and Spider 2.0-Snow benchmark material. | Primary empirical preprint citeturn18view4 |
| Execution accuracy can create false positives/negatives; FLEX reports human-agreement improvement from 0.62 to 0.8704 Cohen's κ. | Kim et al., *FLEX: Expert-level False-Less EXecution Metric for Text-to-SQL Benchmark*: https://aclanthology.org/2025.naacl-long.228/ ; DOI: https://doi.org/10.18653/v1/2025.naacl-long.228 | 2025, NAACL | Evaluation methodology for text-to-SQL benchmarks, not direct production SQL verification. | Peer-reviewed primary empirical study citeturn18view0 |
| Explicit abstention and human interaction can be designed into text-to-SQL when schema linking is uncertain. | Chen et al., *Reliable Text-to-SQL with Adaptive Abstention*: https://arxiv.org/html/2501.10858v1 ; DOI: https://doi.org/10.1145/3709719 | 2025-01-18 arXiv v1; ACM publication 2025 | Primarily schema-linking uncertainty on benchmark tasks; should not be treated as a guarantee over entire analytical workflow. | Primary empirical research citeturn15view1turn18view5 |
| A SQL join creates one result row for every matching pair, providing the mechanical basis for fanout. | PostgreSQL 18, *Table Expressions*: https://www.postgresql.org/docs/18/queries-table-expressions.html | PostgreSQL 18 current docs, inspected 2026-10-10 | PostgreSQL relational join behavior; principle generalizes to standard relational joins. | Versioned official documentation citeturn17view0 |
| `BETWEEN` is endpoint-inclusive; comparisons with NULL yield unknown; these semantics matter for SCD and filter boundaries. | PostgreSQL 18, *Comparison Functions and Operators*: https://www.postgresql.org/docs/18/functions-comparison.html | PostgreSQL 18 current docs | PostgreSQL/SQL comparison semantics. | Versioned official documentation citeturn17view2turn17view3 |
| Except for `count`, PostgreSQL aggregates such as `sum` return NULL on no rows; NULL treatment needs explicit policy. | PostgreSQL 18, *Aggregate Functions*: https://www.postgresql.org/docs/18/functions-aggregate.html | PostgreSQL 18 current docs | PostgreSQL aggregate semantics. | Versioned official documentation citeturn17view4 |
| Timestamp truncation can be performed with an explicit timezone rather than relying on session timezone. | PostgreSQL 18, *Date/Time Functions and Operators*: https://www.postgresql.org/docs/18/functions-datetime.html | PostgreSQL 18 current docs | `date_trunc` behavior for timestamp/timezone values. | Versioned official documentation citeturn17view1 |
| Read-only transactions disallow broad classes of mutating SQL, making them an appropriate execution boundary for generated analytical queries. | PostgreSQL 18, *SET TRANSACTION*: https://www.postgresql.org/docs/18/sql-set-transaction.html | PostgreSQL 18 current docs | PostgreSQL transaction access mode; other warehouses require equivalent platform controls. | Versioned official documentation citeturn19view0 |
| dbt generic tests support unique/not-null/accepted-values/relationships assertions. | dbt Developer Hub, *Add data tests to your DAG*: https://docs.getdbt.com/docs/build/data-tests | Dynamic docs, inspected 2026-10-10 | Current dbt data-test behavior. | Provider documentation citeturn19view3 |
| dbt unit tests apply static fixtures to SQL logic and are explicitly recommended for date math, tricky edge cases, prior bugs and critical models. | dbt Developer Hub, *Unit tests*: https://docs.getdbt.com/docs/build/unit-tests | Dynamic docs; current page inspected 2026-10-10 | dbt v1.8+ unit testing; page also documents newer v2 local-test capabilities. | Provider documentation citeturn16view4turn19view2 |
| Model contracts validate shape/types but are not semantic-metric guarantees. | dbt Developer Hub, *Model contracts*: https://docs.getdbt.com/docs/mesh/govern/model-contracts | Dynamic docs, inspected 2026-10-10 | dbt contracted models and platform-dependent constraints. | Provider documentation citeturn16view6turn17view7 |
| Calculation semantics may change without changing model shape; model versions can be pinned while unpinned references resolve to latest. | dbt Developer Hub, *Model versions*: https://docs.getdbt.com/docs/mesh/govern/model-versions | Dynamic docs, inspected 2026-10-10 | dbt model governance/version behavior. | Provider documentation citeturn17view5turn17view6turn19view1 |
| dbt snapshots implement Type-2 slowly changing dimensions and expose validity history. | dbt Developer Hub, *Add snapshots to your DAG*: https://docs.getdbt.com/docs/build/snapshots | Dynamic docs, inspected 2026-10-10 | dbt snapshot/SCD functionality. | Provider documentation citeturn19view4 |
| dbt semantic models encode entities, dimensions and an aggregation time dimension in a semantic graph. | dbt Developer Hub, *Semantic models*: https://docs.getdbt.com/docs/build/semantic-models | Dynamic docs; page states current v1.12+ semantics | MetricFlow/dbt Semantic Layer definition mechanisms. | Provider documentation citeturn19view5 |
| Source freshness is a separate operational check and is not automatically part of every dbt build. | dbt Developer Hub, *Source freshness*: https://docs.getdbt.com/docs/deploy/source-freshness | Dynamic docs, inspected 2026-10-10 | dbt job-level freshness checks and SLA status. | Provider documentation citeturn15view3 |
| Synthetic fixture produces verified expected and deliberately misleading results. | [Portable local fixture/harness](sandbox:/mnt/data/ai_sql_verification_fixture.zip) | Executed 2026-10-10 | Synthetic SQLite 3.46.1 data only; no model evaluation, private data or real customer result. | **Executed local synthetic test**, authored for this dossier |

The source hierarchy matters. PostgreSQL documentation establishes SQL-engine behavior; dbt documentation establishes what dbt features do; text-to-SQL papers establish empirical benchmark findings under their stated experiments. None of those sources establishes that the worked revenue definition is “the correct” definition for a real business. That definition is explicitly a **hypothetical contract for the synthetic case**.

## Candidate article angles, reusable assets and freshness triggers

The strongest article angle is probably **“The dangerous SQL is the SQL that runs.”** The worked fanout example makes the point without requiring readers to understand LLM internals: the query compiles, executes and returns plausible-looking monthly numbers, yet February net revenue is `635` instead of the contracted `255`. The AI-specific argument follows naturally: code-generation fluency does not change relational algebra.

A distinct second angle is **“Don't ask AI to verify a definition it was forced to invent.”** This would center on the two legitimate-looking net-revenue series:

\[
(120,255,45)
\]

for refund-event accounting versus

\[
(100,270,50)
\]

for refund-backdated accounting. The key design lesson is that ambiguity resolution belongs before or outside SQL generation. Adaptive-abstention research gives this angle empirical grounding. citeturn15view1

A third angle is **“AI SQL needs a test harness, not another confidence paragraph.”** The concrete practitioner story is a progression from model-generated SQL to metric contract, adversarial fixture, fanout check, control totals and source/version provenance. dbt's unit- and data-testing capabilities make this implementable for small teams rather than an enterprise-only architecture. citeturn19view2turn19view3

A fourth is **“Semantic layers reduce the problem; they don't prove the answer.”** That article could distinguish raw text-to-SQL from metric selection over governed semantics. dbt's current semantic-model specification provides a concrete implementation example, while its model-version documentation provides the useful counterpoint that apparently non-breaking calculation changes can still alter results. citeturn19view5turn17view6

A fifth angle is **“Why text-to-SQL benchmarks are the wrong procurement test.”** Spider 2.0 and BEAVER show large gaps in enterprise-like conditions; ReViSQL shows that verified data can also yield very high results; FLEX and the 2026 annotation audit show why even execution-based benchmark rankings require skepticism. citeturn18view1turn18view2turn18view3turn18view0turn18view4 The practical replacement is an internal set of representative, executable business questions with authoritative definitions and known results.

The most useful reusable asset from this dossier is the **[executable verification fixture](sandbox:/mnt/data/ai_sql_verification_fixture.zip)**. For a real team, I would turn that pattern into a small repository of golden analytical cases. Each case would contain a metric contract, compact adversarial input tables, expected outputs, one or more known-bad queries/failure signatures, independent controls, and metadata identifying the authoritative model versions. The golden set should stay small enough that every case has an owner and a business reason to exist; hundreds of synthetic questions with weak labels would recreate the benchmark-quality problem that recent research has exposed. citeturn18view4

The dossier should be refreshed when any of the following occurs: the team's warehouse or SQL dialect changes; dbt changes contract, unit-test, semantic-layer or model-version behavior; an authoritative metric/source changes semantics or version; time/currency/refund policies change; new failure cases occur in production review; or materially stronger text-to-SQL evaluations appear on verified enterprise-like datasets. Benchmark numbers in particular are freshness-sensitive: Spider 2.0's cited result is from its March 17, 2025 v2 paper, BEAVER's cited result is from its May 13, 2026 v3 preprint, ReViSQL is from March 20, 2026, and the benchmark-error audit is from January 19, 2026. citeturn16view0turn16view1turn16view3turn18view4

The durable decision rule is less likely to age: **generated analytical SQL should be trusted only after its semantics are specified, its sources and versions are known, its relational structure satisfies explicit cardinality/grain invariants, and its result survives executable independent checks.** Model quality can reduce how often those gates fail. It should not replace the gates.