How would you modify retrieval architecture for hybrid text and SQL RAG?

WHAT THIS TESTS: This question probes whether you understand that unstructured text retrieval and structured data querying are fundamentally different operations with different failure modes, latency profiles, and correctness requirements. The interviewer wants to see if you can design a routing and synthesis layer rather than treating all data as embeddings. Specifically, they are looking for awareness that SQL engines handle aggregations, joins, and precise numeric filters far better than semantic search ever could.

A GOOD ANSWER COVERS: First, intent classification or query planning to decide whether the question needs text, SQL, or both. Second, preserving native SQL execution for structured data instead of abandoning the relational engine, because vector search cannot reliably sum columns or filter by exact dates. Third, running retrieval paths in parallel or sequence depending on dependencies, for example using the SQL result to filter which documents to retrieve, or vice versa. Fourth, a synthesis stage where both structured results and unstructured snippets are fed into the final LLM prompt with clear citations and source attribution. Fifth, handling schema exposure by providing the LLM with relevant table schemas, descriptions, and few-shot examples to improve text-to-sql generation accuracy.

COMMON WRONG ANSWERS: The biggest red flag is suggesting you dump SQL tables into documents, embed them, and rely solely on vector search. This loses numeric precision, aggregation power, and join semantics entirely. Another weak pattern is proposing a single monolithic prompt that asks the LLM to write SQL and summarize documents simultaneously without an explicit intermediate result stage, which dramatically increases hallucination risk and makes debugging impossible. Ignoring schema context and expecting the LLM to guess table names or column types is also a serious failure mode that leads to bad queries.

LIKELY FOLLOW-UPS: How do you handle cases where the SQL query returns no rows but the text corpus has relevant context, or the reverse? How do you prevent SQL injection or limit the LLM to read-only queries against a read replica? What if the user question is ambiguous and could be answered by either source alone? How do you rank or reconcile conflicting information between a document and a database record? How do you manage latency and cost when both retrievers are slow or when the SQL query requires scanning large tables?

ONE CONCRETE EXAMPLE: Imagine a financial RAG system where a user asks how much revenue the company had last quarter and what the CEO said about it. The query planner detects both a numeric aggregation and a qualitative summary. The SQL path executes SELECT revenue FROM quarterly_reports WHERE quarter equals Q2_2024 against the finance database. The vector path retrieves the top three chunks from earnings call transcripts mentioning revenue outlook. The synthesis prompt receives the exact SQL result and the transcript snippets, then generates an answer that pairs the 450 million dollar figure with the CEOs commentary on supply chain headwinds, citing both the database row and the document source.

Source: llamaindex.ai