ETL Remodeling for a Fortune 500 Insurance Player

Establishing new ETL processes as a part of a comprehensive digital transformation campaign

Client and Business Goals

The client is a niche specialty property and casualty insurance company operating globally. Recently, the giant with nearly 6,000 employees embarked on a series of IT projects in a pursuit of digital transformation of their entire business.

As the insurance sector is embracing data technologies to create new valuable offers meeting the demands of time for their customers, it’s only logical our client started from an ETL project. They approached AIS Novations because of our robust ETL expertise lying at the core of any data-heavy endeavor.

The project objective was to improve and optimize data processing workflows in the Fortune 500 company with offices all over the globe, generating dozens of millions of rows on a monthly basis.

Why do businesses need ETL software in the first place? Any company that uses an ERP system at some point understands that the data within their ERP is a total mess. There are two main reasons that cause it:

- Accidental errors during data input, transfer or occurring because of the bugs.
- Different data formats in the adjacent IT systems.

Even with the first cause eliminated (which is possible in theory, but rarely in practice), the second one will persist. Controlled differences in data formats and structure technically aren’t an error, but they do cause businesses problems.

Thus, our team had two key tasks for this project:

1. Align all the data to the required format (a single system of values and data break-down), at this ensuring data quality and reliability.
2. Provide an audit trail for the data transformation. Thus, data users can see how the target data came into being by tracing it back to the original state, if needed.

Solution

The project implies five major steps that a joint team of the client’s and AIS talents are implementing:

Data Extraction — the software extracts data of varying quality from multiple sources.
Data Validation — the extracted data is checked for correctness and fullness.
Data Mapping — the validated data is mapped against the target model.
Data Aggregation — this step is required because of the difference in data break-down in OLTP and OLAP systems. To speed up the process, the OLTP part could be omitted, but then the audit trail would have been lost. The audit trail in its turn is required to build a Drill-down report which shows which lines of the OLTP system made the OLAP cell.
Data Load — the data is loaded into the target system (database or data warehouse).

Challenge

Working in partnership with a globally acclaimed insurance corporation required going an extra mile to gain deep domain knowledge during the onboarding phase. Our team also had to rethink our data processing approaches to fit the needs of the client’s high-load data systems.

The AIS team showed great work ethics during a demanding onboarding phase. In less than two months, we became a natural extension of the client’s team, strictly following the established processes and guidelines.
Founded about a quarter of a century ago, the insurance giant has grown a massive client base, which meant for us a vast amount of data to be processed. With over 90 mln rows loaded monthly, few commercial solutions could satisfy the demands of the project. The joint team came up with a custom software that perfectly meets the high performance and scalability requirements of the corporate business.

Technologies

.NET Core
.NET
C#
SQL

Visual Basic
Astera Centerprise
Azure DevOps

AIS Team

2Software Developers

1Team Lead

1Project Manager

Customer location: USA

Project duration: 2+ years (ongoing)

Results

After a successful probation period, the two development teams became a single efficient whole tackling strategic digitalization tasks. AIS Novations takes pride in its neatly organized workflows, which our client appreciates. Our talents work as a dedicated team, delivering in strict compliance with the client’s requirements. All the infrastructure is on the client’s side.

Besides great technical skills, our talents demonstrated commitment to result, delivering on or even ahead of schedule and being active participants of daily meetups in MS Teams. All AIS team players are fluent in English (B2 or higher), with English being the only language of the project communication.