How to Configure dbt for BigQuery: Setup Guide

To configure dbt for BigQuery, create a profiles.yml file with your BigQuery project, dataset, and authentication details. Then, set the target to use BigQuery and run dbt commands to build your models.

Syntax

The profiles.yml file defines how dbt connects to BigQuery. Key parts include:

• type: Set to bigquery to specify the adapter.
• project: Your Google Cloud project ID.
• dataset: The BigQuery dataset where dbt will create tables.
• method: Authentication method, e.g., service-account.
• keyfile: Path to your service account JSON key file.
• threads: Number of parallel threads dbt can use.
• timeout_seconds: Optional query timeout.
• location: BigQuery dataset location.

yaml

my_bigquery_project:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account
      project: your-gcp-project-id
      dataset: your_dataset_name
      keyfile: /path/to/your/service-account.json
      threads: 4
      timeout_seconds: 300
      location: US

Example

This example shows a complete profiles.yml setup for dbt with BigQuery using a service account key. Replace placeholders with your actual project ID, dataset, and keyfile path.

yaml

my_bigquery_project:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account
      project: my-gcp-project-123
      dataset: analytics_db
      keyfile: /Users/me/keys/my-service-account.json
      threads: 2
      timeout_seconds: 300
      location: US

Common Pitfalls

• Incorrect keyfile path: Make sure the keyfile path is absolute and points to a valid JSON file.
• Missing permissions: The service account must have BigQuery Data Editor and Job User roles.
• Wrong dataset name: Dataset must exist in BigQuery or dbt will error.
• Location mismatch: The location must match your dataset's region.

yaml

Wrong example:
my_bigquery_project:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account
      project: my-gcp-project-123
      dataset: wrong_dataset
      keyfile: ./wrong-path.json
      threads: 2
      location: US

Right example:
my_bigquery_project:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account
      project: my-gcp-project-123
      dataset: analytics_db
      keyfile: /Users/me/keys/my-service-account.json
      threads: 2
      location: US

Quick Reference

Field	Description	Example
type	Adapter type for dbt	bigquery
method	Authentication method	service-account
project	Google Cloud project ID	my-gcp-project-123
dataset	BigQuery dataset name	analytics_db
keyfile	Path to service account JSON key	/path/to/key.json
threads	Number of parallel threads	4
timeout_seconds	Query timeout in seconds	300
location	BigQuery dataset location	US

Key Takeaways

Create a profiles.yml file with BigQuery connection details to configure dbt.

Use service-account authentication with a valid JSON key file for secure access.

Ensure the dataset exists and the location matches your BigQuery dataset region.

Check permissions for the service account to avoid authorization errors.

Set threads and timeout_seconds to optimize dbt performance on BigQuery.