Retrieval Over Structured Data With Text-to-SQL

The situation

The assistant answers business questions for a finance and operations team. Some of those questions are genuinely about documents, what the refund policy says, how the reconciliation runbook handles a mismatch, and a vector-backed knowledge base serves them well. But a growing share look nothing like that. “What was total revenue by region last quarter?” “How many subscribers churned in July, split by plan tier?” “Which ten accounts have the largest outstanding balance?” The answers to those live in a Redshift warehouse and a set of RDS tables, not in any document.

The first build embedded each row of the sales fact table as a short text string, “region: EMEA, quarter: Q2, amount: 4211.55”, and dropped the embeddings into the same vector index as the documents. It demos, then it lies. Ask for total revenue by region and the retriever returns the ten rows most textually similar to the phrase “total revenue by region”, which is not the ten largest, not a sum, and not grouped by anything. The number the model then reports is confabulated from whatever handful of rows came back.

The schema is stable and well understood. There are a few dozen tables with clear semantics, primary and foreign keys, and a data team that can describe every column. The question is how to point natural language at that structure and get an answer that is actually computed, not retrieved by resemblance.

What actually matters

The load-bearing distinction is whether the answer is a fact you can retrieve or a value you have to compute. “What does the refund policy say about prorated charges?” is a fact: it exists verbatim somewhere, and similarity search finds the passage that contains it. “What was total revenue by region last quarter?” is a computation: the answer exists nowhere until you filter to last quarter, group by region, and sum. Embeddings encode semantic resemblance, and resemblance has no arithmetic. There is no vector operation that sums a column, joins two tables, or ranks by an aggregate. Ask a similarity index a question whose answer is a SUM ... GROUP BY and the best it can do is hand back rows that read like the question.

Once the answer is a computation, the natural home is the engine already built to compute it. The relational database and the warehouse do joins, aggregates, window functions, and precise filters as their day job, exactly, every time, with the freshest data as of the query. The generative model’s role shifts. It stops being the thing that produces the answer and becomes the thing that produces the query: it reads the question, reads an accurate description of the schema, and emits SQL. The database runs the SQL and returns rows. The model may then summarise those rows into prose, but the numbers came from the engine, not the model.

That shape, text to SQL, changes what you have to worry about. Retrieval quality is now query correctness: does the generated SQL express the question, against the right tables, with the right joins and filters? Grounding is now schema grounding: the model can only write a correct query if it has been told what the tables and columns mean. And a new concern appears that pure vector RAG never had, because you are now executing model-generated code against a live database. Safety of execution moves to the centre. A retrieval that returns a wrong passage is embarrassing; a generated query that drops a table or scans the entire warehouse unbounded is an incident.

Freshness and precision usually tip the same way. Structured questions tend to want the current number to the penny, not an embedding captured whenever the row was last indexed. Running SQL at question time reads live data; an embedded-row index is a stale snapshot that has to be re-embedded on every change. If the question is “what is the balance right now”, the index is answering “what was the balance when we last reindexed”.

None of this retires vector search. Plenty of questions really are about documents, and for those, text to SQL has nothing to compute. The mature design routes: decide per question whether it is a metric or a fact, send it down the matching path, and combine when a question needs both.

What we’ll filter on

1. Answer type: is the answer a fact retrievable by similarity, or a value that must be computed by aggregation or join?
2. Schema stability: is there a known, describable schema for the model to target, or is the data shapeless text?
3. Precision and freshness: does the answer need to be exact and current, or is a close semantic match acceptable?
4. Execution safety: can generated queries be constrained to read-only, scoped to allowed tables and columns, and capped on rows and cost?
5. Build versus buy: does a managed service generate and run the SQL, or does the design need custom tool calling to keep control of execution?

The retrieval landscape

1. Plain vector RAG over embedded rows. Each row serialised to text, embedded, indexed. Correct for finding a specific row that resembles a description (“the account for the customer who complained about late deliveries”). Wrong for anything aggregate or precise, because similarity cannot sum, join, or rank by a computed value. On the landscape mainly to name the failure mode this whole post is about.

2. Amazon Bedrock Knowledge Bases with structured data retrieval. A Knowledge Base can be backed by a structured data source rather than documents. You point it at a source such as an Amazon Redshift warehouse or tables catalogued in the AWS Glue Data Catalog and queried through Amazon Athena. At query time it generates SQL from the natural-language question, runs it against the source, and returns the result, optionally with a natural-language summary. The RetrieveAndGenerate style call handles question to SQL to answer; a retrieve-only mode returns the generated SQL and rows so you can inspect or post-process them. Grounding comes from the schema plus any descriptions and curated query examples you supply.

3. Do-it-yourself text to SQL with function and tool calling. The model is given a tool such as run_sql_query, and its description carries the schema, the column semantics, and the rules. The model calls the tool with a generated query; your code, not the model, executes it against Athena or RDS under a role and connection you control, then feeds the rows back into the conversation. More plumbing than the managed path, and more control over exactly what runs and how it is validated before it runs.

4. Hybrid routing over both. A classifier or a router prompt decides whether an incoming question is a metric or a document question, sends metrics to text to SQL and documents to vector RAG, and merges the results when a question needs both (“summarise last quarter’s revenue and quote the policy that governs regional pricing”). This is where most real systems end up.

5. Pre-computed metrics and semantic layers. Not generative at all: a curated set of named metrics or a BI semantic layer that the model selects from rather than authoring raw SQL. Narrower, safer, and only as flexible as the metrics someone defined ahead of time. Worth naming because it is the low-risk alternative when free-form query generation is more power than the use case needs.

Side by side

Reading it for this situation, precise aggregates over a stable schema with live freshness, the embedded-row index is off the table for the metric questions, and the choice narrows to Bedrock Knowledge Bases structured retrieval or a hand-built text-to-SQL tool, wrapped in routing so the document questions still reach the vector path.

Two paths for one metric question

The picks in depth

Bedrock Knowledge Bases, structured data retrieval. The reason to reach for this first is that it removes the part that is easy to get subtly wrong: turning a question into correct SQL against your schema, running it, and coming back with an answer, without you writing the generation loop. You register a structured source (a Redshift warehouse, or tables exposed through the Glue Data Catalog and Athena), and the service handles question to SQL to result. Two things earn their keep. First, schema descriptions and curated example queries: the more accurately each table and column is described, and the more representative examples you provide, the better the generated SQL, this is the grounding, and it is where your effort goes. Second, the retrieve-only mode: instead of letting the service answer directly, you can have it return the SQL it generated and the rows it produced, so you can log the query, sanity-check it, or hand the rows to your own summarisation prompt. Access is governed by the permissions of the role the Knowledge Base uses against the source, so you constrain what can be read at the connection, not just in the prompt.

Do-it-yourself with tool calling. The reason to build it yourself is control over the exact moment of execution. The model is handed a tool whose description is the schema and the rules; it proposes a query; your executor validates and runs it. That seam is where every guardrail lives, and they are the same guardrails whichever path you choose, they are just yours to place explicitly here:

• Read-only role. The database credentials the executor uses grant SELECT and nothing else. No INSERT, UPDATE, DELETE, DROP. Even a perfectly generated query cannot mutate data, because the connection cannot. This is the single most important control, and it lives in IAM and database grants, not in the prompt.
• Allowed tables and columns. Restrict the surface to the tables the assistant is meant to answer from, through the grants on the read-only role and, ideally, a dedicated schema or a set of views that expose only those columns. Sensitive columns simply are not reachable.
• Row and cost caps. Enforce a LIMIT, a scan or byte-scanned ceiling (Athena and Redshift both expose ways to bound query cost), and a statement timeout, so a query that would scan the whole warehouse is stopped rather than billed.
• Validation and parameterisation. Parse the generated SQL and reject anything that is not a single read statement; block multiple statements, comments that smuggle a second query, and any DML or DDL keyword. Where the model supplies literal values, bind them as parameters rather than string-concatenating them into the query.
• Schema grounding. The model can only write a correct query if it knows what the columns mean. The tool description, or the retrieved schema context, carries table purpose, column semantics, units, and the join keys. An inaccurate or missing description is the most common cause of confidently wrong SQL.

Hybrid routing ties them together. A lightweight classifier, or the orchestrating model itself, tags each question as metric or document and dispatches accordingly. Metric questions become SQL and return computed numbers; document questions hit the vector index and return passages. A question that needs both fans out to both and the model composes the two results into one answer. The router is the piece that lets a single assistant answer “what is the refund policy” and “what did we refund last month” without pretending one engine can do both.

A worked example: total revenue by region last quarter

A user asks: “What was total revenue by region last quarter?”

Down the embedded-row path, the question is embedded and matched against the vector index. It returns the ten rows whose serialised text most resembles “total revenue by region last quarter”, perhaps ten arbitrary EMEA line items because “region” and “revenue” appear in them. The model sums those ten and reports a number. It is wrong by orders of magnitude, and nothing in the pipeline knows it.

Down the text-to-SQL path, the router tags the question as a metric. The model, grounded on the schema, generates a query against the sales fact table:

SELECT region, SUM(amount) AS total_revenue
FROM sales
WHERE sale_date >= DATE '2026-04-01'
  AND sale_date <  DATE '2026-07-01'
GROUP BY region
ORDER BY total_revenue DESC;

The executor validates it, a single SELECT, allowed table, bounded by date, under the read-only role, adds a LIMIT as a backstop, and runs it against Athena or Redshift. The engine returns one row per region with an exact sum over live data. The model turns those rows into a sentence: “Last quarter, EMEA led at 4.2M, followed by AMER at 3.1M and APAC at 1.8M.” Every number came from the warehouse. The model only did the translation at each end, question in, prose out, and touched none of the arithmetic in between.

The contrast is the whole point. Same question, same data, and the difference between a confabulated figure and an audited one is whether the answer was retrieved by resemblance or computed by a query.

What’s worth remembering

1. Ask whether the answer is a fact or a computation. Facts are retrievable by similarity; sums, joins, counts, and rankings are not. That single question decides the pattern.
2. Embedding a row as text cannot aggregate. Vector search has no arithmetic, so it returns rows that resemble the question, never the sum or the grouping the question asked for.
3. Text to SQL is the right pattern for structured questions. The model writes the query, the database computes the answer, and the model only translates language at each end.
4. Grounding is schema grounding. Accurate table and column descriptions, plus representative example queries, are what make generated SQL correct; a vague schema is the usual cause of confidently wrong queries.
5. Bedrock Knowledge Bases can retrieve over structured data. Point one at a Redshift warehouse or Glue Data Catalog tables through Athena, and it generates and runs the SQL for you, with a retrieve-only mode to inspect the query and rows.
6. You are executing generated code, so guard it. Run under a read-only role, scope to allowed tables and columns, cap rows and scan cost, set a timeout, and validate that only a single read statement runs.
7. The read-only role is the control that matters most. If the connection cannot mutate data, no generated query can, whatever the prompt says.
8. Prefer live queries to stale embeddings for precision and freshness. SQL at question time reads current data to the penny; an embedded-row index answers as of its last reindex.
9. Route, do not choose once. Send document questions to vector RAG and metric questions to text to SQL, and combine when a question needs both.
10. A semantic layer of named metrics is the low-risk alternative. When free-form query generation is more power than you need, let the model pick from curated metrics instead of authoring raw SQL.