What Is Data Migration? Types, Process & Best Practices

Data migration is the extraction, transformation, and loading of data from a source system into a new platform, preserving accuracy, usability, and operational continuity.

Data migration is one of those projects that sounds boring until it breaks something important.

Then suddenly it is the most exciting thing in the company, in the worst possible way. Sales cannot find accounts. Finance reports do not match. Someone is asking why a customer’s address is blank. Another person is asking why the system is slow. And the migration team is sitting there going, we moved the data, what do you mean it is not there.

So yeah. Data migration matters.

Not because moving rows from A to B is inherently hard. But because businesses run on assumptions. And migrations are where those assumptions get tested, in public, with deadlines.

This guide covers the practical stuff. What data migration actually is, the major types, real use cases, concrete examples (with mapping and validation ideas), common approaches, the full lifecycle, the usual traps, and a tool rundown that is actually organized by what you are trying to do.

And I will keep repeating the same theme, because it is true.

Most migrations fail less from tools, and more from poor scoping, unclear ownership, and weak validation.

What is data migration?

Data migration is the process of moving data from one system, database, storage, or format to another while preserving accuracy, meaning, security, and continuity of business operations.

At its core, data migration is:

1. Extract data from the source system
2. Transform it (map fields, convert formats, clean values, apply rules)
3. Load it into the target system
4. Reconcile and validate that the target matches expectations

People call that ETL or ELT, depending on where transformations happen, but the idea is the same. Move the data. Preserve meaning. Prove it worked.

What data migration is not

A lot of projects get confused because the words sound similar. These are related, but not the same.

Migration vs integration

• Migration is usually a one time or finite project to move data and shift operations to the new system.
• Integration is ongoing data exchange between systems that continue to exist.

Migration vs replication

• Replication continuously copies data from a source to a target for availability, read scaling, or disaster recovery.
• A migration might use replication or CDC for low downtime cutover, but the end goal is usually to stop using the old system.

Migration vs backup

• A backup is for restore and resilience. It is not optimized for operational use.
• A migration changes the live operating environment. It is not just “store a copy somewhere.”

Migration vs archiving

• Archiving moves older or inactive data into cheaper storage or a system designed for retention and retrieval.
• It can be part of a migration, especially when you decide not to bring full history into the new system.

The “source of truth” shift

A migration often changes the system of record.

That is a big deal. It means:

• Which system is authoritative for a customer record changes
• Which IDs are canonical changes
• Which timestamps and statuses the business trusts changes
• Which validation rules, workflows, and permissions define reality changes

This is why migrations are not only technical. They are operational and political too, honestly.

Why data migration matters

In plain English, data migration is moving data from one system, location, or format to another without breaking business operations.

That last part is the whole game.

A migration is not “successful” because the load job finished. Success usually means:

• Accuracy: values are correct, not just present.
• Completeness: nothing important went missing.
• Minimal downtime: the business can still run, and cutover does not turn into a weekend-long incident.
• Security and compliance: the data did not leak, and it landed in the right place with the right controls.
• User adoption: people can do their jobs in the new system. Reports match expectations. Workflows still make sense.

If you have been on a few of these projects, you already know where things go sideways.

Not usually because “we picked the wrong tool.”

It is more like:

• We did not decide what “done” means, so everyone had a different definition.
• We did not define ownership, so nobody could approve mapping decisions.
• We migrated garbage data faithfully, and then acted surprised it was still garbage.
• We tested the happy path and skipped reconciliation, and then discovered the missing 4 percent when the CFO asked for numbers.
• We forgot about permissions, attachments, audit trails, retention rules, or the 12 downstream systems nobody mentioned until cutover week.

This article will help you avoid that. It is not magic. It is mostly clarity.

Where migrations show up in the real world

You see migrations in places like:

• Switching ERP or CRM platforms
• Moving from on prem infrastructure to the cloud
• Modernizing an app, splitting a monolith, changing the database engine
• M&A consolidation where two companies need one set of systems
• Rebuilding analytics, moving from legacy warehouses to modern cloud platforms

The 6 major types of data migration (with quick examples)

Most migrations fall into a few buckets. Real projects often combine them. You might do a cloud move and an application migration at the same time, plus schema changes. Which is where things get spicy.

1) Database migration

This is moving data between database engines or versions.

Examples:

• Oracle to PostgreSQL
• SQL Server on prem to Azure SQL Database
• MySQL to Aurora
• PostgreSQL 11 to PostgreSQL 16 (version upgrade plus extension changes)

Common triggers:

• Licensing costs
• Performance and scalability
• Standardization across teams
• Moving to managed services

Key complexities:

• Stored procedures and functions do not port cleanly
• Data type differences
• Indexing strategy changes
• Sequences, identity columns, and key generation
• Collation, case sensitivity, and timezone behaviors

2) Application migration

This is moving data between applications, not just databases.

Examples:

• Salesforce to HubSpot
• Zendesk to Freshdesk
• NetSuite to SAP
• Workday to another HRIS

Complexities:

• Object models differ, sometimes dramatically
• Custom fields and custom objects
• Workflows and automation rules
• Permissions and role models
• Attachments, notes, emails, activity history

This type basically requires mapping workshops with the business, because you are not just mapping columns. You are mapping meaning.

3) Cloud migration

This is moving data from on prem to cloud, or between cloud providers.

Examples:

• On prem PostgreSQL to AWS RDS
• On prem SQL Server to Azure SQL Managed Instance
• S3 to Azure Blob
• AWS to GCP for analytics workloads

Patterns:

• Lift and shift (move as is)
• Replatform (move to managed DBs)
• Refactor (change architecture)

Focus areas:

• Network throughput and transfer time
• Encryption in transit and at rest
• IAM, key management, access boundaries
• Cost controls (storage tiers, egress, query costs)

4) Data warehouse / analytics migration

This is moving analytics stacks.

Examples:

• Teradata or Oracle DWH to Snowflake
• Hadoop based warehouse to BigQuery
• Redshift classic to lakehouse style architecture

Shifts you often see:

• Modeling changes (star schema vs more ELT and modular models)
• Transformations moving to dbt or SQL based pipelines
• Semantic layer rebuild in BI tools

Complexities:

• Massive historical data volumes
• Partitioning and clustering differences
• Query compatibility and function differences
• Rebuilding BI dashboards and metric definitions
• “Same numbers” expectations

5) Storage migration

This is unstructured data.

Examples:

• NAS to object storage
• File share to SharePoint
• SharePoint to Google Drive
• On prem document management to cloud storage

Key issues:

• Metadata preservation
• File paths and naming constraints
• Permissions and ACL translation
• Deduplication
• eDiscovery and retention policies

Validation often looks like:

• Checksums or hashes
• File counts and total size checks
• Sampling and spot checks
• Permissions diff reports

6) Schema/format migration (transformation heavy migration)

Same platform, new structure.

Examples:

• Normalizing a database
• Converting JSON blobs into relational tables
• Renaming columns and splitting tables
• Merging two customer tables into one canonical model

When required:

• App redesign
• New compliance fields
• New reporting needs
• Standardization across teams

Risks:

• Semantic drift, data moves but meaning changes
• Broken downstream dependencies, reports, integrations
• Quiet bugs, like timezone conversions or rounding rules

Common migration approaches: big bang vs phased vs parallel run

There are three common approaches. None is “best.” It depends on downtime tolerance, regulatory constraints, integration complexity, and whether your team can handle coexistence.

Big bang

Everything migrates, then you cut over once.

Pros:

• Fastest cutover
• Less long term complexity

Cons:

• Highest risk
• If something is wrong, it is wrong everywhere
• Requires strong test coverage and rehearsals

When it works:

• Small scope
• Simple dependencies
• You can tolerate downtime
• You can rehearse multiple times

Phased or waves

Migrate by business unit, region, product, or object set.

Pros:

• Reduces blast radius
• Easier to learn and improve each wave

Cons:

• Coexistence is hard
• You need sync strategies between old and new
• Reporting becomes messy during transition

Good when:

• You have multiple teams and domains
• Downtime must be limited
• You can tolerate a longer program timeline

Parallel run

Run old and new in parallel, reconcile outputs.

Pros:

• Best for regulated environments
• Builds confidence
• Helps prove parity for audits and finance

Cons:

• Operationally expensive
• You basically run two systems
• Requires tight reconciliation processes

How to choose, quickly:

• If you cannot tolerate downtime and you need high confidence, parallel run or phased.
• If you have a small dataset and clean boundaries, big bang can be fine.
• If your org is messy and requirements are shifting, phased often gives you room to breathe, but it can also drag forever if you do not enforce boundaries.

Real world data migration use cases (what companies actually migrate)

Let’s talk about what actually happens in companies. Not theoretical migrations, real ones, with business outcomes and hidden requirements.

Use case 1: CRM migration during a sales process redesign

Business goal:

• Better pipeline visibility
• Better lead routing and automation
• Cleaner reporting
• Improved adoption

What moves:

• Accounts and contacts
• Leads
• Opportunities and pipeline stages
• Activities: calls, meetings, tasks
• Notes, emails, attachments
• Owner assignments and team roles

What usually breaks:

• Deduplication rules (two systems have different definitions of duplicate)
• Ownership rules and territory logic
• Stage semantics (stage names might map, but meaning does not)
• Required fields that did not exist before
• Inactive users and reassignment

Success metrics:

• Adoption (people actually use the new CRM)
• Report parity (the key dashboards match expectations)
• Pipeline accuracy (stage totals and conversion rates make sense)

Hidden requirements:

• Consent and marketing preferences
• Audit trails for deal changes
• Email capture rules and privacy settings
• Identity mapping for users and teams

Use case 2: ERP migration for finance/operations standardization

Business goal:

• Standardize processes across regions
• Improve controls
• Support growth, new entities, new products
• Reduce manual reconciliations

What moves:

• Chart of accounts
• Vendors and customers
• SKUs, inventory, BOMs
• Purchase orders and invoices
• Payments, credits, journals
• Tax and currency configurations

Common pitfalls:

• How much historical transaction data is in scope
• Period close timing
• Currency conversion rules
• Tax rules, exemptions, jurisdiction mapping
• Master data governance (who owns vendor creation, SKU rules, etc)

Cutover sensitivity:

• Finance does not love surprise changes during close
• Audit requirements mean you need evidence, not vibes
• You may need read only access to the old system for years

Hidden requirements:

• Retention rules and legal hold
• Segregation of duties and role mapping
• Approval workflows and controls evidence

Use case 3: Cloud move to reduce infrastructure burden

Business goal:

• Reduce ops overhead
• Improve reliability
• Scale without constant hardware planning
• Improve DR posture

What moves:

• App databases
• Logs and event streams
• Files and documents
• Backups
• Analytics pipelines

Key constraints:

• Bandwidth and transfer time
• Downtime windows
• Encryption and key management
• DR strategy and RPO/RTO targets

Why teams choose managed DBs:

• Patching and upgrades
• Built in HA
• Scaling options
• Monitoring and backups included (though you still need to configure them right)

Hidden requirements:

• Data residency, region restrictions
• Access patterns changing (private networking, VPNs, zero trust)
• Cost surprises (storage, egress, query usage)

Use case 4: Data warehouse modernization for faster analytics

Business goal:

• Faster queries
• Lower cost for scaling
• Easier ingestion from SaaS tools
• Better governance and lineage
• Enable self serve analytics

What moves:

• Raw ingestion pipelines
• Transformed marts
• BI dashboards and metric layers
• Governance policies, access controls

Typical needs:

• Redesign ELT, do more transformation inside the warehouse
• Implement data quality checks and observability
• Rebuild semantic layer in Looker, Power BI, Tableau, etc

Stakeholder expectation management:

• People expect “same numbers” across old vs new reports
• But often the old system had undocumented logic, manual fixes, or broken joins
• You need a plan for metric definition and sign off

Hidden requirements:

• Data retention and purging policies
• PII handling in analytics environments
• Row level security and masking

Use case 5: M&A consolidation (two companies, one set of systems)

Business goal:

• One view of customers, products, and financials
• Reduce duplicate systems and costs
• Unified operations

Two patterns:

• Consolidate into one company’s existing platform
• Build a new shared platform (sometimes cleaner, often slower)

Hard parts:

• Identity resolution (same customer, different IDs)
• Duplicate vendors, customers, SKUs
• Conflicting business rules and definitions
• Different fiscal calendars, currency, tax handling

Compliance:

• Retention requirements may differ
• Legal holds and eDiscovery
• Auditability of what changed and why

Hidden requirements:

• Decommissioning legacy systems properly (access removal, cost shutdown, archival)
• Contractual data obligations
• Data sharing boundaries during transition

Data migration examples (mini case studies with mapping + validation)

This is where it becomes real. Each example shows: source, transformation, target, and how to validate.

Example 1: SQL Server to PostgreSQL (data types + identity columns)

Scenario:

• You are moving an operational database from SQL Server to PostgreSQL to reduce licensing cost and standardize on Postgres.

Source:

• SQL Server tables using NVARCHAR, DATETIME, BIT, UNIQUEIDENTIFIER, IDENTITY columns

Target:

• PostgreSQL tables with TEXT or VARCHAR, TIMESTAMP, BOOLEAN, UUID, sequences

Sample mapping (common patterns):

Identity and sequence strategy:

• Preserve primary keys if the application expects stable IDs.
• If you bulk load explicit IDs, you must set the sequence afterward so it does not collide.

Example:

• Load table with existing IDs
• Then run something like:
• SELECT setval(‘table_id_seq’, (SELECT max(id) FROM table));

Validation checks:

• Row counts per table (with filters if soft deletes exist)
• Primary key uniqueness
• Foreign key integrity checks
• Min and max dates for key timestamp columns
• Null rate comparisons per column (especially required fields)
• Aggregate totals for key measures (counts by status, totals by day)

Also, check indexing. A migration that is “correct” but 10x slower is still a failed migration to users.

Example 2: Salesforce to a new CRM (object model differences)

Scenario:

• Sales is redesigning pipeline stages and moving from Salesforce to a different CRM.

The tricky part is not extraction. It is meaning.

Source objects

• Lead, Contact, Account, Opportunity, Activity, Task, Event, Attachment, User

Target objects

• Often a different model. Some CRMs combine lead and contact differently, some use person accounts conceptually, some do not.

Mapping decisions you have to make:

• What becomes a lead vs a contact in the new system
• Whether you merge duplicate contacts across accounts
• How you handle converted leads in Salesforce (they already became contacts and opportunities)
• How you map stage names and forecast categories

Sample mapping ideas:

Handling inactive users:

• Build a user crosswalk table.
• Decide a policy: reassign to manager, to a queue, or to a “legacy owner” placeholder.
• Document it. People will ask later.

Validation checks that matter to the business:

• Pipeline totals by stage (before vs after)
• Activity counts per rep (especially last 30/90 days)
• Number of open deals and their close dates
• Required fields pass rate
• Duplicate rule behavior (how many merges happened, how many were flagged)

And do a sampling session with sales ops. Ten random deals, click through, do they look right. That catches semantic drift faster than any row count.

Example 3: On prem file share to S3 or Azure Blob (permissions + metadata)

Scenario:

• You are moving a departmental file share into object storage for durability and cost, maybe also to support an app.

Source

• Windows file share with folder hierarchy and NTFS ACLs

Target

• S3 buckets or Azure Blob containers, with IAM policies and possibly object metadata tags

Key design choices:

• Preserve folder structure as key prefixes, or flatten it
• How to store metadata for search, classification, retention
• How to map permissions that do not translate perfectly

ACL translation reality:

• NTFS ACLs can be very granular.
• Object storage permissions often work better with role based access at bucket or prefix levels.
• You usually need to simplify, which means you need business approval.

Document exceptions:

• Files that violate naming rules
• Oversized paths
• Unsupported characters
• Broken permissions inherited incorrectly in the source

Validation approach:

• File count comparisons per folder
• Total bytes per folder (approximate, but helpful)
• Checksums or hashes for a sample set, or for all files if feasible
• Permission sampling for sensitive folders
• A permissions diff report, even if it is high level

Also, plan for “what about shares that are actually used by old apps.” Those break quietly.

Example 4: Teradata to Snowflake (replatform + ELT)

Scenario:

• Modernize analytics. Move from Teradata to Snowflake.

Approach:

1. Stage raw data in Snowflake (landing tables)
2. Transform using SQL models, often with dbt
3. Rebuild marts and aggregates
4. Recreate scheduling and orchestration (Airflow, dbt Cloud, etc)
5. Repoint BI tools and rebuild semantic layer

Port stored logic:

• Teradata stored procedures and macros often need rewriting.
• Some logic moves into dbt models.
• Some moves into orchestration code.

Validation:

• Report parity for top dashboards
• Aggregates by day, region, product match within a defined tolerance
• Row counts in fact and dimension tables
• Performance benchmarks for top queries (latency, concurrency)
• Cost expectations (query patterns can get expensive if not managed)

And here is the part nobody likes. Sometimes parity fails because the old warehouse had undocumented fixes. You need a decision process: do we replicate the old bug for continuity, or do we fix it and communicate the change.

The data migration lifecycle (a practical end to end blueprint)

This is the part you can turn into a project plan. The order matters. And each phase has a “done looks like” output. If you cannot define done, you cannot finish.

1) Discovery and scoping

What you do:

• Inventory sources, tables, objects, volumes
• Identify data owners and business stakeholders
• Identify downstream consumers: reports, apps, integrations, extracts
• Decide history scope: full history, partial, or current state only
• Identify regulatory constraints: PII, PHI, PCI, retention, audit requirements

Deliverables:

• Source inventory
• Scope statement: in scope vs out of scope
• Stakeholder list and ownership model
• High level timeline and cutover strategy hypothesis

Done looks like:

• Everyone agrees what you are migrating and what you are not.
• You have named owners who can approve mapping and quality decisions.

2) Data profiling and quality baseline

What you do:

• Profile null rates, duplicates, outliers
• Check referential integrity (or at least approximate it)
• Identify forbidden values and invalid formats
• Build a quality baseline so you can measure regression

Remediation decisions:

• Fix at source (best long term, often slow)
• Fix in flight transformations (practical, needs documentation)
• Fix post load cleanup (risky, but sometimes necessary)

Deliverables:

• Profiling report
• Quality baseline metrics
• Issue list with owners and resolution plan
• Acceptance thresholds (what is acceptable, what triggers a stop)

Done looks like:

• You know what is ugly in the data, and you have a plan for it.

3) Data mapping and transformation design

What you do:

• Create mapping specs: field to field rules, transforms, defaults
• Define reference data and lookups (statuses, codes, categories)
• Define key strategy:
• Natural keys vs surrogate keys
• Crosswalk tables for IDs
• Document business logic explicitly

Deliverables:

• Mapping document
• Transformation rules
• Key strategy and ID crosswalk plan
• Data dictionary updates (target definitions)

Done looks like:

• Business signs off on meaning, not just column names.

4) Build migration pipelines (ETL/ELT)

What you do:

• Extraction strategy:
• Full extract
• CDC for deltas
• Batching, pagination, retries
• Transformation strategy:
• In staging
• In target (ELT)
• Load strategy:
• Bulk load tuning
• Upserts vs inserts
• Ordering to satisfy foreign keys
• Handling deletes, soft deletes, merges

Deliverables:

• Migration pipeline code or configuration
• Logging and observability plan
• Repeatable deployment process
• Data handling procedures (masking in non prod, secrets management)

Done looks like:

• You can run it repeatedly with consistent results. One off scripts are how you get burned.

5) Testing: unit, integration, and reconciliation

What you do:

• Unit tests for transformations (input, output expectations)
• Integration tests end to end
• Reconciliation:
• Row counts
• Checksums where possible
• Aggregates and totals
• Referential integrity checks
• Sampling with business users
• Performance tests:
• Load time
• Query latency
• Concurrency behavior

Deliverables:

• Test plan
• Test evidence and results
• Reconciliation reports
• Sign off checkpoints

Done looks like:

• You can prove accuracy and completeness enough for your risk level.

6) Cutover planning (and rollback)

What you do:

• Plan downtime window and freeze periods
• Communication plan (who needs to know, when)
• Rehearsals: run through cutover steps and time them
• Rollback plan:
• What triggers rollback
• How you revert systems
• How you handle divergence if some writes happened

Deliverables:

• Cutover runbook
• Rollback plan
• Dry run metrics (timings, failures, fixes)
• Stakeholder comms templates

Done looks like:

• You are not improvising at 2am. Or at least, less of it.

7) Go live, monitoring, and hypercare

What you do:

• Monitor error rates and pipeline failures
• Validate data freshness and key reports
• Monitor user workflows and support tickets
• Hypercare process: triage, fix forward vs rollback decisions
• Decommission legacy systems carefully

Deliverables:

• Monitoring dashboards and alerts
• Issue log and resolution process
• Ownership handoff
• Decommission plan, archiving evidence, access removal

Done looks like:

• The old system is not quietly still powering critical processes because nobody wanted to shut it off.

Key challenges (and how to avoid them without overengineering)

These are the failure modes that show up over and over. The fix is usually not a bigger tool. It is a clearer process.

Challenge 1: Bad or ambiguous data (garbage in, garbage moved)

Why migrations expose old problems:

• Duplicates that were tolerable in the old UI become a nightmare in a new system
• Missing IDs that were “optional” break foreign keys
• Inconsistent enums and free text fields do not map cleanly

Simple mitigation:

• Profile early, not after you build pipelines
• Define acceptance thresholds
• Create a cleansing plan with owners
• Decide what happens to bad records:
• Fix
• Quarantine
• Default values with audit trail

Challenge 2: Losing meaning during transformation

Semantic drift examples:

• A “status” field that used to mean billing status now gets interpreted as account health
• Currency rounding differences change totals
• Timezone handling shifts dates by a day for some regions
• “Active” means different things across systems

Mitigation:

• Mapping workshops with real owners
• Business sign off on definitions
• Unit tests for transformations
• Preserve legacy values in “source_*” fields when unsure, so you can trace and debug later

Challenge 3: Downtime and cutover anxiety

Drivers:

• Long loads and slow network
• Re indexing and constraint rebuild time
• App configuration dependencies
• Surprise data volumes

Mitigation:

• Phased cutover if the scope is too large
• Use CDC for final delta loads
• Pre load most data, then do a smaller final sync
• Rehearse and time every step

And keep a human reality in mind. People panic less when they know what is happening, even if it is not perfect.

Challenge 4: Security, privacy, and compliance gaps

Risks:

• Moving PII into non compliant zones
• Leaking exports to laptops or unsecured buckets
• Weak access controls in staging areas
• Non prod environments filled with real customer data

Mitigation:

• Data classification early
• Encryption in transit and at rest
• Masking or tokenization in non prod
• Least privilege IAM
• Audit trails:
• Who ran migrations
• When
• What datasets
• Evidence for auditors

A migration with no evidence trail is an audit headache waiting to happen.

Challenge 5: Performance surprises in the target system

Why “same data” performs differently:

• Indexes differ
• Partitioning strategy changes
• Query planner behavior differs by engine
• Old app used weird queries that only worked because of accidental indexes

Mitigation:

• Design for the target engine, not the source
• Benchmark critical queries
• Review and tune indexes, clustering, partitioning
• Update queries or ORM patterns if needed

How to choose the right data migration tool (the decision framework)

Start with requirements, not vendor names. Tools are amplifiers. They do not fix unclear scope.

Step 1: Write down your requirements

Ask:

1. What are the sources and targets, exactly?
2. How much data (rows, files, total size)?
3. Do you need CDC for low downtime?
4. How transformation heavy is it?
5. Do you need governance, lineage, approvals?
6. What are your security and compliance constraints?
7. Who will operate it after go live?

Step 2: Match the tool category to the job

Common categories:

• Managed cloud migration services
• ETL/ELT platforms for transformation heavy work
• Replication and CDC tools for low downtime
• Open source and developer first pipelines
• Database native utilities for one off moves

Step 3: Decide with criteria that actually matter

• Reliability and restartability
• Observability: logs, metrics, retry behavior, alerting
• Schema evolution handling
• Connector coverage and quality
• Cost model (per row, per connector, per hour, per GB)
• Security features and deployment model
• Team skill fit (who will debug it at 3am)

Practical advice:

• Run a proof of concept on 1 to 2 critical tables or objects.
• Include a messy one, not just the easy one.
• Validate end to end. Extract, transform, load, reconcile.

If you cannot reconcile in a POC, you will not magically reconcile in production.

Data migration tools (grouped by use case)

This is not every tool on earth. It is the ones that show up a lot, grouped so you can actually choose.

AWS Database Migration Service (DMS)

Best for:

• Heterogeneous database migrations
• CDC replication for low downtime

Strengths:

• Managed service
• Supports CDC
• Works well in AWS ecosystems

Limitations:

• Task tuning can be finicky at scale
• Transformations are limited
• Validation is not automatic enough, you still need reconciliation

Typical scenarios:

• SQL Server to Aurora
• Oracle to Postgres on RDS with CDC cutover

Azure Database Migration Service

Best for:

• Microsoft ecosystem moves

Strengths:

• Integrated with Azure tooling
• Useful assessments and guidance for supported paths

Limitations:

• Engine support is narrower than “anything to anything”
• Still requires planning for compatibility and app changes

Typical scenarios:

• On prem SQL Server to Azure SQL Managed Instance

Google Database Migration Service

Best for:

• Cloud native migrations into GCP supported engines

Strengths:

• Managed and integrated with GCP
• Good for supported homogeneous and some heterogeneous migrations

Limitations:

• Not a general transformation platform
• You still need validation and cutover planning

ETL/ELT platforms (transformation heavy migrations)

Fivetran

Best for:

• SaaS to warehouse ingestion fast

Strengths:

• Connector based, quick setup
• Reliable for many standard sources
• Minimal ops burden

Limitations:

• Custom transforms need add ons or downstream tooling
• Can get expensive at scale depending on pricing model

Typical scenarios:

• Salesforce, NetSuite, Jira into Snowflake or BigQuery, then transform with dbt

Matillion

Best for:

• Cloud warehouse migrations and ELT orchestration

Strengths:

• Strong for Snowflake, Redshift, BigQuery patterns
• Visual pipelines and orchestration

Limitations:

• Costs can scale with usage
• Still requires good engineering practices for testing and versioning

Informatica

Best for:

• Enterprise governance, complex transformations, regulated environments

Strengths:

• Mature platform, broad connectivity
• Strong governance, metadata capabilities

Limitations:

• Heavier implementation effort
• Cost and complexity

Typical scenarios:

• Large enterprise application migrations with complex rules

Talend (self managed / legacy footprint)

Best for:

• Flexible ETL where you already have Talend skills in house

Strengths:

• Powerful, flexible
• Works across many systems

Limitations:

• Self managed overhead
• Consider product roadmap and maintenance effort

Replication / CDC tools (low downtime)

Debezium

Best for:

• Open source CDC with Kafka ecosystem

Strengths:

• Strong for streaming change events
• Good flexibility and transparency

Limitations:

• Requires ops maturity
• You own the reliability story end to end

Typical scenarios:

• CDC from Postgres or MySQL into Kafka, then into target systems

Qlik Replicate (Attunity)

Best for:

• Enterprise CDC and replication across many endpoints

Strengths:

• Broad source and target support
• Mature CDC capabilities

Limitations:

• Licensing can be significant
• Still requires governance and reconciliation

Oracle GoldenGate

Best for:

• Oracle heavy environments, high reliability CDC

Strengths:

• Very robust for Oracle replication
• Mature and proven in critical systems

Limitations:

• Cost and complexity
• Requires specialized skills

Open source + developer first tools

Airbyte

Best for:

• Engineering owned ingestion and migrations with many connectors

Strengths:

• Many connectors
• Hackable and extensible
• Good fit when you want control

Limitations:

• Connector quality varies
• You run and operate it (unless using a managed offering)

Meltano

Best for:

• Orchestration around Singer taps and targets

Strengths:

• Flexible, developer friendly
• Good if you like code based pipelines

Limitations:

• Requires engineering time
• Connector ecosystem depends on Singer quality

Singer taps/targets

Best for:

• Lightweight, simple pipelines

Strengths:

• Simple concept, composable

Limitations:

• Quality varies a lot
• Not always enterprise ready without additional guardrails

Database native utilities (fast for one off moves)

Best for:

• Small to medium migrations
• Controlled environments
• Minimal transforms

Examples:

• pg_dump and pg_restore
• mysqldump
• SQL Server BACPAC exports
• Native bulk loaders:
• Postgres COPY
• MySQL LOAD DATA
• SQL Server bcp

Strengths:

• Fast
• Predictable
• Low tooling overhead

Limitations:

• Limited transformation support
• Not designed for complex mapping or CDC
• Validation and reconciliation are on you

What “good” looks like: success metrics and deliverables

If you want migrations to stop feeling like chaos, define acceptance criteria. Measure them. Publish them.

Measurable success metrics

Examples you can actually use:

• Accuracy: 99.9 percent of records match expected values for key fields, based on reconciliation tests
• Completeness: 100 percent of in scope objects migrated, with row count parity within defined rules (like excluding archived records)
• Reconciliation pass rate: all critical checks pass, non critical issues are documented and accepted
• Downtime: cutover completes within X minutes
• Performance SLAs: top 20 queries within X seconds, or dashboards load within X seconds
• Data freshness: if running CDC or incremental loads, lag stays under X minutes

Key deliverables (the stuff you will need later)

• Mapping document (signed off)
• Updated data dictionary
• Test plan and test evidence
• Reconciliation reports
• Runbook and rollback plan
• Audit logs and migration evidence
• Decommission plan, including compliance archiving

Operational handoff

After go live, who owns what:

• Monitoring dashboards and alerts
• Incident response for pipeline failures
• Data quality checks and remediation workflows
• Ownership of the new source of truth

If nobody owns it, it will rot. Quietly at first, then loudly.

Closing: a simple way to start your migration plan

If you are staring at a migration and not sure where to begin, do this sequence:

Scope → profile → map → build → test → cutover → monitor.

And start with a pilot. One domain, one object set, one critical table group. Prove your tooling, your mapping process, your validation approach.

Because the theme holds.

Migrations are primarily about clarity, validation, and ownership. Tools come last.

FAQs

What is data migration?

Data migration is the process of moving data from one system, database, storage location, or format to another, while keeping the business running and ensuring the data remains accurate, complete, and usable.

What is the difference between ETL and ELT in migration?

ETL transforms data before loading it into the target. ELT loads data first and transforms it inside the target system (often a cloud warehouse). Both can support migrations, the choice depends on tooling, scale, and where transformations are easiest to manage.

How long does a data migration take?

It depends on scope, data volume, downtime constraints, and data quality. Small migrations can take weeks. Enterprise ERP or multi system consolidations can take months or longer. The biggest schedule driver is usually mapping, validation, and stakeholder sign off, not raw data transfer.

What are the biggest reasons data migrations fail?

Unclear scope, missing ownership, poor data quality, weak validation and reconciliation, underestimating cutover complexity, and ignoring security or compliance requirements. Tools rarely fix these problems.

What is CDC and why is it used in migrations?

CDC means change data capture. It captures ongoing changes in the source (inserts, updates, deletes) and replicates them to the target. It is used to reduce downtime by keeping the target nearly in sync before final cutover.

Should we migrate all historical data?

Not always. Many teams migrate current state plus a limited history window, and archive older data for compliance or reference. The right choice depends on reporting needs, legal retention, and cost.

How do you validate a data migration?

Common validation methods include row counts, checksums or hashes, aggregate comparisons (totals by day, by status), referential integrity checks, null rate comparisons, and sampling with business users. For analytics, you often validate report parity and key metric definitions.

What is a “big bang” migration and when should you use it?

A big bang migration cuts over all at once. It can work for smaller, well understood scopes with strong test coverage and acceptable downtime. It is risky for large, interconnected systems.

What tools are best for data migration?

There is no single best tool. Managed cloud services (AWS DMS, Azure DMS, Google DMS) are good for database moves and CDC. ETL/ELT platforms (Fivetran, Matillion, Informatica) are better for transformation heavy work. CDC tools (Debezium, Qlik Replicate, GoldenGate) are best for low downtime. For simple moves, database native tools can be enough.

What deliverables should a migration project produce?

At minimum: a signed off mapping document, test plan and evidence, reconciliation results, cutover runbook, rollback plan, audit logs, updated data dictionary, and a decommission or archival plan for the legacy system.