Cloud run DBT

An example project to run DBT on Google Cloud Run. It will create infrastructure for you and seed tables, then create a job and run a DBT model on it. The job will be triggered by a Cloud Scheduler job or manually using task.

Prerequisites

• A Google Cloud Platform organisation with billing enabled (else you will have to modify the Terraform to use a project without billing)
• A Landing zone (only if this goes passed an experiment)
• Google Cloud SDK
• Terraform
• Docker
• DBT
• Python
• Task
• jq

If you are on a Apple with a silicon chip you will need to enable buildx in Docker: Build multi architecture images on m1 mac Running docker on Mac to push to the cloud can be a bit slow and tedious, and if you want this to have a simpler setup please use CI/CD to build and push the images and update the jobs.

Environment

You will need an environment file .env in the root of the project with the following variables:

PROJECT_ID=<your project id>
REGION=<region to deploy to>

You can also run task terraform:output after an apply to populate the .env file with the output variables.

Getting Started

Fist make sure you have the latest version of task installed brew install go-task To get started, clone this repository and change into the directory:

git clone [email protected]:thoughtgears/cloud-run-dbt.git
cd cloud-run-dbt
task run

This will create the infrastructure and seed the tables, then creating a cloud run job to run the DBT model. It will then trigger the job using the API that will mimic a cloud scheduler job.

Cloud scheduler job

Project can be the the number or the project id, region is the region where the job is deployed and the job is the job name that you have deployed. You will have to set the timezone based on the tz database. You will also need to create a service account and give it the run.invoker role on the job.

gcloud iam service-accounts create cloud-scheduler-dbt --display-name="Service account for cloud scheduler that can trigger dbt jobs"

gcloud run jobs add-iam-policy-binding ${JOB} \
    --region=${REGION} \
    --member='serviceAccount:cloud-scheduler-dbt@${PROJECT_ID}.iam.gserviceaccount.com' \
    --role='roles/run.invoker' \
    --project ${PROJECT_ID}

gcloud scheduler jobs create http ${JOB} \
    --schedule "0 1 * * *" \
    --time-zone "Europe/London" \
    --uri "https://run.googleapis.com/v2/projects/${PROJECT_ID}}/locations/$REGION}/jobs/${JOB}.:run" \
    --http-method post \
    --message-body '{}' \
    --oauth-service-account-email "cloud-scheduler-dbt@${PROJECT_ID}.iam.gserviceaccount.com" \
    --oauth-token-scope "https://www.googleapis.com/auth/cloud-platform" \
    --project ${PROJECT_ID}