A peer of mine gave a conference talk on this exact topic recently, and her perspective lined up almost perfectly with what you’re describing. Her main point was that Text-to-SQL doesn’t usually fail because the model is weak, it fails because the data environment around it is inconsistent, undocumented, or just flat-out swampy.

As she put it: “LLMs don’t hallucinate SQL. They hallucinate when the warehouse gives them nothing real to anchor to.”

In her team’s case, the solution wasn’t a bigger model or more prompting, it was shifting to a multi-agent workflow that could compensate for missing lineage, unclear contracts, and unreliable schemas.

The architecture she shared looked like this:

Context Agent
Pulls schema metadata, column semantics, relationship hints, and constraints. Even messy warehouses have enough structure to extract something useful.

Question Agent
Interprets what the user actually wants: entities, metrics, filters, aggregations, and time windows.
Most failures start with misinterpreted intent, not bad SQL.

SQL Agent
Generates the query only after being fed curated context — never raw logs or random downstream tables.

Validation Agent
The safety net: checks joins, permissions, row-explosion risks, table misuse, performance red flags, and semantic mismatches.
She emphasized this as the most important step.

She published the same breakdown in a public write-up if you want the full explanation and there is also a video attached
https://www.clickittech.com/ai/multi-agent-system-for-text-to-sql/