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. We'll look at one example of adjusting the Looker jobs to a use a reservation resources, but there are many other tools.

Editing Billing Project ID in the BigQuery connection can run the jobs in a dedicated project which is either assigned to a reservation or uses on-demand capacity.

The service account will require BigQuery Job User and Service Usage Consumer roles in this project.