Data Migration and ETL
Moving data between systems without losing rows, breaking references, or discovering the problem in week three.
Data migration is the work that looks simple in the plan and consumes the project. The data is never as clean as the source system's owners believe, the edge cases are not documented, and the point at which you find out is usually after the old system has been switched off.
I approach migrations on the assumption that the data is worse than described - because it always is - and build the reconciliation in from the start.
What this covers
One-off migrations and ongoing pipelines, both.
- System-to-system data migration
- Database platform migrations
- ETL and ELT pipeline development
- Data cleansing and de-duplication
- Schema mapping and transformation rules
- Reconciliation and verification reporting
- Incremental and delta sync strategies
- Legacy format extraction - fixed-width, EDI, spreadsheets
- Scheduled and event-driven pipelines
- Rollback planning and dry runs
How a migration runs
Rehearsed repeatedly before it is run for real. Nobody should be finding out anything new on cutover day.
1Profile the source
Before mapping anything, I profile the actual data: null rates, duplicates, orphaned references, encoding problems, values that violate the documented rules. This nearly always changes the plan.
2Agree the mapping and the rules
Field-by-field mapping, plus explicit decisions about what happens to bad records. Written down and signed off, because these are business decisions rather than technical ones.
3Build it repeatable
The migration is written as code that can be run again from scratch, not as a sequence of manual steps. That is what makes rehearsal possible.
4Dry runs and reconciliation
Run against a copy, then reconcile: counts, checksums, totals, spot checks on known records. Repeat until the report is clean.
5Cutover with a rollback path
A planned window, a rehearsed sequence, and a documented way back if something unexpected appears.
The part that goes wrong
In my experience the failures are almost never in the transformation logic. They are in the assumptions: a field that was reused for a second purpose in 2014, a customer record duplicated across three systems with different spellings, a currency column that turned out to hold two currencies, character encoding that survived every test until it met a real customer name.
This is why profiling comes before mapping and why reconciliation is built in rather than performed once at the end. A migration you can run repeatedly and verify automatically is a migration you can be confident in; one that runs once, by hand, at 2am, is not.
The same discipline applies to ongoing ETL pipelines, with the addition that they need to handle partial failure and be safe to re-run. A pipeline that cannot be re-run without duplicating data will eventually cause an incident. If the systems either side of the pipeline are themselves the problem, legacy code modernisation may be the more useful place to start.
Related services
API Development
REST and GraphQL APIs, integrations and microservices that are documented, versioned and pleasant to consume.
Legacy Code Modernisation
Old codebases made safe to change again: upgrades, refactoring, tests and documentation, without a risky rewrite.
Cloud & Server Admin
AWS and Linux setup, hardening, monitoring and deployment pipelines for teams without a dedicated ops person.
Frequently asked questions
How long does a data migration take?
The profiling and mapping phase usually takes longer than the build. For a single well-understood system, expect weeks rather than days; for multiple sources with poor data quality, longer. I will give you a realistic estimate after profiling, not before.
Can the business keep running during the migration?
Usually yes. Depending on the systems involved, that may mean an incremental sync with a short final cutover, or a parallel-run period. Both are planned rather than improvised.
What happens to records that fail validation?
That is a business decision, made explicitly and in advance: rejected and reported, migrated with a flag, or corrected by rule. What does not happen is records disappearing silently.
Do you build ongoing pipelines as well as one-off migrations?
Yes - scheduled and event-driven ETL pipelines with monitoring, alerting and safe re-run behaviour.
Facing a migration?
Tell me what is moving from where to where. The first useful step is usually profiling what is actually in the source system.
Tell me about your project