RAG Over Structured Data: Why Vector Search Fails on Tables (And What Actually Works)

Ask a RAG chatbot to explain a paragraph from a PDF and it does a fine job. Ask the same chatbot for the average deal size from last quarter's sales spreadsheet, and it often makes up a number with total confidence.

This is not a small bug to patch. It is a mismatch between how retrieval-augmented generation got designed and how spreadsheets, CSVs, and SQL tables actually store information. RAG was built for paragraphs. Tables are a different shape of problem, and vector search alone does not solve it.

Why a Document Chatbot Falls Apart on a Spreadsheet

Most RAG systems get built and tested on documents like PDFs, contracts, or help articles. These are long stretches of sentences. A model reads one chunk of text and answers a question because the answer sits right inside it.

A spreadsheet or a SQL table works differently. Each row is a small, isolated fact. A question like "What is the average order value in California last month" needs the model to find every matching row, add the numbers, and divide. No single row contains the answer. The answer only exists once many rows get combined.

document RAG:  question -> retrieve one chunk -> answer sits inside it
table RAG:     question -> retrieve rows -> combine rows -> answer emerges

What Structured Data Actually Means

Structured data is any information stored in strict rows and columns, such as a spreadsheet, a SQL database, or a CSV export. Every row follows the same pattern. A customer table might always store a name, an email, and a signup date, always in the same order.

Unstructured data is the opposite. A blog post, an email, or a PDF report does not follow a fixed shape. Sentences run long or short, and the same idea gets worded ten different ways across ten different documents.

The Real Reason Vector Search Breaks on Tables

Vector search works by turning text into a list of numbers, called an embedding, and comparing how close two embeddings sit to each other. This works well for meaning and language. It works poorly for math.

To make a table searchable this way, most systems flatten each row into a sentence first:

row:      name: Priya | city: Pune | sales: 42000
flattened: "Priya from Pune had sales of 42000."

That flattened sentence goes into the vector database as one more chunk of text among thousands of others.

This flattening throws away relational structure, the links between rows, columns, and data types that give a table its meaning in the first place. A join between a customers table and an orders table, or a filter based on a date range, disappears the moment a row turns into a plain sentence.

Research on this exact problem backs this up. On WikiTableQuestions, a benchmark researchers use to test how well AI models answer questions about tables, even a leading model got the answer wrong close to a third of the time.

Aggregation questions make this worse. A question asking for a total, an average, or a count needs the model to scan every relevant row. Vector search only returns a small handful of the closest matches, so most of the rows a total actually needs never make it into the model's context window at all.

Where Teams Get Burned in Production

The failure rarely shows up as an error message. It shows up as a confident, wrong answer. A support chatbot might tell a customer their order total is correct, based on three retrieved rows, when the real order touched twelve rows across two tables.

Long tables cause a second failure. A model asked to add up fifty retrieved rows tends to lose count partway through, especially when everything gets crammed into one long block of text. This is one of the quieter reasons RAG fails in production, and it rarely shows up during a small demo with five sample rows.

Multi-table questions cause a third failure. Real business data almost never lives in one table. Customer information sits in one table, orders in another, payments in a third. Vector search has no built-in way to join across these boundaries, since each row got embedded on its own, with no sense of the tables around it.

Text-to-SQL: The Other Approach, and Its Own Limits

One alternative skips vector search completely. Instead of retrieving text, a model reads the question, looks at the table structure, and writes a SQL query itself. This pattern is called text-to-SQL. The query runs directly against the real database and returns an exact number, not a guess.

This approach fixes the math problem completely. A total is a total, an average is an average, calculated by the database engine instead of guessed by a language model. Teams building this pattern found that giving the model richer context about the table itself, not only its column names, made a noticeable difference to accuracy.

Text-to-SQL comes with its own weak spot. It needs a real, live database to query against, with a known schema. It struggles with vague, conversational questions, and a small mistake in the generated query can silently return the wrong rows with no warning at all.

Hybrid Retrieval: The Fix Most Production Teams Land On

Most production systems in 2026 do not pick one approach over the other. They combine both. A question first gets classified: does it need exact numbers from a table, or does it need context from a document? Numeric questions get routed to a SQL layer. Open-ended questions get routed to standard vector search, the kind covered in our RAG architecture guide.

Modern databases make this easier to build than it used to be. Postgres extensions now let a single SQL query combine a vector similarity score with regular relational filters in the same statement, joining vector tables with relational tables directly. That means a query can filter by exact date range and rank by semantic closeness at the same time, instead of running two separate systems and merging results by hand.

A second version adds structure back into the text before it gets embedded. Instead of turning a row into one flat sentence, the system attaches metadata alongside it, things like column names, data types, and a short summary of what the table represents. This gives vector search something closer to real structure to match against, rather than a bare sentence stripped of context.

A Simple Comparison

How to Pick the Right Approach for Your Project

Start by looking at the actual questions users ask, not the format of the data sitting behind the scenes. A support tool answering "What does this policy cover" needs standard document RAG, even if some of the underlying content lives in a spreadsheet.

A finance or operations tool answering "What was our revenue in March" needs text-to-SQL or a hybrid setup, since no amount of clever chunking makes vector search reliable at counting rows.

A small project with one clean table and simple filters rarely needs the full hybrid setup. Text-to-SQL alone, with guardrails around the generated query, is often enough to start.

The Bottom Line

Structured data was never the problem RAG got built to solve in the first place:

documents  -> meaning-based retrieval -> vector search fits well
tables     -> math and joins          -> vector search fits poorly

RAG grew out of question answering over documents, and tables need a different toolkit, sitting closer to traditional search and database systems than to plain vector retrieval. Knowing which questions belong to which toolkit solves most of the problem before a single line of code gets written.

Continue with RAG Architecture Explained, read Why RAG Fails for the full list of production failure modes, or browse the RAG category for the complete series.