Getting Started with dbt Core and Bigquery

Steven Johnson -

In this guide, we'll walk through how to setup dbt Core in the cloud with Bigquery. After you finish this guide, you'll have the sample data provided uploaded to Bigquery and run your first dbt command in the cloud.

Although the steps in this guide will specifically use Bigquery, the steps can be modified slightly to work with any database that dbt supports. We also have guides made specifically for Databricks, Redshift, and Snowflake.

If you'd rather watch a video version of this guide, feel free to head over to YouTube. Let's jump right in!

dbt Core Part 1 - Loading Sample Data into Bigquery

Before getting into the steps of setting up the sample data in Bigquery, please download the sample files that we will use for this tutorial here.

Setting up demo project​

  1. Navigate to the BigQuery Console
  2. Click to access the Project Browser on the top left corner of your screen.
  3. On the top right of the Project Browser, click New Project. This will redirect you to put information in about your new project
  4. Under Project Name, enter dbt-demos
  5. Organization and Location can stay at their default values.
  6. Click Create*.

Upload Data​

  1. Locate the dbt-demos project that we created on the left side bar.
  2. Click the 3 dots and choose Create dataset
  3. For Dataset ID, enter 538_football.
  4. Click Create Dataset.
  5. Expand the dbt-demos project on the left sidebar by clicking the arrow. Locate the 538_football dataset. Click the 3 dots and choose Create Table.
  6. Under Create Table from, choose Upload and choose spi_matches_latest.csv.
  7. Under File Format, choose CSV.
  8. Project and Dataset should automatically be set to dbt-demos and 538_football respectively.
  9. Under table, enter stg_football_matches
  10. Check the box for Auto Detect.

Next:

  1. Click Create Table.
  2. Repeat steps 6-12 with the second CSV file, however name the table stg_football_rankings.

You should be able to see the two tables you created under the 538_football dataset on the left sidebar.

dbt Core Part 2 - Setting Up dbt on Github

Fork dbt Setup from GitHub​

  1. Fork this repository. The repository contains the beginning state of a dbt project.
  2. Clone the repository locally on your computer.
  3. Open dbt_project.yml in your text editor.

dbt Project File Setup​

  1. Change the project name to soccer_538.
  2. Change the profile to soccer_538.
  3. Change model name to soccer_538.
  4. Under the soccer_538 model, add a staging and marts folder that are both materialized as views.
  5. Save your changes.

Profile Setup

  1. Open profiles.yml.
  2. Update the file to this:
soccer_538:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account
      project: dbt-demos # Replace this with your project id
      dataset: dbt_shipyard # Replace this with dbt_your_name, e.g. dbt_bob
      threads: 4
      timeout_seconds: 300
      location: US
      priority: interactive
      keyfile: "{{ env_var('BIGQUERY_KEYFILE') }}"

3.  Create a new file in your root directory of your dbt project called execute_dbt.py.

4.  Paste this code block for the content of execute_dbt.py:

import subprocess
import os
import json

bigquery_credentials = os.environ.get('BIGQUERY_CREDS')
directory_of_file = os.path.dirname(os.path.realpath(__file__))
dbt_command = os.environ.get('dbt_command', 'dbt run')

os.chdir(directory_of_file)
if not bigquery_credentials or not bigquery_credentials == 'None':
    bigquery_credentials = json.loads(bigquery_credentials)
    with open('bigquery_creds.json', 'w') as outfile:
        json.dump(bigquery_credentials, outfile)

subprocess.run(['sh', '-c', dbt_command], check=True)

5. Commit and push your changes to Github.

Now that we have our sample data and dbt processes setup, we need to write our example models for the dbt job to run.

dbt Models​

  1. Navigate into the models folder in your text editor. There should be a subfolder under models called example. Delete that subfolder and create a new folder called 538_football.
  2. Create two subfolders inside 538_football called staging and marts.

3.   Inside the staging folder, create a file called stg_football_matches.sql.

4.   Paste the following code into that file:

SELECT * FROM dbt-demos.538_football.stg_football_matches

5.  Inside the staging folder, create a file called stg_football_rankings.sql

6.  Paste the following code into that file:

SELECT * FROM dbt-demos.538_football.stg_football_rankings

7.  In the staging folder, add a file called schema.yml.

8.  In this file, paste the following information:

version: 2

models:
  - name: stg_football_matches
    description: Table from 538 that displays football matches and predictions about each match.

  - name: stg_football_rankings
    description: Table from 538 that displays a teams ranking worldwide

9.   In the marts folder, create a file called mart_football_information.sql.

10. Paste the following code into that file:

with
  qryMatches as (
    SELECT * FROM {{ ref('stg_football_matches') }} where league = 'Barclays Premier League'
    ),
  qryRankings as (
    SELECT * FROM {{ ref('stg_football_rankings') }} where league = 'Barclays Premier League'
  ),

  qryFinal as (
    select
      qryMatches.season,
      qryMatches.date,
      qryMatches.league,
      qryMatches.team1,
      qryMatches.team2,
      team_one.rank as team1_rank,
      team_two.rank as team2_rank
    from
      qryMatches join
      qryRankings as team_one on
        (qryMatches.team1 = team_one.name) join
      qryRankings as team_two on
        (qryMatches.team2 = team_two.name)
  )

select * from qryFinal

11.  In the marts folder, add a file called schema.yml

12.  In this file, paste the following:

version: 2

models:
  - name: mart_football_information
    description: Table that displays football matches along with each team's world ranking.

13. Save the changes.

14. Push a commit to Github.

We are ready to move into Shipyard to run our process. First, you will need to create a developer account.

dbt Core Part 3 - Setting Up dbt on Shipyard

Create Developer Shipyard Account​

  1. Navigate to Shipyard's sign-up page here.
  2. Sign up with your email address and organization name.
  3. Connect to your Github account by following this guide. After connecting your Github account, you'll be ready to create your first Blueprint.

Creating dbt Core Blueprint​

  1. On the sidebar of Shipyard's website, click Blueprints.
  2. Click Add Blueprint on the top right of your page.
  3. Select Python.
  4. Under Blueprint variables, click Add Variable.
  5. Under display name, enter dbt CLI Command.
  6. Under reference name, enter dbt_command.
  7. Under default value, enter dbt run.
  8. Click the check box for required
  9. Under placeholder, enter Enter the command for dbt.
  10. Click Next
  11. Click Git.
  12. Select the repository where your dbt files sit.
  13. Click the source that you want the files pulled from. Generally main or master.
  14. Under file to run, enter execute_dbt.py.
  15. Under Git Clone Location, select the option for Unpack into Current Working Directory.
  16. Click Next Step on the bottom right of the screen.
  17. Next to Environment Variable, click the plus sign to add an environment variable.

Add Environment Variables

Add the following environment variables with your Bigquery service credentials json inputted for the BQ credentials.

Variable NameValue
BIGQUERY_CREDSBQ credentials
BIGQUERY_KEYFILE./bigquery_creds.json
DBT_PROFILES_DIR.

Python Packages​

  1. Click the plus sign next to Python Packages.
  2. In the Name field, enter dbt-bigquery. In the version field, enter ==1.0.0.
  3. Click Next.

Blueprint Settings​

  1. Under Blueprint Name, enter dbt - Execute CLI Command.
  2. Under synopsis, enter This Blueprint runs a dbt core command.
  3. Click Save.
  4. In the top right of your screen, click Use this Blueprint. This will take you over to the Fleet Builder and prompt you to select a project.

Build dbt Core Fleet​

  1. On the Select a Project prompt, click the drop down menu to expand it and select Create a New Project.
  2. Under project name, enter dbt Core Testing.
  3. Under timezone, enter your timezone.
  4. Click Create Project.
  5. Select dbt Core Testing and click Select Project. This will create a new Fleet in the project. The Fleet Builder will now visible with one Vessel located inside of the Fleet.
  6. Click on the Vessel in the Fleet Builder and you will see the settings for the Vessel pop up on the left of your screen.
  7. Under Vessel Name, enter dbt Core CLI Command.
  8. Under dbt CLI Command, enter dbt debug.
  9. Click the gear on the sidebar to open Fleet Settings.
  10. Under Fleet Name, enter dbt Core.
  11. Click Save & Finish on the bottom right of your screen.
  12. This should take you to a page showing that your Fleet was created successfully.
  13. Click Run Your Fleet. This will take you over to the Fleet Log.
  14. You can click on the bar to get the output from your run.

If you scroll to the top of the output, you will see that the environment variables that were put in during the Blueprint creation process are hidden from the user.

If dbt debug succeeds, we're ready to move into part three of the dbt and BigQuery guide. If it fails, please go back to the steps above and make sure everything is setup correctly. Feel free to send an Intercom message to us at anytime using the widget on the bottom right of the Shipyard screen.

In the meantime, consider signing up for our weekly newsletter, "All Hands on Data." You'll get insights, POVs, and inside knowledge piped directly into your inbox. See you there!