Why Current Text-to-SQL Benchmarks Fail Real-World Data Stores
Any text-to-SQL benchmark should address difficulties of real-world data stores
I argue that existing benchmarks for Text-to-SQL models are misleading because they rely on simplified datasets that ignore the complexity of real-world data stores. True evaluation must account for messy schemas, ambiguous natural language, and the intricate constraints found in actual production environments to ensure these tools are genuinely useful.
If you think you can do real-world Text-to-SQL, you are likely testing against a fantasy version of data that does not exist in production.
- fivetenpen
The biggest issue with business users (who don’t know SQL) writing SQL with an LLM is that there is no one to validate that query and now that business user will treat that LLM response as canon to share in meetings, presentations, and with clients. The LLM may have forgotten a filter, used the wrong definition of revenue, or misunderstood the user’s intent by writing a query that answers their question in the most literal way.
That is the crux of the issue. No amount of semantic layers and context will help this until an LLM can read the user’s mind to remove ambiguity in the prompt.
I see most of the benefits of LLMs to be used by analysts who know SQL to work more productivly.
- data-ottawa
SQL generation is quite good, 90% of the problem is data quality.
The best thing you can do for your data agent is build a clean frontroom around your specific desired use cases -- with very clear documentation and obvious idiomatic join patterns built on clear marts. It is hard work, but I've been doing this a while and this is the only way.
A full top down rebuild is rarely feasible and can take years. What I suggest is focusing on rebuilding your base layers and introducing a versioned schema/model approach (to isolate breaking changes). Even with the data/compute redundancy it introduces, it is so much easier to move the sale report from customers_v1 to customers_v2 than it is to evaluate what happens when you remove the salesforce id as primary key from customers into all the downstream dependencies.
- programmertote
Interesting... at my current job, my team and I are solving this problem of how to make sure LLM understand our SQL data warehouse to answer analytical questions for clients. We have a 20-year old database with rotting schema as the blog post describes. So we had to rebuild the database in a way that is well structured and governed. Once that hard work is done, we slap dbt models on top of business metrics with model YAML files (and some common MD files) carrying a lot of semantic and metadata info for them.
Then our software engineering team ingest the dbt models (we have to tactically create dbt models; that is, always think "what would make LLM hallucinate less" as we are implementing them) and info from the semantic layer to build context for the LLM, and use that to answer analytical questions. So far, it's been promising. The accuracy isn't zero like the blog's author suggested though. We have built like 30 metrics in dbt and semantic layer in the last quarter, and asked the research and analytics teams to do internal testing on the LLM app. I will find out how accurate this approach is from the feedback soon.