Updated: 20260121
The purpose of this tutorial is to introduce data engineers to the basic functionality of dbt Cloud services by connecting dbt Studio to Google BigQuery to hos the data warehouse and GitHub for source code management.
Prerequisites
Before starting this tutorial, the following accounts and systems should be set up.
1. A data warehouse database should be created and/or configured. This tutorial uses Google BigQuery. In Google BigQuery, create a dataset that will hold the project data. Upload source data or create tables in BigQuery that will hold the data to be transformed.
For Google BigQuery, follow this tutorial to create a Service Account and download the Service Account JSON file (containing the private key). Make sure this file is available when configuring the connection to BigQuery.
To obtain the 311 complaint data that matches your complaint types, do one of the following:
- You can use the Socrata API to fetch this data from NYC Opendata and save it to a CSV file (or a series of files). Then load this CSV file into a BigQuery table.
- Create a table in BigQuery by pulling the data from a bigquery-public-data dataset.
# Create a subset of the 311 data in BigQuery
CREATE OR REPLACE TABLE `NYC_311_Complaints.bicycle_complaints_2014`
AS
SELECT * FROM
`bigquery-public-data.new_york_311.311_service_requests`
WHERE complaint_type LIKE '%Bicycle%'
AND FORMAT_DATE("%Y", created_date)=2014
Note: Replace the section WHERE complaint_type LIKE '%Bicycle%' with the specific complaint type (or types) you are going to be working with.
2. dbt can use GitHub as a source code Repository. Set up a GitHub account and be ready to create a repository to hold the source code for dbt transformations.
The next page will introduce setting up a dbt account.