No More Phantom Columns: Finally Getting LLM-Generated SQL to Behave

Description and use

LLM produced queries with phantom columns absent from the new schema. The solution involved extracting relevant schema snippets, employing a generate_sql_query function with strict column constraints, and an error‑feedback loop. This reduced errors to ~5 % and enabled reliable SQL generation.

Problem

LLM-generated SQL kept inventing columns that did not exist in the new reporting schema, causing query failures and undermining reliability.

Outcome

Phantom column errors dropped from 100% to ~5%, enabling reliable, automated SQL generation for the new reporting schema.

Workflow

• 1. Build a Python utility to extract relevant table DDL snippets based on keyword/semantic search of the query.
• 2. Provide only those snippets to the LLM as context.
• 3. Define a function tool generate_sql_query with required sql_query and reasoning fields.
• 4. Prompt the LLM to call the function, enforcing that only columns in the provided snippet may be used.
• 5. Execute the returned SQL; on psycopg2.errors.UndefinedColumn, capture the error message.
• 6. Feed the error back to the LLM in the next turn and request a corrected query.
• 7. Repeat until a valid query executes or a fallback is triggered.

Evidence

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: SELECT * FROM user_events WHERE user_identifier = 123 AND event_name = 'login' ORDER BY created_at DESC LIMIT 10;

Source

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.

Source

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."

Source

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.

Source