Replicating a legacy database into a modern target looks like a solved problem — until the day the load breaks every night for a different reason. Yesterday it was an accented character turning into gibberish. Today, a column that did not fit. Tomorrow, a date the new database refuses. The team finds out from a user complaint, reprocesses by hand and turns into a retry operator.

That was the scenario at a Brazilian insurer: replications from a legacy Progress database into MySQL, orchestrated in Azure Data Factory, failing on every load. Here we describe what we did — mapping the failures into eight classes, converting the data dictionary between the two worlds with AI support, generating pipelines in bulk from the command line and leaving the replication observable instead of heroic.

The symptom: every load fails in a new way

Legacy replication is fragile by nature. The source carries thirty years of accumulated decisions — reused fields, types created before any standard existed, tables with no declared primary key. The target, modern and strict, refuses what the source tolerated in silence. Between the two, the orchestrator only reports that the activity failed.

The practical effect is familiar. The nightly load breaks, someone reprocesses it in the morning, it works “by chance” and nobody writes anything down. The following week the same error comes back in another table and the investigation starts from scratch. That is why the first decision was not technical but methodological: stop fixing and start classifying.

Why classify before fixing

Without classification, every incident looks new. With it, the pattern appears fast: eight error classes covered practically every failure on record.

• Connectivity and timeout — the integration runtime lost its session with the ODBC driver on long loads.
• Type with no direct equivalent — decimals and booleans reaching the target with different semantics.
• Truncation — target column smaller than the actual content.
• Encoding — accented characters corrupted between the legacy system and the target charset.
• Invalid date — zeroed or out-of-range dates, rejected by strict mode.
• Duplicate key — tables with no real primary key, carrying a repeated “identifier”.
• Null in a mandatory column — an optional field at source becoming NOT NULL at the target.
• Reprocessing without idempotency — an interrupted load duplicated rows when run again.

Each class earned an entry in the runbook: how to recognise it from the message, which SQL query confirms it, what the fix is and how to validate afterwards. That document is the cheapest and most underrated deliverable of the project — it turns “call the person who knows” into “follow the procedure”, the same principle we apply in IT support.

AI-assisted dictionary conversion

The most laborious part of replication is not moving rows, it is translating the dictionary. Hundreds of tables, thousands of columns, three decisions per column: which type at the target, which encoding, what to do about keys and nulls. Done by hand, that is a week of spreadsheets. With an AI assistant reading the exported schema and returning the target DDL, it is an afternoon — provided the output is treated as a proposal, not as truth.

Three fronts dominate the translation. Type with no direct equivalent: arbitrary-precision decimals, booleans, text with no declared limit. Encoding: the legacy system writes in a single-byte code page and the target expects UTF-8; without explicit conversion on read, cedillas and tildes arrive as rubbish — and the load finishes successfully. Keys and nulls: where the source declares no primary key, you have to choose between a natural identifier and a technical key.

This part is pure development supported by AI: the assistant proposes the column-by-column mapping, justifies each choice and generates the DDL. We review it — and that is where the value shows, because reviewing a thousand lines of proposal is far quicker than writing them.

Validating the conversion in three layers

Conversion without validation is a gamble. We use three layers, in this order.

1. Row count — rows per table, source against target. Catches partial loads and reprocessing duplicates. It is cheap and should run in every window.
2. Checksum per column — the sum of the numeric fields and an aggregate hash of the text ones. Exposes precision loss and corrupted accents, both invisible to a row count.
3. Targeted sampling — instead of a random sample, pick the edge rows: the longest text in the table, the value with the most decimal places, records with accents, minimum and maximum dates, null fields. Conversion errors live at the edges.

Pipelines in bulk, not click by click

With the dictionary settled, what remains is mechanical work: per table, one source dataset, one target dataset and one copy activity. In the portal, that means dozens of clicks, with human error guaranteed somewhere along the way.

We did it differently. One dataset template and one pipeline template, parameterised by table, columns and pre-load script; a generator that reads the converted dictionary and emits the JSON; and publishing from the command line, with everything versioned in the repository. The fix that closes the reprocessing class went in there: a controlled TRUNCATE as the pre-script of the copy, making the full load idempotent — running it twice now gives the same result as running it once. Six pipelines fixed and republished in a single window.

The gain is an order of magnitude. Fixing one pipeline in the portal took hours, and the fix did not propagate to the others. With templates, parameters and the command line, applying the same fix to the whole set takes minutes — and it is recorded as a code change, not as the memory of whoever happened to be on call. It is the discipline of infrastructure as code applied to data.

What remains after the fix

The goal was never zero failures, but that a failure should become a manageable event. By the end, the replication had four things it did not have before: classification (every failure falls into one of the eight classes), a runbook (each class with its diagnosis and fix), alerting (whoever needs to know finds out before the user does) and automatic validation inside the load window.

That changes who is able to respond. Before, only someone who knew the history could resolve it. Afterwards, any on-call engineer with the runbook handles the routine classes and escalates only what is genuinely new.

The honest limit is worth stating: AI replaced nobody here. It read schemas, proposed mappings, generated JSON and summarised hundreds of messages into eight patterns. The expensive decisions — what counts as a key, what may be rounded, what may be truncated — stayed with the people who answer for the data.

To go deeper, two resources from the Inove Academy prepare the ground. The quick guide to cloud migration helps decide what goes across as it is and what deserves a redesign before it becomes a pipeline — a badly designed replication is debt you carry along without noticing. And the FinOps calculator covers the other side of the account: a poorly built load window is runtime switched on for longer than it needs to be.

The lesson is simple to state and hard to practise. Reliable replication does not come from trying again more carefully, but from reducing the failures to a small, known set, converting the dictionary with real validation and fixing in bulk. AI shortens each of those stages — but whoever decides what correct data is remains the team, and it is just as well that it does.