Workload management using Reservations

This page describes how to use BigQuery Reservations to help manage your BigQuery workloads.

Choose a billing model

You can switch between on-demand and capacity-based billing models at any time, or you can choose to mix and match the two models together. The two pricing models complement each other. On-demand billing lets you pay for slots only when you need them, while capacity-based pricing lets you reserve slot allocations for predictable workloads, generally at a discount to on-demand pricing.

Consider the following factors when choosing capacity-based billing:

• Cost. Google recommends that your monthly analytics spend be around the minimum BigQuery capacity-based commitment.
• Efficiency. If you run workloads that are infrequent or inconsistent, on-demand pricing might be more efficient. On-demand pricing lets you only pay for slots when you need them.
• Predictability. You might prefer the capacity-based billing model if you value monthly predictability of pricing because you have the option to pay a consistent monthly fee.
• Resources. Capacity-based and on-demand billing models differ in how they offer capacity. For more information, see Estimating the amount of slots to purchase.

As a new customer, you might choose to start with capacity-based billing. At any point in time, you can observe what your cost would have been with on-demand billing, and at any point in time you can choose the model that works for you.

You can also experiment with slots autoscaling. Autoscaling slots provide flexibility to handle a sudden increase of workloads, and are ideal for handling variance in capacity needs.

You can also combine on-demand and capacity-based billing models, at the workload level or at the business unit level, by organizing workloads or business units into different projects or folders and assigning each one to a reservation or to on-demand billing.

Note: On-demand pricing gives you access to 2,000 slots or more per Google Cloud project. With capacity-based pricing, you can commit to fewer than 2,000 slots, but your queries might be less performant, depending on your workload demands.

You can use capacity-based in one region and on-demand in another region. By default, all projects use on-demand billing. Within a region, you can opt a project, folder, or organization into capacity-based billing by assigning it to a reservation. For example, if you purchase a slot commitment in the US multi-region and assign your organization to the default reservation, your organization will be on capacity-based billing in the US multi-region, but will remain on on-demand billing in all other regions.

Within a region, you can combine capacity-based and on-demand billing by explicitly assigning projects to a reservation. Any project not assigned to a reservation remains on on-demand billing. You can also explicitly assign a project to use on-demand billing by assigning the reservation ID none. This is useful if you assign a folder or an organization to a reservation, but want some projects within that folder or organization to use on-demand billing. For more information, see Assign a project to None.

Projects with on-demand billing use capacity that's separate from your committed capacity. Those projects don't affect the availability of your committed capacity.

Creating an administration project

When you create commitments and reservations, they are associated with a Google Cloud project. This project manages the BigQuery Reservations resources, and is the primary source of billing for these resources. This project does not have to be the same project that holds your BigQuery jobs.

Google recommends creating a dedicated project for Reservations resources. This project is called the administration project, because it centralizes the billing and management of your commitments. Give this project a descriptive name like bq-COMPANY_NAME-admin. Then create one or more separate projects to hold your BigQuery jobs.

Only projects within the same Organization resource as the administration project can be assigned to a reservation. If the administration project is not part of an Organization, then only that project can use the slots.

The administration project is billed for the committed slots. Projects that use the slots are billed for storage but not billed for the slots. You can purchase more than one type of plan (for example, annual and three-year) and put the slots into the same administration project.

Note: After you create a reservation or a commitment, you can't move it to a different administration project.

As a best practice, limit the number of administration projects. This helps to simplify billing management and slot allocation. One administration project for all of your organization's reservations is recommended where practical. Complex organizations might require additional administration projects to meet management or billing requirements.

Creating multiple administration projects

In some cases, you might want to create more than one administration project:

• To separate costs from multiple reservations and commitments into different organizational units.
• To map one or more slot commitments to specific sets of reservations.

Idle slot capacity is not shared between reservations in different administration projects.

When you're on the BigQuery page of the Google Cloud console, you can view reservations and commitments only for the currently selected administrator project.

Estimating how many slots to purchase

BigQuery is architected to scale linearly with increased resources. Depending on the workload, incremental capacity is likely to give you incremental benefits. Therefore, choosing the optimal number of slots to purchase depends on your requirements for performance, throughput, and utility.

You can experiment with baseline and autoscaling slots to determine the best configuration of slots. For example, you can test your workload with 500 baseline slots, then 1,000, then 1,500, and 2,000, and observe the impact on performance.

You can also examine the current slot usage of your projects, along with the chosen monthly price that you want to pay. On-demand workloads have a soft slot cap of 2,000 slots, but it is important to check how many slots are actually being used by your projects by using INFORMATION_SCHEMA.JOBS* views, Cloud Logging, the Jobs API, or BigQuery Audit logs. For more information, see Visualizing slots available and slots allocated.

After you purchase slots and run your workloads for at least seven days, you can use the slot estimator (Preview) to analyze performance and model the effect of adding or reducing slots. For more information, see Estimate slot capacity requirements.

Managing your workloads and departments using reservations

You can use BigQuery Reservations to isolate your committed capacity across workloads, teams, or departments by creating additional reservations and assigning projects to these reservations. A reservation is an isolated pool of resources with the added benefit of being able to take advantage of idle capacity across your entire organization.

For example, you might have a total committed capacity of 1,000 slots and three workload types: data science, ELT, and BI.

• You can create a ds reservation with 500 slots, and assign all relevant Google Cloud projects to the ds reservation.
• You can create an elt reservation with 300 slots, and assign projects you use for ELT workloads to the elt reservation.
• You can create a bi reservation with 200 slots, and assign projects connected to your BI tools to the bi reservation.

Instead of partitioning your capacity across workloads, you might choose to create reservations for individual teams or departments.

Managing reservations in different regions

Reservations are a regional resource. Slots purchased and reservations created in one region cannot be used in any other region. Project, folders, and organizations can all be assigned to reservations in one region and run on- demand in another. To manage reservations in another region, you must change the region in the BigQuery Capacity Management page:

1. In the BigQuery console, click Reservations.
2. Click the Location picker and select a region where you want to manage reservations.
3. After a region is selected, you can purchase slots, create reservations, and assign a project to a reservation.

BigQuery Reservations for complex organizations

BigQuery Reservations is an organization-scoped resource. You purchase reservations centrally to be used across your entire organization. You can require individual departments or divisions to manage their BigQuery Reservations by purchasing committed capacity and distributing that capacity across the department or division. The administration project is associated with a Cloud Billing account, which is billed for the capacity.

You can use a distinct Google Cloud organization for each of your departments or divisions. In this scenario, you define an administration project for each organization and manage BigQuery Reservations for that organization from the administration project. There is no sharing of committed or idle capacity across organizations.

Idle and unallocated slots are only shared across reservations created in the same administration project and within the same edition. If you use more than one administration project, slots are not shared across reservations in different administration projects.