Why can't RAG reliably answer 'how many orders were refunded last month?'

Retrieval returns a few passages; the answer requires counting across all records, which no small set of chunks contains. A query over the data is needed.

What safeguards does text-to-SQL need?

Read-only credentials on a replica, allowlisted tables and columns, query timeouts and row limits, and showing the generated query for transparency.

How should a system handle 'summarise this 40-page contract'?

Process the whole document, either in a long context window or by summarising sections and combining them, rather than retrieving a few chunks.

When RAG Is the Wrong Tool — RAG in Production

Scenario

Example company: an internal analytics assistant indexes order records, support tickets and reports as text chunks and answers everything with RAG.

What you see in production

"How many orders were refunded last month?" gets confident, wrong numbers, different each time. "Summarise this 40-page contract" misses sections. Questions about the employee handbook, on the other hand, work well.

The naive code

def answer(question):
    chunks = search(question, k=5)            # five refund records out of 3,000
    return llm(question, chunks)              # "about 5 refunds last month"

Reproduce it

An aggregate question answered from the top 5 chunks versus from a query over the data:

Top-k chunks cannot count; a structured query canTry it yourself

import random
random.seed(3)
orders = [{"id": i, "month": random.choice(["2026-09", "2026-10"]), "status": random.choice(["paid"] * 9 + ["refunded"])}
          for i in range(3000)]
chunks = [f"Order {o['id']} in {o['month']} was {o['status']}." for o in orders]

top5 = [c for c in chunks if "refunded" in c and "2026-09" in c][:5]     # what retrieval can show the model
print("RAG sees", len(top5), "refund records, so it answers: about", len(top5))
print("SQL-style count:", sum(o["status"] == "refunded" and o["month"] == "2026-09" for o in orders))

Output

RAG sees 5 refund records, so it answers: about 5
SQL-style count: 145

Root cause

RAG retrieves a few passages that look relevant. That suits questions answered by a specific piece of text. It cannot answer questions that need all the data (counts, sums, trends) or the whole of one long document (a full summary), because the answer is not in any five chunks.

The fix

Route each question to the right method:

Lookup in text (policies, docs, how-to): RAG.
Aggregates and filters over records (counts, totals, top N): a tool that queries the database, for example a parameterised query tool or carefully constrained text-to-SQL against a read-only replica, with the result returned to the model.
Whole-document tasks (summarise, compare two contracts): load the full document into a long context, or summarise section by section and combine.
Mixed questions: an agent with both a search tool and a query tool chooses per step.

tools = [search_docs_tool, run_report_tool]          # run_report: fixed, parameterised analytics queries
# "How many refunds last month?" -> run_report(metric="refunds", period="2026-09")

Trade-offs

Text-to-SQL is powerful but risky: use read-only credentials, allowlisted tables, query timeouts and row limits, and show the query used.
Long-context processing of whole documents costs more per question; cache summaries for frequently used documents.
Routing adds a step that can be wrong; evaluate routing decisions on a labelled set of question types.

Detect it in production

Questions with numbers in the answer but no tool call: likely guessed aggregates.
Answer variance for the same question (different numbers on repeated runs).
User feedback grouped by question type.

Key points

RAG answers questions whose answer is in a few passages.
Counts, totals and trends need queries over the data; whole-document tasks need the whole document.
Route by question type and give agents both search and query tools.

Further reading: Tool use with Claude · Long context prompting tips · OWASP SQL injection prevention