RAG vs Text-to-SQL for Structured Data

If your question is "how many," "what's the total," or "which rows match," don't retrieve — compute. RAG retrieves semantically similar text; it cannot reliably count, sum, filter, or join. For structured data, the correct pattern is text-to-SQL (or tool/function calling): the LLM generates a query, a real database engine executes it, and the answer is exact. Use RAG only when the answer lives inside unstructured passages.

Quick Answer: RAG embeds and fuzzy-matches text — it's the wrong tool for "compute over rows" questions like counts, sums, and joins. Use text-to-SQL or tool/function calling to generate a real query against structured data instead. Reserve RAG for "find a passage" questions over unstructured documents.

Why RAG Breaks on Structured Data

RAG answers "find the passage most similar to this question" — it was built for unstructured text, not aggregation over rows. When a table gets chunked and embedded, each row (or batch of rows) becomes an isolated vector with no awareness of the other 9,999 rows sitting beside it in the database.

Ask a RAG pipeline "how many customers churned last quarter?" and it does not count anything. It retrieves the chunks whose embeddings are closest to your question's embedding — likely a handful of rows that mention "churned" or "quarter" — and asks the LLM to eyeball an answer from that fragment. The LLM then does what LLMs do when starved of ground truth: it produces a fluent, confident number that may have no relationship to the real count.

This isn't a tuning problem you fix with a better embedding model. Semantic similarity and aggregation are different operations entirely — one measures "how alike are these two pieces of text," the other measures "what is true across the full set." No amount of top-k retrieval tuning bridges that gap, because the retrieval step never sees the whole set to begin with.

The Failing Example: RAG on a Table

Take a orders table with 50,000 rows and ask: "What's the total revenue from orders placed in March, excluding refunds?" A RAG system embeds row chunks, retrieves the ~10 chunks most semantically similar to your question (probably rows containing the literal words "March" or "revenue"), and passes them to the LLM as context.

The LLM then sums whatever fragment it was handed — not the true total. If March had 4,200 orders and retrieval surfaced 10, the "answer" is a guess dressed up as a number. Refund exclusion compounds the failure: it's a filter condition, and filters require scanning every row against a predicate, not similarity ranking.

Contrast that with a generated SQL query:

SELECT SUM(amount) FROM orders
WHERE order_date BETWEEN '2026-03-01' AND '2026-03-31'
AND status != 'refunded';

This scans the actual rows, applies the actual predicate, and returns the actual total. There's no "top-k" approximation because a database engine doesn't approximate SUM() — it computes it. This is the core distinction Prodinja's own RAG knowledge complete guide keeps coming back to: retrieval and computation are not interchangeable, no matter how the demo looks.

Semantic Retrieval vs. Generated SQL vs. Tool Calling

These three patterns solve different problems and are frequently confused because all three sit an LLM in front of a data source. Knowing which one you're actually building determines whether your "AI feature" is trustworthy or a plausible-sounding random number generator.

Pattern	What it does	Good for	Fails at
Semantic retrieval (RAG)	Embeds text, retrieves nearest neighbors by vector similarity	Finding a passage, policy, or precedent in unstructured docs	Counts, sums, joins, exact filters, "all rows matching X"
Text-to-SQL	LLM generates a SQL query from natural language; a real DB engine executes it	Aggregations, filters, joins, exact lookups over structured data	Ambiguous schema, fuzzy/unstructured requests ("find angry reviews")
Tool/function calling	LLM decides which pre-defined function/API to invoke, with what arguments	Actions, live lookups, calling internal APIs, guarded computations	Requires the function to already exist and cover the request

Semantic retrieval and text-to-SQL aren't rivals fighting for the same job — they're specialists. A support-ticket search tool needs RAG. A "how many tickets are open by priority" dashboard needs text-to-SQL. Most real products need both, wired to different questions.

Where Tool Calling Fits

Tool/function calling is often the more production-safe cousin of text-to-SQL: instead of letting the LLM emit arbitrary SQL against a live database, you expose a fixed set of vetted functions (get_monthly_revenue(month, exclude_refunds)) and the LLM just picks one and fills in arguments. You trade flexibility for guardrails — no injection risk, no runaway query, no accidental DROP TABLE.

The tradeoff is coverage. Tool calling only answers questions your engineers anticipated and wrapped in a function. Text-to-SQL, properly sandboxed (read-only credentials, row limits, query timeouts), answers questions nobody thought to pre-build — at the cost of needing more rigorous validation before execution. Anthropic's own guidance on tool use recommends narrow, well-described function signatures over open-ended query generation whenever the action space is knowable in advance, precisely because it removes a whole class of runtime failure.

The Decision Rule: "Find a Passage" vs. "Compute Over Rows"

Ask one question before building anything: is the answer a specific piece of text that already exists somewhere, or is it a number/set derived by scanning many rows? If it's the former, retrieve. If it's the latter, generate a query or call a function.

"What does our refund policy say about digital goods?" → Find a passage → RAG.
"How many refunds did we issue for digital goods last month?" → Compute over rows → text-to-SQL.
"Summarize the top complaints in support tickets this week." → Arguably both: retrieve relevant tickets (RAG), then have the LLM summarize the retrieved text — not compute a number.
"Which customers are eligible for a loyalty discount?" → Compute over rows against a filter condition → text-to-SQL or a tool call, never retrieval.

A useful gut-check phrase from working data teams: if you'd normally write a WHERE clause, a GROUP BY, or a JOIN to answer it by hand, don't ask an embedding to do it for you. Embeddings measure closeness in meaning space; SQL measures truth in a dataset. Confusing the two is the single most common failure mode teams building "chat with your data" features run into, and it's the same failure mode Prodinja's guide to the four product decisions RAG forces flags as a scoping mistake made before a single line of retrieval code is written.

A Related Trap: Chunking Doesn't Save You

Some teams respond to bad RAG-on-tables results by tuning chunk size — bigger chunks, row-per-chunk, table-per-chunk. This treats a category error as a tuning problem. Chunking strategy genuinely matters for retrieval quality over prose (see Prodinja's guide to chunking strategy and retrieval quality), but no chunk boundary makes a vector database capable of SUM(). If the underlying need is aggregation, better chunking is optimizing the wrong layer entirely.

Building Text-to-SQL Safely

Text-to-SQL done carelessly is a security incident waiting to happen; done deliberately, it's a reliable pattern used in production analytics tools today. The gap between those two outcomes is entirely in the guardrails, not the core idea.

Non-negotiable safeguards:

Read-only database credentials for the LLM-facing connection — no INSERT, UPDATE, DELETE, or DROP permissions at the database-user level, enforced by the DB, not by prompt instructions.
Schema grounding, so the model generates queries against your real table and column names rather than hallucinating a plausible-sounding schema — this is exactly what a documented, current data model gives you.
Query validation before execution — parse the generated SQL, reject anything outside an allow-listed set of statement types, and cap row limits and timeouts so one bad query can't scan a billion-row table.
Human-readable query surfaced back to the user, so a technical PM or analyst can sanity-check what actually ran instead of trusting a black-box number.
A fallback for ambiguity — when the natural-language question maps to more than one plausible query (e.g., "active" could mean three different status columns), the system should ask a clarifying question rather than silently picking one interpretation.

The MIT-and-industry-adjacent Spider benchmark for text-to-SQL (a widely cited academic benchmark spanning thousands of complex, cross-domain natural-language-to-SQL pairs) is a useful reality check here: even strong models still show a meaningful accuracy gap on complex multi-join queries versus simple single-table lookups. Directionally, that means text-to-SQL earns the most trust on well-scoped, well-documented schemas — and degrades on ambiguous or sprawling ones, which is a schema-quality problem more than a model problem.

Schema Quality Is the Real Bottleneck

A text-to-SQL system is only as reliable as the schema it's grounded in — vague table names, undocumented columns, and inconsistent types produce vague, wrong, or inconsistent queries no matter how capable the underlying model is. This is the part teams skip because it feels like "just documentation," and it's the part that actually determines whether the feature ships.

What a well-grounded schema needs:

Descriptive table and column names (customer_id beats cid, order_status beats flag2).
Explicit foreign-key relationships the model can use to generate correct JOINs instead of guessing.
Documented enum values (what does status = 3 actually mean?) so the model maps natural language to the right literal.
Consistent types and naming conventions across tables, so a created_at in one table isn't a date_created in another.

This is precisely where Prodinja's Data Modelling tool sits in a broader workflow: it turns entities you define into real SQL DDL — actual CREATE TABLE statements with typed columns and relationships, not a diagram that stays disconnected from the database. A cleanly generated schema is the structured surface a text-to-SQL layer needs to ground against, the same way Prodinja's API Designing tool turns endpoints into callable specs — the concrete, structured target that tool/function calling reaches for instead of asking an embedding to fuzzy-match its way to an answer. Neither tool runs live inference against your production data inside the prototype today; they're where the schema and interface design work that a real text-to-SQL or tool-calling layer depends on actually happens.

Key Takeaways

RAG answers "find a passage"; it does not answer "compute over rows." Counts, sums, filters, and joins need a real query engine, not vector similarity.
Text-to-SQL generates a real query executed by the database, so aggregations return exact answers instead of LLM guesses over a retrieved fragment.
Tool/function calling trades flexibility for safety — a fixed set of vetted functions removes injection and runaway-query risk, at the cost of only covering anticipated questions.
The decision rule is simple: if you'd write a WHERE, GROUP BY, or JOIN by hand, don't ask an embedding to approximate it.
Chunking strategy can't fix a category error — better chunk boundaries improve prose retrieval, not a vector database's ability to aggregate.
Schema quality is the real bottleneck for text-to-SQL accuracy — descriptive names, documented relationships, and consistent types matter more than model choice.
Guardrails (read-only credentials, query validation, row limits) are non-negotiable before any generated-SQL layer touches production data.

Frequently Asked Questions

Is text-to-SQL better than RAG for analytics questions?

Yes, for questions that require counting, summing, filtering, or joining rows — text-to-SQL executes a real query against the full dataset, while RAG only reasons over a small retrieved sample and cannot guarantee a correct aggregate.

Can RAG ever work on structured data?

RAG can work when the structured data is being searched for a specific matching row or description (e.g., "find the product that matches this description"), not when the goal is a computed aggregate — that distinction is the whole decision rule.

Is text-to-SQL safe to expose to end users?

It can be, with read-only database credentials, query validation before execution, row/timeout limits, and a schema the model is well-grounded in — without those guardrails, it's a real injection and runaway-query risk.

What's the difference between text-to-SQL and tool calling?

Text-to-SQL lets the model generate arbitrary queries against a database (more flexible, more validation needed); tool calling restricts the model to a pre-defined, vetted set of functions (safer, but only covers anticipated questions).

Why do RAG demos on tabular data look convincing but fail in production?

Small demo tables often let a handful of retrieved rows accidentally cover the whole answer; at production scale with thousands of rows, the retrieved sample stops being representative and aggregate answers become unreliable guesses.