(Applies to dbt v2.0 and later)

Connect BigQuery to Fusion Preview

You can configure the BigQuery adapter by running dbt init in your CLI or manually providing the profiles.yml file with the fields configured for your authentication type.

The BigQuery adapter for Fusion supports the following authentication methods:

• Service account (JSON file)
• gcloud OAuth
• Workload Identity Federation (Microsoft Entra)

Warehouse permissions

The Google Cloud identity (service account or user) that dbt Fusion engine uses must have IAM permissions to run jobs, read and write table data, and read metadata Fusion uses for introspection and source freshness.

Required Google Cloud objects

Before connecting, these objects must exist or be accessible:

Object	BigQuery term	Purpose
Project	GCP project	Your Google Cloud project ID
Dataset	Dataset	Target dataset (equivalent to a schema)
Service account or user	IAM identity	Identity for authentication
Location or region	Location	Data location (for example, US, EU, us-east1)

IAM permissions

The following permissions are required for fundamental dbt features::

Permission	Purpose
bigquery.datasets.get	Access dataset metadata
bigquery.tables.create	Create tables
bigquery.tables.delete	Drop or replace tables
bigquery.tables.get	Read table metadata
bigquery.tables.getData	Read table data
bigquery.tables.list	List tables in dataset
bigquery.tables.update	Update table schema
bigquery.tables.updateData	Insert, update, or delete rows
bigquery.jobs.create	Run queries

The following are optional permissions for additional dbt features:

Permission	When required
bigquery.datasets.create	Auto-create datasets
bigquery.routines.create	Create UDFs or stored procedures
bigquery.jobs.get	Monitor job status
storage.objects.create	Python models (GCS staging)
dataproc.*	Python models on Dataproc

Predefined IAM roles

The following roles represent the typical starting point for dbt access:

Role	Description	Use case
roles/bigquery.dataEditor	Read and write tables in datasets	Standard dbt operations
roles/bigquery.user	Run jobs, create datasets	Job execution
roles/bigquery.jobUser	Run jobs only	Minimal query execution

For Storage Read API access with Fusion, also grant BigQuery Read Session User (roles/bigquery.readSessionUser) on the project, as noted in Connect BigQuery.

Metadata operations

The following are required for fundamental dbt features:

Query type	SQL or API used	Required permission
Get table schema	get_table_schema API	bigquery.tables.get
List relations	Query against dataset	bigquery.tables.list
Source freshness	Query __TABLES__ metadata	bigquery.tables.get
Get table stats	Query INFORMATION_SCHEMA	bigquery.tables.get
Create dataset	CREATE SCHEMA	bigquery.datasets.create

INFORMATION_SCHEMA and metadata views

Fusion queries these BigQuery system views:

View	Purpose	Scope
INFORMATION_SCHEMA.TABLES	List tables	Dataset or region
INFORMATION_SCHEMA.COLUMNS	Column metadata	Dataset or region
INFORMATION_SCHEMA.VIEWS	View definitions	Dataset or region
INFORMATION_SCHEMA.PARTITIONS	Partition information	Dataset only
__TABLES__ (deprecated)	Table modification times	Dataset

BigQuery DataFrames (optional)

For BigQuery DataFrames workflows, users typically need additional roles such as BigQuery Job User, BigQuery Read Session User, Notebook Runtime User, Code Creator, and colabEnterpriseUser. See your Google Cloud admin for exact role names in your organization.

Configure Fusion

Executing dbt init in your CLI will prompt for the following fields:

• Project ID: The GCP BigQuery project ID
• Dataset: The schema name
• Location: The location for your GCP environment (for example, us-east1)

Alternatively, you can manually create the profiles.yml file and configure the fields. See examples in authentication section for formatting. If there is an existing profiles.yml file, you have the option to retain the existing fields or overwrite them.

Next, select your authentication method. Follow the on-screen prompts to provide the required information.

Supported authentication types

Service account (JSON file)

Selecting the Service account (JSON file) authentication type will prompt you for the path to your JSON file. You can also manually define the path in your profiles.yml file.

Example service account JSON file configuration

profiles.yml

default:
  target: dev
  outputs:
    dev:
      type: bigquery
      threads: 16
      database: ABC123
      schema: JAFFLE_SHOP
      method: service-account
      keyfile: /Users/youruser/Downloads/CustomRoleDefinition.json
      location: us-east1
      dataproc_batch: null

gcloud OAuth

Prior to selecting this authentication method, you must first configure local OAuth for gcloud:

Local OAuth gcloud setup

1. Make sure the gcloud command is installed on your computer.
2. Activate the application-default account with:

gcloud auth application-default login \           
  --scopes=https://www.googleapis.com/auth/bigquery,\
https://www.googleapis.com/auth/drive.readonly,\
https://www.googleapis.com/auth/iam.test,\
https://www.googleapis.com/auth/cloud-platform

# 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 `--scopes` flag.

A browser window should open, and you should be prompted to log into your Google account. Once you've done that, dbt will use your OAuth'd credentials to connect to BigQuery.

Example gcloud configuration

profiles.yml

default:
  target: dev
  outputs:
    dev:
      type: bigquery
      threads: 16
      database: ABC123
      schema: JAFFLE_SHOP
      method: oauth
      location: us-east1
      dataproc_batch: null

Workload Identity Federation (Microsoft Entra)

Workload Identity Federation (WIF) lets you authenticate to BigQuery using credentials issued by an external OAuth identity provider — currently Microsoft Entra — without managing a Google service account key file.

Fusion exchanges an Entra-issued token for short-lived Google credentials through a Google Cloud workload identity pool.

Before selecting this method, an administrator must configure a workload identity pool and provider in GCP that trusts your Entra tenant. You'll need the resulting workload pool provider path.

Configuration fields

Here are the required and optional fields for the WIF config in your profiles.yml file:

Field	Required	Description
method	Yes	Set to external-oauth-wif.
workload_pool_provider_path	Yes	The full resource path of the GCP workload identity pool provider that trusts your Entra tenant.
token_endpoint.type	Yes	The external identity provider type. Only entra is currently supported.
token_endpoint.request_url	Yes	The Entra OAuth token endpoint.
token_endpoint.request_data	Yes	The URL-encoded request body used to fetch the Entra token (for example grant_type, client_id, client_secret, and scope).
service_account_impersonation_url	No	The generateAccessToken URL of a service account to impersonate after federation, if you want BigQuery access scoped to that service account's permissions.

WIF config example in profiles.yml

The following code snippet is an example of a WIF config in your profiles.yml. Make sure to replace the values with your own:

profiles.yml

default:
  target: dev
  outputs:
    dev:
      type: bigquery
      threads: 16
      database: ABC123
      schema: JAFFLE_SHOP
      method: external-oauth-wif
      location: us-east1
      workload_pool_provider_path: //iam.googleapis.com/projects/123/locations/global/workloadIdentityPools/my-pool/providers/my-provider
      token_endpoint:
        type: entra
        request_url: https://login.microsoftonline.com/TENANT_ID/oauth2/v2.0/token
        request_data: "grant_type=client_credentials&client_id={{ env_var('ENTRA_CLIENT_ID') }}&client_secret={{ env_var('ENTRA_CLIENT_SECRET') }}&scope=SCOPE/.default"
        
      # Optional: include only if your workload identity pool doesn't have direct resource access and needs to impersonate a service account.
      # service_account_impersonation_url: https://iamcredentials.googleapis.com/v1/projects/-/serviceAccounts/sa@project.iam.gserviceaccount.com:generateAccessToken

More information

Find BigQuery-specific configuration information in the BigQuery adapter reference guide.

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.