Data migration is the part of a software project where “it mostly worked” is not good enough. A missing field in a dashboard is annoying. A missing field in a customer’s transaction history is a support crisis, a trust problem, and in some cases a compliance issue. Yet database migration routinely gets treated as a checkbox at the end of an application migration project, instead of the highest-risk workstream it actually is.

A database migration that avoids data loss requires four things done in order: a field-by-field schema mapping before any code is written, extract-and-load validation against row counts and checksums, a reconciliation pass comparing source and target before cutover, and a rollback plan for the data layer specifically — separate from the application rollback. Any ETL (extract, transform, load) process missing one of these four is running on hope.

This checklist is what we use on database migration and ETL engagements at Cation System, whether the migration is standalone or part of a larger cloud migration or legacy system modernization project.

Step 1: Map the Schema — Field by Field, Not Table by Table

The most common source of silent data loss is not a failed transfer — it is a field that existed in the old system, got missed in the schema mapping, and simply does not appear in the new one. Nobody notices until a customer asks where a specific piece of their history went.

• Document every table, every field, and its data type in the source system
• For each field, decide explicitly: migrate as-is, transform, merge into another field, or intentionally drop (and get sign-off on every “drop” decision — do not let it happen by omission)
• Pay special attention to calculated or derived fields — these often encode business logic that is not obvious from the schema alone. A “status” field that gets set by six different application code paths needs its full logic understood before you can safely migrate what feeds it.
• Identify fields with inconsistent historical data (a currency field that used to allow free text, a status enum that had values added and removed over the years) — these need explicit transformation rules, not a straight copy.

This step is where the “audit before you touch anything” principle matters most. Skipping it does not save time — it moves the discovery of missing logic from before the migration (cheap to fix) to after go-live (expensive, and often noticed by a customer first).

Step 2: Choose an Extraction and Load Method Deliberately

• Bulk export/import — simplest, works well for systems that can tolerate a defined downtime window during the load
• Continuous replication — keeps source and target in sync during a longer migration window, useful when downtime tolerance is low, but adds complexity in handling schema differences during the sync period
• Dual-write during transition — the application writes to both old and new systems for a defined period, allowing extended validation before fully cutting over; highest complexity, but lowest risk for high-stakes data

The right choice depends on data volume, downtime tolerance, and how different the source and target schemas are. We do not default to one method — this gets decided based on your specific constraints during the audit.

Step 3: Build Validation Into the Transform, Not After It

Validation that happens after the full load is validation that happens too late — by then you are debugging a live discrepancy instead of catching it in a staging environment.

• Row counts per table, source vs. target, before considering any batch complete
• Checksums or hash comparisons on critical fields (especially financial or transactional data) to catch silent corruption that row counts alone would miss
• Referential integrity checks — foreign keys that resolve correctly in the target, not just the source
• Business rule validation — does the transformed data still satisfy the rules the application enforces? (e.g., an order total that should always equal the sum of its line items)
• Null and default value audits — fields that were “usually” populated in the source but not enforced as required often reveal gaps only when validated explicitly

Step 4: Reconcile Before You Cut Over

Reconciliation is a dedicated comparison pass between source and target, run close to the actual cutover date (not weeks earlier, when the source data will have moved on).

• Run the full validation suite from Step 3 against a fresh extract, as close to cutover time as your migration method allows
• Spot-check a sample of records manually — automated checks catch structural problems, but a human review catches things like “this looks technically correct but semantically wrong”
• Involve someone who understands the business meaning of the data, not just the schema — an engineer who has never used the system will not notice that a field’s values look subtly off

Step 5: Plan the Data Rollback Separately from the Application Rollback

If the migrated application needs to roll back after cutover, what happens to the data written to the new system in the meantime? This question needs an answer before cutover, not during an incident.

• Define how long the old system’s data remains the source of truth in parallel-run
• Decide how transactions written after cutover would be reconciled back if a rollback becomes necessary
• Keep the old database intact and untouched for an agreed buffer period (commonly 2-4 weeks) after cutover — do not decommission it the moment the new system goes live

Common Mistakes That Cause Data Loss

• Treating data migration as a script, not a project. A one-off ETL script with no validation layer is the single most common cause of silent data loss we see in migrations we are brought in to fix.
• Migrating on a schema that was never fully mapped. Undocumented fields and legacy business logic get dropped by omission, not by decision.
• No reconciliation step. Row counts matching at extract time does not mean the data is correct at load time — transformation errors happen in between.
• No rollback plan for data specifically. Application rollback plans are common; data rollback plans are often missing entirely, which turns a recoverable incident into a permanent one.

What This Costs and Takes

A standalone database migration for a moderate-sized system, done properly with validation and reconciliation, typically runs ₹6-12 lakh over 6-8 weeks. Larger datasets, multiple source systems being consolidated, or strict audit-trail/compliance requirements push this toward ₹15-25 lakh+. When bundled into a larger cloud migration or platform migration, the data workstream still deserves its own budget line and timeline — do not let it get absorbed silently into the application estimate.

The Bottom Line

Data migration failures are rarely dramatic. They are quiet — a missing field, a subtly wrong calculated value, a handful of orphaned records nobody notices for months. The checklist above exists to catch those failures before they reach a customer, not after. Schema map first, validate during the transform, reconcile before cutover, and plan the data rollback separately from the application rollback.

If you are planning a database migration and want it scoped by people who treat data integrity as the primary risk (not a line item), talk to our team. See our full approach on the database migration & ETL services page.