In general terms, the term Data Reconciliation signifies a process to verify that the data from the legacy source system was migrated per the given requirements, whatever they might be. In short “the data are still the same”.
The challenge is that data most often is transformed or changed so a simple one-to-one or number-to-number comparison is not always possible or even enough.
A simple example can be duplicate customers being merged into one customer, products being redefined, split into components, or bundled into more complex service and product offerings. Balances on orders, invoices and across accounting items is typically easier to reconcile, but never trivial. In some way, you don’t expect the data to be the same in the new system as it was in the old.
Let’s dive into this topic in a bit more detail to understand the different issues.
Definition
The difference between audit and reconciliation lies in their objectives and processes.
Reconciliation:
- It’s a process that compares two sets of records to ensure they agree.
- It’s used to ensure that the data (typically numbers) leaving an e.g. account matches the actual data entered in the new system - accounting approach.
- It’s a part of the internal processes of an organization for accuracy typically for (accounting type) numbers, but could also be used to reconcile different activities between systems like how many customers have pending questions in a call center, etc.
Audit:
- An audit is a formal check of some data or process, let's say financial accounts, of an organization, or an adherence to a stated policy (activity or process).
- It involves examining reporting, accounts, documents, and activities to ascertain how far the organization presents or adheres to regulatory or otherwise stated requirements or processes.
- It can be conducted internally by a somewhat independent group of employees or by external auditors.
- As part of an auditor’s work, they might ask for something to be reconciled.
For this blog, we just blend the activities and definitions above a bit carelessly as we are focusing on how the migration system and process make it possible to audit and/or reconcile the results of the migration easily and transparently.
Why is Data Reconciliation Important?
During data migration, errors can occur in extraction, mapping, and transformation or load and operation procedures. Technical failures, such as network dropouts or broken transactions, may corrupt data.
These errors can lead to issues like:
- Missing records
- Incorrect values
- Duplicated records
- Wrongly formatted values
- Broken relationships across data elements, tables, or entire systems
Data reconciliation helps by:
- Extracting accurate and reliable information from raw (close to source and target) data.
- Producing a consistent set of data representing the most likely process operation with as little as possible manipulation of data.
- Integrate an automated process (system or software) allowing for multiple control points to run fast including meaningful reporting of any divergence.
Ensuring that the migration system has a way to reconcile data for quality assurance and audit addressing these considerations is of critical importance in ensuring a (documented) successful outcome.
The way to achieve this is worth a closer look. In general, you want the reconsolidation to be conducted independently from the core migration engine. It sorts of points in the direction of creating a parallel mini-migration engine. The issue here - apart from being costly and time-consuming - it might itself be flawed with errors and hence not the reference to ‘the truth’ that was the idea.
Terminology Associated with Data Reconciliation
- Indicator or Control Point: The Data element chosen to be reconciled, stating measurement level, method, and expected outcome.
- Gross Error: Reflects bias errors, measurement failures, abnormal outliers, noise, and spikes in measurements.
- Observability: Consider which indicator can be determined based on constraints and measurements.
- Variance: Measures the variability of an indicator.
- Redundancy: Determines which measurements could or should be estimated from other indicators using assumptions.
In summary, data reconciliation ensures data integrity, consistency, and accuracy during migration. It’s like solving a jigsaw puzzle to align different data pieces!
How to do reconciliation during a data migration
Here is the textbook version of how to go about Data Reconciliation as part of a data migration.
Is a critical step and one that is often left for too late. If well-designed it will ensure the accuracy and consistency of data and provide comfort to stakeholders that the new system's data are per requirements.
Here's a step-by-step approach to effectively reconcile data:
1. Pre-Migration Data Assessment:
- Review the source data for completeness and accuracy.
- Identify any data quality issues that need to be addressed before migration.
2. Define Reconciliation Metrics:
- Establish key metrics and thresholds for reconciliation, such as record counts, sum totals, and data quality indicators.
3. Data Mapping and Transformation:
- Map source data fields to the target system.
- Transform data as necessary to fit the target system's requirements.
4. Perform Trial Migration:
- Conduct several trial migrations with a subset of data.
- Reconcile the trial migration results against the defined metrics.
5. Execute Data Migration:
- Migrate the full dataset to the target system.
- Monitor the migration process for any issues or discrepancies.
6. Post-Migration Reconciliation:
- Compare source and target data using the defined metrics.
- Identify any variances and investigate the root causes.
7. Resolve Discrepancies:
- Correct any data issues found during reconciliation.
- Reconcile the data again to ensure all issues have been resolved.
8. Final Validation:
- Perform a final validation to confirm that the data in the target system is accurate and complete.
- Sign-off on the data migration once all reconciliation metrics meet the predefined thresholds.
9. Documentation:
- Document the reconciliation process, findings, and resolutions for future reference.
10. Continuous Monitoring:
- Set up ongoing monitoring mechanisms to ensure data remains consistent and accurate over time.
This walk-through seems straightforward. Now only the issue is we need a system to do the reconciliation that is not (too) dependent on the system or process we are trying to check.
It's important to use the right tools and technologies that can automate and facilitate the reconciliation process, making it more efficient and less prone to errors.
Hopps approach to and support for data migration reconciliation
In almost all data migrations, there is a business requirement for reconciliation.
While everybody agrees on the general approach to reconciliation, it is more difficult to pin down specific practices once you get a bit more into the details.
In broad terms, reconciliation covers:
That all, in-scope data in the legacy source system
is either migrated or discarded in accordance
with the requirements.
Easily stated, but what does this actually mean? In conceptual terms, reconciliation means to reconcile expected results and actual results.
In some migrations, it is enough to count stuff: "We had x invoices in the legacy source system, y invoices were discarded by the migration as required, z invoices were delivered to the target system. If x minus y equals z everything is ok". For this kind of simple counting, the Hopp Portal is often quite enough to satisfy reconciliation requirements.
But in many migrations, this simple approach doesn't cut it. And then suddenly things become much more complicated and for a generic migration tool like Hopp, it becomes impossible to provide a generic reconciliation. The truth is that more advanced reconciliation actually means is completely dependent on what is actually being migrated.
In addition, the more transformations that are taking place in the migration, the more reconciliation is getting complicated. Given that Hopp is built for complex data migration, extensive transformations are simply a fact of life in any Hopp-driven data migration.
Hopp exposes interfaces to inspect the migrated data and collect Audit data that represents the Actual Result of the migration. By implementing these interfaces, a migration project can collect the Audit Data from each Business Object as it is migrated and hand back the collected audit data to the Hopp Runtime. The Hopp Runtime will store the Audit data next to the Business Object and take care of all the annoying housekeeping when Business Objects are re-iterated and new Audit data is collected.
For more info please look at our support site: More on Reconciliation and Audit
In addition to the Audit Collect interfaces, Hopp also exposes an Audit Result interface. This is called when an operator initiates an Audit Result job from the Portal Operations interface and will receive all the collected Audit data for all Business Objects.