Source: TesterHome Community

 

---

 

Introduction to Data Migration Testing

Data migration is the process of moving data from a legacy database to a new one while ensuring business continuity between the old and new systems.

Before testing, you must understand how data will be restructured and linked between the two systems through migration strategies and plans.

The testing process must ensure correctness on two levels:

• Technical level: Ensure zero data loss, correct database/table processing, and accurate field mapping.
• Business level: Ensure correct data transformation, preserved data volume, forward/backward compatibility, normal functioning of old and new features, and accurate reports.

 

Key Challenges, Focus Areas, and Risks

You may wonder: What exactly should you focus on during data migration testing? What risks might you encounter?

1. Technical Details of the Migration

Data migration typically falls into two categories:

• Migration between identical database types
• Migration between different database types

The latter often involves table structure changes, encoding differences, and syntax variations – all of which increase migration difficulty.

Table structure considerations:

• Some migrations directly copy tables
• Others split or merge table structures – the latter demands rigorous validation of field attributes, types, lengths, and mappings

Data volume impact:

The scale of migrated data affects outcomes. Large data volumes increase the difficulty of maintaining accuracy across database tables.

2. Business State at the Migration Point

Typically, application services are halted before migration, and necessary data checks are performed to prevent new transactions or OS-level disruptions during the migration window.

If the migration involves schema changes (additions, deletions, or modifications), testers must verify:

• The correctness of each table change
• The downstream impact on data and reports

3. Business Rule Changes Pre- and Post-Migration

Beyond validating the new system’s rule changes, you must ensure that migrated legacy data properly adapts to them.

Example scenario:

A field present only in the new system (but absent in the old system) must receive a correct default value.

The ultimate goal: Ensure business availability and continuity of the migrated legacy data, so there is no need for re-rollback testing after migration.

 

Migration Test Strategy and Plan

Step 1 – Fully Understand the Migration Plan

You must understand:

• The go-live plan for both old and new systems
• How data will be migrated
• How existing compatible data will be handled

Key questions to answer:

Question	Why It Matters
Will batch processes or end-of-day services be halted during switchover?	Prevents conflicts during migration
What happens to legacy system equipment after cutover (retired or retained)?	Impacts regression testing scope
How will data reconciliation and ledger migration be carried out?	Ensures financial data integrity
What are the synchronization timing and scope (full vs. incremental)?	Determines validation approach

 

Real-world example – Commercial Drafts System:

• Migration shell scripts created temporary tables in the legacy database mirroring new business table fields
• Data was filtered and processed within the legacy database
• Data was exported to flat files and imported via Oracle’s sqlldr into Informix
• Existing data: one-time full migration
• New data: real-time incremental migration
• Migration point: after daily start
• The legacy system remained active post-migration
• Testing included validation on the new system AND regression testing on the old system

Step 2 – Define Migration Scope and Rules

After the migration plan is clear, define the scope to focus your test cases.

Determine whether migration involves:

• Master data
• Historical business data
• In-flight transaction data

Key focus areas:

A. Data types and states – different data types require different testing focuses:

Data Type	Testing Focus
Master data	Data consistency between old and new systems
Pure historical data	Data consistency + query functionality on new system
In-flight data	Status at each step + ability to continue processing

 

B. Migration rules – document and validate:

• Database changes
• Field type changes
• Field length changes
• Field mapping relationships

Design test cases based on these rules to ensure correct mapping post-migration.

C. Downstream files – verify:

• Number of related systems involved
• Corresponding data unload files
• Data volume correctness after migration
• Field correctness after migration

Special attention: Encoding methods may change. Coordinate with upstream/downstream systems early to define solutions and prevent garbled characters.

Step 3 – Plan for Backup and Rollback

Backup and rollback strategies must cover:

• Data backup and rollback
• Application backup and rollback

Be prepared to roll back promptly if migration fails or encounters critical issues.

Special circumstances example (Commercial Drafts System):

If missing or incorrect records are discovered in production, define manual maintenance procedures using newly added maintenance functions.

Step 4 – Build the Migration Test Schedule

Data migration verification relies on both technical and business validation methods.

Scheduling approach:

• Plan migration validation windows according to SIT phase schedules
• Typical approach: Run two migration test cycles across three SIT phases
• If a UAT regression phase exists, add a third validation cycle there

Important considerations:

• Migration requires environment resets
• If external joint debugging partners are involved, adjust migration test timing based on their availability

 

Technical and Business Validation – Lessons Learned

Technical Validation Items

	Validation Item	Description
1	Zero data loss	Use pre- and post-migration SQL checks to verify no duplication or omission
2	Database testing	Verify table/field processing rules, field mapping, and migration rules
3	Migration log inspection	Check step-by-step processing logic and data record correctness
4	Initial values	Validate configuration and handling of serial numbers, business IDs, account IDs, etc.
5	Exception handling	Test abnormal data, abnormal transactions, and exception processing scenarios

 

Business Validation Items

	Validation Item	Description
1	Register and report accuracy	Verify report functionality and data accuracy
2	Functional availability	Ensure all features work correctly
3	Business continuity	Validate end-to-end business processes

 

Test Preparation and Execution

Test Data Preparation

Data pre-loading is a critical pre-test step. It determines whether you have sufficient and complete data for migration testing.

Process:

1. Confirm the migration plan and schedule
2. Prepare migration test data
3. For complex scenarios, build detailed scenario tables
4. Pre-load data according to scenario tables
5. Verify data completeness after loading – data cannot be supplemented after system switchover

Timing consideration: Prioritize validating this data in early SIT phases. Some data (e.g., matured or settled bills) is time-sensitive and may become unusable for later business continuity testing.

Pre-Migration Data Backup

• Export business report data before migration for later validation
• Developers should back up the pre-migration database to enable rollback if migration issues occur

Execution Example – Commercial Drafts System

Phase 1: Data volume validation

• Query, count, and compare business and common data tables between old and new systems (both frontend and backend)
• Validate accuracy of common organizational structure data and user data volume

Phase 2: Data accuracy validation

Sub-item	Validation Action
Zero data loss	Compare pre- and post-migration counts based on migration rules
Database points	Compare table structures, field processing rules, field mapping, and migration rules
Permissions	Validate organizational structure, user data volume, and job role permissions in common tables

Phase 3: Register and report validation

Verify for all organizations (head office, branches, etc.):

• Correctness of all fields in buying/selling registers
• Correctness of all fields in end-of-day balance registers
• Accuracy of total counts

Phase 4: Functional availability and business continuity validation

Functional availability – verify:

• Old and new data operations are unimpeded in the new system flow
• Example: Old system data can be selected in the new system’s ticket selection interface
• Example: Fractionalized ticket business processes remain unaffected

Query capability – verify:

• Both old and new data can be queried in the new system’s registers
• Old data remains queryable in the old registers

Business continuity – verify:

• For ticket migration (e.g., discounted traditional bills), subsequent business operations can proceed normally
• For master data, business operations under affected users/organizations function correctly

Phase 5: Special circumstances and exception validation

• In-flight transaction data – focus on status at each step
• Verify whether pending transactions can continue processing