A plain-language guide to what data migration actually involves, why it derails so many enterprise programs, and how to plan a successful transition.
What Is Data Migration?
Data migration means moving an organization's databases, schemas, stored procedures, reports, and applications from one platform to another. Think of it as moving your entire house — not just the furniture, but the plumbing, electrical wiring, and the habits of everyone who lives there.
Moving house analogy: You don't just throw boxes in a truck. You inventory everything, figure out what fits the new layout, move room by room, and live in both places until you're sure the new one works. Data migration follows the same steps — Assessment, Schema Conversion, Data Transfer, Validation, and Cutover.
Three Flavors of Migration
| Approach | What It Means | When It Applies |
|---|---|---|
| Lift-and-Shift | Move the data and schemas as-is, minimal changes. Get off the old license fast. | License renewal deadline, end-of-support hardware, cost pressure. |
| Re-Platform | Move the data and adapt schemas/procedures to take advantage of the new platform's strengths. | Modernization play. New features like Iceberg, multi-engine, or Kubernetes ops. |
| Re-Architect | Redesign the data model, pipelines, and applications from scratch on the new platform. | Fundamental change in data strategy — e.g., moving from data warehouse to lakehouse. |
Typical Cost and Timeline
Enterprise migrations from legacy platforms like Oracle Exadata or Teradata are major programs:
- Timeline: 12–36 months for a full migration (some drag on for 5+ years)
- Cost: $10M–$50M+ including consulting, parallel licensing, and lost productivity
- People: 20–100+ engineers at peak, plus vendor professional services
These numbers are why migration conversations make CIOs nervous — and why anything that reduces scope, risk, or duration has enormous value.
Why Migrations Fail
More than half of enterprise data migrations exceed their budget, timeline, or both. Here are the six most common reasons:
Stored Procedure Rewrite
This is the #1 blocker. A typical Oracle shop has 5,000–50,000 stored procedures in PL/SQL. Moving to most modern platforms means rewriting every single one in a different language. That's years of work, endless regression testing, and the biggest source of budget overruns.
Data Format Incompatibilities
Data types, character encodings, date formats, and NULL handling differ between platforms. Subtle differences cause data corruption that's only discovered months later.
Performance Regression
Queries that ran in 2 seconds on the old system take 20 seconds on the new one. Users revolt. The DBA team gets pulled into months of query tuning on an unfamiliar platform.
Dual-Run Costs
The old system can't be turned off until the new one is proven. That means paying for both licenses simultaneously — sometimes for years. Oracle and Teradata don't give discounts for "we're leaving."
Organizational Resistance
"It works, don't touch it." DBAs who've spent 15 years mastering Oracle don't want to start over. Business units don't want to risk their reporting. Migration requires executive air-cover and change management, not just technology.
Underestimated Complexity
The initial assessment misses dependencies — a reporting tool that connects via ODBC, a downstream system that depends on a specific stored procedure, a batch job that nobody documented. Scope creep is the norm, not the exception.
The customer's biggest fear is not the technology — it's the risk. Anything that reduces rewrite scope, shortens the parallel-run period, or lets existing DBAs keep their skills has massive value.
The Seven Phases of Migration
Every migration follows these phases, whether it takes 6 months or 6 years.
Phase 1 — Discovery
Inventory everything: databases, schemas, tables, stored procedures, ETL jobs, downstream reports, user permissions, and dependencies. You can't migrate what you don't know about.
This is where most teams get their first shock — they discover far more procedures, dependencies, and undocumented integrations than anyone expected.
Phase 2 — Schema Conversion
Translate the database structure (DDL) from the source system to the target. Map data types, constraints, indexes, and partitioning strategies. This is usually the easiest phase — most modern SQL is fairly portable.
Phase 3 — Data Transfer
Move the actual data. This involves an initial bulk load (terabytes or petabytes) plus ongoing Change Data Capture (CDC) to keep both systems in sync during the migration period.
Phase 4 — Procedure Translation
This is the big one. Stored procedures contain the business logic — calculations, validations, transformations that the business depends on. On most modern platforms, this means rewriting thousands of procedures in a new language. The effort required depends heavily on how compatible the target platform's procedural SQL dialect is with the source.
Phase 5 — Validation
Prove the new system produces the same results as the old one. Row counts, checksums, query result comparison — this is labor-intensive but non-negotiable. Regulators, auditors, and business owners all need proof. Federated query engines that can query both old and new systems simultaneously make this step significantly easier.
Phase 6 — Parallel Run
Both systems run side-by-side with production workloads. Results are compared daily. This is the "prove it" phase and typically lasts 1–6 months. It's also when dual-run costs are at their peak.
Phase 7 — Cutover
Switch applications, users, and reporting tools to the new platform. Decommission the old system. Pop champagne (or deal with the inevitable last-minute issues nobody anticipated).
Migration Paths by Source System
Not all migrations are equal. Here's how each common source system compares in terms of complexity and key considerations:
| Source System | Complexity | Key Enabler | Notes |
|---|---|---|---|
| Oracle / Exadata | Low | Procedural SQL compatibility + bulk extraction | PL/SQL compatibility on the target platform is the game-changer. Most Oracle procedures can translate with minimal manual effort when the target dialect is close. |
| Teradata | Moderate | AMP-aware parallel extraction | SPL (Teradata's procedural SQL) requires more restructuring than PL/SQL but less than a full rewrite. Efficient extraction depends on respecting Teradata's AMP parallelism. |
| PostgreSQL / Greenplum | Low | Native PL/pgSQL compatibility | PL/pgSQL is already close to standard procedural SQL. Greenplum's MPP model maps well to modern distributed engines. Often the easiest migrations. |
| SQL Server | Moderate | T-SQL adaptation | T-SQL conventions — variable syntax, error handling, temp tables — differ from PL/SQL patterns. Requires more procedure-by-procedure attention. |
| Cloudera / Hadoop | Low–Moderate | Native Hive/Spark, Iceberg upgrade | Already running Hive and Spark workloads. Migration is often an upgrade path: convert Hive tables to Iceberg, adopt Kubernetes ops, add multi-engine capabilities. |
| IBM Db2 | Moderate | SQL PL adaptation | Db2's SQL PL is close to PL/pgSQL. Main effort is adapting Db2-specific features (MQTs, temporal tables) to modern open-format equivalents. |
Oracle migrations tend to be the most tractable when the target platform has strong PL/SQL compatibility. Teradata migrations are helped significantly by tools that understand AMP parallelism for extraction and validation. PostgreSQL and Greenplum are natural fits for modern open-format lakehouses. Cloudera migrations are often upgrade paths rather than full rewrites.
Key Questions to Ask Before Starting
Use these questions early in migration planning to scope the effort and surface hidden complexity:
"How many stored procedures do you have?" This is the single best predictor of migration complexity. A few hundred is manageable. Tens of thousands changes the economics entirely — procedure translation becomes the dominant cost item.
"What's your current annual maintenance and license cost?" Oracle and Teradata licenses run $2M–$20M+ per year for enterprise customers. Quantifying this makes the cost of delay concrete.
"When is your next license renewal?" Renewals are the natural trigger for migration programs. Most organizations start planning 12–18 months before renewal. Timing matters.
"Have you attempted migration before? What blocked it?" Many enterprises have tried and failed. The answer is almost always stored procedures or underestimated complexity. Understanding past failures shapes the approach for the next attempt.
"Who owns the migration decision — IT, the data team, or the business?" Migration is a political decision as much as a technical one. IT wants simpler ops. Finance wants lower costs. Business wants less risk. Knowing who's driving — and who might block — is essential.
"How many databases and how much data are we talking about?" A single 50 TB data warehouse is a different program from 200 databases across 15 business units. The latter is a multi-year, multi-phase engagement that requires phased scoping.