Demystifying BigQuery reservations

Learn how to mix flat-rate and on-demand pricing

Photo by Mike Petrucci on Unsplash

BigQuery is the exabyte-scale data warehouse of Google Cloud. It revolutionized the market with the concept of data warehouse as a service and pay-as-you-go.

BigQuery completely decouples the computing and storage layers and this is reflected in its pricing. You pay computing and storage separately.

Focusing on the computing layer, with the on-demand pricing, you pay only for the bytes actually read by your queries and you get access to a computing power of up to 2000 burstable Slots* per project. This is a supercomputer at your fingertips.

* A Slot is the unit of measure for the computing power of BigQuery. More Slots, more speed.

However, many complex organizations have internal processes and governance rules to follow. These organizations want to allocate their budgets to the different groups and make sure that everyone stays in the allocated spending.

For these use cases, BigQuery supports the flat-rate pricing. You purchase in advance reserved computation power expressed in Slots with an annual, monthly or by-minute (Flex Slots) commitment.

The origin project of a query

You may have not noticed this but

This can be made very clear by looking at this command line

The origin project (in blue) is specified via the project_id parameter while the projects of the queried tables (in green) are expressed as part of the full name of the table.

Projects involved in the example

Even when using the BigQuery web UI these concepts are visible.

Parts of a query in the web UI

Having clarified the concept of “origin project of a query” and provided a way to visualize it, we can proceed by focusing again on the flat-rate model.

Assign slots to a Project

Data model of the Reservation API

In BigQuery you can purchase a certain number of Slots through a Commitment. It specifies the number of Slots desired and for what minimum period of time (year, month, minute). The longer the Commitment, the lower the price per Slot.

Through Reservations you divide the Slots you purchased into parts and give them a name.

Assignments assign the amount of Slots specified by a Reservation to one or more Projects.

To recap, you buy the Slots and distribute them to the Projects.

The flat-rate model requires that (at least) one Project be elected to the Admin Project role by enabling the Reservation API on it. It can be any Project but you may want to have a dedicated Project just to keep things clear and separated.

Commitments, Reservations and Assignments can be created only within Admin Projects. Assignments can point to any Project.

Commitments, Reservations and Assignments have a Location-wide scope. In other words, the Slots of a Commitment cannot be distributed over multiple Locations but it is possible to buy Commitments on multiple Locations.

Everything can be easily done from the BigQuery web UI, via CLI or API.

The Reservations panel in the web UI

The possible configurations are infinite but here are some simple examples:

All the Slots assigned to the Admin Project
All the Slots assigned to another Project
300 Slots to Project A, 200 Slots to Project B and no Slots to Project C

Change the pricing model of a query

Now that we have the Slots assigned to a Project, how can we use them?

By changing the origin Project of a query it is possible to change the resources and the pricing model.

  • If the origin Project doesn’t have any Assignment, the query will use the on-demand model.
  • If the origin Project has an Assignment, the query will use the flat-rate model and will have access to the Slots assigned.

In the following examples we have a Project A with Slots assigned and a Project B without Slots. In the first example, Project A is the origin and therefore the flat-rate model is applied.

Flat-rate example

In the second example, exactly the same query is executed, but this time the origin Project is Project B. Because it has no Slots, the on-demand model is used.

On-demand example

We can now pause a moment, do a step back and try to understand the whole picture.

The idea behind Reservations is to distribute Slots (or not) to a set of Projects. Then, allocate the workloads (queries) on these Projects based on the desired pricing model and the Slots you need.

Since the origin Project is defined at run time, this query / orgin_project allocation does not have to be static. It can be changed at any time, even just before running a query.

By combining this with the Resource Hierarchy and perhaps multiple Admin Projects, highly advanced workload management configurations are possible.

Constraining users

We can imagine a situation in which the user group X (e.g. engineering) has 20000 dedicated Slots on Project X and the user group Y (e.g. finance) has 500 dedicated Slots on Project Y. Users are asked to use only their own Project.

With what we have explained so far, nothing prevents an impatient user of group Y from using the higher power available on Project X. After all, he only has to use Project X as the origin of his queries.

The bigquery.jobs.create permission can help us in this cases. Only when a user (or group) has this permission on a Project, he can use it as the origin. The permission is included in roles/bigquery.jobUser and roles/bigquery.user.

Returning to our example, we must be sure that each group has one of these roles on all and only the Projects it can use as origin.

* Group X
* Project X: roles/bigquery.jobUser
* Project Y: -
* Group Y
* Project X: -
* Project Y: roles/bigquery.jobUser

Limit on-demand use

A similar approach can be used to limit the on-demand use for a group of users. For example, we can force group Z to use only the on-demand model and up to 10 TB/day.

We create a Project Z, do not assign Slots to it, but instead configure the custom quota “BigQuery API — Query usage per day” to 10 TB. Then, we assign roles/bigquery.jobUser to group Z only in Project Z.

In this way, the group Z can only use Project Z as origin. As a result, all their queries will use the on-demand pricing and erode the configured quota.

The quotas menu

This can be repeated to create multiple independent buckets and, if desired, may even limit usage per user with the custom quota “BigQuery API — Query usage per day per user”.

Other tips are limiting bytes per query and monitor usage as described here.

Closing

In this article we have learned that:

  1. Computing pricing comes in two types, on-demand and flat-rate, and both may be needed in your organization.
  2. You can use them both on a case by case basis by selecting the right “origin project” for your queries.
  3. You can purchase flat-rate computing power (Slots, Commitments) and allocate it (Reservations, Assignments) to specific users (Projects, Roles).
  4. On-demand use can be allocated and controlled as well.

To celebrate the 10 years anniversary of BigQuery, Google is offering Trial Slots with a 95% discount. I encourage you to start experimenting with Reservation. Just login to the Google Cloud Console and buy a per-minute Commitment (Flex Slots).

Update 20200812: now you can buy even just 100 Slots.

 by the author.

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Recommended from Medium

Cortana is really, really bad

Host a Static website on EC2 using Route 53 with 3rd party domain

Difference Between System Admin And Network Admin

Difference Between System Admin And Network Admin

Main characteristics of microservice architecture

Where Does a Software Engineering Head Invest Its Time?

Hey. everyone

Flutter Events & Conferences in 2022 you mustn’t miss

Download In @#PDF The Professional Product Owner: Leveraging Scrum as a Competitive Advantage Read…

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Valentino Miazzo

Valentino Miazzo

Data Analytics @ Google

More from Medium

Version Control of BigQuery schema changes with Liquibase

BigQuery or BigTable?

Physical Modeling of Tables in BigQuery

GCP: How To Sync Cloud SQL with BigQuery

https://cloud.google.com/bigquery#section-9