PaperlayerView exact revision on arXiv

Source-linked AI summary

Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows

Fangyu Lei, Jixuan Chen, Yuxiao Ye, Ruisheng Cao, Dongchan Shin, Hongjin Su, Zhaoqing Suo, Hongcheng Gao, Wenjing Hu, Pengcheng Yin, Victor Zhong, Caiming Xiong, Ruoxi Sun, Qian Liu, Sida Wang, Tao Yu

arXiv:2411.07763v2cs.CLcs.AIcs.DB

TL;DR

Real-world enterprise text-to-SQL requires models to work across large, heterogeneous databases and multi-step workflows rather than simplified benchmark inputs. Spider 2.0 evaluates this setting with 632 workflow problems and an agentic environment, finding that current systems solve only a small fraction of tasks. The benchmark exposes challenges in schema understanding, dialect handling, workflow planning, and project-context use.

  • Problem

    Existing text-to-SQL benchmarks often omit industrial database scale, diverse SQL dialects, project codebases, and complex multi-step workflows.

  • Method

    Spider 2.0 evaluates 632 enterprise workflow tasks using real databases, project codebases, documentation, and database interfaces that agents iteratively interact with.

  • Results

    21.3% is the success rate of the best o1-preview-based code agent on Spider 2.0, compared with 91.2% on Spider 1.0 and 73.0% on BIRD.

  • Takeaways & Limitations

    Spider 2.0 provides a more realistic and highly challenging direction for developing enterprise text-to-SQL and autonomous code agents.

  • Takeaways & Limitations

    Project-level DBT tasks remain difficult, with agents solving only 12.82% of tasks, while nested columns cause schema-linking errors.

Abstract

from arXiv · show

Real-world enterprise text-to-SQL workflows often involve complex cloud or local data across various database systems, multiple SQL queries in various dialects, and diverse operations from data transformation to analytics. We introduce Spider 2.0, an evaluation framework comprising 632 real-world text-to-SQL workflow problems derived from enterprise-level database use cases. The databases in Spider 2.0 are sourced from real data applications, often containing over 1,000 columns and stored in local or cloud database systems such as BigQuery and Snowflake. We show that solving problems in Spider 2.0 frequently requires understanding and searching through database metadata, dialect documentation, and even project-level codebases. This challenge calls for models to interact with complex SQL workflow environments, process extremely long contexts, perform intricate reasoning, and generate multiple SQL queries with diverse operations, often exceeding 100 lines, which goes far beyond traditional text-to-SQL challenges. Our evaluations indicate that based on o1-preview, our code agent framework successfully solves only 21.3% of the tasks, compared with 91.2% on Spider 1.0 and 73.0% on BIRD. Our results on Spider 2.0 show that while language models have demonstrated remarkable performance in code generation -- especially in prior text-to-SQL benchmarks -- they require significant improvement in order to achieve adequate performance for real-world enterprise usage. Progress on Spider 2.0 represents crucial steps towards developing intelligent, autonomous, code agents for real-world enterprise settings. Our code, baseline models, and data are available at https://spider2-sql.github.io

1 INTRODUCTION

Spider 2.0 targets enterprise text-to-SQL workflows that exceed conventional benchmark assumptions in data scale, database diversity, context, and workflow complexity. It introduces a 632-task benchmark and evaluates agents that must interact with codebases, documentation, and databases to produce multi-step results, revealing substantial performance gaps.

  • Motivation: Existing text-to-SQL benchmarks often use small, non-industrial schemas, simplistic SQL, and limited dialect diversity that underspecify real-world complexity.Enterprise databases instead span diverse systems, thousands of columns, nested structures, and multiple SQL dialects.
  • Benchmark: 632 real-world tasks cover data wrangling, transformation, and analysis across industrial applications, local databases, and cloud warehouses.Spider 2.0 databases average 812 columns, include terabyte-scale data, and use dialects curated from technical and open-source sources.
  • Task setting: Spider 2.0 tasks require project codebases, documents, database interfaces, and multiple SQL queries rather than only a prepared question, schema, and predicted query.The agent dynamically navigates projects and databases while using SQL and command-line scripts to complete transformations or analyses.
  • Evaluation: 21.3% is the best o1-preview-based code agent performance on Spider 2.0, versus 91.2% on Spider 1.0 and 73.0% on BIRD.Spider 2.0-lite reaches only 5.7% for the strongest text-to-SQL parser, underscoring the benchmark’s difficulty.
  • Evaluation: The main obstacles include schema linking in massive databases, SQL dialect handling, nested-query planning, documentation use, and project-level codebase understanding.These requirements combine metadata search, external context, complex transformations, and analytics within a single workflow.

2 BENCHMARK CONSTRUCTION

Spider 2.0 defines complementary agentic and traditional text-to-SQL settings and constructs them from complex, realistic enterprise data workflows. Its annotation pipeline combines diverse databases, rewritten SQL, project context, natural-language instructions, execution-based evaluation, and quality control.

  • Task definition: Spider 2.0 is defined as an agentic task that iteratively modifies SQL or Python code using database observations until producing a final answer.The agent receives a question, database interface, and codebase, then executes and revises code based on observations.
  • Task definition: Spider 2.0-snow and Spider 2.0-lite instead provide a question, schema, and documentation for a parser to output one SQL query.Spider 2.0-lite uses diverse database systems, whereas Spider 2.0-snow is entirely hosted on Snowflake.
  • Database and SQL collection: The benchmark collection selects databases with more than 200 columns or nested schemas and gathers 1,021 complex SQL queries plus 157 data-transformation projects.Sources include BigQuery public data, Snowflake Marketplace data, and other platforms; selected systems include BigQuery, Snowflake, SQLite, DuckDB, PostgreSQL, and ClickHouse.
  • Annotation pipeline: The annotation pipeline rewrites SQL to prevent leakage, collects dialect documentation and project contexts, and writes distinct instructions for agentic versus text-to-SQL settings.The settings balance naturalness in code-agent questions against unambiguity in self-contained text-to-SQL questions.
  • Evaluation and quality control: The benchmark uses execution-based evaluation and multiple review rounds to validate outputs, while its tasks span data wrangling, transformation, and analysis with unusually complex SQL.At least three annotators review each instruction, gold query, and evaluation script; the datasets emphasize diverse dialects, specialized functions, documentation, codebases, and multi-step reasoning.

3 EXPERIMENTS

The experiments evaluate models, code-agent frameworks, and text-to-SQL methods using execution-based metrics across Spider 2.0 variants and difficulty levels. Results show that current systems perform substantially worse on real-world workflow tasks than on established text-to-SQL benchmarks.

  • Evaluation results: 21.3% SR is achieved by the best o1-preview-based code-agent framework on Spider 2.0.Spider 2.0 uses Success Rate to measure the proportion of successfully completed task instances.
  • Evaluation results: 23.77% and 23.40% are the peak success rates of o1-preview and o3-mini on Spider 2.0-snow and Spider 2.0-lite, respectively.These models outperform GPT-4o and Claude-3.5-Sonnet across Easy, Medium, and Hard cases.
  • Evaluation results: 5.68% EX is achieved by DAIL-SQL + GPT-4o on Spider 2.0-lite, versus 86.6% on Spider 1.0 and 57.4% on BIRD.CHESS + GPT-4o handles more instances than DIN-SQL after filtering the minimally sufficient schema.
  • Evaluation results: 2.20% EX is achieved by the best method on Spider 2.0-snow, where SQL dialect differences increase the challenge.SFT CodeS-15B reaches only 0.73% EX on Spider 2.0-lite despite extensive fine-tuning.
  • Evaluation results: Current code-agent frameworks must explore codebases and documentation, navigate complex databases, and debug from SQL execution feedback.Spider-Agent provides a baseline for evaluating these database-related coding capabilities.

4 ANALYSIS

Spider 2.0 exposes distinct weaknesses in enterprise text-to-SQL, especially with nested schemas, external documents, project-level workflows, and complex SQL analysis. Error analysis shows that schema linking and erroneous data analysis account for major failures, while documentation and few-shot examples provide limited relief.

  • Task-type analysis: Nested columns remained difficult because models struggled to infer the information and function of array- or dict-valued fields for schema linking.The paper contrasts this with human multi-step and iterative understanding of nested schemas.
  • Task-type analysis: 11.54% of external-document tasks were answered correctly, because models struggled to ground document requirements together with schema information in SQL.The models often explored databases and used plausible strategies but failed at the grounding step.
  • Task-type analysis: 12.82% of DBT-based project tasks were solved, requiring multiple SQL models, repository exploration, and simultaneous database transformation.The reported result corresponds to 10 correctly solved examples.
  • Error analysis: 35.5% of sampled errors involved erroneous data analysis, including dialect-function usage, advanced calculations, and intricate query planning.The listed subcategories were dialect function usage at 10.3%, advanced data calculation at 7.5%, and intricate query planning at 17.7%.
  • Error analysis: Complex intermediate processing through CTEs and set operations contributed to erroneous data processing, while oracle function documentation and few-shot prompting produced only slight or marginal improvements.The analysis links these failures to multi-stage query construction and reports limited benefit from added syntax documentation or demonstrations.
  • Error analysis: 27.6% of errors were wrong schema linking, including 16.6% column-linking errors and 10.1% table-linking errors.Spider 2.0-lite averages over 755 columns per database, while BIRD averages approximately 54; BigQuery syntax such as TABLE SUFFIX and wildcards also challenged models.

5 RELATED WORK

Related work spans conventional code-generation benchmarks and interactive code-agent and text-to-SQL methods. These lines position Spider 2.0 at the intersection of generative code models and interactive problem solving.

  • Code generation and text-to-SQL benchmark: Earlier benchmarks commonly formulate code generation, including SQL, Bash, and Python tasks, as sequence-to-sequence problems.The cited benchmark examples include SQL-Spider, Bash-NL2Bash, and Python-HumanEval.
  • Code agent framework and text-to-SQL methods: Recent work combines generative code models with interactive problem solving through agent-based frameworks and text-to-SQL methodologies.The passage describes these efforts as targeting improved language-model reasoning capabilities.

6 CONCLUSION

Spider 2.0 is a benchmark for enterprise-level text-to-SQL workflows with diverse systems, large schemas, project contexts, and multi-step data-engineering tasks. The o1-preview-based agent achieves only 21.3% success, establishing a challenging direction for more realistic text-to-SQL systems.

  • Conclusion: Spider 2.0 covers diverse SQL dialects, large complex schemas, project codebases, documentation, and tasks across the data-engineering pipeline.It is designed as a real-world enterprise-level text-to-SQL workflow benchmark.
  • Conclusion: 21.3% success was achieved by the o1-preview-based code agent on Spider 2.0.The paper presents this result as evidence that Spider 2.0 remains highly challenging for advanced LLMs.

A SPIDER 2.0 EVALUATION SCRIPTS

Spider 2.0 uses execution-based evaluation tailored to task outputs, while its annotation pipeline constructs realistic transformation projects and SQL tasks from code, documentation, and database context.

  • Evaluation Scripts: Execution Accuracy checks whether every gold-result column appears in the predicted output for Spider 2.0-lite.This focused evaluation aims to reduce false negatives without increasing false positives.
  • Evaluation Scripts: Spider 2.0 evaluates task completion with Success rate using human-written string-, table-, or database-based scripts.The evaluation function depends on the type of answer output.
  • SQL Annotation: Annotation combines original SQL, rewritten gold SQL, external knowledge, and merged queries to create complex workflow tasks.Examples include traffic-channel analysis, route comparisons, patent originality calculations, conversion rates, and weather-informed bike-trip analysis.
  • DBT Project Annotation: DBT project construction removes selected SQL data flows while retaining YAML, Markdown, and project structure for realistic completion tasks.Annotators run the project, use planned models and data flow as instructions, remove one to three flows, and write natural-language tasks.
  • DBT Project Annotation: Solving DBT examples requires understanding the task and inspecting relevant .yml, .md, and .sql files across the codebase.YAML defines models, Markdown describes them, and SQL implements the transformation models.

B.3 SPIDER 2.0 DATABASE EXAMPLES

Spider 2.0 database examples expose agents to nested analytics data, multi-schema environments, external classification documents, cloud-query interfaces, and natural enterprise questions.

  • Database Examples: Google Analytics 4 exports daily events tables whose event parameters can be nested and repeated within RECORD fields.Each property and linked Firebase project receives a dataset, with tables named events_YYYYMMDD when daily export is enabled.
  • Database Examples: BigQuery examples require navigating multiple schemas and tables in complex enterprise database environments.The environment illustrates the broader structural variety represented in Spider 2.0.
  • External Documents: External documents provide traffic-channel and page-category rules used to classify analytics data.The page-category example distinguishes Product List Pages from Product Detail Pages.
  • Query Interface: BigQuery tasks provide query.py, credentials, examples, and CSV answer formats so agents can interact with the database and check output structure.The interface can print or save query results, while predefined files constrain required columns and support self-debugging.
  • Task Instructions: Spider 2.0 instructions favor naturalness, whereas Spider 2.0-lite instructions favor unambiguous specifications.The paired examples show concise real-world questions versus explicitly enumerated answer fields and conditions.

B.7.1 PROCESSED FUNCTIONS FOR DIFFERENT DATABASE SYSTEMS

Spider 2.0 preprocesses official documentation for database-specific functions so retrieved dialect knowledge can be inserted into agent prompts during SQL generation.

  • Processed Functions: Pre-parsed function-document chunks are retrieved and inserted into prompts to compensate for agents’ SQL dialect deficiencies.The corpus covers documentation from multiple database systems and organizes parsed functions by categories.
  • BigQuery: BigQuery documents include ST_INTERSECTS, which returns TRUE when two geography inputs share at least one point.A TRUE result implies ST_DISJOINT returns FALSE.
  • PostgreSQL: PostgreSQL enum-support functions enable cleaner programming without hard-coding particular enum values.The documentation illustrates these functions using a rainbow enum type.
  • Snowflake: Snowflake documentation defines ATAN2(y, x) as the inverse tangent of the ratio of its arguments, returning radians in [-pi, pi].The first parameter is the Y coordinate and the second is the X coordinate; the returned value is DOUBLE.
  • DuckDB: DuckDB datediff(part, startdate, enddate) returns the number of partition boundaries between two dates.The documented month example returns 2, and date_diff is an alias.
  • SQLite: SQLite group_concat concatenates non-NULL X values, optionally using Y as the separator, and string_agg is an alias.Without Y, the separator is a comma; concatenation order is arbitrary unless ORDER BY is included.

B.8 EXTEND DATASET STATISTIC

Spider 2.0 extends benchmark statistics across diverse domains, data types, SQL structures, and agent interactions, documenting the breadth and operational complexity of its tasks.

  • Dataset Statistics: Spider 2.0 databases span a wide array of domains and real-world scenarios.The benchmark’s database scope is presented as notably diverse.
  • Dataset Statistics: Spider 2.0 includes STRING, BOOLEAN, INTEGER, FLOAT, STRUCT, JSON, TIMESTAMP, and GEOGRAPHY data types.This range contributes to varied SQL dialect and data-handling challenges.
  • Dataset Statistics: Spider 2.0 contains more SQL keywords and tables than previous datasets, with queries requiring more table joins on average.These statistics characterize the benchmark’s SQL complexity.
  • Spider-Agent: Spider-Agent supports multi-turn database coding through a specialized command-line action space.The framework was developed for database-related coding tasks and projects.
  • Spider-Agent: There was no clear correlation between the number of joins and model performance.The authors speculate that uniformly complex annotated examples make performance independent of the number of involved tables.
  • Spider-Agent: Solved tasks required an average of 9.0 agent steps, ranging from 6 to 17 steps.The analysis covers 286 solved trajectories and reports action invocation frequencies by turn.

C.2 DETAILS OF SPIDER 2.0-LITE EXPERIMENTS

Spider 2.0-lite experiments compare established LLM-based text-to-SQL baselines and categorize their errors. The analysis emphasizes prompting strategies and recurring failure types.

  • Baseline methods: DIN-SQL decomposes tasks and adapts prompts to task complexity.
  • Baseline methods: DAIL-SQL achieves the best EX on Spider through prompt optimization and in-context learning.
  • Baseline methods: CHESS combines schema filtering with entity and context retrieval and SQL revision, achieving the best EX on BIRD.
  • Baseline methods: CodeS fine-tunes open-source code-generation models on extensive text-to-SQL corpora.
  • Error analysis: The appendix summarizes the descriptions and examples for all error categories.

C.4 OTHER ANALYSIS

Spider 2.0 remains difficult for current LLM-based methods, with performance varying substantially across database types and SQL dialects.

  • 5.68% EX is achieved by DAIL-SQL + GPT-4o on Spider 2.0-lite and Spider 2.0-snow.This compares with 86.6% on Spider 1.0 and 57.4% on BIRD.
  • CHESS + GPT-4o tackles more instances than DIN-SQL after efficiently filtering the minimal sufficient schema.
  • Snowflake examples are the most challenging database-type cases in Spider 2.0.
  • 12.78% performance on BigQuery exceeds 6.6% on Snowflake for the same questions hosted on both systems.The comparison uses 180 randomly selected examples.

C.5 EXPERIMENTS COST

This section summarizes experiment costs and includes an example task involving the percentage of page views for PDP pages.

  • Average API-call cost per instance is summarized across methods in Table 21.
  • The example task asks for the percentage of page views on January 2, 2021, that were PDP-type pages.
  • Figure 23 attributes an SQL prediction error to misunderstanding external knowledge for classifying PDP and PLP pages.The predicted regular-expression approach overlooks URL structure and the position of a '+' sign.
  • Figure 24 presents pass@{n} results.

C.6 CASE STUDY OF SPIDER-AGENT

Spider-Agent uses iterative reasoning, actions, observations, database exploration, codebase operations, SQL execution, and debugging to address enterprise workflow tasks. Case studies show successful iterative refinement and DBT completion, alongside failures with codebase discovery, complex documents, and nested schemas.

  • Success cases: Iterative debugging using feedback from executed queries enables Spider-Agent to refine SQL until it reaches a bug-free result in a Google Patents case.
  • Method: The agent explores databases and codebases, writes and edits SQL files, executes queries, and can run DBT commands.Its action space includes Bash, file creation or editing, BigQuery SQL execution, table discovery, table information, and sample-row retrieval.
  • Success cases: Spider-Agent successfully completes a DBT transformation after exploring the codebase and database, adding two SQL scripts, debugging, and running DBT.
  • Failure cases: Failures arise when the agent cannot locate codebase information, transform complex document strings into SQL, or comprehend nested database columns.These cases produce an incorrect query or repeated unsuccessful attempts.
  • Method: Spider-Agent follows ReAct by alternating reasoning, actions, and observations while solving complex tasks.
  • Method: The system instructions require inspecting files and schemas, using appropriate SQL actions, writing multiple queries, and revisiting information after errors.
Loading 2411.07763v2…