Advanced RAG → Structured + unstructured hybrid (SQL for numbers, vectors for narrative)
What it is
"How many contracts did we sign in Q3?" retrieved five contracts and the model counted five. "What's our average deal size by region?" retrieved three deals and the model averaged them. These aren't retrieval failures — they're questions with a computational answer that lives in structured data, being routed to a system that returns text passages.
The hybrid pattern:
question ──► classify
├── narrative ("what does the MSA say about termination") → vector/hybrid RAG
├── aggregate ("how many, total, average, top N, trend") → text2SQL over the warehouse
└── both ("why did EMEA revenue drop and what does the → SQL for the numbers
post-mortem say") + RAG for the narrative
→ synthesise one answer
The third case is the valuable one: numbers with narrative explanation, each from the system that can actually produce it, cited separately.
Related patterns: extracting structured fields from documents at ingest (complex layout forms, tables tables) into a queryable table, so "sum the values in all invoices from Vendor X" becomes SQL over extracted data rather than arithmetic over retrieved text.
How to implement
- Classify the question (routing): aggregate keywords, comparison operators, "per month/region", numeric intent. Rules catch most; an LLM classifier handles the rest.
- Curate a narrow semantic layer for text2SQL — not the raw warehouse. A handful of well-named views with descriptions, documented join paths, and column semantics. Text2SQL accuracy on a curated 10-table layer is a different world from a 500-table schema.
- Generate SQL with the schema, column descriptions, sample values for enums, and few-shot examples of correct queries (dynamic few-shot selection dynamic examples from a query library works very well here).
- Validate before execution: parse the SQL (sqlglot), enforce read-only, reject queries touching tables outside the allow-list, inject
LIMIT, enforce a statement timeout, and apply row-level security for the caller. - Execute and return results as a small table; if the result set is large, summarise it (top N + aggregate) rather than dumping rows into the context (per-source truncation budgets).
- Render for the model as a Markdown table in a delimited block with the SQL shown, so the answer can cite the query as its provenance.
- Synthesise: for combined questions, run both retrieval paths in parallel and give the generator both blocks with instructions to attribute numbers to the query and narrative to the documents.
Small example
import sqlglot
from sqlglot import expressions as exp
ALLOWED_TABLES = {"v_contracts", "v_invoices", "v_vendors", "v_deals"}
MAX_ROWS = 200
SQL_SYS = """Write a single read-only SQL query (Postgres dialect) answering the question.
Schema:
v_contracts(contract_id, vendor_id, region, signed_date, value_usd, status)
v_vendors(vendor_id, vendor_name, region, tier)
v_invoices(invoice_id, vendor_id, invoice_date, amount_usd, paid)
Rules: use only these views; no DDL/DML; always include a LIMIT; use explicit date ranges.
Output JSON: {"sql": string, "explanation": string}"""
def validate_sql(sql: str, caller) -> str:
tree = sqlglot.parse_one(sql, read="postgres")
if not isinstance(tree, exp.Select):
raise ValueError("only SELECT permitted")
for t in tree.find_all(exp.Table):
if t.name not in ALLOWED_TABLES:
raise ValueError(f"table not allowed: {t.name}")
if not tree.args.get("limit"):
tree = tree.limit(MAX_ROWS)
# row-level security: tenant predicate injected, never supplied by the model
tree = tree.where(exp.condition(f"tenant_id = '{caller.tenant_id}'"))
return tree.sql(dialect="postgres")
def sql_answer(question, caller):
examples = example_store.similar("text2sql", caller.tenant_id, question, k=4) # Dynamic few-shot selection
gen = json.loads(llm("text2sql", SQL_SYS, [{"role": "user",
"content": render_examples(examples) + f"\n<question>{question}</question>"}]).text)
try:
safe = validate_sql(gen["sql"], caller)
rows = warehouse.execute(safe, timeout_s=10, role="readonly_rag")
except Exception as e:
log_sql_failure(question, gen.get("sql"), str(e))
return {"status": "failed", "reason": str(e)}
return {"status": "ok", "sql": safe, "rows": rows[:50], "n_rows": len(rows),
"table_md": to_markdown_table(rows[:50])}
def hybrid_answer(question, caller):
kind = classify_question(question) # "narrative" | "aggregate" | "both"
blocks, sources = [], []
if kind in ("aggregate", "both"):
r = sql_answer(question, caller)
if r["status"] == "ok":
blocks.append(f'<query_result sql="{html.escape(r["sql"])}" rows="{r["n_rows"]}">\n{r["table_md"]}\n</query_result>')
sources.append({"type": "sql", "sql": r["sql"]})
if kind in ("narrative", "both"):
chunks = rerank_hosted(question, hybrid_retrieve(question, caller), top_n=6)
blocks.append("<context>\n" + render(chunks) + "\n</context>")
sources += [{"type": "doc", "chunk_id": c["chunk_id"]} for c in chunks]
return generate(question, blocks, sources,
instruction="Numbers must come from <query_result> and be attributed to the query; "
"narrative claims must cite passage ids.")
Pros
- Correct numbers. Counts, sums, and averages are computed, not estimated from a sample of retrieved rows.
- Complete answers: aggregates over the whole dataset, not over whatever k passages were retrieved.
- Auditable: the SQL is the provenance, and a reviewer can re-run it.
- Combined answers — the number plus the explanation — are what business users actually want.
- Removes an entire category of embarrassing hallucination.
Cons
- Text2SQL is error-prone on complex schemas: wrong joins, wrong grain (double-counting on fan-out joins), wrong date boundaries, wrong filters. And a wrong query returns a confident wrong number, which is worse than a vague answer.
- Security surface: user text becoming database queries needs hard controls (prompt-injection defence).
- Semantic layer maintenance — views, descriptions, and examples must track schema changes.
- Classification errors send narrative questions to SQL (empty results) or aggregates to RAG (hallucinated numbers).
- Row-level security must be enforced in the database, not in generated SQL.
When to use
- Any assistant over a business domain where users ask both "what does the contract say" and "how much did we spend".
- Corpora with a structured counterpart: contracts + a contracts table, tickets + a ticket database, invoices + a finance warehouse.
- When extracted document fields (complex layout) make a queryable table possible.
When NOT to use
- No structured data exists — then aggregate questions should be answered with an honest limitation, not a hallucinated number (refusal / no-answer path).
- Schemas too complex to curate a semantic layer for, and no appetite to build one — text2SQL over a raw 500-table warehouse will not reach acceptable accuracy.
- When a purpose-built BI tool already answers these questions and users should be sent there.
Production at scale
The semantic layer is the whole game. Text2SQL accuracy is dominated by schema quality, not model quality. Curate a small number of denormalised, well-named views with column descriptions, documented grain, and enum sample values. Every view addition goes through evaluation.
A query library beats prompting. Maintain verified (question → SQL) pairs and retrieve the most similar ones as dynamic few-shot examples (dynamic few-shot selection). This is the single highest-impact accuracy lever, and it improves as analysts contribute.
Defence in depth on execution: a dedicated read-only role with grants only on the allow-listed views, database-enforced row-level security keyed to the caller, statement timeouts, result-row caps, a separate connection pool, and query cost limits. Never rely on prompt instructions or on filters the model wrote (excessive agency, authentication & authorization).
Show the query. Users and auditors should see the SQL that produced the number, with a "run it yourself" affordance. This converts an opaque hallucination risk into a verifiable claim, and analysts will correct it — free labelled data (data flywheel).
Validate results, not just SQL: sanity checks (non-negative counts, totals reconciling with a control query, date ranges within the data's range). Flag anomalies rather than reporting them confidently.
Grain errors are the silent killer. A join that fans out multiplies sums. Encode grain in view descriptions, prefer pre-aggregated views, and add regression tests where a known question must return a known number.
Evaluate separately: SQL execution accuracy (does it run), result accuracy (right number) against a labelled question→answer set, and classification accuracy for routing. Report all three; a system with 95% execution and 70% result accuracy is dangerous.
Latency: warehouse queries can take seconds. Run the SQL and vector legs in parallel for "both" questions; set timeouts; degrade to the narrative answer with an explicit note if the query times out.
Prod checklist
- Question classification (narrative / aggregate / both) with measured accuracy
- Curated semantic layer: few views, described columns, documented grain, enum samples
- Verified query library used as dynamic few-shot examples
- SQL parsed and validated: read-only, table allow-list, LIMIT injected, timeout enforced
- Database-enforced row-level security for the caller; dedicated read-only role
- Results capped/summarised before entering context; SQL shown to the user
- Result sanity checks and grain regression tests
- Parallel execution of SQL and vector legs; timeout degradation with an explicit note
- Separate metrics for execution accuracy, result accuracy, and routing accuracy
Metrics to watch
| Metric | Why |
|---|---|
| Result accuracy on labelled aggregate questions | The number that matters |
| SQL execution failure rate | Schema/prompt quality |
| Routing accuracy (aggregate vs narrative) | Misroutes hallucinate numbers |
| Query p95 and timeout rate | Latency |
| Analyst corrections to shown SQL | Free training data |
Related
- Routing — the decision point
- Dynamic few-shot — the query library
- Tables — extracted fields feeding structured tables
- Injection — user text to SQL
- Refusal — when there's no structured source