Database Migration Test Data Verification Prompt

This prompt is for engineers who are about to execute a schema migration and need to verify that data survived the transformation intact. The job-to-be-done is generating a small, targeted set of pre-migration and expected post-migration record pairs, along with the SQL comparison queries that will prove correctness after the migration runs. The ideal user is a backend engineer, database reliability engineer, or platform developer who already has the DDL for both the old and new schemas and understands the migration logic—column renames, type casts, default value applications, and dropped or added columns.

You should use this prompt when the migration is complex enough that manual spot-checking is risky: think breaking normalizations, splitting or merging columns, changing enum representations, or applying new default values to existing rows. The prompt works best when you provide the exact source and target DDL, the migration rules in plain language, and a representative sample of real source rows. It is not a replacement for a full data diff tool or a migration framework's built-in verification. Do not use this prompt for simple additive migrations (new nullable columns only), for verifying entire production-scale tables, or when you cannot supply concrete source rows. The output is a verification harness, not a migration script.

Before you run this prompt, gather the source table DDL, the target table DDL, a written description of every transformation rule, and at least three representative source rows that exercise different code paths. After you receive the output, treat the generated comparison queries as a starting point: review them for correctness, run them in a staging environment first, and add any edge cases the prompt missed. If the migration involves financial, healthcare, or otherwise regulated data, a human reviewer must sign off on the verification queries before they are used as a gate for production deployment.

This prompt template is the core instruction you will send to the model. It is designed to accept a source schema, a target schema, and a set of migration rules, then produce a verification dataset and the SQL queries needed to confirm correctness. The template uses square-bracket placeholders so you can swap in your own schemas, constraints, and output format requirements without rewriting the logic.

textCopy
You are a database migration verification engineer. Your task is to generate a test dataset and comparison queries that validate a schema migration.

## INPUTS
- Source Schema (DDL): [SOURCE_DDL]
- Target Schema (DDL): [TARGET_DDL]
- Migration Rules: [MIGRATION_RULES]
- Row Count: [ROW_COUNT]
- Risk Level: [RISK_LEVEL]

## MIGRATION RULES FORMAT
Each rule describes a transformation:
- Column renames: old_name -> new_name
- Type casts: column_name: old_type -> new_type
- Default value application: column_name: DEFAULT [value]
- Dropped columns: column_name: DROPPED
- Added columns: column_name: ADDED [type] [constraints]

## TASK
1. Generate [ROW_COUNT] pre-migration rows that conform to the Source Schema.
2. For each pre-migration row, generate the expected post-migration row by applying the Migration Rules.
3. Produce a set of SQL comparison queries that verify:
   - Row count preservation (unless rules explicitly add or remove rows).
   - Column rename correctness (old column value equals new column value).
   - Type cast correctness (value is valid in target type, no silent truncation).
   - Default value application (NULLs or missing values replaced correctly).
   - Dropped columns are absent from target.
   - Added columns exist with correct type and constraints.

## OUTPUT SCHEMA
Return a JSON object with this structure:
{
  "pre_migration_rows": [
    { "column_name": "value", ... }
  ],
  "expected_post_migration_rows": [
    { "column_name": "value", ... }
  ],
  "comparison_queries": [
    {
      "query_id": "string",
      "check_type": "row_count | column_rename | type_cast | default_value | dropped_column | added_column",
      "description": "string",
      "sql": "string",
      "expected_result": "string"
    }
  ],
  "coverage_summary": {
    "total_rules": <int>,
    "rules_covered": <int>,
    "uncovered_rules": ["string"]
  }
}

## CONSTRAINTS
- Every Migration Rule must be covered by at least one comparison query.
- Pre-migration rows must include edge cases that exercise each rule (e.g., NULL values for default application, boundary values for type casts).
- SQL queries must be executable in a standard SQL environment (PostgreSQL-compatible syntax).
- If [RISK_LEVEL] is "high", include a rollback verification query for each destructive rule.
- Do not include any data that could be real PII. Use synthetic values only.

## EXAMPLES
[EXAMPLES]

To adapt this template, replace each bracketed placeholder with your concrete inputs. For [SOURCE_DDL] and [TARGET_DDL], paste the full CREATE TABLE statements. For [MIGRATION_RULES], use the exact format shown so the model can parse transformations reliably. The [EXAMPLES] placeholder is optional but strongly recommended for complex migrations: provide one or two example rule-to-query pairs to anchor the model's output style. If you need a different output format, modify the OUTPUT SCHEMA block but keep the field-level descriptions so downstream validators can check completeness. After generating the output, run the comparison queries in a test environment before trusting the verification dataset in production migration rehearsals.

This prompt is designed to be called programmatically as part of a database migration verification step, not as a one-off chat interaction. The typical integration point is a CI/CD pipeline stage that runs after migration scripts have been applied to a staging or ephemeral environment. The harness should supply the DDL diff, the migration script, and a sample of pre-migration production records (sanitized if necessary) as inputs. The prompt returns structured JSON containing pre-migration records, expected post-migration records, and comparison SQL queries. The harness must then execute those comparison queries against the actual migrated database and compare results to the expected outputs.

Validation and Execution Flow: Parse the model's JSON output and validate that every expected post-migration record has a corresponding comparison query. Execute each comparison query against the migrated database. If a query returns zero rows (indicating the expected record was not found), log the specific record and column mismatch. For column rename and type cast checks, the harness should run the provided schema introspection queries (e.g., INFORMATION_SCHEMA.COLUMNS checks) and compare the actual column names and data types against the expected post-migration schema. Retry Logic: If the model output fails JSON schema validation, retry once with a stricter prompt that includes the validation error message. If the retry also fails, escalate to a human reviewer with the raw output attached. Logging: Log the prompt version, input hashes, model response, validation results, and comparison query outcomes. This trace is critical for debugging migration issues and for audit evidence in regulated environments.

Model Choice and Tool Use: Use a model with strong SQL generation and structured output capabilities (GPT-4o, Claude 3.5 Sonnet, or equivalent). Enable JSON mode or structured outputs if the provider supports it, binding the response to a schema that includes pre_migration_records, expected_post_migration_records, and comparison_queries arrays. This prompt does not require RAG or external tool calls during generation—the DDL and migration script are provided inline. However, the harness itself acts as the tool executor for the generated SQL. Human Review Gate: For migrations involving financial data, healthcare records, or irreversible schema changes, configure the harness to pause after generating the verification plan and require a human to approve the comparison queries before execution. This prevents a hallucinated DROP or UPDATE statement from running unchecked. After execution, flag any mismatches for manual triage before the migration is promoted to production.