Data Analytics

Optimize BigQuery costs with Flex Slots

Editor’s note: This is one installment in a series about managing BigQuery costs. Check out the other posts on choosing between BigQuery pricing models and using Reservations effectively.

Google Cloud’s enterprise data warehouse BigQuery offers some flexible pricing options so you can get the most out of your resources. Our recently added Flex Slots can save you money by switching your billing to flat-rate pricing for defined time windows to add maximum efficiency. Flex Slots lets you take advantage of flat-rate pricing when it’s most advantageous, rather than only using on-demand pricing.

This is particularly useful for those of you querying large tables—those above 1 terabyte. Flex Slots lets you switch to flat-rate pricing to save money on these larger queries. We often hear, for example, that running data science or ELT jobs over large tables can benefit from using Flex Slots. And companies with teams of AI Notebook users running analytics jobs for several hours or more a day can benefit as well. 

In this blog post, you’ll see how you can incorporate Flex Slots programmatically into your BigQuery jobs to meet querying spikes or scale on demand to meet data science needs, without going over budget or using a lot of management overhead. 

Users on Flat Rate commitments no longer pay for queries by bytes scanned and instead pay for reserved compute resources; using Flex Slots commitments, you can cancel anytime after 60 seconds. At the time of this writing, an organization can run an hour's worth of queries in BigQuery's U.S. multi-region using Flex Slots for the same price as a single 4TiB on-demand query.  

Setting up for Flex Slots

The recommended best practice for BigQuery Reservations is to maintain a dedicated project for administering the reservations. In order to create reservations, the user account will need the bigquery.resourceAdmin role on the project and Reservations API slots quota.

Understanding the concepts

  • Flex Slot commitments are purchases charged in increments of 500 slot hours for $20, or ~$0.33/minute. You can increase your slot commitments if you need faster queries or more concurrency.  
  • Reservations create a named allocation of slots, and are necessary to assign purchased slots to a project. Find details on reservations in this documentation.
  • Assignments assign reservations to Organizations, Folders, or Projects. All queries in a project will switch from on-demand billing to purchased slots after the assignment is made.
1 bigquery Flex Slot.jpg

You can manage your Flex Slots commitments from the Reservations UI in the Google Cloud Console. In this post, though, we’ll show how you can use the Python client library to apply Flex Slots reservations to your jobs programmatically, so that you can schedule slots when you need them and reduce any unnecessary idle time. This means you can run jobs at any hour, without an admin needing to click a button, and automatically remove that slot commitment when it’s no longer needed (no admin needed).  

Check out the BigQuery Quickstart documentation for details on how to authenticate your client session. Here’s a look at a simple script that purchases Flex Slots for the duration of an ELT job:

  from google.cloud import bigquery
from google.cloud.bigquery.bigquery_reservation_v1 import *
import time

bq_client = bigquery.Client(project='[PROJECT]')
res_client = ReservationClient()
parent = 'projects/{}/locations/{}'.format('[ADMIN_PROJECT]', '[LOCATION]')

# commit to a flex slots purchase, billing begins
commit_config = CapacityCommitment(plan='FLEX', slot_count=500)
commit = res_client.create_capacity_commitment(parent=parent,
                                               capacity_commitment=commit_config)

# create a slot reservation with a descriptive name
res_config = Reservation(slot_capacity=500, ignore_idle_slots=False)
res = res_client.create_reservation(parent=parent,
                                    reservation_id='[RESERVATION_NAME]',
                                    reservation=res_config)

# assign the reservation to the project executing the BigQuery jobs
assign_config = Assignment(job_type='QUERY',
                           assignee='projects/{}'.format('[PROJECT]'))
assign = res_client.create_assignment(parent=res.name, assignment=assign_config)

# Assignments can take up to three minutes to take effect
time.sleep(180)

# execute large jobs at flat rate pricing
job = bq_client.query("merge into `dataset.analytics_table` using select ...")
job.result(max_results=1)

# remove the assignment, reservation, and capacity commitment
res_client.delete_assignment(name=assign.name)
res_client.delete_reservation(name=res.name)
res_client.delete_capacity_commitment(name=commit.name)

Confirming query reservations

You can see your query statistics nicely formatted in the BigQuery query history tab within the BigQuery console. The Reservation name will be indicated with a property for queries that used the reserved slots, as shown here:

2 Confirming query reservations.jpg

Interpreting the run times and costs

The charts compare the query times and costs of on-demand runs, soft-capped at 2,000 slots, with runs at increments of 500 slots up to 2,000 for a single 3.15 TB on-demand query. It’s important to remember that Flex Slot customers will also pay for idle time and those costs can add up for larger reservations. Even padded with three minutes of idle time, Flex Slots cost 60% to 80% less than the cost of on-demand pricing for large queries.

4 avg query cost.jpg

There’s a near-linear performance increase as slots are added.

3 slots and on-demand.jpg
60% to 80% cost savings using Flex Slots

Using Flex Slots and the Reservation APIs together lets you fine-tune your organization's cost and performance profile with flexibility that is unprecedented among data warehouse solutions. 

In addition, check out the BigQuery Trial Slots promotional offer for new and returning BigQuery customers. This lets you purchase 500 slots for $500 per month for six months (a 95% discount from current monthly pricing). This limited time offer is subject to available capacity and qualification criteria and while supplies last. Learn more here. To express interest in this promotion, fill out this form and we'll be in touch with the next steps.

For more details on how to get started with BigQuery or developing with the Reservations APIs, check out these resources: