# Scheduled serverless dbt + BigQuery service

Author: [Mete Atamel](https://atamel.dev/) | Published: 2020-07-29 | Topics: Cloud Run, Serverless, BigQuery, dbt, How To | Canonical: https://atamel.dev/posts/2020/07-29_scheduled_serverless_dbt_with_bigquery/


My colleague [Felipe Hoffa](https://twitter.com/felipehoffa) recently published a
blog post titled [Get started with BigQuery and dbt, the easy
way](https://medium.com/@hoffa/get-started-with-bigquery-and-dbt-the-easy-way-36b9d9735e35).
More specifically, he showed how to install [dbt](https://getdbt.com/) in Google
Cloud Shell, configure it and manually run it to create a temporary dataset in
BigQuery. This is great for testing dbt + BigQuery but how do you run this
kind of setup in production?

[dbt
documentation](https://docs.getdbt.com/docs/running-a-dbt-project/running-dbt-in-production/)
states that **Running dbt in production simply means setting up a system to run a
dbt job on a schedule, rather than running dbt commands manually from the
command line.**

Cloud Shell is just a temporary VM in the cloud and not suitable for production
workloads. One obvious solution is to create a dedicated VM, install dbt and
have some kind of cron job on that VM to run dbt on a schedule. This will work
but who wants to maintain a VM? Not to mention, you need to pay for the VM per second
even when dbt is not running. It's wasteful. We can do better.

A better solution is to use [Cloud Run](https://cloud.google.com/run). Cloud Run
is fully managed, no VMs to setup or maintain. Its pricing model is based on
request time, you'll only get charged when the dbt service is running.

In this blog post, I want to show you how to take Felipe's sample and make it
more production-ready by running it as a serverless Cloud Run service on a schedule.

## Challenges

Running dbt as a Cloud Run service has a few challenges, namely:

1. dbt is mainly a command line tool whereas Cloud Run expects HTTP requests.
   How do you call dbt command from a Cloud Run service?
2. Cloud Run runs containers. How do you run dbt in a container?
3. How do you authenticate dbt with BigQuery? OAuth works for end users but for
   services running in the cloud, it's probably not the right solution.

Let's tackle them in that order.

## Running shell commands from Cloud Run

Cloud Run has [an
example](https://cloud.google.com/run/docs/quickstarts/build-and-deploy#shell)
on how to run a shell command from an HTTP Server deployed to Cloud Run. It
involves setting up a Go based HTTP server that simply calls a shell script upon
receiving a GET request. You can take a look the details in
[invoke.go](https://github.com/meteatamel/cloudrun-tutorial/blob/master/dbt/invoke.go).

In our case, the shell script,
[script.sh](https://github.com/meteatamel/cloudrun-tutorial/blob/master/dbt/script.sh)
simply calls dbt with the profile folder:

```sh
#!/bin/sh
dbt run --profiles-dir .
```

## Container image for dbt

dbt has some [base images](https://hub.docker.com/r/fishtownanalytics/dbt/tags)
that you can rely on (although the documentation is pretty much non-existent).
In the container, we want to include the HTTP Server with the `script.sh`. We also
want to include dbt runtime. This is a sample [Dockerfile](https://github.com/meteatamel/cloudrun-tutorial/blob/master/dbt/Dockerfile) that works:

```dockerfile
FROM golang:1.13 as builder
WORKDIR /app
COPY invoke.go ./
RUN CGO_ENABLED=0 GOOS=linux go build -v -o server

FROM fishtownanalytics/dbt:0.17.0
USER root
WORKDIR /dbt
COPY --from=builder /app/server ./
COPY script.sh ./
COPY dbt_project ./

ENTRYPOINT "./server"
```

Build the container using `gcloud`:

```sh
export SERVICE_NAME=dbt-service
gcloud builds submit \
  --tag gcr.io/$(gcloud config get-value project)/${SERVICE_NAME}
```

## Authentication

By default, Cloud Run uses the Compute Engine default service account and that
should be able to make BigQuery calls. However, it's best practice to assign a
more granular permission to your Cloud Run service by assigning a dedicated
service account with more restricted IAM roles.

In our case, the Cloud Run service will only talk to BigQuery, so let's create a
service account with `bigquery.admin` role. You probably want to use even a
finer grained role in production:

```sh
export SERVICE_ACCOUNT=dbt-sa
gcloud iam service-accounts create ${SERVICE_ACCOUNT} \
   --display-name "DBT BigQuery Service Account"
gcloud projects add-iam-policy-binding \
  $(gcloud config get-value project) \
  --member=serviceAccount:${SERVICE_ACCOUNT}@$(gcloud config get-value project).iam.gserviceaccount.com \
  --role=roles/bigquery.admin
```

We will use this service account when we deploy the Cloud Run service.

## Deploy Cloud Run service

Now that we have all the pieces assembled, deploy to Cloud Run with the
service account created earlier and also `no-allow-unauthenticated` flag to make
it a private service:

```sh
gcloud run deploy ${SERVICE_NAME} \
    --image gcr.io/$(gcloud config get-value project)/${SERVICE_NAME} \
    --service-account ${SERVICE_ACCOUNT}@$(gcloud config get-value project).iam.gserviceaccount.com \
    --no-allow-unauthenticated
```

After a few seconds, you should see the service deployed and running:

![dbt Cloud Run service](/img/2020/dbt-cloud-run-service.png)

You should also see that the service is private:

![dbt Cloud Run service](/img/2020/dbt-cloud-run-service-permissions.png)

## Scheduling

The final step is to call the Cloud Run service on a schedule. You can do this with Cloud Scheduler.

First, make sure the Cloud Scheduler API is enabled:

```sh
gcloud services enable cloudscheduler.googleapis.com
```

Create a service account for Cloud Scheduler with `run.invoker` role:

```sh
export SERVICE_ACCOUNT=dbt-scheduler-sa
gcloud iam service-accounts create ${SERVICE_ACCOUNT} \
   --display-name "DBT Scheduler Service Account"
gcloud run services add-iam-policy-binding ${SERVICE_NAME} \
   --member=serviceAccount:${SERVICE_ACCOUNT}@$(gcloud config get-value project).iam.gserviceaccount.com \
   --role=roles/run.invoker
```

Create a Cloud Scheduler job with the service account to call the Cloud Run service every 5 minutes:

```sh
export SERVICE_URL="$(gcloud run services list --platform managed --filter=${SERVICE_NAME} --format='value(URL)')"
gcloud scheduler jobs create http ${SERVICE_NAME}-job --schedule "*/5 * * * *" \
   --http-method=GET \
   --uri=${SERVICE_URL} \
   --oidc-service-account-email=${SERVICE_ACCOUNT}@$(gcloud config get-value project).iam.gserviceaccount.com \
   --oidc-token-audience=${SERVICE_URL}
```

To test that the service gets called and the temporary BigQuery
dataset gets created, you can manually trigger the job:

```sh
gcloud scheduler jobs run ${SERVICE_NAME}-job
```

You should see a `temp` dataset created in BigQuery console:

![dbt temp dataset](/img/2020/dbt-temp-dataset.png)

---

That's it. Hopefully, this blog post gave you an idea on how to use dbt +
BigQuery + Cloud Run together. This was a very simple dbt job but the same setup
can be used for more complicated scenarios. You can check out my [Cloud Run
Tutorial](https://github.com/meteatamel/cloudrun-tutorial) and its [Scheduled
Cloud Run dbt service with
BigQuery](https://github.com/meteatamel/cloudrun-tutorial/blob/master/docs/scheduled-dbt-service-bigquery.md)
section for all the code for this post.

Feel free to reach out to me on Twitter
[@meteatamel](https://twitter.com/meteatamel) or read my previous posts on
[medium/@meteatamel](https://medium.com/@meteatamel).