Looker user attributes and `if dev` to auto-switch between dev & prod schemas


(Michael Dunn) #1

At RealSelf, we use DBT for our data layer modeling, and people developing models in DBT default to having their models built in user-specific sandbox schemas. My sandbox schemas are dbt_md_base for raw or lightly cleansed data, dbt_md_work for interstitial models, and dbt_md for final modeled tables. These mirror the production dbt_base, dbt_work, and dbt schemas.

One of the challenges we face with Looker is how to switch between sandbox schemas and production schemas so we can test views and explores before rolling them out. If we hard-code the table reference to the sandbox schema during development, at some point, someone will forget to change it to the production schema before release.

This blog post by Fishtown Analytics’ Tristan Handy talks about a solution to that in some detail. I’m here to offer an improvement to his solution. Do use user attributes as he suggests, but what if there was a way to set the user attributes once, and then never touch them again, but have Looker automatically switch between sandbox and production based on whether or not you’re in dev mode?

Rejoice, fellow Lookers, for there is indeed such a way!

Looker has a not-well-advertised (IMO) feature that allows you to dynamically insert text into the sql: option of a derived table definition, based on whether the user is in dev mode or not.

-- if dev -- some text
-- if prod -- some other text

Conveniently, Liquid is parsed after this feature, so we can combine this with user attributes like so:

derived_table: {
    sql:
      SELECT
        some_field
        ,some_other_field
      FROM
        -- if dev -- {{_user_attributes['sandbox_schema']}}.some_table
        -- if prod -- {{_user_attributes['dbt_schema']}}.some_table
      ;;
  }

In dev mode, therefore, a view using both of these columns would generate this SQL:

WITH some_view AS (SELECT
        some_field
        ,some_other_field
      FROM
         dbt_md.some_table
        -- if prod -- dbt.some_table
      )
SELECT 
	some_view.some_field  AS some_view_some_field
    ,some_view.some_other_field  AS some_view_some_other_field
FROM some_view

And outside dev mode, it would generate this:

WITH some_view AS (SELECT
        some_field
        some_other_field
      FROM
        -- if dev -- dbt_md.some_table
        dbt.some_table
      )
SELECT 
	some_view.some_field  AS some_view_some_field
    ,some_view.some_other_field  AS some_view_some_other_field
FROM some_view

Voilà! You are now querying your sandbox when in dev mode, and production tables when not in dev mode!


(ernesto ongaro) #2

Looks like you can use sql_table_name with this syntax. For larger deployments we’d suggest running separate dev/prod instances of Looker (link).

view: events {
  sql_table_name:
    -- if prod -- public.events
    -- if dev -- public.events2
;;

(Maximilian Roos) #3

Beyond this, it would be v helpful for us to be able to switch a global variable beyond prod vs dev


(Morgan Imel) #4

Hi @Maximilian_Roos, do you mind expanding on your use case so I can pass your feedback along?