PaperlayerView exact revision on arXiv

Source-linked AI summary

A Cost-Aware Agentic Architecture for NL-to-SQL over Nested Enterprise Schemas, with a New Benchmark

Yoga Sri Varshan Varadharajan, Ajay Yadav, Ritesh Goru, Prateek Chaudhury, Constantine Caramanis, Prateek Jain, Divyateja Pasupuleti, Sunil Kumar Pandey

arXiv:2609.04641v1cs.AI

TL;DR

Existing NL2SQL benchmarks under-measure production enterprise schemas with nested, semi-structured, graph-like structure. The paper introduces a nested-schema benchmark and a cost-aware single-generation agentic architecture, achieving 91.7% answer correctness on DevRev with a 54.6-point margin over the next-best system.

  • Problem

    Existing benchmarks largely omit nested-type traversal and typed link-graph reasoning, despite these structures appearing in production enterprise schemas.

  • Method

    The paper introduces 900 execution-verified DevRev queries and a Semantic Depth Score, alongside a single-generation agentic system with iterative schema selection, metadata retrieval, and structured repair.

  • Results

    91.7% Answer Correctness on DevRev is 54.6 pp above FlexSQL, the next-best system at 37.1%.

  • Takeaways & Limitations

    The benchmark measures nested-type and link-graph reasoning, while the architecture addresses these structures through iterative schema selection, sub-field-aware retrieval, and history-aware error handling.

  • Takeaways & Limitations

    The system requires an execution API, is evaluated only on Snowflake, and uses LLM-assisted benchmark generation verified against the production database rather than fully human curation.

Abstract

from arXiv · show

Natural-language-to-SQL systems have ad- vanced rapidly on academic benchmarks, yet production enterprise schemas exhibit graph- like, semi-structured, deeply nested structure that current benchmarks do not measure. We make two complementary contributions. First, we introduce the DevRev NL2SQL bench- mark: 900 execution-verified queries with nested-type and link-graph structure, accom- panied by the Semantic Depth Score (SDS), a schema-agnostic rubric for analytical reasoning depth. Second, we present a cost-aware single- generation agentic architecture whose schema- selection, metadata-retrieval, and error-repair components are designed for the requirements this regime imposes. On the DevRev NL2SQL benchmark the system attains 91.7% answer correctness, a margin of 54.6 percentage points over the next-best baseline; on the Spider 2.0 Snowflake public dataset, it is competitive with leading systems at a single-generation operating point.

1 Introduction

Production enterprise NL2SQL involves graph-like, semi-structured, deeply nested schemas that existing benchmarks largely do not characterize. The paper introduces the DevRev benchmark and a cost-aware single-generation architecture tailored to this regime.

  • Motivation: Enterprise schemas contain typed ARRAY[STRUCT] columns, polymorphic link arrays, and deeply nested objects requiring array reasoning, graph traversal, and specialized SQL constructs.These queries may combine LATERAL FLATTEN, conditional aggregation, and enum-constrained filters.
  • Motivation: Existing benchmarks emphasize flat relational schemas and largely omit nested-type traversal, while DevRev fields provide descriptions documenting operational intent.Spider 2.0-Snow has 7,860 tables across 152 databases, but flat primitive-typed DDL and essentially no nested-type queries.
  • Motivation: Iterative schema discovery, metadata-rich retrieval, and structured error handling address coverage gaps and diverse nested-type failures that generic retries handle poorly.Correct LATERAL FLATTEN requires sub-field schemas and representative array values, not only column type signatures.
  • Motivation: Inference cost motivates studying single-generation architectures because longer schemas, larger SQL candidates, slower executions, and retries elevate per-query cost.The approach could in principle be combined with multi-candidate generation.
  • Contributions: The DevRev NL2SQL dataset contains 900 execution-verified queries grounded in a production Snowflake schema with nested fields, typed arrays, and up to four nesting levels.The schema includes 30 core entity types, 13,898 fields, 2,036 typed ARRAY[STRUCT] columns, and 1,412 STRUCT-typed or nested fields.
  • Contributions: 91.7% correctness is achieved on the full 900-query DevRev corpus, while the architecture uses one generation trajectory per query and no fine-tuning.It also reaches 91.5% on the high-SDS subset and is competitive on the Spider 2.0 Snowflake public leaderboard at a single-generation operating point.

2 Related Work

Related work spans multi-candidate scaling, agentic tool augmentation, established NL2SQL benchmarks, and synthetic query generation. The paper positions DevRev as targeting nested-type and link-graph reasoning rather than flat-schema evaluation or training-data synthesis.

  • Multi-Candidate and Test-Time Scaling: Tournament-style NL2SQL systems generate multiple candidates and select or refine among them using preference optimization, diverse prompting, parallel synthesis, or execution-guided confidence.Examples include CHASE-SQL, XiYan-SQL, Agentar-Scale-SQL, and DeepEye-SQL.
  • Agentic and Tool-Augmented NL2SQL: Agentic NL2SQL work frames generation as multi-step tool calling, decomposition, self-correction, collaboration, or metadata enrichment for enterprise schemas.The paper contrasts this line with its two-tier orchestrator design.
  • NL2SQL Benchmarks: Spider 1.0, BIRD, and Spider 2.0 expand benchmark scale or schema messiness but retain the flat-table assumption, with Spider 2.0-Snow containing 7,860 tables across 152 databases.Its Snowflake subset shifts schema linking to large scale without declared sub-field schemas or typed link arrays.
  • Synthetic NL Query Generation: SING-SQL hierarchically partitions databases and synthesizes in-domain training data across complexity tiers, whereas DevRev generates evaluation queries targeting analytical depth.DevRev also uses six-persona rewriting and specifically targets nested-type, link-graph schemas.

3 The DevRev Benchmark

The DevRev benchmark measures NL2SQL over production enterprise schemas with explicit nested structures and typed link graphs. It adds Semantic Depth Score, a schema-agnostic rubric for diagnosing analytical reasoning requirements.

  • Schema Regime: Spider 2.0-Snow evaluates large-scale schema linking over flat DDL, whereas DevRev evaluates nested-type traversal and link-graph reasoning in a production schema.Spider 2.0-Snow contains no STRUCT columns and represents semi-structured data as opaque VARIANT types without DDL sub-field schemas.
  • Schema Regime: DevRev schemas contain explicit ARRAY[STRUCT] sub-fields, polymorphic typed links, up to four nesting levels, and English field descriptions encoding operational intent.Queries require LATERAL FLATTEN, enum-constrained filtering, and multi-hop typed-link traversal.
  • Schema Regime: The production database has 30 core entity types and 190 schema variants, while benchmark generation uses 15 types with detailed m-schema documentation.The benchmark’s schema counts are summarized in Table 1.
  • Schema Patterns: Polymorphic link arrays store relationship type and display_id, requiring type filtering and external entity mapping to resolve join targets.The same array can link to issues, enhancements, or other tickets.
  • Schema Comparison: DevRev’s ARRAY[STRUCT] fields expose sub-field schemas, unlike Spider 2.0-Snow’s opaque VARIANT columns with no DDL sub-field definitions.Table 1 marks 15 of 30 core entity types as used for benchmark generation.
  • Benchmark Construction: Queries are generated through column planning, formula retrieval, natural-language query generation, and persona rewriting to enforce analytical depth and lexical variation.The pipeline uses DevRev-specific column names and business concepts.
  • Semantic Depth Score: SDS is a seven-dimensional rubric scoring analytical reasoning independently of resulting SQL syntax, with dimensions covering eligibility, derived metrics, scoping, logic, precision, time, and construct diversity.Each dimension receives 0–5 points, for a maximum score of 35.
  • Semantic Depth Score: SDS separates semantic depth from clause counts and supports diagnostic evaluation of targeted difficulties such as temporal reasoning or advanced SQL constructs.A deeply chained derivation can score highly despite few clauses, while long SQL with simple aggregations can score lower.

4 System Design for Nested Enterprise Schemas

The system separates orchestration from SQL synthesis and uses iterative schema discovery, structured repair, deterministic checking, and semantic validation for nested enterprise schemas. These components are organized to improve structural correctness while controlling inference and database costs.

  • Design Principles: The architecture uses iterative schema selection, sub-field-aware retrieval, structured error handling, and cost-aware single-generation operation for the enterprise schema regime.These principles derive from polymorphic links and nested ARRAY[STRUCT] fields.
  • Two-Tier Agentic Architecture: A top-level orchestrator manages the query lifecycle by invoking KGSchemaGetter, NodeSchemaGetter, and NLToSQL while retaining attempt history and database interaction.It decides whether to expand schema context, repair the query, or accept the result.
  • Two-Tier Agentic Architecture: The NL2SQL generator is a tool call that receives assembled context and returns one SQL query without calling tools or seeing execution results.The orchestrator, rather than the generator, handles meta-level decisions and execution feedback.
  • Iterative Schema Discovery: Runtime schema selection is LLM-driven through knowledge-graph tool calls, and selected tables receive compressed per-table schemas through parallel NodeSchemaGetter calls.The runtime has no embedding-based ranker in the hot path, and catalog entries include top-K key columns.
  • Pre-Execution Verification: The Query Plan Verifier audits generated SQL for join safety, grain correctness, and deduplication placement before deterministic checking or execution.Detected flaws produce targeted feedback routed directly back to generation.
  • Structured Error Repair: Execution failures are classified into 15+ error types, with schema-coverage errors triggering schema expansion and other errors triggering targeted in-place repair.Structured directives and failed-attempt history are injected into subsequent generator calls, while the taxonomy remains configuration-extensible.
  • Deterministic Checker Chain: The deterministic checker chain uses blockers for semantic failures and rewriters for cross-dialect fixes, running cheap static checks before database execution.Examples include identifier-casing normalization and mapping DATE_DIFF to Snowflake’s DATEDIFF.
  • Dynamic Cheatsheet: A dynamic cheatsheet persists transferable SQL rules across the evaluation session and injects top rules before every generation call.Rules can encode nested-type practices such as using LATERAL FLATTEN for an array.

5 Experiments

Experiments show strong DevRev performance, cost efficiency, and robustness across semantic difficulty, while ablations identify metadata retrieval and structured feedback as especially important components. SDS-guided hints also improve failing-query accuracy without changing core architectures.

  • 5.2 Main Results: 91.7% Answer Correctness was achieved on DevRev-900, 54.6 percentage points above FlexSQL’s 37.1%.The baselines clustered between 27–37% across distinct architectures.
  • 5.2 Main Results: $0.57 per correctly answered query was 8× cheaper than APEX-SQL, 27× cheaper than FlexSQL, and 1.6× cheaper than ReFoRCE.ReFoRCE had the lowest per-query cost but only 29.1% correct queries.
  • 5.3 Difficulty-Stratified Analysis: The system’s Answer Correctness varied by less than 6 percentage points across Easy, Medium, and Hard SDS buckets, while baseline performance degraded monotonically.The gap over FlexSQL increased from 44.6 percentage points on Easy to 57.1 on Hard.
  • 5.4 SDS as an Actionable Improvement Signal: SDS-guided query hints measurably improved accuracy without modifying the core architecture.The evaluation injected dimension-specific hints into originally failing queries for each baseline.
  • 5.4 SDS as an Actionable Improvement Signal: SDS-guided hints raised ReFoRCE’s failing-query accuracy from 29.1% to 63.2% and APEX-SQL’s from 27.2% to 41.4%.The largest category gains were Ranking/Top-N for ReFoRCE (+39.6 pp) and Date/Time (+35.5 pp).
  • 5.5 Ablations: Removing metadata-enriched retrieval caused the largest ablation drop, reducing overall accuracy by 15.0 percentage points and high-D7 accuracy by 64.4 points.Removing structured error handling reduced accuracy by 13.2 points and increased mean repair rounds from 1.4 to 3.2.

6 Conclusion

The paper contributes a benchmark for nested enterprise NL2SQL and a cost-aware single-generation architecture tailored to schema selection, metadata retrieval, and error repair. It also identifies offline execution, Snowflake-only evaluation, human-curation differences, and inferential cost comparisons as boundaries.

  • The DevRev benchmark fills a measurement gap for nested-type traversal and polymorphic link-graph reasoning in enterprise schemas.
  • The architecture uses iterative schema selection, sub-field-aware metadata retrieval, and a structured history-aware error taxonomy.
  • The architecture can be composed with tournament-style multi-candidate generation as a direct extension path.
  • The system relies on an execution API, so it cannot run in offline settings.
  • The evaluation covers two Snowflake benchmarks, while generalization to other SQL dialects remains to be validated.
  • Cost comparisons with multi-candidate systems are inferential when candidate counts or execution-call counts are not publicly reported.

B.2 Error Taxonomy Classes

The error taxonomy classifies execution failures by phase and maps them to targeted repair actions. The deterministic checker chain catches statically detectable problems before database execution, while rewriters silently normalize SQL.

  • The taxonomy classifies failures into 15+ error types organized by failure phase and corrective action.
  • Schema-coverage errors trigger schema expansion, while nested-type errors trigger construct-specific repair directives.
  • Blockers reject SQL and return classified errors to the repair loop, while rewriters transform SQL and never fail.
  • Identifier errors trigger alias-rewriting repair, whereas semantic errors trigger repair with the question re-presented alongside failed SQL.
  • Database execution is the final checker stage, after statically detectable failures have been handled.

C Dataset Generation Pipeline: Full Details

The dataset-generation pipeline combines sub-schema pruning, formula-guided planning, slot-level query generation, rewriting, execution filtering, and quality audits. It targets analytical diversity and semantic depth across the resulting corpus.

  • Sub-schema generation: A sub-schema generator selects 2–5 analytically useful non-connection columns per table while retaining primary- and foreign-key columns.
  • Formula retrieval: The formula book contains 25 structural patterns derived from the highest-SDS Spider 2.0-Snow queries.
  • Formula retrieval: Each formula combines a macro logic skeleton with micro patterns tied to Semantic Depth Score dimensions and SQL structures.
  • Planning: Stage 1 creates a six-slot plan with analytical categories, SDS targets, columns, intent, and required formula patterns.
  • Planning: Coverage constraints require aggregation in all six slots, relationship/network reasoning in at least five, and additional minimum category coverage.
  • Formula retrieval: Stage 2 ranks compatible formulas without LLM calls, retaining up to five per slot and preventing formula repetition within a sub-schema.
  • Query generation: Stage 3 generates one query per slot using the sub-schema, analytical intent, required patterns, and a differently instantiated formula example.
  • Quality control: All generated queries are executed against DevRev, with empty-result queries discarded at a 7.6% rejection rate.

D.1 Results on DevRev: Detailed Breakdown

The DevRev evaluation reports high answer correctness and relatively stable performance as Semantic Depth Score increases, while failures at specific dimensions identify reasoning and nested-type pressure points. The evaluation uses a rubric-based judge and spans multiple analytical categories.

  • Overall results: 91.7% Answer Correctness (825/900 queries) is achieved on the full DevRev benchmark.
  • Overall results: 91.5% Answer Correctness (745/814) is achieved on the high-SDS subset, covering 90.4% of the corpus.
  • Dimension breakdown: Failures on high-D2, high-D6, and high-D7 queries implicate computed-value reasoning, date-function handling, and nested-type machinery, respectively.
  • SDS breakdown: All three baselines decline monotonically as SDS increases, while the system remains within a 2.8 pp range.
  • Query complexity categories: The evaluation distinguishes eight analytical categories, including aggregation, statistical analysis, comparison, ranking, date/time, and relationship/network reasoning.
  • Evaluation protocol: The LLM judge scores generated SQL across four criteria with a maximum of 50 points, using question, SQL, schema, and result-row evidence.
  • Query complexity categories: Aggregation appears in all six generation slots, relationship/network reasoning in at least five, and classification/grouping in at least four.

D.4 Dataset Statistics

DevRev contains 900 execution-verified queries spanning multiple complexity categories, while its analytical depth is comparable to Spider 2.0-Snow but difficult in different ways.

  • 900 execution-verified queries form the DevRev corpus, evaluated across 14 schema combinations.
  • 89.2% judge score occurs for Easy queries, 94.4% for Medium, and 91.0% for Hard queries.
  • Aggregation is universal by design, while each query may receive multiple complexity-category assignments.
  • Mean SDS is 19.15 for DevRev versus 20.20 for Spider 2.0-Snow, indicating comparable analytical depth.Spider 2.0-Snow is harder in SQL-construct diversity and multi-stage pipeline logic, whereas DevRev is harder in derived-metric construction.

D.5 Results on Spider 2.0-Snow

On Spider 2.0-Snow, the system is evaluated on the 120-query subset with public gold answers rather than the full leaderboard, limiting direct ranking comparisons.

  • The 85.8% score is indicative rather than directly ranked because evaluation uses only the publicly released 120-query subset.
  • 103 of 120 gold-answer queries are answered correctly, yielding 85.8% EX.
  • Baseline systems scoring 27–37% AC on DevRev score 63–73% EX on Spider 2.0-Snow’s full leaderboard.The contrast is reported as reflecting differences between the nested enterprise regime and the flat-table schema-linking regime.
  • The architecture is described as competitive on flat-table, large-scale schema linking while using one generation pass per query.

D.6 Cost Comparison

The architecture reduces inference cost through a single schema-selection pass while retaining strong answer correctness and matching ReFoRCE’s median database-execution count.

  • Median cost measurements use the same stratified 50-query sample, with LLM calls taken from API logs and database executions from per-query traces.
  • Matching ReFoRCE at median 4 database executions, the system achieves 3× higher AC and uses 8–23× fewer LLM calls than APEX-SQL and FlexSQL.
  • The architecture avoids multi-candidate exploratory database probing through a single schema-selection pass guided by a metadata-enriched retriever.
  • APEX-SQL uses a median 16 LLM calls and 18 database calls, while FlexSQL uses 46 LLM calls and 20 database executions per query.
Loading 2609.04641v1…