Testing data migrations (2026)

TL;DR

Testing data migrations means proving that every eligible source record reaches the correct target form exactly once, every excluded or rejected record is explainable, relationships and business behavior remain valid, and the process can resume or roll back safely. Use mapping-level assertions, layered reconciliation, production-like rehearsals, and explicit cutover gates.

Key Takeaways

• Turn source-to-target mapping rules into executable invariants before the migration code runs.
• Profile real data shapes safely because nulls, duplicates, encodings, and legacy states defeat happy-path fixtures.
• Reconcile counts, keys, aggregates, relationships, transformations, rejects, and business behavior at several levels.
• Test reruns, checkpoints, interruption, rollback, and late-arriving writes instead of validating only one clean execution.
• Separate deployment compatibility, data backfill, read cutover, write cutover, and cleanup into observable gates.
• Measure locks, replication lag, queue depth, duration, and error budgets in a production-like volume rehearsal.
• Preserve privacy by using approved snapshots, masking rules, least privilege, controlled exports, and evidence retention.

Testing data migrations is the evidence-driven work of proving that information survives a change in schema, database, service, storage engine, encoding, or ownership without silent loss or corruption. Row counts alone are not enough. A migration can preserve the number of records while swapping keys, truncating values, duplicating money, breaking relationships, or losing writes made during cutover.

A publish-ready test strategy starts with source-to-target rules and business invariants, then covers profiling, transformation, execution, reconciliation, application compatibility, online cutover, recovery, performance, security, and monitoring. This guide provides that workflow plus a runnable in-memory migration test.

TL;DR

The central equation is not always source count equals target count. A better form is eligible source = migrated target + documented rejects + documented merges, adjusted for deliberate splits and late writes. Every term needs a query and an owner.

1. Classify the Migration and Its Failure Modes

The word migration covers very different changes. A schema migration may add a column or split one table. A platform migration may move data from one database engine to another. A service decomposition may transfer ownership from a monolith to a new service. An ETL backfill may transform historical records into an analytics model. A storage migration may move files and metadata. Each has different oracles and recovery boundaries.

Document these characteristics:

• Offline maintenance window or online zero-downtime process.
• Copy, move, merge, split, archive, re-key, or recompute.
• One-time script, repeatable job, streaming replication, or change data capture.
• Full history, active subset, rolling window, or tenant-by-tenant scope.
• Same schema, transformed schema, or transformed business meaning.
• Reversible, compensatable, or irreversible after cutover.
• Source remains authoritative, target becomes authoritative, or dual authority exists temporarily.

Then list failure modes in business terms. Missing a customer may prevent login. Duplicating a payment may change a balance. Mapping a deleted user to active may create unauthorized access. Reordering events may produce the wrong current state. Losing an attachment may invalidate a case record. This turns technical checks into prioritized risks.

Pay special attention to irreversible transformations, legal retention, money, identity, permissions, and shared identifiers. Use risk-based testing to select deeper checks where exhaustive row-level comparison is impractical. Do not lower the oracle for high-impact data simply because the table is large. Change the execution technique, such as partitioned hashes or targeted invariant queries.

2. Write the Contract for Testing Data Migrations

A source-to-target mapping document is the test basis. For every target field, identify source field or expression, type conversion, null handling, default, validation, lookup, deduplication, and reject behavior. Record ownership and version. A blank mapping cell is a decision still missing, not permission for the script to guess.

Example mapping:

Add cross-field and set-level invariants. An active subscription needs an active customer. Order total must equal lines plus tax minus discount according to the historical rule version. A deleted account must not gain credentials. Natural keys must remain unique within their scope. These rules catch errors that field-by-field comparison misses.

Define eligible, excluded, rejected, repaired, merged, and split records. A reject should have a stable source key and reason code, without copying sensitive values into a broad log. Decide whether rejects block cutover, have an accepted threshold, or require business sign-off. Avoid arbitrary percentage tolerances. Ten rejected test accounts and ten rejected payroll records do not carry equal risk.

Version the contract with the migration code. If a mapping changes during rehearsal, invalidate affected evidence and rerun it. Otherwise a green report may prove an obsolete rule.

3. Profile Source Data Before Designing Cases

Production-like data contains history that current application validation no longer permits. Profile source tables or approved masked snapshots for row counts, null rates, distinct counts, min and max, length, encoding, date range, enum values, duplicates, orphans, and distribution across tenant or partition. Use read-only access and queries reviewed for load.

Look for sentinel values such as zero dates, empty strings used as null, negative identifiers, placeholder emails, trailing spaces, mixed normalization, legacy enum codes, and records created by retired versions. Check whether application and database null semantics differ. A unique field may contain several nulls legally but the target model may not allow them.

Profile relationships as well as columns. Count children without parents, parents with extreme child counts, cycles in hierarchies, duplicated join rows, and references to soft-deleted entities. These are not automatically migration defects. They are source conditions that the contract must preserve, repair, quarantine, or reject deliberately.

Select test fixtures from discovered partitions:

1. Normal recent records.
2. Each null and enum category.
3. Exact min and max lengths and values.
4. Oldest and newest timestamps.
5. Unicode, combining characters, and supported scripts.
6. Duplicate candidates and natural-key collisions.
7. Deep or wide relationships.
8. Soft-deleted, archived, and restored states.
9. Records spanning previous schema versions.
10. Known source anomalies with approved expected handling.

Store synthetic reproductions of risky shapes when possible. If masked production snapshots are necessary, document masking determinism and referential preservation. Randomly changing every identifier can destroy the very duplicate and relationship patterns the rehearsal needs. API test data management offers compatible fixture and cleanup practices.

4. Validate Schema, Types, Defaults, and Encodings

Apply the target schema to a clean environment and to a realistic previous version. Verify columns, types, nullability, defaults, generated fields, indexes, keys, checks, partitions, views, triggers, permissions, and comments where operational tooling depends on them. Confirm the application can start and perform safe operations before the backfill.

Type conversions need explicit boundaries. Test integer width, decimal precision and scale, floating-point avoidance for money, date range, time zone, binary length, Unicode storage, JSON shape, and boolean encodings. A database may silently round or truncate depending on engine and settings. The migration should detect unacceptable conversion rather than let implicit casts decide.

Defaults can hide missing mappings. If a new non-null column defaults to active, every omitted legacy status may look valid. Reconcile how many rows received the default and prove they were eligible. Separate a legitimate source-derived default from an accidental insert default.

Encoding tests should compare Unicode code points and user-visible behavior according to requirements. Include accents, emoji if supported, right-to-left samples, combining forms, and characters near byte limits. Length in characters and length in bytes are different. Check database, driver, export, queue, and target service rather than only one SQL client.

For generated identifiers, verify uniqueness, sequence position, and collision behavior. If legacy IDs are preserved, check the target sequence starts above the migrated maximum before new writes. If IDs are remapped, validate a durable crosswalk and every foreign key consumer. Never infer completeness from the target's new sequential IDs.

Schema validation should run before data copy and again after finalization. Online migrations often add constraints or build indexes later. A structure that was intentionally permissive during backfill must reach the final enforced state before completion is declared.

5. Test Transformation Logic at Boundaries and in Combinations

Unit-test pure transformations separately from database movement. A mapping from legacy status codes to target enums should have a case for every known code, null, whitespace, casing if relevant, and unknown value. Money conversion needs positive, zero, negative if allowed, fractional boundary, maximum, and rounding examples. Timestamp conversion needs daylight-saving gaps and overlaps in the source zone.

Then test combinations that express business meaning. An archived record with no owner may be permitted, while an active record with no owner must reject. A migrated subscription can be canceled but still retain paid history. A deleted user can remain referenced by an audit event without receiving an active login identity.

Check lossy transformations deliberately. Splitting a full name, flattening nested addresses, changing decimal scale, or moving free text into an enum can discard information. The contract should identify the accepted loss, preservation field, or reject rule. QA should not certify loss merely because the target schema has nowhere to store the value.

Deduplication needs a deterministic winner and merge rule. Create two source records that collide on the target natural key but disagree on each field. Verify which value wins, how child records re-link, whether history remains auditable, and whether rerun chooses the same result. Count source-to-target cardinality as one-to-one, many-to-one, or one-to-many.

Test lookup failures. A country code, plan name, or external account may not exist in the target reference set. Verify default, quarantine, or failure behavior and ensure the job does not silently attach a convenient but wrong reference. Pin lookup dataset versions for reproducible rehearsal.

Property-based or generative tests can help with pure mappings, but retain named examples for business edges. Random data rarely generates meaningful legacy anomalies without constraints, and a passing transformation unit test cannot prove the extraction, load, and cutover paths.

6. Exercise Execution, Checkpoints, Reruns, and Rejects

Run the migration on empty, small, and production-like datasets. Capture migration version, source snapshot or high-water mark, parameters, start and end time, processed counts, checkpoint, rejects, retries, and code revision. A result without execution identity cannot be compared or reproduced.

Interrupt at controlled points: before any commit, after one chunk, after a data commit but before checkpoint update, after checkpoint but before status update, and near completion. Restart using the documented command. Verify the job neither skips uncheckpointed data nor duplicates committed effects. Checkpoints should represent durable progress, not an in-memory counter.

Test rerun semantics:

• Rerun the same completed migration with identical inputs.
• Rerun after a partial failure.
• Rerun one tenant or partition.
• Rerun after correcting a rejected record.
• Rerun with a different code or mapping version and confirm protection against accidental mixing.

Idempotency may mean no changes on the second run, deterministic upsert, or a blocked duplicate execution. State the intended behavior. A script that inserts duplicates on rerun is unsafe even if the planned production execution is 'only once,' because recovery routinely requires retries.

Reject handling needs tests for reason accuracy, source key, safe details, maximum error volume, storage retention, correction workflow, and reprocessing. Confirm that one poison record cannot block unrelated partitions unless atomicity requires it. Do not let a successful process exit code hide unresolved blocking rejects.

A dry run should use the same parsing and transformation logic and report projected effects without committing business changes. Compare dry-run counts with a real isolated execution. A completely separate estimation query can drift and give false confidence.

7. Reconciliation Techniques for Testing Data Migrations

Reconciliation is layered because no single comparison proves correctness. Start with counts by eligibility, tenant, date bucket, status, and partition. Global counts can cancel errors: ten missing active records plus ten extra deleted records still produce equality. Segment using business dimensions.

Compare key sets to find missing and unexpected records. For one-to-one movement, an anti-join in each direction is powerful. For remapped IDs, join through the crosswalk and test crosswalk uniqueness. For merges and splits, reconcile the declared cardinality and source lineage.

Compare aggregates for numeric fields: sum, min, max, null count, positive and negative counts, and grouped totals. Use exact decimal arithmetic for money. A matching total alone can hide individual swaps, so combine aggregates with row-level comparisons and business invariants.

Hashing can accelerate stable row comparison when canonicalization is explicit. Choose field order, null marker, encoding, date format, numeric representation, and delimiter escaping. Hashes from different databases can disagree because serialization differs. Validate the canonical form on named records before scaling it. Never use a hash as evidence without retaining a way to locate mismatched keys.

Sample intelligently when full row comparison is too expensive. Include risk-based strata, boundaries, every transformation category, recent and old data, high-value entities, rejects, and a reproducible random sample. Publish what was not compared. For critical ledgers or permissions, full invariant checks may still be required.

Reconcile side effects too. Count search documents, object files, events, audit entries, cache warmups, and notifications according to the contract. A row that exists in the target but is absent from the search index can still break user behavior. Pair technical queries with end-to-end journeys for representative migrated entities.

8. Prove Application and Integration Compatibility

A technically correct database can still break the product. Run old and new application versions against the schema combinations they will encounter during rollout. If deployment follows expand and contract, old code must tolerate added structures, new code must tolerate incomplete backfill, and writers must preserve fields needed by both versions until cutover.

Test critical reads and writes with pre-migration, migrated, and newly created records. Cover authentication, authorization, search, edit, delete, restore, reporting, export, background jobs, webhooks, and support tools. Compare behavior for the same business entity across old and new paths where a shadow read is available.

Validate integrations that store or receive identifiers. Data warehouses, search indexes, caches, object storage, analytics, fraud systems, billing providers, and partner callbacks may depend on old keys or event schemas. Verify crosswalk use and event compatibility. A database foreign key cannot protect an external consumer.

Reports deserve separate reconciliation. Filters, time zones, historical status definitions, joins, and slowly changing dimensions can produce plausible but wrong totals. Select known entities whose expected history is manually explainable, then compare aggregate reports across important periods.

Permission behavior is high risk. Test migrated memberships, inherited roles, disabled accounts, tenant boundaries, sharing links, and service scopes. A missing relation may deny legitimate users, while an over-broad default may expose data. Check through the application boundary and through controlled queries.

Use feature flags and routing controls exactly as production will. A direct target database check does not prove the application reads it. Capture which path served each test, especially during percentage or tenant-based rollout.

9. Test Online Migration, Dual Writes, and Cutover

Online migration adds a moving source. Define a high-water mark, change capture mechanism, lag metric, conflict policy, and authority at each phase. A common sequence is expand schema, deploy compatible code, backfill history, catch up changes, shadow-read, switch reads, switch writes, observe, then remove old structures. The exact sequence varies, but every authority transition needs a gate.

Create writes before backfill reaches a key, while it processes that key, after it passes, during read cutover, and during write cutover. Include create, update, delete, restore, and related-record operations. Verify no late source change is lost and no stale backfill overwrites a newer target value.

Dual writes can fail on one side. Introduce target timeout, source timeout, duplicate delivery, out-of-order delivery, and process restart in a controlled environment. Confirm the declared consistency and repair mechanism. If the application reports success after only one side commits, measure how reconciliation detects and repairs the gap.

Shadow reads compare results without serving the new response. Verify comparison logic does not expose sensitive values in logs, overload the target, or treat expected formatting differences as business mismatches. Track mismatch categories and sample records for diagnosis. A low aggregate mismatch rate is insufficient if all mismatches affect one premium tenant or permission rule.

At cutover, record the final source position, target catch-up position, lag, reconciliation outcome, active code versions, flags, and authorized decision. Freeze or queue writes only if the plan requires it, then verify the actual user experience during the window.

After cutover, keep rollback compatibility for the agreed period. Do not drop source columns, disable replication, or delete the crosswalk until exit criteria and rollback expiry are satisfied. Cleanup is a separate deployment with its own test plan.

10. Rehearse Performance, Locks, Rollback, and Disaster Recovery

Use production-like volume and distribution, not only row count. One million narrow rows differ from one million rows with large JSON, deep relationships, hot tenants, and many indexes. Measure extraction rate, transform rate, load rate, total duration, transaction log growth, storage, CPU, memory, lock wait, replication lag, queue depth, and application latency.

Run alongside representative user traffic. A migration can finish within its maintenance estimate yet cause unacceptable lock contention or replica lag. Test chunk size, pause, retry, and throttling controls. Verify health checks and operational queries remain available. Avoid dangerous full-table comparison queries during peak rehearsal without database review.

Rollback means more than reversing DDL. Decide what happens to new writes made after cutover, remapped IDs, external events, files, and consumers that already observed target state. Some migrations are not safely reversible and need forward repair or compensating actions. The plan must say so before execution.

Rehearse rollback at multiple phases: before backfill, during partial backfill, after catch-up, immediately after read cutover, and after new writes begin. Verify application compatibility, data authority, job cancellation, and monitoring each time. Record the last point where rollback remains valid.

Backup restoration is not migration rollback unless it meets recovery point and time objectives and accounts for writes after the backup. Test restore integrity, credentials, encryption keys, sequence values, crosswalks, and application startup. Measure the actual time in a representative environment.

Define abort thresholds, such as unexplained reconciliation gaps, lock duration, lag, error rate, or missing critical paths. Thresholds should map to business risk and operational capacity, not be invented during the production window.

11. Automate a Small End-to-End Migration Oracle

The following Python 3 test uses the standard sqlite3 and unittest modules. It creates legacy data, migrates eligible customers with explicit status and money rules, records an unknown status as a reject, and reconciles keys, totals, and rerun behavior. Save it as test_migration.py, then run python -m unittest -v test_migration.py.

import sqlite3
import unittest
from decimal import Decimal, ROUND_HALF_UP

SCHEMA = '''
CREATE TABLE legacy_customer (
  legacy_id INTEGER PRIMARY KEY,
  given_name TEXT,
  family_name TEXT,
  status_code TEXT NOT NULL,
  balance_dollars TEXT NOT NULL
);
CREATE TABLE customer (
  customer_id TEXT PRIMARY KEY,
  full_name TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('active', 'inactive', 'deleted')),
  balance_cents INTEGER NOT NULL
);
CREATE TABLE migration_reject (
  legacy_id INTEGER PRIMARY KEY,
  reason_code TEXT NOT NULL
);
'''

STATUS = {'A': 'active', 'I': 'inactive', 'D': 'deleted'}

def to_cents(text):
  return int((Decimal(text) * 100).quantize(Decimal('1'), rounding=ROUND_HALF_UP))

def migrate(connection):
  rows = connection.execute(
    'SELECT legacy_id, given_name, family_name, status_code, balance_dollars FROM legacy_customer'
  ).fetchall()
  for legacy_id, given, family, code, dollars in rows:
    if code not in STATUS:
      connection.execute(
        'INSERT OR REPLACE INTO migration_reject VALUES (?, ?)',
        (legacy_id, 'UNKNOWN_STATUS'),
      )
      continue
    full_name = ' '.join(part.strip() for part in (given or '', family or '') if part and part.strip())
    connection.execute(
      'INSERT OR REPLACE INTO customer VALUES (?, ?, ?, ?)',
      (f'L-{legacy_id}', full_name, STATUS[code], to_cents(dollars)),
    )
  connection.commit()

class MigrationTest(unittest.TestCase):
  def setUp(self):
    self.db = sqlite3.connect(':memory:')
    self.db.executescript(SCHEMA)
    self.db.executemany(
      'INSERT INTO legacy_customer VALUES (?, ?, ?, ?, ?)',
      [
        (1, ' Ana ', 'Lopez', 'A', '10.25'),
        (2, 'Bo', None, 'I', '-1.005'),
        (3, 'Cy', 'Ng', 'X', '7.00'),
      ],
    )

  def tearDown(self):
    self.db.close()

  def test_mapping_reconciliation_and_rerun(self):
    migrate(self.db)
    migrate(self.db)
    self.assertEqual(
      self.db.execute('SELECT customer_id, full_name, status FROM customer ORDER BY customer_id').fetchall(),
      [('L-1', 'Ana Lopez', 'active'), ('L-2', 'Bo', 'inactive')],
    )
    self.assertEqual(self.db.execute('SELECT SUM(balance_cents) FROM customer').fetchone()[0], 924)
    self.assertEqual(self.db.execute('SELECT legacy_id, reason_code FROM migration_reject').fetchall(), [(3, 'UNKNOWN_STATUS')])
    eligible = self.db.execute(
      "SELECT COUNT(*) FROM legacy_customer WHERE status_code IN ('A', 'I', 'D')"
    ).fetchone()[0]
    self.assertEqual(eligible, self.db.execute('SELECT COUNT(*) FROM customer').fetchone()[0])

if __name__ == '__main__':
  unittest.main()

This test makes mapping and rounding visible, but a real migration suite should not encode business policy separately from an authoritative mapping without review. Add database-specific integration tests for actual types, constraints, transaction behavior, bulk loader, and query plans.

Interview Questions and Answers

Q: How do you start testing a data migration?

I classify the migration and authority phases, then review source-to-target mappings, business invariants, eligibility, rejects, and rollback. I profile source data safely before selecting representative and boundary fixtures.

Q: Why are matching row counts insufficient?

Counts can match while records are duplicated, swapped, truncated, misclassified, or detached from relationships. I combine segmented counts with key-set, field, aggregate, relationship, reject, and business-flow reconciliation.

Q: How do you test migration restartability?

I interrupt before and after durable commits and checkpoints, restart with the documented command, and verify no eligible record is skipped or applied twice. I also rerun completed partitions and corrected rejects.

Q: How do you test an online migration?

I create writes around the backfill cursor and each cutover phase, monitor capture lag, and compare source and target outcomes. I test dual-write partial failures, shadow-read mismatches, authority switches, and late-arriving updates.

Q: What belongs in a reconciliation report?

I include execution identity, source position, mapping version, segmented counts, missing and extra keys, aggregate and invariant results, rejects by reason, sampled row comparisons, downstream effects, and unresolved risk.

Q: How do you test rollback?

I rehearse it at several migration phases and account for writes made after cutover, external events, new identifiers, and application compatibility. If reverse migration is unsafe, I test the approved forward repair or compensation plan instead.

Q: What source data edges do you profile?

I inspect nulls, duplicates, lengths, ranges, encodings, time zones, enum values, old schema versions, orphans, soft deletes, and tenant distribution. I turn discovered shapes into named fixtures and reconciliation groups.

Q: How do you protect sensitive data during rehearsal?

I use approved masked or synthetic datasets, least-privilege read access, encrypted transfer and storage, controlled temporary files, safe reject logs, and documented deletion. Masking must preserve the relationships and distributions needed by the test.

Common Mistakes

• Declaring success because global source and target counts match.
• Writing cases from the target schema without an approved mapping contract.
• Testing only current clean records and missing legacy nulls, enums, encodings, and orphans.
• Allowing defaults and implicit casts to hide missing transformation rules.
• Running one successful migration but never interrupting, restarting, or rerunning it.
• Comparing hashes without defining identical canonical serialization.
• Ignoring late writes, replica lag, events, files, search indexes, and external consumers.
• Calling backup restore a rollback without accounting for new writes and recovery objectives.
• Dropping old structures immediately after cutover, before rollback compatibility expires.
• Copying production personal data into unmanaged test files or verbose reject logs.

Conclusion

Testing data migrations is complete only when structure, content, relationships, business behavior, execution recovery, and operational cutover all have evidence. Start from explicit mappings and invariants, profile the source, reconcile at several levels, and rehearse the same restart and authority changes expected in production.

Choose one critical entity and write its eligibility equation, key mapping, field rules, relationship checks, and business journey. If the team cannot produce those five artifacts, the migration is not yet ready for a trustworthy rehearsal.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Testing multipart form data uploads (2026)
• Accessibility testing checklist (2026)
• Accessibility testing with Cypress (2026)
• Accessibility testing with Playwright (2026)
• Agentic testing with tool calling (2026)
• Agile testing quadrants (2026)