Text-to-SQL Fundamentals

How LLMs translate natural language into SQL: the techniques, failure modes, and production safeguards data engineers must own.

Text-to-SQL is the bridge between natural language and the warehouse. It lets non-engineers query production data without writing SQL -- but the data engineer still owns the infrastructure that makes it safe, accurate, and scalable. The LLM is one component; the schema, the sandboxing, and the eval harness are yours.

The failure modes are well-documented and predictable: schema ambiguity (two tables named "orders"), multi-hop joins (answering requires four tables), implicit aggregations ("how many customers" when the fact table has one row per order), and date arithmetic (fiscal quarters, timezone offsets). These look like LLM failures but are almost always schema design failures. A well-modeled, well-documented warehouse is dramatically easier to query in natural language than a legacy schema with cryptic column names and no descriptions.

Schema representation is the highest-leverage variable. How you describe tables, columns, and relationships to the LLM determines the majority of output quality. A column named "c_id" with no metadata will generate wrong SQL. The same column renamed "customer_id" with a description ("unique identifier, foreign key to customers.id") will not. This is data documentation work -- the kind that data engineers have historically deprioritized. Text-to-SQL changes the ROI calculation: good column descriptions now directly improve user-facing query accuracy.

Production text-to-SQL needs guardrails that belong entirely in the data engineer's domain: read-only credentials for the query-executing agent, per-query cost estimation before execution (the LLM will sometimes generate full table scans), result row limits, query timeout enforcement, and audit logging of every generated query. The LLM generates SQL; you decide whether it runs.

Key resources

• Techniques for Improving Text-to-SQL (Google Cloud)

  The clearest engineering-focused breakdown of what actually moves text-to-SQL quality: schema representation, few-shot examples, and query decomposition. Start here before anything else.

• SQLCoder -- Open-Source Text-to-SQL Model (Defog AI)

  A fine-tuned open-source model that beats GPT-4 on SQL generation benchmarks. Shows you what a purpose-built model can achieve and is a viable option for self-hosted deployments where sending schema to a third-party API is a compliance problem.

• Vanna.AI -- RAG-Based Text-to-SQL Framework

  The fastest way to prototype a production text-to-SQL system. The architecture -- train on schema DDL plus sample queries, then retrieve relevant context at inference time -- is the pattern most serious implementations converge on.

• DIN-SQL: Decomposed In-Context Learning for Text-to-SQL

  The paper that established decomposition (break the question into subproblems, then combine) as a reliable technique for hard queries. Useful for understanding why chain-of-thought prompting helps on multi-join, multi-aggregation questions.