Why LLMs should write SQL, not guess KPIs from retrieved chunks.
The first time I hooked up a RAG pipeline over a product catalog, a stakeholder asked the system a simple question:
"What's the average price of black dresses from Zara in the last 30 days?"
The pipeline returned a number. The number looked precise. It was wrong.
Not slightly wrong but perhaps wrong enough that the stakeholder was about to put it in a client report.
I caught it by accident, refreshing my eval set. An hour of digging later, I understood what had happened. The retriever had pulled the top 12 most relevant product chunks. The LLM had dutifully averaged the prices in those 12 chunks. There were more than 100 matching products in the database. The "average" was an average of a sample biased by whatever the embedding similarity happened to surface first.
This is the problem nobody mentions in the RAG tutorials.
Where it breaks
RAG works beautifully for interpretation problems — questions where the answer is already written down somewhere in your knowledge base:
"What does our return policy say about international shipments?"
"Which products in our catalog match a 'minimalist tailored' aesthetic?"
"Summarize the key arguments in this 80-page contract."
The LLM's job is to find the relevant text, read it, and rephrase it. The math, if any, was done by humans who wrote the source documents.
The moment a question requires computation over the full dataset, RAG breaks. In a specific and predictable way.
Three failure modes I've seen in production:
1. Sample bias. Retrieve top-K, compute over K, present as if it's the population. Your "average" is whatever the vector index happened to surface — usually skewed toward whatever the query embedding was close to.
2. Phantom precision. The LLM doesn't know it's working from a sample. It returns "$47.23" with all the confidence of a database query, when the real average is $52.80 across the full table.
3. Aggregation drift. Ask for "total revenue last quarter" and the LLM might pull 8 sales-related documents, sum the dollar figures it sees in the text, and return a number that has no relationship to actual total revenue. Some of those figures might be projections, others discounts, others single line items from larger invoices.
RAG retrieves text. Arithmetic over text is a category error.
The pattern that works
Don't make the LLM compute. Make the LLM delegate.
User question (natural language)
↓
LLM parses intent → generates SQL
↓
Database executes SQL → returns exact result
↓
LLM explains the result in plain EnglishApplied to the Zara question, the LLM doesn't try to compute the average. It writes:
sql
SELECT AVG(price) AS avg_price, COUNT(*) AS n
FROM products
WHERE brand = 'Zara'
AND color = 'black'
AND category = 'dress'
AND sold_at >= NOW() - INTERVAL '30 days';The database does the work it's been engineered for fifty years to do. The LLM does the work it's good at: understanding what the human asked, mapping it to a schema, and explaining the answer back in plain English.
Two side benefits:
The
COUNT(*)lets you tell the user "average over 137 dresses" — phantom precision goes away.The query is auditable. If the answer looks wrong, you can read the SQL. You cannot read a vector retrieval and a generation step the same way.
Hybrid is the real production answer
The dichotomy "RAG vs. SQL" is a teaching tool, not an architecture. Real systems blend both.
A question like "Show me reviews of our top 5 best-selling dresses last quarter" needs both:
SQL to compute "top 5 best-selling dresses last quarter" — a ranking over structured data.
RAG to retrieve the actual review text for those 5 products — interpretation over unstructured text.
The orchestration matters. SQL runs first to identify the entities; retrieval runs second, scoped to those entity IDs. An agent (LangGraph, a state machine, whatever you prefer) decides which path to take.
The decision rule I use:
Question requires aggregation, ranking, or counting? → SQL
Question requires interpretation, summarization, or matching meaning? → RAG
Question requires both? → SQL filters the scope, RAG handles the unstructured part
A note on tooling
You probably shouldn't write the text-to-SQL prompts yourself. Worth knowing:
Vanna AI — open-source text-to-SQL with retrieval-augmented prompting over your schema and example queries.
Semantic layers (Cube, dbt Semantic Layer, MetricFlow) — define metrics once in a layer above your DB and let the LLM generate queries against the semantic layer rather than raw SQL. This is the right move the second you have more than two BI consumers, because it answers "what does revenue mean exactly?" once, in one place, instead of every time someone asks.
LangChain SQLDatabaseChain / LlamaIndex NLSQLTableQueryEngine — fine for prototypes. Breaks under any real schema complexity.
The trap most teams fall into: they build a custom NL-to-SQL pipeline, hit the first hard schema case, and bolt on retry logic instead of stepping back to use a semantic layer.
The takeaway
LLMs are excellent translators and explainers. They are bad calculators.
Build your pipeline to play to that asymmetry:
Let the LLM generate the query.
Let the database execute it.
Let the LLM narrate the result.