...

Cloud data migration for
the ultimate reliability

Insurance

Migrating to Snowflake and dbt to ensure reliability for scalable solutions in the modern insurance sector.

migration case study Arch Insurance challenge

Our client delivers specialised risk solutions globally, spanning diverse industries.

Infinite Lambda helped them migrate from a legacy data platform to a modern, cloud-based system that offers the capabilities to enable them to keep scaling their business. Moreover, the solution we built adhered to the highest auditability and security standards that ensure full compliance of the strictly regulated insurance industry.

What we did

SQL Server to dbt migration
Source tables to Snowflake migration
Automated data validation
Metadata enrichment

The Challenge

The client had a legacy data platform they needed to modernise to enable them to assess risks better and price more accurately.

They relied on data from a variety of systems for their core reporting, some of which had Change Data Capture (CDC) with high refresh frequency, which would produce different results when running the same query again within a few minutes.

They often had to wait 48 hours between data creation and that data being available for use. This was highly detrimental as the data was used across all departments for essential business operations, such as calculating insurance pricing, contract renewal, invoicing and more.

The CDC processes were challenging to manage with insufficient testing capabilities, and broken pipelines often took the data teams several days to fix, during which time the data was not available to the business. It had become critical with the data often being unavailable, causing downtime for key business operations.

The solution

Migrating an insurance client to Snowflake

The strategy

We strategised and implemented a data platform migration. Employing an Agile delivery approach, we combined the planning and the implementation phases, which took place in parallel.

There were 9 source systems in total and the client’s data team had already started putting together a roadmap of which data sources they wanted to migrate first.

Resolving dependencies

There were dependencies within each source system, with one model depending on another. Infinite Lambda’s engineers mapped these out and created a roadmap that would allow us to work our way through the models.

Data validation on dbt

A crucial step in the migration delivery was validating the data to ensure success. We would migrate a model and compare the outcome we would get on SQL Server versus dbt.

Lifting and shifting was not an efficient approach in our client’s case because some of the stored procedures we were going to migrate involved modifications, such as deleting data from tables.

We had built dbt macros to compare and we would run the checks daily to make sure it was all matched. This approach was highly efficient as it allowed us to spot issues and fix them right away.

Metadata enrichment

The client’s legacy SQL server was still in use and we applied a robust approach to data validation, comparing the data in the old system with the new one.

Here, we leveraged a package with custom functions and saved the results in Snowflake. Ultimately, as there was a huge volume of models to compare, we automated the process.

To make the models more performant, we created intermediary models to optimise the data transformations and designed the data model for maximum efficiency.

The new layers we introduced made the code much cleaner and better organised. This made it quicker and more efficient to build new models or change the existing ones as needed, saving development time and shortening the time to delivery.

Knowledge transfer

We provided a series of training sessions to transfer all necessary knowledge and ensure the team could start leveraging the new platform self-sufficiently.

After the platform handover, we continued to provide some ad hoc support and guidance to ensure they were set up for success.

The technology we useD

Migration case study with Arch Insurance
The Result

A reliable, performant cloud data platform

We built a robust data platform for our insurance client that they could fully rely on.

With the new solution:

  • The data is always fresh
  • Downtime was eliminated
  • Potential issues can quickly be identified and fixed

 

We reduced the time it took to fix an issue from two days to a couple of hours, which would unblock critical business processes for the client.

Infinite Lambda handed over a system that would offer all of the benefits of cloud data technology to empower the client to keep scaling their business, leveraging the most cutting-edge data solutions.

don’t wait

Let’s walk the walk together

We are a generation of engineers and technologists who are passionate about transforming organisations with digital-age solutions and seeing them thrive on the cloud.

see Related Stories

We have helped over 50 organisations to deliver projects at different scales with over £100m in ROI.

Telecommunications
Cloud Data Transformation in Telecommunications
Media
Data Platform Migration for Enhanced Reporting and Auditability
World Health Organisation
Carbon Analytics Platform for a Vehicle Fleet
The Francis Crick Institute
Scalable Global Trusted Research Environment
Railsr
Robust Data Platform for a Digital Bank
Oddbox
Building a Single Source of Truth

We have helped over 50 organisations to deliver projects at different scales with over £100m in ROI.