FDE Times dataFDE jobs open 873▼9 Change in open roles over 7 days, like-for-like: roles from companies we started tracking this week are left outNew in 7 days 55Companies hiring 181Remote-friendly 25%Median US pay $200kTop hirer Databricks 98
中文VI

The newspaper of the Forward Deployed Engineer

Guides

The RAG demo that answers everything until the CFO asks a question

Error codes need keyword search and aggregate figures need SQL, so sort the customer's questions before you choose a vector database.

ShareLinkedInFacebookX

In brief

  • Naive RAG looks fine in a demo but breaks in production, because enterprise questions are rarely just lookups.
  • Error codes, SKUs and version numbers need BM25. Questions worded differently from the documents need vector search. Aggregate-figure questions need SQL.
  • SQL that runs can still be wrong, so before returning a result, check the allowed tables, the required filters and the correct date field.
Stacked bar splitting 200 hypothetical customer questions into three types: 90 semantic lookups (vector), 60 with exact codes (BM25 + vector) and 50 aggregate-figure queries (text-to-SQL). The 50-question segment is shaded orange because it would fail by design with vector search alone.
A hypothetical example of 200 labelled support tickets. If all 200 went through a vector-only pipeline, the 50 aggregate-figure questions, a quarter of traffic, would be answered wrongly by design. Source: hypothetical example in the article.

In your second week on site, your RAG demo answers every question about the product documentation smoothly. Then the head of support types “What does TS-999 mean?” and the finance director asks for last month’s revenue by region. The system answers both with great confidence, and gets both wrong.

This scenario is not rare. One engineer who has built question-answering systems over structured data explains that naive RAG breaks in production because enterprise questions are rarely just lookups. Demos tend to consist entirely of lookup questions. Real users do not ask like that.

So the FDE skill here is not picking the best vector database. You need to classify the customer’s questions first, and only then choose a path for each type. It is an architectural decision that an engineer at headquarters struggles to make alone, because they do not have the list of real questions.

Three kinds of question need three different tools

The first kind contains an exact code. Anthropic, writing about contextual retrieval, gives an example: an embedding model may find content about error codes in general yet miss the exact passage containing “TS-999”. BM25 matches token by token, so it is strongest on SKUs, error codes, version numbers, people’s names and regulatory document numbers.

That strength is also its weakness. BM25 compares tokens; it does not understand meaning. If a customer asks “why is my account locked” and the documentation says “lockout policy”, BM25 finds almost nothing useful. Questions like that need vector search, which compares meaning rather than individual words.

The third kind is the most dangerous: aggregate questions such as “how many”, “total”, “by month”. No document passage contains the answer ready-made, so however good the retrieval, the model can only add up in its head a few figures scattered across chunks. The same engineer advises that aggregate questions should usually go through a step that runs SQL.

Question type How to spot it Tool What goes wrong with the wrong tool
Exact-code lookup Contains an error code, SKU, version number or proper name BM25 (often combined with vector) Vector returns a “similar-looking” document, not the exact code
Meaning-based lookup Customer uses different words from the documents Vector BM25 returns nothing or something off-topic
Aggregate figures “how many”, “total”, “by region”, “top” Text-to-SQL RAG produces a plausible-looking but invented number

A worked example with 200 questions

Suppose you export 200 real questions from a software company’s ticketing system and spend an afternoon labelling them. A hypothetical result: 90 meaning-based questions, 60 containing an identifier and 50 asking for aggregate figures.

If all 200 go into a single vector pipeline, the last 50 (a quarter of the traffic) fail by design, before embedding quality even comes into it.

The first router does not need to be sophisticated. Simple rules are cheap, easy to debug and easy to explain to the customer. Just remember to match on word boundaries: raw substring matching lets “top” falsely match “laptop” or “stop”.

import re

# Identifier patterns: TS-999, SKU-1042, v2.3...
IDENTIFIER = re.compile(r"\b[A-Z]{2,}-\d+\b|\bv\d+\.\d+")
# \b keeps word boundaries, so "top" does not match "laptop" or "stop"
AGGREGATE_TERMS = re.compile(
    r"\b(how many|total|average|by month|by region|top)\b",
    re.IGNORECASE,
)

def route(question: str) -> str:
    if AGGREGATE_TERMS.search(question):
        return "sql"
    if IDENTIFIER.search(question):
        return "hybrid"  # BM25 keeps the exact code, vector covers the meaning
    return "vector"

Later you can replace the rules with an LLM-based classifier. But keep the 200 labelled questions: they are the eval set for measuring the router, and the strongest evidence you have when the customer asks why the architecture has three branches.

Merge results by rank, not by score

For the hybrid branch you need to blend two result lists. BM25 scores and cosine scores sit on different scales, so simply adding them is wrong. Reciprocal Rank Fusion (RRF) solves this because it uses only ranks, not raw scores.

The formula: a document’s score is the sum of 1/(k + rank) across each list, where k is a smoothing constant. Try k = 60. Document A ranks first in BM25 and fifth in vector, so it scores 1/61 + 1/65 ≈ 0.0318.

Document B ranks first in vector but does not appear in BM25, scoring only 1/61 ≈ 0.0164. Documents that appear in both lists are pushed up, exactly as intended.

Anthropic has also published figures on combining keywords with vectors, though in a pipeline with more stages than ordinary hybrid search. Combining contextual embeddings with contextual BM25 cut failed retrievals by 49%; adding a reranking step cut them by 67%. The failure rate in the top 20 chunks fell from 5.7% to 1.9%.

But do not treat hybrid as gospel. On the WANDS e-commerce benchmark, an article by Denser found no statistically significant difference between BM25 and pure dense search (0.6983 versus 0.6953).

The highest-scoring hybrid version was one tuned by up-weighting product names. Results on someone else’s data are no substitute for measurement on your customer’s data.

SQL that runs can still return the wrong number

For the text-to-SQL branch there is a notable result from practice, and a trap that comes with it. One engineer reports that combining schema-aware retrieval with SQL execution raised accuracy on structured reporting questions from about 40% to about 75–80%.

This is an internal evaluation from a single source, so treat it as a signal rather than a benchmark.

The error this engineer describes is worth remembering: the model used the invoice creation date when the metric required the posting date. The SQL was valid, ran without error and produced a tidy number, and that number was wrong. No syntax check would have caught it.

So generated SQL must pass a validation layer before it reaches the user: does it use only permitted tables, and does it include every required filter? You can encode business definitions as rules.

The code below is only a sketch to illustrate the idea, not a production validator. It scans the raw string, so a comment containing the word posting_date would slip through; a real version should parse the SQL into a syntax tree before checking.

ALLOWED_TABLES = {"invoices", "regions"}

def check_sql(sql: str, tables_used: set[str], metric: str) -> list[str]:
    errors = []
    if not tables_used <= ALLOWED_TABLES:
        errors.append(f"Tables outside the allowed list: {tables_used - ALLOWED_TABLES}")
    if metric == "revenue" and "posting_date" not in sql:
        errors.append("Revenue must filter on posting_date, not created_at")
    if "status" not in sql:
        errors.append("Missing required filter on status")
    return errors

The rules are simple; the hard part is getting the definitions. On site, ask the person in charge of finance: “Which date does this month’s revenue count by?” That answer is the first validation rule you need to write.

Four common mistakes

The most common mistake is sending every question into one vector pipeline because the demo worked, then discovering in production that a whole group of aggregate questions cannot be answered correctly. Next comes trusting SQL simply because it runs, and merging BM25 and vector scores by addition instead of by rank.

The last mistake is harder to see: reporting a single overall accuracy figure. If your eval set is 90% meaning-based questions, the system can post a handsome score while the SQL branch gets half its answers wrong. Measure each question type separately.

This week’s exercise, and how to put it on your CV

Take 50 real questions, label them by the three types, run them through the router above and record an accuracy table for each branch. On your CV, do not write “built a RAG system”.

Write something like: “classified 200 real customer questions, split out a text-to-SQL branch with business-definition checks, measured accuracy separately for each type”. A line like that shows you know how to measure, not just how to assemble libraries.

Retrieval libraries will keep changing. The habit of asking “what does the customer actually ask?” before writing the first line of code will not.

Was this article useful?

Use with your AI assistantAsk Claude ↗Ask ChatGPT ↗
3 sources
Read next on the roadmap · Stage 3: Applied AIEvery call times out at 30 seconds. The user still waits 90Each step restarts the clock and retries push the worst case to 270 seconds; one deadline for the whole request, passed down, fixes it.