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

32.4.1 LLMs and Tabular Data

Tables break LLMs in ways free-form text does not. Strict row-column structure, typed values, foreign keys, and uniqueness constraints all matter. Stuff a 50-row table into the context window and the model reasons over it directly. Bigger than that and you flip the problem: get the model to write SQL or pandas that pulls only what you need.

32.4.1.1 Direct Table Reasoning

For tables under 50 rows, drop them straight in the prompt. Markdown with clear column headers beats raw CSV: the model reads the schema visually. The approach breaks two ways at scale, the table overflows context, and LLM arithmetic across many rows is flaky. Beyond 50 rows, switch to query generation.

# Direct table Q&A: serialize a small DataFrame into the prompt and let the LLM
# answer questions about it. Works for tables under ~50 rows; bigger tables need text-to-SQL.
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

32.4.2 Text-to-SQL: Architecture

Text-to-SQL turns "show me last quarter's top customers" into a runnable query. The hard part is not the SQL grammar, it is picking the right tables, columns, and joins from a schema the user never names. Modern LLM pipelines crack 85% on Spider by stacking three tricks: schema linking, few-shot examples, and an execute-then-repair loop. Figure 32.4.1a traces the pipeline end to end.

Figure 32.4.1b: Text-to-SQL pipeline: schema linking maps natural language to database elements, the LLM generates SQL, execution returns results, and errors trigger regeneration.

32.4.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.

A concrete failure: a user asks "show me churn last quarter." Your schema has no churn column, but it has users.last_active_date, users.cancellation_date, and subscriptions.status. The LLM needs to recognize that "churn" is a derived concept (users where cancellation_date BETWEEN Q3_start AND Q3_end, or status = 'cancelled' with a date filter). Without good schema linking, the model might invent a churn column (and the query fails on execution), or it might query the wrong table (and silently return the wrong number). The fix is including a few-shot example that demonstrates the canonical pattern for "churn" on your schema, plus a column description like cancellation_date - the date a user ended their subscription; used to compute churn rate.

# Pull table names, columns, and sample rows out of the database to feed the LLM.
# The richer the schema context, the more accurate the generated SQL.
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)

32.4.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 32.4.3 below demonstrates how to combine schema information with optional few-shot examples in a single LLM call.

# Generate executable SQL from a natural-language question. Optional few-shot
# examples (question + expected SQL pairs) steer the model toward your SQL dialect.
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

32.4.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.

# Self-correcting SQL loop: when the generated query throws, feed the error back
# to the LLM and ask for a fix. Bounded retries keep one bad query from looping forever.
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}

32.4.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.

32.4.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.

# Load a CSV into an in-memory SQLite database so the LLM can hit it with
# SQL queries instead of doing brittle arithmetic across rows in the prompt.
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
    )

32.4.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 32.4.2a shows how hybrid retrieval merges database results with document context.

Figure 32.4.2b: Hybrid retrieval combines precise numerical data from databases with contextual explanations from documents to produce comprehensive answers.

32.4.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.

32.4.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.

32.4.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:

32.4.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.

pip install ragas datasets
from datasets import Dataset
from ragas import evaluate
from ragas.metrics import (
    LLMContextRecall, Faithfulness, ContextPrecision, ResponseRelevancy,
)

ds = Dataset.from_list([
    {
        "user_input": "What is our vacation policy?",
        "retrieved_contexts": ["Full-time employees receive 20 days of paid vacation."],
        "response": "Full-time employees receive 20 paid vacation days per year.",
        "reference": "20 paid vacation days annually for full-time employees.",
    },
])
report = evaluate(
    ds,
    metrics=[LLMContextRecall(), Faithfulness(), ContextPrecision(), ResponseRelevancy()],
)
print(report.to_pandas())  # one row per example with per-metric scores

RAGAS is the best-known framework, but it is not the only one. Three alternatives are worth naming because each occupies a slightly different niche in production stacks.

• ARES (Saad-Falcon et al., NAACL 2024) trains a lightweight judge model on synthetic in-domain examples and then evaluates context relevance, answer faithfulness, and answer relevance with that fine-tuned judge instead of calling a frontier LLM per query. The trade-off versus RAGAS is upfront fine-tuning cost in exchange for lower per-evaluation cost and tighter domain alignment; ARES is a good fit when the eval set is large enough that the judge-API bill itself becomes the bottleneck.
• TruLens (TruEra) is the team that coined the "RAG triad" terminology (context relevance, groundedness, answer relevance) reused above and in Section 32.2.2. TruLens packages those three metrics as runtime feedback functions and an observability dashboard, so each production query gets a score tile next to the trace; it sits closer to APM than to a CI metric library.
• DeepEval (Confident AI) presents RAG metrics with a pytest-style API (assert_test, @pytest.mark.deepeval), which is convenient when the team already runs pytest in CI; see the library-shortcut callout in Section 36.2.

from trulens.core import TruSession, Feedback
from trulens.providers.openai import OpenAI as TruOpenAI
from trulens.apps.langchain import TruChain

provider = TruOpenAI()
session = TruSession()

f_groundedness   = Feedback(provider.groundedness_measure_with_cot_reasons).on_input_output()
f_context_rel    = Feedback(provider.context_relevance).on_input_output()
f_answer_rel     = Feedback(provider.relevance).on_input_output()

tru_chain = TruChain(
    rag_chain,
    app_id="rag-prod",
    feedbacks=[f_groundedness, f_context_rel, f_answer_rel],
)
with tru_chain as recording:
    rag_chain.invoke("What is our vacation policy?")
session.get_records_and_feedback()[0].head()

32.4.8.3 Manual vs. Automated RAG Tuning