Secure and Efficient: Transforming Oil & Gas Operations with Databricks

Client

The client is an upstream oil and gas company with operations across the United States, primarily focused on extracting hydrocarbons from wells.

Challenges

• To reduce end-to-end data processing time, which was nine hours when the client approached us. 
• To access the data through reporting tools and spreadsheets for downstream consumption. 
• Accessibility to all the end users who receive access at appropriate security levels. 
• Data models to create 58 self-service reports.

LTM Solution

• Created data model in line with the benchmark set by Databricks and PBI for Azure cloud migration with scalable design.
• Automated end-to-end ETL flow with detailed audit log and parallel execution to reduce overall execution time. 
• Reduction of fact table size from 18gb to 6gb by vertically partitioning into fact and lookup tables for faster load to insert full form (PBI). 
• Laid the foundation for strategic financial system making it scalable for future on-prem systems onboarded to cloud. 
• Created reconciliation script, which generates automated report of all the KPIs on report validation. 
• More accuracy of data as compared to on-prem system. Additionally, more data issues were identified and fixed. 
• Simplified and enhanced security model to provide row level security basis Azure AD.

Business Benefits

• Overall processing time: 300% reduction from 9 hours to less than 3 hours for a full load and <1.5 hours for an incremental load.
• Fact table size: 300% reduction from 18GB to 6GB by vertical partitioning.
• Security No dependency on script to grant/revoke use access. It can be done through a simple CSV mapping sheet.
• Connectivity: 58-self-service spreadsheet reports and 2 PBI dashboards are connected seamlessly.