Lessons in Data Validation and Model Redesign During Migration

Sandro Palleschi, Director of Enterprise Data Services at Empire Life, shares how meticulous planning, incremental rollouts, and rigorous testing ensured a seamless transition away from Informatica.

‍

Sandro oversaw several migrations away from Informatica, each marked by unique challenges and opportunities for modernization. One project required a complete redesign of the existing data model, presenting both technical complexities and opportunities for improvement.

The legacy model had become outdated and inefficient, prompting the team to rebuild it from the ground up to enhance functionality and resolve persistent data quality issues. This redesign introduced significant complexities, as it involved mapping data between vastly different schemas.

"It wasn’t apples to apples anymore," Sandro explains. The team faced the challenge of retrofitting the new system to ensure it could interpret and return data results consistent with those of the legacy platform. Each adaptation required careful consideration to preserve the integrity and functionality of the data.

With meticulous planning, custom validation tools, and an application-by-application migration strategy, Sandro’s approach provides a roadmap for tackling even the most complex data migrations.

Validating data parity when the schemas change

The migration was seen as an opportunity to address fundamental inefficiencies in the legacy data model. Initially designed over a decade ago, the model for Master Data Management (MDM) had grown outdated, prompting Sandro’s team to rebuild it from the ground up. This redesign streamlined key data entities such as individuals, organizations, and contracts while resolving persistent data quality issues.

Mapping the legacy system’s complex relationships into a new framework presented significant technical challenges. With the schema changes, direct comparisons between the old and new systems were no longer feasible. The team had to ensure consistent results despite differences in how the data was structured and represented.

Datafold: Since it wasn’t apples-to-apples, what kind of testing strategies were you using to validate the mappings?

Sandro: We compared extracts from both platforms during the cycles. For example, we would compare the data going into the legacy Informatica system and see that it was the same as the data going into the new platform.

Datafold: Were you looking at counts or aggregates?

Sandro: Actual data comparisons, down to the row-level. The challenge was, because it was a data model change, trying to compare the data in one format to the data in the other format wasn’t straightforward. We had to really think that through to ensure the results at the end of the day were the same, even though they were represented differently across the two data models. That was a challenge.

And although we hadn’t initially planned to make a lot of changes, we incorporated many improvements into the new data model. So not only was it just about what we had, we improved a whole bunch of the processes and ETL workflows and the way the data was represented to fix the data quality issues we were having with Informatica. We tried to tackle as much of that as we could during the migration.

Ensuring data validation wasn’t just a technical challenge—it was also a rigorous exercise in accountability. From an audit perspective, the team needed to demonstrate that the migration preserved the accuracy and integrity of relationships within the data. This required extensive documentation, frequent collaboration with auditors, and careful validation to ensure that every profile, relationship, and entity in the legacy system was faithfully represented in the new platform.

Datafold: Was the audit process necessary because this was healthcare data? Or what type of data was this?

Sandro: This was our party data—customer data, advisor data, all of our policies. Since it was PII, we had to validate it with 100% certainty. The auditors were involved right from the beginning. They were coming to our meetings and stand-ups on a daily basis, and they would stay on top of what we were doing and what they wanted to see in terms of validation. Which really helped because a lot of the time, they would come in after the fact and ask for all the stuff, and it takes a long time to put it together. This way, we were able to build it for them as we were going through the project. 

Datafold: You’ve already mentioned data validation and auditing as challenges. Was there anything else unexpected that came up?

Sandro: The original data model exercise took a while, because if you look at the way the ETL process worked, there were a lot of ETL mappings that the team wasn’t familiar with, so we had to relearn a lot as the platform ran on Informatica. 

That was challenging because we’d encounter issues with why things worked a certain way in Informatica, realize it was wrong, fix it on one end, and then validate that what we did was correct. This made the validation process much more complicated. Validation, as an umbrella across the entire process, was definitely the biggest challenge.

Balancing performance and scope

Migration projects often stumble not because of technical complexity, but due to overlooked details that undermine their success. According to Sandro, the key to avoiding pitfalls lies in preparation and precision: test cases aren’t just a step—they’re the foundation of success.

Teams frequently underestimate the importance of rigorous testing, performance validation, and detailed planning. Sandro highlighted the critical need for a methodical approach that doesn’t just focus on the finish line but ensures every step along the way is solid.

Datafold: Speaking more broadly now, what steps do you think technical teams tend to overlook when they’re doing a migration?

Sandro: I would say test cases. We spent a lot of time making sure that we had the right test cases to prove the migration would be successful. So we documented all our internal use cases. Then there were the test cases that each of the applications wanted to test. That’s something you should put a lot of work into—making sure you’ve accounted for everything from a testing perspective to prove to yourself that it’s working.

I think performance sometimes gets overlooked too. So we built performance testing into the use cases to make sure that we could do volume testing and confirm that the migration to the new platform wouldn’t negatively impact performance.

This rigorous approach tied seamlessly into their application-by-application migration strategy, which minimized risks and allowed for early issue detection. 

Datafold: Did you have to think about rollback plans?

Sandro: Absolutely. When we moved into production, the good thing about going application by application is that if there were problems, we wouldn’t cut that application over. We’d continue running the existing system in parallel.

While we were doing the parallel runs, if we encountered an issue at the application layer, we might have had to make a change and test that again internally to ensure the weekly or daily cycles were still running correctly. Sometimes we had to go back and make some changes. Because we went application by application, we didn’t have to migrate all the data in one big bang. We did it in stages, in logical or conceptual groups

Doing it that way minimized the impact on our consumers. But the longer the migration or the longer the parallel runs, the more it impacts the business, because there was a code freeze. So there were things the business wanted to do that we had to hold off on until we finished.

For example, once we got the data we did the cut over and started running in parallel. Any net new functionality coming after that—there were some applications we could point directly to the new platform without requiring conversion or migration. 

Setting the stage for successful delivery

Embarking on a migration project, especially for the first time, can be overwhelming. As Sandro notes, success hinges on making deliberate choices upfront and documenting every detail.

Datafold: Looking back, do you have any advice for someone embarking on their very first migration project? Things they should take note of?

Sandro: Inventory. Make sure you inventory all the consumers of the data. Make sure you have a liaison to work with as you go through testing, validation, and defining the scope of the migration. Is it a lift and shift, or are you going to spend a lot of time improving what was there before? You have to make those decisions upfront. But inventory everything—that’s important. 

Know your domain, know exactly what you need to change, who the consumers are, and what the SLAs are. Have all that documented, and that becomes your scope for the migration.

The more consumers you have, the harder it is. The more you decide to improve as you go, the more work it’s going to be and the harder it will be to validate.

By focusing on thorough test cases, performance benchmarks, and application-by-application rollouts, Sandro’s migration strategy not only minimized risks but also allowed for early issue detection and incremental progress. With a commitment to maintaining business continuity through parallel systems and well-documented processes, his team delivered a seamless migration while setting a stronger foundation for the future.

Datafold: What else was critical to the project’s success?

Sandro: Communication. Set up a solid communication plan with stakeholders, including data consumers. They need to know where the project stands, when they’ll be asked to test, and how they can contribute. Create a detailed runbook to guide them through the transition and have a backup plan in case a rollback is necessary