BigQuery dynamic reservation: the new query cost hack!

In the big data world, data is constantly growing, leading to increased costs. FinOps initiatives are emerging to control this cost expansion.

In

Google Cloud

, BigQuery is no exception, and various strategies exist to optimize and reduce its costs.

This document focuses on a new BigQuery feature that can further optimize query costs.

Recap BigQuery cost models

Before delving into the new feature, it’s helpful to recap the strengths and limitations of reservation versus on-demand cost models.

Reservations vs. On-Demand Cost

By default, BigQuery charges per volume of data scanned. It’s crucial to note that “scanned” refers to the data read and processed by the query, not just the data retrieved.

Techniques like partitioning, sharding, clustering, and indexes can limit the data scanned, saving money.

The lowest on-demand price is $6.25 per terabyte (TB) scanned, with each project able to use up to 2,000 slots at any given time (and 20k per organization)

In large companies with massive datasets, the on-demand pricing model may not be sustainable. Therefore, a capacity model can be chosen, where a predefined compute capacity, known as “slots,” is reserved.

With this model, the cost is based on the compute capacity used for the query, regardless of the data volume scanned.
Optimizations to limit data scanned still apply (less data, less computation needed) but have a less direct cost impact.

The lowest cost is $0.04 per slot/hour for Standard Edition, without commitment.
There are no real limits to the number of slots, as long as you can afford the reservation size. The default quota is between 2,000 and 10,000 slots, depending on the region, but it can be increased upon request to the support.

The choice of cost model depends on how data is queried and used, as well as the data volume:

• If a large volume of data is scanned with minimal computations, the reservation model is generally better.
• If the data volume is acceptable but complex computations and transformations are performed, the on-demand model may be preferable, provided the 2,000 slot limit isn’t an issue.

Working with Reservations and Assignments

When working with reservations, several concepts are important

• Baseline number of slots: Paid annually, with options for three-year commitments.
• Maximum number of slots: The difference between the maximum and baseline slots are “autoscale slots,” which are more expensive but only paid for when used.

Each reservation can have zero to many projects attached, which is called an “assignment.”
A project can only be assigned to a single reservation.

Current Limitations and Caveats

One of the main issues is the binary assignment:

• A project not in a reservation runs all queries on the on-demand pricing model.
• A project assigned to a reservation runs all queries with that reservation.

The available features depend on the reservation edition (Standard, Enterprise, Enterprise Plus), which also affects the cost per slot.

Therefore, if a single query needs an Enterprise Plus Edition feature, the entire project’s assignment might need to be changed, or a new reservation and/or commitment created, leading to extra costs.

The same problem arises when some queries are cheaper on-demand and others in reservations.
A choice must be made for the entire project, even if it’s not optimal for all queries.

This stems from the heterogeneous queries and requirements of your project.

Slot distribution among active queries can also be an issue.
BigQuery evenly distributes available slots across concurrent queries, which is re-evaluated at each query step.

If too many queries run concurrently, the slot capacity pool can be exhausted or be very low per query, leading to extra request duration.

Autoscale feature can mitigate this issue. However, adding a new query can still cause latency and bottlenecks.

Moreover, multiple projects can be assigned to the same reservation, meaning other teams’ queries can impact your performance.

To mitigate this, different reservations can be created, and the “share idle slots” feature can be used.
However, some situations may still require running urgent production queries on a saturated reservation.

For these cases, BigQuery released the dynamic reservation feature.

The BigQuery dynamic reservation feature

To address issues with projects assigned to a single reservation, BigQuery introduced a feature that allows query runners (users or service accounts) to override the default reservation to use for a query.

The key addition is the ability to deactivate reservation usage and switch to on-demand pricing by specifying “none” as the reservation.

Testing in Practice

To demonstrate, I used the BigQuery console, although the BigQuery CLI or API can also be used.

First, at least two reservations are needed. I used only the autoscale configuration and created a main and a backup reservation.

My project was assigned to the main reservation, and the backup reservation was empty.

Then, 3 dummy queries were tested:

• Default reservation
• Override to use the backup reservation
• Set the reservation to “None” to switch to on-demand pricing

Finally, the INFORMATION_SCHEMA.JOBS view was used to check the reservation used for each job to validate the dynamic changes.

This worked well, allowing queries to be switched to a backup reservation for emergency or isolated runs, and to switch dynamically to on-demand pricing.

Evaluating Potential Gain

Switching between reservation and on-demand pricing can significantly impact query cost.
You can see this only in my dummy example!

• Dummy queries showed that queries with reservations, “cost” 0 bytes and 32 slot-ms. Here I have to pay the slots usage.
• The no-reservation query cost 0 bytes and 9 slot-ms. However, due to on-demand pricing which bill the bytes, the actual cost is zero.

To evaluate this in your company, a query can estimate the gain per query.

with parameters as (
  select 6.25 as on_demand_tb_cost, 0.04 as slot_hour_cost
)
from region-US.INFORMATION_SCHEMA.JOBS
|> join parameters on true
|> extend (total_bytes_billed/(1024*1024*1024*1024)) * on_demand_tb_cost as on_demand_cost
|> extend (total_slot_ms/3600000) * slot_hour_cost as reservation_cost
|> select creation_time, project_id, query, reservation_id, total_bytes_billed, total_slot_ms, on_demand_cost, reservation_cost
|> order by creation_time desc

You can change parameters according to your region and discounts.

Queries can also be grouped per day and per query hash (query_info.query_hashes.normalized_literals) to group similar queries, regardless of WHERE clause variables.

with parameters as (
  select 6.25 as on_demand_tb_cost, 0.04 as slot_hour_cost
)
from region-US.INFORMATION_SCHEMA.JOBS
|> aggregate count(1) as nb_run, SUM(total_bytes_processed)/(1024*1024*1024*1024) as total_tb_processed, SUM(total_slot_ms)/(3600000) as total_slot_h group by date(creation_time) as date, query_info.query_hashes.normalized_literals as query_hash
|> join parameters on true
|> extend total_tb_processed * on_demand_tb_cost as on_demand_cost
|> extend total_slot_h as reservation_cost
|> select date, query_hash, nb_run, total_tb_processed, on_demand_cost, total_slot_h, reservation_cost
|> order by date desc

Cautions and Tradeoffs

While automating optimization at scale might seem appealing, it’s not straightforward!

• Slot consumption can only be known after the first query run, making a priori cost optimization impossible.
• On-demand pricing is limited to 2,000 concurrent slots per project (and 20k per organization!), which can slow down previously larger queries.
• Switching to on-demand pricing removes the possibility of using idle slots from other reservations.
• Committing to many slots over three years and then using reservations less could waste the commitment.

Try, Monitor, and Save Money

Dynamic reservation in BigQuery is a useful addition for fine-tuning query run time and cost.

However, it must be tested, monitored for latency, and weighed against potential risks.

The optimization and risk versus money saved must be carefully considered based on your specific use cases and constraints.