# AI-Assisted SQL With Verification: Evidence Dossier **Research snapshot:** 2026-10-10 **Decision supported:** How should a small data team pilot AI-assisted SQL while detecting wrong grain, fanout joins, filters, time semantics, slowly changing dimensions, and misleading totals? **Target environment:** analytical warehouse, dbt-style transformations, read-only analytical workloads. **Evidence boundary:** public primary sources and synthetic fixtures only; no private company data or claimed production ROI. ## Decision summary and uncertainties ### Bottom line **Do not treat generated SQL as an answer. Treat it as an untrusted candidate program that has to satisfy an explicit analytical contract and then survive executable checks.** For a data lead, I would pilot AI-assisted SQL only behind this sequence: **question → explicit metric contract → versioned source retrieval → candidate SQL → deterministic structural/data checks → read-only execution → reconciliation against known results/invariants → human review when required → interpretation.** That distinction matters because SQL can be syntactically valid, execute successfully, and return plausible values while being analytically wrong. Research on semantic-error detection explicitly studies this class of failure, and current systems are not reliable enough to make model self-review the sole gate: NL2SQL-BUGs contains 2,018 expert-annotated semantic-error cases and reports average LLM error-detection accuracy of 75.16%; SQLens likewise starts from the observation that syntactically valid SQL can remain semantically incorrect. [1](https://arxiv.org/abs/2503.11984) The evidence also argues against a simplistic conclusion that “models are bad at SQL.” **The harder problem is grounding SQL in the correct business meaning and source state.** EntSQL reports that 96% of its enterprise examples require knowledge beyond the question and schema, such as metric definitions, reporting conventions and temporal rules; its best evaluated English system reached 15.9% execution accuracy when supplied long-form enterprise documents. BEAVER, built from three anonymized private enterprise warehouses and query/report patterns, reports only 10.8% execution accuracy for an advanced agentic method in its much harder enterprise setting. These absolute numbers should **not** be projected onto a curated small-team warehouse, but they are strong counterevidence to treating schema access plus a powerful model as sufficient. [2](https://arxiv.org/html/2606.03363v3) Conversely, explicit semantics help. A 2026 paired preprint tested three frontier models on 100 retail questions and found that adding a 4 KB hand-authored semantic document improved accuracy by 17–23 percentage points, from 45.5–50.5% to 67.7–68.7%. That is encouraging, but the residual error rate is also the important result: **better context reduced errors; it did not eliminate the need for verification.** This is a single preprint on one dataset rather than a production customer study. [3](https://arxiv.org/abs/2604.25149) ### The decision I would make Run a **bounded pilot**, but position it as **AI-assisted SQL authoring with verification**, not autonomous analytics. The pilot should initially admit only questions for which: | Requirement | Minimum condition | |---|---| | Metric meaning | An authoritative definition already exists, or the user explicitly resolves the definition before SQL generation. | | Sources | Approved warehouse models/tables and their expected grain are known. | | Access | The user/system identity already has read permission; the AI cannot expand permissions. | | Expected behavior | The task can be checked by known results, fixture cases, reconciliations or strong invariants. | | Operation | Read-only analytical SQL; no DDL/DML or autonomous remediation. | | Risk | Internal exploratory or operational analytics first; externally reported/high-consequence numbers keep mandatory analyst sign-off. | | Versions | Metric definition, model/schema version and data snapshot/as-of state can be recorded with the result. | Questions should **not** silently proceed when “net revenue,” “active customer,” refund recognition, cancellation treatment, timezone, fiscal month, currency treatment, or SCD interpretation is unresolved. Ambiguity is not a model-confidence problem. It is missing specification. Research systems such as AmbiSQL explicitly move ambiguity resolution *before* final SQL by asking the user to clarify intent. [4](https://arxiv.org/abs/2508.15276) ### What remains uncertain The major uncertainty is external validity. Enterprise text-to-SQL benchmarks intentionally stress much harder conditions than many small-team marts, while common public benchmarks can underrepresent enterprise complexity. The absolute benchmark scores therefore do not predict a particular team's pilot accuracy. [2](https://arxiv.org/html/2606.03363v3) Benchmark labels themselves are not unquestionable ground truth. ReViSQL's authors report correcting data errors in **61.1% of a subset** of BIRD Train during construction of their verified dataset, and showed materially different model results after cleaning. The correct inference is not that “61.1% of BIRD is wrong”; it is that evaluation labels also require verification and versioning. [5](https://arxiv.org/html/2603.20004v2) This field is also moving quickly enough that benchmark names without versions are inadequate. The Spider 2.0 repository records ambiguity fixes, an evaluation-suite correction that changed scores, dynamic data updates, and the replacement of the original Spider2 setting with a 68-task Spider2-DBT setting. A pilot should therefore record benchmark commit/data version rather than quote a floating leaderboard number. [6](https://github.com/xlang-ai/Spider2) ## Evidence synthesis around the decision ### Model capability is not system reliability The central design error to avoid is compressing five different functions into “the AI answered the question”: | Function | What it should do | How to evaluate it | |---|---|---| | **Retrieval** | Select the right governed models, definitions and documentation versions. | Source recall, wrong-source rate, version fidelity. | | **SQL generation** | Translate a resolved analytical specification into candidate SQL. | Correct tables, joins, predicates, aggregation and dialect. | | **Execution / calculation** | Let the warehouse perform deterministic relational and numeric operations. | Executability, result equality, invariants, reconciliation. | | **Interpretation** | Explain the verified result and relevant caveats. | Factual fidelity to returned values and definitions. | | **Human judgment** | Resolve business ambiguity, approve high-risk uses and decide what conclusion matters. | Review effort, disagreement, error severity, decision outcome. | Enterprise research supports this decomposition. BEAVER evaluates retrieval, join-key detection, column mapping, domain-knowledge extraction and query decomposition separately because success on one component does not guarantee a correct final answer. [7](https://arxiv.org/html/2409.02038v3) EntSQL makes the semantic dependency especially clear: a realistic analytical request may require internal reporting conventions, metric definitions, temporal rules and entity mappings that cannot be recovered from schema names alone. [2](https://arxiv.org/html/2606.03363v3) **Interpretation:** a bigger model can improve candidate generation, but system reliability comes from constraining the candidate with authoritative context, warehouse permissions, executable tests, abstention and review. ### Context should be authoritative, small enough to inspect, and versioned “Give the model more schema” is weaker than “give it the correct analytical contract.” For each eligible question, the context package should contain at least: | Context object | Example for this dossier | |---|---| | Metric definition | What exactly counts as net revenue? | | Result grain | One row per local calendar month. | | Fact grains | `orders`: order; `order_items`: order item; `refunds`: refund event. | | Dimension grain | `customer_history`: customer × validity interval. | | Join contracts | `order_items → orders` many-to-one; refunds require pre-aggregation before order-level combination; SCD uses time-range join. | | Time semantics | Reporting timezone; order time vs refund time vs cancellation time; `[start, end)` boundary convention. | | Null policy | Guest customer IDs, unmatched dimension records, missing refund values. | | Additivity | Revenue additive over months; monthly distinct customers non-additive. | | Allowed sources | Governed model identifiers rather than any discoverable raw relation. | | Version | Metric-definition version, dbt/model version or Git SHA, data as-of/cutoff. | | Permissions | User/query identity and permitted models/columns/rows. | There is empirical support for supplying explicit business semantics rather than making the model infer them, although the strongest recent controlled result located here is a preprint rather than independent field evidence. [3](https://arxiv.org/abs/2604.25149) dbt's model contracts are useful but narrower: its documented preflight checks validate returned column names and data types and can incorporate some platform-supported constraints. **Inference:** that protects output shape, not the business meaning of “net revenue”; analytical semantics therefore require separate tests and definitions. [8](https://docs.getdbt.com/docs/mesh/govern/model-contracts) ### Grain should become an executable contract Before generating SQL, require the proposed solution to state: > **Result grain:** one row per reporting month. > **Order grain:** one row per `order_id`. > **Item grain:** one row per `order_item_id`; aggregate to order before combining with another one-to-many child. > **Refund grain:** one row per refund event; aggregate to the recognition grain before combination. > **Customer-history grain:** one row per `customer_id × validity interval`. Then test those claims. For a supposed primary grain `K`, the simplest diagnostic is: ```sql select count(*) as rows, count(distinct K) as distinct_keys from relation; ``` A declared unique grain requires equality. In a dbt project this maps naturally to `unique` and `not_null` tests; dbt also provides `relationships` tests and permits arbitrary business assertions expressed as SQL returning failing rows. [9](https://docs.getdbt.com/docs/build/data-tests) For generated analytical SQL, I would add per-stage observability: ```text CTE / relation declared grain rows distinct grain keys orders order_id ... item_gross order_id ... refund_by_order order_id ... qualified_orders order_id ... monthly_result month ... ``` A model does not get to “explain away” a grain violation. A failed uniqueness/multiplicity check is a deterministic failure. ### Joins require cardinality checks, not visual inspection The dangerous pattern is not an obviously invalid join. It is a valid join that changes multiplicity. Suppose one order has: - two `order_items`; - two `refunds`. Joining both children directly at `order_id` creates **four combined rows**. `SUM(item_amount)` and `SUM(refund_amount)` can both become inflated even though the query executes perfectly. The safe default for additive measures is: ```text order_items → aggregate to order_id refunds → aggregate to order_id according to refund policy then join the two order-grain relations ``` A verification layer should compute for every join: ```text expected cardinality: 1:1 / many:1 / 1:many / many:many left row count joined row count matched left keys unmatched left keys max matches per left key p95 / p99 matches per left key keys violating expected cardinality ``` For an expected many-to-one join, this query should return zero rows: ```sql select left_key, count(*) as matches from left_relation l join right_relation r on ... group by left_key having count(*) > 1; ``` This is precisely the kind of database-grounded verification that recent research finds useful. SQLens combines database signals with LLM signals and reports higher semantic-error-detection F1 than its best LLM-only self-evaluation comparison; PV-SQL similarly combines probing with an executable rule/checklist approach. Both results are benchmark results—not production guarantees—but they support the architecture of **database probes plus deterministic conditions rather than verbal self-critique alone**. [10](https://arxiv.org/abs/2506.04494) ### Slowly changing dimensions need an as-of assertion dbt snapshots implement Type-2 slowly changing dimensions by preserving successive states and validity information rather than overwriting history. [11](https://docs.getdbt.com/docs/build/snapshots) A fact at time `t` therefore normally needs a validity join such as: ```sql fact.customer_id = history.customer_id and fact.event_at >= history.valid_from and ( fact.event_at < history.valid_to or history.valid_to is null ) ``` Joining on `customer_id` alone may attach several historical states to one event, causing both semantic leakage from the future and row multiplication. The corresponding invariant is more important than the syntax: > For every fact that requires a known dimension state, exactly one history record must match the event timestamp. ```sql select fact_id, count(history.customer_id) as history_matches from ... group by fact_id having count(history.customer_id) <> 1; ``` A zero-match case may mean incomplete history; a multi-match case may mean overlapping SCD intervals. Both should stop automatic acceptance rather than be papered over with `DISTINCT`. ### Filters should be represented as a filter contract For each metric, record the temporal and eligibility predicates independently of SQL syntax. For the worked example below: ```text Reporting timezone: Europe/Prague Reporting cutoff: 2026-03-05T00:00:00Z Month assignment: local month of ordered_at_utc Refund handling: include refunds processed before cutoff; allocate them back to original order month Cancellation: remove orders cancelled before cutoff Active customer: customer had a qualifying positive-net order AND customer_history.status = 'active' as of order time Interval rule: [valid_from, valid_to) Guest customer: revenue included; active-customer count excluded ``` Every line could reasonably have been defined differently. A generated query should not invent the choice. Timezone handling is not cosmetic. BigQuery, for example, documents time-zone-aware timestamp extraction and notes that one UTC instant can correspond to different civil dates depending on timezone; it also warns of unintuitive truncation behavior around some daylight-saving transitions. [12](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions) Accordingly, unit fixtures should deliberately include: - a UTC timestamp on one side of a local month boundary; - the other side of that boundary; - a daylight-saving transition if the business operates in affected zones; - exact `valid_to` timestamps; - null customer IDs; - late refunds and cancellations. dbt's unit-test documentation explicitly recommends static fixtures for SQL logic including date math, truncation and edge cases before materializing a full model. [13](https://docs.getdbt.com/docs/build/unit-tests) ### Totals need an additivity declaration Not every monthly metric can be totaled with `SUM(monthly_value)`. In the worked definition: \[ \text{Period net revenue} = \sum_m \text{Monthly net revenue}_m \] because the chosen revenue measure is additive over these disjoint reporting months. But: \[ \text{Period active customers} \neq \sum_m \text{Monthly distinct active customers}_m \] because one customer may be active in several months. Instead: \[ \text{Period active customers} = \left| \bigcup_m \text{qualifying customer IDs in month } m \right| \] This distinction should be part of metric metadata: ```text net_revenue: time_additivity: additive active_customers: time_additivity: non_additive total_rule: recompute distinct customer_id at requested total scope ``` This catches a particularly deceptive class of BI output: every monthly row is correct, while the grand total is wrong. ## Verification design for a production-minded pilot ### The verification stack I would implement six gates. | Gate | Required evidence | Automatic outcome on failure | |---|---|---| | **Semantic specification** | Grain, definition, timezone, filters, total behavior, cutoff. | Clarify; no SQL generation. | | **Source and version fidelity** | Approved relations; definition/model versions; data as-of. | Abstain or retrieve correct context. | | **Permission boundary** | Query runs as appropriately constrained identity against approved sources. | Deny; never request elevated access merely to satisfy the query. | | **Structural/data checks** | Grain uniqueness, join cardinality, null handling, SCD matches, boundary predicates. | Reject candidate SQL. | | **Executable verification** | Known-result fixtures, reconciliations, independent component totals. | Reject/revise candidate SQL. | | **Risk-based review** | Analyst/metric-owner approval where policy requires it. | Do not publish/use decision. | The ordering matters. **Do not execute first and ask whether the result “looks reasonable” later.** ### Source fidelity should be a separately scored property A query can calculate the right-looking number from the wrong table. Store with every accepted answer: ```text question / resolved specification metric_definition_id + version source relation identifiers model / semantic-layer version dbt project Git SHA or equivalent release identifier data snapshot / warehouse timestamp generated SQL hash + full SQL execution identity verification checks and outcomes result hash reviewer / review status where applicable ``` This makes two evaluations independent: 1. **Was the intended, approved source/version used?** 2. **Did the resulting SQL produce the correct analytical result?** One should not substitute for the other. Spider 2.0's own evolving evaluation data illustrates why this matters: its repository records corrected ambiguous examples, evaluation-suite fixes and changing leaderboard results. [6](https://github.com/xlang-ai/Spider2) ### Permission compliance must be enforced below the model The model should not decide what it is allowed to read. A warehouse can enforce this at query time. As one concrete platform example, BigQuery supports row-level policies that filter visible rows based on user conditions, column-level access controls using policy tags, and authorized views that expose queryable subsets without granting direct source-dataset access. [14](https://cloud.google.com/bigquery/docs/row-level-security-intro) The architecture should therefore be: ```text AI proposes SQL ↓ fixed execution identity / user-delegated constrained identity ↓ warehouse authorization ↓ read-only query ``` not: ```text AI service account with broad warehouse access ↓ application tries to decide what the user should see ``` That second design turns the AI layer into a new authorization system—unnecessary leverage in exactly the wrong place. For Czech/EU use, this has an additional privacy implication. GDPR Article 5 establishes purpose limitation, data minimization, accuracy, integrity/confidentiality and accountability for processing personal data. That argues for retrieving only the schema fields, examples and result rows needed for the task rather than spraying raw warehouse contents into prompts or probing queries. [15](https://eur-lex.europa.eu/eli/reg/2016/679/oj/eng) I found no SQL-correctness reason to design a different verification algorithm for Czech versus US teams. The material difference here is governance around data access and personal data; jurisdiction-specific legal review remains separate from this technical dossier. ### Verification should use independent signals The strongest check is not “ask another model whether this SQL is correct.” Use several differently correlated mechanisms: **Static/structural:** approved relations, prohibited statements, declared grain, expected join paths, required predicates. **Data probes:** uniqueness, referential match rate, multiplicity distribution, SCD match counts, null rates. **Fixture execution:** adversarial miniature data where the answer is known. **Reconciliation:** calculate revenue components independently: \[ \text{Net} = \text{Item gross} - \text{Eligible refunds} \] and compare their combined result with the candidate. **Result-level invariants:** `net <= gross` under the chosen contract, counts non-negative, result key unique, and total rules obeyed. **Metamorphic cases:** make a controlled change whose effect is known. Examples: - duplicate an unrelated dimension row → correct result should remain unchanged if the join is properly constrained; - place an order one second across a local month boundary → exactly one month's allocation should change; - add a canceled order → result should change or remain unchanged according to the declared cancellation policy; - split one $30 refund into two $10+$20 refund records → correct order-level net revenue should remain identical; - split one $100 item into two $40+$60 item rows → order-level revenue should remain identical. Research systems such as PV-SQL provide evidence that database probing and executable verification constraints can improve benchmark performance, while SQLens reports advantages over pure LLM self-evaluation. The important transfer here is the architecture, not their benchmark effect sizes. [10](https://arxiv.org/abs/2506.04494) ### Clarification, abstention and human-review conditions **Force clarification before SQL** when two defensible definitions lead to different answers. For this scenario that includes: > “Do refunds reduce the month in which the sale occurred, or the month in which the refund was processed?” > “Does active customer mean a customer with qualifying purchase activity, or one whose lifecycle status was `active`?” > “Which timezone assigns orders to months?” > “Does a later cancellation restate the original month, or become a negative event in the cancellation month?” The existence of ambiguity-aware text-to-SQL work such as AmbiSQL reinforces that asking is preferable to silently picking a semantic branch. [4](https://arxiv.org/abs/2508.15276) **Force abstention** when an authoritative definition cannot be located, source versions conflict, an expected many-to-one join becomes many-to-many, an SCD fact has zero or multiple as-of matches, deterministic verification disagrees with the candidate, or the needed data falls outside the execution identity's permissions. **Force human review** for a newly defined metric, externally communicated financial/reporting numbers, high-consequence decisions, unexplained reconciliation differences, material source/metric-version changes, or any situation where a correct business interpretation depends on context that has not been encoded into the governed definition. These are proposed operating controls, not claims of an industry standard. ## Worked synthetic example and portable verification asset ### The unresolved business question The prompt is: > “Calculate monthly net revenue and active customers from orders, order items, refunds and customer history.” That is **not yet executable as a responsible analytical specification**. At least four questions must be answered: 1. Does a refund reduce the original order month or the refund-processing month? 2. Are cancellations retroactive restatements or negative events in cancellation month? 3. What makes a customer “active”: purchase behavior, lifecycle status, or both? 4. Which timezone defines calendar months? A model that writes SQL before resolving these choices is generating assumptions, not merely code. ### One explicit definition for the fixture For demonstration, the portable fixture makes these choices: ```text Reporting timezone: Europe/Prague Reporting cutoff: 2026-03-05T00:00:00Z Revenue month: local calendar month of ordered_at_utc Gross revenue: Σ(quantity × unit_price) for order items Refunds: refunds processed before cutoff; allocated back to the original order month Cancellations: orders cancelled before cutoff are removed from revenue Active customer: distinct non-null customer_id with a qualifying order having positive net order revenue AND customer_history.status = 'active' at order timestamp Customer SCD: valid_from <= ordered_at < valid_to, with NULL valid_to meaning current/open-ended Guest/inactive customers: their qualifying revenue remains revenue; they do not count as active customers Monthly net revenue: additive across reporting months Monthly active customers: non-additive across months ``` This is a **synthetic choice**, not a recommendation for how a real company should recognize revenue. ### Fixture cases The miniature dataset includes the traps requested in the research scope. | Case | Synthetic fact | What it tests | |---|---|---| | Month boundary | `O1` occurs `2026-01-31 23:30 UTC`, which is February in Prague. | UTC/local month mismatch. | | Month boundary | `O4` occurs `2026-02-28 23:30 UTC`, which is March in Prague. | Same error in opposite reporting transition. | | Fanout | `O2` has two items and two refund records. | Joining both children at order grain multiplies rows. | | Cancellation | `O3` is subsequently canceled before cutoff. | Cancellation rule. | | Null | `O6` has no customer ID. | Revenue vs customer-count null semantics. | | SCD | `C1` changes from active to inactive on Feb 15. | As-of dimension join. | | Non-additive count | `C2` buys in February and March. | Monthly distinct counts cannot be summed. | | Metric separation | Inactive `C1` and `C4` can contribute revenue but not active-customer count. | Do not accidentally filter revenue through customer-status logic. | ### Known result Under the stated definition, the independently specified expected monthly output is: | Reporting month | Net revenue | Active customers | |---|---:|---:| | 2026-02 | $400.00 | 2 | | 2026-03 | $240.00 | 2 | The money calculation is stored as integer cents in the fixture. For February: \[ (150-20) + (200-30) + 60 + 40 = 400 \] The canceled order is excluded, and the Feb 28 23:30 UTC order belongs to March in Prague. For March: \[ 120 + 50 + 70 = 240 \] The $70 order from `C1` contributes revenue but not an active customer because `C1` is inactive at that event time under the selected SCD definition. The tempting total is: \[ 2 + 2 = 4 \] but the correct period-level distinct active customer set is: \[ \{C1,C2,C3\} \] so: \[ \text{Period active customers}=3 \] while: \[ \text{Period net revenue}=400+240=640 \] This demonstrates why metric additivity belongs in the semantic contract, not merely in chart formatting. ### An executable query can still be badly wrong The included deliberately bad query: - uses UTC month rather than Europe/Prague; - joins `order_items` and `refunds` simultaneously at order grain; - joins customer history only on `customer_id`, without validity intervals; - still executes successfully. On the synthetic fixture it returns: | UTC-derived month | Wrong net revenue | Active customers | |---|---:|---:| | 2026-01 | $220.00 | 1 | | 2026-02 | $560.00 | 2 | | 2026-03 | $190.00 | 2 | Nothing about successful execution reveals that the intended result was February $400 and March $240. That is the central practical point: **execution validity is necessary and weak evidence of analytical correctness.** Research on semantic SQL errors makes the same distinction. [1](https://arxiv.org/abs/2503.11984) ### Portable test asset [Download the portable synthetic verification harness](sandbox:/mnt/data/ai_sql_verification_fixture.py) It is a self-contained Python 3.9+ script using only the standard library (`sqlite3` and `zoneinfo`). It creates the synthetic schema and fixtures, registers an IANA `Europe/Prague` month function, executes a reference query, tests fixture invariants, executes candidate SQL under a read-only SQLite authorizer, verifies source/metric-version markers, compares the result with known output, and demonstrates the misleading-total trap. Run: ```bash python ai_sql_verification_fixture.py --demo good python ai_sql_verification_fixture.py --demo bad ``` A candidate SQL file can be tested with: ```bash python ai_sql_verification_fixture.py --candidate candidate.sql ``` Its required result contract is: ```text month net_revenue_cents active_customers ``` This is deliberately a **logic fixture**, not a substitute for dialect-specific warehouse tests. Production BigQuery, Snowflake, Databricks, Postgres or other SQL should additionally be tested in its actual engine because date/time functions, type coercion and other behavior differ by dialect. BigQuery's documented timestamp behavior alone illustrates why cross-dialect assumptions should not be trusted blindly. [12](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions) ### How I would translate the fixture into dbt Use dbt unit tests for narrow transformation logic and static edge cases, which dbt explicitly supports for SQL models, including date math, truncation and edge cases. [13](https://docs.getdbt.com/docs/build/unit-tests) Use data tests for persistent warehouse invariants: ```text orders.order_id: unique not_null order_items.order_item_id: unique not_null order_items.order_id: relationships → orders.order_id refunds.order_item_id: relationships → order_items.order_item_id monthly_result.month: unique not_null custom: every non-null eligible customer event has exactly one SCD as-of match custom: no order-level row multiplicity after item/refund pre-aggregation custom: result agrees with component-level gross/refund reconciliation ``` dbt's built-in data-test mechanism supports uniqueness, null, relationship and accepted-value assertions and permits custom SQL business assertions. [9](https://docs.getdbt.com/docs/build/data-tests) ## Pilot design, baseline and economics ### Compare the workflow, not SQL-writing speed A misleading pilot asks: > “How long did AI take to generate SQL versus how long did an analyst type it?” A useful pilot measures: \[ T_\text{lifecycle} = T_\text{context} + T_\text{clarification} + T_\text{generation} + T_\text{execution} + T_\text{failed queries} + T_\text{verification} + T_\text{review} + T_\text{reconciliation} + T_\text{maintenance allocation} \] The non-AI baseline should receive the **same task, same authoritative documentation, same warehouse snapshot and same access permissions**. For each task, capture: | Measure | Analyst baseline | AI-assisted workflow | |---|---:|---:| | Context preparation minutes | ✓ | ✓ | | Clarification minutes | ✓ | ✓ | | SQL authoring/generation minutes | ✓ | ✓ | | Failed executions | ✓ | ✓ | | Warehouse queries/bytes/credits | ✓ | ✓ | | Verification minutes | ✓ | ✓ | | Review/reconciliation minutes | ✓ | ✓ | | Final result correct | ✓ | ✓ | | Source/version correct | ✓ | ✓ | | Permission compliant | ✓ | ✓ | | Ambiguity correctly surfaced | ✓ | ✓ | | Total elapsed time | ✓ | ✓ | This prevents a ten-second SQL generation from being credited as productivity if it induces twenty minutes of detective work. ### A bounded task set I would use roughly **60–100 locally representative tasks** for the first decision pilot, with deliberate coverage of: ```text ordinary single-fact aggregations one-to-many joins multiple one-to-many children / fanout null dimension keys month and timezone boundaries refund/cancellation timing SCD history non-additive totals ambiguous metric requests permission-denied sources stale/wrong semantic versions ``` The number is a proposed practical pilot size, not a statistical rule. With small samples, percentage differences will have wide uncertainty; retain per-task evidence rather than pretending a single accuracy number is precise. The gold answer should not simply be “whatever the senior analyst wrote once.” ReViSQL's benchmark-cleaning results are a useful warning that labels and reference SQL themselves can contain errors. Gold cases should be reviewed, executable and preferably supported by component reconciliations or hand-checkable fixtures. [5](https://arxiv.org/html/2603.20004v2) ### Proposed go/no-go thresholds These are **proposed operating thresholds for this pilot**, not externally validated standards. | Dimension | Proposed pilot gate | |---|---| | Permission compliance | **100%**; any unauthorized read/write or bypass attempt is a hard stop. | | Source/version fidelity | **100%** on the evaluation set. | | Known ambiguous-definition cases | **100% clarify/abstain** rather than silently choose a consequential definition. | | Core trap suite | **Zero silent wrong answers** on fanout, SCD, time-boundary, cancellation/refund and non-additive-total traps. | | Final analytical correctness | Target **≥98%** on eligible gold-supported tasks *after the full workflow*, and not worse than the analyst baseline. | | Repeatability | Same pinned specification + data snapshot should return the same deterministic result on repeated runs. | | Critical errors | **Zero** permission violations or materially misleading accepted results. | | Human review effort | At least **20% lower median review/reconciliation time** than baseline before calling the pilot an efficiency win. | | Tail effort | Do not accept a gain driven solely by easy cases if the 90th-percentile review burden materially worsens. | | Failure recovery | Failed verification must produce a traceable rejection/revision/clarification state rather than a polished unsupported answer. | The ≥98% and 20% values are **decision thresholds I propose**, not measured industry norms. A team can set stricter thresholds for financial or regulatory reporting. I would also distinguish “generator accuracy” from “workflow accuracy.” A generator may fail often while the system remains safe because verification catches the failures. Conversely, an impressive generation rate is irrelevant if one silent fanout error reaches a decision-maker. ### Ownership and review responsibility | Owner | Responsibility | |---|---| | **Metric/business owner** | Owns definitions, temporal rules and interpretation. | | **Analytics/data engineer** | Owns source models, grain, lineage, join contracts, data tests and versioning. | | **Security/platform owner** | Owns warehouse identities, row/column access and read-only execution boundaries. | | **Analyst/reviewer** | Resolves non-encoded ambiguity and approves risk-tiered results. | | **AI system** | Generates candidates, gathers permitted context, proposes checks and explains verified outputs. It owns no business definition or access decision. | ### Transparent synthetic economics No measured ROI was found or assumed. The useful calculation is a break-even model. Let: - \(N\) = eligible AI-assisted questions per month; - \(s\) = end-to-end analyst minutes saved per question **after review and failures**; - \(S\) = one-time setup hours; - \(A\) = months over which setup is amortized; - \(M\) = recurring governance/testing/maintenance hours per month; - \(H\) = loaded labor cost per hour; - \(C\) = variable AI + incremental warehouse cost per question. Then: \[ \text{monthly overhead hours} = \frac{S}{A}+M \] \[ \text{gross labor hours saved} = \frac{N \times s}{60} \] \[ \text{net monthly value} = H \left( \frac{Ns}{60} - \frac{S}{A} - M \right) - NC \] For an **illustrative—not measured—scenario**: ```text N = 100 questions/month S = 24 setup hours A = 3 months M = 4 hours/month monthly overhead = 24 / 3 + 4 = 12 hours ``` Sensitivity to actual end-to-end minutes saved: | Minutes saved per eligible question | Gross hours saved / month | Net hours after 12 h overhead | Questions/month needed to cover 12 h overhead | |---:|---:|---:|---:| | 5 | 8.3 h | −3.7 h | 144 | | 10 | 16.7 h | +4.7 h | 72 | | 15 | 25.0 h | +13.0 h | 48 | | 20 | 33.3 h | +21.3 h | 36 | This is why SQL generation latency is the wrong ROI metric. At five minutes of genuine lifecycle saving per question, a modest setup/governance burden can erase the benefit. At twenty minutes, the economics look very different. The pilot should therefore measure \(s\), \(M\) and \(C\) rather than debate them theoretically. ## Contradictions, limitations, and claim-to-source dossier ### Where the evidence pulls in different directions **Explicit semantic context works, but is not enough.** The paired semantic-layer benchmark reports a sizable 17–23 percentage-point improvement, yet accuracy remained below 70% in that experiment. The defensible conclusion is “encode semantics,” not “a semantic layer solves AI SQL.” [3](https://arxiv.org/abs/2604.25149) **Verification agents help, but they do not prove autonomous reliability.** SQLens and PV-SQL report benchmark gains from database signals, probing and rule-based/executable verification. These results support adding independent verification channels; they do not establish error-free production operation or eliminate human review. [10](https://arxiv.org/abs/2506.04494) **Hard enterprise benchmarks show poor end-to-end performance, but may understate performance in a curated mart.** EntSQL and BEAVER intentionally include private business rules, difficult schemas and complex enterprise SQL. Their low absolute results demonstrate unresolved capability problems, but using those percentages as a forecast for a five-table governed revenue mart would be bad inference. [2](https://arxiv.org/html/2606.03363v3) **Headline benchmark scores can move because the benchmark moved.** Spider 2.0 explicitly documents changed examples, ambiguity fixes and evaluation-suite corrections. ReViSQL also demonstrates that reference-data quality can materially change measured performance. Model evaluation therefore needs frozen data/commit identifiers. [6](https://github.com/xlang-ai/Spider2) **Human gold SQL is not automatically gold.** Human review is necessary for semantic ownership, but reviewers can also make mistakes. The target is independently verifiable analytical truth where possible—not deference to either human or model authority. ReViSQL's expert correction workflow and NL2SQL-BUGs' discovery of benchmark annotation errors are direct evidence for this caution. [5](https://arxiv.org/html/2603.20004v2) ### Unresolved gaps I did **not** find strong public, independent, longitudinal evidence measuring the full lifecycle economics of AI-assisted analytical SQL in small professional data teams under the verification regime proposed here. Consequently, this dossier makes no production ROI claim. The most relevant recent semantic-layer study located is a preprint with 100 questions over one retail dataset. Its effect sizes are useful evidence but should not be treated as broadly established causal estimates across organizations. [3](https://arxiv.org/abs/2604.25149) Current enterprise benchmarks capture realistic complexity better than classic schema-only tasks, but no public benchmark can reproduce a specific organization's undocumented revenue conventions, data quality problems, permission structure and review culture. EntSQL explicitly focuses on this business-knowledge gap. [2](https://arxiv.org/html/2606.03363v3) The supplied fixture tests known error classes. It cannot establish that a system will detect **unknown** semantic errors. This is precisely why clarification, ownership and human escalation remain part of the recommended design. A CIDR 2026 paper titled *Text-to-SQL Benchmarks are Broken: An In-Depth Analysis of Annotation Errors* was located in official conference material, but the PDF could not be reliably inspected with the required page-level PDF workflow in this research session. I therefore **excluded its specific numerical annotation-error estimates** rather than silently relying on search snippets or secondary summaries. The independently accessible ReViSQL and NL2SQL-BUGs sources provide evidence of benchmark-label problems without that inaccessible evidence. [16](https://www.vldb.org/cidrdb/2026/text-to-sql-benchmarks-are-broken-an-in-depth-analysis-of-annotation-errors.html?utm_source=chatgpt.com) ### Claim-to-source table | Claim supported | Primary source and direct URL / DOI | Source date | Inspected | Exact scope | Evidence type | |---|---|---|---|---|---| | Enterprise SQL often depends on business definitions beyond question/schema; EntSQL has 1,066 bilingual examples, 96% requiring external domain knowledge; best evaluated English system with long documents reached 15.9% execution accuracy. | *EntSQL: A Benchmark for Grounding Text-to-SQL in Long-Context Enterprise Knowledge* — `https://arxiv.org/abs/2606.03363` | v3 2026-07-14 | 2026-10-10 | Five enterprise BI domains; long-form proprietary-style context; benchmark result, not production deployment. [2](https://arxiv.org/html/2606.03363v3) | Empirical research / preprint | | Enterprise schemas and queries remain difficult even for recent agentic systems; BEAVER reports 10.8% end-to-end execution accuracy and 30.1% with oracle subtask hints. | *BEAVER: An Enterprise Benchmark for Text-to-SQL* — `https://arxiv.org/abs/2409.02038` | Original arXiv 2024; inspected v3 dated 2026-05-13 | 2026-10-10 | 9,128 queries; three anonymized enterprise warehouses; real-query-derived templates and expert verification; benchmark result. [7](https://arxiv.org/html/2409.02038v3) | Empirical research | | Explicit business semantics improved accuracy by 17–23 percentage points in one controlled paired benchmark, but resulting accuracy remained 67.7–68.7%. | *Semantic Layers for Reliable LLM-Powered Data Analytics* — `https://arxiv.org/abs/2604.25149`; DOI `https://doi.org/10.48550/arXiv.2604.25149` | 2026-04-28 | 2026-10-10 | 100 NL questions; Cleaned Contoso Retail/ClickHouse; three models; schema-only vs schema + 4 KB semantics document. [3](https://arxiv.org/abs/2604.25149) | Empirical research / preprint | | LLM semantic-error detection remains imperfect; average detection accuracy reported as 75.16% on NL2SQL-BUGs. | *NL2SQL-BUGs* — `https://arxiv.org/abs/2503.11984`; related DOI `https://doi.org/10.1145/3711896.3737427` | v1 2025-03-15; v2 2025-06-15 | 2026-10-10 | 2,018 expert-annotated semantic-error instances; 9 top-level / 31 subcategories; benchmark error-detection task. [1](https://arxiv.org/abs/2503.11984) | Peer-reviewed benchmark research | | Database-grounded signals can improve semantic-error detection versus LLM self-evaluation. | *SQLens: An End-to-End Framework for Error Detection and Correction in Text-to-SQL* — `https://arxiv.org/abs/2506.04494`; DOI `https://doi.org/10.48550/arXiv.2506.04494` | 2025-06-04 | 2026-10-10 | Two public benchmarks; reports +25.78% F1 over best LLM self-evaluation comparison and up to 20% execution-accuracy improvement. Not a field deployment. [10](https://arxiv.org/abs/2506.04494) | Empirical research | | Database probing plus executable rule/checklist verification can improve benchmark text-to-SQL results. | *PV-SQL: Synergizing Database Probing and Rule-based Verification for Text-to-SQL Agents* — `https://arxiv.org/abs/2604.17653`; DOI `https://doi.org/10.48550/arXiv.2604.17653` | 2026-04-19 | 2026-10-10 | BIRD benchmark; reports +5% execution accuracy and +20.8% valid-efficiency score versus stated baseline. Findings of ACL 2026; not production evidence. [17](https://arxiv.org/abs/2604.17653) | Empirical research | | Ambiguity can be treated as an interactive clarification problem rather than silently resolved by generation. | *AmbiSQL: Interactive Ambiguity Detection and Resolution for Text-to-SQL* — `https://arxiv.org/abs/2508.15276`; DOI `https://doi.org/10.48550/arXiv.2508.15276` | v1 2025-08-21; v2 2026-03-23 | 2026-10-10 | Interactive ambiguity detection/clarification; demonstration integrated with a commercial text-to-SQL backend. [4](https://arxiv.org/abs/2508.15276) | Research prototype / demonstration | | Benchmark reference data can contain substantial errors; cleaning can materially affect measured accuracy. | *ReViSQL: Achieving Human-Level Text-to-SQL* — `https://arxiv.org/abs/2603.20004` | v2 2026-03-30 | 2026-10-10 | Authors report errors corrected in 61.1% **of a subset of BIRD Train**, 2.5k verified instances, and 8.2–13.9-point single-generation gains from better data under their setup. [5](https://arxiv.org/html/2603.20004v2) | Empirical research / benchmark audit | | Benchmark/data versions materially change over time; Spider 2.0 documents ambiguity fixes, evaluation-suite changes and a 68-task DBT setting. | Spider 2.0 official repository — `https://github.com/xlang-ai/Spider2`; paper `https://arxiv.org/abs/2411.07763` | Paper 2024; repository updates through 2026-08-12 visible at inspection | 2026-10-10 | Official benchmark repository; version/update history, not independent evaluation. [6](https://github.com/xlang-ai/Spider2) | Primary benchmark documentation | | dbt data tests support uniqueness, not-null, relationships and arbitrary SQL assertions. | dbt Developer Hub, Data tests — `https://docs.getdbt.com/docs/build/data-tests` | Rolling documentation; no stable publication date shown | 2026-10-10 | Current dbt documentation; product behavior, not evidence that tests guarantee semantic correctness. [9](https://docs.getdbt.com/docs/build/data-tests) | Provider documentation | | dbt unit tests can validate SQL logic on small static inputs and are recommended for date math, truncation and edge cases. | dbt Developer Hub, Unit tests — `https://docs.getdbt.com/docs/build/unit-tests` | Rolling docs; feature available from dbt v1.8 | 2026-10-10 | SQL-model unit-testing behavior and recommended use cases. [13](https://docs.getdbt.com/docs/build/unit-tests) | Provider documentation | | dbt snapshots implement Type-2 slowly changing dimensions and preserve historical validity states. | dbt Developer Hub, Snapshots — `https://docs.getdbt.com/docs/build/snapshots` | Rolling documentation | 2026-10-10 | dbt snapshot mechanics; supports the SCD context, not a specific join design mandate. [11](https://docs.getdbt.com/docs/build/snapshots) | Provider documentation | | dbt model contracts verify schema shape such as names/types and platform-supported constraints. | dbt Developer Hub, Model contracts — `https://docs.getdbt.com/docs/mesh/govern/model-contracts` | Rolling documentation | 2026-10-10 | Contract/preflight behavior. Conclusion that business semantics require separate checks is this dossier's interpretation of documented scope. [8](https://docs.getdbt.com/docs/mesh/govern/model-contracts) | Provider documentation | | Timezone choice can change calendar-date/month assignment; timestamp truncation has DST caveats. | Google BigQuery Timestamp functions — `https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions` | Rolling documentation | 2026-10-10 | BigQuery/GoogleSQL-specific timestamp behavior. [12](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions) | Provider documentation | | Warehouse-enforced row and column restrictions and governed views can preserve access boundaries around AI-generated queries. | BigQuery row security `https://cloud.google.com/bigquery/docs/row-level-security-intro`; column access `https://cloud.google.com/bigquery/docs/column-level-security-intro`; authorized views `https://cloud.google.com/bigquery/docs/authorized-views` | Rolling documentation | 2026-10-10 | BigQuery-specific authorization mechanisms; analogous design must be adapted per warehouse. [14](https://cloud.google.com/bigquery/docs/row-level-security-intro) | Provider documentation | | For personal data in the EU/Czech context, purpose limitation, data minimization and security/accountability are explicit GDPR principles. | Regulation (EU) 2016/679 — `https://eur-lex.europa.eu/eli/reg/2016/679/oj/eng` | Adopted 2016-04-27; OJ 2016-05-04 | 2026-10-10 | GDPR Article 5; relevant only where the AI/analytics workflow processes personal data. [15](https://eur-lex.europa.eu/eli/reg/2016/679/oj/eng) | Primary law | ## Practical recommendation, article angles and freshness triggers ### The minimum viable safe architecture For a small professional team, I would **not** begin with an elaborate autonomous SQL agent. Start with a deliberately boring pipeline: ```text User question │ ▼ Resolve definition / ambiguity │ ▼ Retrieve approved metric + source metadata │ ▼ LLM produces: - SQL - declared result grain - source list - expected join cardinalities - filters/time semantics - additivity/total rule │ ▼ Deterministic verifier - allowed sources? - read-only? - grain tests? - join multiplicity? - SCD as-of? - null/boundary cases? - version match? │ ▼ Constrained warehouse execution │ ▼ Fixture / reconciliation / invariant checks │ ├── fail → revise / clarify / abstain │ ▼ Risk-tiered analyst review │ ▼ Result + SQL + provenance + caveats ``` The high-leverage feature is not another “SQL expert” prompt. It is turning the **implicit assumptions analysts normally hold in their heads into inspectable, executable contracts**. ### What I would build first First, require a structured pre-SQL object: ```yaml question: monthly net revenue and active customers result_grain: - reporting_month metrics: net_revenue: definition_version: rev-v1 additive_over_time: true active_customers: definition_version: active-v1 additive_over_time: false total_rule: recompute_distinct time: timezone: Europe/Prague order_month_field: ordered_at_utc refund_policy: allocate_to_original_order_month cancellation_policy: restate_original_order reporting_cutoff: 2026-03-05T00:00:00Z sources: orders: grain: order_id order_items: grain: order_item_id joins_to_orders: many_to_one refunds: grain: refund_id must_preaggregate_to: order_id customer_history: grain: [customer_id, validity_interval] join: as_of_order_timestamp nulls: null_customer_id: revenue: include active_customers: exclude ``` Then make the verifier check the SQL against this object. Only after that would I invest in increasingly agentic generation. ### Candidate distinct article angles **“The SQL ran. That proves almost nothing.”** Use the synthetic fixture to show a query returning neat monthly numbers while simultaneously failing timezone, fanout and SCD semantics. **“The four contracts AI-generated SQL should have to sign.”** Structure around grain, join cardinality, filter/time semantics, and total/additivity behavior, with source/version/permission provenance as the surrounding control plane. **“Why your AI SQL reviewer should not just be another LLM.”** Contrast verbal self-review with database probes, fixtures, reconciliations and independently executable conditions, supported by NL2SQL-BUGs, SQLens and PV-SQL. [1](https://arxiv.org/abs/2503.11984) **“Semantic layers help. They do not absolve you from testing.”** Pair the +17–23-point semantic-context result with the still-sub-70% benchmark accuracy and harder EntSQL/BEAVER evidence. [3](https://arxiv.org/abs/2604.25149) **“The most dangerous dashboard number may be the Total row.”** Explain additive revenue versus non-additive distinct-customer counts, then connect the lesson to AI generation. **“How to run an AI-SQL pilot that can actually tell you no.”** Focus on analyst baseline, ambiguity tests, permission hard gates, trap suite, full-lifecycle review effort and explicit go/no-go criteria rather than demo success. ### Reusable assets The concrete portable asset produced for this dossier is the [synthetic SQL verification harness](sandbox:/mnt/data/ai_sql_verification_fixture.py). Additional useful assets to derive from it are a dbt unit-test fixture pack, a warehouse-specific join-cardinality macro, a YAML analytical-contract schema, and a pilot scorecard. Those additional files were **not** created in this dossier and should therefore be treated as proposed assets, not supplied artifacts. ### Freshness triggers Re-run this research rather than reusing the conclusions uncritically when any of the following happens: - the candidate model or SQL-agent architecture changes materially; - a benchmark changes dataset/evaluator versions, as Spider 2.0 has done previously; [6](https://github.com/xlang-ai/Spider2) - the dbt version changes unit-test, contract, semantic-model or snapshot behavior; [13](https://docs.getdbt.com/docs/build/unit-tests) - the warehouse changes timezone, security, SQL or authorization semantics; - the organization's metric definitions, refund policy, cancellation policy or SCD implementation change; - a pilot shifts from internal exploratory analytics to external financial, regulatory or high-consequence reporting; - model access begins including personal/sensitive sample values or new jurisdictions, requiring fresh privacy/security review; - materially stronger independent production evidence appears on verified enterprise analytical SQL, especially studies measuring **post-review final correctness and full lifecycle effort**, rather than benchmark SQL-generation accuracy alone. The durable conclusion is therefore narrower than “AI can/cannot write SQL”: > **AI can be useful for producing candidate SQL. Trust should attach only to a versioned analytical specification plus independently verified execution—not to the fluency of the SQL or the explanation around it.** ## Portable citation index 1. [[2503.11984] NL2SQL-BUGs: A Benchmark for Detecting Semantic Errors in NL2SQL Translation](https://arxiv.org/abs/2503.11984) 2. [EntSQL: A Benchmark for Grounding Text-to-SQL in Long-Context Enterprise Knowledge](https://arxiv.org/html/2606.03363v3) 3. [[2604.25149] Semantic Layers for Reliable LLM-Powered Data Analytics: A Paired Benchmark of Accuracy and Hallucination Across Three Frontier Models](https://arxiv.org/abs/2604.25149) 4. [[2508.15276] AmbiSQL: Interactive Ambiguity Detection and Resolution for Text-to-SQL](https://arxiv.org/abs/2508.15276) 5. [ReViSQL: Achieving Human-Level Text-to-SQL](https://arxiv.org/html/2603.20004v2) 6. [GitHub - xlang-ai/Spider2: [ICLR 2025 Oral] Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows · GitHub](https://github.com/xlang-ai/Spider2) 7. [BEAVER: An Enterprise Benchmark for Text-to-SQL](https://arxiv.org/html/2409.02038v3) 8. [Model contracts | dbt Developer Hub](https://docs.getdbt.com/docs/mesh/govern/model-contracts) 9. [Add data tests to your DAG | dbt Developer Hub](https://docs.getdbt.com/docs/build/data-tests) 10. [[2506.04494] SQLens: An End-to-End Framework for Error Detection and Correction in Text-to-SQL](https://arxiv.org/abs/2506.04494) 11. [Add snapshots to your DAG | dbt Developer Hub](https://docs.getdbt.com/docs/build/snapshots) 12. [Timestamp functions  |  BigQuery  |  Google Cloud Documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions) 13. [Unit tests | dbt Developer Hub](https://docs.getdbt.com/docs/build/unit-tests) 14. [Introduction to BigQuery row-level security  |  Google Cloud Documentation](https://cloud.google.com/bigquery/docs/row-level-security-intro) 15. [Regulation - 2016/679 - EN - gdpr - EUR-Lex](https://eur-lex.europa.eu/eli/reg/2016/679/oj/eng) 16. [Text-to-SQL Benchmarks are Broken: An In-Depth Analysis ...](https://www.vldb.org/cidrdb/2026/text-to-sql-benchmarks-are-broken-an-in-depth-analysis-of-annotation-errors.html?utm_source=chatgpt.com) 17. [[2604.17653] PV-SQL: Synergizing Database Probing and Rule-based Verification for Text-to-SQL Agents](https://arxiv.org/abs/2604.17653) ## Import verification and asset availability Captured from genuine ChatGPT Deep Research, research snapshot 2026-10-10; completed in 10 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.