Data Type Casting Risk Analysis Prompt

This prompt is designed for data engineers and backend developers who are preparing or reviewing a database migration script that changes a column's data type. The core job-to-be-done is to prevent data corruption, silent truncation, or application errors that result from implicit or explicit type casting. The ideal user has the proposed ALTER TABLE ... ALTER COLUMN statement, the current schema definition, and a clear understanding of the data's business meaning. Use this prompt when the cost of a casting failure is high—for example, in financial, healthcare, or analytics pipelines where precision loss or overflow would corrupt downstream reporting or trigger cascading application failures.

You should not use this prompt for simple, lossless type widening (e.g., SMALLINT to INTEGER) where the risk is near zero, or for reviewing an entire migration script's transaction safety. For those cases, use a targeted prompt like the Transaction Boundary Safety Review or a standard linter. This prompt is also not a substitute for running the migration against a production-like copy. It is a pre-flight risk analysis that generates a structured report and boundary-value test cases, which you must then execute in a dry-run environment. The prompt requires you to provide the exact DDL, the current column's data profile (min, max, avg length, null count), and any known application constraints as the [CONTEXT].

After using this prompt, your next step is to take the generated risk-ranked list and the boundary-value test cases and run them against a staging or snapshot database. The prompt's output is a plan for validation, not the validation itself. If the analysis identifies a 'Critical' risk of silent truncation or overflow, you must add a data backfill or multi-step migration strategy before proceeding. Avoid the temptation to accept a 'Low' risk rating without verifying it against a full data profile, as the model's assessment is only as good as the statistics you provide.

The prompt below is designed to be copied directly into your AI harness, IDE, or evaluation pipeline. It uses square-bracket placeholders for all dynamic inputs so you can substitute actual schema definitions, migration SQL, and risk thresholds without rewriting the core instruction. Every placeholder is documented so you know what to provide and why it matters for the analysis.

textCopy
You are a database migration safety analyst. Your task is to review a proposed column type change and produce a risk-ranked list of potential failures.

## INPUTS
[MIGRATION_SQL]
The ALTER COLUMN or type-casting DDL statement under review.

[SOURCE_SCHEMA]
The current column definition, including data type, precision, scale, collation, and nullability.

[TARGET_SCHEMA]
The proposed column definition after migration.

[TABLE_STATISTICS]
Row count, distinct value count, min/max values, null count, and sample values for the column. Provide as a structured summary.

[APPLICATION_QUERIES]
Relevant application code, ORM model definitions, or query patterns that reference this column. Include any implicit type assumptions.

## CONSTRAINTS
[CONSTRAINTS]
- Maximum risk level to flag: [RISK_THRESHOLD]
- Deployment pattern: [DEPLOYMENT_PATTERN] (e.g., blue-green, rolling, downtime-window)
- Rollback strategy: [ROLLBACK_STRATEGY]
- Special handling required for: [REGULATED_DATA_CLASSIFICATIONS]

## OUTPUT SCHEMA
[OUTPUT_SCHEMA]
Return a JSON object with this structure:
{
  "summary": "One-sentence risk verdict",
  "risk_level": "LOW|MEDIUM|HIGH|CRITICAL",
  "findings": [
    {
      "id": "F-001",
      "category": "TRUNCATION|PRECISION_LOSS|OVERFLOW|IMPLICIT_CONVERSION|NULL_SURPRISE|COLLATION_CHANGE|APPLICATION_BREAK",
      "severity": "LOW|MEDIUM|HIGH|CRITICAL",
      "description": "What could fail and why",
      "affected_rows_estimate": "count or percentage",
      "example_value": "a value that would break",
      "test_query": "SQL to validate this risk in the actual table"
    }
  ],
  "rollback_safety": {
    "is_rollback_safe": true|false,
    "irreversible_operations": ["list any operations that cannot be undone"],
    "rollback_steps": ["ordered steps to reverse the migration if needed"]
  },
  "recommended_pre_flight_checks": ["checks to run before migration"],
  "boundary_value_tests": [
    {
      "test_case": "description",
      "input_value": "value to test",
      "expected_behavior": "what should happen",
      "failure_mode": "what breaks if the cast is wrong"
    }
  ]
}

## INSTRUCTIONS
1. Compare [SOURCE_SCHEMA] to [TARGET_SCHEMA] and identify every semantic difference in the type change.
2. For each difference, determine whether existing data in [TABLE_STATISTICS] could violate the new type constraints.
3. Cross-reference [APPLICATION_QUERIES] to identify code paths that make implicit assumptions about the current type.
4. Rank findings by severity: CRITICAL (guaranteed data loss or outage), HIGH (likely breakage for some rows), MEDIUM (edge-case risk), LOW (cosmetic or unlikely).
5. Generate boundary value test cases that exercise the edges of the new type.
6. Assess rollback safety given [ROLLBACK_STRATEGY] and [DEPLOYMENT_PATTERN].
7. If [REGULATED_DATA_CLASSIFICATIONS] are present, flag any findings that could cause compliance violations.
8. Do not fabricate table statistics or query patterns. If input data is insufficient to assess a risk, note it as "INSUFFICIENT_DATA" with the specific information needed.

To adapt this prompt, replace each placeholder with actual data from your migration tooling. The [TABLE_STATISTICS] placeholder works best when populated from ANALYZE TABLE output or a data profiling query. The [APPLICATION_QUERIES] placeholder should include ORM model field definitions and any raw SQL found via codebase search for the column name. If you cannot provide query patterns, remove that section and add a constraint instructing the model to flag this as a blind spot. For production use, always run the generated test_query values against a staging copy of the database and feed results back into a second-pass review before approving the migration.

This prompt is designed to sit inside a pre-deployment migration review workflow, not as a standalone chat. The harness should feed the prompt a structured migration specification (the DDL or ORM migration diff), the current schema metadata (column types, row counts, sample values), and any known application query patterns that reference the affected columns. The prompt returns a risk-ranked list of casting concerns, each with a severity level, a description of the failure mode, and a suggested test case. The harness must validate this output before it reaches a human reviewer or blocks a deployment pipeline.

The implementation should follow a validate → retry → escalate pattern. After receiving the model response, parse the JSON output and validate that every risk entry contains the required fields: risk_id, severity, column_name, current_type, target_type, failure_mode, affected_rows_estimate, and test_case. If validation fails, retry once with an error message appended to the prompt context. If the retry also fails, log the raw response and escalate to a human reviewer with a clear flag that automated analysis was incomplete. For high-risk migrations (tables over 10M rows, financial data, or PII columns), always require human approval regardless of model confidence. The harness should also execute the generated boundary-value test cases against a staging or snapshot environment and attach the results to the review report before it reaches a human.

Model choice matters here. Use a model with strong SQL and type-system reasoning (GPT-4o, Claude 3.5 Sonnet, or equivalent). Avoid small or general-purpose models that may hallucinate type coercion rules. Set temperature=0 for deterministic output. If your migration tooling already produces a structured diff (e.g., Alembic, Flyway, or Liquibase output), parse that into the [MIGRATION_SPEC] placeholder rather than pasting raw SQL. For teams with large schemas, consider a RAG step that retrieves only the relevant table definitions and column statistics before assembling the prompt, keeping context within token limits. Log every run—input spec, model response, validation result, and human decision—for auditability and prompt improvement over time.