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.

Reservation assignments can be configured at different levels:

• On-demand (default): Billed per data processed
• Project-level assignment: Billing project assigned to a reservation
• Query-level assignment: Query with specific reservation (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
• 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 admin 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 above to increase/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. We consider overall project usage patterns and potential savings by switching between On-Demand and Editions models with project-level reservation assignments.

DAG Node-Level Reservation Assignment

Flexible reservation assignment at the DAG node level delivers significant savings with minimal effort. We offer 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

Recommendations are based 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.

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 our 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