Using Generative AI in SaaS Data Migration

Using Generative AI in SaaS Data Migration

This post documents how we use generative AI in production migration work — what it handles well, where it falls short, and the specific pipeline we built for one deduplication problem. It is written for engineers evaluating whether AI-assisted migration is worth the implementation cost.

Where Generative AI Fits in Data Migration Work

AI is useful for a narrow class of migration tasks: those that are repetitive, pattern-driven, and where the "correct" output can be defined well enough to validate. It is not useful for tasks that require architectural judgment, schema design, or understanding undocumented system behavior.

Task types where AI provides measurable value:

• Conditional field mapping: Applying transformation rules at scale once the rule is defined (e.g., "if source field X is null, populate target field Y with default Z").
• Format normalization: Standardizing date formats, phone number structures, address casing across large record sets.
• Fuzzy deduplication: Identifying records that refer to the same entity despite surface-level differences in spelling, punctuation, or abbreviation.
• Iteration scaffolding: Completing the remaining N records once the first 10 have been processed and validated by an engineer.

Task types where AI is unreliable without heavy human oversight:

• Schema mapping decisions (which source field maps to which target field — context-dependent and often underdetermined).
• Data loss detection (AI does not know what it does not know).
• Compliance-sensitive transformations (GDPR field exclusions, PII handling).
• Anything requiring knowledge of undocumented source system behavior.

The Pipeline Model: One to Ten

The most accurate mental model for where AI fits in migration work is this: AI does not replace the engineering work required to define the problem. It accelerates execution once that problem is defined.

• Zero to One: An engineer designs the migration pipeline — source schema analysis, target field mapping, transformation logic, validation criteria. This requires judgment. AI is not useful here in any meaningful sense.
• One to Ten: Once the transformation logic is defined and validated on a sample, AI can apply it at scale across the full dataset. This is where the time savings are real.

The ratio of Zero-to-One to One-to-Ten effort varies by migration complexity. Simple flat-file migrations may be 10% / 90%. Migrations involving custom objects, multi-system merges, or undocumented source APIs may be 60% / 40% — meaning AI provides proportionally less value.

Real Use Case: Fuzzy Deduplication Before a Gorgias Migration

The Problem

A client needed to clean 22,000 customer records before migrating to Gorgias. The Gorgias data model requires a unique customer identity per contact — duplicate records create support ticket fragmentation and break conversation history continuity. The deduplication had to happen upstream, before import.

The challenge was not simple exact-match deduplication. The duplicates were phonetically or abbreviation-equivalent names formatted differently:

• "St. Germain Paul" vs. "Saint Germain Paul"
• "de la Cruz, Maria" vs. "Maria De La Cruz"
• Hyphenated surnames with and without hyphens
• Accented characters vs. ASCII transliterations

A rule-based approach would require an engineer to enumerate every variant class explicitly. That is feasible for known patterns but fails on novel ones.

The Pipeline

Input format: CSV export from the source system, with columns for customer ID, full name, email, and account creation date.

Preprocessing: Before any LLM call, we normalized the name field — stripped leading/trailing whitespace, lowercased, removed punctuation — to reduce trivial variation. Email was used as a hard-match signal: any two records sharing an email were flagged as duplicates without LLM involvement.

LLM task: The remaining ambiguous cases — same or similar name, different email — were passed to GPT-4o via API. The prompt asked the model to assess whether two name strings referred to the same person, given the name variants and any available secondary fields.

Prompt structure (simplified):

You are a data deduplication assistant. Given two customer name strings, determine whether they likely refer to the same person. Consider abbreviations (St. = Saint), honorific variations, punctuation differences, and transliteration. Return JSON: {"likely_same": true/false, "confidence": "high/medium/low", "reason": "..."}.

Record A: {{name_a}}
Record B: {{name_b}}

Records were batched in groups of 50 pairs per API call to reduce latency and cost.

Output parsing: The JSON response was parsed and written to a staging table. Records flagged likely_same: true with confidence: high were auto-merged. Records with confidence: medium were queued for human review. Records with confidence: low were left as-is.

Validation: A random sample of 200 auto-merged records was reviewed manually by an engineer. The false positive rate on the high-confidence tier was low enough to proceed without reverting any merges. Medium-confidence records required approximately 2 hours of human review across the full set.

What Failed

The approach had documented failure modes:

• Non-Latin scripts: Names containing Arabic, Chinese, or Cyrillic characters produced inconsistent confidence scores. These were excluded from LLM processing and handled by the client manually.
• Common short names: Single-name records (e.g., "Maria" with no surname) produced high false positive rates — the model over-inferred similarity. These were routed to human review regardless of confidence score.
• Compound name reordering: "Paul Saint Germain" vs. "Saint Germain Paul" — the model handled this correctly most of the time, but edge cases with three-part compound surnames were unreliable.

Cost and Time

The 22,000-record dataset generated approximately 8,000 ambiguous pairs after the email hard-match pass. At GPT-4o API pricing (batched), the LLM processing cost was a small fraction of the engineering time it replaced. The full deduplication — preprocessing, LLM processing, human review of medium-confidence cases, and final validation — took under a day. A rule-based alternative, estimated against the actual variant classes found in the dataset, would have required several days of engineering time to build and would still have missed novel patterns.

Limitations

AI-assisted deduplication is not appropriate for all migration contexts:

• Low record volumes: Below a few thousand records, manual review is faster and cheaper than building an LLM pipeline.
• High-stakes identity fields: In financial, healthcare, or legal data, the cost of a false positive merge may outweigh the time savings. Human review of all matches is required regardless of confidence score.
• Unstructured free-text fields: LLMs handle name deduplication well because names have implicit grammar. Free-text notes, comments, or descriptions do not — model output is less reliable.
• Gorgias-specific constraint: Gorgias does not support bulk un-merge of customer records via API. A false positive merge that reaches the target system requires manual correction per record. This raised the cost of errors and was the reason we set a conservative confidence threshold for auto-merge.

When to Use AI in Migration Work

What This Approach Cannot Replace

AI did not design the migration. It did not map the Gorgias schema. It did not validate that ticket history would attach correctly post-import. It did not catch a field-length constraint on the Gorgias customer name field that caused import failures on records exceeding 100 characters — that required an engineer.

The accurate framing: AI compressed the execution time on one well-defined subtask. The migration project as a whole was still engineer-led, engineer-validated, and engineer-responsible.