Set up a BigQuery multi-project service account

Many teams keep raw data and transformed data in separate BigQuery projects. This guide creates a service account that can access multiple projects, then configures dbt™ to read sources from one project and write models to another.

Prerequisites

A Google Cloud account with administrative access.
Two or more Google Cloud projects with BigQuery enabled.

Create the service account

Create the account

In the Google Cloud Console, select your primary project, then go to IAM & Admin > Service Accounts and select Create service account. Name it (for example, bq-multi-project-sa) and select Create and continue.

Assign roles in the primary project

Add the BigQuery Data Editor and BigQuery Job User roles, then select Continue and Done.

Create and download a key

Open the service account, go to the Keys tab, select Add key > Create new key, choose JSON, and select Create. Store the downloaded key file securely; it grants access to your resources.

Grant access to additional projects

In each additional project, go to IAM & Admin > IAM, select Grant access, enter the service account email (bq-multi-project-sa@your-project-id.iam.gserviceaccount.com), and add the BigQuery Data Editor and BigQuery Job User roles. Repeat for every project the account needs.

The service account now appears in the IAM list of each project with the two BigQuery roles. Use its JSON key as the credentials for your BigQuery connection in Paradime.

Point dbt™ at source and target projects

With cross-project access in place, tell dbt™ which project to read from and which to write to. In BigQuery, the dbt™ database parameter is the project ID.

Reference external source projects

Set database on each source to the project that holds the raw data:

sources:
  - name: marketing_data
    database: raw-marketing-project-123  # source project ID
    schema: google_ads
    tables:
      - name: campaigns
      - name: ad_groups

  - name: sales_data
    database: raw-sales-project-456
    schema: transactions
    tables:
      - name: orders
      - name: line_items

Control where models are written

Set database and schema in a model’s config block to write it to a specific project:

{{
  config(
    materialized = 'table',
    schema = 'marketing_analytics',
    database = 'analytics-target-project-789',
    partition_by = {"field": "date_day", "data_type": "date"}
  )
}}

WITH campaign_data AS (
  SELECT * FROM {{ source('marketing_data', 'campaigns') }}
)

SELECT
  date_day,
  campaign_id,
  campaign_name,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks
FROM campaign_data
GROUP BY 1, 2, 3

Set default destinations per folder

For larger projects, set default destinations by model folder in dbt_project.yml:

dbt_project.yml

name: 'project_analytics'
version: '1.0.0'
config-version: 2

models:
  cross_project_analytics:
    database: analytics-target-project-789  # default target project

    staging:
      database: staging-project-567
      schema: stg
      materialized: view

    marts:
      marketing:
        database: marketing-analytics-project-345
        schema: marketing
      finance:
        database: finance-analytics-project-901
        schema: finance

The generate_database_name macro is standard dbt™ behavior: it uses the database you set in config, and otherwise falls back to your active target’s database.

Foundations

Automate with AI agents

Set up & configure

Orchestrate data pipelines

Migrate to Paradime

Develop in Paradime

Learn dbt™

Set up a BigQuery multi-project service account

Create the service account

Point dbt™ at source and target projects

Reference external source projects

Control where models are written

Set default destinations per folder

Next steps

BigQuery integration

Optional connection attributes

​Create the service account

​Point dbt™ at source and target projects

​Reference external source projects

​Control where models are written

​Set default destinations per folder

​Next steps

BigQuery integration

Optional connection attributes

Create the service account

Point dbt™ at source and target projects

Reference external source projects

Control where models are written

Set default destinations per folder

Next steps