Moving from legacy systems to modern ones looks like a two-click copy. At scale, it runs into technical, operational and business challenges that affect data quality, timelines, cost and the overall success rate. Identifying them early lets teams plan how to reduce each risk before it becomes an unplanned cost.
1. Poor source data quality
Over years, databases accumulate:
- Duplicate records (the same customer entered three times with different spellings)
- Missing values (null emails, blank postcodes)
- Inconsistent formats (12/03/2024 vs 2024-03-12, USA vs United States)
- Outdated information (old addresses, departed employees)
- Invalid data (negative quantities, impossible dates, broken references)
If this data is migrated without cleansing, the problems simply move to the new environment: garbage in, garbage out.
How to mitigate it
- Profile the data first: measure null rates, duplicates, distinct values and outliers.
- Define data quality rules together with the business owners.
- Cleanse and deduplicate before loading, or in a staging area.
- Decide what not to migrate: archive or drop obsolete data.
2. Data compatibility between source and target
Different databases and applications use different data types, structures, schemas and business rules. Examples:
- Oracle NUMBER vs PostgreSQL NUMERIC or INTEGER, and DATETIME time-zone handling
- A single name field in the source, but first_name and last_name in the target
- Status codes (A and I) that must become active and inactive
- Character encodings (Latin-1 to UTF-8) that corrupt accented characters
How to mitigate it
- Build a detailed source-to-target mapping document.
- Write explicit transformation rules for every field.
- Validate strictly: row counts, checksums, field-level comparisons and sample reviews by business users.
3. Large data volumes
The more users a system has, the more data it collects. Organisations may need to move millions of records, terabytes of files and years of historical data. This leads to network saturation and slow transfers, long migration windows, and downtime risk for the business.
How to mitigate it
- Plan migration windows (weekends or low-traffic periods).
- Tune performance: bulk loads, parallelism, and disabling indexes and constraints during the load, then rebuilding them.
- Migrate incrementally: move historical data first, then sync the changes with change data capture (CDC) until cutover.
- Use offline transfer appliances (for example AWS Snowball) when the network is too slow.
Other common challenges
Added context beyond the lecture:
- Hidden business logic: rules buried in legacy code, triggers or stored procedures that nobody documented.
- Downtime constraints: some businesses cannot stop for even a few hours.
- Security and compliance: protecting sensitive data in transit and staying within GDPR, HIPAA and similar rules.
- Scope creep and unclear ownership: no one decides what data matters.
- Insufficient testing: problems are discovered only after go-live.
- Skills gaps: few people still understand the legacy platform.
Using AI to address these challenges
AI is increasingly used to speed up and secure migrations, especially from legacy platforms to modern cloud and data lakehouse architectures:
- Data mapping automation: suggests source-to-target field mappings from names, types and sample values.
- Data cleansing automation: detects duplicates, anomalies and invalid records, and proposes fixes.
- Code translation: converts legacy code (PL/SQL, T-SQL, COBOL, stored procedures, ETL jobs) to modern SQL, Spark or Python.
- Predictive risk and validation: flags high-risk tables, predicts failures, and automatically reconciles source and target.
AI speeds up the work but does not replace human judgement. Mappings, business rules and final sign-off still need review by engineers and data owners.
Key takeaways
- Migrations are much more complex than they look.
- The three core challenges are poor source data quality, compatibility between source and target, and large volumes.
- Each has standard mitigations: profiling and cleansing, mapping and validation, and incremental or optimised loading.
- AI-assisted automation (mapping, cleansing, code translation, validation) is increasingly central to modern migrations.
Next: the risks and failures of data migration.