The Client is a privately held exploration and production company and is one of the largest private operators in the United States. The company’s Energy Services division provides construction, roustabout, trucking, vehicle maintenance, and well and wireline services, making it vertically integrated.
The Client has had issues with artificial lift equipment failure and would like the ability to auto detect trending conditions and pre-emptively prevent failures. Alerts need to be setup to notify the appropriate stakeholders in the company of negative trends. Also, we need to identify preventative maintenance to avert any failure and reduce cost. Finally, we needed a way to log, audit, and measure many points within the ingestion pipeline. The technology stack available to us was AWS so the pipeline needed to be built using native AWS tools.
iOLAP partnered with Endeavor team to document and identify the areas of impact. These potential failure points will be evaluated, and recommendations will be provided. Collaborative effort will be made to use the data source to predict failures. Part of the solution will be to create data experiment cycles and evaluations.
The lakehouse architecture comprised of three primary components.
S3 was leveraged as the data lake. Data is partitioned by day and stored in parquet format. Data stored here is close to raw format.
The ingestion pipeline consists of 3 phases. The first phase handles data on the lake, followed by sending the data to the Snowflake in a staging area, and finally the data is loaded to an EDW.
Data lake ingestion starts when Endeavor uses Boomi to send data from on-premises to the S3 data lake's inbound folder. This triggers a lambda python process that does the following:
The landing of the parquet file will trigger the second lambda process. This process:
The warehouse is built on top of Snowflake. We organized Snowflake into 4 databases:
The EDW table loads are driven by batch jobs. These batch jobs kick off Glue jobs which in turn invoke Snowflake SQL/ELT files that are stored on S3. A Postgres metadata lookup occurs for each EDW table that is loaded which can tell the job which Snowflake warehouse (compute) to use.
Systems shall utilize IOLAP’s best practices for automation, logging, stability, and recoverability. The benefit of risk assessment is to properly identify the potential failure points and cut cost by addressing the risk before it becomes an issue. Improved maintenance cycles and risk plans in place will save Endeavor time and money. We learned to build, manage, and maintain a near real-time ingestion engine between multiple technologies. Snowflake and AWS best practices were applied from the get-go, and we learned a lot on how triggering lambda jobs by s3 events work.