GCP – Optimizing BigQuery computational analysis costs

BigQuery is a powerful and scalable petabyte-scale data warehouse known for its efficient SQL query capabilities, and is widely adopted by organizations worldwide.

While BigQuery offers exceptional performance, cost optimization remains critical for our customers. Together, Google Cloud and Deloitte have extensive experience assisting clients in optimizing BigQuery costs. In a previous blog post, we discussed how to reduce and optimize physical storage costs when implementing BigQuery. This blog post focuses on optimizing BigQuery computational costs through the utilization of the newly introduced BigQuery editions instead of the on-demand ($/TB) pricing model.

Selecting BigQuery editions slots with autoscaling is a compelling option for optimizing costs.

Design challenges

Many organizations select BigQuery’s on-demand pricing model for their workloads due to its simplicity and pay-per-query nature. However, the computational query analysis costs can be significant. Minimizing expenses associated with computational analysis is a prominent issue for some of our clients.

Deloitte helped clients to address the following challenges:

Conducting a proof of concept to compare BigQuery editions with on-demand costsWhere to manage BigQuery editions slotsHow to charge back to different departmentsWhich criteria to use to group projects into reservationsHow to share idle slots from one reservation to others to reduce wasteHow many slots to commit for maximum ROI

Read on to learn how to address the above mentioned challenges as you work to optimize costs on Google Cloud with BigQuery.

Recommended approach

First, if you are not familiar with BigQuery editions, we recommend that you readintroduction to BigQuery editions andintroduction to slots autoscaling.

On-demand vs. BigQuery editions

Then, let’s understand the key differences between on-demand and BigQuery editions. In the on-demand pricing model, each project can scale up to 2,000 slots for analysis. The price is based on the number of bytes scanned multiplied by the unit price, independent of the slot capacity used.

On the other hand, BigQuery editions are billed based on slot hours. BigQuery editions allows for autoscaling, meaning it can scale up to the maximum number of slots defined and scale down to zero once computational analysis jobs are finished. Note that there is a one-minute scale-down window.

If you have workloads that require more than 2,000 slots per project available in on-demand, you should use BigQuery editions with higher capacity requirements. Additionally, if you’re using Enterprise or Enterprise Plus editions, you can assign baseline slots for workloads that are sensitive to “cold start” issues, ensuring consistent performance. Finally, you have the option to make a one- or three-year slot commitment to lower the unit price by 20% or 40%, respectively. Note: baseline and committed slots are charged 24/7, regardless of job activity.

Setting up a slots admin project

Before creating a reservation, it’s essential to establish a BigQuery admin project within your organization dedicated solely to administering slots commitments and reservations. This project should not have any other workloads running inside it. By doing so, all slots charges are centralized in this project, streamlining administration.

There is value to managing all reservations in a single BigQuery admin project since idle and unallocated slots are only shared among reservations within the same administration project. This practice ensures efficient slot utilization and is considered a best practice.

Comparing BigQuery editions vs. on-demand costs

To determine whether BigQuery editions slots are more cost-effective than on-demand for your workload, conduct a proof of concept by selecting a project with high on-demand query consumption and try out the BigQuery editions slots model. First, create a reservation within the BigQuery admin project and assign a project to it. Start with 2,000 slots as the maximum reservation size, equivalent to the current on-demand capacity.

You can then use the BigQuery administrative charts to determine slot cost. Additionally, you can run a query using the JOBS information schema to find out how many bytes have been scanned in the project to calculate the cost with the “on-demand” pricing model.

The following picture depicts a BigQuery slots reservation using Enterprise edition, with 2,000 max slots and 0 baseline slots without any commitment. Only one project has been assigned into this reservation to conduct a proof of concept:

Based on our experience, we saw huge benefits to leverage slots in our case. However, you must perform your own assessment as both slot-based and on-demand models offer value depending on your specific query requirements. For projects with minimal traffic volume and straightforward management needs, where most jobs or queries complete within seconds and data scans are limited, the on-demand model might be a more suitable choice.

Figure 2 and Figure 3 shows this comparison when using the BigQuery editions slot-cost model versus the on-demand model.

From Figure 2, we see that the BigQuery editions slot cost is $1,641.

Figure 3 shows a cost of $4,952 for the same time period when using BigQuery on-demand cost.

Figure 4 shows that the initial autoscaling slot size we used was too large. In Figure 7, you will see the recommended slot size from the slot estimator to allow you to reset the slot size for cost optimization.

Managing reservations and chargeback

In the BigQuery editions slots model, all slot costs are recorded in the central BigQuery admin project. Chargeback to different departments becomes a crucial consideration when there are requirements to charge different departments or teams for resources consumed for billing and accounting purposes.

An upcoming BigQuery slot allocation billing report has lines for each reservation’s cost for each assigned query project. To facilitate chargeback, we recommend grouping projects based on their cost center, allowing for easy allocation. Until this new feature is available, you can run queries based on the BigQuery information schema to determine each project’s slot hours usage for chargeback purposes.

Grouping projects

To optimize slot usage, consider grouping projects based on different workload types, such as Business Intelligence (BI), standard Extraction Transformation Loading (ETL), and data science projects. Each reservation can have unique characteristics shared by the group, defining baseline and maximum slots requirements. Grouping projects by cost center is an approach for efficient chargeback, with each cost center belonging to different departments (e.g., BI, ETL, data science).

Utilizing idle slots

Idle slots can be shared to avoid waste. By default, queries running in a reservation automatically use baseline idle slots from other reservations within the same administration project. Autoscaling slots are not considered idle capacity, as they are removed when they are no longer needed. Idle capacity is preemptible back to the original reservation as needed, irrespective of the query’s priority. This automatic and real-time sharing of idle slots helps to ensure optimal utilization.

Inintroduction to slots autoscaling, this picture explains a reservation with idle slots sharing;

Reservations use and add slots in the following priority:

Baseline slotsIdle slot sharing (if enabled)Autoscale slots

For the ETL reservation, the maximum number of slots possible is the sum of the ETL baseline slots (700) and the dashboard baseline slots (300, if all slots are idle), along with the maximum number of auto scale slots (600). Therefore, the ETL reservation in this example could utilize a maximum of 1600 slots.

Determining the right commitment level

By committing to a one- or three-year plan, you can get a 20% or 40% discount on pay-as-you-go slots (PAYG). However, if your workloads mainly consist of scheduled jobs and are not always running, you might end up paying for idle slots 24/7. To find the best reservation settings, you can use the slot estimator tool to analyze your usage patterns and gain insights. The tool suggests the optimal commitment level based on your usage. It uses a simulation starting with 100 slots as a unit to find the best ROI for your commitment level. The screenshot below shows an example of the tool.

Presently, the Google Cloud console also provides recommendations onorganization-level BigQuery editions in a dashboard, enabling you to gain a comprehensive overview of the entire system.

Additionally, optimizing slots usage together with a 3-year commitment can further reduce costs.

Let’s build

Transitioning from on-demand ($/TB) to slots using BigQuery editions presents a significant opportunity for reducing analytics costs. By following the step-by-step guidance on conducting a proof of concept and transitioning to the BigQuery editions slots model, organizations can maximize their cost optimization efforts. We wish you a productive and successful cost optimization journey as you build with BigQuery! As always, reach out to us for support here.