Introduction

The second article in a three-part series shifts focus from indexing structured data to turning retrieved schema into actual SQL. It examines the practical boundaries of using retrieval-augmented generation for text-to-SQL tasks.

What Happened

The author describes a pilot project comparing two approaches on the same dataset. The core challenge: a retriever may surface the correct schema chunks, but translating those into accurate SQL is where most natural language querying implementations quietly fail. The piece walks through the RAG-based Text-to-SQL pattern, which indexes schema DDL, includes three to five representative sample rows per table, and adds plain-English descriptions of what each table and column represents in business terms.

A single chunk for the sales_fact table illustrated the approach: it combined DDL definition, a plain-language description of what the table records, join key references, and three sample rows with actual values. This level of context gives the language model enough information to generate syntactically correct SQL without needing to discover the schema at runtime.

The pipeline works in four steps: retrieve relevant table chunks, assemble a prompt using schema context and curated few-shot examples, let the LLM generate SQL in a single pass, and let the user execute the resulting query on Athena. During the pilot, adding 15 to 20 well-chosen natural language to SQL examples covering date filtering, simple aggregations, and single-table joins meaningfully improved accuracy on similar questions without any model retraining.

Why This Matters

The article argues that RAG-based text-to-SQL is not simply a weaker version of the agentic pattern; it is a distinct tool with a well-defined use case. For simple to moderately complex single-table queries, it remains faster, cheaper, and operationally simpler than any agentic alternative. However, the limitations become critical when users ask questions that go beyond that scope, which is inevitable in most enterprise deployments.

Three failure modes emerged from the pilot. First, cross-table queries often fail because the retriever surfaces chunks from only two of three required tables, leaving the LLM to generate SQL against an incomplete schema. Second, medium-complexity aggregations—conditional sums, window functions, multi-step logic—exceed what a single LLM pass can reliably produce, and the system offers no automatic way to detect the error. Third, schema similarity causes retrieval confusion when multiple tables share column names like amount across sales_fact and billing_fact, leading the LLM to generate syntactically correct SQL against the wrong table entirely.

Key Takeaways

  • Chunking strategy directly determines which query pattern a RAG system can successfully support.
  • Point lookups by ID or attribute work well with Strategy 1, while analytical queries typically require Strategy 3 or an agentic fallback.
  • Adding 15–20 carefully chosen few-shot examples dramatically improves accuracy without retraining the model.
  • Cross-table queries frequently fail because the retriever surfaces incomplete table context, causing the LLM to reference missing columns.
  • Schema similarity, such as the amount column appearing in multiple fact tables, triggers retrieval confusion and wrong SQL generation.
  • The approach excels when latency, cost, and predictable query patterns are the primary constraints.
  • Recognizing architectural limits early prevents user-facing failures and guides the path toward agentic patterns.

Conclusion

The author frames the RAG-based text-to-SQL pattern as a genuine, purpose-built tool rather than a flawed approximation. It delivers speed and cost benefits for the right use cases, but it has hard boundaries that users will eventually encounter. The key architectural lesson is to acknowledge those boundaries upfront and build an escape hatch—a direct database connection—before users hit them in production.

Part 3 of the series will explore the agentic approach that addresses these gaps, offering a hybrid architecture that combines retrieval with tool use, retry loops, and observation cycles for more robust natural language to SQL workflows.