The Power of Optimization: redefining Snowflake ETL for cost saving and performance improvement

A company specialized in digital content security and monetization, with a focus on the media and entertainment industries. It provides solutions and services to protect digital assets and ensure secure content delivery across diverse networks and devices.

Use cases
  • Content Protection
  • Content Monetization
  • Multiscreen Content Distribution
  • Analytics & Data Security

Challenge

The team faced a double challenge in the Snowflake project. In the first challenge, they needed to increase the frequency of running the ETL process to achieve faster results. While it was estimated that increasing this frequency would lead to increased costs, the reality surpassed the estimate over the months, and costs increased more than anticipated. Therefore, the team was faced with an even greater challenge: reducing the costs associated with the ETL process in Snowflake.

The ETL process involves different components such as databases, lambda execution, EC2 scripting, step functions, data loading and transformation, and Looker visualization. Power Quangers team had to analyze and identify areas for improvement in each of these components and find effective solutions to reduce costs. However, it was critical that these improvements did not compromise the quality, efficiency, and the ability of the ETL process to provide useful and accurate information to the customer.

Process

To address the challenge, the team followed a structured process within a tight timeframe of two months. They conducted an analysis of the components involved to identify areas with high costs. Effective solutions were proposed, including query optimization, table partitioning, historical data cleansing, and process optimization.

PQ, prioritized and planned the gradual implementation of improvements, focusing on achieving tangible results within the given time. They implemented changes in small parts of the system, testing and evaluating the impact on cost and performance. This included specific query optimizations, selective data scanning in S3, and optimizing process execution.

To ensure effectiveness, the team established an ongoing monitoring process using tools like CloudWatch. This allowed them to track relevant metrics, receive performance and cost alerts, and make additional adjustments as needed.

Constant communication with the client-side team, was maintained throughout the process. Regular meetings facilitated progress updates, sharing of results and lessons learned, and ensured alignment with project objectives while leveraging valuable insights from the client team.

Solution

To reduce costs and enhance efficiency, the team focused on optimizing the data ingestion process to Snowflake. They refined queries executed from EC2 instances, automated data cleansing, and removed unnecessary historical data based on customer agreements. By optimizing the ETL process to run only when necessary, they minimized resource usage while ensuring data accuracy.

Results

As a result of the implemented improvements, we achieved substantial cost savings amounting to tens of thousands of dollars per year. Exceeding expectations, the team achieved an impressive 50% cost reduction and optimized the ETL process by 40-50%. To achieve this process of reaching a duration of less than 10 minutes, we successfully reduced the time to 7-8 minutes from the initial 14 minutes.

Team

The team working on this project is named Power Quangers, a dedicated video-team composed of a Team Leader and video developers.
They are supported by an extended team of Qualabs that helps them in several areas: Delivery, Technology, Client satisfaction and People care.

Maximiliano
Camilo
Juan Manuel
Facundo
Martina

Extended Team

Delivery Manager: ensures value delivery together with the team, in addition to collaborating with continuous improvement processes.

Tech Manager: in charge of designing, describing and managing the engineering of solutions in relation to specific customer problems.

Client Manager: ensures a better understanding of the client's needs and challenges. Always aligned with the client and the Qualabs culture.

People Ops Manager: accompanies the team in retrospectives and internal team meetings. Assists in the preparation of feedback and encourages team building and cultural alignment.

Want to know more?

TALK TO OUR SPECIALISTS!
  • Email: info@qualabs.com
  • Phone: +598 2708 5252
  • Address: Av. Brasil 2371, Montevideo, Uruguay

©2024. All rights reserved