Okay, so picture this: we're rolling out a brand new reporting schema for our platform. Shiny, optimized, a little different from the old one. My grand vision? Let our team ask questions in plain English and have an LLM spit out the SQL. No more waiting for dev cycles just to get a COUNT(*) from a new table.
Sounds great on paper, right? The promise of text-to-SQL is seductive. I mean, it can generate SQL. Give it a schema, ask a question, and boom, query. But my early experiments quickly turned into a battle against phantom columns.
The Problem: Ghost in the Machine (or, in the Schema)
The LLM, bless its creative heart, kept making stuff up. I'd feed it our beautiful, meticulously designed CREATE TABLE statements for, say, our user_events table with event_id, user_id, event_type, event_timestamp. Then I'd ask it, "Show me the last 10 'login' events for user_id 123." And it'd give me back something like:
sql
SELECT * FROM user_events
WHERE user_identifier = 123 AND event_name = 'login'
ORDER BY created_at DESC LIMIT 10;
See the issue? user_identifier? event_name? created_at? None of those exist in my schema. It was close enough to be frustrating, but just wrong enough to crash hard with psycopg2.errors.UndefinedColumn: column "user_identifier" does not exist every single time. It was like it had seen similar schemas in its training data and decided to just invent columns that felt right, even when given explicit instructions.
This wasn't just a minor annoyance; it completely undermined the reliability of the system. I couldn't trust anything it generated, and manual validation defeated the whole purpose.
What I Tried (and Mostly Failed At)
I spent a good three days banging my head against this. My initial openai client was 1.3.0, and I even upgraded to 1.10.0 thinking some magic might happen there. It didn't. The problem wasn't the library; it was my approach.
Dumping the Entire Schema: My first thought was, "More context equals better results, right?" So I serialized our entire reporting schema (which has about 47 tables now) into a massive string and shoved it into the prompt. The LLM either got confused, missed crucial details, or the prompt simply became too long for gpt-3.5-turbo to handle effectively. gpt-4 was better, but prohibitively slow and expensive for every single query.
Aggressive System Prompts: I tried everything. "ONLY use column names from the provided schema." "DO NOT invent columns." "STICK STRICTLY to the schema definitions." I even tried putting it in all caps and using negative prompting ("do not use..."). It felt like talking to a very polite, very helpful, but occasionally hallucinating assistant.
Few-Shot Examples: I gave it a dozen good (natural language, correct SQL) pairs based on our new schema. This helped for queries very similar to the examples. But as soon as a new pattern emerged or a slightly different column was needed, it would still slip into the phantom column trap. Maintaining enough examples to cover all bases felt like writing the SQL myself again, which, again, defeated the purpose.
The Breakthrough: Contextual Tools and Tighter Guardrails
The real game-changer wasn't one silver bullet, but a combination of focused context provision and leveraging the LLM's function-calling capabilities. It became a multi-stage process, less about magic and more about engineering.
1. Smart Schema Snippet Generation
Instead of the whole schema, I realized the LLM only needed the relevant parts. This was critical for our new schema, which the LLM had no prior training on.
I built a small Python utility that, given a natural language query, would do a quick keyword scan (and eventually, I'll layer in semantic search using embeddings) over our custom schema metadata. This metadata isn't just CREATE TABLE statements; it includes human-readable descriptions for tables and columns too.
For example, if the user asks "How many active subscriptions do we have in October?", my utility identifies subscriptions and users tables (because active might relate to user status, though subscriptions holds the core info). Then, it dynamically generates a concise DDL snippet only for those tables, like this:
sql
-- Table: subscriptions
-- Details about user subscriptions.
CREATE TABLE subscriptions (
subscription_id UUID PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(user_id),
plan_type VARCHAR(50) NOT NULL, -- e.g., 'monthly', 'annual'
start_date DATE NOT NULL,
end_date DATE
);
-- Table: users
-- Information about registered users.
CREATE TABLE users (
user_id UUID PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100),
signup_date DATE NOT NULL
);
This focused context greatly reduced the LLM's opportunity to hallucinate.
2. Function Calling for Strict SQL Output
This was the biggest win. Instead of asking the LLM to just return SQL as raw text, I defined a tool for it. The LLM's job became to call a function generate_sql_query with specific arguments.
python
tools = [
{
"type": "function",
"function": {
"name": "generate_sql_query",
"description": "Generates a PostgreSQL query to answer a natural language question based on the provided schema.",
"parameters": {
"type": "object",
"properties": {
"sql_query": {
"type": "string",
"description": "The PostgreSQL query."
},
"reasoning": {
"type": "string",
"description": "Brief explanation of the query's logic."
}
},
"required": ["sql_query", "reasoning"],
},
}
}
]
The system prompt was then refined: "You must use the generate_sql_query tool. The sql_query argument must only use column names present in the provided schema snippet. If a column is not explicitly in the snippet, you cannot use it." This explicit constraint, enforced by the structured nature of function calls, felt like putting the LLM in a very specific, well-defined box, limiting its creative freedom where it mattered most.
3. The Feedback Loop (Error Handling)
Even with all these guardrails, occasionally a phantom column would slip through (maybe one time out of twenty, down from ten out of ten). My application catches the psycopg2.errors.UndefinedColumn error during execution. I then feed that exact error message back to the LLM in the next turn of the conversation, along with a prompt to correct its previous output. This automated retry and self-correction mechanism made the system robust.
The Takeaway
It wasn't about a more powerful LLM model, but about smarter interaction design. The LLM is an incredible reasoning engine, but it needs clear, concise, and relevant context, alongside strict guardrails on its output. It took me a solid week of iterating, trying different models, prompt structures, and context injection methods, but now, finally, our reporting dashboard can reliably generate SQL for our new schema. It feels pretty great.
Now, to make that schema selector even smarter... 🤖
Top comments (1)
Implementing structured tool calls combined with a dynamic schema snippet retriever solves the root cause of LLM hallucinations in text-to-SQL pipelines. To make your retry loop even more robust, you can also inject schema foreign-key constraints directly into the function arguments so the model understands relational joins. Let me know if you run into any parsing edge cases with your retry handler and I can help outline the execution state machine right here.