SQL Query Syntax Error Repair Prompt

This prompt is designed for analytics engineers, dbt developers, and data platform operators who need to recover from a failed SQL query compilation. The core job-to-be-done is automated repair: you feed the prompt a failing SQL statement, the exact error message from the database or compiler, and the target SQL dialect, and it returns a corrected, parseable query. This is not a prompt for generating new queries from scratch or for optimizing query performance. It is a targeted recovery tool that sits inside a retry loop, invoked immediately after a syntax or compilation error is caught.

Use this prompt when you have a structured error message to work with and the original query intent is clear. The required inputs are the [FAILING_SQL], the [ERROR_MESSAGE], and the [TARGET_DIALECT] (e.g., PostgreSQL, BigQuery, Snowflake). The prompt is most effective for common syntax mistakes like missing commas, incorrect quoting, keyword misuse, or dialect-specific function signature errors. It is less reliable for logical errors where the SQL parses but returns wrong results, or for deeply nested dynamic SQL generated by complex macro systems where the error message is opaque. Do not use this prompt when the error is a runtime data issue (e.g., division by zero) or a permissions error; those require a different recovery path.

Before putting this prompt into production, you must implement a validation harness that executes the corrected query in a dry-run or sandboxed environment. The harness should compare the output schema of the repaired query against the expected schema to ensure the fix did not alter the query's intent. If the repair fails validation, the harness should increment a retry counter and re-invoke the prompt with the new error context, up to a defined retry budget. After the budget is exhausted, escalate to a human for manual review. Never apply a model-generated SQL fix directly to a production pipeline without automated validation and a rollback plan.

This template is the core instruction set for an AI model to act as a SQL repair agent. It is designed to be injected into a retry or recovery harness whenever a generated or templated SQL query fails compilation. The prompt forces the model to isolate the fault, explain the correction, and output only the fixed SQL, which makes the output safe to pipe directly into a subsequent execution attempt. Copy this block and replace the square-bracket placeholders with the actual failing query, the exact error message from your database, and the target SQL dialect.

textCopy
You are a precise SQL repair agent. Your only task is to fix syntax errors in a provided SQL query so that it compiles successfully against the target dialect. Do not change the query's logical intent, add new columns, or alter JOIN logic unless required to resolve the syntax error. You must output a JSON object with three fields: "fixed_sql", "explanation", and "confidence".

INPUT:
- Failing SQL:
```sql
[FAILING_SQL]

• Error Message:

textCopy
[ERROR_MESSAGE]

• Target Dialect: [TARGET_DIALECT]

CONSTRAINTS:

• Preserve the original query's SELECT columns, FROM sources, JOIN conditions, WHERE filters, GROUP BY clauses, and ORDER BY logic exactly as written, unless a clause is the direct cause of the syntax error.
• If the error is an unclosed quote, missing parenthesis, or invalid keyword, fix only that token and leave the rest of the query intact.
• If the error message points to a specific line and position, prioritize that location.
• If the error is ambiguous or you cannot determine a single high-confidence fix, set confidence to "low" and explain the ambiguity.
• Do not add comments to the fixed SQL unless comments were present in the original.
• Do not wrap the fixed SQL in markdown fences inside the JSON string; return it as a plain text string.

OUTPUT_SCHEMA: { "fixed_sql": "<corrected query string>", "explanation": "<one-sentence description of the change made>", "confidence": "high|medium|low" }

[EXAMPLES]

[TOOLS]

To adapt this template, start by replacing [FAILING_SQL] and [ERROR_MESSAGE] with the exact strings from your failed compilation step. The [TARGET_DIALECT] placeholder should be set to a specific identifier like PostgreSQL 15, BigQuery, Snowflake, or MySQL 8.0—this prevents the model from applying generic ANSI SQL fixes that might not work in your environment. The optional [EXAMPLES] block is where you can insert one or two few-shot demonstrations of correct repairs for your team's common failure patterns, such as unescaped single quotes in string literals or missing commas in column lists. The [TOOLS] block is reserved for future use if you wire the prompt into an agent harness that can execute a dry-run validation; for now, leave it empty or remove it. After adapting the template, always validate that the model's output JSON parses correctly before attempting to execute the fixed_sql.

When you integrate this prompt into a production pipeline, do not execute the repaired query blindly. The harness should parse the confidence field and route low-confidence repairs to a human for review or log them for later audit. Even high-confidence fixes should be run in a dry-run or EXPLAIN mode first to confirm the syntax is valid without mutating data. The next step after copying this template is to build the validation wrapper described in the Implementation Harness section, which will handle JSON parsing, confidence routing, and safe execution.

The SQL Query Syntax Error Repair Prompt is designed to sit inside a compilation or execution retry loop, not as a standalone chat interface. When a dbt model, scheduled query, or templated SQL statement fails with a syntax error, the harness should capture the failing SQL, the full error message, and the target dialect before calling the model. This context is the minimum required input. The prompt should be invoked only after a deterministic parser or linter has confirmed the error is syntactic rather than semantic—semantic errors (e.g., missing tables, type mismatches) require a different recovery path. The harness must enforce a retry budget: a maximum of three repair attempts per statement before escalating to a human or logging the failure for manual review.

Validation is the critical gate after each repair attempt. The corrected SQL must pass a dialect-specific parser (e.g., sqlfluff, sqlparse, or the target warehouse's dry-run API) before it is allowed to proceed. If the parser rejects the output, the harness should feed the new error back into the prompt for a second attempt, appending the previous attempt's diff to prevent oscillation. A semantic-diff check should compare the original and repaired queries to flag dropped columns, missing JOINs, or altered WHERE clauses that would change the query's intent. For dbt pipelines specifically, the harness should also verify that ref() and source() calls remain intact and resolve correctly against the project manifest. Log every repair attempt—original SQL, error, model response, parser result, and diff—to an audit table for postmortem analysis and prompt improvement.

Model choice matters here. Use a model with strong code-generation capabilities (e.g., GPT-4, Claude 3.5 Sonnet) and set the temperature low (0.0–0.2) to prioritize deterministic repairs over creative rewrites. The prompt should be stateless per attempt; do not carry conversation history across different SQL statements. If the harness detects that the error is a known anti-pattern (e.g., unquoted reserved keywords, dialect-specific date syntax), consider a fast-path rule-based fix before invoking the model to save latency and cost. Never auto-execute repaired SQL against a production database without human approval if the query modifies data (INSERT, UPDATE, DELETE, MERGE). For read-only SELECT statements, auto-execution is acceptable only after parser validation and a row-count estimate check to prevent accidental full-table scans. Wire the harness to emit a structured log event with fields repair_attempt, parser_passed, semantic_diff_summary, and escalation_triggered so that observability platforms can track repair effectiveness over time.