Energy Infrastructure Company Benefits from Azure Data Lakehouse

For over 70 years, our client has served the natural gas industry with a focus on midstream natural gas compression. They are a leading provider of natural gas compression services to customers in the oil and natural gas industry throughout the U.S., and a leading supplier of aftermarket services to customers that own natural gas compression equipment in the U.S.

Our client was working on several strategic initiatives that focused on transforming their data and analytics approach and infrastructure. They were challenged with delivering targeted analytics to a broad internal audience. Their existing legacy data and analytics platform included data from Oracle Cloud Applications for Finance, Projects, Fixed Assets, Procurement, Order Management and Inventory, however it was inefficient and did not provide easy access to timely, accurate data for the end users. The inefficiency of their existing legacy platform was having a broad impact at a critical time in the company’s analytics evolution. They knew they needed to make a change to support the growth of the company and to drive efficiency, scalability, and cost savings.

The Apps’ team used best practices to design, develop and deploy an Azure Synapse data lake and warehouse solution using medallion architecture (i.e,. Bronze, Silver and Gold Layers). We worked with the client to implement advanced data lakehouse techniques including the use of use of Parquet and Delta files and Azure Synapse Serverless (where data remains in object storage, but a relational overlay is placed on top for transactional consistency and to present a SQL-compatible interface). From there we developed ETL/ELT processes to extract data from Cloud Fusion ERP and Dynamics 365 into the Bronze Layer of Azure in Azure Data Lake Storage Gen 2 (ADLS Gen2) and kept the extracted data in original form for future reference. We then developed data pipelines to load the data into the Azure Data Lakehouse Silver and Gold Layers and to transform the data into the target star schema tables. The data pipelines supported both the initial load of historical data as well as ongoing daily incremental loads. We automated processes, designed and developed intermediate views where necessary and optimized all tables and views for performance – all while maintaining the accuracy of the data.

The solution is efficient and scalable to process additional data files and configurable to load full or incremental or on-demand loads of all or an explicit set of objects.

Apps Associates created technical design documentation describing the architecture, configuration, and design for the solution along with production operational documentation and conducted knowledge transfers session with the appropriate teams.

We worked together with client to optimize existing queries and organize by intermediate views, abstract views, and final views to achieve optimal performance. The solution is contributing to performance improvement by 400% and is able to reconcile the data exactly to their legacy system.