Metadata Driven Data Migration on Azure

SITUATION

Our client was migrating from on-premise database (SQL and DB2) solutions to the cloud using Azure. The migration project presented a complex challenge as it required the implementation of enhancements and data governance. Additionally, the migration involved moving fact and dimension tables with consistent rules, utilizing SSIS packages as the source, while developing the solution on Azure services. Performance needed to be enhanced to comply with delivery times for data analytics and data science teams as well as to use less memory, thus saving budget. They weren’t making the necessary progress quickly enough. They turned to their trusted partner, Apex, to get them on track with an efficient roadmap and implementation to project completion.

Apex created a plan using an Agile approach. First, we implemented a dynamic metadata solution around the new Azure data factory. For user-friendly and secure ingestions, we implemented PowerApps. SQL scripts were used for a dynamic metadata creation processes. Apex completed the SSIS ETL migration to ETL Data Factory pipelines and data flows, changing the data architecture pattern along the way. Once completed, we created documentation diagrams for ETL developments. Then, our team provided insights into the potential benefits of implementing Databricks and Synapse for key engineering workloads. Testing new optimization techniques, including cluster dimensioning, allowed our client to generate total cost of ownership (TCO) estimates and asses potential cost and performance improvements.

RESULT

Apex provided solutions that were easily replicated which simplified and reduced cost for the client. The team quickly responded to client needs and implemented new approaches which enhanced solution value. We received high praise from teams, such as sales, for our work with PowerApps. Improved data governance made it much easier to maintain. Solutions implemented for existing dataflow and pipeline performance dramatically reduced the average weekly pipeline time on one team from 48 hours to 17 hours. This allowed the team to not need to work after hours running deployment pipelines for the first time in four months. Migrating regular dataflows to metadata-driven solutions and implementing solutions on Synapse and Databricks further improved performance for the client.