ETL Development for Financial Data Synchronization

A custom connector to bridge Oracle ERP and client’s DWH and enable insightful reporting

Client and Business Goals

A US-based prime small-business insurance provider contracted AIS Novations 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 the AIS and client’s engineers has been doing monolith decoupling, legacy support, and a bunch of ETL, data management and analytics projects.


The AIS 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.


The fundamental principles of any ETL project are pretty 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.


  • .NET Core
  • C#
  • SQL
  • Visual Basic
  • .NET
  • MS Visual Studio
  • SQL Server
  • Astera Centerprise
AIS Team
4Software Developers
1Project Manager
Customer location: USA
Project duration: 1 year (ongoing)


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.

alt image

After a successful onboarding phase, the AIS 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.

Other projects