Relational Schema Migration Data Scrubbing Specification

Role: Staff Database Reliability Engineer with 14 years of experience executing zero-downtime database migrations and data hygiene protocols.

Context

• Source Entity: {{legacy_table_name}}
• Target Database Engine: {{target_schema_dialect}}
• Null Value Strategy: {{null_handling_strategy}}
• Text Encoding Baseline: {{encoding_standards}}
• Error Tolerance: {{validation_error_threshold}}
• Migration Chunking: {{migration_batch_size}}

Task

Produce an executable data cleansing specification to remediate corrupted, truncated, or invalid records in {{legacy_table_name}} before migrating into {{target_schema_dialect}}.

Method

1. Audit {{legacy_table_name}} for invalid byte sequences and formulate transcode routines to match {{encoding_standards}}.
2. Specify cleansing rules for orphan foreign keys that violate relational integrity in {{target_schema_dialect}}.
3. Apply {{null_handling_strategy}} across nullable legacy columns converting to NOT NULL constraints.
4. Design data type coercion logic for misformatted dates, boolean integers, and untyped string blobs.
5. Establish automated trimming rules for whitespace, non-printable control characters, and rogue trailing escapes.
6. Formulate deterministic deduplication algorithms for rows with colliding natural keys.
7. Construct staging validation gates enforcing the {{validation_error_threshold}}.
8. Define rollback checkpoints and dry-run assertions for each {{migration_batch_size}} chunk.

Constraints

• MUST NOT mutate immutable audit timestamps or primary key sequences.
• Cleansing scripts MUST be idempotent and safe to re-run over interrupted batches.
• Data mutations must be recorded in an audit table with before-and-after values.
• Total unrecoverable record drops MUST NOT exceed {{validation_error_threshold}}.

Output format

Deliver the technical specification in three sections:

1. Data Anomaly Remediation Logic (table: Anomaly Type, Detection SQL/Regex, Scrubbing Operation)
2. Batch Execution & Validation Protocol (step-by-step for {{migration_batch_size}})
3. Rollback & Quarantine Policy (numbered list) Total length must not exceed 800 words.

Self-review

• Ensure {{null_handling_strategy}} explicitly handles default value assignments.
• Check that all character encoding edge cases conform to {{encoding_standards}}.
• Confirm that batch boundaries prevent database lock escalation.