DBT reservations

Overview

Model-level assignment optimizes BigQuery resource usage by assigning compute reservations to dbt models based on Masthead recommendations. This system enables businesses to efficiently manage their BigQuery resource allocation and optimize costs with minimal manual intervention.

Key Benefits

• Cost optimization: Automatically route high-compute workloads to reserved slots and high-data volume workloads to on-demand capacity

• Automated reassignment: Once configured, reservations are applied automatically based on model categorization

Prerequisites

Add default DBT labels to your BigQuery jobs to enable Masthead to track and analyze compute usage per dbt model. Follow the DBT BigQuery query-comment documentation to set up the following labels:

# dbt_project.yml

query-comment:
  job-label: True

Installation

Add the package to your packages.yml:

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

Then run:

dbt deps

Usage Example

Model Configuration

Add the pre-hook to your dbt model:

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

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

Reservations Configuration

The reservation configuration object defines how models are assigned to reservations:

# dbt_project.yml or profiles.yml
vars:
  RESERVATION_CONFIG:
    - tag: 'editions'
      reservation: 'projects/{project}/locations/{location}/reservations/{name}'
      models:
        - 'model.project.table'
        - 'model.project.another_table'
    - tag: 'on_demand'
      reservation: 'none'
      models:
        - 'model.project.model_name'

Masthead provides the RESERVATION_CONFIG with flexible model categorization:

• Editions: Large data volume operations requiring consistent compute

• On-Demand: Computation-intensive operations with variable resource needs

You can review it regularly manually or develop an automated process using programmatic insights access.

The recommended compute model is estimated based on:

• Usage Patterns: Historical query performance and frequency

• Cost Analysis: BigQuery slot usage and billing data

• Resource Availability: Current reservation capacity and utilization

Monitoring and Optimization

To ensure optimal performance:

• Monitor performance: Track BigQuery job execution and costs in real-time

• Review categorization: Regularly check model assignments in Compute Cost Insights

• Adjust allocations: Fine-tune reservation assignments based on usage patterns and cost analysis

Troubleshooting

If you encounter issues with reservation assignment:

1. Check compilation: Verify dbt compilation completes without errors (dbt compile)

2. Validate configuration: Ensure model names in RESERVATION_CONFIG match exactly

3. Monitor assignments: Review BigQuery job details to confirm reservation usage

4. Get support: Contact Masthead support for configuration optimization assistance

5. Override assignments: Pass RESERVATION_CONFIG directly via command line:

   dbt run --select MODEL_NAME --vars '{"RESERVATION_CONFIG": [{"reservation": "projects/BQ_ADMIN_PROJECT/locations/us/reservations/RESERVATION_NAME", "models": ["model.DBT_PROJECT.MODEL_NAME"]}]}'