The migration had been running for six hours when the engineer noticed the phone numbers. A regex-based ETL script was dutifully copying customer records from a 2007 Access database into a shiny new Postgres instance, and somewhere around record fourteen thousand it had started stripping the leading zeros from UK mobile numbers. The system thought 07700 900123 was actually 7700900123, which is a completely different kind of wrong. The client, a property management firm with twelve thousand tenants, was not going to find this amusing.
This is the quiet terror of data migration. It's never the big, obvious fields that kill you. It's the edge cases, the encoding artifacts, the fields that were "optional" in 2007 but are required in 2024. Traditionally, you solve this with painstaking manual mapping — a business analyst and a developer spending weeks documenting schemas, writing transformation rules, and praying. Recently, a new tool has entered the toolkit: the large language model. Used correctly, an LLM can accelerate the drudgery of AI assisted data migration legacy systems work. Used incorrectly, it can manufacture errors at industrial scale.
The promise is seductive. Dump two schemas into Claude or GPT, ask it to propose a mapping, and watch it reason about field names, data types, and relationships in seconds. What used to take two days of spreadsheet archaeology now takes twenty minutes. The catch is that LLMs are confident liars. They'll map "CUST_NM" to "customer_name" correctly nine times out of ten, but on the tenth they'll hallucinate a relationship or misinterpret a business rule that costs real money.
AI assisted data migration legacy systems projects work when the LLM handles the mapping logic and a human-curated validation suite handles the safety. The AI proposes. The code transforms. The tests catch. This isn't AI replacing the engineer — it's AI replacing the spreadsheet.
The Safe Pattern
The safe way to use LLMs in migration is a three-stage pipeline. Stage one is discovery: the LLM reads both schemas and proposes a mapping, including data-type conversions, relationship inferences, and transformation logic. Stage two is code generation: the engineer reviews the mapping, corrects hallucinations, and generates the actual ETL code. Stage three is validation: an automated suite checks every migrated record for type fidelity, referential integrity, and business-rule compliance.
The LLM never touches production data. It never writes to the target database. Its entire job is to produce a mapping document and, optionally, the transformation code that implements it. The engineer owns the review. The test suite owns the safety.
Validation Suite Must-Haves
A migration validation suite needs three layers. The first layer is schema validation: every column in the target schema has the right type, the right constraints, and the right nullability. The second layer is referential integrity: every foreign key resolves, every parent record exists, and no orphaned rows slipped through. The third layer is business-rule validation: custom logic that checks domain-specific constraints the schema can't express.
For example, a business rule might state that every active tenant must have a valid phone number and at least one emergency contact. The schema can enforce presence. It can't enforce "valid" or "emergency." That's where the validation suite comes in. The LLM can help draft these rules by reading the old system's documentation and the new system's requirements. The engineer codifies them. The test suite runs them on every record.
Human Review Protocol
Even the best automated suite can't catch every error. Human review is still necessary, but it needs to be targeted. Reviewing twelve thousand records one by one is impractical. The right approach is stratified sampling: review every record in high-risk tables, review a random sample from medium-risk tables, and review only exceptions from low-risk tables.
High-risk tables usually include anything with financial data, personally identifiable information, or regulatory reporting requirements. Medium-risk tables are standard operational data. Low-risk tables are logs, audit trails, and anything that can be regenerated if lost.
Timeline Reality
Clients always underestimate data migration. They think it's a simple copy-paste. In reality, discovery usually takes thirty percent of the timeline because legacy data is always dirtier than anyone admits. The Access database with twelve thousand tenants also has four hundred duplicate records, seventeen different phone-number formats, and a column called "NOTES" that contains everything from maintenance requests to legal threats.
A realistic timeline for a focused single-system migration is six to ten weeks. Week one is profiling and discovery. Weeks two through four are mapping and build. Weeks five through six are dry runs and validation. Week seven is the production cutover. Weeks eight through ten are buffer for the things that inevitably go wrong.
Final Thought
LLMs are a powerful accelerator for AI assisted data migration legacy systems work, but they're not a replacement for engineering judgment. The engineer who uses AI to draft mappings and write validation suites will outrun the engineer who does it by hand. The engineer who trusts AI to handle the migration unsupervised will eventually explain to a client why their phone numbers are wrong.