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

[michael.dunn]

At RealSelf, people developing 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.

We use Looker for visualization, and one of the challenges we face 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.

@Tristan talks about a solution to that in some detail here. 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 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!

[claire]

@michael.dunn - this is so nifty! Thank you so much for sharing

[tristan]

This is really a neat solution. I suppose it means that all of your looker views are generated from select statements, not sql_table_names?

That is some terrible syntax though!! Why didn’t Looker just include the dev/prod checks as liquid?! Very strange.

[tristan]

Also, PS: I linked to this thread from the original post. Thanks again

[michael.dunn]

Yeah, it seems like it’d be so simple to include a variable/function like _user_attributes[], perhaps _isdev() that returns a boolean. But I don’t suppose it’s a super common use case either.

[claire]

Have we asked Looker if they can implement it as liquid? If you put something on their Discourse I’d be more than happy to pile on some support in the comments!

[ernesto]

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
;;

I’ve +1’d the Looker request to support this via a Liquid variable.

[ranggasob]

Nice thread. I’ve tried this and it works great for a Looker explore that is based on a single view (no additional joins to other views).

But for Looker explore that joins a couple of views, the ‘conditional’ statement don’t seem to carry over. For example:

view: some_view {
sql_table_name:
– if dev – dev.some_table
– if prod – prd.some_table ;;

dimension: id {
type: number
sql: ${TABLE}.id ;;
primary_key: yes
}

dimension: description {
type: string
sql: ${TABLE}.description ;;
}
}

Joining the view above with another view via explore:

explore: some_explore {
view_name: main_view

join: some_view {
relationship: one_to_one
sql_on: {main_view.id} = {some_view.id} ;;
}
}

SQL render output on Looker explorer Development mode:

SELECT
some_view.description
FROM prd.main_view
LEFT JOIN dev.some_view
– if prod – prd.some_view ON main_view.id = some_view.id

the JOIN ON statement only applies to prd.some_view.

Has anybody experience this? Is there a solution this? This means that in order to avoid this issue all joins need to happen in dbt model. But, this means we cannot use Looker’s explore modeling when using dbt and Looker together.

[michael.dunn]

It’s not even that it only applies to the prod version. It’s simpler than that. They’re just not inserting a line break between the join clause and the on clause, causing on to be part of the SQL comment.

Have you tried inserting an extra line break in your LookML before the ;; in your sql_table_name attribute?

[ranggasob]

You’re right! it works. I never thought of that. Thanks!

[MartinGuindon]

I want to put an emphasis on something important that was not specifically mentioned here, and which caused me some headaches until I figured it out.

It is super important that the double semicolons are on their own line, otherwise the ON clause on joins will be commented out and will therefore return duplicated rows.

So, this is bad:

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

And this is good:

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

[matthias]

This approach to implement separate environments is brilliant. Thank you a lot for this Michael and Tristan.

I am wondering if this approach also can work with aggregate tables or persistent derived tables. After defining an aggregate table on an explore referencing views that are using the if dev / prod function and user attributes to switch between development and production as described above, I get the following warning:

Persistent Native Derived Table for view “<my_explore::my_aggregate_table>” references user attributes. Persisting this table may result in unexpected behavior.

Has somebody here come across the same issue? Is it somehow possible to user aggregate tables with this approach?