RAG Fails to Aggregate: An Alternative System Unveiled

Wider context windows do not fix RAG

In an attempt to improve the accuracy of responses provided by language models, I increased the context window size fivefold. However, each adjustment led to unexpected results. I designed a question-answering system based on a dataset, but I found that RAG responses were often barely half correct.

A test on 100,000 lines

To evaluate RAG's performance, I measured its capabilities across seven types of queries and five context sizes, using a dataset of 100,000 lines. This test aimed to determine how the model handled complex aggregation queries.

Misplaced confidence

Last month, I worked on a new feature for EmiTechLogic. This feature allows users to upload their messy CSV files and ask questions in simple English. It seemed ideal for RAG, so I invested in this technology, using embeddings and retrieval techniques to present well-structured answers.

During the initial demonstrations, the results were impressive: well-organized tables, accurate figures, and professional formatting. I even began to trust the system during our internal tests. However, when I checked one figure, the actual grocery expenses in the dataset were $1,140,033.24. The model provided a category breakdown that seemed legitimate, but the total was less than half of the actual expense.

The illusion of accuracy

Faced with this inconsistency, I increased the context window to 4k, then to 16k, 32k, and finally 128k tokens. At each step, the response became longer and more detailed, but still incorrect. I realized that the problem did not stem from retrieval, but from asking a retrieval system to perform complex calculations on data it had only partially seen. The model produced polite and structured responses that seemed correct but were fundamentally wrong.

Why RAG fails to aggregate

The RAG pipeline does not truly understand structured data. It transforms each CSV line into plain text without genuinely interpreting the data. For a query like "What is the total spending by category?", the pipeline follows several steps:

1. Tokenization: it identifies relevant keywords.
2. Evaluation: it evaluates all lines to find keyword overlaps.
3. Returns: it returns the top lines as plain text.
4. Requests: it asks the language model to sum and group the data.

It is at the final step that the system fails. The model does not perform an actual sum but mimics aggregation by generating a response based on number patterns.

A revealing benchmark

To accurately measure these failures, I built a benchmark comparing two pipelines for each query. The first is a RAG simulation, testing five context sizes ranging from 5 lines to 8,000, or from 325 tokens to 500,000. For each size, I tracked three metrics: the amount of data seen by the model, the sum calculated from that segment, and a reader's ability to detect the error.

The second pipeline is a semantic engine that executes the same query by performing a full scan of the 100,000 lines, returning an exact answer.

Comparison of processing flows

The simulation does not reproduce the exact outputs of the language model, but it retains a key structural property: a partial segment of data is fed into a system that returns a complete response. It is this property that causes the problem, and it is what the benchmark measures.

I chose seven types of queries to cover each aggregation model one might encounter in a structured data system:

• Total spending by category: requires summing all lines across 14 groups.
• Highest average transaction by category: the average changes with each missing line.
• Total spent on grocery_pos: the filter requires seeing all matching lines.
• How many female customers made transactions: the count is meaningless on a partial scan.
• Total spending where amount > $500: the threshold logic requires complete data.
• State with the lowest total spending: the minimum can only be found with all groups present.
• Percentage of fraudulent transactions: the ratio is undefined on a partial denominator.

These queries are standard in data analysis, making this failure critical.

Collapse of error observability

Here is the complete output of the benchmark for the query that triggered everything. The numbers make the problem impossible to ignore.

GROUND TRUTH (Semantic Engine)

SUM(amt) GROUP BY category → 14 groups

#1 grocery_pos $1,140,033.24

#2 shopping_net $773,527.93

#3 shopping_pos $725,766.14

#4 gas_transport $648,804.24

#5 home $556,526.53

Latency: 100.47 ms | Lines scanned: 100,000

RAG SIMULATION — what the LLM receives at each context size

• Context Lines Coverage Partial Sum Detectable Error?
• small (~325 tokens) 5 0.0050% $197.73 EASY
• small (~3K tokens) 50 0.0500% $2,003.56 MODERATE
• medium (~32K tokens) 500 0.5000% $31,023.21 HARD
• large (~130K tokens) 2,000 2.0000% $140,093.16 VERY HARD
• very large (~520K tokens) 8,000 8.0000% $569,368.22 ALMOST IMPOSSIBLE

I observed these results for a while. The most troubling part was not just that the answers were wrong, but how much harder it became to spot the errors as the context window grew.

At 8,000 lines, the error was still over 50%, but the response resembled a professional report. You would need to manually check the figures to notice something was off. This is what I began to call the Collapse of Error Observability. The more context I provided to the model, the more convincing the output became — but not more accurate.

The "Partial Sum" column shows the total if the LLM were to add each value in the lines it actually retrieved. The "Detectable Error?" column assesses the likelihood that a human reader would spot an error.

With 5 lines, the partial sum is $197.73. The correct total is $1,140,033.24. It's obvious. The output is short, the numbers are wrong, and the missing data is clear. The error is instantaneous.

At 8,000 lines, the partial sum reaches $569,368.22. The LLM has now seen all 14 categories. It generates a report of 1,500 words with specific figures and confident language. The error is 50%, but it is hidden in authoritative and well-structured prose. Without an external reference, a reader has no way to detect it.

This pattern has held across the seven queries:

• Context window size | Lines | Dataset Coverage | Response Length | Detectable Error?
• ~325 tokens | 5 | 0.005% | ~50 words | YES — obviously a guess
• ~3K tokens | 500 | 0.050% | ~150 words | MAYBE
• ~32K tokens | 500 | 0.500% | ~400 words | HARD
• ~130K tokens | 2,000 | 2.000% | ~800 words | VERY HARD
• ~520K tokens | 8,000 | 8.000% | ~1,500 words | ALMOST IMPOSSIBLE
• Semantic Engine | 100,000 | 100% | <200 ms | N/A — exact

I called this the Collapse of Error Observability. As the context grows, confidence increases alongside it. Accuracy does not follow.

The illusion of context

The modes of failure are asymmetrical, making them dangerous:

An incorrect RAG response seems correct. It is formatted, specific, and confident. A failed calculation generates an explicit error. It is visible.

One failure is silent. The other is loud. As context windows reach millions of tokens, the silent failure becomes harder to detect. The system does not become safer as it scales. It just becomes more convincing.

The Semantic Engine: Proof that the correct answer is quick

Before fully understanding the problem, I had already assembled a simple semantic engine out of frustration. I just wanted the correct answer at least once.

The approach turned out to be simple: analyze the query into appropriate operations and perform a single pass over the dataset. No embeddings, no retrieval, no guessing.

Here’s what that looks like in practice:

The logic is straightforward. Take a query like "What is the total spending by category?". The engine maps it to a direct operation: SUM(amt) GROUP BY category. It processes the entire 100,000 lines in a single pass. It accumulates the grouped totals. There is no retrieval. No inference. No partial scan. It visits each line once and returns the exact result.

This proves that the correct answer is not costly. Benchmark queries finish in under 200 ms. Sample size: 100,000 lines. Aggregation is trivial. Failure occurs when you direct these queries to a system built to misunderstand them.