By the end of this tutorial, you'll be able to use Spectacles and dbt Cloud to check if a dbt Pull Request will cause SQL errors in Looker.
To test changes to your dbt repo, Spectacles runs a test Suite, but instead of testing production data, tests data in a staging schema that reflects the output of your dbt changes.
In dbt Cloud, you will need a job that:
- Runs on every pull request on your dbt repo.
- Generates the full output of your dbt project. If you are using modified state in your dbt Cloud job, the first step of your job needs to clone your production schema into the target schema for each run.
We'll configure schema references in our LookML with a user attribute, so Spectacles can modify
sql_table_name in your views and direct Looker to query the dbt Cloud-generated staging data instead of production data.
This guide explains how to make the necessary changes to your LookML.
For each user attribute you'd like to modify in Spectacles, you'll need to configure it with a default value on the Spectacles Settings page.
Here are the steps in Spectacles:
- In the top-right, click Settings ⚙️.
- Scroll down to the Looker Settings section.
- Under User attribute, input the name of the user attribute that controls the schema in your LookML.
- Under Default value, input the name of schema you use for production. For non-dbt Cloud runs, Spectacles will use this value.
- Click Update Settings.
Want to know more about using user attributes with Spectacles? Find our guide to user attributes here.
Let's set up a new Suite to trigger a SQL validation whenever a dbt pull request is opened.
- In the top menu, click Suites.
- Click New Suite.
There are a lot of options on this page. For the moment, we're going to create a simple Suite that only runs SQL validation.
Name your Suite "dbt Cloud PRs"
Configure the Triggers section with the following steps:
- Click Trigger on pull requests from dbt Cloud.
- In the dbt Cloud Repo field, put in the full name of your dbt Cloud repo. If the URL of your repo is
https://github.com/spectacles-ci/spectacles-dbt, your input would be
- In the dbt Cloud Job ID field, put in the ID of the dbt Cloud job that runs on pull requests. This number is the final number in the URL for your dbt Cloud job. If the URL is
https://cloud.getdbt.com/#/accounts/2284/projects/7873/jobs/8358, your input would be
- In the Schema User Attribute field, select the user attribute that we created in the section above.
Configure the SQL Validator section with the following options:
- Explores to query: Leave the default, otherwise list them with the model selection syntax.
- Explores to exclude: Leave the default
- Fail fast: Yes
- Query concurrency: Leave the default
Uncheck Is Enabled for the Content Validator and the Assert Validator.
Click Create Suite.
Open a pull request in your dbt repo. This pull request doesn't need to have any major changes. It must pass dbt Cloud's checks.
Spectacles will kick off a SQL validation run against your production LookML, but will update the schema user attribute to point Looker at the dbt Cloud-generated staging data.
Congratulations! If Spectacles identifies any errors, try fixing them in your dbt repo and adding a new commit to the pull request.