Prepare LookML to Test dbt Changes

To test dbt changes, Spectacles requires two things:

1. Staging data resulting from the dbt change that it can test.
2. A way to point your LookML to that staging data instead of production data.

We use user attributes to allow Spectacles to control the destination of SQL fields like sql_table_name or derived tables.

In this guide, we'll help you set up dynamic schema references in your LookML that allow you to point Looker to specific schemas using user attributes. These references could also apply to databases, projects and datasets, depending on what data warehouse you're using.

Dynamic references with user attributes

The Spectacles dbt integration works by running the Spectacles validators exactly as we do for Looker pull requests. The only difference is that we begin the run by telling Looker to direct the queries to the dataset we previously generated in our dbt CI job.

We do this by updating one or more user attributes that control the schema and database your LookML references in SQL fields. Looker uses the value of these user attributes instead of hard-coded values.

For example, instead of a hard-coded sql_table_name like this,

sql_table_name: analytics.fct_orders ;;

we use a dynamic reference like this.

sql_table_name: {{ _user_attributes['dbt_schema'] }}.fct_orders ;;

In this example, analytics is the schema for your production data. We replace this schema with {{ _user_attributes['dbt_schema'] }} where dbt_schema is the name of the Looker user attribute we'd like to use to control the schema.

If your dbt CI job creates all your data in a schema called dbt_cloud_pr_1234_10, Spectacles can update the value of the user attribute accordingly only for the specific run used to test dbt changes. All other Spectacles runs or Looker queries will use the default schema analytics unless told otherwise.

Choosing a user attribute configuration

The exact setup of the user attribute(s) in Looker will depend on how you've configured your dbt CI job.

For this example, we'll assume that you're either using a data warehouse that doesn't have a database/project namespace (e.g. Redshift) or that you've created CI data in the same database/project as the data that Looker uses in production.

There are five different options for how to set this up. Use the one that best applies to your dbt CI setup.

1. One schema per environment

   • Production: analytics
   • Development: dbt_cloud_pr_8358_72

2. Custom schemas, only in production

   • Production: core, marketing
   • Development: dbt_cloud_pr_8358_72

3. Custom schemas in dev and production

   • Production: analytics_core, analytics_marketing
   • Development: dbt_cloud_pr_8358_72_core, dbt_cloud_pr_8358_72_marketing

4. Custom schemas in dev and production, appended in dev

   • Production: core, marketing
   • Development: dbt_cloud_pr_8358_72_core, dbt_cloud_pr_8358_72_marketing

5. Custom schemas in dev and production, appended in production

   • Production: analytics_core, analytics_marketing
   • Development: dbt_cloud_pr_8358_72

One schema per environment

• Production schema name:

  • analytics

• dbt CI schema name:

  • dbt_cloud_pr_8358_72

In this configuration, replace any reference to your dbt schema in your LookML views with a user attribute.

Existing

sql_table_name: analytics.fct_orders ;;

New

sql_table_name: {{ _user_attributes['dbt_schema'] }}.fct_orders ;;

Create a user attribute in Looker called dbt_schema and set the default value to whatever your production schema name is (in this case analytics).

Custom schemas, only in production

• Production schema name:

  • core
  • marketing

• CI schema name:

  • dbt_cloud_pr_8358_72

In this configuration, replace any reference to your dbt schema in your LookML views with a conditionally inserted user attribute:

Existing

sql_table_name: core.fct_orders ;;

New

sql_table_name: {% if _user_attributes['dbt_schema'] != 'production' %}{{ _user_attributes['dbt_schema'] }}{% else %}core{% endif %}.fct_orders ;;

Create a user attribute in Looker called dbt_schema and set the default value to the word 'production'.

Custom schemas in dev and production

• Production schema name:

  • analytics_core
  • analytics_marketing

• CI schema name:

  • dbt_cloud_pr_8358_72_core
  • dbt_cloud_pr_8358_72_marketing

In this configuration, partially replace any reference to your dbt schema in your LookML views with a user attribute:

Existing

sql_table_name: analytics_core.fct_orders ;;

New

sql_table_name: {{ _user_attributes['dbt_schema'] }}_core.fct_orders ;;

Create a user attribute in Looker called dbt_schema and set the default value to whatever your production schema name is (in this case analytics).

Custom schemas in dev and production, appended in dev

• Production schema name:

  • core
  • marketing

• CI schema name:

  • dbt_cloud_pr_8358_72_core
  • dbt_cloud_pr_8358_72_marketing

In this configuration, partially replace any reference to your dbt schema in your LookML views with a conditionally inserted user attribute:

Existing

sql_table_name: core.fct_orders ;;

New

sql_table_name: {% if _user_attributes['dbt_schema'] != 'production' %}{{ _user_attributes['dbt_schema'] }}_{% endif %}core.fct_orders ;;

Create a user attribute in Looker called dbt_schema and set the default value to the word 'production'.

Custom schemas in dev and production, appended in production

• Production schema name:

  • analytics_core
  • analytics_marketing

• CI schema name:

  • dbt_cloud_pr_8358_72

In this configuration, partially replace any reference to your dbt schema in your LookML views with a conditionally inserted user attribute:

Existing

sql_table_name: core.fct_orders ;;

New

sql_table_name: {% if _user_attributes['dbt_schema'] != 'production' %}{{ _user_attributes['dbt_schema'] }}{% else %}analytics_core{% endif %}.fct_orders ;;

Create a user attribute in Looker called dbt_schema and set the default value to the word 'production'.