Achieved Data Ecosystem Modernization for a US Banking Client by Migrating their Data Warehouse from SQL to Cloud

THE CLIENT

The client is a dynamic and forward-thinking mid-tier bank in the US, committed to delivering exceptional financial solutions and customer service. With a focus on innovation and agility, this bank is driving growth and success in a highly competitive industry.

THE CHALLENGE / REQUIREMENT

The client faced significant challenges related to their data ecosystem, including a high total cost of ownership and exponential data growth in their data warehouse. With data doubling every two years at a rate of 100+ TB, controlling and managing the data growth in their SQL server-based data warehouse was extremely challenging.

To compound these challenges, the client also experienced performance issues in the consumption layer due to the data volume and increased consumption/data usage patterns. The data sharing process was difficult, and the client needed a simplified development process to manage their complex data ecosystem effectively.

THE SOLUTION

We began by migrating the existing data warehouse on SQL server to Snowflake on AWS, leveraging our expertise in cloud migration to streamline workflows and enhance performance. We retrofitted the ETL/consumption layer to consume data from the modernized data store, ensuring that the client's data ecosystem was up-to-date and efficient.

We took a phased approach to ensure success and minimize disruption to the clients' business operations and successfully completed a migration project in three phases over a 2-year roadmap for the client.

In the first phase, we lifted and shifted the data and schema/data model, keeping the data in sync with incremental daily data. We also translated the schema, ensuring that both legacy DW and Snowflake continued to exist. During this phase, the business continued to consume from the legacy DWH, but Snowflake had up-to-date data, ensuring that there was no disruption to the client's operations.
In the second phase, we modified the ETL layer in Informatica to process data from the source system directly, enhancing workflows and performance.
Finally, in the third phase, we retrofitted the consumption layer for the business to consume data from Snowflake, ensuring that the client's data ecosystem was up-to-date and efficient.

Multiple tools were used to bring automation and accuracy through this process :

A fully automated tool for data migration and data validation.
Tool for database query (SQL) conversion
Mphasis tool for mass conversion of Informatica ETL, which reduced conversion build time by over 60%

BUSINESS BENEFITS

Polyjuice ETL retrofitting tool has led to more than 60% reduction in conversion-built effort.

Custom tools developed for SQL translation and data migration, resulted in more than 35% reduction in effort. This helped to streamline workflows, reduce complexity, and allocate resources to areas that drive business growth.

Complete regression test automation framework has led to more than a 40% reduction in effort, ensuring that the clients' data ecosystems are efficient, up-to-date, and reliable.