RAGtext-to-SQLSQLLLM engineeringretrieval

RAG Over SQL and Structured Data: Text-to-Query Patterns That Work

Pramod Dutta · Jul 5, 2026 · 19 min read

RAG over structured data breaks the moment you treat a database like a pile of documents. Embedding rows and ranking them by cosine similarity cannot answer "what was our average order value in March", because aggregation is computation, not lookup. The approach that works is text-to-query: the model writes SQL, a guarded executor runs it against the live database, and the final answer is grounded in the result set instead of whatever chunks happened to sit nearby in vector space. This guide covers the structured data RAG patterns that hold up in production in 2026: schema-aware text-to-SQL, schema linking for large warehouses, semantic layers with verified queries, and the narrow but real cases where embedding rows still makes sense. It also covers the parts most tutorials skip: guardrails, routing, and evaluation.

Why Vector Search Alone Fails for RAG Over Structured Data

Classic RAG assumes the answer already exists as a span of text somewhere in your corpus. Structured data violates that assumption in four distinct ways.

Aggregation. Average order value, month-over-month growth, p95 latency: these require scanning and computing over many rows. No chunk contains the answer. The database has to compute it.
Freshness. An embedding is a snapshot. Rows change constantly, and re-embedding a 40 million row table on every update is neither cheap nor fast. A SQL query is always current.
Precision. "Orders over 500 dollars from EMEA, excluding refunds, last quarter" is a WHERE clause. Similarity search returns things that are roughly about that. Databases return exactly that, and analytics questions punish "roughly".
Relationships. The value of relational data lives in the joins. Flattening tables into text chunks destroys the foreign keys that connect a customer to their orders to their invoices, and no reranker reconstructs them.

There is also a cost argument. Embedding, storing, and refreshing vectors for millions of rows costs real money and still answers questions worse than the SQL engine you already run. The database is the best retriever for its own data.

So in structured data RAG, the retrieval step is not nearest-neighbor search. It is query execution. The LLM's job shrinks to translation (question to query) and synthesis (result set to answer). Current models are genuinely good at both, provided you feed them the right context. The rest of this article is about what that context is and how to execute the output safely.

The Four Patterns, and When to Use Each

Direct text-to-SQL. The model sees the whole schema and writes a query. Works well up to roughly 40 to 50 well-named tables. Cheapest to build, the right first move for internal tools.
Schema linking plus text-to-SQL. For warehouses with hundreds or thousands of tables. Retrieve the relevant tables and columns first, then generate against that subset.
Semantic layer and verified queries. The model does not write free-form SQL. It picks a vetted metric or query template and fills in typed parameters. Highest correctness, right for business metrics and anything customer-facing.
Rows as documents. Embed text-heavy columns for lookup-and-describe questions, with hard metadata filters. Never for aggregates.

Most real systems combine pattern 1 or 2 with a router, then graduate the top 20 recurring questions into pattern 3 once the query logs show what people actually ask.

Pattern 1: Text-to-SQL With a Schema Card

The single biggest driver of text-to-SQL accuracy is not the model. It is what the model knows about your schema at generation time. Build a schema card containing four things: the DDL, business-meaning comments, a few sample rows per table, and the distinct values of low-cardinality columns.

Sample rows matter more than people expect. Without them, the model guesses value formats: it writes country = 'India' when the column stores 'IN', or status = 'completed' when the enum is 'settled'. Three real rows fix an entire class of silent failures.

import sqlite3

def schema_card(conn: sqlite3.Connection, sample_rows: int = 3) -> str:
    tables = conn.execute(
        "SELECT name, sql FROM sqlite_master "
        "WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
    ).fetchall()
    parts = []
    for name, ddl in tables:
        parts.append(ddl.strip() + ";")
        cur = conn.execute(f"SELECT * FROM {name} LIMIT {sample_rows}")
        cols = [d[0] for d in cur.description]
        parts.append(f"-- {name} columns: {', '.join(cols)}")
        for row in cur.fetchall():
            parts.append(f"-- sample: {row}")
        parts.append("")
    return "\n".join(parts)

def enum_hints(conn, table: str, column: str, cap: int = 20) -> str | None:
    vals = [
        v[0] for v in conn.execute(
            f"SELECT DISTINCT {column} FROM {table} LIMIT {cap + 1}"
        ) if v[0] is not None
    ]
    if len(vals) > cap:
        return None
    return f"-- {table}.{column} values: {sorted(map(str, vals))}"

The generation prompt should pin down the dialect, the date, the timezone, and an escape hatch for unanswerable questions:

You are a senior analyst writing SQLite SQL. Today is 2026-07-09 (UTC).

Rules:
- Output exactly one SELECT statement and nothing else.
- No markdown fences, no commentary.
- Use only the tables and columns shown in the schema.
- Alias every aggregate (SUM(amount) AS total_amount).
- Use explicit JOIN ... ON clauses.
- Timestamps are ISO 8601 strings in UTC.
- If the schema cannot answer the question, output exactly CANNOT_ANSWER.

Schema:
{schema_card}

Similar answered questions:
{few_shot_pairs}

Question: {question}

Two practical notes. First, models still love wrapping SQL in markdown fences even when told not to, so strip any fence lines from the output before parsing. Second, state the dialect explicitly and keep it consistent: a prompt that says Postgres while the executor is SQLite produces date_trunc calls that fail at runtime. If you must support several engines, generate in one dialect and transpile with sqlglot.

There is also an agentic variant of this pattern. In 2026 most agent stacks expose the database through an MCP server (mature servers exist for Postgres, SQLite, and the major warehouses), giving the model list_tables, describe_table, and query tools so it can explore the schema itself before answering. This works well for ad-hoc analysis, but cap the exploration loop at a handful of steps and point the MCP server at the same read-only credentials described next. An agent that can explore is also an agent that can wander.

Guardrails: Making Generated SQL Safe to Execute

Never rely on the prompt for safety. "Only output SELECT statements" is a suggestion; the database's permission system is a guarantee. Use defense in depth: lock down the connection, validate the AST, then execute with limits.

Layer one is the database role. Create a dedicated reader with no write grants, a statement timeout, and read-only transactions:

CREATE ROLE rag_reader LOGIN PASSWORD 'rotate-me';
GRANT CONNECT ON DATABASE analytics TO rag_reader;
GRANT USAGE ON SCHEMA public TO rag_reader;
GRANT SELECT ON orders, customers, products, payments TO rag_reader;
ALTER ROLE rag_reader SET default_transaction_read_only = on;
ALTER ROLE rag_reader SET statement_timeout = '8s';

Grant SELECT on views, not base tables, when columns need masking: expose customers_masked with the email hashed and the PII columns dropped, and leave those columns out of the schema card entirely. What the model never sees, it can never leak. For multi-tenant databases, enforce tenant isolation with row-level security keyed to a session variable that your application sets. The tenant filter must never be something the model writes.

Layer two is static validation. Parse the generated SQL with sqlglot and reject anything that is not a single SELECT over allowed tables, then force a row cap:

import sqlglot
from sqlglot import exp

ALLOWED_TABLES = {"orders", "customers", "products", "payments"}

def validate_sql(raw_sql: str, dialect: str = "sqlite") -> str:
    statements = sqlglot.parse(raw_sql, dialect=dialect)
    if len(statements) != 1:
        raise ValueError("exactly one statement required")
    tree = statements[0]
    if not isinstance(tree, exp.Select):
        raise ValueError("only SELECT is allowed")
    for node in tree.find_all(exp.Table):
        if node.name.lower() not in ALLOWED_TABLES:
            raise ValueError(f"table not allowed: {node.name}")
    if not tree.args.get("limit"):
        tree = tree.limit(500)
    return tree.sql(dialect=dialect)

Layer three is the repair loop. Generated SQL fails sometimes: a misspelled column, a bad cast, an ambiguous reference. Feeding the exact database error back to the model fixes a large share of failures on the first retry. Cap it at two repairs and fail gracefully:

def answer(question: str, conn, generate) -> dict:
    sql = generate(question)
    last_error = None
    for _ in range(3):  # one attempt plus two repairs
        if sql.strip() == "CANNOT_ANSWER":
            return {"ok": False, "reason": "not answerable from this schema"}
        try:
            safe_sql = validate_sql(sql)
            rows = conn.execute(safe_sql).fetchall()
            return {"ok": True, "sql": safe_sql, "rows": rows}
        except Exception as err:
            last_error = str(err)
            sql = generate(question, previous_sql=sql, error=last_error)
    return {"ok": False, "reason": f"query kept failing: {last_error}"}

Track how often the repair loop fires. A rising repair rate is an early warning that the schema card has drifted from the real schema.

Pattern 2: Schema Linking for Large Schemas

Direct text-to-SQL degrades as the schema grows, and not just because of context limits. Even when 800 tables technically fit in the window, distractor tables actively hurt: the model joins to orders_backup_2023 because it looked plausible. The fix is schema linking: retrieve the few tables that matter, then generate against only those.

Build two small retrieval indexes.

Table cards. One document per table: name, a one-paragraph description, column list with meanings, and representative values. Embed these. For very wide tables (the 400-column fact table every warehouse has), embed column descriptions individually as well, so retrieval can select columns, not just tables.
Answered examples. Pairs of (question, validated SQL) from your own logs. Embed the questions.

At query time, embed the incoming question, pull the top 5 to 8 table cards, expand the set with their foreign-key neighbors so joins remain possible, and pull the top 3 answered examples as few-shots. Include the join graph explicitly in the prompt as plain lines: orders.customer_id -> customers.id. Models join far more reliably when the edges are spelled out than when they must infer them from DDL.

The answered-examples index is the highest-leverage component in the whole system, and it is the core idea Vanna built a product around. Real validated queries teach the model your conventions: which status values mean "active", how fiscal quarters work, which of the three date columns is the one people mean. Cold-start it with 20 to 40 hand-written pairs covering the canonical joins, then grow it from production: every answer a user confirms or thumbs-up gets promoted into the index. Accuracy compounds as usage grows, which is a property vector RAG over documents never gave you.

Pattern 3: Semantic Layers and Verified Queries

Free-form SQL generation has a ceiling for business metrics, and it is lower than demos suggest. "Revenue" hides a dozen decisions: gross or net, refunds included, timezone, currency conversion date, whether test accounts count. A model that writes syntactically perfect SQL can still silently pick the wrong definition, and nobody notices until the number disagrees with the board deck.

The fix is to stop asking the model to write SQL for known metrics. Define the metric once, and let the model select and parameterize it. You can adopt a full semantic layer (dbt's Semantic Layer, Cube, or the warehouse-native options like Snowflake Cortex Analyst and Databricks Genie all ship this pattern now), or you can own a small registry of verified queries yourself:

name: revenue_by_month
description: Net revenue per calendar month in USD, refunds excluded, UTC.
params:
  - name: start_date
    type: date
  - name: end_date
    type: date
sql: |
  SELECT date_trunc('month', paid_at) AS month,
         SUM(amount_cents) / 100.0 AS net_revenue
  FROM payments
  WHERE status = 'settled'
    AND paid_at >= {{start_date}}
    AND paid_at < {{end_date}}
  GROUP BY 1
  ORDER BY 1

At runtime: embed each template's name and description, retrieve the best candidates for the question, and have the model emit only a template name plus a JSON object of parameters. Validate the parameters against the declared types (dates parse, enums come from an allowlist), render, execute. The model never touches the SQL text, so the metric definition cannot drift.

The tradeoff is coverage. Verified queries answer the questions you anticipated; users ask others. The standard resolution is a two-tier answer policy: template match runs as trusted, no template match falls through to guarded text-to-SQL and the answer is labeled as best-effort. In customer-facing products, skip the fallback and refuse instead. A wrong number shown confidently to a customer costs more than a refusal.

Pattern 4: Rows as Documents, Done Right

Embedding structured rows is the wrong default, but it is the right tool for two specific jobs.

Entity lookup and description. "Tell me about the Acme account" is not an aggregation. It is a fetch-and-summarize over one entity's row plus its related text.
Semantic search over text columns. Support tickets, product reviews, item descriptions: the text column is genuinely unstructured, and similarity search is the correct retrieval for it.

Two rules make this pattern work. First, serialize rows as sentences, not JSON dumps. Embedding models produce noticeably better neighborhoods for natural phrasing than for key=value soup:

Acme GmbH is an enterprise customer in Germany on the Scale plan,
214 seats, active since 2023-04-12, account owner: Priya Nair.
metadata: {"customer_id": 8112, "country": "DE", "plan": "scale", "seats": 214}

Second, keep IDs, numbers, and categorical fields in metadata, and filter on them structurally. The self-query pattern applies here: the model extracts a filter object (plan = "scale", country = "DE") plus a semantic query string, the vector store applies the hard filter, and similarity ranks only within the filtered set. Every serious vector store supports metadata filtering; use it instead of hoping the embedding encodes "Germany".

Keep the index fresh with change data capture or an updated_at watermark so you re-embed only changed rows. And hold the line on scope: the moment a question needs counting, summing, or comparing across rows, it belongs to SQL, which is what the router is for.

Routing Questions Between SQL and Vectors

Once you run both a SQL path and a vector path, you need a router. A small, fast classification prompt is enough:

Classify the question. Reply with one word.

sql    -> needs counts, sums, filters, rankings, or exact records
vector -> asks about the content of documents, tickets, or reviews
both   -> needs numbers plus an explanation that lives in text
refuse -> not answerable with our data

Question: {question}

The both label earns its place. "Why did churn spike in May?" needs SQL to establish that churn actually spiked and by how much, and vector retrieval over cancellation reasons and support tickets to explain why. Run both branches, then synthesize a single answer that cites numbers from the query result and quotes evidence from the documents.

In tool-using agents, routing often happens implicitly: expose query_database and search_documents as separate tools with sharp descriptions and let the model choose. That works with current frontier models, but log the choice either way. Tool-selection accuracy is a metric, and it degrades quietly when tool descriptions drift or new tools crowd the list.

A Reference Architecture for RAG Over Structured Data

Putting the pieces together, the request path that works looks like this:

Receive the question, attach conversation context (a follow-up like "and for Q2?" needs the prior question resolved before generation).
Route: sql, vector, both, or refuse.
Retrieve context for generation: table cards and foreign-key edges, top answered examples, candidate verified templates.
Generate: a verified-template call when one matches, guarded text-to-SQL otherwise.
Validate statically: single SELECT, allowed tables, row cap injected.
Execute with the read-only role and statement timeout.
Repair on error, at most twice, feeding the exact database error back.
Synthesize the answer from the result set, and show your work.
Log everything: question, SQL, latency, row count, route, repairs, user feedback.

Synthesis deserves care. Pass the model a compact rendering of at most a few dozen rows; if the result is larger, aggregate or truncate and say so in the answer, keeping the full result available as a download. Always display the executed SQL (or the template name and parameters) alongside the answer: analysts trust numbers they can audit, and showing the query turns "the AI said 4.2 million" into "this query returned 4.2 million". Instruct the synthesis prompt to distinguish an empty result from a zero value, and to state the time window and filters it actually used.

Two production notes. Cache aggressively: an exact-match cache on normalized question plus a semantic cache mapping similar questions to validated SQL cuts both latency and warehouse spend, and warehouses bill per query. And treat query results as untrusted content during synthesis: a support ticket stored in the database can contain prompt-injection text, so wrap retrieved values in clear delimiters and instruct the model to treat them as data, never as instructions.

Evaluating RAG Over Structured Data

Text-to-SQL has a property most LLM tasks lack: you can score it objectively. Two different SQL strings are equivalent if they return the same result, so evaluate execution accuracy, not string similarity. Run the gold query and the generated query, compare result sets order-insensitively with float tolerance:

def same_result(a: list[tuple], b: list[tuple], ndigits: int = 6) -> bool:
    def norm(rows):
        out = []
        for r in rows:
            out.append(tuple(
                round(v, ndigits) if isinstance(v, float) else v
                for v in r
            ))
        return sorted(out, key=repr)
    return norm(a) == norm(b)

Build a golden set of 50 to 150 questions drawn from real logs, and stratify it deliberately: simple lookups, filtered aggregates, multi-table joins, time-window math, null handling, and, critically, unanswerable questions. The unanswerable stratum measures whether the system refuses correctly instead of hallucinating a plausible query over the wrong table, which is the most damaging failure mode in front of executives.

Track a small dashboard per release: validity rate (generated SQL parses and passes the validator), execution accuracy against gold results, repair-loop usage, refusal precision and recall, and p95 latency. Re-run the suite on every schema migration, prompt edit, and model upgrade; all three break text-to-SQL in ways unit tests never catch.

Public benchmarks like Spider 2.0 and BIRD are useful for calibrating expectations, mainly because they show that enterprise-style questions over messy schemas remain far from solved even for frontier models. Treat vendor demo accuracy with skepticism. The only number that matters is accuracy on your schema, your dialect, your questions.

Failure Modes You Will Hit, and the Fixes

Date math goes wrong first. Fiscal versus calendar years, week start days, timezone boundaries. Put the timezone and fiscal rules in the schema card as explicit lines, and make date questions the largest stratum in your golden set.
Ambiguous metric names. "Revenue" and "active users" mean different things to different teams. Add a definitions block to the prompt, or move those metrics to verified templates.
Enum and format mismatches. The model filters on 'Completed' when the data says 'settled'. Sample rows and enum hints in the schema card eliminate most of these.
Empty results narrated as facts. The query returns zero rows and the model announces "there were no sales in Q1", when actually the status filter was wrong. Have synthesis state that the query returned no rows and surface the filters used, so a human can spot the bad predicate.
Cartesian joins. A missed ON clause turns 10 thousand rows into 100 million and eats the statement timeout. Spelled-out foreign-key edges prevent most cases; the row cap and timeout contain the rest.
LIMIT in the wrong place. Injecting a limit inside a subquery changes aggregate semantics. Apply the cap only to the outermost SELECT, which is what the sqlglot approach above does.
Dialect drift. Functions from the wrong engine. Pin the dialect in the prompt, validate with the same dialect, transpile when you must cross engines.

Where to Start

Do not build all four patterns at once. Start with pattern 1: a schema card, the three-layer guardrails, and the repair loop, pointed at a read-only replica. That is a weekend of work and it already answers real questions. Then instrument it, because the logs tell you what to build next: recurring questions become verified templates, misses become golden-set entries and few-shot examples, and schema growth tells you when to add linking. RAG on structured data rewards this incremental path, because unlike document RAG, every validated answer makes the system measurably better.

FAQ

Is text-to-SQL the same as RAG over structured data?

Text-to-SQL is the core retrieval mechanism inside it. The full RAG loop is: translate the question to a query, execute it, then generate an answer grounded in the result set. Retrieval-augmented generation still describes the shape exactly; only the retriever changed from a vector index to a database engine.

Should I embed my database rows?

Only text-heavy columns, and only for lookup or semantic-search questions. Embeddings cannot aggregate, go stale on every update, and cost money at row scale. If the question involves counting, summing, ranking, or comparing, generate a query instead.

How do I handle a schema with hundreds of tables?

Schema linking. Embed per-table cards and retrieve the top handful per question, expand with foreign-key neighbors, and add few-shot examples from validated past queries. Do not stuff the full schema into the context window; distractor tables reduce accuracy even when they fit.

Which model should I use for text-to-SQL in 2026?

Any current frontier model handles mainstream SQL dialects well, and the gap between models is smaller than the gap between good and bad schema context. Invest in the schema card, examples, and guardrails first, then benchmark two or three models on your own golden set. For high-volume internal workloads, a small fine-tuned model behind the same guardrails can win on cost.

How do I stop the model from modifying or deleting data?

Database permissions, not prompts. Connect through a role with SELECT-only grants, read-only transactions, and a statement timeout, and validate the AST to reject anything but a single SELECT. Prompt instructions are a courtesy; the grant table is the control.

Does this work for NoSQL, APIs, or spreadsheets?

The pattern generalizes: generate the target query language (Mongo aggregation pipelines, Elasticsearch DSL, GraphQL, even pandas over a loaded sheet), validate it, execute with least privilege, ground the answer in the result. It works best where a real schema exists to put in the prompt. For ad-hoc CSVs, loading them into DuckDB and running the SQL patterns above beats bespoke dataframe generation.

How do I protect PII in a structured data RAG system?

Expose masked views instead of base tables, leave sensitive columns out of the schema card entirely, and enforce tenant isolation with row-level security bound to the application session, never to model-generated SQL. The model cannot leak a column it has never seen.