Data Reconciliation Post-Migration Prompt

This prompt is designed for data engineers and backend platform teams who have just executed a database migration and need to confirm that data integrity survived the operation. The job-to-be-done is producing a structured, executable reconciliation checklist—row counts, checksums, distribution checks, and orphan detection—that validates the target state against the source state. It is not a migration review prompt for pre-flight risk assessment; use the Migration Rollback Safety Review or Schema Drift Detection prompts before deployment. This prompt assumes the migration has already run and you are now in the verification window.

The ideal user has access to both source and target database environments, can execute validation SQL, and can feed the results back into the prompt for interpretation. Required context includes the migration scope (which tables, columns, or transformations were applied), expected row counts or tolerance thresholds, and any known intentional differences such as filtered rows or recalculated columns. Without this context, the prompt will generate generic checks that may flag expected differences as failures, wasting triage time. Do not use this prompt when the migration is still in planning, when you lack query access to both environments, or when the migration involved opaque ETL tools whose internal state you cannot inspect.

The prompt produces a checklist, not a pass/fail verdict. It generates validation queries and interpretation guidance, but the harness must execute those queries and feed results back for analysis. In high-stakes migrations—financial ledgers, healthcare records, user deletion pipelines—always pair the prompt output with human review of flagged discrepancies. The prompt can identify mismatches, but it cannot decide whether a mismatch is an acceptable business deviation or a data corruption incident. Wire the output into a reconciliation runbook that logs every check, its result, and the human sign-off before closing the migration window.

This prompt template is the core instruction set you'll send to the model. It is designed to be wired into an automated harness that first executes validation SQL against the source and target databases, then feeds the results into this prompt for interpretation. The model's job is not to run queries, but to analyze the provided row counts, checksums, and distribution samples to produce a structured reconciliation report with a clear pass/fail status for each check.

textCopy
You are a senior data engineer validating the results of a database migration. Your task is to analyze the provided reconciliation data and produce a structured report.

## INPUT DATA
- Source Database Snapshot: [SOURCE_STATS]
- Target Database Snapshot: [TARGET_STATS]
- Row Count Comparison Results: [ROW_COUNT_DIFF]
- Checksum Validation Results: [CHECKSUM_RESULTS]
- Distribution Drift Samples: [DISTRIBUTION_SAMPLES]
- Orphan Record Detection Results: [ORPHAN_RESULTS]

## OUTPUT SCHEMA
Return a single JSON object with the following structure:
{
  "overall_status": "PASSED" | "FAILED" | "INCONCLUSIVE",
  "summary": "A concise, one-paragraph executive summary of the reconciliation outcome.",
  "checks": [
    {
      "check_name": "string",
      "status": "PASSED" | "FAILED" | "WARNING",
      "expected": "string describing the expected state",
      "actual": "string describing the observed state",
      "delta_description": "A precise description of the discrepancy, or null if passed.",
      "severity": "CRITICAL" | "HIGH" | "MEDIUM" | "LOW",
      "recommendation": "A clear, actionable next step to resolve the issue, or null if passed."
    }
  ],
  "requires_rollback": true | false,
  "rollback_reason": "A justification for the rollback recommendation, or null if rollback is not required."
}

## CONSTRAINTS
- Do not invent or assume data not present in the input. If a check cannot be evaluated due to missing input, set its status to "WARNING" and explain why.
- A checksum mismatch on any table must result in an overall_status of "FAILED" and requires_rollback set to true.
- A row count discrepancy greater than [ROW_COUNT_TOLERANCE]% must result in an overall_status of "FAILED".
- Distribution drift is a warning unless it exceeds [DRIFT_THRESHOLD]%, in which case it is a failure.
- Orphan records must be flagged as "CRITICAL" severity.
- If the overall_status is "INCONCLUSIVE", explain exactly what additional data or checks are needed to reach a conclusion.

To adapt this template, replace the square-bracket placeholders with actual data from your reconciliation harness. [SOURCE_STATS] and [TARGET_STATS] should be structured summaries including table lists, row counts, and schema versions. [ROW_COUNT_TOLERANCE] and [DRIFT_THRESHOLD] should be numeric values representing your team's acceptable deviation percentages (e.g., 0 for exact match, 0.5 for minor drift). Before putting this into production, run it against a known migration outcome—both a clean pass and a deliberate failure—to calibrate the severity and rollback logic. If the model ever recommends a rollback, route that decision to a human for final approval before executing any destructive action.

This prompt is designed to be the final interpretation layer in a multi-step reconciliation pipeline. It should not be called directly with raw migration metadata. Instead, the application harness must first execute the validation SQL queries (row counts, checksums, distribution checks, orphan detection) against both the source and target databases, collect the results, and format them into the structured [RECONCILIATION_DATA] input block. The prompt then interprets discrepancies, classifies severity, and generates a human-readable report. This separation ensures the model reasons over concrete evidence rather than guessing about data state.

The implementation should follow a strict execute → validate → interpret → review loop. First, run all reconciliation queries with timeouts and error handling; a failed query should not block the entire report but must be flagged as query_status: failed in the input. Second, validate that the harness output conforms to the expected input schema before calling the model—reject malformed measurement blocks early. Third, call the model with a temperature of 0.0–0.2 to minimize variance in severity classification. Fourth, parse the model's JSON output and validate it against the [OUTPUT_SCHEMA] before surfacing it. Any output that fails schema validation should trigger a single retry with the validation error appended to the [CONSTRAINTS] block. If the retry also fails, escalate the raw reconciliation data and partial output to a human operator via a review queue rather than silently dropping findings.

For production deployments, log every reconciliation run as an immutable record: input snapshot, model response, validation pass/fail, and human review decision. This audit trail is critical for compliance workflows (SOC2, HIPAA) where data integrity verification must be demonstrable. Avoid wiring this prompt directly into an automated rollback decision. The model's severity classification informs a human decision; it does not replace it. The most common production failure mode is stale reconciliation queries that reference dropped columns or renamed tables after a schema change—build a pre-flight check that validates query executability before feeding results to the model.