Text-to-SQL System with Structured RAG and Comprehensive Evaluation

ice_/_nyse · Finance · 2024 · source Agentic: no · promotional bias: med · source-backed · extracted with prompt 0.5.6 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: Financial-services text-to-SQL

ICE and NYSE needed business users to query structured financial data in natural language without knowing SQL, schemas, or internal data models.

Constraints:

The system had to retrieve relevant structured-table context before generation.
It needed to run on enterprise-governed Databricks data assets.
Evaluation had to compare generated SQL beyond string equality.
The implementation needed a feedback loop for incorrect queries.
The project had a short delivery window of roughly five weeks.

Architecture

The team built a structured-RAG text-to-SQL pipeline on Databricks Mosaic AI. Vector Search indexes table definitions and example queries; retrievers assemble schema, sample rows, and few-shot examples; an LLM generates SQL through Foundation Model APIs; evaluation compares syntax and execution; inference tables feed corrections back into few-shot learning.

Diagram confidence: reconstructed (reconstructed from prose).

flowchart LR
  business_user_interface["Business user interface"] --> structured_rag_retrievers["Structured RAG retrievers"]
  structured_rag_retrievers --> vector_search_metadata_indexes["Vector Search metadata indexes"]
  structured_rag_retrievers --> yaml_configuration_retriever["YAML configuration retriever"]
  structured_rag_retrievers --> sql_generation_llm["SQL generation LLM"]
  sql_generation_llm --> evaluation_harness["Evaluation harness"]
  evaluation_harness --> inference_tables["Inference tables"]
  inference_tables --> vector_search_metadata_indexes

Drag to pan · scroll or use +/− to zoom · reconstructed from the source's described component topology

Components

Component	Role	Interacts with
`Business user interface`	Collects natural-language questions from non-technical financial users.	`Structured RAG retrievers`, `SQL generation LLM`
`Structured RAG retrievers`	Retrieve table definitions, sample rows, and few-shot query examples.	`Vector Search metadata indexes`, `YAML configuration retriever`, `SQL generation LLM`
`Vector Search metadata indexes`	Index table definitions and sample question-SQL pairs for similarity retrieval.	`Structured RAG retrievers`
`YAML configuration retriever`	Supports lightweight experimentation with table definitions and sample queries before production indexing.	`Structured RAG retrievers`
`SQL generation LLM`	Generates SQL from the augmented structured prompt.	`Business user interface`, `Structured RAG retrievers`, `Evaluation harness`
`Evaluation harness`	Measures syntactic and execution-match quality and feeds corrections into improvement loops.	`SQL generation LLM`, `Inference tables`
`Inference tables`	Store generated outputs and correction data for continuous few-shot improvement.	`Evaluation harness`, `Vector Search metadata indexes`

Data flow

A user asks a business question. The pipeline retrieves related table DDLs, column descriptions, sample rows, and similar question-SQL pairs, inserts that context into an augmented prompt, sends it to a model such as Llama 3.1-70B, extracts SQL from the response, then evaluates generated SQL against ground truth and execution results.

3 supporting quotes

“The system uses Databricks Vector Search to index table metadata and enable rapid retrieval of relevant context when processing user queries.”summary, stated

“When a question is submitted, an embedding vector is generated and matched against the vector indexes to retrieve related table DDLs with column descriptions”summary, stated

“This augmented prompt is sent to an LLM (the case study mentions Llama 3.1-70B) via Foundation Model APIs”summary, stated

Why this architecture

Structured RAG was chosen because text-to-SQL quality depends on grounding the model in the correct enterprise schema and examples, not just prompting it with the user question.

“Unity Catalog, Vector Search, Foundation Model APIs, and Model Serving to implement a structured RAG pipeline.”summary, stated

“The retrieved context is combined with the input question to create an augmented prompt that provides the LLM with rich contextual information.”summary, stated

Models used: Llama 3.1-70B · Databricks Foundation Model APIs · Databricks Vector Search

Scale

The project was completed in about five weeks.
“The project was completed in approximately five weeks”summary, stated
The evaluation covered approximately 50 queries.
“achieving 77% syntactic accuracy and 96% execution match across approximately 50 queries.”summary, stated
The system achieved 77% syntactic accuracy and 96% execution match.
“achieving 77% syntactic accuracy and 96% execution match across approximately 50 queries.”summary, stated

Alternatives considered (roads not taken)

Static YAML retrieval only

Rejected because: YAML was useful for experimentation, but the production path needed Vector Search over metadata tables.

“ConfigRetriever: Reads context from a YAML configuration file, allowing rapid experimentation with different table definitions and sample queries without creating actual tables and vector indexes.”summary, stated

“VectorSearchRetriever: The production retriever that reads from the metadata tables and uses Vector Search for efficient similarity-based retrieval.”summary, stated

Evaluate by SQL structure alone

Rejected because: The team added execution matching because equivalent SQL can differ syntactically.

“The team implemented two complementary evaluation approaches:”summary, stated

“executes both ground truth and generated SQL queries on the same dataset and compares results based on row count, content (actual data values), and column types.”summary, stated

Patterns

`rag-retrieval` (structured retrieval)

The text-to-SQL system retrieves schema definitions, sample rows, and few-shot examples before generation.

“When a question is submitted, an embedding vector is generated and matched against the vector indexes to retrieve related table DDLs with column descriptions, sample data rows from Unity Catalog, and relevant example question-SQL pairs.”summary, stated

`eval-driven-development` (quality control)

The team adapted Spider-style evaluation and added execution matching to guide system quality.

“A notable aspect of this LLMOps implementation is the comprehensive evaluation strategy adapted from the Spider benchmark”summary, stated

`data-quality-loop` (continuous improvement)

Inference-table feedback and few-shot learning let the system learn from incorrect generated queries.

“A continuous improvement loop using inference tables and few-shot learning enables the system to learn from incorrect queries over time.”summary, stated

Tradeoffs

Use structured RAG over plain prompting.

Chose Retrieval of table metadata, sample rows, and few-shot query pairs over Sending a natural-language question alone to the LLM.

Rationale: The model needed rich enterprise data context to generate valid SQL.

“The prompt includes the user's question, relevant table DDLs with column descriptions, sample data for each related table, and example question-SQL pairs”summary, stated

Keep two retriever implementations.

Chose ConfigRetriever for experimentation and VectorSearchRetriever for production over One retrieval path for both prototyping and production.

Rationale: The two paths optimize for different needs: rapid iteration versus efficient indexed retrieval.

“The system implements two specialized retrievers that can be configured via YAML:”summary, stated

Gotchas (what bit them)

Text-to-SQL evaluation cannot rely on exact SQL strings.

Root cause: Equivalent queries may use different ordering or structure while returning the same answer.

Resolution: Use both syntactic component matching and execution-result matching.

“This evaluates the structural correctness of generated SQL by computing F1 scores for component matching and accuracy scores for exact matching.”summary, stated

“executes both ground truth and generated SQL queries on the same dataset and compares results based on row count, content (actual data values), and column types.”summary, stated

Production schema retrieval needs more than a quick config file.

Root cause: Manual configuration is good for experimentation but not enough for production metadata retrieval.

Resolution: Move from ConfigRetriever experimentation to VectorSearchRetriever over metadata tables.

“• ConfigRetriever: Reads context from a YAML configuration file, allowing rapid experimentation with different table definitions and sample queries without creating actual tables and vector indexes. This lightweight approach accelerates testing and refinement during development.”summary, stated

“VectorSearchRetriever: The production retriever that reads from the metadata tables and uses Vector Search for efficient similarity-based retrieval.”summary, stated

Evaluation

ICE and Databricks adapted Spider-style syntactic evaluation and added execution-match evaluation over approximately 50 enterprise queries.

Metrics: 77% syntactic accuracy. · 96% execution match. · Approximately 50 queries in the evaluation set.

“A notable aspect of this LLMOps implementation is the comprehensive evaluation strategy adapted from the Spider benchmark, a widely recognized benchmark for text-to-SQL systems. The team implemented two complementary evaluation approaches:”summary, stated

“The team adapted the Spider evaluation benchmark for enterprise use, achieving 77% syntactic accuracy and 96% execution match across approximately 50 queries. A continuous improvement loop using inference tables and few-shot learning enables the system to learn from incorrect queries over time.”summary, stated

Outcomes

The team delivered a Databricks-based text-to-SQL application for ICE/NYSE.
The system reached 77% syntactic accuracy and 96% execution match.
The pipeline created a continuous improvement path through inference tables and few-shot learning.

The case reports a five-week build and approximately 50 evaluation queries.

“Intercontinental Exchange (ICE), operator of the New York Stock Exchange, collaborated with Databricks to build a text-to-SQL application”summary, stated