01
Business needs
Pelmorex Corp., a Canadian weather information and media company wanted to optimize its existing AWS NetSuite pipelines to ensure daily data availability, facilitate seamless integration of new sources, and enhance operational efficiency.
To address these objectives, Pelmorex was looking for a partner who would help them:
- Improve the efficiency of ETL processing
- Achieve better resource utilization by optimizing AWS cluster configuration and storage class
- Optimize costs for AWS resources by efficiently processing AWS Glue jobs
- Meet compliance with organizational security policies
- Ensure data security and disaster recovery
69% reduction in lines of code, improving code maintainability
02
Solution
After a deep analysis of the existing NetSuite jobs, the Impetus team refactored the codebase into a modular structure, significantly reduced the lines of code, reconfigured the cluster size, stored curated data into an optimized form, and optimized the complete pipeline.
The existing solution involved the following steps:
- Ingesting NetSuite API data into a raw Amazon S3 bucket
- Transforming data using AWS Glue jobs
- Loading transformed data into publishing folders (all files in one bucket) in Amazon S3
- Ingesting transformed data (in the data frame) into Snowflake
- Executing procedures within Snowflake
- Accessing data through PowerBI
32% reduction in execution time, enhancing efficiency
Solution highlights
The team prioritized infrastructure security, network configuration, and collaboration with business teams throughout the implementation process to ensure seamless integration and minimal downtime, with the following highlights:
- Created date-wise Amazon S3 folder to store curated data in Parquet file format for efficient Amazon S3 storage backup
- Applied modular approach on AWS Glue job to achieve better efficiency
- Stored data in an Amazon S3 folder using partitioning whenever necessary and ingested the partitioned data into Snowflake to enhance the performance
- Ingested the curated data from Amazon S3 published bucket (Parquet format) into Snowflake table to achieve better performance
- Applied versioning to the Amazon S3 bucket to achieve disaster recovery
- Used AWS Secrets Manager and Amazon S3 bucket versioning to ensure data security
- Optimized cluster configurations by reducing worker nodes from 13 to 4 and DPUs from 26 to 8 to achieve cost optimization and better performance
- Eliminated obsolete AWS Glue jobs and Snowflake tables to achieve cost optimization
- Integrated AWS services: EC2 for job scheduling, Lambda and Code Commit for CI/CD
- Utilized Snowflake for data warehousing, enabling ETL operations and PowerBI reporting
AWS Technologies used:
AWS Glue, Amazon S3, SNS, Lambda function, Snowflake, Amazon EC2, AWS CodeCommit, AWS Identity and Access Management, AWS Secrets Managers, Amazon CloudWatch
03
Impact
The optimized solution enabled Pelmorex to achieve significant improvements across its data pipeline, resulting in enhanced operational efficiency and substantial benefits. Other benefits include:
- 32% reduction in execution time (from 35 to 24 minutes), enhancing efficiency
- 79% cost reduction on a quarterly basis
- Refactored the code and improved the code maintainability by reducing lines of code by 69% (8200 to 2500 lines), thereby ensuring faster and more efficient processing
- Modularized the entire AWS Glue job for scalability and maintenance
- Ensured a backup for potential data restoration by systematically storing curated data into date-based folders within an Amazon S3 bucket daily, while concurrently pushing it to Snowflake tables
