Why a Single LLM Prompt Writes Bad SQL (and What a Multi-Agent Pipeline Fixes)
You can get SQL out of one prompt. Getting SQL you'd trust against production is a different problem, and it's why serious NL-to-SQL systems don't use one prompt.
Paste your schema into a chatbot, ask for "revenue by month," and you'll get SQL back. It might even be right. This is why a lot of people think natural-language-to-SQL is a solved problem you can build in an afternoon.
Then you point it at a real database, with a real schema, real business definitions, and real consequences for a wrong number, and the cracks show. The single-prompt approach fails in predictable ways, and each one is a reason serious systems use a pipeline instead.
One prompt asks the model to do four jobs at once
"Turn this question into SQL and run it" bundles four very different tasks into a single guess:
- Understand what the question refers to in your schema.
- Generate correct, dialect-specific SQL.
- Guarantee the query is safe to execute.
- Explain the result.
When all four happen in one shot, they can't check each other. The model can't validate its own safety in the same breath it's being creative about joins. There's no gate between "here's a query" and "run it." A mistake at any step flows straight through to your database.
A pipeline separates the jobs so each can be done well, and, critically, so later stages can catch the earlier ones' mistakes.
Where the single prompt goes wrong
It hallucinates schema. Given a big or unfamiliar database, a one-shot model invents plausible table and column names. SELECT revenue FROM sales looks fine until neither revenue nor sales exists. A dedicated resolution step grounds the query in real entities and real values first.
It misreads business meaning. "Active users," "churn," "enterprise tier": these are business definitions, not columns. One prompt guesses. A context-grounded stage looks them up in the synonyms and business rules your database actually carries.
It has no safety gate. A single prompt that both writes and runs SQL is trusting the model to never, under any phrasing or injection attempt, produce something destructive. That's a behavioral hope, not a guarantee. A separate validation stage rejects anything that isn't read-only before execution, which is a structural guarantee the model can't override.
It can't ask for help. Real questions are sometimes ambiguous. A one-shot pipeline has to guess and commit. A staged one can recognize ambiguity at resolution time and ask a clarifying question instead of confidently querying the wrong thing.
What the pipeline adds
Cazper's flow splits the work. An entity-resolution stage figures out what you mean and produces a structured plan. A generation stage turns that plan into dialect-specific SQL using your database's stored context. A validation stage enforces read-only before anything runs. Execution is bounded. And a final stage explains the result.
The value isn't that there are more steps. It's that the steps check each other. Resolution catches "what does this even refer to." Context catches "does this match the real schema." Validation catches "is this safe." No single stage, and no single prompt, can do all three, because doing one well requires not doing the others at the same time.
The honest summary
A single prompt is a great way to prototype natural-language-to-SQL. It's a poor way to run it. The gap between "produces SQL" and "produces SQL I'd put in front of my business" is exactly the set of failures a pipeline is built to catch: hallucinated schema, misread definitions, unsafe queries, forced guesses.
That gap is the whole product. It's why Cazper is a pipeline, not a prompt.
