How to configure CI to build in a separate BigQuery project

Archive
1

Problem: when doing continuous integration testing on a BigQuery database, dbt Cloud will, by default, create datasets in your production BigQuery environment, prefixed with dbt_cloud_pr.

Solution: create a new, separate BigQuery project for CI builds and instruct dbt Cloud to build into this new BigQuery project instead when running the CI build job following a pull request.

  1. Create a new Google Cloud Platform project and enable billing.

  2. Add your existing production dbt Cloud BigQuery service account to the new GCP project; assign BigQuery Editor permissions to this existing account in the new project.

  3. Create a CI environment in your existing dbt Cloud project.

  4. Create a CI job in the CI environment
    a. Environment target name: set the target name to dbt_cicd (or whatever you like, e.g. your new GCP project’s name)
    b. Execution settings: defer to previous run state, choose a frequently-run production job. You might also want to set a run timeout; I used 3600 seconds to time out after 1 hour.
    b. Commands to execute: dbt build --select state:modified+
    d. Triggers: In the Webhooks tab, turn on “Run on Pull Requests”

  5. Add target-name logic to your project’s dbt_project.yml
    More details: Using custom databases | dbt Docs

    models:
  <your-resource-path-here>:
    +database:  "{% if target.name == 'dbt_cicd' %}your-new-gcp-project{% else %}{{ target.database }}{% endif %}"
2 Likes
2

Now that I’ve added seeds to my dbt project, I found I also needed to target the correct project when building seeds, too. More to add to dbt_project.yml:

seeds:
  <your-resource-path-here>:
      +database:  "{% if target.name == 'dbt_cicd' %}your-new-gcp-project{% else %}{{ target.database }}{% endif %}"
1 Like
3

What a great, easy to follow, tip. Thanks! Exactly what I was looking for.