Structured Data & Text-to-SQL

Why memorize a million rows when you can simply ask the database a well-formed question?

RAG, SQL-Fluent AI Agent

1. LLMs and Tabular Data

Tabular data presents unique challenges for LLMs. Unlike free-form text, tables have strict row-column structure, typed values, and relational constraints (foreign keys, uniqueness). LLMs can process small tables directly in their context window, but larger datasets require the model to generate executable queries (SQL, pandas) that retrieve the relevant information.

1.1 Direct Table Reasoning

For small tables (fewer than 50 rows), LLMs can reason over tabular data presented directly in the prompt. The key is formatting: presenting tables as Markdown or structured text with clear column headers helps the model understand the schema. However, this approach fails for large datasets because of context window limits and because LLM arithmetic on many rows is unreliable. Code Fragment 20.5.1 below puts this into practice.

# implement ask_about_table
# Key operations: API interaction

import pandas as pd
from openai import OpenAI

client = OpenAI()

def ask_about_table(df: pd.DataFrame, question: str):
 """Ask a question about a small DataFrame directly."""
 # Convert to markdown for clear formatting
 table_md = df.to_markdown(index=False)

 response = client.chat.completions.create(
 model="gpt-4o",
 messages=[{
 "role": "system",
 "content": "You are a data analyst. Answer questions about "
 "the provided table. Show your reasoning."
 }, {
 "role": "user",
 "content": f"Table:\n{table_md}\n\nQuestion: {question}"
 }]
 )
 return response.choices[0].message.content

2. Text-to-SQL: Architecture

Text-to-SQL systems convert natural language questions into SQL queries that can be executed against a database. The core challenge is mapping the user's informal language to the precise syntax of SQL while correctly identifying which tables, columns, and joins are needed. Modern LLM-based text-to-SQL pipelines achieve over 85% accuracy on standard benchmarks by combining schema linking, few-shot examples, and error correction. Figure 20.5.1 traces the complete text-to-SQL pipeline.

2.1 Schema Linking

Schema linking is the process of identifying which database tables and columns are relevant to the user's question. This is often the hardest step because users rarely use exact column names. A user asking about "revenue" might mean the total_amount column in the orders table, or the annual_revenue column in the companies table. Code Fragment 20.5.2 below puts this into practice.

# implement get_schema_context
# Key operations: RAG pipeline, prompt construction

def get_schema_context(db_connection, relevant_tables=None):
 """Extract schema information for the LLM prompt."""
 schema_parts = []

 # Get all tables or filter to relevant ones
 tables = relevant_tables or get_all_tables(db_connection)

 for table in tables:
 columns = get_columns(db_connection, table)
 col_info = []
 for col in columns:
 col_str = f" {col['name']} {col['type']}"
 if col.get("primary_key"):
 col_str += " PRIMARY KEY"
 if col.get("foreign_key"):
 col_str += f" REFERENCES {col['foreign_key']}"
 col_info.append(col_str)

 # Include sample values for disambiguation
 samples = get_sample_values(db_connection, table, limit=3)

 schema_parts.append(
 f"CREATE TABLE {table} (\n"
 + ",\n".join(col_info)
 + f"\n);\n-- Sample rows: {samples}"
 )

 return "\n\n".join(schema_parts)

2.2 SQL Generation with Few-Shot Examples

Once the schema context is prepared, the next step is generating executable SQL from the user's natural language question. Code Fragment 20.5.3 below demonstrates how to combine schema information with optional few-shot examples in a single LLM call.

# implement text_to_sql
# Key operations: API interaction

def text_to_sql(question, schema_context, examples=None):
 """Generate SQL from natural language question."""

 few_shot = ""
 if examples:
 few_shot = "\n\nExamples:\n" + "\n".join([
 f"Q: {ex['question']}\nSQL: {ex['sql']}"
 for ex in examples
 ])

 response = client.chat.completions.create(
 model="gpt-4o",
 messages=[{
 "role": "system",
 "content": f"""You are a SQL expert. Generate a SQL query to
answer the user's question based on this schema:

{schema_context}
{few_shot}

Rules:
- Use only tables and columns from the schema
- Return ONLY the SQL query, no explanation
- Use standard SQL compatible with PostgreSQL
- Add LIMIT 100 to prevent unbounded results
- Handle NULLs appropriately"""
 }, {
 "role": "user",
 "content": question
 }],
 temperature=0.0
 )

 sql = response.choices[0].message.content.strip()
 # Remove markdown code fences if present
 sql = sql.replace("```sql", "").replace("```", "").strip()
 return sql

3. Error Correction and Self-Healing Queries

Even the best LLMs generate incorrect SQL queries. Common errors include referencing nonexistent columns, incorrect join conditions, missing GROUP BY clauses, and type mismatches. A robust text-to-SQL pipeline includes an error correction loop that catches execution failures, sends the error message back to the LLM along with the original query, and asks for a corrected version. Code Fragment 20.5.4 below puts this into practice.

# implement execute_with_retry
# See inline comments for step-by-step details.

import sqlite3

def execute_with_retry(question, schema_context, db_path,
 max_retries=3):
 """Execute SQL with automatic error correction."""
 conn = sqlite3.connect(db_path)
 error_history = []

 for attempt in range(max_retries):
 # Generate SQL (include errors from prior attempts)
 if error_history:
 error_context = "\n".join([
 f"Attempt {e['attempt']}: {e['sql']}\n"
 f"Error: {e['error']}"
 for e in error_history
 ])
 question_with_context = (
 f"{question}\n\nPrevious failed attempts:\n"
 f"{error_context}\n\nFix the errors."
 )
 else:
 question_with_context = question

 sql = text_to_sql(question_with_context, schema_context)

 try:
 cursor = conn.execute(sql)
 columns = [desc[0] for desc in cursor.description]
 rows = cursor.fetchall()
 return {
 "sql": sql,
 "columns": columns,
 "rows": rows,
 "attempts": attempt + 1
 }
 except Exception as e:
 error_history.append({
 "attempt": attempt + 1,
 "sql": sql,
 "error": str(e)
 })

 return {"error": "Failed after max retries",
 "history": error_history}

4. Text-to-SQL Benchmarks

Standard benchmarks measure text-to-SQL system performance across databases of varying complexity. The two most widely used benchmarks are Spider and BIRD, which test different aspects of the text-to-SQL challenge.

5. CSV and Spreadsheet Processing

Not all structured data lives in databases. CSV files, Excel spreadsheets, and Google Sheets are ubiquitous in business environments. LLMs can process these by either loading them into an in-memory database (SQLite) for SQL queries or generating pandas code for direct manipulation. Code Fragment 20.5.5 below puts this into practice.

# implement csv_to_queryable
# Key operations: memory management

import pandas as pd
import sqlite3

def csv_to_queryable(csv_path, table_name="data"):
 """Load a CSV into SQLite for text-to-SQL querying."""
 df = pd.read_csv(csv_path)

 # Create in-memory database
 conn = sqlite3.connect(":memory:")
 df.to_sql(table_name, conn, index=False)

 # Generate schema description
 schema = f"Table: {table_name}\nColumns:\n"
 for col in df.columns:
 dtype = str(df[col].dtype)
 sample = df[col].dropna().head(3).tolist()
 schema += f" {col} ({dtype}), e.g., {sample}\n"

 return conn, schema

# Usage
conn, schema = csv_to_queryable("sales_data.csv", "sales")
result = execute_with_retry(
 "What are the top 5 products by total revenue this quarter?",
 schema, conn
)

6. Hybrid Structured/Unstructured Retrieval

Many real-world questions require combining structured data (from databases) with unstructured context (from documents). For example, "Why did Q3 revenue decline for our enterprise segment?" requires both the numerical revenue data from a database and contextual explanations from quarterly reports, meeting notes, or market analyses. Figure 20.5.2 shows how hybrid retrieval merges database results with document context.

7. Multi-Table Joins and Complex Queries

The hardest text-to-SQL queries involve multiple tables with complex join conditions, nested subqueries, window functions, and conditional aggregations. These queries require the LLM to understand the database's relational structure: which tables connect to which, through what foreign keys, and with what cardinality.

8. Automated RAG Pipeline Optimization

Building a RAG pipeline involves dozens of configuration decisions: which embedding model to use, what chunk size and overlap to set, how many documents to retrieve, whether to apply reranking, what prompt template to use for generation, and how to handle multi-hop queries. Traditionally, teams tune these parameters manually through trial and error. Automated RAG optimization treats the entire pipeline as a configurable system and uses systematic search to find the best configuration for a given dataset and task. This approach is gaining traction as RAG matures from a research prototype into a production pattern.

8.1 RAG as a Configurable Pipeline

The key insight behind AutoRAG is that every RAG pipeline is a composition of interchangeable components, each with its own hyperparameters. Just as AutoML tunes model architecture and learning rate, AutoRAG tunes retrieval and generation parameters:

8.2 RAGAs: Evaluation Metrics for RAG

Meaningful optimization requires meaningful metrics. The RAGAs framework (Shahul et al., 2023) provides a suite of evaluation metrics specifically designed for RAG systems. These metrics evaluate both the retrieval and generation stages independently, enabling targeted optimization. The key metrics are:

• Context Precision: What fraction of retrieved passages are actually relevant to the question? High precision means the retriever is not wasting context window space on irrelevant documents.
• Context Recall: What fraction of the information needed to answer the question is present in the retrieved passages? High recall means the retriever is finding all the relevant evidence.
• Faithfulness: Is every claim in the generated answer supported by the retrieved context? This directly measures hallucination risk.
• Answer Relevancy: Does the generated answer actually address the question asked? A faithful answer can still be irrelevant if the model focuses on the wrong part of the context.

8.3 Manual vs. Automated RAG Tuning