Appearance
Structured Data + Text
Most real-world data is not purely text. It is a mix of structured records in database tables and unstructured content in documents, emails, and notes. A question like "which customers who bought product X in Q3 also opened a support ticket about billing?" requires joining a sales database with a ticket system and searching ticket contents for the word "billing." No single retrieval strategy handles both. This lesson covers combining SQL queries with vector search to answer questions that span structured and unstructured data.
What you'll learn
- Text-to-SQL converts natural language questions into SQL queries that run against structured data
- Hybrid retrieval combines structured filters from SQL results with semantic search from vector stores
- The LLM decides which data source to query based on the question type, then combines the results
The problem
A user asks "which premium customers reported billing issues in the last 30 days?" This single question requires three operations. First, query the customers table for users on the premium plan. Second, search the support ticket contents for the word "billing" and its synonyms. Third, join the results by customer ID and filter to the last 30 days. A vector search alone cannot do the structured filtering. A SQL query alone cannot do the semantic search across ticket contents. The solution requires both, coordinated by the LLM.
Build it
Step 1: Classify the question type
The system first evaluates the user's query to determine if it requires structured data, unstructured text, or both. This classification routes the query to the appropriate execution paths.
python
def classify_question(question):
prompt = f"""Classify this question as SQL, SEARCH, or BOTH.
SQL: requires querying structured data (numbers, dates, aggregates, customer records, filtering by plan/status)
SEARCH: requires searching document contents (policies, emails, notes, ticket descriptions)
BOTH: requires structured data and document search
Question: {question}
Answer (SQL, SEARCH, or BOTH):"""
return call_llm(prompt, max_tokens=10).strip()Step 2: Generate and execute SQL
For queries requiring structured data, the LLM translates the intent into a safe, read-only SQL statement. A robust schema definition is provided to guide the generation.
python
TABLE_SCHEMA = """
Tables:
- customers(id INT, name TEXT, plan TEXT, created_at DATE)
- orders(id INT, customer_id INT, product TEXT, amount DECIMAL, date DATE)
- tickets(id INT, customer_id INT, subject TEXT, body TEXT, status TEXT, created_at DATE)
"""
def generate_sql(question):
prompt = f"""Given this database schema: {TABLE_SCHEMA}
Generate a PostgreSQL SELECT query for this question.
Only use SELECT. Never INSERT, UPDATE, DELETE, or DROP.
Return ONLY the SQL query, no explanation.
Question: {question}"""
sql = call_llm(prompt, max_tokens=200).strip()
# Safety: reject non-SELECT queries
if not sql.upper().strip().startswith("SELECT"):
raise ValueError(f"Rejected non-SELECT query: {sql[:50]}")
return sql
def run_sql(sql):
cursor.execute(sql)
columns = [desc[0] for desc in cursor.description]
return [dict(zip(columns, row)) for row in cursor.fetchall()]Step 3: Combine results
The hybrid retrieval function orchestrates both paths and merges the results. It intelligently passes structured identifiers to the vector search to narrow down semantic matching.
python
def hybrid_retrieve(question):
classification = classify_question(question)
structured_results = None
search_results = None
customer_ids_from_sql = set()
if "SQL" in classification:
sql = generate_sql(question)
structured_results = run_sql(sql)
if structured_results:
customer_ids_from_sql = {r.get("customer_id") for r in structured_results if "customer_id" in r}
if "SEARCH" in classification:
# If we have customer IDs from SQL, use them to narrow the vector search
if customer_ids_from_sql:
search_results = vector_search_with_filter(
question,
filter_field="customer_id",
filter_values=list(customer_ids_from_sql)
)
else:
search_results = vector_search(question)
# Combine and answer
answer_prompt = f"""Question: {question}
Structured data from database:
{structured_results if structured_results else "No structured data available"}
Relevant documents from knowledge base:
{search_results if search_results else "No document results available"}
Answer the question using both sources. If the structured data and documents
contradict each other, note the discrepancy. Cite specific data points."""
return call_llm(answer_prompt)What goes wrong
| Mistake | How you notice it | The fix |
|---|---|---|
| LLM generates dangerous SQL | UPDATE, DELETE, or DROP in generated query | Only allow SELECT. Reject any query that doesn't start with SELECT. Use a read-only database user |
| SQL query returns wrong data | Results don't match the question | Include the schema in the prompt. Test common query patterns against known results |
| Structured and unstructured results contradict | The database says one thing, documents say another | Surface the contradiction to the user: "The database shows X, but support tickets mention Y" |
| SQL injection via LLM-generated queries | Malicious user input embedded in SQL | Use parameterized queries. Never concatenate user input into SQL. The LLM generates the query structure, not the values |
Confirm it worked
Test the hybrid approach by simulating a database of customer records and a separate knowledge base of support tickets. The assertion validates that the LLM successfully joins insights from both sources.
python
# Set up test data
cursor.execute("INSERT INTO customers VALUES (1, 'Alice', 'premium', '2026-01-01')")
cursor.execute("INSERT INTO tickets VALUES (1, 1, 'Billing issue', 'I was charged twice this month', 'open', '2026-07-15')")
index_documents([{"id": "ticket-1", "content": "Customer reports double charge on July bill. Amount: $99 charged twice.", "customer_id": "1"}])
# Question that spans both sources
answer = hybrid_retrieve("Which premium customers reported billing issues recently?")
assert "Alice" in answerNext: Conversational RAG