Data Migration — essence, methods and data transfer process

What is Data Migration

Data Migration is the process of transferring data from one source to another while ensuring its integrity, consistency and availability after completion. Unlike simple copying, migration includes format transformation, deduplication, referential integrity checks and result validation.

The need for Data Migration arises when upgrading a DBMS (e.g., from MySQL 5.7 to MySQL 8.0), changing cloud providers, migrating from a monolith to microservices, or switching to a NoSQL schema. According to Stripe (2024), 89% of companies encounter data migration at least once every two years, and 43% consider it the most challenging technical upgrade phase.

Main Goals of Data Migration

The first goal is improving performance by moving to a more modern storage solution. The second is reducing operational costs when changing infrastructure providers. The third is ensuring compliance with regulatory requirements (GDPR, 152-FZ), when data must be stored in a specific jurisdiction.

How Migration Differs from Integration

Integration involves ongoing synchronization between two running systems. Migration is a one-time transfer followed by decommissioning the source. Integration does not delete data at the source; migration ends with transitioning the target system to primary status. This fundamental difference determines the choice of tools and validation approaches.

ETL Data Migration Model

The basic architecture of any Data Migration is built on the ETL model (Extract, Transform, Load). Extract — retrieving data from the source. Transform — converting to the target schema. Load — loading into the destination. Each phase has its own quality control methods.

Extract: data extraction

At the extraction stage, data is read from the source database, file storage or API. Incremental extraction (CDC — Change Data Capture) allows transferring only changed records, reducing traffic volume. Full dump is suitable for small volumes, but for terabyte-sized databases, streaming replication via Debezium or Kafka Connect is preferable.

Transform: schema transformation

Transformation includes renaming columns, changing data types, normalizing values and aggregation. For example, when migrating from MySQL to PostgreSQL, the numeric type DECIMAL must be converted to NUMERIC, and the date format to ISO 8601. According to Talend (2024), 70% of migration time is spent on transformation, not on transfer.

Load: loading into the destination

Loading is performed in batches (batch insert) or streamingly. An idempotency key is used to eliminate duplicates. After loading, a validation step is mandatory: comparing record counts, calculating checksums and checking business rules. Without validation, Data Migration is considered incomplete.

Data Migration Strategies

The choice of Data Migration strategy determines system downtime, rollback complexity and the amount of preparatory work. The main strategies are Big Bang, Trickle and Parallel Run. Each is applicable in different scenarios.

Big Bang Migration

Big Bang is a one-time shutdown of the old system, data transfer and startup of the new one. Suitable for small volumes and simple schemas. Risk: in case of failure, the system is unavailable until full recovery from backup. In 2024, GitLab used Big Bang to migrate 5 TB of data from AWS RDS to GCP Cloud SQL with a 14-hour downtime window.

Trickle Migration

Trickle is a streaming synchronization in small portions. The old and new systems run in parallel, changes are replicated in real time. After data stabilization, the old source is shut down. This approach requires bidirectional synchronization and conflict resolution. It is used in Continuous Delivery when updating a database schema without downtime.

Parallel Run

Parallel Run — both systems operate simultaneously, the application writes and reads from both sources. After data verification on the destination, the old source is shut down. This is the safest strategy, but also the most expensive — it requires maintaining two infrastructures. It is used when migrating critical financial systems.

Types of Data Migration

In application development, several types of Data Migration are distinguished based on the transfer object and context. Each type has its own methodology and tools. Understanding the type is the first step toward choosing the correct strategy.

Database Migration

Database Migration is the transfer between DBMS from different vendors: from Oracle to PostgreSQL, from SQL Server to MySQL, from MongoDB to DynamoDB. The complexity lies in incompatible data types, SQL dialects and indexing mechanisms. Tools: AWS DMS, Debezium, Liquibase.

Application Data Migration

Transferring data between different versions of the same application — for example, when updating code with changes to entity structure. Often accompanied by running migration scripts in the application language: Active Record Migrations in Ruby on Rails, Flyway for Java, Entity Framework Migrations in .NET. These scripts sequentially transform the schema and data.

Cloud Data Migration

Cloud Data Migration is the transfer of data from on-premise infrastructure to the cloud or between clouds. AWS Snowball, Azure Data Box and Google Transfer Appliance are used for physical transportation of terabyte-sized arrays. VPN tunnels and replication are used for online migration. According to Gartner, by 2027 70% of migrations will be performed in a hybrid cloud environment.

Planning Data Migration

Data Migration without a plan is a guaranteed failure. Planning includes auditing the current schema, data profiling, choosing a strategy, preparing the environment, testing and approving the rollback plan. According to a McKinsey study (2024), 54% of failed migrations are caused by the absence of a formal plan.

Audit and profiling

Before migration, it is necessary to audit the source system: determine data volume, number of tables, dependencies between entities, types of nullable fields and the presence of duplicates. Profiling identifies anomalies — NULL values in key fields, format mismatches, broken links. This data forms the baseline of the full dump.

Test environment preparation

A test migration is performed on a copy of production data before the main launch. The goal is to check ETL pipeline performance, transformation correctness and loading speed. A minimum of three full test cycles is recommended before Big Bang. Each cycle includes a full transfer, validation and rollback.

Rollback Plan

Rollback is a return to the original system when critical errors are detected. The rollback plan includes: a full source backup before the start, schema recovery scripts, a step-by-step instruction for re-enabling the old system and a communication plan for user notification. Without an approved rollback plan, Data Migration should not be launched in production.

Data Migration Code Examples

Let us consider a practical Data Migration example in Kotlin combined with Flyway. The migration script V1 creates a users table and transfers data from a legacy format. Flyway automatically tracks applied migrations and guarantees idempotency.

// V1__Migrate_Users.kt — data migration from legacy format
import org.flywaydb.core.api.migration.BaseJavaMigration
import java.sql.Connection
import java.sql.PreparedStatement

class V1__MigrateUsers : BaseJavaMigration() {
    override fun migrate(connection: Connection) {
        val legacyUsers = connection.prepareStatement(
            "SELECT id, name, legacy_role FROM users_legacy"
        ).executeQuery()

        val insertStmt: PreparedStatement = connection.prepareStatement(
            "INSERT INTO users (id, name, role, migrated_at) VALUES (?, ?, ?, NOW())"
        )

        while (legacyUsers.next()) {
            insertStmt.setInt(1, legacyUsers.getInt("id"))
            insertStmt.setString(2, legacyUsers.getString("name"))
            val role = mapLegacyRole(legacyUsers.getString("legacy_role"))
            insertStmt.setString(3, role)
            insertStmt.executeUpdate()
        }
    }
}

A migration example in Python using SQLAlchemy for transferring data from CSV to PostgreSQL. The script extracts from the file, converts types and loads the target tables.

# migrate_data.py — loading CSV into PostgreSQL with transformation
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql://user:pass@host/db")
df = pd.read_csv("legacy_orders.csv")

df["order_date"] = pd.to_datetime(df["order_date"])
df["amount"] = df["amount"].astype("float")
df.to_sql("orders", engine, if_exists="append", index=False)

print("Data Migration completed")