FinOps Optimization Drives 77% BigQuery Cost Reduction for E-commerce Client
Applying FinOps strategies to optimize BigQuery processes, our client drastically reduced their costs.
.png?width=500&height=278&name=Customer%20Cases%20image%20(22).png)
within 20 days
After 1 month
The Goal
Our client, an e-commerce business, faced rising cloud costs that threatened to undermine their data analytics investments. They needed a smart and sustainable approach to financial management within their Google Cloud environment.
By adopting FinOps principles, the goal was clear: optimize BigQuery storage and query expenses while maintaining data-driven insights and performance.
The Challenge
Our customer was experiencing a significant cost increase due to inefficient use of BigQuery (BQ), resulting from processes implemented by another company.
Specifically, several processes were running on an hourly basis with the goal of updating and inserting data. These processes retrieved data from BigQuery enriched it and then transmitted the enriched data to another location. However, as the tables grew in size, these processes led to several challenges:
- High Frequency of Updates: The processes were running too frequently, performing hourly updates and inserts that significantly increased both storage and query costs.
- Inefficient Data Retrieval: The processes retrieved more data than necessary, resulting in excessive data scanning and unnecessary expenses.
- Scaling Issues: As the tables grew larger, the inefficiencies of these processes became more pronounced, causing costs to rise sharply.
- Frequent Data Updates: The client often made individual updates, which contributed to high query costs in BigQuery, a platform optimized for large-scale analysis rather than frequent small updates.
The client needed to investigate and address these issues to reduce costs while still maintaining the functionality of their processes.
Start Your Success Story with Crystalloids
Partner with Crystalloids and Google Cloud to bring your project to life.
The Solution
By applying FinOps strategies, we implemented several cost-saving measures based on FinOps principles:
- Optimizing Row-Level Updates with Batch Processing:
A major contributor to high BigQuery costs was the frequent updating of individual rows. Each update increased the amount of data processed, as we were updating records for each row processed. While BigQuery excels at querying and analyzing large datasets, it is not designed for frequent small updates. Since BigQuery’s pricing model is based on the amount of data processed, these frequent update queries resulted in elevated costs.
To address this issue, we identified the update mechanism as an area for improvement. We recommended consolidating these updates using a caching mechanism. Rather than performing individual updates for each row, we implemented a local cache and executed a single batch update at the end of each process. This change significantly reduced the number of individual updates, minimizing associated costs and enhancing overall query efficiency. - Selective Field Usage for Query Optimization:
The SQL queries used in the client’s processes were retrieving all fields (using *), which led to unnecessary data scanning and inflated costs. To rectify this problem, we analyzed the specific fields required for each process and modified the queries to select only the necessary fields. By narrowing the selection to essential fields, we drastically decreased the amount of data scanned, reducing it from gigabytes to megabytes. This optimization resulted in a significant decrease in data processing costs. - Partitioning Data Tables for Efficiency:
Another key optimization involved the client’s data tables. Without partitioning, BigQuery scans the entire table during each query execution, increasing costs as the table size grows. We recommended partitioning the tables based on logical divisions, such as time intervals or product categories. This strategy ensured that BigQuery only scanned relevant partitions, significantly reducing the amount of data processed and, consequently, the associated costs.
The Result
These FinOps-driven changes delivered outstanding results:
- A 38% reduction in BigQuery costs within just 20 days.
- After one month, the client achieved a 77% cost reduction compared to their previous BigQuery expenses.
By strategically applying FinOps principles, we helped the client save on cloud costs. We empowered them to better manage their cloud spend and maintain the performance of their data analytics operations.
