The Migration We Said Would Take a Weekend
"Production data has memory. It remembers every bad decision that was made in the five years before you got there."
The task was described, in the handover document, as "migrate customer records from the legacy schema to the new data model." The legacy schema had 23 tables. The new data model had 31 tables. The migration would involve transforming roughly 2.8 million customer records and their associated data.
My tech lead's estimate was a weekend. A long weekend, he acknowledged, Friday evening through Monday morning, with a rollback plan ready in case anything went wrong. He had done migrations before. He was not being cavalier. He had seen the schema, understood the transformation logic, and calculated the volume. His estimate was reasonable by every measure I could apply at the time.
The migration took eleven weeks. Here is some of what we found.
The Character Encoding Problem Nobody Knew Existed
The legacy database had been created in 2009 with Latin-1 encoding. The new database was UTF-8. The vast majority of customer names were ASCII-compatible and worked fine. But 4,312 customer records contained characters outside the ASCII range, names with accents, names from non-Latin scripts that had been entered through a legacy API endpoint that apparently didn't enforce encoding, and a small number of records that contained literal byte-sequence garbage that had probably been entered through a third party integration that was no longer active.
The migration script, written and tested against a copy of the database that had been created with the same Latin-1 encoding and contained no non-ASCII characters, ran correctly against that copy. The first time it ran against a full production snapshot, 4,312 records failed with encoding errors at the point of insertion into the UTF-8 target. The other 2,795,688 records were fine.
We spent four days on this. The correct handling wasn't simply "convert all Latin-1 to UTF-8," because some of the characters weren't actually Latin-1, they were garbage bytes that had been stored raw and would produce garbage characters in any target encoding. Each category needed different handling: genuine accented characters needed proper conversion, non-Latin script characters needed review to confirm they were correct representations of actual names, and the byte-sequence garbage needed to be flagged for manual review by a member of the customer operations team who could look up the original source data.
At the end of this process, we had a cleaner customer name dataset than the company had ever had. That was not the goal. It was a consequence of the migration finding problems that had been invisible in the legacy database because the legacy database had simply stored whatever it was given without validation.
Orphaned Foreign Keys and the Records That Shouldn't Exist
The legacy schema had a table called customer_addresses with a foreign key to customers. Foreign key constraints had not been enforced in the legacy database, a decision that predated the current engineering team and that had probably been made for performance reasons that were valid in 2009 and irrelevant by 2014 when the hardware was upgraded.
The result: 18,907 rows in customer_addresses referenced customer IDs that did not exist in the customers table. Some of these customer IDs had existed and been deleted. Some had never existed, pointing to records that had been created incorrectly and then partially rolled back in ways that left orphaned children. Some were test records from the early years of the system that had been cleaned up incompletely.
The new schema enforced foreign key constraints. These 18,907 rows could not be inserted as-is. They needed to be categorised: addresses attached to deleted customers (exclude from migration, log for audit), addresses attached to IDs that never existed (same), and anything anomalous that warranted human review. Writing and running the categorisation query took a day. Reviewing the results and making decisions about each category took a week. Getting sign-off from the data governance team on the disposal decisions took another ten days.
The number of records involved, 18,907 out of roughly 2.8 million, looks small. The time required to handle them correctly was not small. Data quality work has a fixed overhead that doesn't scale with volume: understanding the problem, categorising the cases, getting decisions made, documenting what you did and why. You need roughly the same amount of process to handle 20,000 orphaned records as 200,000.
The Timezone Problem
The legacy system stored all timestamps in local time, specifically, in the server's local timezone, which at some point between 2009 and 2016 had been changed from UTC+5:30 to UTC. Nobody had documented this change. Nobody had migrated the historical timestamps when the server timezone changed. The database contained timestamps in two different timezones, stored in the same column, with no indicator of which timezone applied to which row.
We discovered this when we noticed that customer records created before approximately mid-2016 had signup timestamps roughly 5 hours and 30 minutes different from what the business records said they should be. A customer who the CRM system showed as having signed up at 10am IST had a database timestamp of 04:30.
Reconstructing the correct interpretation of each timestamp required cross-referencing against other tables, email send logs, payment records, event logs from the application layer, that had their own timestamp storage conventions. For records where no corroborating data existed, we had to make a policy decision: apply the IST offset to all pre-2016 records, accept that some records would remain incorrect, document the policy and the uncertainty.
This is a category of problem that is genuinely difficult to prevent in the first place. Timezone handling in databases is widely acknowledged as complex. The convention of storing timestamps as UTC and converting to local time only at the application layer was known and followed by some engineers on the team during some periods, not followed by others during other periods, and the server timezone change in 2016 was an infrastructure decision that didn't involve the application team at all. The inconsistency accumulated without anyone intending it.
The Frozen-Time Problem
The migration couldn't run in a single pass against the live production database. The data volume was too large, the transformation was too complex, and we needed to validate the output before switching over. The approach was: take a production snapshot, run the migration against the snapshot, validate, fix any issues, run again against a fresh snapshot, validate again. Repeat until clean.
The problem is that customer data changes continuously. Customers update their addresses. New customers sign up. Accounts are cancelled. During the time between taking a snapshot and completing the migration, the source data and the migrated data drift apart. By the third snapshot, taken five weeks into the project, the gap between what was in the snapshot and what was in live production was significant enough to matter.
The solution was a two-phase migration: migrate the historical bulk of the data from a snapshot, then run a synchronisation process that applied the delta, the changes that had occurred between the snapshot date and the cutover date, against the migrated database before switching over. Writing and testing the synchronisation logic was a separate engineering task that we hadn't scoped in the original estimate, because the original estimate hadn't anticipated that the migration would take long enough for the delta to become material.
What the Estimate Had Been Based On
The weekend estimate was not wrong about the easy part. The transformation logic itself, given clean data, with a correct schema mapping, the actual work of reading from one structure and writing to another, was straightforward and took about what was estimated. The estimate was wrong about the hard part: data quality issues that only appear when you run against real production data at full volume, process decisions that require sign-off from people outside the engineering team, and the complexity introduced by continuous change in the live system during a migration that took longer than anticipated.
The useful lesson from this isn't "estimates are hard", that's true but not actionable. The useful lesson is that production data has a history. It was created by systems with different assumptions, different bugs, different conventions. It contains records that reflect decisions made by people who are no longer at the organisation, for reasons that are no longer documented. A migration that looks simple when you're looking at the schema often becomes complicated when you're looking at the actual data, and the only way to know how complicated is to look at the actual data.
Running a full data quality analysis before estimating a migration, not against a sanitised sample, but against a real production snapshot, specifically looking for encoding issues, referential integrity violations, format inconsistencies, and unexpected distributions, is the step that could have caught most of what we found. It would have taken two or three days. It would have changed the estimate significantly and prevented the project from being under-resourced for the first several weeks while everyone recalibrated to the actual scope.
The migration was completed. The new schema was better, enforced constraints, consistent encoding, proper timezone storage. The company ended up with cleaner data than they started with. None of that was the goal. The goal was a weekend. What we got was eleven weeks of one of the best learning experiences of my career.
Tags