Why route "how many refunds did we issue last week" to SQL instead of a vector index?

## Two backends, two answer shapes The cleanest way to think about source routing is to ask what the answer physically *is*. A question like "What is our refund policy for tickets changed within 24 hours?" has an answer that already exists as prose in some document. Retrieval's job is to find that prose. Approximate nearest-neighbour search over embeddings is well suited: semantically similar phrasing wins, and the generator grounds its reply in the returned span. A question like "How many refunds did we issue last week?" has an answer that exists nowhere as text. It is a computation — a `count` over rows filtered by a date range. No document contains it, no passage can be retrieved to supply it, and no amount of top-k tuning changes that. The correct backend is the record store, reached by generating a query (text-to-SQL) and executing it. ## What goes wrong when you misroute Sending the aggregate to the vector index is the instructive failure. The retriever happily returns the ten most refund-ish passages it can find — support emails, the policy page, a runbook. The generator, told to answer from context, does what models do: it produces a number. Sometimes it counts the retrieved passages. Sometimes it copies a figure out of an unrelated report. The answer is fluent, specific and unverifiable, and it changes between runs because top-k changes. This is the archetypal "RAG gives confidently wrong numbers" complaint, and the cause is routing, not retrieval quality. The reverse misroute is less harmful but still bad: send "what is the refund policy" to text-to-SQL and the model either emits a query against a table that holds no policy text, returning empty, or invents a `policies` table that does not exist and errors. At least it fails loudly. ## Signals a router uses Intent classification here is fairly tractable because the linguistic markers are strong. Aggregate and analytical intent shows up as counting and comparison language ("how many", "total", "average", "trend", "top five", "compared to last quarter"), explicit time windows, and references to entities that are obviously rows — orders, refunds, tickets, users. Explanatory intent shows up as "what is", "why", "how do I", "under what conditions", and references to concepts rather than records. The hard cases are hybrids: "Why did refunds spike last week?" needs both a number from the records and an explanation from incident notes or policy changes. Mature systems answer these by routing to both, running the SQL and the semantic retrieval, and giving the generator both result sets — rather than forcing a single winner. Some systems make each backend a tool and let the model decide, which is the same routing decision expressed differently. ## Making generated SQL safe and correct Text-to-SQL brings its own engineering, and interviewers often probe it. **Safety.** Execute as a read-only role with no write or DDL privileges; that is the real control, not string-scanning the generated SQL for forbidden keywords. Add a statement timeout and a row limit so a bad join cannot take the database down, and run it against a replica rather than the primary where possible. **Correctness.** The model needs schema context — table and column names, types, and crucially the business semantics (which timestamp column means "issued", whether refunds are soft-deleted, what currency amounts are in). Most text-to-SQL errors are semantic, not syntactic: the query runs and returns a plausible number computed from the wrong column. Curated views or a semantic layer that exposes a small, well-named surface dramatically outperform pointing the model at a raw hundred-table schema. **Verification.** Because a returned number carries no provenance, show the executed query alongside the answer. That single UI decision converts an unverifiable claim into a checkable one, and it is what makes analysts trust the feature. ## Where the boundary really sits The generalisation worth stating: route by whether the answer must be *found* or *computed*. Vector retrieval finds; SQL computes. Exact lookups of a single record — "what is the status of booking QX7742?" — also belong on the computed side even though they involve no aggregation, because approximate similarity search over a document index is the wrong tool for retrieving an exact row by key. Getting this boundary right removes an entire class of hallucination from a RAG product, and it is cheap: the classification is coarse and the linguistic signals are unusually clear.