Demystifying BigQuery reservations
Learn how to mix slot-based and on-demand pricing
Note: Updated in 2024/07/29 to BigQuery Editions.
Note: For a deep dive on BigQuery cost optimization don’t forget this white paper.
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 slot-based pricing (Editions). You allocate computation power expressed in Slots. This makes possible precise budgeting. Optionally, you can create Commitments to gain access to discounted Slot prices.
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.
Even when using the BigQuery web UI these concepts are visible.
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 slot-based model.
Note: The origin project is known as billing project in the documentation.
Assign slots to a Project
In BigQuery, with the Reservations you allocate a certain amount of Slots and give them a name. Each Reservation defines its minimum and maximum number of Slots. The Autoscaler will provide Slots above the minimum up to the maximum when there is demand.
With the Assignments you allow one (or more) Resource to use the Reservation. A Resource is a Project, Folder or Organization.
Commitments allow to obtain discounts. The customer commits to consume a given amount of Slots for 1 year or 3 years. Because this gives predictability to Google Cloud, the customer is awarded with discounted Slot price. The longer the Commitment, the lower the price per Slot.
To recap, you allocate Slots with Reservation and assign them to (origin) Projects.
The slots model requires that (at least) one Project be elected to Admin Project. In this project you will create Reservations and Commitments. This requires 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.
Note: after March 2024 every project where you enable the BigQuery API has automatically enabled also the Reservation API. So, technically, every project where you enable BigQuery is also a “potential” Admin Project. Following the principle of least privilege, we suggest to enable only the Reservation API in your Admin Project.
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 possible configurations are infinite but here are some simple examples:
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 slot-based model and will have access to the Slots assigned.
- Special case, a query with origin in a Project with an Assignment from the None Reservation will use the on-demand model.
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 slot-based model is applied.
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.
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 chosen at run time, this allocation does not have to be static. It can be changed at any time, even just before running a query. Another way to change the billing model and allocated resources is keeping the origin project fixed but altering the Reservation assigned to it.
By combining this with the Resource Hierarchy , automation 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.jobUserLimit 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.
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:
- Computing pricing comes in two types, on-demand and slot based, and both may be needed in your organization.
- You can use them both on a case by case basis by selecting the right “origin project” for your queries.
- You can allocate Slots (Reservations, Assignments) to specific users (Projects, Roles) and get discounted prices via Commitments.
- On-demand use can be allocated and controlled as well.
