dbt-bigquery

Warnings

• breaking Starting with dbt Core v1.8, installing a dbt adapter (like `dbt-bigquery`) no longer automatically installs `dbt-core`. You must explicitly install both `dbt-core` and `dbt-bigquery` to ensure a working environment.
  Fix: Always install with `pip install dbt-core dbt-bigquery`.
• gotcha The `job_execution_timeout_seconds` configuration (formerly `timeout_seconds` in older versions) in `profiles.yml` is critical to prevent BigQuery queries from running indefinitely and incurring high costs. BigQuery's default behavior may not impose a strict timeout.
  Fix: Explicitly set `job_execution_timeout_seconds` in your `profiles.yml` for all targets (e.g., `job_execution_timeout_seconds: 300` for a 5-minute timeout).
• gotcha BigQuery Python models and BigQuery DataFrames require additional IAM roles beyond basic BigQuery User/Data Editor, including BigQuery Job User, BigQuery Read Session User, Notebook Runtime User, Code Creator, and `colabEnterpriseUser`.
  Fix: Ensure your service account or user credentials have all necessary BigQuery DataFrames-related IAM roles granted in GCP.
• gotcha Python 3.14 is currently not supported for `dbt-bigquery`. Users should use Python versions 3.13.x or older.
  Fix: Use a compatible Python version (e.g., Python 3.10, 3.11, 3.12, 3.13).
• gotcha Using `LIMIT 0` with `INSERT INTO` statements in BigQuery does not optimize the query and can still result in the scanning of the entire source table, leading to unexpected billing costs.
  Fix: Be aware of this BigQuery behavior when attempting 'dry run' inserts. Consider alternative validation methods for large tables.
• deprecated With dbt Core v1.11, default warnings for non-standard YAML configurations (e.g., misspelled config keys, invalid top-level properties) are now enabled. This can expose previously overlooked issues in `dbt_project.yml` or model configurations.
  Fix: Review dbt logs for new warnings and correct any non-standard or deprecated configurations in your dbt project files.

Install

• pip install dbt-core dbt-bigquery Install dbt-bigquery and dbt-core

Imports

• dbt-bigquery adapter is used via dbt CLI, not direct Python imports

  dbt run

  dbt-bigquery is an adapter plugin for dbt Core. Users primarily interact with it through the dbt Command Line Interface (CLI) and YAML configuration files (profiles.yml, dbt_project.yml), rather than direct Python import statements in their models or scripts.

Quickstart

This quickstart demonstrates how to set up a dbt project to connect to Google BigQuery using a service account. It involves creating a `profiles.yml` file, initializing a dbt project, creating a simple SQL model, and then running dbt commands. Ensure you have a Google Cloud Project with BigQuery enabled and a service account key file.

# Create a profiles.yml file in ~/.dbt/ or your dbt project directory.
# Ensure GCP_PROJECT_ID and GOOGLE_APPLICATION_CREDENTIALS (path to service account JSON) are set as environment variables.

# ~/.dbt/profiles.yml
# ---
# my_bigquery_project:
#   target: dev
#   outputs:
#     dev:
#       type: bigquery
#       method: service-account
#       project: "{{ env_var('GCP_PROJECT_ID', 'your-gcp-project-id') }}"
#       dataset: "dbt_dev_dataset" # This dataset must exist in BigQuery
#       keyfile: "{{ env_var('GOOGLE_APPLICATION_CREDENTIALS', 'path/to/your/service-account.json') }}"
#       threads: 4
#       location: US # Or your BigQuery dataset location (e.g., EU, asia-northeast1)
#       job_execution_timeout_seconds: 300
# ---

# 1. Initialize a new dbt project (if you don't have one):
# dbt init my_new_dbt_project
# cd my_new_dbt_project

# 2. Edit dbt_project.yml to reference your profile (e.g., 'my_bigquery_project'):
# name: 'my_new_dbt_project'
# version: '1.0.0'
# config-version: 2
# profile: 'my_bigquery_project' # Must match a profile name in profiles.yml

# 3. Create a sample SQL model: models/my_first_model.sql
# ---
# -- models/my_first_model.sql
# SELECT
#     current_timestamp() as current_time,
#     'Hello dbt-bigquery!' as message
# ---

# 4. Set environment variables (replace with your actual values):
# export GCP_PROJECT_ID="your-gcp-project-id"
# export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/service-account.json"

# 5. Run dbt commands:
# dbt debug   # To verify your connection to BigQuery
# dbt run     # To execute the model and create a view/table in BigQuery