Creating a project in dbt using BigQuery

Leia esse post em português aqui

The objective of this article is to demonstrate the step-by-step process for creating a local dbt project using BigQuery as a Data Warehouse. For this, it’s important that you have a Gmail account that can be used to create the project.

1. Creating the Project on GCP

The first step is to access the Google Cloud Platform to create a new project. Click on your current project and create a new project:

Give it a name and then click on create:

Once inside the project, access the Service Accounts and create a new service, then click create and continue (don’t click Done yet):

Next, when choosing the role, select BigQuery Admin:

Then click continue and Done. Still in Service Accounts, go to Actions as indicated by the arrow and then click on Manage Keys. Click add key, then create key, leave the JSON format selected, and select create:

This will download your credentials to your machine. I suggest renaming your credentials to something like “credentials.”

2. Installation and Configuration of dbt core and dbt-bigquery

When installing dbt-bigquery on our machine, dbt core is automatically installed as well. Following the documentation, let’s install it with pip. The standard command is pip install dbt-<adapter> where <adapter> will be the chosen Data Warehouse (Snowflake, BigQuery, Redshift, etc). So, in your VS code terminal, type:

pip install dbt-bigquery

Once this is done, we can create the project using dbt by typing:

dbt init <nome-do-projeto>

When the project is created, some information is requested:

• Project name: Define a name for your project following dbt’s instructions.
• Database: Set it to “bigquery.”
• Chosen authentication method: “service_account.”
• DBT Dataset: Set it to “bigquery.”
• Threads: 1
• job_execution_timeout_seconds: 300
• Location option: 1

Once this is done, your project will be created!

Now, we need to configure some settings. In the dbt project, create a profile.yml file and follow the documentation’s instructions to configure your profile. Your file should have the following configuration:

# For more information on how to configure this file, please see:
# https://docs.getdbt.com/docs/profile
dbt-bigquery:
   outputs:
      dev:
         type: bigquery
         threads: 1
         method: service-account
         project: [id do seu projeto no GCP]
         dataset: schema
         keyfile: [caminho das suas credenciais]
         timeout_seconds: 300
   target: dev

There you go! Now you can test if your repository is configured correctly by running dbt run --models my_first_dbt_modeland the success message should appear like the one below:

Now, you can create your own models and explore all the features offered by dbt. If you prefer, you can also check the project on my GitHub.

GitHub — camila-marquess/dbt-studies