Data Migration Engineer
Data Engineering
Move data between systems without losing, corrupting or silently duplicating any of it. Over 12 weeks you'll plan and execute migrations end to end: profiling the source, mapping schemas, handling the rows that do not fit, reconciling counts, and designing a rollback you would actually be willing to use. Migrations are judged entirely on what does not happen. The work is in the verification — proving that what arrived matches what left, and knowing what to do about the three percent that never maps cleanly. Topics covered: source profiling and data quality assessment, schema mapping and type coercion, incremental and dual-write cutover strategies, idempotent and resumable loads, reconciliation and row-level verification, rollback planning, zero-downtime cutover.
Time commitment
10-15 hours per week
Prerequisites
Confident SQL and comfort with at least one scripting language. Some familiarity with how databases handle transactions and constraints. Patience for detail is the real prerequisite.
You'll leave with
A completed migration between two real systems, with a written runbook, a reconciliation report proving nothing was lost, an exception log for the rows that did not map cleanly, and a rehearsed rollback plan.
What you receive
Structured weekly plan
Clear objectives, defined tasks, and concrete deliverables for every week of the program.
AI-led evaluation
Every submission is reviewed across technical proficiency, ownership, communication, and learning.
Human expert on request
Request a human expert reviewer for advanced topics, escalations, or specialised guidance.
Verifiable credentials
Formal offer letter on approval. Verifiable experience letter on completion.
The 12-week roadmap
Every week is planned before you start.
- Week 1
Profile the source before promising anything
Migrations fail in week one, at the point where someone estimates the work without looking at the data. Spend this week finding out what is actually in the source system: the row counts, the nulls where the schema says there cannot be any, the three date formats in one column, the encoding that breaks on row 40,000.
What you'll do
- Stand up source and target databases locally with a dataset of at least 100k rows
- Profile every table: row counts, null rates, distinct counts, min/max, orphaned keys
- Find at least five data quality issues the schema alone would not reveal
- Quantify each: how many rows, and what happens to them if you do nothing
- Write the profiling report that a stakeholder would read before approving the work
You hand in: A reproducible profiling script and a written report naming each issue, its row count, and its consequence.
- Week 2
Schema mapping, including what does not map
Produce the field-by-field mapping between source and target. The interesting part is the residue: the columns with no destination, the destination columns with no source, and the ones where the types almost match. Decide each case now, in writing, rather than at 2am during cutover.
What you'll do
- Build a field-level mapping document covering every source column
- Mark each as direct, transformed, derived, defaulted, or deliberately dropped
- Write the transformation rule for every non-direct mapping
- List target columns with no source and decide the default for each
- Get the mapping reviewed by someone who knows the source domain
You hand in: The signed-off mapping document with a decision recorded against every column on both sides.
- Week 3
Build the loader, and make it resumable
Write the migration itself, with one hard requirement: it must be safe to run twice, and safe to kill halfway. A migration that only works on a clean run is a migration you cannot use, because the first attempt will not be clean.
What you'll do
- Implement batched extract, transform and load with a configurable batch size
- Make every write idempotent, keyed on a stable natural or surrogate identifier
- Track progress so an interrupted run resumes where it stopped
- Run it twice end to end and assert the target row count is identical
- Kill it mid-run, restart, and prove no row was written twice or skipped
You hand in: The loader, the idempotency proof from a double run, and the interrupted-run evidence.
- Week 4
The exception path: rows that will not go
Every migration has a percentage that does not fit. The failure is not that they exist, it is silently dropping them. Build the quarantine path, make it visible, and produce the list a human has to make decisions about.
What you'll do
- Add a quarantine table capturing rejected rows with the reason for rejection
- Categorise rejections: malformed, orphaned, duplicate, out of range, unmappable
- Make the run fail loudly if the rejection rate exceeds a threshold you choose
- Build the exception report a business owner can triage without SQL
- Define the re-processing path for a corrected row
You hand in: The quarantine mechanism, a categorised exception report, and the documented threshold with its justification.
- Week 5
Reconciliation: proving nothing was lost
This is the week that decides whether the migration is trustworthy. Counts matching is necessary and nowhere near sufficient — sums, checksums and row-level comparison are what let you say the target is faithful to the source.
What you'll do
- Reconcile row counts per table, with rejections accounted for explicitly
- Reconcile aggregates: sums of monetary columns, min/max dates, distinct key counts
- Build row-level checksum comparison on a statistically meaningful sample
- Investigate every discrepancy to root cause — no unexplained differences
- Produce the reconciliation report as a signable artefact
You hand in: The reconciliation suite and a report where every difference is either zero or explained.
- Week 6
Referential integrity and the order of operations
Load order is not cosmetic. Get dependencies wrong and you either fail on constraints or, worse, succeed with orphans. Work out the correct order, handle circular references, and decide what happens to a child whose parent was rejected.
What you'll do
- Build the dependency graph across all tables and derive the load order from it
- Handle at least one circular or self-referencing relationship explicitly
- Decide the policy for orphaned children and implement it
- Verify every foreign key in the target resolves after the load
- Document the ordering and what breaks if someone changes it
You hand in: The dependency graph, the ordered load, an integrity verification query returning zero orphans, and the ordering note.
- Week 7
Scale and the rehearsal that finds the real problems
Run against full production volume in a rehearsal environment. Everything that worked on 100k rows will behave differently at 100 million, and the point of a rehearsal is to discover that somewhere other than the real cutover.
What you'll do
- Generate or obtain a full-volume dataset
- Run the complete migration end to end and time every phase
- Find the bottleneck; improve it and re-measure
- Record memory, disk and connection usage under load
- Produce a realistic duration estimate with a contingency you can justify
You hand in: Rehearsal timings by phase, the bottleneck analysis with its fix, and the duration estimate.
- Week 8
Cutover strategies, and choosing one on purpose
Big-bang, phased, or parallel-run — each buys something and costs something. Work through all three against your scenario and choose deliberately, rather than defaulting to whichever is easiest to build.
What you'll do
- Write the trade-offs of big-bang, phased and parallel-run for your specific case
- Implement a dual-write or change-data-capture path for incremental sync
- Measure the residual delta after an incremental sync completes
- Calculate the downtime each strategy requires
- Choose one and write the argument, including what you are accepting
You hand in: The trade-off analysis, a working incremental sync, and the chosen strategy with its reasoning.
- Week 9
The rollback you would actually be willing to use
A rollback plan nobody has tested is a document, not a plan. Build one, rehearse it under time pressure, and find out whether it works when the target already has new writes in it.
What you'll do
- Define the point of no return and what makes it irreversible
- Build the rollback procedure, including data written after cutover
- Rehearse a rollback from a mid-migration state against a clock
- Time it and write down the real number
- Define the decision criteria for triggering it, and who decides
You hand in: The rollback procedure, a timed rehearsal record, and the written go/no-go criteria.
- Week 10
The cutover runbook
Write the document the migration is actually run from: every step, in order, with its owner, its duration, its verification, and its abort condition. Then have somebody else follow it while you stay quiet.
What you'll do
- Write the runbook step by step with owners and expected durations
- Add a verification check after every destructive step
- Include the communication plan: who is told what, and when
- Have a peer execute it without your help
- Revise it wherever they hesitated
You hand in: Both versions of the runbook and a note on every point where your instructions were not enough.
- Week 11
Execute the cutover
Run the real thing in a controlled environment, following your own runbook exactly, including the parts that are inconvenient. Log the timeline as it happens rather than reconstructing it afterwards.
What you'll do
- Execute the full cutover following the runbook without improvising
- Record a timestamped log of every step as it happens
- Run the full reconciliation suite against the migrated target
- Handle whatever goes wrong, and write down what it was
- Make the formal go-live decision against your own criteria
You hand in: The execution log, the post-cutover reconciliation report, and the recorded go-live decision.
- Week 12
Handover and the honest post-mortem
Close the migration out properly: validate against the business rather than against your own checks, hand over to whoever runs it now, and write the post-mortem that names what went wrong without softening it.
What you'll do
- Have a domain owner validate the migrated data against their own expectations
- Document the residual known issues, including any you chose not to fix
- Write the decommissioning plan for the source system
- Write a post-mortem: what went to plan, what did not, what you would change
- Hand over to the operating team and have them confirm they can support it
You hand in: The sign-off record, the known-issues list, the decommissioning plan, and the post-mortem.