Back to Blog

BigQuery x dbt — setting up

Steve Pisani

So you know what dbt is, you have some data in BigQuery and your keyboard is beckoning you to type some SQL.

In this post, I will be showing you how to install and run dbt from your local machine from the command-line interface (CLI). This is also called dbt Core. In addition to this, you may run dbt from a web-based application called dbt Cloud. I won’t be showing that here.

Let’s get you started.

If you need a refresher on what dbt is, check out my last post, the official dbt website, or some pointed articles from Fishtown Analytics (how to set it up) (what it is exactly).

First, create a project or connect to your existing project on BQ (BigQuery) by:

  1. heading go over to the BQ console →
  2. creating a new project 👇
Create Google Cloud Project

You should have a fresh new project now; looking something like this 👇

Fresh New Project in BigQuery

Great! Now let’s get you connected to the project. You have a few options here, which dbt kindly lists here. I will show you how to implement OAuth via google’s gcloud CLI tool.

Setup OAuth Profile & gcloud Configuration

First, make sure that your account has the proper IAM permissions for BigQuery access.

Note: If you created the project in the previous steps you should be good to go with permissions implicitly. The following step will explicitly add permissions.

Navigate to menu → IAM & Admin → IAM. Once here, add BigQuery Admin to your account’s role.

Now, install google-cloud-sdk

brew install --cask google-cloud-sdk

Once dbt is installed you will need to set up a profile to connect to your BigQuery projects. The following is an example of the setup for one project.

echo "
<profile_name>:
 outputs:
   dev:
     type: bigquery
     method: oauth
     project: <google-project-name>
     dataset: <dbt_your_name>
     threads: 5
     timeout_seconds: 300
     location: US
     priority: interactive
     maximum_bytes_billed: 1000000000000
 target: dev" >> ~/.dbt/profiles.yml

log in to your google account via gcloud

gcloud config configurations create <profile_name>
gcloud config set project <google-project-name>
gcloud auth application-default login

🎉 Now you’ve got a BigQuery instance ready for all kinds of dbt’ing.

dbt is, under the hood, a python package so you are going to need a few things before you can get it to run. Assuming you are on a mac, you can get everything you need via the following commands:

brew update
brew install git
brew tap fishtown-analytics/dbt
brew install dbt

To test your installation, run

dbt --version

Finally, you are ready to run

dbt init <your_project_name>

which will generate a skeleton for your project in the directory you run it in.

There you have it. You’re ready to go create, test, and document your data in BigQuery through dbt.

Feel free to follow along with the CLI suggestions (seen in the previous image and/or your terminal) to get up and running or check out my next post in which I will go through the exact commands needed for said creating, testing, and documenting.

✌️Steve

P.S. Want to set up on a warehouse other than BigQuery? Say Redshift or Snowflake? Message me here and let me know which you would like another post around. Or, if you just have to get your dbt on right now, message me and I can run you through it 👌

If you’d like to hop on a call to discuss your data warehouse with a Flywheel engineer, just shoot us a note at solutions@flywheelsoftware.com.

Share on social media: 

More from the Blog

The Monthly Wheeler: September 2021

The easiest way to Activate your Customer Data: September Release Notes

Read Story

The Monthly Wheeler: August 2021

The easiest way to Activate your Customer Data: August Release Notes

Read Story

The Monthly Wheeler: May 2021

The easiest way to Activate your Customer Data: May Release Notes

Read Story

Looking for guidance on your Data Warehouse?

Get in touch with a Flywheel Engineer to chat about activating your data