Outsourcing ETL: The Hidden Risk in Data Migration
A £126 million difference in a trial balance is an effective way to get everyone’s attention.
The discrepancy emerged during the second test migration of a finance-system implementation I was supporting. Transactions had been duplicated after a supplier record was split into two records during the transformation process.
The defect could be corrected. The more difficult problem was what had already happened to confidence in the programme.
The finance team began questioning every report produced by the new system. This exposed a wider lesson: outsourcing the entire Extract, Transform and Load process can reduce the customer’s workload, but without the right controls it can also reduce visibility of the data the customer is ultimately expected to approve.
Here is what happened, how we restored traceability and what organisations should put in place before a data migration begins.
What is ETL in data migration?
ETL stands for:
Extract: taking the required data from the legacy system
Transform: converting, cleaning and mapping that data into the structure required by the new system
Load: importing the transformed data into the new system
A system migration commonly involves three parties:
the organisation moving systems;
the incumbent or legacy-system supplier; and
the new software or implementation supplier.
The source-system owner or customer is often well placed to support the extraction because they understand the existing database, data structures, history and retention requirements.
Where an organisation has a capable system administrator or data specialist who can interrogate the source effectively, completing the extraction internally can preserve valuable control over its scope.
Where the organisation relies mainly on standard reports, the incumbent supplier may be better equipped to perform the extraction.
That does not mean the new implementation supplier should never extract the data. The important issue is control.
Whoever executes the extraction, the customer needs a clear record of:
what was extracted;
what was excluded;
how the data was transformed;
how source and target fields correspond;
and how every migrated record can be traced back to its origin.
That was where this project ran into difficulty.
When one supplier owns the entire ETL process
My customer had made what appeared to be a sensible decision.
Rather than using significant internal resources to deliver the migration, the organisation appointed the new supplier to execute the full ETL process. The supplier had experience with both software platforms, although it was not the incumbent provider.
The plan included four test migrations followed by the production migration.
The supplier would complete the extraction, transformation and loading. The customer would then reconcile the data and approve the result.
On paper, this created a single point of accountability.
In practice, the customer was being asked to approve a process it could not fully see.
After the first test migration, the finance team began reconciling the data manually using spreadsheets and formulas such as XLOOKUP.
The team quickly encountered a practical limitation. There were more than four million transactions for a single year, together with numerous fields requiring source-to-target comparison.
Excel worksheets support a maximum number of rows well below the volume involved in this migration. Even if the records were divided between multiple files, the number of mappings and migration iterations would have made manual reconciliation slow, fragile and difficult to repeat.
The organisation needed a controlled reconciliation process rather than a collection of manual spreadsheet checks.
That was when Technoivity became involved.
Six gaps made the migration difficult to prove
The initial review identified six major control gaps.
1. The extract scope was not defined
Before you can prove that data migrated correctly, you need to know what was supposed to migrate.
At the start of the reconciliation, it was not clear:
which records had been extracted;
which financial periods were included;
which transaction types were covered;
or what had deliberately been excluded.
I requested the extraction scripts from the supplier. When they arrived, they showed that the extract covered the previous three financial years but excluded closed periods and period 13 year-end adjustments.
The problem was not simply the chosen scope. It was that the scope had been decided without consultation and had not been formally agreed with the customer before reconciliation began.
Migration control: Agree and document an extraction specification before the first test migration. It should define the systems, entities, records, periods, transaction types and exclusions in scope.
2. The transformation rules were unavailable
Extraction is only the beginning.
Where source and target systems use different lists of values, terminology or data structures, values need to be transformed.
Reconciliation becomes extremely difficult if the team testing the migration does not have access to those transformation rules.
VAT coding was one example in this project. A legacy value of T1 became STD20 in the new system.
Neither value proves that something is wrong. The reconciliation needs an agreed mapping to establish whether the transformation is correct.
Because those mappings were not initially available, I reconstructed the mapping tables by comparing the distinct values in both datasets and validating the relationships with the finance team.
Migration control: Every deterministic transformation should have an auditable source-to-target mapping or documented rule available to the reconciliation team.
3. The source and target fields did not match
Field names had also changed.
A source field called Value did not map intuitively to an identically named target field. Testing a sample of transactions established that the target system’s Amount field represented the comparable net value.
The target system’s Balance field represented a running total and should not have been used for that comparison.
This may sound straightforward once the answer is known. Finding and validating the correct relationship repeatedly across a new data model is not straightforward.
Without a documented data dictionary, the team risked comparing fields that looked similar but represented different information.
Migration control: Create a source-to-target data dictionary identifying the source field, target field, definition, transformation rule and expected result.
4. New identifiers broke record-level traceability
The supplier generated new identifiers during the migration.
Generating new IDs is not inherently a problem. The issue was that the new identifiers did not provide a way to trace a target record back to the corresponding source record.
From the second test migration onwards, I asked the supplier to retain the legacy reference in an external ID field for every migrated record.
That restored record-level lineage.
It also helped expose duplicate records in the source system that required separate investigation.
Without that connection, the team could identify that totals were different but could not efficiently establish which individual records had caused the discrepancy.
Migration control: Preserve a stable legacy identifier, external ID or documented cross-reference throughout every migration iteration.
5. The target data model introduced new relationships
One legacy record did not necessarily equal one target record.
Information previously stored as a single entity had been divided into one-to-many relationships. These included:
addresses;
bank details;
and contacts.
The change added three tables to the reconciliation design. Each child record needed to be compared with the single legacy record from which it originated.
This is an important distinction. Record counts alone do not demonstrate migration completeness when the underlying data model has changed.
A source dataset containing one supplier record might legitimately produce several related target records. Equally, an incorrect relationship could produce unintended duplicates while still appearing superficially complete.
Migration control: Document all source-to-target relationships, including one-to-many and many-to-one transformations, before designing the reconciliation.
6. Target-system reporting was not ready
The last problem was access to the migrated data.
The new system’s BI capability had not yet been deployed, while the available standard reports did not provide all the required information in a single suitable output.
Reports therefore had to be produced separately for 42 legal entities and combined before an organisation-wide reconciliation could be completed.
The standard reports also separated fields needed for comparison across multiple outputs.
We automated the consolidation of those files, avoiding the need to stitch the data together manually after each migration. Without automation, this preparation would have added roughly a day of manual work to every test migration.
Migration control: Prove that the reconciliation team can obtain complete, usable and consistently structured target-system data before migration testing starts.
Turning reconciliation into a repeatable control
At this scale, manual spreadsheet reconciliation was not sufficient.
The customer had already spent around three weeks on the first manual attempt without completing it.
I built a repeatable reconciliation process using Microsoft Power Query.
For each migration iteration, the process could:
ingest the legacy and target-system outputs;
consolidate reports from the 42 legal entities;
standardise the file structures;
apply the agreed transformation mappings;
align comparable source and target fields;
retain the source identifiers;
and report differences for investigation.
A complete run took just under 20 minutes.
That mattered because reconciliation should not be treated as a one-off exercise completed shortly before go-live.
When several test migrations are planned, the same controls need to run repeatedly. This allows the project team to confirm that defects have been corrected while also checking that a change has not introduced a regression elsewhere.
Automation did not remove the need for judgement. It directed the team’s attention towards exceptions requiring investigation instead of consuming its time on repeatable data preparation and comparison.
Independent reconciliation should operate across the entire ETL process, comparing the source and target data rather than validating only the final load.
Legacy system data moving through Extract, Transform and Load stages with independent reconciliation and assurance across the complete ETL process
The reconciliation that exposed a £126 million variance
The value of a repeatable process became clear during the second test migration.
The reconciliation showed that the trial balance was out by £126 million.
The investigation identified duplicated transactions created when a supplier had been split into two records during transformation.
The supplier corrected the issue for the following test migration.
Technically, the defect was recoverable.
But something less tangible had already been damaged: confidence.
When a finance team uncovers a variance of that magnitude, subsequent reports understandably receive much greater scrutiny. Users begin questioning whether other defects remain undiscovered and whether figures produced by the new system can be trusted.
Migration assurance is therefore not simply about demonstrating that the data eventually balances.
It is about establishing sufficient evidence, visibility and traceability for stakeholders to trust the process used to get there.
A better operating model: outsource execution, not assurance
The lesson is not that outsourcing ETL is inherently wrong.
Specialist suppliers can reduce the burden on internal teams and provide valuable migration experience. In some programmes, a new implementation supplier may be well placed to perform all three stages.
The risk arises when executing the migration and assuring the result effectively become the same responsibility.
If one supplier performs the extraction, transformation and loading, the customer still needs enough independent visibility to establish:
what was extracted;
what was excluded;
what changed;
why it changed;
where each target record originated;
whether data relationships changed;
whether totals and record-level values reconcile;
and what constitutes an acceptable result.
A supplier can execute the work, but the organisation remains accountable for deciding whether the migrated data is complete, accurate and fit for operational use.
That makes migration governance as important as the mechanics of moving the data.
Seven controls to establish before the first migration
The following controls should be in place before the first test migration is run.
1. An agreed extraction specification
Define the systems, entities, records, periods, transaction types and exclusions in scope.
The specification should make clear who approved the scope and how any subsequent changes will be controlled.
2. A source-to-target data dictionary
Identify which source fields correspond to which target fields.
Include field definitions, data types, expected formats and the business meaning of each field. Similar field names should not be assumed to contain comparable information.
3. A transformation register
Record value mappings, conversions, substitutions, default values and other transformation logic.
The register should distinguish agreed business rules from corrections made to address source-data quality problems.
4. A persistent source identifier
Retain a legacy reference or another cross-reference for record-level traceability.
If the target system requires a new identifier, store the original reference separately rather than replacing it without a trace.
5. A relationship map
Document where the target model introduces one-to-many, many-to-one or otherwise changed structures.
This is necessary to define meaningful completeness tests when a single source record becomes several target records.
6. A tested target-data extraction method
Confirm how reconciliation-quality data will be obtained from the new system before testing starts.
Do not assume that the required BI environment, reporting layer or custom reports will be available in time.
7. A repeatable reconciliation process
Automate comparisons where the data volume, structural complexity or number of migration cycles makes manual checking impractical.
The process should produce clear exceptions that can be reviewed, assigned, corrected and retested.
Together, these controls shift the reconciliation question from:
“Do the final totals look right?”
to:
“Can we demonstrate that the correct data moved, that every agreed transformation was applied and that the result is traceable back to the source?”
Reduce the workload without surrendering control
Outsourcing the ETL in this programme achieved part of what the customer intended. It reduced the internal workload associated with executing the migration.
However, too much knowledge about the extraction and transformation initially sat outside the customer team.
That made independent reconciliation harder than it needed to be.
The answer is not necessarily to bring every migration task back in-house. It is to decide deliberately which party executes each part of the migration and which party independently assures it.
For high-volume finance, payroll and HR systems, the reconciliation model should be designed before the first test migration is run, not after the first unexplained difference appears.
Planning a complex data migration?
Technoivity helps organisations establish the mapping, reconciliation, testing and governance controls needed to provide confidence in complex HR, payroll and finance-system migrations.
If your programme needs independent migration assurance, data reconciliation or support recovering a migration already in difficulty, book a discovery conversation with Technoivity.
Comments