# AI-Assisted SQL with Verification: Evidence Dossier

**Research snapshot:** October 10, 2026  
**Decision supported:** How should AI-generated analytical SQL be checked for grain, joins, filters, permissions, time semantics, and misleading totals?  
**Scope:** Small professional data team using analytical warehouses and dbt-style transformations; synthetic public-data-only evaluation; no private-company data.  
**Central example:** Monthly net revenue and active customers from orders, order items, refunds, and slowly changing customer history.

## Executive answer and uncertainties

The strongest design is **not “LLM writes SQL, another LLM checks it.”** Treat generated SQL as an untrusted implementation of an already specified analytical contract. Verification should be a gated pipeline:

**business definition → source/version/permission resolution → grain and join checks → compile/execute safely → adversarial executable tests → reconciliation → targeted human review.**

That ordering matters. A database can successfully execute SQL that has the wrong grain, multiplies facts through a fanout join, applies a month filter in the wrong timezone, uses current rather than historical customer status, attributes refunds to the sale month rather than refund month, or silently answers a different interpretation of “active customer.” Canonical dimensional-modeling guidance has long emphasized that grain must be stated precisely and that mixed grains can lead to double counting; Type-2 historical dimensions similarly require an explicit point-in-time join rather than an unconstrained join to every historical version. [1](https://www.kimballgroup.com/2003/10/fistful-of-flaws/)

The research literature gives a closely related warning about evaluation. Zhong, Yu and Klein introduced distilled test-suite evaluation precisely because executing a candidate SQL query against one database instance can fail to distinguish semantically different queries that happen to produce the same result on that instance. Their approach tests candidate queries against multiple database states designed to distinguish alternatives. [2](https://arxiv.org/abs/2010.02840?utm_source=chatgpt.com) More recent evidence weakens the case for relying on leaderboard execution accuracy even further: Jin et al. reported annotation-error rates of **52.8% on BIRD Mini-Dev and 62.8% on Spider 2.0-Snow** in their expert analysis, and re-evaluation of 16 open-source agents on a corrected BIRD subset changed relative performance by −7% to +31% and ranks by −9 to +9 positions. That is benchmark evidence, not a production error-rate estimate, but it demonstrates that even the supposed ground truth requires scrutiny. [3](https://arxiv.org/abs/2601.08778)

Accordingly, the useful distinction is:

| Correctness level | Question | Sufficient evidence |
|---|---|---|
| Syntactic validity | Is this legal SQL? | Parser/compiler |
| Executability | Does it run against this warehouse? | Successful execution |
| Snapshot result correctness | Does it match a known answer on these rows? | Golden fixture/result |
| Semantic correctness | Does it implement the intended business definition? | Explicit metric contract + executable tests |
| Robustness | Does it remain correct when cardinalities, nulls, boundaries or schemas change? | Adversarial/mutated fixtures + schema/version checks |
| Business interpretation | Is the returned number the number the decision-maker thinks it is? | Definition provenance + clarification/human judgment |

Constrained decoding such as PICARD can reduce generation of syntactically invalid SQL by rejecting inadmissible continuations, but that addresses the first layer, not the revenue definition, correct grain, historical status policy, or refund timing. [4](https://aclanthology.org/2021.emnlp-main.779/?utm_source=chatgpt.com)

### Bottom-line recommendation

For this audience and workflow, I would use **generated SQL plus deterministic verification as the default**, backed where practical by a governed semantic layer. Human review should be concentrated on unresolved semantics and exceptional changes, rather than being the only correctness control.

A governed semantic layer reduces the number of joins and metric expressions an AI system is free to invent. dbt's current MetricFlow documentation says entities encode join relationships and that its join construction is designed to avoid fanout and chasm joins; that is useful provider documentation, but not independent evidence that every modeled metric is semantically correct. A bad governed definition merely produces the wrong answer consistently. [5](https://docs.getdbt.com/docs/build/about-metricflow?utm_source=chatgpt.com)

### Main uncertainties

There is **no credible public evidence here that supports a single production error rate** for AI-generated SQL in a dbt-style analytics team. BIRD, Spider 2.0, LiveSQLBench and related evaluations are benchmarks, not measurements of a particular company's analytical workflow. Spider 2.0 is useful evidence that realistic tasks involve large schemas, metadata, documentation, codebases and multi-step workflows, but its reported model scores should not be treated as current production reliability estimates. [6](https://arxiv.org/abs/2411.07763?utm_source=chatgpt.com)

Public benchmark definitions are themselves moving targets. The Spider 2 project subsequently changed its published task structure; its repository described Spider2-DBT as 68 repository-level tasks and removed the original Spider2 setting from the updated structure. That is a concrete reason to record **dataset/version fidelity separately from a score**. [7](https://github.com/xlang-ai/Spider2?utm_source=chatgpt.com)

Likewise, the 2026 RBAC text-to-SQL study is relevant to permissions, but its roles and policies were synthesized from benchmark schemas and then human-validated; it is not evidence from actual corporate access-control logs. Its most defensible lesson is architectural: SQL utility and authorization compliance are distinct evaluation dimensions, and deterministic DBMS enforcement remains necessary. [8](https://arxiv.org/abs/2607.22115)

No material Czech-versus-US distinction changes the relational failure modes in this dossier. Grain, fanout, null handling, temporal joins and month boundaries behave according to data and SQL semantics, not geography. Organizational and jurisdictional policy can change **which data may be queried**, so permission policy should be an explicit input enforced by the data platform, rather than something the model is expected to infer.

## Evidence synthesis around the decision

### Start from the analytical decision, not from SQL

For the synthetic request—

> “Calculate monthly net revenue and active customers.”

—there is **not yet enough information to generate a verifiably correct query**.

At least the following semantic choices are unresolved:

| Term | Plausible interpretations | Verification consequence |
|---|---|---|
| Month | UTC month; company timezone; customer's timezone; accounting period | Can move transactions between periods |
| Revenue timestamp | created, paid, fulfilled, invoiced, recognized | Changes period assignment |
| Cancellation | exclude any ever-cancelled order; exclude only pre-payment cancellations; reverse on cancellation date | Changes both historical and current totals |
| Refund | revise original sale month; recognize refund when issued; recognize when settled | Changes monthly net revenue |
| Revenue amount | items only; items + shipping; net of tax; gross of tax; discounts separately | Different metric |
| Active customer | any transacting customer; customer with status=`active`; end-of-month active; current active | Different population |
| Historical customer status | current record; status at payment; status at month end | Requires different SCD join |
| Partial/null refund | zero, pending, error, or excluded | `COALESCE` can hide data defects |
| Currency | single currency; transaction currency; converted at transaction/refund/month-end FX | Requires an FX definition |

This is where **clarification is required rather than clever SQL**. AmbiSQL is directly relevant: its authors treat natural-language ambiguity as a separate problem from SQL generation and report, on their ambiguous-query evaluation dataset, 87.2% precision for ambiguity detection and a 50% improvement in exact-match SQL accuracy when clarification is incorporated. Those figures are benchmark/demo results rather than production effect sizes, but they support explicitly detecting ambiguity instead of silently choosing an interpretation. [9](https://arxiv.org/abs/2508.15276?utm_source=chatgpt.com)

A reasonable machine-readable contract for the worked example is:

\[
\text{NetRevenue}_m =
\sum_{\substack{o:\ localMonth(paid\_at_o)=m\\eligible(o)}} 
\sum_{i\in items(o)} amount_i
-
\sum_{\substack{r:\ localMonth(refunded\_at_r)=m\\status_r=succeeded}}
amount_r
\]

and

\[
\text{ActiveCustomers}_m =
\left|
\left\{
customer(o):
eligible(o),
localMonth(paid\_at_o)=m,
status(customer(o), paid\_at_o)=active
\right\}
\right|.
\]

For the fixture, `eligible(o)` means `paid_at IS NOT NULL` and there is **no cancellation at or before payment**. Later cancellations do not retroactively erase gross revenue; monetary reversal is represented by a successful refund event. This is a deliberately chosen synthetic policy, not a universal accounting rule.

### Grain is the first mechanical invariant

For the example, the source grains are:

| Relation | Declared grain | Candidate key | Join implication |
|---|---|---|---|
| `orders` | one row per order | `order_id` | Parent |
| `order_items` | one row per order item | `order_item_id` | many items per order |
| `refunds` | one row per refund event | `refund_id` | many refunds per order |
| `customer_history` | one row per customer-status version | `customer_history_id` | many versions per customer |
| Final result | one row per reporting month | `month` | Aggregated result |

The important point is that **foreign keys do not imply equal grain**. Joining both `order_items` and `refunds` directly to `orders` gives, for one order with \(I\) items and \(R\) refunds, up to \(I\times R\) joined rows. Any additive item or refund measure then risks multiplication.

Kimball's guidance treats declaring fact-table grain as foundational and specifically warns about mixing facts at different levels of detail and about totals that can consequently be double-counted. [1](https://www.kimballgroup.com/2003/10/fistful-of-flaws/)

For AI-generated SQL, this means a verifier should not merely ask “are these join keys legal?” It should require a **cardinality assertion**:

```text
orders(order_id)                  1
order_items(order_id)             N
refunds(order_id)                 N
customer_history(customer_id)     N over time
```

A raw child-to-parent-to-child join is therefore suspicious whenever additive measures from either child are subsequently summed. The safe default is to aggregate each fact stream to the required grain **before** combining them.

dbt's generic data tests are directly useful for the prerequisites: current documentation describes assertions over models/sources and built-in tests including uniqueness and non-null checks. Model contracts independently validate expected column names and types before model construction. Neither facility establishes the business meaning of “net revenue,” but both can turn assumptions about keys and schema shape into executable gates. [10](https://docs.getdbt.com/reference/resource-properties/data-tests?utm_source=chatgpt.com)

### Filters need semantic tests, not textual presence checks

It is insufficient to verify that a generated query “has a date filter” or “filters cancelled orders.”

For example:

```sql
WHERE cancelled_at IS NULL
```

and:

```sql
WHERE cancelled_at IS NULL OR cancelled_at > paid_at
```

implement different cancellation policies.

Likewise, these two refund strategies answer different questions:

```sql
-- Original-sale-period restatement
GROUP BY month(order.paid_at)
```

versus:

```sql
-- Event-period recognition
GROUP BY month(refund.refunded_at)
```

A verifier therefore needs a requirement such as:

```text
refund_recognition_basis = refunded_at
```

and then a boundary fixture that would fail if refunds are accidentally grouped by the original order month.

A static LLM critique can flag suspicious filters, but **only an authoritative definition says which filter is correct**.

### Timezone is part of the metric definition

A source timestamp can represent an absolute instant while the business month is a civil-time concept. BigQuery's official timestamp documentation explicitly notes that timezone information is needed when converting between absolute timestamps and civil time; its timestamp functions expose timezone parameters for those operations. [11](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?utm_source=chatgpt.com)

Therefore:

```text
reporting timezone = Europe/Prague
```

belongs in the metric contract, not in an implementation footnote.

The useful test case is a timestamp close to the boundary. In the supplied fixture:

```text
2026-01-31 23:30:00 UTC
→ 2026-02-01 00:30:00 Europe/Prague
```

so grouping the raw UTC string puts the order in January while the defined metric puts it in February.

Prefer half-open period predicates conceptually:

```text
period_start <= local_time < next_period_start
```

rather than hand-written “23:59:59” end points.

### Slowly changing dimensions require an “as of what?” decision

For Type-2 history, an unconstrained join such as:

```sql
JOIN customer_history h
  ON h.customer_id = o.customer_id
```

is wrong whenever a customer has multiple historical versions. It can duplicate the fact and can pick status values from the wrong period.

The worked definition instead requires:

```sql
o.paid_at_utc >= h.valid_from_utc
AND (h.valid_to_utc IS NULL OR o.paid_at_utc < h.valid_to_utc)
```

plus `h.status = 'active'`.

Kimball's Type-2 guidance describes the new-row approach with surrogate/version rows and effective/expiration dates, and explicitly identifies point-in-time filtering as necessary to select the correct historical version. [12](https://www.kimballgroup.com/2005/03/slowly-changing-dimensions-are-not-always-as-easy-as-1-2-3/)

The verifier should also fail if SCD intervals overlap for a customer. Otherwise one fact can match two “valid” historical rows despite the SQL looking perfectly reasonable.

### Null handling must preserve uncertainty

The dangerous AI habit is often:

```sql
COALESCE(refund_amount, 0)
```

because it makes the query run and totals look tidy.

But the correct handling depends on what null means. In the fixture:

```text
failed refund + NULL amount       allowed and ignored
succeeded refund + NULL amount    data-quality failure
```

The verifier therefore asserts:

```sql
COUNT(*) = 0
FROM refunds
WHERE refund_status = 'succeeded'
  AND amount_cents IS NULL
```

before computing the metric.

The general rule is: **use `COALESCE` only after the business meaning of null is specified.** “Null equals zero” is itself a semantic assumption.

### One successful execution is weak evidence

The 2020 distilled-test-suite work provides the strongest directly relevant formal evidence here: SQL programs that are semantically different can be indistinguishable on a particular database state, so evaluation should execute against database states that expose relevant distinctions. [2](https://arxiv.org/abs/2010.02840?utm_source=chatgpt.com)

For an analytics team, that translates naturally into adversarial fixtures:

| Mutation | Correct metric should… | Failure exposed |
|---|---|---|
| Add a second refund to an order with multiple items | subtract only the added refund | fanout |
| Split one €/$10 refund into €/$7 + €/$3 | remain unchanged | row-count-dependent logic |
| Put a payment 30 minutes across local month boundary | move to correct local month | UTC/local-time error |
| Move customer status boundary across the payment instant | update active count only as defined | bad SCD join |
| Add a failed null refund | remain unchanged | careless null handling |
| Add a succeeded null refund | fail validation | hidden source defect |
| Add an unrelated historical customer row | remain unchanged | unconstrained history join |
| Rename/version an authoritative model | fail source-fidelity gate until remapped | stale context |

Schema evolution deserves explicit testing rather than assuming a model that worked yesterday will infer today's schema correctly. EvoSchema, a 2026 benchmark specifically studying text-to-SQL under schema changes, reports material robustness degradation under schema evolution and finds table-level changes particularly difficult. It is benchmark evidence rather than a production field study, but it reinforces version-aware regression testing. [13](https://arxiv.org/abs/2603.10697?utm_source=chatgpt.com)

### Permissions are correctness, not merely security

A correct SQL answer under unrestricted access can still be an invalid system response for the requesting user.

The 2026 RBAC text-to-SQL study explicitly separates **execution correctness** from **permission compliance** and argues that ordinary execution-accuracy benchmarks miss queries that would be correct under full access but violate the user's role. The authors also keep deterministic DBMS enforcement in the architecture rather than proposing that an LLM replace it. [8](https://arxiv.org/abs/2607.22115)

That leads to a strong deployment rule:

> **Generate and execute under the effective user/service role that is allowed to answer the question. Do not give the LLM privileged data and ask it to remember what the user is permitted to see.**

Representative warehouse controls support this approach. BigQuery row-level access policies act as filters based on the accessing principal, while policy tags can enforce column-level access control and masking. [14](https://docs.cloud.google.com/bigquery/docs/row-level-security-intro?utm_source=chatgpt.com)

There is an important limitation: the RBAC benchmark itself states that it does **not** evaluate inference-based leakage where allowed columns can indirectly reveal a prohibited fact. So permission checking based purely on referenced tables/columns is necessary but not universally sufficient. [8](https://arxiv.org/abs/2607.22115)

## Verification architecture and alternatives

### A practical verification stack

The high-leverage architecture for a small team is the following. This is a **design synthesis**, not a measured claim that every layer has a particular detection rate.

| Gate | Deterministic input/check | What it catches | What it does not establish |
|---|---|---|---|
| Semantic contract | metric name, grain, timezone, event basis, null policy, SCD policy | silent interpretation choices | whether source data is correct |
| Context/source fidelity | resolved relation names, dbt/model version, lineage, schema | stale/wrong table, obsolete columns | metric semantics |
| Permission gate | execute metadata/query using effective role | unauthorized row/column/table use | inference leakage in all cases |
| SQL safety | parser/AST, SELECT-only policy, dialect compile | syntax, prohibited writes, bad references | analytical correctness |
| Grain/cardinality | key uniqueness + expected 1:N/N:1 relations | common fanout/chasm patterns | every logically incorrect join |
| Required-filter inspection | structural check for defined time/status/cancel rules | omitted/wrong implementation pattern | correctness on edge values |
| Warehouse dry-run/compile | actual dialect/backend | execution feasibility; sometimes cost estimate | correct answer |
| Golden/adversarial execution | fixture with hand-derived answer | concrete semantic errors | untested future cases |
| Intermediate reconciliation | gross − refunds = net; row counts by stage | plausible-but-wrong totals | original business definition |
| Human review | unresolved meaning / exceptional change | ambiguity and domain judgment | deterministic repeatability |

Where a platform supports it, dry-run facilities also provide an inexpensive pre-execution operational guard. BigQuery, for example, supports dry runs and reports estimated data processing subject to documented limitations; this is useful for cost/safety gating but should not be confused with correctness verification. [15](https://docs.cloud.google.com/bigquery/docs/running-queries?utm_source=chatgpt.com)

### Conditions that should stop automatic answering

| Situation | System behavior |
|---|---|
| “Revenue” definition or recognition timestamp is unspecified | **Clarify** |
| Reporting timezone is missing and timestamps can cross boundaries | **Clarify** |
| “Active customer” has multiple plausible definitions | **Clarify** |
| Current-vs-historical dimension semantics are unspecified | **Clarify** |
| Two authoritative metric definitions disagree | **Abstain / human review** |
| Source model/version cannot be established | **Abstain** |
| Required source is outside the requester's permission boundary | **Refuse that route or find an authorized alternative** |
| Key uniqueness/cardinality assertion fails | **Stop execution of final result** |
| SCD intervals overlap | **Stop; data-quality failure** |
| Successful financial event has missing amount | **Stop or quarantine according to explicit policy** |
| Candidate disagrees with golden fixture | **Reject candidate** |
| Candidate passes fixture but uses a novel/unreviewed join path | **Human review or expand tests** |
| Source or metric definition changed since last validation | **Re-run regression suite** |

This matters because ambiguity is not necessarily a model weakness to “reason through.” In many cases there simply is no uniquely correct SQL until a human or authoritative semantic definition resolves the question. AmbiSQL's premise and evaluation are consistent with making clarification an explicit system action. [9](https://arxiv.org/abs/2508.15276?utm_source=chatgpt.com)

### Decision matrix

The following ratings are a reasoned architecture assessment, **not benchmark measurements**.

| Approach | Grain/fanout protection | Semantic consistency | Ambiguity handling | Permissions | Reproducibility | Main weakness | My use |
|---|---|---|---|---|---|---|---|
| Analyst-written SQL + ordinary peer review | Medium | Medium–High | High | Depends on environment | Medium | reviewer may reproduce same assumption; expensive to repeat | Baseline |
| AI SQL + human review | Medium | Medium | High if reviewer notices ambiguity | Depends on environment | Medium | fluent SQL increases false confidence; humans become bottleneck | Useful transition state |
| Governed semantic queries | High for modeled relationships | High for already-governed definitions | Low–Medium | Platform dependent | High | wrong/stale semantic definition becomes consistently wrong | Preferred for stable core metrics |
| AI SQL + deterministic gates | High for encoded invariants | High on covered definitions/tests | Medium; must route ambiguity | High if execution role enforced | High | requires fixture and contract engineering | **Preferred default** |
| Governed semantic layer + deterministic gates + targeted human review | Highest practical combination | Highest practical combination | High | High | High | setup/maintenance cost; governance can slow immature models | **Target architecture for important metrics** |

dbt's Semantic Layer/MetricFlow is an example of the governed-query approach: its official documentation says join paths are constructed from entity/key types and are designed to avoid fanout and chasm joins. Because this is provider documentation rather than an independent comparative evaluation, it supports the capability description but not a claim that the approach has a measured production error advantage over alternatives. [5](https://docs.getdbt.com/docs/build/about-metricflow?utm_source=chatgpt.com)

dbt's model contracts and tests complement rather than replace that semantic governance. Contracts check model shape and data types; tests can assert properties such as uniqueness and non-nullness. Neither determines whether refunds belong in the refund month or sale month. [16](https://docs.getdbt.com/docs/mesh/govern/model-contracts?utm_source=chatgpt.com)

### The baseline comparison I would actually run

To decide whether AI assistance deserves production use, give the **same specified analytical contract and synthetic/adversarial fixtures** to:

| Variant | Allowed mechanism |
|---|---|
| Analyst baseline | analyst writes SQL manually |
| AI + human | model generates; analyst reviews |
| Semantic | query is expressed through governed metrics/entities |
| AI + deterministic | model generates; automated gates decide pass/fail |
| Combined | governed semantic layer + generation + deterministic checks + exception review |

Do not score “looks reasonable.” Measure separately:

```text
semantic/query correctness on known results
ambiguity: asks/abstains when definition is missing
source + model version fidelity
permission compliance
reproducibility
failure recovery
elapsed latency
warehouse/model cost
human review minutes
```

A system that answers more requests but silently guesses ambiguous definitions should not win merely because its apparent completion rate is higher.

Spider 2.0 is useful context for why this workflow-level evaluation is preferable to a toy SQL leaderboard: its tasks were designed around enterprise-style environments involving large schemas and supporting metadata/documentation rather than only compact schema-to-query translation. [6](https://arxiv.org/abs/2411.07763?utm_source=chatgpt.com) At the same time, the later benchmark revisions and the 2026 annotation-error findings mean even realistic benchmarks must be versioned and treated cautiously. [7](https://github.com/xlang-ai/Spider2?utm_source=chatgpt.com)

## Worked synthetic example and portable test asset

### Explicit analytical contract

The supplied fixture makes every consequential assumption visible.

**Reporting grain:** one row per reporting month.

**Timezone:** `Europe/Prague`; event timestamps stored in UTC.

**Monetary unit:** integer **synthetic currency cents**; one currency only; no tax, shipping, discount or FX logic.

**Gross revenue:** sum of order-item cents, recognized at `paid_at`.

**Cancellation:** an order is excluded only if `cancelled_at <= paid_at`.

**Refunds:** only `refund_status='succeeded'`; recognize at `refunded_at`.

**Nulls:** successful refund amount must be non-null; failed refund may have null amount; SCD `valid_to=NULL` means open ended.

**Active customer:** distinct customer with an eligible paid order in that month whose historical status was `active` at that order's payment instant.

**SCD interval convention:** `[valid_from, valid_to)`.

These choices are illustrative. A real team must obtain them from an authoritative metric owner/model rather than copying these policies.

### Critical fixture rows

Consider order `101`:

```text
order 101
  paid: 2026-01-31 22:30 UTC
  item A: 6000 cents
  item B: 4000 cents

refunds:
  1000 cents in February
   500 cents in February
```

The order has **2 items × 2 successful refunds**. A raw join produces four combinations:

```text
item 6000 × refund 1000
item 6000 × refund  500
item 4000 × refund 1000
item 4000 × refund  500
```

The true gross is:

\[
6000 + 4000 = 10000
\]

but the raw join exposes:

\[
6000 + 6000 + 4000 + 4000 = 20000.
\]

Likewise, refund values are duplicated across item rows.

The fixture additionally includes order `102` at:

```text
2026-01-31 23:30 UTC
```

which is:

```text
2026-02-01 00:30 Europe/Prague
```

so it belongs to **February**, not January, under the stated reporting policy.

Refund `2004` occurs at:

```text
2026-02-28 23:30 UTC
```

which is March 1 locally and must therefore be recognized in March.

Customer `1` becomes inactive at:

```text
2026-02-15 00:00 UTC
```

so a later February order still contributes revenue but cannot independently qualify that customer as historically active under the stated definition.

### Expected results

The reference calculation is:

| Month | Gross sales | Successful refunds recognized | Formula | Net revenue | Active customers |
|---|---:|---:|---|---:|---:|
| 2026-01 | 10,000 | 0 | 10,000 − 0 | **10,000** | **1** |
| 2026-02 | 18,000 | 3,500 | 18,000 − 3,500 | **14,500** | **2** |
| 2026-03 | 0 | 2,500 | 0 − 2,500 | **−2,500** | **0** |

For February:

\[
Gross = 5000 + 10000 + 3000 = 18000
\]

\[
Refunds = 1000 + 500 + 2000 = 3500
\]

\[
Net = 18000 - 3500 = \mathbf{14500\ cents}.
\]

A negative March value is not automatically an error: with event-period refund recognition, a month can contain refunds for earlier sales without containing any new eligible sales.

### The plausible wrong query

The negative control intentionally performs the attractive shortcut:

```sql
FROM orders o
JOIN order_items i USING (order_id)
LEFT JOIN refunds r USING (order_id)
...
GROUP BY sale_month
```

It executes successfully.

On the actual supplied fixture, it returns:

| UTC sale month | Wrong net-revenue cents | Customer count |
|---|---:|---:|
| 2026-01 | **20,000** | 1 |
| 2026-02 | **18,000** | 2 |

It also produces no March row.

Nothing crashes. The numbers are not obviously absurd. Yet the query is wrong simultaneously because it:

1. creates item × refund fanout;
2. attributes refunds to the sale month;
3. groups by UTC rather than Prague civil month;
4. ignores the historical-status definition of active customer.

This is exactly the class of failure for which “the query ran successfully” is nearly useless evidence.

### Correct query pattern

The reference implementation first brings each fact stream to an independently safe grain:

```sql
item_gross_by_order AS (
    SELECT order_id, SUM(amount_cents) AS gross_cents
    FROM order_items
    GROUP BY order_id
)
```

Sales are then grouped by the local payment month, while refunds are separately grouped by the local **refund-event** month. Only those already aggregated streams are combined.

Historical customer status is resolved through the explicit as-of interval:

```sql
o.paid_at_utc >= h.valid_from_utc
AND (
    h.valid_to_utc IS NULL
    OR o.paid_at_utc < h.valid_to_utc
)
```

This does not imply every analytical model should pre-aggregate in exactly this way. It illustrates the more general invariant: **know the grain of every intermediate relation and never sum a measure after joining through a relationship that can multiply that measure unless the multiplication is intended.**

### Executable verification performed

The included harness was executed during preparation of this dossier. It passed checks for:

```text
PASS orders.order_id uniqueness
PASS order_items.order_item_id uniqueness
PASS refunds.refund_id uniqueness
PASS successful-refund amount non-null
PASS no overlapping customer SCD intervals
PASS item foreign-key completeness
PASS refund foreign-key completeness
PASS local month boundary for order 102
PASS local month boundary for refund 2004
PASS historical status boundary for order 105
PASS reference result against hand-specified expected output
PASS negative control detected as wrong
```

The implementation uses Python's standard-library SQLite plus `zoneinfo`; timestamps are mapped to `Europe/Prague` through a registered `REPORT_MONTH()` function. Thus the asset does not rely on a particular commercial warehouse.

**Portable asset:** [Download the AI SQL verification fixture](sandbox:/mnt/data/ai_sql_verification_fixture.zip)

It contains:

```text
README.md
schema_and_fixture.sql
reference.sql
bad_query.sql
expected.csv
verify.py
```

A candidate query can be checked with:

```bash
python verify.py --candidate your_candidate.sql
```

Passing this fixture is **evidence, not proof**. That limitation is important. The distilled-test-suite research indicates why multiple distinguishing database states are stronger than one snapshot; production use should extend this fixture with cardinality, schema, temporal and null mutations. [2](https://arxiv.org/abs/2010.02840?utm_source=chatgpt.com)

## Claim-to-source evidence dossier

The table separates empirical research from benchmark construction, provider documentation and practitioner guidance. **Inspected date is October 10, 2026 for every accessible source below.**

| Claim supported | Source and source date | Exact scope | Evidence type | Direct URL / DOI | Inspected |
|---|---|---|---|---|---|
| A single database execution is insufficient to establish SQL semantic equivalence; distilled test suites execute against distinguishing database states | Zhong, Yu & Klein, *Semantic Evaluation for Text-to-SQL with Distilled Test Suites*, 2020-10-06. [2](https://arxiv.org/abs/2010.02840?utm_source=chatgpt.com) | Evaluation methodology for text-to-SQL semantic correctness; not production analytics reliability | Peer-reviewed empirical/methodological research | https://arxiv.org/abs/2010.02840 | 2026-10-10 |
| Constrained decoding can prevent many syntactically inadmissible SQL continuations but is a generation constraint, not a business-semantics verifier | Scholak et al., *PICARD*, 2021. [4](https://aclanthology.org/2021.emnlp-main.779/?utm_source=chatgpt.com) | Text-to-SQL constrained autoregressive decoding | Peer-reviewed method + primary preprint | https://arxiv.org/abs/2109.05093 | 2026-10-10 |
| BIRD expanded evaluation toward larger, value-rich, domain-diverse databases; historical scores should not be read as current production reliability | Li et al., BIRD, 2023-05-04. [17](https://arxiv.org/abs/2305.03111?utm_source=chatgpt.com) | 12,751 question-SQL pairs, 95 databases, multiple domains; benchmark setting | Benchmark research | https://arxiv.org/abs/2305.03111 | 2026-10-10 |
| Realistic enterprise-style text-to-SQL requires more than translating against a small schema | Lei et al., *Spider 2.0*, first preprint 2024-11-12. [6](https://arxiv.org/abs/2411.07763?utm_source=chatgpt.com) | 632 enterprise-workflow problems at publication, including BigQuery/Snowflake settings and auxiliary metadata/code | Benchmark research | https://arxiv.org/abs/2411.07763 | 2026-10-10 |
| Benchmark scope/version itself can change and therefore must be pinned | Spider 2 official repository; major structure/update described 2025-05-22. [7](https://github.com/xlang-ai/Spider2?utm_source=chatgpt.com) | Current project/repository structure; Spider2-DBT published as 68 repository-level tasks after restructuring | Primary benchmark implementation/version evidence | https://github.com/xlang-ai/Spider2 | 2026-10-10 |
| Text-to-SQL benchmark annotations can contain enough errors to materially alter reported agent scores and rankings | Jin et al., v1 2026-01-13; v3 2026-01-19. [3](https://arxiv.org/abs/2601.08778) | Expert analysis of BIRD Mini-Dev and Spider 2.0-Snow; re-evaluation of 16 open-source agents | Empirical benchmark-audit research | https://arxiv.org/abs/2601.08778 ; DOI: 10.48550/arXiv.2601.08778 | 2026-10-10 |
| Schema evolution is a separate robustness problem for text-to-SQL | *EvoSchema: Towards Text-to-SQL Robustness Against Schema Evolution*, 2026-03-11. [13](https://arxiv.org/abs/2603.10697?utm_source=chatgpt.com) | Constructed benchmark of schema-evolution perturbations; not observed production drift | Benchmark/robustness research | https://arxiv.org/abs/2603.10697 | 2026-10-10 |
| Permission compliance and query utility should be evaluated separately; unrestricted execution accuracy misses RBAC failures | Fei et al., *Benchmarking Text-to-SQL under Role-Based Access Control*, 2026-07-24. [8](https://arxiv.org/abs/2607.22115) | RBAC-augmented Spider/BIRD/LiveSQLBench; roles synthesized then human-reviewed; explicit limitation on inference leakage | Benchmark empirical research | https://arxiv.org/abs/2607.22115 | 2026-10-10 |
| Ambiguous NL questions can warrant explicit clarification rather than silent SQL generation | Ding, Lin & Zeng, *AmbiSQL*, 2025-08-21. [9](https://arxiv.org/abs/2508.15276?utm_source=chatgpt.com) | Interactive ambiguity detection/clarification; benchmark/demo evaluation | Research system/demo evidence | https://arxiv.org/abs/2508.15276 | 2026-10-10 |
| dbt data tests can encode assertions such as uniqueness and non-nullness | dbt Developer Hub, page dated 2026-03-13. [10](https://docs.getdbt.com/reference/resource-properties/data-tests?utm_source=chatgpt.com) | dbt `data_tests` capability; does not establish business metric correctness | Provider documentation | https://docs.getdbt.com/reference/resource-properties/data-tests | 2026-10-10 |
| dbt model contracts validate declared output shape, names/types and supported constraints | dbt Developer Hub, updated 2026-09-10. [16](https://docs.getdbt.com/docs/mesh/govern/model-contracts?utm_source=chatgpt.com) | Model interface/schema guarantees; support varies by materialization/platform | Provider documentation | https://docs.getdbt.com/docs/mesh/govern/model-contracts | 2026-10-10 |
| MetricFlow derives joins from entity/key semantics and is designed to prevent fanout/chasm joins | dbt Developer Hub, updated 2026-09-10. [5](https://docs.getdbt.com/docs/build/about-metricflow?utm_source=chatgpt.com) | dbt Semantic Layer / MetricFlow behavior | Provider documentation; capability claim, not independent comparative evidence | https://docs.getdbt.com/docs/build/about-metricflow | 2026-10-10 |
| Freshness can be made an explicit dbt gate rather than assumed | dbt Developer Hub, updated 2026-09-16. [18](https://docs.getdbt.com/reference/commands/freshness?utm_source=chatgpt.com) | `dbt freshness` warning/error thresholds for configured resources | Provider documentation | https://docs.getdbt.com/reference/commands/freshness | 2026-10-10 |
| Conversion between absolute timestamps and civil time requires timezone semantics | Google Cloud BigQuery timestamp-function documentation, living docs. [11](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?utm_source=chatgpt.com) | BigQuery/GoogleSQL timestamp functions; warehouse-specific syntax illustrates a general temporal requirement | Official platform documentation | https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions | 2026-10-10 |
| Row-level policy can be enforced deterministically by the warehouse rather than trusted to an LLM | Google Cloud BigQuery row-level-security docs, living docs. [14](https://docs.cloud.google.com/bigquery/docs/row-level-security-intro?utm_source=chatgpt.com) | BigQuery row-access policies | Official platform documentation | https://docs.cloud.google.com/bigquery/docs/row-level-security-intro | 2026-10-10 |
| Column-level policy tags can restrict or mask sensitive fields | Google Cloud BigQuery policy-tag documentation, living docs. [19](https://docs.cloud.google.com/bigquery/docs/best-practices-policy-tags?utm_source=chatgpt.com) | BigQuery policy tags; representative platform mechanism | Official platform documentation | https://docs.cloud.google.com/bigquery/docs/best-practices-policy-tags | 2026-10-10 |

Two older Kimball Group articles were also inspected as primary practitioner material: Margy Ross's October 10, 2003 discussion of grain/mixed-grain flaws and the March 10, 2005 article on Type-2 slowly changing dimensions. They support the grain and point-in-time modeling discussion above but should be treated as canonical practitioner guidance, not controlled empirical research. [1](https://www.kimballgroup.com/2003/10/fistful-of-flaws/)

### Source-access note

The CIDR 2026 PDF for *Text-to-SQL Benchmarks are Broken: An In-Depth Analysis of Annotation Errors* repeatedly timed out in the web fetcher and therefore was **not analyzed from the PDF**. The later public arXiv revision of the research and the authors' public implementation repository were accessible and inspected instead; claims above are explicitly attributed to those inspected sources. [3](https://arxiv.org/abs/2601.08778)

No private customer data, private benchmark results, vendor customer claims or inferred production effect sizes were used.

## Contradictions, limitations, implications and freshness

### Where the evidence disagrees—or appears to

There is a useful tension between modern benchmark progress and the reliability question.

BIRD, Spider 2.0 and LiveSQLBench are efforts to make text-to-SQL evaluation more realistic by incorporating richer schemas, values, enterprise-style metadata or dynamic workloads. [20](https://arxiv.org/abs/2305.03111?utm_source=chatgpt.com) Yet the 2026 annotation audit found substantial errors in prominent benchmark subsets and showed that corrections can reorder systems materially. [3](https://arxiv.org/abs/2601.08778) These are not contradictory findings. Together they imply:

> **Benchmark realism and benchmark ground-truth reliability are separate properties.**

Similarly, MetricFlow's documented protection against fanout/chasm joins is valuable, but it does not contradict the recommendation for executable verification. The semantic layer can only enforce the relationships and metrics that have been modeled. Provider documentation establishes intended behavior; it does not establish that a team's local definition is correct. [5](https://docs.getdbt.com/docs/build/about-metricflow?utm_source=chatgpt.com)

And deterministic DB permissions do not make an AI system useful by themselves. The RBAC paper points out the distinction: a DBMS may safely reject an unauthorized query while the upstream text-to-SQL system still fails to produce an authorized alternative or an appropriate refusal. [8](https://arxiv.org/abs/2607.22115) Safety and task completion therefore need separate metrics.

### Important unresolved gaps

**Production comparative evidence is thin.** The sources reviewed do not provide a clean independent study comparing, on the same real analytics workload, analyst-written SQL, AI+human review, a governed semantic layer, and AI+deterministic verification. The decision matrix in this dossier is therefore a systems-design synthesis, not an empirically established ranking.

**Semantic correctness still needs an oracle.** No verifier can deduce whether the CFO intends refunds to restate the sale month or hit the refund month merely by inspecting rows. The authoritative definition must come from governance, documentation or clarification.

**Executable tests are necessarily incomplete.** Distinguishing fixtures dramatically improve evidence over one snapshot, but no finite fixture proves correctness for every future data distribution. The Zhong et al. methodology supports stronger semantic testing, not an absolute proof of arbitrary analytical programs. [2](https://arxiv.org/abs/2010.02840?utm_source=chatgpt.com)

**A semantic layer moves the trust boundary.** It can eliminate repeated free-form metric logic but makes the quality, review process and version of semantic definitions more important.

**Permission analysis has boundaries.** The 2026 RBAC benchmark explicitly excludes inference-based leakage from its scope. [8](https://arxiv.org/abs/2607.22115)

**Schema robustness remains under active study.** EvoSchema provides evidence that evolution matters, but its controlled perturbations should not be interpreted as a measured probability of production failures. [13](https://arxiv.org/abs/2603.10697?utm_source=chatgpt.com)

### Practical policy for a small data team

I would make the unit of trust **a verified analytical request**, not “SQL produced by model X.”

For a stable core metric such as revenue, store something equivalent to:

```yaml
metric: monthly_net_revenue
output_grain: reporting_month
timezone: Europe/Prague

sales:
  source: governed_order_items
  event_time: paid_at
  cancellation_policy: exclude_if_cancelled_at_lte_paid_at

refunds:
  source: governed_refunds
  event_time: refunded_at
  statuses: [succeeded]
  null_amount: error

customers:
  active_definition: transacted_and_status_active_as_of_paid_at
  scd_interval: "[valid_from, valid_to)"

verification:
  source_version_required: true
  permission_role_required: true
  grain_checks_required: true
  golden_fixture_required: true
  reconciliation_required: true
```

Then let AI generate or adapt the implementation **inside those constraints**.

The artifact stored with an accepted answer should ideally identify:

```text
natural-language request
resolved semantic definition/version
generated SQL
warehouse dialect
resolved database/schema/relation names
transformation/model version
effective execution role
reporting timezone
source freshness/as-of point
verification checks and outcomes
result
known caveats
```

That is much more valuable for reproducibility than saving a chat transcript saying that the model was “confident.”

dbt's current contracts, tests and freshness commands already provide pieces of this control plane for model shape, data assertions and staleness, though none independently verifies the analytical interpretation. [16](https://docs.getdbt.com/docs/mesh/govern/model-contracts?utm_source=chatgpt.com)

### Candidate article angles and assets

| Angle | Thesis | Distinctive evidence/asset |
|---|---|---|
| **SQL That Runs Can Still Be Wrong** | Syntax and successful execution are the bottom two rungs of analytical correctness, not the finish line | Fanout/time/SCD fixture with correct vs executable-wrong outputs |
| **Another LLM Is Not a SQL Verifier** | Model critique is useful, but deterministic invariants and counterexample execution provide qualitatively different evidence | Verification-stack matrix + distilled-test-suite research |
| **Start with Grain, Not the Prompt** | The highest-leverage defense against plausible wrong totals is to declare the grain of every source and intermediate relation | Item × refund fanout example |
| **Semantic Layers Change the AI Problem** | Governing metrics and join paths reduces what the model has to invent; it does not eliminate verification | MetricFlow provider docs + trust-boundary analysis |
| **Why Execution Accuracy Is a Weak Production KPI** | One-snapshot execution, benchmark annotation quality, permissions and ambiguity each create blind spots | Zhong test suites + 2026 benchmark audit + RBAC study |
| **When an AI Analyst Should Ask Instead of Answer** | Clarification/abstention is a feature when business definitions are genuinely underdetermined | AmbiSQL evidence + revenue-definition decision table |
| **Version Fidelity Is Part of Correctness** | The right SQL against yesterday's schema/benchmark/metric definition can be today's wrong answer | Spider 2 benchmark evolution + EvoSchema |

Useful reusable assets from this dossier are the [portable executable fixture](sandbox:/mnt/data/ai_sql_verification_fixture.zip), the semantic-contract template, the verification-layer matrix, and the analyst-vs-AI decision matrix.

### Freshness triggers for revisiting the dossier

| Trigger | Why it matters |
|---|---|
| Material dbt Semantic Layer/MetricFlow join-semantics change | Could change what fanout protection can be delegated to the semantic layer |
| dbt contract/test behavior or supported constraints change | Alters deterministic gate capabilities |
| Major Spider 2/BIRD/LiveSQLBench version or corrected labels | Historical benchmark comparisons may cease to be apples-to-apples |
| New independent text-to-SQL benchmark audits | Could materially change confidence in reported capability rankings |
| New evidence on production analytics-agent failure rates | Would fill the largest current empirical gap |
| Warehouse permission semantics change | Can alter effective query results or authorization behavior |
| Warehouse timezone/date-function semantics change | Can affect boundary implementation |
| Any internal metric-definition change | Requires semantic-contract version bump and fixture/regression update |
| Any source schema/grain/lineage change | Requires re-validation even if the generated SQL text is unchanged |
| A new model release | Relevant only after rerunning the target workflow; leaderboard improvement alone is not evidence of correctness here |

The central decision therefore does **not** depend on finding an LLM that “knows SQL well enough.” Current text-to-SQL research shows substantial capability, but it also documents ambiguity, benchmark-quality problems, access-control failures and robustness challenges. [3](https://arxiv.org/abs/2601.08778) The more defensible architecture is to let the model propose SQL while the surrounding system defines what the number means and produces observable evidence that the implementation respects that definition.

For analytical decisions, the useful standard is not **“the SQL looks good and ran.”** It is **“the definition was explicit, the source/version and permission boundary were known, the query preserved grain, adversarial cases produced known results, intermediate totals reconciled, and unresolved ambiguity was not silently guessed.”**

## Portable citation index

1. [Fistful of Flaws - Kimball Group](https://www.kimballgroup.com/2003/10/fistful-of-flaws/)
2. [Semantic Evaluation for Text-to-SQL with Distilled Test Suites](https://arxiv.org/abs/2010.02840?utm_source=chatgpt.com)
3. [[2601.08778] Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards](https://arxiv.org/abs/2601.08778)
4. [PICARD: Parsing Incrementally for Constrained Auto- ...](https://aclanthology.org/2021.emnlp-main.779/?utm_source=chatgpt.com)
5. [About MetricFlow | dbt Developer Hub](https://docs.getdbt.com/docs/build/about-metricflow?utm_source=chatgpt.com)
6. [Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows](https://arxiv.org/abs/2411.07763?utm_source=chatgpt.com)
7. [xlang-ai/Spider2: [ICLR 2025 Oral] Spider 2.0: Evaluating ...](https://github.com/xlang-ai/Spider2?utm_source=chatgpt.com)
8. [Benchmarking Text-to-SQL under Role-Based Access Control](https://arxiv.org/abs/2607.22115)
9. [AmbiSQL: Interactive Ambiguity Detection and Resolution for Text-to-SQL](https://arxiv.org/abs/2508.15276?utm_source=chatgpt.com)
10. [About data tests property | dbt Developer Hub](https://docs.getdbt.com/reference/resource-properties/data-tests?utm_source=chatgpt.com)
11. [Timestamp functions | BigQuery](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?utm_source=chatgpt.com)
12. [Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3 - Kimball Group](https://www.kimballgroup.com/2005/03/slowly-changing-dimensions-are-not-always-as-easy-as-1-2-3/)
13. [EvoSchema: Towards Text-to-SQL Robustness Against Schema Evolution](https://arxiv.org/abs/2603.10697?utm_source=chatgpt.com)
14. [Introduction to BigQuery row-level security](https://docs.cloud.google.com/bigquery/docs/row-level-security-intro?utm_source=chatgpt.com)
15. [Run a query | BigQuery](https://docs.cloud.google.com/bigquery/docs/running-queries?utm_source=chatgpt.com)
16. [Model contracts | dbt Developer Hub](https://docs.getdbt.com/docs/mesh/govern/model-contracts?utm_source=chatgpt.com)
17. [Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs](https://arxiv.org/abs/2305.03111?utm_source=chatgpt.com)
18. [About dbt freshness command | dbt Developer Hub](https://docs.getdbt.com/reference/commands/freshness?utm_source=chatgpt.com)
19. [Best practices for using policy tags in BigQuery](https://docs.cloud.google.com/bigquery/docs/best-practices-policy-tags?utm_source=chatgpt.com)
20. [Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs](https://arxiv.org/abs/2305.03111?utm_source=chatgpt.com)


## Import verification and asset availability

Captured from genuine ChatGPT Deep Research, research snapshot 2026-10-10; completed in 9 minutes. Retrieved 2026-10-10. Original prompt and canonical conversation are retained on the run record. Delivery is not independent verification of studies, dates, benchmark values or execution claims.

Referenced sandbox fixture/harness files are unavailable in the rendered report: every corresponding download anchor has an empty href; a direct click on the practitioner fixture produced no download. No executable attachment is claimed as delivered. Embedded report examples are preserved; research assertions about execution remain unverified.