Your RAG system works great for text but completely fails when users ask questions about Excel sheets, pricing tables, and CSV reports. How do you make your RAG system understand and retrieve from structured/tabular data?

What you need to know

Why vector search fails on tables

"What was total revenue from Karnataka in Q2?" needs every relevant row, filtered and summed. Vector search returns the few rows whose text looks most similar, and the model then adds up whatever it was given — often a partial answer that looks complete. Numbers also embed poorly: "₹4,999" and "₹49,999" look almost the same to an embedding model.

The table path

1. Load with types — CSV and Excel into DuckDB or Postgres; dates as dates, money as numbers.
2. Describe the tables — a data dictionary: table and column descriptions, sample values and 20–50 gold question-to-SQL pairs.
3. Retrieve the schema — embed those descriptions so retrieval picks the right tables for the question.
4. Generate SQL — with structured output.
5. Validate — parse it, allow SELECT only, force a LIMIT, run as a read-only role with a timeout.
6. Explain — the model writes the answer from the returned rows, and the UI shows the SQL and row count.

1import duckdb, sqlglot2from sqlglot import exp34con = duckdb.connect("reports.duckdb", read_only=True)56def safe_run(sql: str, limit: int = 1000):7    tree = sqlglot.parse_one(sql, read="duckdb")8    if not isinstance(tree, exp.Select):9        raise ValueError("only SELECT queries are allowed")10    if not tree.args.get("limit"):11        tree = tree.limit(limit)12    return con.execute(tree.sql(dialect="duckdb")).fetchall()1314# rows = safe_run(llm_sql("Total Q2 revenue by state", schema_context))

Tables inside PDFs

A third case: price lists and rate cards inside PDFs. Extract them with a layout-aware parser (Docling, Unstructured or a cloud document-intelligence service), keep each table as Markdown, and index a short natural-language summary so vector search can find it. When it is retrieved, put the whole table in context, not a fragment.

Failure modes

Evaluate with execution accuracy: run the generated SQL and the gold SQL and compare results. Text-similarity scores say nothing about whether a query is right.

A real-life example

Scenario, numbers made up. A consumer-goods company's sales assistant indexes monthly Excel reports as text chunks. Asked "Which distributor had the highest returns in Tamil Nadu last quarter?", it names a distributor that appears in a retrieved chunk but is not the top one.

The team loads 36 monthly reports into DuckDB, writes a data dictionary for 14 tables, and collects 40 gold question-SQL pairs. A router sends numeric questions to the SQL path. On a 150-question test, correct answers to table questions rise from 31% to 87%, and every answer now shows the query it ran, which lets finance staff spot the remaining mistakes quickly.

Follow-up questions to expect

• "How do you stop harmful SQL?" — A read-only database role, a parser that allows only SELECT, a forced LIMIT and a statement timeout; the prompt is not the defence.
• "What about very wide or messy spreadsheets?" — Clean them into tidy tables at ingestion, with one header row and typed columns; text-to-SQL on messy sheets fails in unpredictable ways.
• "When would you still embed table content?" — For finding which table or document is relevant, and for small lookup tables where one row answers the question.