RAG for Tables and Structured Data

Most RAG tutorials stop at "chunk documents → embed → retrieve," as though the world contained only running-prose text. But in real enterprise documents—financial reports, product specifications, price lists, and statistical reports—tables contain the highest density of information. Feeding tables into RAG as ordinary text almost inevitably causes a disaster: chunks split them apart, semantics are lost, and numeric lookups become inaccurate.

This article breaks down the full table-RAG pipeline: extraction, chunking, storage, retrieval, and citation. Every stage has pitfalls and trade-offs.

1. Why Tables Are RAG's "Hardest Problem"

The underlying assumption of a general RAG pipeline is that text is linear—chunk it by token or paragraph, embed it, and retrieve it. But tables are two-dimensional, not linear. Three classic pain points:

1. Chunking splits tables apart. Most splitters (such as RecursiveCharacterTextSplitter) work on flat text. A 500-row table hits a chunk boundary and gets split into three segments. Every segment loses the header and the meaning of the columns. Retrieval may return the second segment, which contains 150 rows of numbers, but no one knows what each column means.

2. Row and column semantics are lost. A row only makes sense when paired with its header. A standalone 3200 conveys nothing—only "Q3 revenue was 32 million" has meaning. Embedding a bare row such as iPhone 15, 3200, 12%, 0.87 produces a vector that is essentially noise in semantic space.

3. Vectors are inherently weak at exact numeric values. Embeddings capture semantic similarity, not numerical precision. The vectors for "revenue grew 12%" and "revenue grew 13%" are nearly identical, even though their meanings differ greatly. Ask "which products have a gross margin below 10%," and vector retrieval is basically blind.

The conclusion is straightforward: do not treat tables as ordinary text; they need a dedicated table pipeline.

2. Table Detection and Extraction

The foundation of the entire pipeline is extracting tables in structured form, rather than as a blob of messy text. Handle them differently by source:

HTML tables are the best case, because the DOM structure is already structured. Use cheerio or jsdom to traverse <table>, separate <thead> from <tbody>, and correctly handle merged colspan / rowspan cells.

Excel / CSV files are already structured, and openpyxl / pandas can handle them directly. Watch out for sheet names, merged cells, and exactly which row contains the header—these are the easiest places to make mistakes.

PDF tables are the hardest to handle. PDFs have no structure, so it must be inferred from visual layout. Choose the toolchain by scenario:

• pdfplumber: Pure Python and based on pdfminer.six; suitable for regular tables with borders, with solid accuracy and common production use.
• camelot: Its lattice mode suits tables with borders, while stream suits borderless tables; it depends on OpenCV / ghostscript.
• tabula-py: A wrapper around Java Tabula. Results are stable, but it requires a JVM and is cumbersome to deploy.
• unstructured / docling: A newer generation of multimodal document parsers that combine layout understanding with table extraction, suitable for complex PDFs.

A working Python example:

import pdfplumber

def extract_tables(pdf_path: str):
    tables = []
    with pdfplumber.open(pdf_path) as pdf:
        for page_idx, page in enumerate(pdf.pages):
            for table in page.extract_tables():
                if not table or len(table) < 2:
                    continue
                header, *rows = table
                tables.append({
                    "source_page": page_idx,
                    "header": [h.strip() for h in header if h],
                    "rows": [[(c or "").strip() for c in row] for row in rows],
                })
    return tables

A few rules of thumb:

• Always clean extracted tables first: convert None to empty strings, remove line breaks, and normalize numeric formats (parse "1,234.5" as 1234.5), or dirty data will break both SQL and vectors later.
• Do not trust PDF table extraction to be 100% accurate. Give every table a confidence score based on row count, column-count consistency, and empty-cell ratio; send tables below the threshold for manual review.
• Use a VLM as a fallback for complex reports. For nested merged cells, cross-page tables, and cases traditional CV methods cannot handle, feed the full-page image directly to a multimodal LLM and have it output a Markdown/JSON table. The result is far better than pure algorithms. This has been one of the clearest trends in the past two years.

3. Chunking Strategy: Whole Table vs. Row Level

How should extracted tables enter the index? There are two mainstream approaches, each with trade-offs.

Strategy A: Treat the whole table as one unit. Serialize the entire table as Markdown (or HTML) and put it into the vector database as a single chunk.

function tableToMarkdown(table: Table): string {
  const header = `| ${table.header.join(" | ")} |`;
  const sep = `| ${table.header.map(() => "---").join(" | ")} |`;
  const rows = table.rows.map(r => `| ${r.join(" | ")} |`).join("\n");
  return `${table.caption}\n${header}\n${sep}\n${rows}`;
}

Advantages: the structure remains intact, and the LLM sees a complete table that it can aggregate across rows. This is suitable for small tables (within a few dozen rows) and queries that require cross-row calculations (such as "find the top three products by sales").

Disadvantages: large tables immediately overflow the token budget. Embedding is calculated only once, so every query against the entire table depends on retrieval through a single vector. The granularity is too coarse—a vector cannot represent all possible query intents for a 500-row table.

Strategy B: Row-level chunking + header reinjection. Treat every row as a chunk, but attach the complete header and table-level metadata back to every chunk. This is the standard approach for large tables.

function tableToRowChunks(table: Table): Chunk[] {
  return table.rows.map((row, i) => ({
    id: `${table.id}:row:${i}`,
    text: `${table.caption}\n${table.header.join(" | ")}\n${row.join(" | ")}`,
    metadata: {
      type: "table_row",
      tableId: table.id,
      rowIndex: i,
      header: table.header,
      source: table.source,
    },
  }));
}

Advantages: fine-grained retrieval can precisely return a few rows even from a large table; chunks are small and embedding quality is high.

Disadvantage: cross-row queries fail. "What is total Q3 revenue?" requires aggregating all Q3 rows, while row-level chunking can retrieve only one row. This is why hybrid retrieval is necessary later.

Rules of thumb:

• Store small tables (fewer than 50 rows) as whole tables.
• Chunk large tables by row while also retaining a table-level summary vector (embed the caption, header, and first few rows) as an entry point for table-level retrieval.
• Regardless of the chunking method, metadata must include tableId, headers, row number, and source—reranking and citation depend on them later.

4. Storage Choices: Vectors vs. Structured Data vs. Hybrid

This step determines the overall architecture. There are three paths:

Pure text vectorization: serialize tables as text and put them into a vector database. It is the simplest option, but it has no ability to perform exact numeric queries. It is suitable only when tables serve purely as "background information."

Structured tables (Text-to-SQL): store tables in a relational database (Postgres / SQLite / DuckDB) and have the LLM generate SQL at query time. Numbers, aggregation, and filtering are all SQL's home turf.

Hybrid (recommended): build both. Route text, descriptions, and long-form content through vectors; route table data through structured queries based on question type.

My assessment is clear: whenever documents contain tables that require exact queries—financial, specification, pricing, or statistical tables—you must add a structured layer. Pure vectors are simply inadequate here. The cost is maintaining two indexes plus a routing layer—but that is necessary complexity for table RAG and cannot be avoided.

5. The Text-to-SQL Pattern

Routing structured data through SQL essentially replaces "fuzzy retrieval" with "precise querying." The workflow is:

1. Schema indexing: store each table's name, column names, column meanings, and foreign-key relationships as context for the LLM to write SQL.
2. Question rewriting: map a user question such as "which products had gross margins below 10% last quarter" to a SQL intent.
3. SQL generation: have the LLM generate SQL under schema constraints.
4. Execution + feedback: execute it in the database, then feed the result (rows plus column names) back to the LLM to compose a natural-language answer.

from sqlalchemy import create_engine, text
from openai import OpenAI

engine = create_engine("duckdb:///docs.duckdb")
client = OpenAI()

SCHEMA = """
表 products: product_id, name, category, price
表 sales: sale_id, product_id, quarter, revenue, cost
-- 毛利率 = (revenue - cost) / revenue
"""

def answer_with_sql(question: str):
    sql = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {"role": "system", "content": f"基于以下 schema 写 DuckDB SQL,只返回 SQL:\n{SCHEMA}"},
            {"role": "user", "content": question},
        ],
    ).choices[0].message.content.strip()

    with engine.connect() as conn:
        result = conn.execute(text(sql))
        cols = list(result.keys())
        rows = result.fetchall()
    return {"sql": sql, "columns": cols, "rowCount": len(rows)}

A few non-negotiable rules for preventing SQL failures:

• Read-only accounts: the database connection used by the LLM must always be read-only, have a timeout, and enforce LIMIT. Otherwise, one accidental DELETE FROM is enough to ruin your day.
• Schema whitelist: do not expose irrelevant tables to the LLM. It reduces both hallucinations and the risk of erroneous queries.
• SQL validation layer: run EXPLAIN on generated SQL first; intercept and regenerate anything with syntax errors or full-table scans.
• Few-shot examples are indispensable: prepare 10–20 "question → SQL" examples. Accuracy on complex queries improves significantly.
• Column names must be semantic: rev_q3 is far inferior to revenue_q3_2024. The quality of schema descriptions directly determines the ceiling of Text-to-SQL.

6. Hybrid Retrieval: Structured Data + Vectors, Fused with RRF

In real documents, one question often requires both tables and text: "How does the iPhone 15 Pro's battery life compare with the previous generation?" It needs both a specification table (structured data) and review text (vectors).

Route first: use a lightweight classifier (rules or a small LLM) to determine the question type. Send purely numeric or aggregation questions to SQL, semantic or descriptive questions to vectors, and run both paths concurrently when uncertain. A router should be conservative: trade latency for recall; it is worth it.

Fuse results from both paths with RRF (Reciprocal Rank Fusion):

function rrf(rankings: string[][], k = 60): Map<string, number> {
  const scores = new Map<string, number>();
  for (const ranking of rankings) {
    ranking.forEach((docId, rank) => {
      scores.set(docId, (scores.get(docId) ?? 0) + 1 / (k + rank));
    });
  }
  return scores;
}

// sqlDocs: SQL 返回的行 id;vecDocs: 向量召回的 chunk id
const fused = rrf([sqlDocs, vecDocs]);
const topK = [...fused.entries()]
  .sort((a, b) => b[1] - a[1])
  .slice(0, 10)
  .map(([id]) => id);

The advantage of RRF is that it does not require score normalization—SQL row counts and vector cosine scores are completely incomparable, but using rank alone is stable. This is the standard approach for fusing heterogeneous results from two paths in table RAG.

Rule of thumb: before structured results are passed to the LLM, they must include column names and table names, or the LLM has no idea what that 12% represents. For questions involving cross-table relationships (data from multiple tables in a Q3 financial report), SQL is ten times more reliable than vectors.

7. Engineering Essentials

These details determine whether your table RAG is a toy or a production system.

1. Table metadata is not optional. Every table chunk must be traceable to its source: source_doc, page, version, extracted_at, confidence, and tableId. Only then can answer citations tell users, "This table comes from page 12 of the 2024 Q3 financial report." version is especially important—financial reports can be revised, and numbers from old versions must never be mixed into new queries.

2. Table-specific reranking. General-purpose reranking models are only moderately effective on serialized table text. There are two improvement paths: mix table data into training, or layer rules into the reranking stage—weighted header matches and weighted numeric matches (when a question contains "gross profit," prioritize tables whose headers contain "gross margin").

3. Cite table fragments. Answers must be able to locate a specific table, or even a specific row and column. In the context provided to the LLM, give every chunk a [TBL: tableId, row: i] marker, so it can cite in its answer: According to [TBL: t_03, row: 7], the iPhone 15 Pro has a battery life of 23 hours. The frontend can then render that marker as a clickable provenance link.

4. Cache SQL results. Cache SQL results for high-frequency questions with fixed report definitions. Reducing a query from 800 ms to 5 ms produces a completely different experience.

5. Monitor extraction quality. Table extraction is the most fragile stage of the entire pipeline. Establish sampled human annotation plus extraction-confidence monitoring, and alert when accuracy falls below a threshold—this is the part most likely to silently decay in a production system.

Summary

• Tables are not text, so do not use pure vectors. Tables are two-dimensional structured data and require dedicated extraction, chunking, storage, and retrieval pipelines.
• Extraction is the foundation. Choose pdfplumber / camelot / docling / VLM based on document complexity; PDF table extraction must have confidence scoring and a human fallback.
• Choose chunking based on table size: store small tables whole; chunk large tables by row, with header reinjection and a table-level summary vector.
• Send numeric queries to SQL, semantic queries to vectors, and fuse with RRF—this is the standard architecture for table RAG.
• Metadata, reranking, citations, caching, and monitoring are the engineering details that determine whether RAG is a demo or a production system.

Key takeaway: Pure-vector RAG can answer "what it says," but not "what exactly is the value"; treating tables as structured data is the crucial leap that takes RAG from "reading documents" to "querying data."