Introduction to workload management

BigQuery workload management lets you control resources and features available for data analysis in your organization as well as defining billing models. BigQuery compute capacity, measured in slots, can be purchased in reservations and commitments which you then assign across your organization to perform jobs.

BigQuery offers two models of workload management for data analysis, also known as compute. With On-demand billing, you pay for the bytes processed when you query your data. With capacity-based billing you reserve processing capacity for workloads with the option of automatically scaling capacity up and down.

You can switch between on-demand and capacity-based billing models at any time, or you can choose to combine the two models. The capacity-based model gives you explicit control over slots and processing capacity; the on-demand model does not.

Reservations trade offs.

The following models define how you manage workloads and how you are billed:

  • On-demand pricing: By default, you are billed for the data scanned by your queries. You have a fixed, per-project query processing capacity, and you only pay for the number of bytes processed when you use them.

  • Capacity-based pricing: You purchase dedicated or autoscaling query processing capacity at a discounted rate. Capacity is measured in slots which you allocate to workloads or parts of your organization. Capacity-based billing offers the option of commitments, which let you commit to a consistent level of processing capacity over a 1- or 3-year period at a discounted price. There is no charge for bytes processed when using the capacity-based model. Advantages of capacity-based pricing include:

    • Cost. As a best practice, you can set your minimum monthly analytics use as your BigQuery capacity-based commitment to reduce costs.
    • Predictability. Capacity-based slots allow for more consistent monthly fees.
    • Centralized purchasing: You can purchase and allocate slots for your entire organization rather than for each project that uses BigQuery.
    • Flexibility. You can choose how much capacity to allocate to a workload or let BigQuery automatically scale capacity based on your workload requirements. You are billed in per-second increments with a minimum usage period of one minute.

Creating a capacity commitment is optional when working with reservations that use BigQuery editions, but can reduce costs for steady-state workloads.

You can combine billing models. For example, you might run some workloads with on-demand pricing and others with capacity-based pricing. As the billing model is specified per project, this would require that you use multiple projects for your query jobs. To better understand BigQuery pricing for reservations, see the following:

Slots

Processing capacity in BigQuery is measured in slots. Slots represent virtual CPUs that are used to query data. In general, access to more slots lets you run more concurrent queries, and your complex queries can run faster. The capacity-based pricing model lets you reserve a volume of slots. Your queries run within that capacity, and you pay for that capacity continuously every second it's deployed. For example, if you purchase 2,000 BigQuery slots, your queries in aggregate are limited to using 2,000 virtual CPUs at any given time. You have this capacity until you delete it, and you pay for 2,000 slots until you delete them.

To learn more about slots and how they are used, see Understand slots.

Slot quotas

Slot quotas provide a safeguard for BigQuery. The quota type depends upon the pricing model of the slots:

  • On-demand pricing model: The per-project slot quota with transient burst capability is sufficient for most users. Depending on your workloads, access to more slots can improve query performance. To check how many slots your account uses, see BigQuery monitoring.

  • Capacity-based pricing model: Reservations quotas and limits define the maximum number of slots you can purchase in a location. You are only billed for your reservations and commitments, not for the quotas.

For information about increasing your slot quota, see Requesting a quota increase.

Jobs

Jobs are actions that BigQuery runs on your behalf to load data, export data, query data, or copy data.

When you use the Google Cloud console or the bq tool to perform one of these jobs, a job resource is automatically created, scheduled, and run. You can also programmatically create a load, export, query, or copy job. When you create a job programmatically, BigQuery schedules and runs the job for you.

Because jobs can potentially take a long time to complete, they run asynchronously and can be polled for their status. Shorter actions, such as listing resources or getting metadata, are not managed by a job resource.

For more information about BigQuery, Understand jobs.

Reservations

Slots are purchased, assigned to resources, and allocated to jobs in pools called reservations. Reservations let you assign slots in ways that make sense for your organization. For example, you might create a reservation named prod for production workloads, and a separate reservation named test for testing, so that test jobs don't compete for production workloads resources. Or, you might create reservations for different departments in your organization.

If you purchase slot commitments before creating a reservation, then a reservation named default is created automatically as a convenience. The default reservation has no special behavior. You can create additional reservations if needed or use the default reservation.

For information about pricing, see capacity-based pricing and on-demand pricing.

Reservation limitations

  • Reservations that you create are not shared with other organizations.
  • You must create a separate reservation and a separate administration project for each organization.
  • Each organization can have a maximum of 10 administration projects with active commitments in a single location.
  • Idle capacity cannot be shared between organizations or between different administration projects within a single organization.
  • Commitments and reservations are regional resources. Commitments purchased in one region or multi-region can't be used for reservations in any other regions or multi-regions, even when the single region location is contained within the multi-region location. For example, you can't use a commitment purchased in the EU multi-region for a reservation in europe-west1.
  • Commitments and reservations can't be moved from one region or multi-region to another.
  • Commitments purchased in one administration project cannot be moved to a different administration project.
  • Commitments purchased with one edition cannot be used with reservations of another edition.
  • Idle slots are not shared between reservations of different editions.
  • Autoscaled slots are not shareable as they will scale down when no longer required.

Reservation assignments

To use the slots that you purchase, you must assign one or more projects, folders, or organizations to a reservation. A reservation is the lowest level at which you can specify slot assignment. When a job in a project runs, it uses slots from its assigned reservation. Resources can inherit assignments from their parents in the resources hierarchy. If a project is not assigned to a reservation, it inherits the assignment of the parent folder or organization, if any.

Projects use the single most specific reservation in the resource hierarchy to which they are assigned. A folder assignment overrides an organization assignment, and a project assignment overrides a folder assignment.

If a project doesn't have an assigned or inherited reservation, then the job uses on-demand pricing. For more information about the resource hierarchy, see Organizing BigQuery resources.

Resources can be assigned to None to represent an absence of an assignment. Projects that are assigned to None always use on-demand pricing. A common use case for None assignments is to assign an organization to a reservation and then use None to opt certain projects or folders out of that reservation. For more information, see Assign a project to None.

When you create an assignment, you specify the job type for that assignment:

  • BACKGROUND: Use this reservation when you choose to use your own reservation to run your BigQuery search index management jobs, BigQuery change data capture (CDC), or BigLake metadata caching background jobs. Also use this reservation when you replicate source databases to BigQuery with Datastream's background apply operations. BACKGROUND reservations are not available in the Standard edition.

  • CONTINUOUS (Preview): Use this reservation for continuous query jobs. To use continuous queries, you must enroll in the feature preview.

  • ML_EXTERNAL: Use this reservation for BigQuery ML CREATE MODEL queries that use services that are external to BigQuery. For more information, see Assign slots to BigQuery ML workloads. ML_EXTERNAL reservations are not available in the Standard edition.

  • PIPELINE: Use this reservation for load and extract jobs.

    By default, load and extract jobs are free and use a shared pool of slots. BigQuery does not guarantee capacity availability for this shared pool or the throughput you see. If you are loading large amounts of data, your job might wait for slots to become available. In that case, you might want to purchase dedicated slots and assign PIPELINE jobs to them. As a best practice, create an additional, dedicated reservation with Ignore idle slots enabled. For more information about idle slots, see Idle slots.

    When load and extract jobs are assigned to a reservation, they lose access to the free pool. You should Monitor resource utilization and jobs to ensure your reservations have enough capacity to perform better than using the free pool.

  • QUERY: Use this reservation for non-continuous query jobs, including SQL, DDL, DML, and BigQuery ML queries.

You can't allocate slots to specific assignments. The BigQuery scheduler handles slot allocation for assignments in a reservation. For more information about how slots are used, see Slot allocation within a reservation.

Understand workload management

BigQuery Reservations are organization-scoped resource that are typically owned by a project but can be used by other projects within the organization. You purchase reservations centrally to be used across your entire organization. You can require individual departments or divisions to manage their reservations by purchasing committed capacity and assigning that capacity to 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 department and manage reservations for that department 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.

Administer organization workloads

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.

As a best practice, create 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 assigned to that project.

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

As a best practice, create one administration project for all reservations. Using a single administration project simplifies how you manage billing and allocate slots. You should only enable the BigQuery Reservations API on the administration project so that all commitments are managed through this project.

Workload and department management

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 advantage that it can 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.

Commitments delete.

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 can't be used in any other region. Single-region locations don't match multi-region locations, even where the single-region location is contained within the multi-region location. For example, you can't use a reservation in the EU multi-region to run a job in europe-west1. Projects, folders, and organizations can all be assigned to reservations in one region and run on- demand in another. To manage reservations in another region, do the following:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the navigation menu, click Capacity Management.

  3. In the Location list, select a region where you want to manage reservations.

    After you select a region, you can create reservations, create commitments, and assign a project to a reservation.

Commitments

A capacity commitment is a purchase of slots for a specified period of time. Slots can be purchased in 100 slot increments, up to your slot quota. Capacity commitments are optional, but can provide cost savings for steady-state workloads. There is no limit on the number of commitments that you can create. You are charged from the moment that your commitment purchase is successful. For current pricing information, see capacity commitment pricing.

  • Three-year commitment. You purchase a three year commitment. You can choose whether to renew or convert to a different type of commitment plan after 3*365 days.

  • Annual commitment. You purchase a 365-day commitment. You can choose whether to renew or convert to a different type of commitment plan after 365 days.

At the end of the commitment period, your commitment will renew based on the selected renewal plan.

You are billed monthly for annual or three-year commitment plans. However, your financial commitment is for the entire commitment period and can't be canceled on a monthly basis. Your usage is updated in the billing report daily, and can be viewed at any time.

Slot commitments are subject to capacity availability. When you attempt to purchase slot commitments, the success of your purchase is not guaranteed. However, once your commitment purchase is successful, the capacity will be available until the commitment expires.

Renew commitments

You select a renewal plan when you purchase a commitment. You can change the renewal plan for a commitment at any time until it expires. The following renewal plans are available:

  • None. After the end of the commitment period, the commitment is removed. Reservations are not affected.
  • Annual. After the end of the commitment period, your commitment renews for another year.
  • Three-Year. After the end of the commitment period, your commitment renews for another three years.

For information about purchasing and renewing commitments, see Create a capacity commitment.

For example, if you purchased an annual commitment at 6:00 PM on October 5, 2019, then you started being charged at that second. You can delete or renew the commitment after 6:00 PM on October 4, 2020, noting that 2020 is a leap year. You can change the renewal plans prior to 6:00 PM on October 4, 2020 as follows:

  • If you choose to renew to an annual commitment, then at 6:00 PM on October 4, 2020, your commitment renews for another year.
  • If you choose to renew to a three-year commitment, then at 6:00 PM on October 4, 2020, your commitment renews for three years.

Note: The renewal process can take up to roughly one hour after the commitment expires. For example, if a commitment expires at 6:00 PM on October 4, 2020, the renewed commitment record appears in the system between around 6:00 PM and 7:00 PM on October 4, 2020. No on-demand charges are applied within this data update period as the effective start time for the created commitment is 6:00 PM.

Commitment expiration

After you create a commitment, it can be deleted only once the commitment expires. To delete an annual or three-year commitment, set its renewal plan to NONE. After the commitment expires, it is deleted automatically. To learn more about commitment expirations, see Commitment expiration.

If you accidentally purchase a commitment or made a mistake when you configured your commitment, contact Cloud Billing Support for help.

What's next