ETL for Financial Data Synchronization

Home
Case Studies
ETL for Financial Data Synchronization

Customer location

Project duration:

1 year (ongoing)

A US-based prime small-business insurance provider contracted Symfa to implement a series of legacy modernization projects.

The complex software enabling work of over 5,000 employees has long been asking for an overhaul. For a year now, the distributed team composed of Symfa's and client’s engineers has been doing monolith decoupling, legacy support, and a bunch of ETL, data management and analytics projects.

The fundamental principles of any ETL project are relevantly simple – the data is extracted from a ‌source (sources), transformed to fit the requirements of a business model and loaded into the target storage. The challenge rises with the complexity of data, and being a global carrier, our client’s sources generate a huge amount of complex data.

The data may differ in format drastically and may need to be extracted from the source for which a standard component does not exist. Such is the Oracle’s ERP case. Oracle ERP returns .csv files which the SQL storage doesn’t welcome. Thus, the ETL processes that we developed include the following steps:

Extract metadata
Extract files
Cleanse and transform the data
Sort out the files in the DWH
Report to the user on the accomplished jobs (the user gets emails which files have been processed in general and which ones contained errors).

Besides the necessity to deal with the millions of rows of complex data, the distributed team was to establish a reliable connection between the Oracle ERP and the client’s ETL tool. The custom data warehouse lacks a standard connector, so the team made a custom connector from scratch. However, to make it a perfect fit for the complex financial reports that the client’s analysts generate, it required a bit of an extra polish during several development iterations that followed to guarantee 100% data integrity, safety and quality. The resulting solution provides a stable connection, data synchronization and data checks so no data is lost or duplicated when it reaches the target.

The Symfa team embarked on the project to streamline the data flow between Oracle ERP and client’s custom made DWH sitting on the SQL server. The ERP connects to the data sources feeding raw data. The data is then extracted and transformed using the ETL tools of the client’s choice – one for the extraction and one for the transformation jobs, given the complexity and volume of the client’s data. The transformed cleansed data is loaded into the data warehouse where financial analysts of the client’s can get it to build the reports using paid/custom made visualization tools or simply Excel.

C#
SQL Server
.NET Core
Visual Basic
Astera Centerprise
.NET
MS Visual Studio
SSIS

With data being at the core of the modern insurance business, ETL projects recently rose to prominence in the client’s company, being the cornerstone of data-driven culture. This ETL project sets a reliable data flow between Oracle ERP and the client’s DWH to supply the data for top-notch analytics.

After a successful onboarding phase, the Symfa team was entrusted with all the extraction jobs. Our engineers leave the data for the client’s team in the data staging area from where they take it to proceed with the transformation (verification, counting, deduplication) and load parts of the ETL process. Both teams have their own areas of responsibility and use their respective data management tools. Such a cooperation model in no way isolates the team members – both teams enable an organic data flow and provide each other with extra support whenever it is needed. The ready-to-go data is then sorted out and stored in the data warehouse where data analysts of the client’s may get it to create custom financial reports. Thus, the engineers established a strong custom connection to synchronize high importance financial data.

All the workflows and communication patterns on the project were established according to the client’s requirements.
All the engineers are actively involved in the project discussions during daily catch ups in Microsoft Teams.
24/7 access to Jira and progress reports (daily/weekly/monthly) add to the project transparency, which is a must for a business operating in a strictly regulated environment, like the one of our client’s.