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 gets the numbers wrong. 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

Everything turns on 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 it returns rows that read like the question.

Once the answer is a computation, the natural home is the engine already built to compute it. A relational database and a warehouse compute joins, aggregates, window functions, and precise filters exactly, every time, over the data as it stands when the query runs. The generative model’s job changes. Instead of producing the answer, it produces the query: it takes the question and a 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 generated SQL is only correct if the tables and columns are described accurately in the context. 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 need 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. Asked for the balance right now, the index returns the balance as it stood at the last reindex.

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 each question by type, metrics down the SQL path and facts down the vector path, and combines the two where 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 landscape

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. Listed to name the failure mode, not as a candidate.

Amazon Bedrock Knowledge Bases with structured data retrieval. A Knowledge Base can be backed by a structured data source rather than documents. Amazon Redshift is the query engine, Serverless or provisioned, and the supported data stores are Redshift itself and the AWS Glue Data Catalog, now surfaced as SageMaker Lakehouse, whose tables are reached through Redshift under Lake Formation grants. 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. RetrieveAndGenerate handles question to SQL to answer, Retrieve returns the rows alone, and GenerateQuery hands back the generated SQL without running it. Grounding comes from the schema plus any descriptions and curated query examples you supply.

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.

Hybrid routing over both. A classifier or a router prompt labels each incoming question as 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.

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.

Evaluation

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 solution

Bedrock Knowledge Bases, structured data retrieval. Reaching for this first 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 behind a Redshift query engine, Serverless or provisioned, with the data either native to Redshift or in Glue Data Catalog tables reached through it. That engine is the constraint worth planning around. The RDS tables in this scenario are not a supported store, so they reach the assistant either by landing in the warehouse, through zero-ETL replication into Redshift or an ordinary load, or down the hand-built path below.

Three configuration points carry most of the accuracy. Table and column descriptions, and curated queries that pair a natural-language question with its SQL, are the grounding, and that is where your effort goes. Inclusions and exclusions narrow the tables and columns the generator sees, though the documentation states plainly that they aid accuracy and are not a substitute for guardrails. A query timeout, executionTimeoutSeconds, bounds how long a generated query runs.

Two limits shape what you build around it. GenerateQuery returns the generated SQL without running it, so you can log it, check it, then run it yourself and hand the rows to your own summarisation prompt; its quota is 2 requests per second. And when RetrieveAndGenerate or InvokeAgent writes the prose answer, only 10 retrieved results reach the generation step, so a group-by across forty regions needs Retrieve plus your own formatting rather than the managed summary. Access is governed by the grants held by the role the Knowledge Base uses against the source, so you constrain what can be read at the connection, not 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 the controls live. They are the same controls whichever path you choose; here they are yours to place explicitly.

• 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 ceiling, and a timeout, so a query that would read the whole warehouse is cut off. An Athena workgroup takes one per-query data-scanned limit, from 10MB upwards, and cancels any query that crosses it, though a cancelled query is still charged for what it scanned first. Redshift aborts on a workload-management query monitoring rule such as scan_row_count or query_execution_time, and statement_timeout stops a statement outright.
• Validation and parameterisation. Parse the generated SQL and reject anything that is not a single read statement; block multiple statements, comments that hide a second statement, 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 the column semantics are in its context. 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.

Worked example

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 flags 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 AUD$4.2M, followed by AMER at AUD$3.1M and APAC at AUD$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.

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. 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.
3. 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.
4. Bedrock Knowledge Bases can retrieve over structured data. Redshift is the query engine, over data in Redshift or in Glue Data Catalog tables mounted into it, and GenerateQuery returns the generated SQL for inspection before anything runs.
5. The read-only role is the control that matters most. If the connection cannot mutate data, no generated query can, whatever the prompt says.