Migrating from On-Premise Relational Databases to Snowflake Cloud Data Warehouse

Problem

The client wanted to move from traditional databases to a cloud environment for better performance and maintenance. They required migration strategy and implementation expertise to migrate traditional databases like Oracle, Postgres, SQL server, and File system to Snowflake and rebuild the ETL pipeline for Analytics reporting.

The Before State

• The client faced challenges in analyzing data from traditional databases and loading it into Snowflake using Airflow and Astronomer due to the complexity of various database source systems
• The existing ODS and EDW architectures were found to be ineffective, leading to delays in the ETL pipeline
• Identified the need to replace the existing ODI 12c integration tool with DBT, leveraging Snowflake best practices
• There were also challenges in defining dependencies for ETL jobs in Astronomer and ensuring that all required stage tables execute first. Despite executing the migration process and regression test scenarios on time, the client faced difficulties achieving their milestone due to these challenges

What KPI Delivered

• KPI Partners analyzed the data in the traditional databases and profiled the data to come up with several patterns for various database source systems to load the data into Snowflake using Airflow and Astronomer

• KPI analyzed the existing ODS and EDW architectures and rebuilt the ETL pipeline in DBT by replacing the existing ODI 12c integration tool, leveraging Snowflake best practices

• Defined dependencies for ETL jobs in Astronomer so that all the required stage tables will execute first to get data from the source and trigger the DBT jobs

• The migration process and regression test scenarios were executed on time, and the client was able to achieve their milestone 

The After State

• Processed legacy historical data of 20-plus years to Snowflake

• Designed and developed the warehouse data model to meet the business reporting requirements

• Created several MART layers for SALES, E2OPEN, and New Product Development (NPD) such as Project Summary, Project task, Project cost, Project Risk, and Project Schedule

• Curated and enabled Snowflake data lake in the customer's database which was used by their Data Science Team and downstream Team