Compute model adjustments

The compute model used by a query can be defined based on various configurations:

• default is the On-demand model,

• the project/folder or organization parent for the job may be assigned to a reservation, and then particular types of jobs are using the selected reservation,

• the query may have a reservation override defined.

Using these levels BigQuery offers a lot of flexibility to optimize the compute models across your queries.

Masthead introduces a new level of compute model configuration - technology. Different technologies often follow clear patterns of computational resources better suited for their workload. Masthead provides recommendations when a specific technology offers significant savings and can be optimized with minimal effort. For the most common technologies, we’ve compiled a list of actions to help you update the compute model across all jobs at the technology level.

Airflow

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
        }
    },
    ...
)

DBT

Use DBT pre-hooks to add the SQL statement updating the reservation to be used.

{{ config(
    pre_hook="SET @@reservation='RESERVATION_ID';",
    ...
) }}

SELECT ...

Project assignment scenario

Fully qualified references for datasets and tables are required (starting with project_id where the data resides). Otherwise BigQuery will be looking for the table in the billing project.

Many tools that connect to BigQuery run the jobs on your behalf, but don't give you the flexibility to manage the compute resources on a job level. That's where the project level assignment comes to help. Looker is a one example, but there are many other technologies with similar approach.

Here is an example migrating the workload from on-demand to a reservation:

1. Assign the new dedicated project to the reservation, setting job type to QUERY.

2. Assign BigQuery Job User and Service Usage Consumer roles to the currently used service account in the new project.

3. Set Billing Project ID of the BigQuery connection in the technology to the new project ID. If no Billing Project ID parameter is present, then verify the impact and adjust Project ID parameter instead.

Done! The queries run by this technology are now running in a new project and use the assigned reservation capacity by default.