Ověřování SQL připraveného pomocí AI

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

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 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

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

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

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

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

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

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

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

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

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

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:

WHERE cancelled_at IS NULL

and:

WHERE cancelled_at IS NULL OR cancelled_at > paid_at

implement different cancellation policies.

Likewise, these two refund strategies answer different questions:

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

versus:

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

A verifier therefore needs a requirement such as:

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

Therefore:

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:

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:

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:

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:

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

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:

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:

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

The verifier therefore asserts:

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

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

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

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

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

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

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

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

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

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:

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 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

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:

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:

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:

2026-01-31 23:30 UTC

which is:

2026-02-01 00:30 Europe/Prague

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

Refund 2004 occurs at:

2026-02-28 23:30 UTC

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

Customer 1 becomes inactive at:

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:

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:

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:

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:

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 [original file unavailable]

It contains:

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

A candidate query can be checked with:

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

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	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	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	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	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	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	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	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	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	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	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	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	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	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	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	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	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

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

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 Yet the 2026 annotation audit found substantial errors in prominent benchmark subsets and showed that corrections can reorder systems materially. 3 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

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 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

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

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

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:

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:

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

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 [original file unavailable], 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 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
2. Semantic Evaluation for Text-to-SQL with Distilled Test Suites
3. [2601.08778] Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards
4. PICARD: Parsing Incrementally for Constrained Auto- …
5. About MetricFlow | dbt Developer Hub
6. Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows
7. xlang-ai/Spider2: [ICLR 2025 Oral] Spider 2.0: Evaluating …
8. Benchmarking Text-to-SQL under Role-Based Access Control
9. AmbiSQL: Interactive Ambiguity Detection and Resolution for Text-to-SQL
10. About data tests property | dbt Developer Hub
11. Timestamp functions | BigQuery
12. Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3 - Kimball Group
13. EvoSchema: Towards Text-to-SQL Robustness Against Schema Evolution
14. Introduction to BigQuery row-level security
15. Run a query | BigQuery
16. Model contracts | dbt Developer Hub
17. Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs
18. About dbt freshness command | dbt Developer Hub
19. Best practices for using policy tags in BigQuery
20. Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs

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.

AI-Assisted SQL With Verification: Evidence Dossier

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

Executive answer and uncertainty

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

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

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

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

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

There are three important uncertainties.

First, benchmark accuracy cannot be translated into a probability that a query on a particular company’s warehouse is correct. Spider, BIRD, Spider 2.0 and BEAVER differ substantially in schema scale, provenance, context supplied to systems, question construction and evaluation methodology. 3

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

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

What the research actually establishes

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

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

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

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

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

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

Why execution match is not semantic proof

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

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

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

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

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

Gold labels themselves are a source of uncertainty

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

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

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

Robustness and transfer remain separate from nominal accuracy

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

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

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

Verification architecture for grain, joins, filters and totals

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

The analytical contract comes before SQL

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

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

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

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

Grain checks should be explicit

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

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

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

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

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

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

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

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

Join verification needs both metadata and executable counterexamples

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

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

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

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

Filters need boundary tests, not visual inspection

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

event_at >= period_start
AND event_at < next_period_start

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

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

Slowly changing dimensions must be point-in-time joins

dbt snapshots explicitly implement Type-2 slowly changing dimensions and represent validity with dbt_valid_from and dbt_valid_to. 15

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

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

assuming the business contract uses half-open intervals.

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

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

Totals require measure-type awareness

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

Revenue under a stable event-based definition is additive:

NetRevenue_{Mar-May}
=
NetRevenue_{Mar}
+
NetRevenue_{Apr}
+
NetRevenue_{May}

A distinct-customer count is not:

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

when a customer can appear in more than one month.

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

Source/version fidelity is a separate acceptance criterion

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

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

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

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

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

Permissions should be enforced outside the model

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

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

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

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

Worked synthetic example and portable verification asset

Business request:

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

That request is not sufficiently defined to generate final SQL.

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

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

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

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

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

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

Timezone: Europe/Prague.

Revenue recognition:

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

with:

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

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

Active customers:

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

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

Customer-history interval: [valid_from, valid_to).

The key synthetic records are:

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

The independently derived expected monthly result is:

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

The revenue reconciliation is visible:

GrossSales = 150+80+40+60+25 = €355

Refunds = 20+10+15 = €45

Cancellations = €60

Net = 355-45-60 = €250

and:

150+150-50 = €250

The monthly active-customer counts sum to:

1+2+1=4

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

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

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

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

The correct lifetime amount is:

€150 - €30 = €120

but the direct join calculates:

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

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

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

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

Portable test asset: Download the PostgreSQL verification fixture [original file unavailable].

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

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

Reproducible evaluation protocol and analyst baseline

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

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

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

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

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

Representative task family

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

A useful suite should contain variants covering:

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

This follows the logic behind test-suite accuracy and formal counterexample search: a semantic test suite should deliberately create instances on which competing interpretations diverge. 1

Acceptance should be conjunctive

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

One possible definition is:

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

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

Report the components separately:

Execution correctness

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

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

Verified result correctness

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

Task-level repeated-run reliability

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

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

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

Ambiguity behavior

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

Source/version fidelity

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

Permission compliance

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

Failure recovery

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

Latency and cost

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

What the model may do versus what should remain deterministic

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

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

Counterevidence, limitations and conditions where the advice fails

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Claim-to-source evidence ledger

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

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

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

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

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

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

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

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

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

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

Portable citation index

1. Semantic Evaluation for Text-to-SQL with Distilled Test Suites - ACL Anthology
2. Joins | dbt Developer Hub
3. [2305.03111] Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs
4. cs.sfu.ca
5. Spider: A Large-Scale Human-Labeled Dataset for Complex and Cross-Domain Semantic Parsing and Text-to-SQL Task
6. Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows
7. BEAVER: An Enterprise Benchmark for Text-to-SQL
8. Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards
9. Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards
10. NL2SQL-BUGs: A Benchmark for Detecting Semantic Errors in NL2SQL Translation
11. [2301.08881] Dr.Spider: A Diagnostic Evaluation Benchmark towards Text-to-SQL Robustness
12. KaggleDBQA: Realistic Evaluation of Text-to-SQL Parsers - ACL Anthology
13. Semantic models | dbt Developer Hub
14. About data tests property | dbt Developer Hub
15. Add snapshots to your DAG | dbt Developer Hub
16. Semantic models | dbt Developer Hub
17. grants | dbt Developer Hub
18. Regulation - 2016/679 - EN - gdpr - EUR-Lex
19. Model contracts | dbt Developer Hub
20. Validations | dbt Developer Hub
21. Evaluating Text-to-SQL Evaluation with Formal Verification
22. Text-to-SQL Empowered by Large Language Models: A Benchmark Evaluation
23. Add data tests to your DAG | dbt Developer Hub

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.

Evidence Dossier: AI-Assisted SQL With Verification

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

Decision answer and uncertainty

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

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

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

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

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

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

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

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

Evidence synthesis around the decision

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Concrete verification workflow for a small data team

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

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

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

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

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

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

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

Worked synthetic example and portable test asset

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

The complete portable package contains:

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

Download the synthetic SQL verification fixture and runnable harness [original file unavailable]

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

The worked metric contract is:

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

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

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

and

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

Customer status is looked up using the SCD interval

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

with an open-ended final version allowed.

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

The verified result is:

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

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

100+75+100+10=285

and February refund events of:

20+10=30

therefore:

285-30=\boxed{255}.

The intentionally wrong query is structurally recognizable:

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

It executes successfully.

It returns:

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

The database has not failed. The query has.

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

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

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

The check that exposes the fanout is intentionally boring:

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

A second invariant checks SCD cardinality under the proper predicate:

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

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

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

\sum(qp-d)=555

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

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

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

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

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

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

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

The actual harness run completed all assertions:

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

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

Alternatives, counterevidence and conditions where the advice fails

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

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

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

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

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

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

The proposed workflow also has limitations.

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

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

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

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

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

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

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

Claim-to-source dossier

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

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

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

Candidate article angles, reusable assets and freshness triggers

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

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

(120,255,45)

for refund-event accounting versus

(100,270,50)

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

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

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

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

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

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

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

Portable citation index

1. PostgreSQL: Documentation: 18: 7.2. Table Expressions
2. PostgreSQL: Documentation: 18: 9.2. Comparison Functions and Operators
3. Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows
4. BEAVER: An Enterprise Benchmark for Text-to-SQL
5. ReViSQL: Achieving Human-Level Text-to-SQL
6. Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards
7. FLEX: Expert-level False-Less EXecution Metric for Text-to-SQL Benchmark - ACL Anthology
8. Reliable Text-to-SQL with Adaptive Abstention
9. Model contracts | dbt Developer Hub
10. Model versions | dbt Developer Hub
11. Semantic models | dbt Developer Hub
12. PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operators
13. Add snapshots to your DAG | dbt Developer Hub
14. Add data tests to your DAG | dbt Developer Hub
15. Unit tests | dbt Developer Hub
16. PostgreSQL: Documentation: 18: SET TRANSACTION
17. PostgreSQL: Documentation: 18: 9.21. Aggregate Functions
18. Source freshness | dbt Developer Hub

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.

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

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

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

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

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

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

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

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

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

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

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

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:

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

For generated analytical SQL, I would add per-stage observability:

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:

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:

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:

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

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

A fact at time t therefore normally needs a validity join such as:

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.

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:

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

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

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:

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:

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

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

The architecture should therefore be:

AI proposes SQL
       ↓
fixed execution identity / user-delegated constrained identity
       ↓
warehouse authorization
       ↓
read-only query

not:

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

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

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

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:

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

Portable test asset

Download the portable synthetic verification harness [original file unavailable]

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:

python ai_sql_verification_fixture.py --demo good
python ai_sql_verification_fixture.py --demo bad

A candidate SQL file can be tested with:

python ai_sql_verification_fixture.py --candidate candidate.sql

Its required result contract is:

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

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

Use data tests for persistent warehouse invariants:

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

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:

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

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:

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

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

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

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

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

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

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

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

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	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	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	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	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	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	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	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	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	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	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	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	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	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	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	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	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:

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:

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

“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

“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 [original file unavailable].

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
• the dbt version changes unit-test, contract, semantic-model or snapshot behavior; 13
• 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
2. EntSQL: A Benchmark for Grounding Text-to-SQL in Long-Context Enterprise Knowledge
3. [2604.25149] Semantic Layers for Reliable LLM-Powered Data Analytics: A Paired Benchmark of Accuracy and Hallucination Across Three Frontier Models
4. [2508.15276] AmbiSQL: Interactive Ambiguity Detection and Resolution for Text-to-SQL
5. ReViSQL: Achieving Human-Level Text-to-SQL
6. GitHub - xlang-ai/Spider2: [ICLR 2025 Oral] Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows · GitHub
7. BEAVER: An Enterprise Benchmark for Text-to-SQL
8. Model contracts | dbt Developer Hub
9. Add data tests to your DAG | dbt Developer Hub
10. [2506.04494] SQLens: An End-to-End Framework for Error Detection and Correction in Text-to-SQL
11. Add snapshots to your DAG | dbt Developer Hub
12. Timestamp functions  |  BigQuery  |  Google Cloud Documentation
13. Unit tests | dbt Developer Hub
14. Introduction to BigQuery row-level security  |  Google Cloud Documentation
15. Regulation - 2016/679 - EN - gdpr - EUR-Lex
16. Text-to-SQL Benchmarks are Broken: An In-Depth Analysis …
17. [2604.17653] PV-SQL: Synergizing Database Probing and Rule-based Verification for Text-to-SQL Agents

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.