Introduction
In my previous article, I discussed migration testing in general terms and the fundamental elements involved in testing. However, in my experience, the testing methods described there are insufficient when the objective is to assess the quality of migrated data in greater detail or to identify individual errors. So far, we have examined data at a relatively high level, but we have not yet explored how good the migrated data actually is. In this context, by quality I mean the ability to assess the correctness of every individual field value, such as an interest rate, accrued interest or maturity date.
Before delving into the assessment of individual data values, I would like to briefly share my first experience of comparing data between two results set. I had been working with databases for approximately five years when a more experienced developer in our team was assigned the task of comparing the outputs of two systems. The comparison quickly identified the contracts where differences existed. However, the results only showed that a discrepancy was present, not exactly where within the data it occurred.
This proved to be a significant challenge because the real investigation only began once a difference had been identified. To address this, an automated field-level comparison was developed that directly identified the data fields where the values in the source and target systems differed. Rather than requiring the developer to spend time locating the differing fields, the comparison immediately highlighted where the discrepancies were found.
This seemingly minor enhancement had a significant impact on testing efficiency. Time was no longer spent discovering the existence of an issue; attention could instead be focused directly on understanding the root cause of the discrepancy. At the same time, the comparison became a far more effective tool for supporting development. Next, I will explain how this approach can be applied to data validation in migration projects.
Data Validation
By data validation, I mean a detailed comparison of data between the old and new systems. It is one of the most challenging migration validation activities because it resembles a Data Warehouse (DW) level immutability test, where the impact of data transformations must also be considered. For this reason, before the actual validation phase begins, it is important to review the objectives with business analysts, subject matter experts and business owners. This ensures that the scope, principles and acceptable deviations of the data validation exercise are clearly defined and understood.
It is also important for the customer organisation to take responsibility for this phase of the work. In many respects, it is a form of User Acceptance Testing (UAT) and should not be left solely in the hands of the supplier.
Data validation typically examines not only record counts in the source and target systems, but also expected and unexpected changes at both field and cell level. The fundamental assumption is that data should generally remain unchanged between systems. This does not mean that data is copied exactly as is, because new contracts may be added to the new system that could not be maintained in the old one, or existing contracts may be enriched with new data fields. In addition, some contracts may be migrated to an entirely different system if the new system does not support all the functionality of the old one. All these examples require input from business experts and business owners within the migration project.
The following activities are involved in data validation:
- Agreeing on data usage
- Agreeing on communication models
- Identifying natural keys
- Planning the scope of testing
- Harmonising datasets
- Building or implementing a comparison tool
- Designing and building reporting
- Building automation
One weakness of data validation is that it often requires some duplication of the transformation logic created as part of the migration. Examples of potential transformations are provided in the section Designing the Scope of Testing. For testing to be possible, these transformations must exist both within the migration itself and within a separate process that converts the original values into the intended end result.
However, data validation enables testing at a much more detailed level than the methods described in my previous article. At best, analysis can be performed down to the cell level. Furthermore, database-driven testing allows validation across all contracts or customers, which is almost impossible to achieve using the other approaches discussed.
Agreeing on Data Usage
At some point during most projects, discussions take place regarding where production data may be used and where test data should be used instead. In my experience, final testing has ultimately always been performed using unmasked production data. However, this should never become the default approach, and production data should only be used when there is a justified reason.
It is therefore important to establish at the beginning of the project what data is permitted in different situations, considering factors such as purpose, users and environments.
Designing Communication Models
Delivering a migration is undoubtedly a technical challenge. However, project costs can be significantly influenced by effective communication. Better information sharing would almost always result in substantial savings. Unfortunately, communication issues are often identified too late in the project lifecycle.
In my experience, the following questions have proven valuable in almost every migration project:
- Have business terms been defined?
- Do the right people have access to the data?
- Is the process for requesting, granting and providing access rights clearly documented?
- Are project personnel listed and is the project organisation documented?
- Have effective escalation procedures been established for handling issues?
Avoid the following:
- Maintaining information, such as documentation, in multiple locations
- Creating barriers between working groups or project phases
- Unclear responsibilities, schedules and communication practices
Identifying Natural Keys
In order to compare data, we must identify the values that enable datasets to be linked. This generally involves identifying so-called natural keys within the data.
A natural key consists of universal attributes of an entity that remain unchanged during a system migration and can be used to uniquely identify the information. Good examples of natural keys in customer data are national identity numbers and business identification numbers. For contracts, an example of a natural key would be the contract’s IBAN number.
It is critically important to identify such a link between the source and target data sets. Without it, validation of this type cannot be performed. In practice, this would mean acknowledging that there is no reliable way to determine whether the data has been migrated correctly. This is clearly not a sustainable solution.
Statements of this kind should instead be treated as the starting point for a discussion. They highlight that the challenge is significant and that creative solutions may be required to achieve the desired level of assurance.
In some cases, no single field exists that can reliably link the records. In such situations, a combination of fields, known as a composite key, can be used. The most important requirement is that the information can be reliably identified in both the source and target systems.
Field-level comparison can only be performed for records where we can confidently verify that the contract or business entity is the same in both systems. This can be illustrated using a Venn diagram, where only the overlapping section can be tested.
Designing the Scope of Testing
At the beginning of the project, it is important to determine what should be tested within the database. During migration, data usually undergoes one of the following treatments:
- Data is migrated unchanged into the new system: Examples include account balances at the time of migration, margins and interest rates.
- Data reclassification: In this case, a source system value is transformed according to agreed rules. For example, a value of “No/Yes” in the source system may become “0/1” in the new system. The key point is that the change is straightforward and the meaning remains unchanged despite the different values.
- Data splitting or merging: In this scenario, the number of columns in the source and target systems may differ. However, there is still a well-defined and repeatable logic linking the data.
- Other changes: Data can be combined in ways that are not easy to verify. Systems may store information in fundamentally different formats, requiring more complex logic for testing. Such changes often require functional testing and integration testing in addition to data validation.
These examples do not cover every possible situation but are intended to illustrate the complexity of testing. The business must determine which data is critical and absolutely must be correct, because not all data has equal importance. Migration project teams should actively help business stakeholders and data owners understand the significance of individual fields.
Testing also has a cost impact. While I firmly believe that effective testing always saves time and money, poor planning or uncontrolled execution can lead to project delays, increased costs and reduced migration quality.
Harmonising Datasets
Because banking systems store information in slightly different formats, it is important to harmonise the data before comparisons can be performed. In practice, the most effective approach is usually to transform the source system data so that it matches the classification structure used in the target system. This also provides an opportunity to validate the migration rules.
An alternative approach is to perform comparisons against a harmonised data model. In this scenario, the source data has already been transformed into a harmonised structure. To ensure continuity, the new system is often aligned with the same data model.
This phase can be highly time-consuming, particularly if migration rules are not clearly documented or changes are not communicated effectively across all stakeholders. Equal effort must also be invested in documenting and understanding the data within the new system.
This stage requires a strong understanding of the business context behind the data. To perform it efficiently, team members must possess not only technical expertise but also a deep understanding of the business domain. The quality of the new system’s data model documentation, the usability of the data model and the format of the data itself all have a considerable impact on this work.
By data format, I mean that when information is not available in a straightforward relational structure, additional effort may be required to transform it into a usable form.
Building or Implementing a Comparison Tool
Once the datasets have been harmonised, a tool is required to perform comparisons efficiently. Several database comparison solutions are available on the market. At ALM Partners, we have developed our own Difference Engine tool, which makes it easy to build a database comparison framework and integrate it into test automation.
Designing and Building Reporting
Once the comparisons have been implemented, the next step is to build reporting for the test results. At this stage, it is worth considering the following questions:
- What data can be displayed in reports (for example, identifiers)?
- Who has access to the reports?
- Which findings are important enough to report?
- Where should identified discrepancies be reported?
- How will remediation activities be tracked?
- Who is responsible for implementing the fixes?
Error Analysis
In addition to identifying errors, time should also be invested in analysing their root causes. The more information developers have about an issue, the easier it becomes to determine where in the process the error originated.
The following questions can support the analysis:
- Does the outcome match the migration rules?
- Is there a common factor linking the errors, or do they affect all contracts?
The approach to error analysis should be agreed between the relevant parties, as this can significantly improve project efficiency. Ideally, issues should also be described with code wherever possible, as this makes them much easier to reproduce.
In most cases, however, it is already known what the correct value for a given field should be, or why the issue was reported in the first place.
Reporting Continuity
Migration projects should allocate sufficient time for updating regulatory reporting. Banks today are subject to extensive reporting obligations, and some regulatory reports must be generated daily.
For this reason, it is important to consider how reporting continuity will be maintained during the system upgrade, as well as how other integrations will be safeguarded.
Adequate time must also be reserved for testing reporting processes. It is rare for a migration to occur without introducing some form of change. Sufficient time must therefore be allocated to analysing the impact of those changes.
Conclusion
Migration projects are among the most demanding initiatives in banking. Success requires careful planning, comprehensive testing and a deep understanding of both the legacy and target systems. In this article, I focused particularly on the key stages of data validation, including identifying natural keys, harmonising datasets and utilising comparison tools. I also highlighted the importance of maintaining reporting continuity.
A successful migration is not merely a technical exercise. It requires close collaboration among all stakeholders involved in the migration, together with the ability to analyse and respond to identified discrepancies. Well-designed and well-executed testing is the key to a reliable outcome and, ultimately, the success of the entire project.
Q&A: Data Validation in a Nutshell
1. What does data validation mean in migration projects?
Data validation refers to the detailed comparison of information between the old and new systems. Its purpose is to ensure that data has been migrated correctly and that any changes are expected and approved by the business.
2. Why is traditional migration testing not sufficient on its own?
Traditional testing often confirms whether processes and overall functionality work correctly but does not necessarily reveal errors in individual data elements. Data validation enables analysis down to field and cell level.
3. What is the most important prerequisite for data validation?
The ability to reliably link records between the source and target systems. This requires natural keys or other identifiers that allow the same contract or customer to be recognised in both systems.
4. What are the main benefits of data validation?
Data validation helps identify errors precisely, enables comprehensive verification of critical migrated information and supports root cause analysis. As a result, the cost-effectiveness, quality and reliability of the migration are significantly improved.
