Constraint Violation Self-Correction Prompt

This prompt is designed for data engineers and MLOps teams who need to automate recovery from row-level constraint violations during ETL staging-area loads. The primary job-to-be-done is to take a single violating row, the exact constraint definition it failed, and any referenced context (such as a parent table lookup for a FOREIGN KEY violation) and return either a repaired row that satisfies the constraint or a structured rejection with a specific violation code. It is not a general data cleaning tool or a one-shot fix for an entire batch of bad data. Its value is realized inside a retry loop within a data pipeline harness, where the model acts as a specialized repair function for individual records that block ingestion.

To use this prompt effectively, the calling harness must provide three concrete inputs: the raw violating row, the DDL or constraint definition that was violated, and the specific error message from the database or validation engine. For FOREIGN KEY violations, the harness must also execute a lookup against the referenced table and supply the set of valid key values as context before invoking the model. The prompt assumes the upstream error is available and unambiguous. It is not designed to diagnose silent data corruption, infer missing constraints, or handle violations that span multiple rows simultaneously. The output must be validated by the harness before the repaired row is re-attempted against the target table.

Do not use this prompt when the violation rate is high enough to suggest a systemic upstream issue, such as a broken source system or a misconfigured schema mapping. In those cases, root-cause analysis and pipeline repair are required before row-level retries. Do not use this prompt for constraints that carry regulatory or financial materiality without adding a human review step after the model's repair suggestion. The prompt is a tactical recovery tool for staging-area loads, not a replacement for data contracts, schema enforcement, or upstream data quality checks. After reading this section, proceed to the prompt template to see the exact instruction structure, then review the implementation harness to understand how to wire it into a retry loop with validation, logging, and escalation thresholds.

This prompt template is designed to be pasted directly into your AI harness. It instructs the model to act as a data repair specialist, taking a single violating row, the constraint it broke, and any relevant context (such as referenced table samples or lookup values). The model must return either a corrected row that satisfies the constraint or a structured rejection with a specific violation code. All dynamic values are represented as square-bracket placeholders—replace these with live data from your pipeline before each call.

textCopy
You are a data repair specialist for an ETL staging area. Your task is to fix a single data row that has violated a database constraint during a load operation.

## INPUT
- Violating Row (as JSON): [VIOLATING_ROW]
- Constraint Definition: [CONSTRAINT_DEFINITION]
- Constraint Type: [CONSTRAINT_TYPE] // e.g., NOT NULL, UNIQUE, CHECK, FOREIGN KEY
- Referenced Context (if applicable): [REFERENCED_CONTEXT] // e.g., sample of existing rows for UNIQUE, lookup table for FOREIGN KEY
- Target Table Schema: [TARGET_TABLE_SCHEMA]

## INSTRUCTIONS
1. Analyze the violating row against the constraint definition and referenced context.
2. Determine if a valid repair is possible without guessing critical business values.
3. If a repair is possible, produce a corrected row that satisfies the constraint.
4. If a repair is not possible, produce a structured rejection.

## OUTPUT SCHEMA
Return ONLY a valid JSON object with this exact structure:
{
  "status": "repaired" | "rejected",
  "repaired_row": { ... } | null, // The full corrected row as a JSON object if status is "repaired"
  "violation_code": "STRING", // A stable code from the list below
  "repair_description": "STRING", // A concise explanation of what was changed or why it was rejected
  "repair_confidence": 0.0-1.0 // Your confidence in the repair, where 1.0 is certain
}

## VIOLATION CODES
Use one of these exact codes:
- `REPAIRED_NOT_NULL_FILLED`: Filled a NULL with a sensible default.
- `REPAIRED_UNIQUE_DEDUPED`: Resolved a duplicate by adjusting a value.
- `REPAIRED_CHECK_CORRECTED`: Corrected a value to satisfy a CHECK constraint.
- `REPAIRED_FK_MAPPED`: Mapped a value to a valid foreign key reference.
- `REJECTED_UNRECOVERABLE`: The row cannot be repaired without guessing critical business data.
- `REJECTED_AMBIGUOUS`: Multiple valid repair options exist and the correct one is unclear.
- `REJECTED_CONTEXT_MISSING`: Required referenced context was not provided.

## CONSTRAINTS
- Do not invent business-critical values like customer IDs, monetary amounts, or dates that are not derivable from the provided context.
- For NOT NULL violations on foreign key columns, prefer rejection over generating a placeholder value.
- For UNIQUE violations, prefer adjusting the non-key attributes if a clear pattern exists; otherwise reject.
- Preserve all other fields in the row exactly as provided unless they must change to satisfy the constraint.
- If the constraint definition references a lookup table, use only values present in the referenced context.

To adapt this template, replace each [PLACEHOLDER] with live data from your pipeline. The [VIOLATING_ROW] should be a JSON object representing the entire row that failed validation. The [CONSTRAINT_DEFINITION] should contain the exact constraint rule as defined in your database or data quality framework. For FOREIGN KEY violations, populate [REFERENCED_CONTEXT] with a sample of valid keys from the referenced table—this grounds the model's repair decision in real data and prevents hallucinated references. After running the prompt, validate the output JSON against the schema before passing the repaired row to your loader. Log all rejections for manual review and track the repair_confidence field to build a quality baseline over time.

The Constraint Violation Self-Correction Prompt is designed to sit inside a staging-area recovery loop, not as a standalone chatbot. When a batch load into a staging table fails with a constraint violation (NOT NULL, UNIQUE, CHECK, FOREIGN KEY), the pipeline should extract the offending row, the constraint definition from the database catalog, and any referenced context (e.g., the parent row for a FOREIGN KEY violation). This triplet—violating row, constraint, and context—becomes the input to the prompt. The model returns either a repaired row that satisfies the constraint or a structured rejection with a violation code. The harness must never automatically commit the repaired row to the target table without validation.

Wire the prompt into an ETL framework (Airflow, Prefect, Dagster, or a custom Python worker) as a recovery task triggered by a constraint error exception. The task should: (1) catch the database error and parse the constraint name and violation type from the exception message or the database's error metadata (e.g., PostgreSQL's PGresult.error_field); (2) query the information schema (information_schema.table_constraints, information_schema.check_constraints, information_schema.key_column_usage) to retrieve the full constraint definition; (3) for FOREIGN KEY violations, query the referenced table to retrieve the parent row that the violating row should reference; (4) assemble the prompt with the violating row serialized as a JSON object, the constraint definition as a SQL DDL fragment, and the referenced context as a JSON object; (5) call the model with a low temperature (0.0–0.2) to maximize deterministic repair behavior; (6) validate the model's output against a strict JSON schema that requires either a repaired_row object or a rejection object with a violation_code and reason; (7) if the output is a repaired row, re-run the constraint check against the staging table schema before inserting; (8) log every repair decision—original row, constraint, model output, validation result, and final disposition—to an audit table for traceability.

Retry logic must include a budget. Set a maximum of 2 retries for the same row. If the model's repair fails validation on the first attempt, feed the validation error back into the prompt as additional context and retry once. If the second attempt also fails, quarantine the row to a dead-letter table and escalate to a human operator via the notification channel your pipeline already uses (Slack, PagerDuty, email). Never loop indefinitely. For UNIQUE constraint violations where the model proposes a value that still collides, the harness should detect the collision on re-validation and treat it as a retryable failure. For CHECK constraints involving business logic (e.g., start_date < end_date), the harness should log the original expression and the model's interpretation to help operators audit whether the repair preserved the intent. Model choice matters: use a model with strong SQL and data reasoning capabilities (GPT-4o, Claude 3.5 Sonnet, or equivalent). Avoid lightweight models for this task because constraint repair requires understanding schema semantics, not just pattern matching.