Why 90% Accuracy in Text-to-SQL is 100% Useless

I’ve been working within the Analytics house for over 20 years. Again then, it was not referred to as “analytics”, it was “Enterprise Intelligence” and even “Resolution Assist Programs” in older occasions. The phrases change, from knowledge warehouses to Large Knowledge, to lakehouses, and now with AI, the essence and the everlasting promise of self-service Analytics stays the identical: extracting fact from knowledge to empower customers with out counting on somebody from the info staff. AI with out people within the loop? That sounds controversial.

With the appearance of Massive Language Fashions (LLMs), one use case I discover fascinating is creating conversational interfaces to speak with databases (Textual content-to-SQL). The potential right here is immense, promising to democratize knowledge entry throughout organizations.

Nevertheless, for this particular use case, the answer must be binary. It both works or it doesn’t.

An accuracy of 80% and even 90% is, sadly, not sufficient. Giving your end-users an AI analytical software that hallucinates tables or misinterprets filters isn’t any joke. You can’t compromise on accuracy as a result of it instantly erodes belief. And what occurs when a system loses belief? It is not going to be used. Adoption will decline, with out forgetting the catastrophic danger of enterprise choices being made primarily based on the fallacious knowledge.

The Complexity of the RAG Pipeline

I began my analysis on this matter over one yr and a half in the past and it rapidly grew to become clear that orchestrating a strong Textual content-to-SQL RAG (Retrieval-Augmented Era) software isn’t trivial. You want a number of parts in your pipeline, working in good concord:

• An intent classifier to detect the purpose of the query.
• A vector database to retailer extra context (like enterprise definitions) that the language fashions want.
• An embeddings mannequin to vectorize this extra information.
• A retrieval mechanism for the saved knowledge.
• Entry to the database.
• The power to generate SQL within the particular dialect of the database.
• And the flexibility to consider the outcomes.

This final half, analysis, I imagine is commonly omitted or handled as an afterthought, however it’s maybe probably the most essential element for making certain the reliability wanted in an enterprise setting.

BigQuery: A Case Research in Native AI Integration

Managing this advanced pipeline typically requires integrating a number of platforms. I used to be just lately impressed by how BigQuery has launched the merger of Analytics and Generative AI natively of their platform.

You will have the flexibility to work along with your SQL within the BigQuery IDE and use Gen AI instantly with out going to a different platform or product. For instance: you possibly can question the database and the retrieved outcomes may be instantly despatched to Gemini (or by means of Vertex you may also add different fashions). You should use Gemini to categorise intent, create embeddings and retailer them in BigQuery’s vector database capabilities, do a semantic search, and generate SQL.

All of that with just one platform, with out the trouble of managing a number of subscriptions.

In fact, like every little thing in life, it has its drawbacks.

One of many fundamental cons is that BigQuery may not be the most cost effective database, and I’ve heard tales of startups the place a single fallacious question can drain your bank card. It has not occurred to me, however I can relate to how this will occur. One other con could be that you simply get totally locked-in with Google. Possibly that’s not a nasty factor; the identical manner we’re all locked in with Gmail. Maybe sooner or later, AI might be a commodity, the best way emails are actually.

One other downside is the dearth of granular traceability of the price of the tokens and a type of “mock LLM” for improvement; you don’t want to essentially use the actual costly LLM at your improvement stage.

If you’re okay with the cons above, you get an impressive product that mixes a number of instruments into one single cloud platform which might deal with huge knowledge massively.

I’ve created the next repo which was a part of the Kaggle hackathon, the place I explored these BigQuery native capabilities additional. For more information please go to the repo right here:

https://github.com/garyzava/bigq-ethereum-rag

The Lacking Piece: Rigorous Analysis

Now, going again to eval frameworks. Platforms like BigQuery simplify the structure, however they don’t mechanically clear up the accuracy drawback. I see a number of options on the market, however most of them lack strong analysis capabilities.

If we settle for that Textual content-to-SQL should be binary (appropriate or incorrect), we want analysis methods that replicate the messy actuality of enterprise knowledge, not the pristine environments of educational or demo datasets.

Evaluating a Textual content-to-SQL system is notoriously tough as a result of declarative nature of SQL and the way advanced your database schema is. Does it have 1000’s of tables? Are these tables nicely documented? Most likely not. Are naming conventions constant throughout all tables?. Two queries can look utterly totally different syntactically (e.g., totally different be part of orders, aliasing, or use of CTEs) but produce similar outcomes.

To really benchmark your RAG software throughout improvement and in manufacturing, you have to use the appropriate metrics.

Metrics That Matter

Going again to the promise of self-service BI or analytics, this implies the end-user is relying 100% on themselves; sadly, there isn’t a human-in-the-loop or knowledge professional to validate the outcomes. Due to this, we have to set up an explainable AI or analysis framework with a set of metrics to measure the standard of the generated SQL.

1. The Shift to Execution Accuracy (EX): Early benchmarks relied on Precise Match (EM), which in contrast the expected SQL string to the bottom fact. This was deeply flawed, because it penalized legitimate syntactic variations. The fashionable commonplace is Execution Accuracy (EX). This metric executes each the expected SQL and the “Gold” (floor fact) SQL in opposition to the precise database and compares the returned consequence units. This accurately validates queries no matter how they’re written.
2. Centered Analysis: In enterprise contexts, a question would possibly return further, non-essential columns (e.g., an ID column used for a be part of). Strict execution accuracy would possibly mark this as a failure. “Execution-based targeted analysis” permits for a extra nuanced comparability, checking if the goal columns and values are appropriate, whereas being extra lenient on extraneous knowledge or row ordering.
3. The “Smooth-F1” Metric: To mitigate the binary nature of Execution Accuracy (the place one fallacious cell fails the whole check), Smooth-F1 is more and more used. This metric gives partial credit score by calculating the overlap between the expected and gold outcomes. If a question returns 99 out of 100 appropriate rows, Smooth-F1 displays excessive efficiency, whereas EX would return 0. That is essential for debugging.
4. LLM-as-a-Decide: Generally execution is not possible (e.g., lacking personal knowledge, atmosphere errors). In these circumstances, a complicated LLM may be prompted to match the semantic logic of the expected SQL in opposition to the Gold SQL. Whereas much less goal than execution, it correlates extremely with human judgment.

Spider 2.0: The Enterprise Actuality Verify

At the moment there are three outstanding analysis frameworks: Spider 2.0, BIRD (BIg Bench for LaRge-scale Database Grounded Textual content-to-SQL) and SynSQL (primarily based on artificial knowledge). Nevertheless, the business has been affected by a false sense of safety created by outdated benchmarks. For years, the business relied on Spider 1.0. It targeted on small, clear SQLite databases (averaging fewer than 10 tables). Fashions have been attaining 90%+ accuracy, main many to imagine the issue was “solved.”

The framework that I all the time emphasize, which includes these trendy metrics and actually assessments enterprise readiness, is Spider 2.0.

Spider 2.0 (launched at the side of ICLR 2025) is a paradigm shift, designed to deal with this “actuality hole” by introducing the complexities that break LLMs in manufacturing:

1. Large Scale: Enterprise schemas are enormous. Spider 2.0 databases common 812 columns, with some exceeding 3,000. This scale typically exceeds the LLM’s context limits, forcing fashions to make use of “Schema Linking” (retrieval) methods simply to determine the related tables earlier than producing SQL.
2. Dialect Variety: Actual corporations use Snowflake, BigQuery, and T-SQL, not simply SQLite. Spider 2.0 enforces dialect range, requiring fashions to grasp particular syntax (e.g., dealing with nested JSON knowledge utilizing UNNEST or FLATTEN).
3. Exterior Information: Enterprise logic (just like the definition of “Churn Charge”) resides in documentation or venture codebases (like DBT), not the schema. Spider 2.0 simulates this by offering exterior recordsdata (Markdown, YAML) the mannequin should learn to floor its reasoning.
4. The Agentic Workflow: Crucially, Spider 2.0 fashions the workflow of a contemporary knowledge engineer. It strikes past static translation, evaluating the mannequin’s means to discover the file system, learn documentation, work together with reside database situations, and debug errors iteratively.

The distinction in issue is stark. Fashions that dominate Spider 1.0 see their success charges drop to 10-20% on the complete Spider 2.0 benchmark, highlighting the deficiencies of present LLMs when confronted with real-world complexity.

Conclusion: The Binary Bar for Enterprise Knowledge

The journey from Enterprise Intelligence to AI-driven analytics has been marked by rising abstraction, however the basic requirement for knowledge integrity stays unchanged. Whereas the promise of Textual content-to-SQL is nearer than ever, we should resist the attract of excessive scores on outdated benchmarks.

Reaching 90% accuracy is likely to be academically fascinating, however within the enterprise, it’s industrially ineffective. The bar is binary: it really works or it breaks belief.

As platforms like BigQuery simplify the combination of AI and knowledge, it’s crucial that we concurrently undertake refined analysis methodologies and rigorous benchmarks like Spider 2.0. Solely by testing in opposition to the messy actuality of enterprise knowledge can we develop Textual content-to-SQL functions dependable sufficient to wager the enterprise on.

Till subsequent time, I hope you may have discovered this matter as fascinating as I do.

Additional Studying

Spider 2.0: Evaluating Language Fashions on Actual-World Enterprise Textual content-to-SQL Workflows Authors: Fangyu Lei, Jixuan Chen, Yuxiao Ye, et al. Revealed: arXiv (Nov 2024), Accepted to ICLR 2025 (Oral). Hyperlink: https://arxiv.org/abs/2411.07763

Actually, like something with AI these days, this dialog doesn’t have to finish right here. I’d love to listen to your inputs and perspective at www.gyza.org