Data Migration Batch Workflow
Migrate large datasets in batches to minimize lock contention and allow rollback at each stage.
Objective
Migrate data in controlled batches to minimize risk and enable partial rollback.
Environment
- A source and target database
- A migration script with batch processing
- A rollback plan for each batch
Steps
1. Determine batch size
Calculate an appropriate batch size based on row size and lock timeout.
Expected state: A batch size is chosen that fits within lock limits.
2. Migrate first batch
Process the first batch of records and verify the results in the target.
Expected state: The first batch is migrated and verified.
3. Validate data
Compare source and target data for the migrated batch to ensure correctness.
Expected state: The migrated batch matches the source data exactly.
4. Continue batches
Process subsequent batches, validating each before proceeding to the next.
Expected state: All batches are migrated and validated in sequence.
5. Final verification
Run a full count and sample comparison between source and target.
Expected state: The complete migration is verified as correct.
Decision points
What batch size to use?
- Small (1000 rows) for safety
- Large (10000 rows) for speed
Start with small batches to validate the process; increase size once the migration is proven correct.
Expected outcome
All data is migrated in verifiable batches with the ability to roll back any individual batch.
Verification
Compare row counts and sample data between source and target; confirm all batches are migrated correctly.
Cleanup
Roll back the last batch if validation fails; fix the issue and reprocess that batch.
Batches make migration safe
Migrating all data at once risks long locks and untestable failures. Batches contain the blast radius.
Implementation Notes
This workflow provides a structured approach to ensure consistent results across team members and environments.
Common Pitfalls
Skipping verification steps or rushing through the process can lead to incomplete results. Always follow each step in order and verify before proceeding.
Quick Reference
# Execute the workflow steps as documented above
echo "Workflow completed successfully"