Workload management using Reservations

Choosing between on-demand and flat-rate billing models

At any point in time, you can choose to switch between on-demand and flat-rate billing models, or you can choose to mix and match the two models together. The two pricing models are complementary to each other. On-demand billing is efficient; whereas, flat-rate is predictable.

Reservations trade offs.

Consider the following factors when choosing flat-rate:

  • Cost. Google recommends that your monthly analytics spend be in the ballpark of the minimum BigQuery flat-rate commitment (currently, 500 slots per month).
  • Efficiency. Consider on-demand if you value efficiency because you pay per query.
  • Predictability. You might prefer flat-rate if you value monthly predictability of pricing because you pay one flat monthly fee.
  • Resources. Flat-rate and on-demand billing models differ in how they offer capacity. For more information, see Estimating the amount of slots to purchase.

Reservations decision.

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

You can also experiment with Flex slots to test out price and performance of flat-rate billing for your organization before settling on a steady-state workload configuration. Flex slots are a special commitment type:

  • Commitment duration is only 60 seconds.
  • You can cancel Flex slots any time thereafter.
  • You are charged only for the seconds your commitment was deployed.

Flex slot commitments seamlessly combine with other types of commitments, and with other Flex slot commitments. Flex slots are ideal for short-term use cases that range from minutes to days at a time:

  • Planning for major calendar events, such as the tax season, Black Friday, popular media events, and video game launches.
  • Meeting cyclical periods of high demand for analytics, like Monday mornings.
  • Completing your data warehouse evaluations and dialing in the optimal number of slots to use.

You can choose to mix-and-match on-demand and flat-rate billing models:

  • Use on-demand billing for predictable workloads like periodic data processing and ELT.
  • Use flat-rate billing for ad-hoc analytics and data science workloads.

Estimating the amount of 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 is subjective to your performance, throughput, and utility preferences.

You can use Flex slots to experiment with the best configuration of slots. For example, you can test your workload with 500 slots, then 1000, then 1500, and 2000, and observe the impact on performance.

You can also examine the current slot usage of your projects, along with the desired monthly price that you want to pay. On-demand workloads currently 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 using INFORMATION_SCHEMA for jobs, Cloud Logging, the Jobs API, or BigQuery Audit logs. For more information, see Visualizing slots available and slots allocated.

Slot usage timeline.

Getting started with BigQuery Reservations

To start using BigQuery Reservations, complete the BigQuery Quickstart and select your administration project.

Then, you must purchase slots and assign your organization to the reservation. If you do not complete both of these steps, your organization will still be charged on-demand rates.

Purchase slots

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. In the navigation panel, go to the Reservations section. Reservations nav button.
  3. Click the Buy slots button. Buy slots.
  4. Select Monthly for the commitment duration. Select monthly.
  5. Turn on Default organization to flat-rate if you want to assign your entire organization to the slots that you are about to purchase. Optionally, you can turn off this option and assign individual projects as shown in the Assign organization to reservation section.
  6. Select US for the location.
  7. Enter 500 for the number of slots.
  8. Review and confirm your order by entering CONFIRM, then selecting PURCHASE. Enter confirm.
  9. Allow up to several minutes for the capacity to be provisioned. After the capacity is provisioned, the requested Capacity Commitment will have a green status. Slot commitments.

When capacity is purchased for the first time, a default reservation is created.

bq

Use the bq mk command with the --capacity_commitment flag to purchase 500 slots for a minimum of one month.

bq mk \
  --project_id=admin_project_id \
  --location=US \
  --capacity_commitment \
  --plan=MONTHLY \
  --slots=500

Where:

  • admin_project_id is the owner project ID of the purchased slots.

Then, use the bq mk command with the --reservation flag to create a reservation with 500 slots.

bq mk \
  --project_id=admin_project_id \
  --location=US \
  --reservation \
  --slots=500 \
  default

Where:

  • admin_project_id is the owner project ID of the reservation resource.

The next step is assigning your organization to the reservation.

Assign organization to reservation

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. In the navigation panel, go to the Reservations section. Reservations nav button.
  3. Click the Assignments tab. Assignments tab.
  4. In the Search and create assignments section, click the Browse button and select your organization. Assignments project picker.
  5. In the Reservation picker, select the reservation name ending with default. Assignments reservation picker.
  6. Click Create.

bq

Use the bq mk command with the --reservation_assignment flag to assign an organization's query jobs to a reservation.

bq mk \
  --project_id=admin_project_id \
  --location=US \
  --reservation_assignment \
  --reservation_id=admin_project_id:US.default \
  --job_type=QUERY \
  --assignee_id=organization_id \
  --assignee_type=ORGANIZATION

Where:

  • admin_project_id is the owner project of the reservation resource.

  • organization_id is your organization ID.

Creating additional reservations with dedicated slots

When an organization is assigned to a reservation, all projects using BigQuery will fairly share those slots. Depending on the workload, sharing slots with other workloads may not be ideal. For example, a production workload likely does not want to share slots evenly with test workloads. If a project requires dedicated slots, additional reservations can be created with their own reserved slots. The following sections describe an example of creating and assigning 500 slots to my-prod-project.

Create prod reservation

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Create Reservation button. Create reservation.
  4. In the Reservation name field, enter prod. Create reservation info.
  5. In the Location field, select US.
  6. For the number of slots, enter 500.
  7. Click Save.

The newly created prod reservation is visible in the overview table.

Reservation overview.

bq

Use the bq mk command with the --reservation flag to create a reservation with 500 slots.

bq mk \
  --project_id=admin_project_id \
  --location=US \
  --reservation \
  --slots=500 \
  prod

Where:

  • admin_project_id is the owner project ID of the reservation resource.

Assign my-prod-project to prod reservation

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Assignments tab. Assignments tab.
  4. In the Search and create assignments section, click the Browse button and select your prod project. Assignments project picker.
  5. In the Reservation picker, select the reservation name ending with prod. Assignments reservation picker.
  6. Click Create.

bq

Use the bq mk command with the --reservation_assignment flag to assign your production project's query jobs to the prod reservation.

bq mk \
  --project_id=admin_project_id \
  --location=US \
  --reservation_assignment \
  --reservation_id=admin_project_id:US.prod \
  --job_type=QUERY \
  --assignee_id=my-prod-project \
  --assignee_type=PROJECT

Where:

  • admin_project_id is the owner project ID of the reservation resource.

  • my-prod-project is your production project ID.

Now, any query jobs submitted from my-prod-project will run under the prod reservation and have access to 500 slots.

Purchasing additional slots and increasing reservation sizes

As the size and complexity of workloads increase over time, you may find it necessary to purchase additional slots to improve performance.

Purchase additional slots

Follow the instructions in Getting started with BigQuery reservations to purchase an additional 500 slots.

Increase prod reservation size

To increase the size of the prod reservation, complete the following steps.

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section. Reservations section.
  3. Select the prod reservation previously created and click the edit button. Reservations edit.
  4. Enter the new reservation size: 1,000 slots. Increase reservation slots.
  5. Click Save.
  6. The updated prod reservation size is visible in the list of reservations. Updated reservation size.

bq

Use the bq update command with the --reservation flag to update the size of your production reservation.

bq update \
  --project_id=admin_project_id \
  --location=US \
  --slots=500 \
  --reservation prod

Where:

  • admin_project_id is the owner project ID of the reservation resource.

Assigning projects/folders/organization to reservations

Project, folders, and organizations can all be assigned to reservations. A project reservation assignment has priority over a folder assignment, which has priority over an organization assignment.

Moving an assignment to a different reservation

You can move an assignment from one reservation to another reservation.

bq

Use the bq update command to move the assignment.

bq update \
  --project_id=admin_project_id \
  --location=US \
  --reservation_assignment \
  --destination_reservation_id=admin_project_id:US.destination_reservation \
  original_reservation.assignment_id

Where:

  • admin_project_id is the owner project ID of the reservation resource.
  • original_reservation is the current reservation.
  • destination_reservation is the destination reservation.
  • assignment_id is the assignment ID.

Converting commitments to longer duration commitment types

You can choose to convert your commitment to a longer-duration commitment type at any time:

  • You can convert your flex slots to a monthly or annual commitment type.
  • You can convert your monthly commitment to an annual commitment.

The following example upgrades your monthly commitment from monthly to annual, and chooses annual renewal.

bq

Use the bq command-line tool to run the following command:

bq update \
--project_id=admin_project_id \
--location=US \
--plan=ANNUAL \
--renewal_plan=ANNUAL \
--capacity_commitment commitment_name

From the moment you update your commitment, you will start being charged the rate associated with the new commitment type, and your commitment end date resets.

Renewing commitments

You can choose your commitment renewal plan option prior to the commitment end date.

Console

New commitments

You can choose your renewal plan option during commitment creation.

Existing commitments

You can change your renewal plan choice for existing annual commitments.

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Slot commitments tab.
  4. For the commitment you want to edit, click Actions and then Edit.

bq

You can change your renewal plan choice for existing commitments with the bq command-line tool and the --renewal_plan option. The following command declines to renew an annual commitment, and converts it to a monthly commitment when the plan end time lapses.

bq update \
--project_id=admin_project_id \
--location=US \
--renewal_plan=MONTHLY \
--capacity_commitment commitment_name

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. In order to manage reservations in another region, you must change the region in the BigQuery Reservation page:

Console

  1. Click on the Reservations section in the BigQuery console UI.
  2. Click on the Location picker and select a region where you want to manage reservations. Select different region.
  3. Once a region is selected, you can purchase slots, create reservations, and assign a project to a reservations.

Assigning load and export jobs to reservations

The slots used by load and other pipeline jobs are free and best-effort. BigQuery does not make guarantees on ingest capability. If you need dedicated resources for load or less volatile performance, you can purchase slots and create reservations dedicated for load and other pipeline jobs.

The following example shows you how to assign my_project's pipeline jobs to your administration project's load reservation.

bq

Use the bq mk command with the --reservation flag to create a reservation with 1,000 slots that will be used for your pipeline jobs.

bq mk \
  --project_id=admin_project_id \
  --location=US \
  --use_idle_slots=false \
  --slots=1000 \
  --reservation my_pipeline

Where:

  • admin_project_id is the owner project ID of the reservation resource.

Use the bq mk command with the --reservation_assignment flag to assign your project's pipeline jobs to the pipeline reservation.

bq mk \
  --project_id=admin_project_id \
  --location=US \
  --reservation_assignment \
  --reservation_id=admin_project_id:US.my_pipeline \
  --assignee_id=my_project \
  --assignee_type=PROJECT \
  --job_type=PIPELINE

Where:

  • admin_project_id is the owner project ID of the reservation resource.
  • my_project is the project ID whose pipeline jobs you want to assign to the reservation.
  • my_pipeline is the name of the reservation to which you want to assign the pipeline jobs.

Removing projects from reservations

You can remove projects from reservations by removing the reservation assignments. If a project is not assigned to any reservations, it uses pay-per-query (on-demand) pricing and is billed accordingly. A project doesn't use on-demand pricing if the parent folders or organization is assigned to a reservation.

To remove a project from a reservation:

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Assignments tab. Assignments tab.
  4. For the assignment you want to delete, click Actions, and then Delete. Delete assignment.

bq

Use the bq rm command with the --reservation_assignment flag to remove your project from the reservation.

bq rm \
  --project_id=admin_project_id \
  --location=US \
  --reservation_assignment admin_project_id:US.reservation_id.reservation_assignment_id

Where:

  • admin_project_id is the owner project ID of the reservation resource.

  • reservation_assignment_id is the ID of the reservation assignment.

Finding a project's reservation assignment

You can find out if your project, folder, or organization is assigned to a reservation by doing the following:

Console

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Assignments tab. Assignments tab.
  4. Click the project picker and select the desired project, folder, or organization. Assignments project picker.
  5. If the reservation assignment exists, it appears below the picker.

bq

Use the bq show command with the --reservation_assignment flag to find what reservation your project's query jobs are assigned to.

bq show \
  --project_id=admin_project_id \
  --location=US \
  --reservation_assignment \
  --job_type=QUERY \
  --assignee_id=my-prod-project \
  --assignee_type=PROJECT

Where:

  • admin_project_id is the owner project ID of the reservation resource.

  • my-prod-project is your production project ID.

Deleting capacity commitments

A capacity commitment is eligible for deletion based on its commitment duration:

  • Flex: After the first 60 seconds, you can delete your commitment at any time. Your bill will be prorated per-second.
  • Monthly: After the first 30 days, you can delete your commitment at any time. Your bill will be prorated per-second.
  • Annual: After one year, you can choose not to renew your slot pool. At this time, your slot pool will convert to the monthly rate, become cancelable at any time, and your bill will be prorated per-second.

The commitment end date shows in the Reservations UI. You can delete your capacity commitment after the commitment date. If you would like to delete a capacity commitment, you must first make sure that there are enough unallocated slots. If not, you must decrease the number of slots in a reservation or remove reservations entirely.

Console

To delete a capacity commitment, complete the following steps:

  1. In the Google Cloud Console, go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Reservations section.
  3. Click the Slot commitments tab.
  4. For the commitment you want to delete, click Actions and then Delete. Commitments delete.
  5. Type "REMOVE" and press Proceed. Commitments delete.

bq

Use the bq rm command with the --capacity_commitment flag to delete a capacity commitment.

bq rm \
  --project_id=admin_project_id \
  --location=US \
  --capacity_commitment=admin_project_id:US.capacity_commitment_id

Where:

  • admin_project_id is the owner project ID of the reservation resource.

  • capacity_commitment_id is the capacity commitment ID.

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 may 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.

Commitments delete.

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

BigQuery Reservations for complex organizations

BigQuery Reservations is an organization-scoped resource. You purchase committed capacity centrally to be used across your entire organization. You can require individual departments or divisions to self-manage BigQuery Reservations, purchasing committed capacity, distributing that capacity across the department or division, and being billed for that 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 disparate organizations.

Using flat-rate and on-demand billing models together

You can use flat-rate and on-demand billing models together.

You can use flat-rate in one region and on-demand in another region:

  1. By default, you use on-demand in all regions.
  2. You opt a project, folder, or organization into flat-rate billing in a region by assigning it to a reservation.
  3. 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 flat-rate billing in the US multi-region, and will remain on on-demand billing in all other regions.

Within a region, you can use both flat-rate and on-demand together:

  1. Do not assign your folder or organization to a reservation.
  2. Instead, explicitly assign projects to a reservation.
  3. Any project not assigned to a reservation will remain on-demand. For example:
    • If you have projects A, B, and C
    • You do not create an organization-wide assignment
    • You explicitly assign projects A and B to a reservation
    • Project C remains on on-demand billing

Any projects that are on on-demand billing use capacity that's separate from your committed capacity. Those projects do not affect the availability of your committed capacity.