Reservation Assignments

BigQuery offers different compute models that impact access to various compute features and directly affect costs. Masthead analyzes usage to determine the optimal model among On-demand, Standard, and Enterprise editions.

You can configure reservation assignments at different levels:

• On-demand: Billed per data processed. This is the default.
• Project-level assignment: Billing project assigned to a reservation
• Query-level assignment: Query with specific reservation. This overrides project settings.

These levels provide flexibility to optimize compute models across your workloads. Read more about combining different compute billing models.

Reservation assignment levels

Masthead provides recommendations to optimize compute costs through reservation assignments at multiple levels:

• Project-level: All workloads in a project use assigned reservations
• Principal-level: Specific service accounts or user identities assigned to dedicated reservations or routed to on-demand
• DAG node-level: Flexible assignment for individual dbt or Dataform models
• Pipeline-level: Individual pipelines assigned to specific reservations

Before applying changes, Masthead ensures your workloads are compatible with the target compute model and suggests the reservation capacity adjustments.

Creating or adjusting a reservation for the migrated workload

1. If you didn’t have a reservation, create one in the BigQuery administration project:

CREATE RESERVATION `{BQ_ADMIN_PROJECT}.region-{region_id}.{RESERVATION_ID}` OPTIONS (
   edition = STANDARD,
   slot_capacity = 0,
   autoscale_max_slots = {estimated_slot_capacity}
);

If your current workload already uses reservations, you’ll need to adjust the reservation capacity as shown in the preceding code block to increase or decrease the capacity for an existing reservation.

Project-level reservation assignment

Masthead analyzes and aggregates all compute workload running within a project to recommend the most cost-effective compute model. Masthead considers overall project usage patterns and potential savings when switching between On-Demand and Editions models with project-level reservation assignments.

Principal-level reservation assignment

When multiple services, applications, and teams share a single BigQuery project, assigning the entire project to an Edition can be suboptimal. Interactive users might experience slot queuing during large batch transformations, or lightweight SQL queries might incur unnecessary reservation autoscale costs.

Masthead analyzes compute usage by caller identity to identify service accounts (such as ETL workers or BI connectors) and user identities that run consistent, compute-heavy jobs. By assigning these specific principals to dedicated reservations or explicitly routing them back to On-Demand you can isolate compute workloads without restructuring projects or rewriting pipelines.

Prerequisites

Before assigning a principal to a reservation, ensure the identity has permission to use the reservation (bigquery.reservations.use). Grant the roles/bigquery.resourceEditor or roles/bigquery.user role to the target principal on the reservation resource in your BigQuery administration project.

Implementation methods

BigQuery SQL DDL

BigQuery natively supports managing principal assignments using SQL DDL statements executed in the administration project.

Assign a principal to a reservation:

CREATE ASSIGNMENT `{BQ_ADMIN_PROJECT}.region-{region_id}.{RESERVATION_ID}.{ASSIGNMENT_NAME}`
OPTIONS (
  assignee = 'projects/{SOURCE_PROJECT_ID}',
  job_type = 'QUERY',
  principal = 'principal://iam.googleapis.com/projects/-/serviceAccounts/{SERVICE_ACCOUNT_EMAIL}'
);

Note

For the principal and job_type formats please refer to BigQuery documentation.

Route a principal to on-demand compute:

To ensure a specific principal bypasses project-level reservations and uses on-demand billing, assign the principal to none:

CREATE ASSIGNMENT `{BQ_ADMIN_PROJECT}.region-{region_id}.none.{ASSIGNMENT_NAME}`
OPTIONS (
  assignee = 'projects/{SOURCE_PROJECT_ID}',
  job_type = 'QUERY',
  principal = 'principal://iam.googleapis.com/projects/-/serviceAccounts/{SERVICE_ACCOUNT_EMAIL}'
);

gcloud & bq CLI

You can also manage principal reservation assignments using the bq command-line tool:

Create a principal assignment:

bq mk --reservation_assignment \
  --project_id={BQ_ADMIN_PROJECT} \
  --location={region_id} \
  --reservation_id={RESERVATION_ID} \
  --job_type=QUERY \
  --assignee_type=PROJECT \
  --assignee_id={SOURCE_PROJECT_ID} \
  --principal="principal://iam.googleapis.com/projects/-/serviceAccounts/{SERVICE_ACCOUNT_EMAIL}"

DAG node-level reservation assignment

Flexible reservation assignment at the DAG node level delivers significant savings with minimal effort. Masthead provides packages for common orchestration technologies to assign reservations to individual models.

How Masthead recommendations work

Masthead analyzes your workload and provides intelligent categorization for each model/action:

• Editions: Large data volume operations requiring consistent compute
• On-Demand: Computation-intensive operations with variable resource needs

Masthead bases recommendations on:

• Usage Patterns: Historical query performance and frequency
• Cost Analysis: BigQuery slot usage and billing data
• Resource Availability: Current reservation capacity and utilization

You can review recommendations manually in Compute Cost Insights or automate the process using programmatic insights access. To learn how Masthead models workload capacity, queue depths, and projected savings before making recommendations, see Compute workload simulation.

Implementation by orchestration tool

dbt

Prerequisites:

Enable dbt job labels to track compute usage per model. Add to your dbt_project.yml:

query-comment:
  job-label: True

Quick Start:

1. Add the package to packages.yml:

packages:
  - package: masthead-data/bq_reservations
    version: 0.0.2 # Use the latest version

1. Install: dbt deps

2. Add to your model:

{{
  config(
    materialized='table',
    sql_header=bq_reservations.assign_from_config()
  )
}}

SELECT * FROM {{ ref('source_table') }}

1. Configure reservations using the RESERVATION_CONFIG variable based on Masthead recommendations

For detailed instructions, configuration examples, and troubleshooting, see the dbt-reservations GitHub repository.

Dataform

Assign reservations at the Dataform action level using the Masthead Dataform package.

Quick Start:

Install the package and configure reservation assignments based on Masthead recommendations provided in the RESERVATION_CONFIG object.

For detailed instructions, configuration examples, and troubleshooting, see the dataform-package GitHub repository.

Airflow

Prerequisites:

Ensure your Airflow DAGs use BigQuery operator labels to track compute usage. Airflow’s BigQuery operators automatically add default labels when properly configured.

Quick Start:

Override reservation assignments at the query level using BigQuery’s SET @@reservation statement:

reservation_id = 'RESERVATION_ID'
sql_with_overridden_reservation = f"""SET @@reservation='{reservation_id}';
{your_sql}
"""
BigQueryInsertJobOperator(
    task_id="run_query",
    configuration={
        "query": {
            "query": sql_with_overridden_reservation
        }
    },
    ...
)

For more advanced usage, configuration patterns, and examples, see the airflow-reservations GitHub repository.

Monitoring and optimization

To ensure optimal performance across all orchestration tools:

• Monitor performance: Track BigQuery job execution and costs in real-time
• Review assignments: Regularly check model/action assignments in Compute Cost Insights
• Adjust allocations: Fine-tune reservation assignments based on usage patterns and cost analysis
• Get support: Contact Masthead support for configuration optimization assistance

Pipeline-level reservation assignment

Masthead provides a granular view of compute costs, grouped by pipeline. This enables optimization through pipeline-level reservation assignments, migrating pipelines between BigQuery Editions and On-Demand models based on slot-consumption and usage patterns.

Recommendation:

Review pipeline-specific recommendations on Compute Cost Insights page. It shows current and alternative costs achievable by switching the compute model for each pipeline.

Alternative model plan for a pipeline