PaperlayerView exact revision on arXiv

Source-linked AI summary

CHESS: Contextual Harnessing for Efficient SQL Synthesis

Shayan Talaei, Mohammadreza Pourreza, Yu-Chen Chang, Azalia Mirhoseini, Amin Saberi

arXiv:2405.16755v3cs.LGcs.AIcs.DB

TL;DR

Text-to-SQL remains challenging because systems must handle large database context, complex schemas, query validity, and ambiguous questions. CHESS addresses these challenges with four specialized agents and achieves strong accuracy across deployment settings, while its schema selection has a documented trade-off on smaller schemas with highly capable models.

  • Problem

    Text-to-SQL must synthesize accurate SQL despite large catalogs and value sets, complex schemas, query-validity requirements, and ambiguous natural-language questions.

  • Method

    CHESS is an LLM-based multi-agent framework using Information Retriever, Schema Selector, Candidate Generator, and Unit Tester agents for retrieval, schema pruning, query generation, and validation.

  • Results

    CHESS achieves 71.10% accuracy on the BIRD test set, within 2% of the leading proprietary method while requiring approximately 83% fewer LLM calls, and reaches 61.5% with open-source models on BIRD development.

  • Takeaways & Limitations

    CHESS provides a configurable text-to-SQL system for complex and industrial-scale databases, including schema pruning that increases accuracy by 2% while reducing token usage ×5.

  • Takeaways & Limitations

    The paper notes that larger real-world databases are still needed for more extensive benchmarking, and Schema Selector can reduce accuracy when schemas are small and LLMs are highly capable.

Abstract

from arXiv · show

Translating natural language questions into SQL queries, known as text-to-SQL, is a long-standing research problem. Effective text-to-SQL synthesis can become very challenging due to (i) the extensive size of database catalogs (descriptions of tables and their columns) and database values, (ii) reasoning over large database schemas, (iii) ensuring the functional validity of the generated queries, and (iv) navigating the ambiguities of natural language questions. We introduce CHESS, a Large Language Model (LLM) based multi-agent framework for efficient and scalable SQL synthesis, comprising four specialized agents, each targeting one of the aforementioned challenges: the Information Retriever (IR) extracts relevant data, the Schema Selector (SS) prunes large schemas, the Candidate Generator (CG) generates high-quality candidates and refines queries iteratively, and the Unit Tester (UT) validates queries through LLM-based natural language unit tests. Our framework offers configurable features that adapt to various deployment constraints, including 1) Supporting industrial-scale databases: leveraging the Schema Selector agent, CHESS efficiently narrows down very large database schemas into manageable sub-schemas, boosting system accuracy by approximately $2\%$ and reducing the number of LLM tokens by $\times 5$. 2) State-of-the-Art privacy-preserving performance: Among the methods using open-source models, CHESS achieves state-of-the-art performance, resulting in a high-performing, privacy-preserving system suitable for industrial deployment. 3) Scalablity with additional compute budget: In settings with high computational budgets, CHESS achieves $71.10\%$ accuracy on the BIRD test set, within $2\%$ of the leading proprietary method, while requiring approximately $83\%$ fewer LLM calls.

1 INTRODUCTION

CHESS frames text-to-SQL as a multi-agent problem involving information retrieval, schema reasoning, query generation, validation, and deployment constraints. Its experiments report strong accuracy and efficiency across industrial-scale, privacy-preserving, and computationally constrained settings.

  • Motivation: Text-to-SQL becomes harder as schemas, database values, and catalogs grow, while ambiguity and missing value matches complicate query formulation.The paper also identifies a substantial human–model accuracy gap and the need for reliable inference-time verification.
  • Deployment and Results: 2% higher accuracy and ×5 lower token usage result when Schema Selector prunes irrelevant columns from schemas exceeding 4,000 columns.The framework is configurable for industrial-scale schemas and different deployment constraints.
  • Deployment and Results: 71.10% BIRD test accuracy comes within 2% of the leading proprietary method while using approximately 83% fewer LLM calls.CHESS also reports state-of-the-art performance among open-source-model methods and maintains high accuracy under computational constraints.
  • CHESS Framework: CHESS combines four specialized agents: Information Retriever, Schema Selector, Candidate Generator, and Unit Tester.The agents retrieve relevant data and context, prune schemas, generate and revise SQL, and select candidates using natural-language unit tests.
  • CHESS Framework: CHESS introduces hierarchical retrieval and natural-language unit-test generation to improve contextual information access and evaluate generated SQL during inference.Its retrieval methods use locality-sensitive hashing, keyword detection, and vector databases, while unit tests distinguish candidate-query semantics.

2 RELATED WORK

Text-to-SQL research has progressed from manually designed templates to transformer-based and LLM-based methods. Recent work increasingly integrates database context and candidate selection to improve SQL generation.

  • Early text-to-SQL systems used custom templates that required significant manual effort.
  • Transformer-based sequence-to-sequence models later supported text-to-SQL as a sequence-generation task.
  • LLM-based approaches introduced zero-shot in-context learning and methods for integrating database values and catalogs into SQL generation.
  • Natural-language generation has also been proposed to enhance text-to-SQL system reliability.
  • Recent methods generate many candidate responses and use selection algorithms to choose among them for text-to-SQL queries.

3 METHODOLOGY

CHESS is a configurable four-agent framework for text-to-SQL that retrieves relevant information, reduces schema complexity, generates and revises queries, and selects candidates through unit tests. Its design adapts these functions to large schemas and deployment constraints, including cases where schema selection can hurt accuracy.

  • Framework overview: CHESS comprises four specialized agents: Information Retriever, Schema Selector, Candidate Generator, and Unit Tester.The framework assigns agents distinct roles and equips them with specialized tools.
  • Information Retriever: The Information Retriever extracts keywords, retrieves similar database entities, and accesses relevant catalog descriptions using syntactic and semantic similarity.Entity retrieval uses keyword extraction and database search, while context retrieval queries catalog descriptions through vector-based retrieval.
  • Schema Selector: The Schema Selector reduces large schemas by filtering columns and selecting relevant tables and columns for the question.Its tools can be used individually or together, with a precision-recall trade-off when selecting schema elements.
  • Deployment adaptation: For schemas exceeding 4,000 columns, Schema Selector substantially reduces irrelevant information, whereas smaller schemas with highly capable LLMs may benefit from omitting it.The paper reports that all four agents become essential for larger schemas, while schema selection can reduce accuracy in smaller-schema settings.
  • Candidate Generator: The Candidate Generator creates SQL queries, executes them, and revises faulty candidates based on execution results such as syntax errors or empty outputs.Candidate generation uses the question, schema, and retrieved context, while revision incorporates the faulty query and issue description.
  • Unit Tester: The Unit Tester generates natural-language tests to distinguish candidate queries and selects the candidate passing the most tests.The unit-test tool generates k tests intended to highlight semantic differences between candidates.

4 EXPERIMENTS AND RESULTS

CHESS is evaluated across standard, privacy-preserving, industrial-scale, and ablation settings, with results showing strong accuracy, efficiency, and schema-handling performance. Its components improve retrieval, schema selection, revision, and unit-test-based candidate selection across datasets and constraints.

  • Industrial-scale schemas: 61% Pass@1 and 63% Pass@5 are achieved on the largest 4,337-column schema after applying the Schema Selector, a 2% improvement over the configuration without schema linking.The Schema Selector improves accuracy while reducing token usage by more than 5× relative to the compared no-schema-linking configuration.
  • Open-source models: CHESS achieves state-of-the-art performance among open-source text-to-SQL methods and supports privacy-preserving on-premise deployment.The open-source configuration is intended for settings where proprietary models are limited by data-privacy requirements.
  • Ablation studies: Selective retrieval outperforms naive context augmentation by 4.76% execution accuracy, while the select tables and revision tools contribute 6.12% and 6.80% improvements, respectively.The ablation evaluates omitted modules and tools on the subsampled development set.
  • Unit tests: Performance improves as the number of natural-language unit tests increases, peaks at 10 tests, and then plateaus as additional tests provide diminishing returns.The unit tester selects the highest-scoring candidate from 20 generated SQL candidates.
  • Schema selection: Schema selection increases precision for selected tables and columns with only a slight recall reduction, producing a compact context focused on relevant information.The agent can narrow a schema to two tables and five columns, including the two columns used in the correct SQL query.

5 CONCLUSION AND LIMITATIONS

CHESS uses four specialized agents to handle complex industrial-scale databases, with schema selection supporting high accuracy under reduced token and call budgets. The paper also identifies the need for larger real-world text-to-SQL benchmarks.

  • CHESS combines Information Retriever, Schema Selector, Candidate Generator, and Unit Tester agents for retrieval, schema pruning, SQL generation, and validation.
  • 71.10% accuracy on the BIRD test set came within 2% of the leading proprietary method while requiring approximately 83% fewer LLM calls.
  • CHESS’s schema selection maintains high accuracy on large industrial-scale schemas while processing less than ×5 tokens.
  • The authors identify a need for an extensive text-to-SQL benchmark that better reflects large-scale database challenges.

A.1 Locality Sensitive Hashing Indexing of Database Values

CHESS retrieves database values efficiently despite mismatches between question keywords and stored values. Its hierarchical approach combines approximate retrieval with more precise similarity filtering.

  • Question keywords may differ from stored database values because of typos, expression variations, or unknown storage formats.
  • The retrieval strategy balances accuracy and efficiency through a hierarchical method instead of comparing every keyword with every database value.
  • Locality Sensitive Hashing indexes unique values during preprocessing and rapidly retrieves the top 10 values similar to each keyword.
  • The method combines embedding-based cosine similarity, threshold filtering, and minimum edit distance to retain one value per keyword and column.
  • 5 seconds versus 5 minutes reduced retrieval time compared with computing edit distance over all unique values on the fly.

A.2 Vector database

CHESS retrieves relevant schema descriptions and preserves structurally important columns while generating and revising candidate queries. These choices address prompt overload and missing-column errors.

  • Including all column descriptions can overwhelm weaker open-source models and lead to incorrect SQL queries.
  • The Information Retriever evaluates question-description similarity with embeddings and stores description vectors in a ChromaDB vector database.
  • Foreign-key and primary-key linking columns are retained regardless of column filtering or selection outputs because they support counting and table joins.
  • The Candidate Generator revises SQL using execution results, while the revise tool can add a missing requested column.

B.1 Fine-tuning Dataset and Model

CHESS fine-tunes an open-source candidate-generation model using structured prompts and context-aware examples. Precise contextual information aligned with gold SQL improves candidate-generation performance.

  • Fine-tuning Dataset and Model: The fine-tuning dataset uses heuristics to address error propagation rather than relying only on correct tables and columns.
  • Fine-tuning Dataset and Model: DeepSeek Coder 34B was fine-tuned with QLoRA using 4-bit quantization and LoRA adapters.
  • Prompt Templates: The prompt templates instruct tools to produce structured outputs for easier parsing by the multi-agent system.
  • Prompt Templates: The extract keyword tool identifies keywords, keyphrases, and named entities from the question and hint.
  • Prompt Templates: The select columns and select tables tools identify schema elements needed to formulate a SQL query.
  • Prompt Templates: The filter column tool labels column information as relevant or irrelevant for constructing the SQL query.
  • Prompt Templates: Candidate generation, revision, unit-test generation, and evaluation are represented as separate prompt-template tools.
  • Fine-tuning Dataset and Model: 72.4% performance was achieved when the open-source model received precise contextual information aligned with the gold SQL.

D.2 Models Ablation

The ablation evaluates execution accuracy across engine configurations on a subsampled development set, using different models for retrieval, generation, selection, and revision.

  • The evaluation compares engine setups defined by column filtering, candidate query generation, and table/column selection plus revision.Each setup is represented as a triplet of these components.

E SCHEMA SELECTION EXAMPLE

The schema-selection example progressively reduces a Formula 1 database schema from 13 tables and 96 columns to a compact schema used for SQL generation.

  • Schema Selection Example: The example asks for Lewis Hamilton’s fastest-ever race lap time in a Formula 1 database containing 13 tables and 96 columns.The evidence interprets “fastest lap time ever” as min(fastestLapTime).
  • Schema Selection Example: 36 columns across 13 tables remain after the filter column tool.This stage preserves linking columns for later global schema reasoning.
  • Schema Selection Example: 2 tables and 7 columns remain after select tables, which drops lapTimes because fastestLapTime is more relevant.The example illustrates how table-level selection uses a broader schema view than local column filtering.
  • Schema Selection Example: The final select columns step produces 2 tables and 5 columns for SQL generation.The funnel therefore narrows the original schema to a substantially smaller final input.
  • Schema Selection Example: Primary and foreign keys are retained during column filtering because joins and entity counts require these linking columns.The ERD marks retained columns through a white-to-dark-blue gradient.

F ERROR ANALYSIS

The error analysis compares CHESS with a vanilla GPT-4 baseline on 147 sampled BIRD development questions, separating pipeline errors from dataset-related problems.

  • Error Analysis: The analysis processes 147 sampled BIRD development questions through CHESS and a vanilla GPT-4 baseline using the full schema.The baseline supplies the question, evidence, and all tables and columns to GPT-4 with chain-of-thought prompts.
  • Error Analysis: The reported error categories include incorrect SQL, ambiguous questions, and incorrect golden SQL.Ambiguity and incorrect gold queries are characterized as dataset problems rather than pipeline failures.
  • Error Analysis: CHESS produces 42.9% incorrect SQL, with incorrect column linking contributing 5.4% of errors.The analysis reports a more even distribution of error types for CHESS across the evaluated categories.
  • Error Analysis: The examples include errors involving wrong columns, tables, filtering entities, revisions, date formatting, and evidence use.These cases illustrate distinct failure modes in SQL generation and revision.

F.1.6 Evidence:

The evidence analysis catalogs failures where questions, evidence, or SQL operations do not align, including ambiguity, formatting errors, missing clauses, and incorrect calculations.

  • Evidence: Several examples show disagreements between predicted and gold SQL over tables, joins, entities, aggregation, and identifier columns.The reported explanations identify concrete mismatches such as frpm versus schools and EUR versus Euro.
  • Evidence: Incorrect use of evidence includes malformed date handling, ignored equations, and failure to apply described filters or aggregation instructions.Examples include year extraction, timestamp formatting, average-of-averages computation, and numerator filtering.
  • Evidence: The analyzed SQL errors also include missing NULL checks, DISTINCT, GROUP BY, and incorrect comparison, division, or SELECT formatting operations.These failures are reported across examples involving cards, patients, chemical compounds, and student-club members.
  • Evidence: Some questions and evidence do not specify which output columns should appear, making the intended SELECT list unclear.The resulting gold SQL may contain incorrect or additional columns.
  • Evidence: Natural-language evidence maps phrases to database values and operations, such as triple bond to bond type = ’#’ and set OGW to setCode = ’OGW’.Such mappings provide explicit operational guidance for SQL construction.
Loading 2405.16755v3…