SQL Accuracy vs. Analytics Accuracy: Why a Valid Query Can Still Answer the Wrong Question

The dashboard number ran clean. It passed every check the tooling offers: the SQL parsed, the columns resolved, the query executed, the result set came back non-empty. Then finance reported a different figure for the same period, and the argument started.

The tension is older than any dashboard. A query can be valid SQL and still answer a different question than the one asked, and the database engine will not tell you. Brass and Goldberg documented the split in what remains the canonical taxonomy of SQL semantic errors: a syntactic error means the character string is not a valid query, while a semantic error means a legal query was entered that does not produce the intended results, and their central finding is that database management systems execute such queries without any warning (Brass & Goldberg). Current text-to-SQL evaluation research restates the same two-predicate test, requiring a query to be both syntactically valid against the database and semantically faithful to the question's intent (ROSE, ACL 2026).

SQL accuracy is the property judged against the database: the statement is legal, the objects exist, the types line up, the query runs. Analytics accuracy is the property judged against the business question: the result answers what was actually asked, under the definitions the organization has agreed to. A syntactically correct query wrong answer is not an edge case; it is the dominant failure mode, and the checks that catch it are not the checks most teams run.

This article separates the two axes, names the points where they diverge, maps each validation check to the axis it actually validates, and describes the minimum governed substrate that makes analytics-accuracy failures visible before a decision depends on them.

What is the difference between SQL accuracy and analytics accuracy?

The two axes answer different questions, and only one of them is machine-checkable today. SQL accuracy asks whether the statement is valid and executable against the database. Analytics accuracy asks whether the result answers the business question under the organization's agreed definitions.

Brass and Goldberg's taxonomy draws the line precisely: a syntactic error is a string that is not a valid SQL query, while a semantic error is a legal query that does not produce the intended results and is therefore incorrect for the given task (Brass & Goldberg). The first is caught by the parser and the catalog. The second is caught by nothing in the engine, which is why the paper's finding that systems execute such queries without warning still matters two decades later.

The asymmetry is structural, not a tooling gap. Execution accuracy, the metric that dominates text-to-SQL evaluation, is computed by running the predicted and ground-truth queries and comparing result sets, with failed predictions counted as incorrect. The toolkit that documents this method also records its limits: subtle SQL semantics, inconsistent implementations, and multiple valid formulations of the same question all escape an exact-match test (VLDB Text-to-SQL Evaluation Toolkit). A query can score as correct under that metric while answering a question nobody asked.

Intent-centered evaluation exists because execution accuracy is insufficient. ROSE scores a prediction 1 only if it executes, is judged semantically acceptable by an independent SQL Prover that sees the question and database but not the gold query, and survives an adversarial refutation step (ROSE, ACL 2026). That cascade is the research community's admission that the second axis needs its own machinery.

Key Takeaway SQL accuracy: the statement is valid and executable against the database. Analytics accuracy: the result answers the business question under the organization's agreed definitions. The engine enforces the first and is silent on the second.

Why can a syntactically correct query still answer the wrong question?

Because the failure classes that produce wrong answers are overwhelmingly semantic, and the engine has no opinion about them. An analysis of in-context-learning text-to-SQL runs found that syntax errors cause 18.8% of execution failures while schema errors cause 81.2%, with projection errors at roughly 40% of semantic errors and incorrect table selection at 35.0% on Spider and 22.4% on BIRD (A Study of In-Context-Learning-Based Text-to-SQL Errors).

A 2026 error taxonomy over 200 manually reviewed failures sharpens the picture. Wrong join path accounts for 38%, missing table 24%, wrong condition 21%, aggregation error 11%, and other 6% (SchemaScope, SURGeLLM 2026). Every one of those categories can produce a query that parses, resolves, and returns rows. The model selects a syntactically plausible but semantically incorrect sequence of joins, typically following a shorter but wrong path through the schema graph.

The same study documents where the difficulty concentrates. Performance holds above 78% for all tested models at join-hop depth 2 or less, drops 10 to 17 percentage points at depth 3, and falls to near-random at depth 5 and beyond. GPT-4o falls from 71% at depth 3 to 54% at depth 4, a single-hop loss of 17 points (SchemaScope, SURGeLLM 2026). Spider and BIRD both place more than 60% of their queries at depth 2 or less, which the authors say systematically under-represents the enterprise regime.

One vendor's evaluation of more than 50,000 generated queries reports schema hallucination as the most common error type and join-path errors in one of every four failed queries, with syntax errors the least common (usedatabrain). That source publishes no full percentages and sells a semantic-layer product, so treat the ranking as a lead rather than a measurement. The direction, however, matches the peer-reviewed taxonomies.

The practical consequence: a team that validates syntax and schema coverage has validated the smaller half of the problem. The larger half produces a number that looks fine.

Where do the two axes diverge — joins, grain, filters, time boundaries, freshness?

The divergence points are the places where a legal query encodes a different business meaning than the one intended. Join fan-out is the clearest. PostgreSQL defines the mechanism exactly: for each row of the first table, the joined table has a row for each row in the second table that satisfies the join condition, and a cross join of tables with N and M rows produces N times M rows (PostgreSQL Documentation). Join an order header to its order lines and the header's amount repeats once per line.

Grain is the level of detail a row represents. Fan-out changes the grain of the result without changing a single character of the SQL, which is why a revenue total can be inflated by a factor equal to the average line count per order and still pass every syntax and schema check.

-- Fan-out: order_amount repeats once per order line
  SELECT SUM(o.order_amount) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id;
  
  -- Corrected: aggregate at order grain before joining
  SELECT SUM(o.order_amount) AS revenue
  FROM orders o
  JOIN (
    SELECT DISTINCT order_id FROM order_items
  ) i ON i.order_id = o.order_id;
  

The corrected version uses a distinct set of order identifiers, so each order contributes its amount once. The same class of error appears in filters, where a required business scope such as closed status or retail customer type is omitted, and in time boundaries, where a month is defined by calendar start rather than by the close date that finance uses.

Freshness belongs to the same family. A query that reads a table before its upstream load completes returns a legal, executable, and stale answer. Nothing in the statement is wrong; the timing is. The cost dimension compounds it: a filter that fails to prune partitions scans more data than the question requires, which is a governance and budget failure rather than a correctness one, but it originates in the same gap between what the query says and what the business meant.

A semantic layer treats entities as the join keys of the model, the traversal paths between semantic models, and dimensions as the ways metrics are grouped or sliced (dbt Developer Hub, About MetricFlow). The governed layer compiles the query from those definitions rather than generating it freehand, which is what removes the fan-out decision from the model's discretion (dbt Labs engineering).

Which checks validate SQL accuracy, and which validate analytics accuracy?

Most validation stacks cover one axis thoroughly and the other barely at all. A documented six-layer validation chain for text-to-SQL pipelines runs intent classification, a statement-type allowlist with default deny, AST structural validation for unauthorized tables and hallucinated columns, catalog-aware semantic binding with a compile or dry-run gate, AST policy injection that mechanically adds tenant and date predicates, and database-level enforcement through read-only grants and native row-level security (Validation-Gates Survey). It is a design pattern documented in a survey, not a standard's text, but it reflects how production pipelines are actually assembled.

The first five layers are SQL-accuracy gates. They catch a query that cannot run or that violates access policy. They do not catch a query that runs correctly and computes the wrong number, because the wrong number is not a property of the statement.

Warning Layers 1 through 3 and 5 are SQL-accuracy gates. Metric definition, effective dates, and source freshness are caught by none of them.

Snowflake's own documentation describes what happens in the absence of a governed definition: without a semantic view, dozens of inconsistent calculations can exist across reports and applications, often with incorrect aggregation methods leading to erroneous results (Snowflake Documentation). That is an analytics-accuracy failure that no AST check will ever flag.

How large is the gap between benchmark accuracy and production accuracy?

The gap is large, and the benchmarks themselves are less reliable than their headline numbers suggest. The Spider 2.0 evaluation of enterprise text-to-SQL workflows reports that the best o1-preview code agent framework solves 21.3% of tasks, against 91.2% execution accuracy on Spider 1.0 and 73.0% on BIRD for the same model family (Spider 2.0, Lei et al.). Only the benchmark's enterprise realism changes between those figures.

The BEAVER enterprise benchmark, built from real query logs in private warehouses, reports that agentic frameworks using GPT-5.2 reach 10.8% accuracy, rising to 30.1% when given all subtask annotations as oracle hints (BEAVER, Chen et al.). Its databases average 101.5 tables and 869.4 columns, against 6.8 tables and 72.5 columns in BIRD. Scale, not model quality, drives the collapse.

The ground truth underneath those numbers is itself contested. A 2026 audit found annotation error rates of 52.8% on BIRD Mini-Dev and 66.1% on Spider 2.0-Snow's open-sourced gold queries, with the largest error pattern being a mismatch between the SQL's semantics and the question's intent (Text-to-SQL Benchmarks are Broken, Jin et al.). BIRD's own leaderboard shows a human baseline of 92.96 execution accuracy against a top leaderboard entry of 82.95 (BIRD Leaderboard). The metric everyone reports is measured against a reference that is frequently wrong.

The optimistic counterpoint comes from a vendor benchmark. dbt Labs reports text-to-SQL at 90.0% and its semantic layer at 98.2% for one model, and 84.1% against 100.0% for another, on an 11-question insurance dataset run 20 times each (dbt Labs 2026 Benchmark). The same benchmark shows text-to-SQL improving from 32.7% to 64.5% between 2023 and 2026, and the semantic layer scoring 0% on questions requiring too many entity hops in both years. dbt Labs sells the semantic layer, the dataset has 15 tables, and the text-to-SQL arm received the entire schema as context, which the authors note is impractical at larger scale.

The two bodies of evidence disagree because they measure different regimes. The academic benchmarks test unmodeled enterprise schemas at scale; the vendor benchmark tests a well-modeled small schema under controlled conditions. Both are correct about their own setting. No source in this set provides a controlled, same-model comparison of one system on both an academic benchmark and that organization's production traffic, so no single production-gap percentage can be claimed.

What governance substrate makes analytics-accuracy failures visible before a decision?

The substrate is a set of definitions and constraints that exist independently of any query, so a wrong answer fails a check rather than reaching a dashboard. The federal statistical framework separates the two properties formally: accuracy measures the closeness of an estimate to its true value, while coherence is the ability to maintain common definitions, classifications, and methodological processes and to remain comparable over time (FCSM-20-04). Comparability over time is a subset of coherence, not of accuracy. A number can be accurate for its period and incoherent with every other period.

The ISO/IEC 25012 data quality model treats accuracy as one inherent characteristic among several, alongside completeness, consistency, credibility, and currentness, with a separate class of system-dependent characteristics describing what an information system achieves and preserves (ISO/IEC 25012; characteristics as applied in Gualo et al.). Accuracy is not the whole of quality, and a governance program built only on accuracy checks will miss the coherence failures that produce the finance disagreement.

A 2026 study measuring governed versus ungoverned analytics accuracy found that a governed pipeline reached 0.951 value-based result accuracy against 0.500 for a semantic-only baseline and 0.000 for both direct LLM and schema-RAG conditions on a 100-question benchmark, replicating on real NHTSA data at 1.00 against 0.68, 0.65, and 0.62 (GROUND). The most instructive result is the ablation: the semantic-only condition, given exact metric and dimension definitions, achieved perfect metric fidelity and correct grain but still leaked data across tenant boundaries on 35% of questions, while ungoverned systems violated row-level security on 53% to 78%. Metric definitions alone do not produce governance.

The substrate that makes failures visible therefore has four parts: a named definition per metric with its aggregation and grain, an explicit join path between entities, a required filter set per metric, and an enforced access policy. The first three catch wrong numbers. The fourth catches the leak that correct numbers do not prevent.

How does a semantic layer ground metric definitions so answers become testable?

A semantic layer turns a metric from a query someone wrote into a named, versioned contract that a query is compiled against. MetricFlow defines metrics as functions that combine simple metrics, constraints, or other mathematical functions into quantitative indicators, with entities as the join keys and dimensions as the grouping axes (dbt Developer Hub, About MetricFlow). Because the definition is an object rather than a statement, it can be reviewed, versioned, and tested.

The compilation step is what makes answers testable. The governed layer constructs SQL at query time from the semantic model and metric configurations, emits the semantic graph as a manifest, and exposes a compile parameter so the generated SQL can be inspected before it runs (dbt Labs engineering). A reviewer can read the compiled query and check its grain, its filters, and its join path against the definition, which is not possible when the query is generated fresh each time.

The independent framing of the layer is consistent across vendors: it is the governed layer between the warehouse and the consuming tools, defining metrics, dimensions, join paths, and access rules once so that every consumer, whether a dashboard, an embedded app, a spreadsheet, or an AI agent, works from the same definitions and returns the same numbers (Cube). Snowflake's documentation makes the same claim from the platform side, describing a single authoritative definition with proper aggregation behavior (Snowflake Documentation).

The coverage limit is real and should be stated plainly. A semantic layer can only answer questions that fall within the scope of what has been modeled, and the dbt Labs benchmark shows its accuracy at 0% on questions requiring more entity hops than the model supports, in both 2023 and 2026 (dbt Labs 2026 Benchmark). The failure mode is different in kind from an ungoverned system: the layer reports that it cannot answer rather than returning a plausible wrong number. For a metric that reaches a board deck or an auditor, that distinction is the whole point.

How we apply this at Singdata, the publisher of this guide. Our platform keeps each metric definition in a Semantic View, so a change made there propagates to downstream tables instead of being hunted table by table. It does not decide whether the definition matches your business intent; that judgement stays with your team.

Which check belongs to which axis — a self-selection summary

Use the axis to decide which check to build first. If a query fails to run, the SQL-accuracy gates catch it. If a query runs and the number is disputed, only the analytics-accuracy controls will find the cause.

The self-selection rule is simple. A check that inspects the statement validates SQL accuracy. A check that compares the result to an agreed definition validates analytics accuracy. Teams that have built the first column and not the second have built a system that reliably executes queries and cannot tell whether the answers are right.

The order matters less than the coverage. A pipeline with strong SQL-accuracy gates and no definition layer will pass every automated check and still produce the finance disagreement that started the investigation. A pipeline with definitions but no access enforcement will produce correct numbers for the wrong audience, which the GROUND ablation shows is a distinct failure that metric definitions do not fix (GROUND).

Conclusion

A valid query and a correct answer are two different properties, and the database only enforces one of them. SQL accuracy is judged against the database: the statement is legal, the objects resolve, the query runs. Analytics accuracy is judged against the business question: the result answers what was asked, under definitions the organization has agreed to and can test.

The evidence for treating them separately is consistent across independent lines. Semantic failures dominate execution failures, with schema errors causing 81.2% against 18.8% for syntax (ICL Error Study), and wrong join paths leading the failure taxonomy at 38% (SchemaScope, SURGeLLM 2026). Enterprise benchmarks report accuracy in the low double digits where academic benchmarks report the low nineties (Spider 2.0; BEAVER). The benchmarks' own gold answers carry annotation error rates above 50% in one audit (Jin et al.). And governance is a separate axis from metric correctness, since a system with perfect metric fidelity still leaked across tenant boundaries on 35% of questions (GROUND).

The next step is a mapping exercise, not a purchase. Take the checks currently running in your pipeline and label each one with the axis it validates. The SQL-accuracy column will be populated. The analytics-accuracy column will show which definitions, grain rules, effective dates, and freshness constraints do not exist yet, and those gaps are where the next finance disagreement is already waiting.