BigQuery targets can be specified using one of three methods:
- a service account file
- service account json
For local development, we recommend using the oauth method. If you're scheduling dbt on a server, you should use the service account auth method instead.
BigQuery targets should be set up using the following configuration in your
# Note that only one of these targets is requiredmy-bigquery-db:target: devoutputs:dev:type: bigquerymethod: oauthproject: [GCP project id]dataset: [the name of your dbt dataset] # You can also use "schema" herethreads: [1 or more]timeout_seconds: 300location: US # Optional, one of US or EUpriority: interactiveretries: 1
Service Account File Authentication
my-bigquery-db:target: devoutputs:dev:type: bigquerymethod: service-accountproject: [GCP project id]dataset: [the name of your dbt dataset]threads: [1 or more]keyfile: [/path/to/bigquery/keyfile.json]timeout_seconds: 300priority: interactiveretries: 1
Service Account JSON Authentication
This authentication method is only recommended for production environments where using a Service Account Keyfile is impractical.
my-bigquery-db:target: devoutputs:dev:type: bigquerymethod: service-account-jsonproject: [GCP project id]dataset: [the name of your dbt dataset]threads: [1 or more]timeout_seconds: 300priority: interactive# These fields come from the service account json keyfilekeyfile_json:type: xxxproject_id: xxxprivate_key_id: xxxprivate_key: xxxclient_email: xxxclient_id: xxxauth_uri: xxxtoken_uri: xxxauth_provider_x509_cert_url: xxxclient_x509_cert_url: xxx
To connect to BigQuery using the
oauth method, follow these steps:
- Make sure the
gcloudcommand is installed on your computer
- Activate the application-default account with
gcloud auth application-default login \--scopes=https://www.googleapis.com/auth/userinfo.email,\https://www.googleapis.com/auth/cloud-platform,\https://www.googleapis.com/auth/drive.readonly
A browser window should open, and you should be promoted to log into your Google account. Once you've done that, dbt will use your oauth'd credentials to connect to BigQuery!
This command uses the
--scopes flag to request access to Google Sheets. This makes it possible to transform data in Google Sheets using dbt. If your dbt project does not transform data in Google Sheets, then you may omit the
priority for the BigQuery jobs that dbt executes can be configured with the
priority configuration in your BigQuery profile. The
priority field can be set to one of
interactive. For more information on query priority, consult the BigQuery documentation.
BigQuery supports query timeouts. By default, the timeout is set to 300 seconds. If a dbt model takes longer than this timeout to complete, then BigQuery may cancel the query and issue the following error:
Operation did not complete within the designated timeout.
To change this timeout, use the
timeout_seconds option shown in the BigQuery profile configuration above.
retries profile configuration designates the number of times dbt should retry queries that result in unhandled server errors. This configuration is only specified for BigQuery targets. Example:
# This example target will retry BigQuery queries 5# times with a delay. If the query does not succeed# after the fifth attempt, then dbt will raise an errormy-profile:target: devoutputs:dev:type: bigquerymethod: oauthproject: abc-123dataset: my_datasetretries: 5
The location of BigQuery datasets can be configured using the
location configuration in a BigQuery profile. Example:
my-profile:target: devoutputs:dev:type: bigquerymethod: oauthproject: abc-123dataset: my_datasetlocation: US # Optional, one of US or EU
Maximum Bytes Billed
maximum_bytes_billed value is configured for a BigQuery profile,
queries executed by dbt will fail if they exceed the configured maximum bytes
threshhold. This configuration should be supplied as an integer number
my-profile:target: devoutputs:dev:type: bigquerymethod: oauthproject: abc-123dataset: my_dataset# If a query would bill more than a gigabyte of data, then# BigQuery will reject the querymaximum_bytes_billed: 1000000000
Database Error in model debug_table (models/debug_table.sql)Query exceeded limit for bytes billed: 1000000000. 2000000000 or higher required.compiled SQL at target/run/bq_project/models/debug_table.sql
Service Account Impersonation
To use this functionality, first create the service account you want to
impersonate. Then grant users that you want to be able to impersonate
this service account the
roles/iam.serviceAccountTokenCreator role on
the service account resource. Then, you also need to grant the service
account the same role on itself. This allows it to create short-lived
tokens identifying itself, and allows your human users (or other service
accounts) to do the same. More information on this scenario is available
Once you've granted the appropriate permissions, you'll need to enable
the IAM Service Account Credentials API.
Enabling the API and granting the role are eventually consistent operations,
taking up to 7 minutes to fully complete, but usually fully propagating within 60
seconds. Give it a few minutes, then add the
option to your BigQuery profile configuration:
my-profile:target: devoutputs:dev:type: bigquerymethod: oauthproject: abc-123dataset: my_datasetimpersonate_service_account: email@example.com
For a general overview of this process, see the official docs for Creating Short-lived Service Account Credentials.
BigQuery's permission model is dissimilar from more conventional databases like Snowflake and Redshift. The following permissions are required for dbt user accounts:
- BigQuery Data Editor
- BigQuery User
This set of permissions will permit dbt users to read from and create tables and views in a BigQuery project.