If You Think You Can Do Real-World Text-to-SQL – Communications of the ACM

Open original

The article examines why translating natural language into SQL for live data warehouses remains a tough problem. It traces the rise of ad-hoc queries in 1990s‐era historical data stores and shows how schema complexity and performance needs outpace current text-to-SQL systems.

  • Benchmarks like Spider (max 200 tables, single database per task) don't reflect real enterprise warehouses with hundreds of tables and thousands of columns spanning dozens of schemas
  • Production metadata is messy: cryptic/inconsistent column naming, typos, abbreviations, and mixed-language query logs undermine model performance
  • Vendor-specific SQL extensions (Oracle's LISTAGG, PostgreSQL's JSONB operators) and user-defined types routinely break academic text-to-SQL models
  • The authors call for shared benchmarks built from anonymized real industry schemas/workloads and argue LLMs need integration with metadata services and human feedback loops before replacing analysts in critical settings

Real-world text-to-SQL faces challenges far beyond converting simple questions into SELECT–FROM–WHERE blocks. Data warehouses built in the 1990s for sales, inventory and customer histories now house hundreds of tables, some with thousands of columns. Ad-hoc queries routinely involve multiway joins, nested aggregations and vendor-specific SQL extensions. Michael Stonebraker and Peter Baile Chen point out that systems honed on benchmarks like Spider (at most 200 tables, one database per task) break down when confronted with live workloads from finance or healthcare, where schemas span dozens of schemas and queries reference business rules buried in documentation. Benchmarks also ignore the mess in production metadata. Column names may not reflect meaning—“value_date” next to “order_val” and “val_dt” requires context or a data dictionary that text-to-SQL models rarely see. Real query logs include typos, abbreviations, even mixed languages. Stonebraker and Chen note that many academic models collapse when faced with vendor-specific functions (Oracle’s LISTAGG, PostgreSQL’s JSONB operators) or when query semantics rely on user-defined types. They argue for evaluation on live data and real query histories, complete with schema evolution and permission layers. To move forward, the authors call for shared repositories of anonymized schemas and workloads, ideally drawn from industries with high ad-hoc demands—retail analytics, telco call logs or clinical records. They urge researchers to report error rates on full SQL dialects, not just core operators. Finally, Stonebraker and Chen stress that even the latest large language models, no matter how impressive on Spider or WikiSQL, require explicit integration with metadata services and user feedback loops before they can replace human analysts in mission-critical environments.

Ask about this article