Blog How to Integrate dbt and Looker with User Attributes

How to Integrate dbt and Looker with User Attributes

Tristan Handy

Jan 15, 2018

How To

dbt and Looker were built with the same ethos: analysts should be working in code. We've been using both tools together for almost two years at this point, and the integration is pretty smooth. Build data models in dbt, and then point Looker views at them:

view: customers {
  sql_table_name: analytics.customer_facts ;;

  ...
}

Typically, we output our production dbt models to a schema called analytics, so the above customers view is pointing to the production version of a model called customer_facts. This is pretty standard operating procedure at this point --- there are 100 or so dbt users today who also use Looker, and to my knowledge they all integrate the two products this way.

There is one major drawback of this approach: development mode. Both dbt and Looker allow analysts to separate their development and production environments so that they can write code and test it without impacting production. This is an absolutely critical feature of any code-focused workflow, but there hasn't been a good way to tie dev mode in one product to dev mode in the other.

dbt controls development vs. production using the database schema, and so analysts who use both products end up changing the sql_table_name for any relevant Looker views to point to their dev schema. In practice this is both a headache and it results in bugs when a reference to a dev schema mistakenly gets pushed into production. In our own client work this is a major workflow irritation.

Turns out, there's a pretty great solution.

Looker gives admins the ability to set user attributes: variables that get set at the user level and then referenced in LookML. These variables get default values based on a user's group and can then be overridden for individual users. By leveraging user attributes, we can quickly switch between dev and prod for an individual user and for all of their Looker views.

Here's a quick run-through of the setup process.

Step 1: Create the User Attribute

Go to Admin >> User Attributes and hit Create User Attribute. Set up an attribute called dbt Schema and set the values as shown in this screenshot:

Every one of these values is important for the attribute to function the way you want, so make sure to double check them.

Next, set a "Group Value" for the "All Users" group. This is a group that Looker gives you that contains all of your user accounts, and this will act as your default value. It should be set to the name of your production schema, which, for us, is always analytics:

Note: You need to be an admin on your Looker instance in order to create and configure this user attribute.

Step 2: Reference this attribute in your LookML

Now that we've created the user attribute, we need to reference it in the sql_table_name for every single Looker view in our entire project. This is actually quite easy; just do a find and replace! Switch all references to your production schema to {{_user_attributes['dbt_schema']}} . Here's what it looks like:

view: customers {
  sql_table_name: 
    {{_user_attributes['dbt_schema']}}.customer_facts
    ;;
  
...
}

Once you've made this change, all of your Looker-generated SQL will now be responsive to the schema you set in the user attribute dbt_schema.

Step 3: Override the aribute to switch to dev mode

Now that you've done all of your configuration, it's very easy to switch to dev mode: just override the user attribute for yourself! Go to Admin >> User Attributes, hit Edit on the dbt_schema attribute, and supply a new value. When I switch to dev mode, that looks like this:

Using this, it takes about 15 seconds to switch all dbt model references from prod to dev or back. And you'll never accidentally push code to master that references your dev schema again! 😀

Happy coding! Ping me in Slack if you end up implementing this. I'd love to hear your feedback.

⚡️Ready to improve your analytics engineering workflow? Get started with dbt today. ⚡️

Quick update to this post: dbt user Michael Dunn has a great extension of this approach here.

Last modified on: Feb 28, 2024