Text to SQL with LLMs, Part 1: Why the Baseline Approach Fails on Real Databases

Every company we talk to wants the same thing: let anyone on the team ask the database a question in plain English and get a correct answer back. No waiting on the one person who knows SQL. No Monday morning report backlog. Just ask, get the number, move on.

Large language models made that goal look easy, and on a demo database it is. On your actual production database, with its 340 tables, cryptic column names, soft deletes, and three different definitions of revenue, it falls apart fast. This four-part series walks through exactly why, and then builds up, step by step, to the architecture that production text to SQL systems actually use.

Part 1 covers the baseline approach: what it looks like, why it kind of works, and the specific ways it fails. If you understand the failure modes, every technique in Parts 2 through 4 will make sense, because each one exists to close a specific gap.

What text to SQL means in practice

Text to SQL (also called natural language to SQL, NL2SQL, or "chat with your database") is the task of converting a plain-language question like "what were our top ten customers by revenue last quarter" into a SQL query that runs against your database and returns the right answer.

The stakes are different from most LLM use cases. If a chatbot writes an awkward sentence, someone notices. If a text to SQL system quietly double-counts revenue in a board report, nobody notices until the numbers stop matching, and by then trust in the whole system is gone. Accuracy is the entire product.

The baseline approach: paste the schema, ask the question

Here is version one of every text to SQL system ever built. Take your table definitions, drop them into a prompt, add the question, and send it to a model.

You are a SQL expert. Given the following schema, write a SQL query
that answers the user's question.

Schema:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    company_name VARCHAR(200),
    state_code CHAR(2),
    created_at DATETIME,
    is_deleted BIT DEFAULT 0
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_status VARCHAR(20),
    order_total DECIMAL(12,2),
    created_at DATETIME
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    sku VARCHAR(50),
    qty INT,
    unit_price DECIMAL(12,2)
);

Question: What is total revenue by customer this year?

Return only the SQL.

Run this against a modern model and you will get syntactically valid SQL almost every time. That is the seductive part. Modern LLMs have seen enormous amounts of SQL during training, and on small, clean, well-named schemas they are genuinely good.

The academic benchmarks tell the same story. Spider 1.0, the benchmark that defined the field for years, used clean schemas and relatively simple questions. Models pushed past 86 percent execution accuracy on it, agent-style systems reached 91 percent, and the leaderboard stopped accepting submissions in early 2024 because the benchmark was effectively solved.

Then researchers built benchmarks that look like your database, and the numbers collapsed.

What the benchmarks say about real databases

The BIRD benchmark was built specifically to test text to SQL against realistic conditions: 12,751 question and SQL pairs across 95 databases totaling 33 GB, with messy values, ambiguous column names, and questions that require outside business knowledge. Human data engineers score about 93 percent on it. As of mid-2026, the top of the leaderboard sits just above 80: the leading published pipeline reaches 81.95 percent on the test set, and in June 2026 Google announced Gemini-SQL2, a specialized text to SQL system built on Gemini 3.1 Pro, at 80.04 percent on the single-model track. Per Google's published comparison, general frontier models like Claude Opus 4.6 land closer to 70 on that same track, and 2026 papers measuring current models prompted zero-shot on BIRD's dev set report scores only in the low 60s at best.

Now rewind to why this benchmark exists, because the founding result still explains the 2026 leaderboard. When BIRD launched in 2023, ChatGPT scored 40 percent, and the strongest model of that era swung from under 35 percent to roughly 55 based purely on whether humans handed it written hints about the data. Three model generations later, that pattern is intact: the spread between today's zero-shot scores and the pipeline-driven top of the leaderboard is the same context gap, just at a higher altitude. That is the thesis of this entire series: with LLM text to SQL, the model matters less than the context you feed it.

Look at what actually closed the gap since 2023. Not raw model intelligence, but heavy pipelines layering schema retrieval, curated examples, candidate generation, and execution feedback. Even Gemini-SQL2's single-model entry ships with a published schema-grounding pattern. The pipeline is the product.

And BIRD is the friendly benchmark. Spider 2.0, released in late 2024, uses real enterprise databases, some with more than 3,000 columns, spread across BigQuery and Snowflake, with tasks that sometimes need over 100 lines of SQL. At launch, an agent framework built on the strongest reasoning model of the day solved 21.3 percent of tasks in the paper's evaluation, versus 91.2 percent on Spider 1.0 and 73 percent on BIRD (paper), a figure the maintainers later revised to 17.1 percent after evaluation fixes. As of April 2026, the leader on the Spider 2.0 Lite track is Oracle's SOMA-SQL at 72.02 percent execution accuracy, and its published architecture is worth previewing: retrieved question and SQL logs, schema enrichment, structured planning, a critique phase, and execution-grounded probing. Hold that list in mind. It reads like a table of contents for Parts 2 through 4 of this series.

So when a vendor demo hits every question perfectly on a five-table sample database, you now know exactly what you are looking at.

The five ways baseline text to SQL fails

After building these systems over production SQL Server, PostgreSQL, and ClickHouse estates, we see the same failure categories on nearly every project. Here they are, with the SQL to prove it.

1. The silent fan-out: joins that double-count money

This is the most expensive failure mode because the query runs, returns plausible numbers, and is wrong. Given the schema above and the question "total revenue by customer this year," models frequently produce something like this:

-- WRONG: looks reasonable, inflates every total
SELECT
    c.company_name,
    SUM(o.order_total) AS revenue
FROM customers c
JOIN orders o        ON o.customer_id = c.id
JOIN order_items oi  ON oi.order_id = o.id
WHERE YEAR(o.created_at) = YEAR(GETDATE())
GROUP BY c.company_name;

The join to order_items was never needed, but it multiplies each order's order_total by its line-item count. A three-item order gets counted three times. Every number in the result is inflated, none of them obviously so.

-- CORRECT: revenue lives on orders, so do not fan out through items
SELECT
    c.company_name,
    SUM(o.order_total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
GROUP BY c.company_name;

The model had no way to know that order_total is already an order-level rollup. Nothing in a bare CREATE TABLE statement says so.

2. Hallucinated tables and columns

Ask a question the schema cannot quite answer and the model will often invent what it needs: a revenue column that does not exist, a customer_name field when the real column is company_name, a regions table it assumes must be there. On a 300-table database where you only pasted a subset of the schema, this gets dramatically worse, because the model fills gaps with statistically plausible guesses from its training data.

3. Missing business logic

"Total revenue by customer" is not actually answerable from the schema alone. Does revenue include cancelled orders? Refunds? Test orders the dev team creates? Should soft-deleted customers (is_deleted = 1) appear? Every analyst at your company applies these filters from memory. The model applies none of them, because they exist nowhere in the prompt. The baseline query above happily counts cancelled orders and deleted customers.

4. Ambiguous terms with no definitions

"Active customers," "churn," "margin," "conversion": every business defines these differently, and some businesses define them three different ways depending on which department you ask. A model given no definitions will pick one interpretation, state it confidently, and hand a marketing lead a number that contradicts the finance dashboard.

5. Dialect and version mismatches

YEAR(GETDATE()) is T-SQL. On PostgreSQL you need EXTRACT(YEAR FROM now()). On ClickHouse, toYear(now()). Date math, string functions, TOP versus LIMIT, identifier quoting: models trained on all dialects at once will happily blend them if you do not pin the target down, and the resulting error messages confuse non-technical users into thinking the whole system is broken.

Cheap fixes that help immediately

Even before you build anything from Parts 2 through 4, four prompt changes measurably reduce failures:

1. Pin the dialect and version. "Write T-SQL for SQL Server 2019" beats "write SQL" every time, and prevents an entire category of syntax errors.
2. Inject the current date. Models do not reliably know today's date. "Assume the current date is 2026-07-18" makes "last quarter" and "this year" computable instead of guessable.
3. Forbid invention. "Only use tables and columns listed above. If the question cannot be answered from this schema, say so instead of writing a query." This converts hallucinations into honest refusals you can act on.
4. Constrain the output. One statement, read-only, with a row limit. This will not stop every bad query, but it narrows the blast radius while you build real guardrails (covered in Part 4).

These get you from bad to less bad. They do not get you to a system your operations manager can trust for a board report. That takes context engineering, and that is where the series goes next.

Where the series goes from here

Each remaining part closes one of the gaps identified above:

Part 2: Schema context engineering fixes failures 1, 2, and 5 by transforming a raw schema dump into curated, annotated, machine-readable context: column descriptions, sample values, relationship maps, and views built as a clean interface for the AI.

Part 3: Retrieval, golden queries, and business context fixes failures 3 and 4 with retrieval-augmented generation: verified question-and-SQL pairs, a business glossary, and value-level grounding so "Illinois" finds 'IL'.

Part 4: Production systems covers semantic layers, agentic self-correction, database-level guardrails, and the evaluation harness that tells you your real accuracy number instead of leaving you to guess.

Get self-service analytics that your team can trust

Field Craft Labs is a Chicagoland software and data engineering consultancy that builds AI data assistants for small and mid-sized businesses. We connect safely to your existing databases, build the context layer that makes answers accurate, and deploy a chat interface so anyone on your team can pull numbers and write reports without knowing SQL, and without pinging your one database person.

We have built and run these systems over production SQL Server, PostgreSQL, and ClickHouse environments, and we bring that experience to yours. Get in touch for a working assessment on your actual data, not a canned demo.

Continue to Part 2: Schema Context Engineering That Actually Moves Accuracy.