Chunking Strategies for Structured Data in RAG Systems (Part 2): Where Text-to-SQL Falls Short

This is Part 2 of a three-part series. Part 1 covered how to chunk structured data so a retriever can find it. This article covers the first query pattern, i.e., RAG-based Text-to-SQL. We discuss what works, what doesn't, and where it hits its limit. Part 3 will cover the agentic approach that solved the hard cases. The Question Part 1 Left Unanswered In Part 1, we solved a specific problem: default RAG chunking breaks on structured data, and there are six strategies to fix it. By the end of that article, your tables are indexed, your schema is retrievable, and your embeddings are in place. But a retriever finding the right chunk is only half the problem. The other half is what the system actually does with what it finds. A business user asks: "What was the total revenue from our top 10 SKUs last quarter, broken down by region?" Your retriever surfaces the right schema chunks. Then something has to turn that into SQL, and that translation step is where most natural language querying implementations quietly fall apart. I built pilots of two approaches on the same dataset and documented my observations. This article covers the first. Chunking Strategy Determines Query Pattern This is the insight that connects both articles and one I have not seen articulated elsewhere. Most content treats chunking and querying as independent decisions. They are not. The chunking strategy you chose in Part 1 directly determines which query pattern you can successfully use here. Part 1 Strategy What it enables in Text-to-SQL S1 — Row-level Point lookups: retrieve specific records by ID or attribute S2 — Small-group Within group comparisons: "largest city in region X?" S3 — Schema-aware Schema discovery: agent knows what tables and columns exist S4 — Entity-centric Multi table context without join reasoning S5 — Hierarchical Drill-down queries: partition first, then specific rows S6 — Beyond RAG Full SQL power: the escape hatch to the agentic pattern The most common mistake: teams build a schema index using Strategy 3, then expect it to answer point lookup queries that only Strategy 1 can serve. Or they use Strategy 1 alone and get surprised when users ask analytical questions that need the SQL engine. The right question before choosing a query pattern should not be about choosing a RAG or agentic approach; it is about deciding what we should actually index, and what can that index support? Pattern 1: RAG-Based Text-to-SQL What I Indexed For the pilot, I indexed three content types per table: Schema DDL — raw table definitions including column names, data types, and constraints. Structural foundation for syntactically correct SQL generation. Sample rows — 3–5 representative rows per table in natural language, following the row-level approach from Part 1. This grounds the LLM in actual data with value ranges, naming conventions, and patterns that DDL alone cannot convey. Table and column descriptions — plain English explanations of what each table contains and what each column means in business terms. Without this the LLM guesses intent from column names alone, which fails on abbreviated or legacy naming conventions. A single chunk for the sales_fact table looked like this: [TABLE] sales_fact DDL: CREATE TABLE sales_fact ( sale_id BIGINT, product_id INT, region_id INT, sale_date DATE, quantity INT, amount DECIMAL(12,2) ); Description: Records every completed sale transaction. One row per sale. Join keys: product_id → product_dim, region_id → region_dim Sample rows: sale_id=1001, product_id=42, region_id=3, sale_date=2024-01-15, amount=249.95 sale_id=1002, product_id=17, region_id=1, sale_date=2024-01-15, amount=89.90 This follows Strategy 3 (schema-aware) from Part 1, where DDL structure is combined with natural language description and sample data. It gives the LLM enough context to generate correct SQL without discovering the schema at runtime. How the Pipeline Works User question: Embed question Retrieve: relevant table chunks + curated NL→SQL examples Assemble prompt: schema context + examples + question LLM generates SQL (single pass) User executes on Athena Return results Adding curated NL→SQL pairs as few-shot examples was the single highest improvement achieved during the pilot. Even 15–20 well-chosen examples covering common patterns such as date filtering, simple aggregation, single table joins were able to meaningfully improve accuracy on similar questions without any model retraining. Where It Worked For simple, single-table queries, the RAG approach performed well and quickly. Questions like: "How many sales were recorded last month?" "What is the total revenue for product ID 42?" "Show me all transactions above $500 in January" These were resolved in low latency with high accuracy. The retrieved schema context was sufficient, and the LLM had seen similar patterns in the curated examples. Where It Failed, And Why The honest finding from the pilot: RAG-based Text-to-SQL works ok in some cases, but when it fails, it fails silently. Cross-table queries exposed the retrieval limitation. A question like "What were the top 10 SKUs by revenue last quarter, broken down by region?" requires joining sales_fact, product_dim, and region_dim. The retriever surfaces the most semantically similar chunks, often from two of the three tables but not the third. The LLM then generates SQL referencing a table it has an incomplete schema for, producing either a hallucinated column name or a syntactically valid but semantically wrong query. There is no retry loop. The system either returns a wrong answer or fails with nothing flagging what has happened. Medium complexity aggregations exceeded the single-pass limitations. Questions involving conditional aggregation, window functions, or multi-step logic exceeded what a single LLM pass could reliably produce from retrieved context. The model would generate SQL that executed without error but returned wrong results. This is the most dangerous failure mode because nothing surfaces it automatically. Schema similarity caused retrieval confusion. When multiple tables share similar column names such as amount in sales_fact and invoice_amount in billing_fact, the retriever sometimes surfaced the wrong table for ambiguous questions. The LLM then generated syntactically correct SQL against the wrong table entirely. The pattern was consistent: as query complexity increased, the accuracy dropped. Not because the LLM couldn't write SQL, but because the single-pass architecture had no mechanism to detect and recover from its own errors. This Is Not Really A Flaw The RAG-based approach is not a weaker version of the agentic pattern. It is a different tool with a genuine and well-defined use case. For simple to moderately complex single-table queries with predictable patterns, it is faster, cheaper, and operationally simpler than any agentic alternative. This limit only becomes a problem when you push it beyond that scope, which in most enterprise deployments, users will eventually do. The architectural lesson from the pilot: recognize the limitations early and build the escape hatch before users find it for you. When to Choose Pattern 1 Queries are predominantly single table or follow predictable join patterns Schema is stable — tables and columns change infrequently Latency is a hard requirement — sub-5-second response Team can invest in curating 20–50 Natural Language →SQL examples Cost per query is constrained — a single LLM call is significantly cheaper than 3–7 What Came Next The queries that broke Pattern 1 all shared a common characteristic: they required the system to observe the result of an intermediate step before deciding what to do next. A cross table join needs to know which tables exist before generating SQL. A complex aggregation needs to see whether the first attempt returned the right shape of data. That capability of generating, executing, observing, and correcting is exactly what the agentic approach provides. And the tool that made it practical was a direct connection to Athena. Part 3 covers the agentic pattern, tool implementation, retry failure mode, and the hybrid architecture that combines both approaches into something more practical than either alone.