dbt Model Compilation Error Recovery Prompt

This prompt is for analytics engineers and data platform developers who need to recover from a dbt model compilation failure. The job-to-be-done is straightforward: take a failing dbt model SQL file, the exact compilation error from the dbt run, and the relevant project context (such as upstream model schemas or ref() definitions), and produce a corrected model file that compiles successfully. The ideal user is someone who understands dbt and SQL but wants to accelerate the fix cycle, not someone expecting the prompt to replace their own understanding of the project's business logic.

Use this prompt when the error is a compilation error—syntax mistakes, undefined macros, incorrect ref() or source() calls, Jinja templating errors, or missing configuration blocks. It is not designed for runtime execution errors (e.g., a UNIQUE constraint violation in the warehouse) or for logical bugs where the SQL compiles but returns incorrect results. The prompt requires three concrete inputs: the failing model SQL, the full error message from dbt, and a project context block that includes at least the schema of any upstream models referenced by ref(). Without the project context, the model cannot reliably fix broken references and may hallucinate column names.

Do not use this prompt for models that fail due to missing data, warehouse permissions, or infrastructure issues. It is also a poor fit for models where the compilation error is a symptom of a deeper design problem that requires a human to rethink the transformation logic. After using the prompt, you must validate the output by running dbt compile on the corrected model and checking that all downstream ref() relationships remain intact. For high-stakes production models, a human reviewer should approve the fix before it is merged. The next section provides the copy-ready prompt template you can adapt to your project.

The following prompt template is designed to be copied directly into your AI harness, IDE, or orchestration layer. It accepts the failing SQL, the exact compiler error, and the broader project context to produce a corrected model file. The template uses square-bracket placeholders for all dynamic inputs, ensuring it can be parameterized programmatically before each inference call. Treat the output as a suggested fix, not an authoritative change—always validate the corrected SQL by running dbt compile in your target environment.

textCopy
You are an expert analytics engineer specializing in dbt and SQL transformation. Your task is to diagnose and fix a dbt model that failed to compile.

## INPUT
- Failing dbt Model SQL:
```sql
[MODEL_SQL]

• Compilation Error Message:

textCopy
[COMPILATION_ERROR]

• Project Context (optional):
  • Target Database Dialect: [DB_DIALECT]
  • Referenced Models and their schemas: [REFERENCED_MODELS]
  • dbt Project Configuration (relevant macros, variables, packages): [PROJECT_CONFIG]

OUTPUT_SCHEMA

Return a valid JSON object with the following structure: { "error_classification": "syntax_error | undefined_model | invalid_jinja | type_mismatch | config_error | other", "root_cause_summary": "A one-sentence explanation of what caused the failure.", "corrected_model_sql": "The full, corrected SQL for the model file. Ensure it is syntactically valid for the target dialect.", "change_log": [ { "line_number": 12, "description": "Added a comma after the 'status' column." } ], "downstream_impact_assessment": "An analysis of whether the fix changes the model's grain, column names, or data types, and whether any downstream refs need updating.", "confidence_score": 0.95 }

CONSTRAINTS

1. Do not change the model's logical intent or grain unless absolutely necessary to resolve the error.
2. Preserve all existing column names and aliases. If a column must be renamed, flag it explicitly in the downstream impact assessment.
3. If the error involves an undefined reference, check if it's a typo, a missing ref, or a missing source definition before suggesting a fix.
4. For Jinja errors, ensure the corrected template is syntactically valid and does not introduce new undefined variables.
5. If you cannot determine a fix with high confidence, set the confidence score below 0.7 and provide a clear explanation in the root cause summary.
6. Do not invent table names, column names, or business logic. Only use information present in the input.

To adapt this template for your environment, replace the placeholders with data from your dbt run artifacts. The [MODEL_SQL] should be the raw file content, and [COMPILATION_ERROR] should be the exact stderr or logs from the dbt compile command. For [REFERENCED_MODELS], you can inject the output of dbt ls or parse your manifest.json to provide upstream schemas. The [PROJECT_CONFIG] placeholder is critical for Jinja-heavy projects; include any custom macros or variables that the failing model uses. If your harness cannot provide rich project context, remove the optional fields from the prompt to avoid confusing the model with empty sections.

This prompt is designed to be called programmatically from a dbt Cloud job, a GitHub Actions workflow, or a local pre-commit hook. The harness should capture the failing model's SQL, the full compilation error message from dbt compile or dbt run, and the relevant project context (schema YAML, upstream model refs, and macros if they appear in the error trace). Do not send the entire project repository; instead, extract only the files referenced in the error and the immediate dependency chain. The prompt expects these inputs as structured variables: [FAILING_MODEL_SQL], [COMPILATION_ERROR], and [PROJECT_CONTEXT] (a concatenated string of relevant YAML and SQL files). The harness should also inject [DIALECT] (e.g., snowflake, bigquery, redshift) to prevent syntax mismatches.

Validation and retry logic: After the model returns a corrected SQL block, the harness must write it to a temporary file and run dbt compile --select <model_name> in a sandboxed environment. If compilation fails again, extract the new error and feed it back into the prompt as a second attempt with the [PREVIOUS_ATTEMPT] and [NEW_ERROR] variables populated. Implement a hard retry budget of 3 attempts. If all attempts fail, log the full retry trace, flag the model for human review, and block the PR merge. Downstream ref integrity check: After a successful compilation, run dbt ls --select <model_name>+ to identify all downstream models and execute a dry-run compile on them. Any new failures introduced by the fix must be surfaced to the developer before the change is accepted.

Model choice and latency: Use a model with strong SQL reasoning capabilities (e.g., Claude 3.5 Sonnet, GPT-4o) and set temperature=0 for deterministic fixes. The prompt is stateless, so each retry is an independent call. For CI/CD integration, expect a 5-15 second latency per attempt; this is acceptable for a blocking quality gate but too slow for an interactive IDE loop. Logging and audit: Log every prompt invocation with the model name, input hash, output diff, compilation result, and retry count. Store these logs alongside the dbt artifacts so that analytics engineers can trace why a fix was applied. Human approval gate: For models tagged as critical or marts in the dbt project, require a human to approve the diff before the fix is committed, even if compilation succeeds. The harness should open a PR comment with the before/after SQL diff and the compilation result, then wait for an approving review.

What to avoid: Do not allow the harness to auto-merge fixes without compilation verification. Do not send secrets, environment variables, or full profile configurations to the model. Do not use this prompt for models that fail due to data issues (e.g., division by zero at runtime) — it targets compilation errors only. For runtime errors, use the SQL Query Syntax Error Repair Prompt or the Data Validation Failure Retry Prompt instead. If the error involves a custom macro with complex Jinja logic, the prompt may struggle; in those cases, escalate directly to the macro author rather than exhausting the retry budget.