How to Connect BigQuery to dbt

Why connect BigQuery and dbt?

Running dbt on BigQuery lets teams version-control SQL, manage data tests, and deploy cost-efficient ELT pipelines without maintaining servers.

What credentials does dbt need?

dbt authenticates with a Google Cloud service-account JSON key that has at least BigQuery Data Editor and BigQuery Job User roles on the target project.

How do I create a service-account key?

In Google Cloud Console, create a new service account → grant BigQuery roles → generate a key → download the JSON file. Store it in a secure path or inject via an environment variable.

Where do I place the key for local runs?

Save the key file outside your repository, e.g., ~/.gcp/galaxy-bq-key.json. Set GOOGLE_APPLICATION_CREDENTIALS to that path so dbt and other tools find it automatically.

What does a BigQuery profile look like?

Add a bigquery target in ~/.dbt/profiles.yml. Specify project, dataset, location, and threads. Use method: service-account plus the key file path or rely on the env var.

Sample profiles.yml

galaxy_bq:
target: dev
outputs:
dev:
type: bigquery
method: service-account # or oauth
keyfile: ~/.gcp/galaxy-bq-key.json
project: galaxy-data-dev
dataset: analytics
threads: 4
location: US
timeout_seconds: 300


How do I test the connection?

Run dbt debug --target dev. A successful connection shows green checks for credentials, project access, and dataset reachability.

How do I model e-commerce tables?

Create a model like models/customer_ltv.sql that joins Customers, Orders, and OrderItems. Run dbt run to materialize it in BigQuery.

Example model

with o as (
select customer_id, sum(total_amount) as lifetime_value
from {{ ref('Orders') }}
group by customer_id
)
select c.id, c.name, c.email, o.lifetime_value
from {{ ref('Customers') }} c
left join o on c.id = o.customer_id;

Best practices for BigQuery + dbt

Set batch_size to control INSERT limits, use cost controls in GCP budgets, and leverage dbt incremental models to minimize slot usage.