SQL Query Error Recovery Prompt

This playbook is for data engineers and analytics teams who need to automatically recover from failed SQL queries in automated pipelines, scheduled reports, or AI-assisted query editors. The prompt takes a failed query and its database error message and produces a corrected query with an explanation of the fix. Use this when you have a query that failed validation or execution and you want the model to diagnose and repair it before retrying. The core job-to-be-done is reducing manual toil in debugging routine SQL failures, such as syntax errors, type mismatches, or missing aliases, that block a data pipeline stage or a dashboard refresh.

The ideal user is a developer or operator who already has the failing SQL text and the exact error message from the database engine (e.g., PostgreSQL, Snowflake, BigQuery). This prompt assumes the original query intent is correct and that the failure is a technical error in the query's construction, not a logical flaw in the analysis. It works best when the error message is specific, such as a parse error pointing to a line number or a semantic error about column ambiguity. Do not use this prompt for generating new queries from natural language questions; that is a separate text-to-SQL generation task. Do not use it when the error is caused by missing data, permission denials, or infrastructure outages, as those require a different recovery path.

Before wiring this into a production harness, ensure you have a validation step that executes the corrected query against a real schema or a dry-run explain plan. A corrected query that is syntactically valid but semantically wrong (e.g., joining on the wrong column) can silently corrupt downstream data. Start with a retry budget of 1-2 attempts and escalate to a human if the model cannot resolve the error after two cycles. The next section provides the copy-ready prompt template you can adapt for your database dialect and error format.

This section provides the core prompt template for the SQL Query Error Recovery workflow. It is designed to be copied directly into your application code, prompt management system, or orchestration layer. The template uses square-bracket placeholders that you must replace with live data at runtime: the failed SQL query, the exact database error message, the relevant schema context, and any dialect-specific constraints. The prompt instructs the model to act as a senior database engineer, analyze the failure, and return a corrected query alongside a clear explanation of the root cause and the fix applied.

textCopy
You are a senior database engineer reviewing a failed SQL query.
Your task is to analyze the error, correct the query, and explain the fix.

## INPUT
Failed SQL Query:
```sql
[FAILED_SQL_QUERY]

Database Error Message:

codeCopy
[DATABASE_ERROR_MESSAGE]

CONTEXT

Relevant Schema (tables, columns, types):

codeCopy
[RELEVANT_SCHEMA_DEFINITION]

SQL Dialect and Version: [SQL_DIALECT]

CONSTRAINTS

• Preserve the original query intent. Do not change the business logic unless it is the direct cause of the error.
• If the error is a syntax violation, fix the syntax.
• If the error is a semantic violation (e.g., column not found, type mismatch, ambiguous reference), use the provided schema to resolve it.
• If the error is a permission or access violation, explain the issue and suggest the required privilege; do not attempt to bypass security.
• If the error cannot be fixed with the information provided, state what is missing.
• Return only valid SQL. Do not include placeholder comments or TODO markers in the corrected query.

OUTPUT_SCHEMA

Return a single JSON object with the following structure: { "error_category": "syntax" | "semantic" | "permission" | "ambiguous" | "unknown", "root_cause": "A concise, one-sentence diagnosis of the failure.", "fix_summary": "A one-sentence description of the correction applied.", "corrected_query": "The full, corrected SQL query as a string.", "requires_schema_change": false, "requires_permission_change": false }

To adapt this template for your environment, replace each placeholder with live data from your application context. The [FAILED_SQL_QUERY] and [DATABASE_ERROR_MESSAGE] are mandatory and come directly from your query runner or database client. The [RELEVANT_SCHEMA_DEFINITION] should be a compact but precise representation of the tables, columns, and data types involved—extracted from your schema registry, INFORMATION_SCHEMA, or migration files. The [SQL_DIALECT] field (e.g., PostgreSQL 16, MySQL 8.0, Snowflake) is critical because syntax and function availability vary across dialects. If your harness already knows the dialect from the connection string, inject it automatically. Before deploying this prompt, test it against a curated set of known error-query pairs and validate that the corrected_query field parses successfully and executes without error in a sandboxed environment. For production use, always run the corrected query through an EXPLAIN plan or a dry-run mode before execution, and escalate any requires_permission_change: true responses to a human DBA for review.

The SQL Query Error Recovery Prompt is designed to sit inside an automated data pipeline or an analyst-facing tool where a failed query triggers a repair loop. The harness receives the original query, the database error message, and optionally the target schema, then calls the LLM to produce a corrected query. The corrected output must never be executed directly against a production database without validation. The harness should first run the corrected query against a sandboxed or read-only replica, or at minimum perform a dry-run EXPLAIN to confirm syntactic validity and schema compatibility before applying the fix.

A robust implementation wraps the prompt in a retry budget with structured validation gates. After receiving the corrected query from the model, the harness should: (1) parse the output to extract the SQL block, discarding any explanatory text; (2) validate the SQL against the target dialect using a parser library such as sqlglot or sqlparse; (3) run EXPLAIN or EXPLAIN ANALYZE on a safe replica to confirm the query compiles against the actual schema; (4) compare the corrected query's column references and table names against the known schema to detect hallucinated identifiers; and (5) if all checks pass, execute the query on a read-only replica and confirm it returns without error. If any step fails, the error message from that step should be fed back into the prompt as additional [ERROR_CONTEXT] for a subsequent retry, up to a maximum of three attempts. After three failures, the harness should escalate to a human operator with the full error chain and the original query preserved.

Model choice matters for this workflow. SQL repair requires strong code-generation capabilities and dialect awareness. Use a model with proven SQL performance, such as gpt-4o, claude-3.5-sonnet, or a fine-tuned code model. Set temperature low (0.0–0.2) to reduce syntactic variation. The prompt must include the target SQL dialect (PostgreSQL, MySQL, BigQuery, etc.) in the [DIALECT] placeholder, as error messages and syntax rules differ significantly across dialects. Log every attempt—original query, error, corrected query, validation results, and final outcome—for auditability and prompt improvement. Never log raw connection strings, credentials, or sensitive data that may appear in error messages; scrub those before storage.

For high-risk environments where queries touch production data, add a human approval gate after validation but before execution. The harness can present the diff between the original and corrected query alongside the validation results, and require explicit approval. If the prompt is embedded in a chat-based analyst tool rather than a fully automated pipeline, the harness should surface the corrected query and explanation to the user for review, rather than auto-executing. The key principle: the LLM proposes a fix, but the harness owns safety, validation, and the decision to execute.