AI data migration: where automated transfer breaks
When transferring accounting data between systems, failure rarely comes from the storage — the database will accept any valid set of rows. What breaks is the semantic part: matching entities from the old and new models.
If in the source a client is a legal entity with a settlement account, while in the target it is a contact person, automatic matching by field name will produce a plausible but incorrect result.
The first bottleneck is divergence of reference data.
Classifiers, units of measure, statuses, and categories in the two systems almost always differ in cardinality: the source has several hundred values, the target has dozens, some codes are outdated, and some were created manually.
String-similarity matching fails on close wordings, and an incorrectly transferred account status silently changes access rights.
The second is broken record relationships. Transfer proceeds table by table, while links between them rely on identifiers: if a contract arrives before its counterparty or receives a new key, the relationships fall apart.
Orphan records appear in the target — payments without invoices, requests without clients, charges without contracts.
The third is manual edits during transfer. They are made in an intermediate table "quickly," outside the procedure: the result is not reproducible, and on the next load the edits are overwritten. We'll examine how each of these gaps is closed.
What changes after switching to automated transfer
The key change is that transfer rules stop living in chat threads and nested spreadsheets.
Field mapping, reference-data normalization, and handling of empty and conflicting values are described as configuration in a repository: one branch — one source of truth, any change goes through review instead of arriving as an "edit in the last file." Some transformations are convenient to keep in dbt — then the rule registry, tests, and field descriptions sit side by side and read as a single document.
From this grows run repeatability. The same set of rules on the same data slice yields the same result: the run is idempotent, a repeated pass does not create duplicates or append records retroactively.
A working pattern emerges — run the transfer on a copy, compare extracts, record discrepancies, and only then go to the production environment.
The share of manual edits falls not because the rules are "smarter," but because they are applied to all records equally. The remaining manual work turns into a short exception list with a reason: ambiguous reference data, a broken relationship, a missing source.
Such a list is visible in full, and it does not grow from run to run — this is measurable transfer quality control.
Automated verification closes the last gap: reconciliation of row counts and checksums for key fields, checks for lost relationships, and a before-and-after discrepancy report.
Instead of "we transferred it, and everything seems to be in place" — a reproducible protocol for each run showing exactly what passed, what went into exceptions, and which rules led to that.
Migration approaches: streaming transfer and batch loading
The choice of mode is determined by two things: how critical downtime is and how the load on the source is structured.
Streaming transfer reads the change log and delivers each transaction to the receiver almost immediately — the source continues to accept writes, and maintenance windows are not needed.
Batch loading works with consistent snapshots on a schedule: simpler to debug and rerun, but requires a window in which writes to the source are stopped.
Between these extremes there is almost always a hybrid scheme — and it covers the main migration scenario. Below is a comparison by applicability and limitations.
| Option | When it fits | Limitations |
|---|---|---|
| Streaming transfer via change log | The source cannot be stopped; data is needed in the new environment with minimal delay | Requires rights to read the log; during a mass update of the entire table, the stream lags; rollback is more complex |
| Batch loading with snapshots | One-time platform change, reporting environment, downtime acceptable in a maintenance window | Transfer without downtime is impossible: writes are stopped to take the snapshot and catch up the delta |
| Snapshot plus change stream | Migration of a running environment: the snapshot is taken in advance, changes accumulate in parallel, switchover takes minutes | The stream start point must be tightly tied to the snapshot, otherwise rows diverge |
Whatever the outcome of the transfer, it is closed not by switchover but by step-by-step reconciliation: first row counts for each table, then checksums, then a sample by keys at range boundaries.
Discrepancies are resolved before moving the load to the new environment — after switchover, finding them is noticeably more expensive.
Implementation stages: from schema analysis to handover
Each stage ends with an artifact that is checked before the next one starts changing data. That is why mapping rules and the rollback point are prepared in advance — a migration error is caught on a slice, not in the production environment.
- Source inventory. We walk through databases, extracts from accounting systems, and file-based tables, recording volumes, formats, duplicates, and "dirty" fields. The result is a source registry with an entity map.
- Field mapping schema. We match source and target fields, describe type conversions and reference data. The model suggests options, and the engineer approves contentious points with the data owner.
- Transfer pipeline assembly. We configure extraction, normalization, deduplication, and writing; rules are versioned so any change can be explained and reverted.
- Test run. We run the flow on a data slice and calculate the share of fields mapped automatically and the number of discrepancies. The report on them is the basis for fine-tuning the rules.
- Data acceptance. We reconcile checksums and aggregates for key entities, and manually spot-check critical records. Criteria are fixed before the run, not after.
- Rollback plan. We prepare a target snapshot and a rollback point: if reconciliation does not match, we return to the original state without losing records.
- Handover to operation. We provide the launch procedure, discrepancy monitoring, and schema documentation — the flow is maintained without our involvement.
Project artifacts: what the customer retains after delivery
We consider project delivery not to be a transfer report, but a set of materials that your team can work with independently going forward. Everything our engineers used is handed over in an editable form: configurations, schemas, logs, and documentation.
- Mapping rule configurations — synonym dictionaries, source priorities, handling of ambiguous cases. They are changed by editing files, without modifying code.
- Transfer schemas — which source, in what order, and through which reference data a record passes, and where mandatory reconciliation points are located.
- Check logs — protocols for each section: the share of records that passed automatically, what went to manual review, and which discrepancies remained open.
- Rollback procedure — the order for returning the storage to its previous state, launch conditions, control points, and responsible parties.
- Maintenance instructions — how to restart a transfer section, add a rule, connect a new source, and update the mapping model.
- A set of control queries and test samples — to recheck data after any rule change.
Materials are updated during the work, not written before delivery, so by the final stage they have already been tested on real runs. You receive not a description of a system, but a working kit: with it, a new engineer can get up to speed on the project in days, not months.
Case study: transferring reference data and balances to a new system
A distributor was transferring its item master and warehouse balances from a legacy SAP system to a new accounting system.
The environment contained 1.4 million product cards, 380 thousand balance rows across 42 warehouses, and seven sources, each with its own units of measure, groups, and supplier codes.
The first transfer was done with extracts and manual reconciliation, and discrepancies surfaced after the period was closed — when correcting anything is expensive.
Matching by exact SKU failed: in the sources, SKUs were written with different delimiters and without leading zeros, which meant 210 thousand items required human decisions. A full run took about 14 hours, and changing a single rule meant restarting the whole thing.
What was changed
Normalization was placed before comparison: SKUs, units of measure, and names are reduced to a canonical form, and only then matched by name and attributes with a confidence score.
Pairs above the threshold are transferred automatically; the rest go into a review queue with differences highlighted, and the analyst's decision is recorded and reused in subsequent runs.
Results in numbers
Manual review dropped from 210 thousand records to 11 thousand — 5% of the volume. A full run now takes 38 minutes, and a repeat run after edits takes 7, because only the affected branches of the reference data are recalculated.
Verification by checksums — the number of balances for each warehouse and group before and after transfer — reduced discrepancies to zero at acceptance, while mapping conflicts fell to 0.4% from 12% on the first attempt.
How does automated field mapping differ from manual mapping?
Automated mapping relies on two signals: structure (type, length, field name) and content — the share of matching values.
Each source-to-target pair is assigned a confidence score: an exact name match gives a high score, while a fuzzy match by string similarity and shared reference-data values gives an intermediate one.
Then the confidence threshold takes effect. Above the upper threshold, the pair is applied automatically; between the thresholds, it goes into a suggestion list; below, it goes into quarantine.
The two-level scheme prevents automation from silently corrupting data while not forcing everything to be reviewed manually.
Manual mapping is required where meaning cannot be derived from the data: one field in the source is split across two in the target, units of measure differ, the key is composite, or historical item duplicates have accumulated in 1C and must be collapsed before transfer.
Expert review is not a full reconciliation, but a review of quarantine plus confirmation of a sample from auto-applied pairs.
The share of manual review becomes a working metric: it falls from the full volume of fields to single-digit percentages, and each expert decision is returned to the rules and improves the accuracy of the next run.
The boundary lies in the nature of the decision: automation narrows the space of options and takes on the volume, while humans are responsible for semantic agreements — which entities to consider the same and how to interpret discrepancies.
That is why the confidence threshold and rules are configured for the specific accounting setup rather than taken as universal.
Technical concerns: accuracy, rollbacks, quality control
Integrity is confirmed not by selective "eyeball" checks, but by reconciliation at the dataset level: the number of records before and after, checksums for numeric fields, and a slice by primary keys.
A discrepancy is immediately tied to the source record and to the rule that produced it — this shows whether it is a mapping error or a reference-data conflict.
Every mismatch and every duplicate pair goes into a report with a decision: accept, merge, or defer. Until a decision is made for a record, it does not go into the target system — the transfer proceeds in waves, and each wave is closed by reconciliation.
How is data integrity confirmed?
By three reconciliations: by count, by checksums of numeric fields, and by a slice of primary keys. Parent-child relationships are additionally reviewed — this catches records that formally transferred but lost a link. The reconciliation report is saved together with the run.
What to do with record mismatches and duplicates in reference data?
The model proposes a merge option and rationale, and the responsible person makes the decision. Disputed pairs go into quarantine — a separate set that is reviewed after the transfer without blocking other records.
Can the transfer be rolled back?
Yes. Records are written to the target system with a run label, so rollback is deleting a set by label, not restoring from a backup. Rolling back an individual wave does not affect data that has already been accepted and confirmed.
What does operation logging record?
Every transformation: source value, the rule that triggered or the model decision, final value, editor, and time. The log is immutable — it can restore the history of any record, even if it was edited manually after the transfer.
Let's discuss your task and come back with an estimate and plan
For the estimate to be workable rather than approximate, engineers need inputs on the data and the environment. The more specific the source materials, the fewer assumptions in the work plan and the more realistic the timelines.
- List of sources: databases, file extracts, external interfaces, message queues — with volumes and update frequency.
- Table schemas or anonymized sample extracts — we use them to verify automated field mapping against real values rather than guesses.
- Business rules: what is considered a duplicate, which fields are mandatory, and how to interpret empty and conflicting values.
- Requirements for the target system: supported types, unique keys, reference data, and mandatory relationships between entities.
- Verification criteria: which discrepancies are critical and what share of records may be checked manually at acceptance.
- Access and environment constraints: where processing is allowed to be deployed and whether a closed environment without external access is required.
- History of past transfers: where relationships broke, what did not reconcile in reports, which scenarios were already tried and why they were abandoned.
In response, we return a work plan, not general promises: stages, the order of runs on a test set, the verification scheme, timeline estimate, and team composition — who is responsible for mapping, who for data validation, and who for accepting the result.
Describe the task in free form and attach what you already have — we will come back with an estimate and a plan.
