Auto-Switch between Dev & Prod Schema and PDTs

I would like to share an approach to auto-switch between development and production datasets on a single looker instance that still works with persistent derived tables. Why is this relevant? Having separate environments is crucial for a controlled development process that does not interfere with production. And while you can create PDTs already downstream with dbt, you may want to use aggregate awareness (and PDTs) to dynamically speed up queries.

This post describes a very elegant approach to auto-switch between development and production datasets by using ‘if dev’ and user attributes in your views:

view: your_view {

  sql_table_name:
  -- if dev -- {{_user_attributes['development_schema']}}.your_table
  -- if prod -- analytics.your_table
  ;;

When in production mode, the production schema analytics is queried. When in development mode, the developer’s development schema is queried. This way each developer can work on features in an isolated dataset, not interfering with production or other developers. This is great!

The problem is this does not seem to work with PDTs and therefore not with aggregate awareness. Trying to build an aggregate table on the view above will result in the following warning:  

Persistent Native Derived Table for view "your_explore::your_view" references user attributes. Persisting this table may result in unexpected behavior.

It’s stated in the docs that liquid and user attributes won’t work with PDTs.

So we have to take another approach when we want to use aggregate awareness and still keep environments separate. Instead of referencing the user attribute in the view we can do this in the database connection settings:

1649d3c8-059a-4920-9fe1-e4e1d0dee82f.png

This means if no dataset is provided as part of the table reference, the development schema maintained in the user attribute is used (here dev_matthias) and the production schema analytics is configured as PDT override. In the view file we can simple remove the user attributes but still point to our production dataset when in production mode:

view: your_view {

  sql_table_name:
  -- if dev -- your_table
  -- if prod -- your_production_schema.your_table
  ;;

Now we can auto-switch between development and production and user aggregate awareness.

But also this approach comes with a drawback, at least when you are on BigQuery: while in development mode the dataset won’t be included in the table references of the generated SQL anymore. So why is this a problem? My preferred workflow for debugging queries was to copy the generated SQL over to the BigQuery UI since you get error messages highlighted and can use auto-completion, features which do not seem to be available in SQL runner. But as we cannot set default datasets in the BigQuery UI, it is not possible to copy the code over without manually adding the table references.

What is your approach to separate environments (on a single looker instance)?

4 5 2,816
5 REPLIES 5

Hey,

great post 🙂

I have though another use case. I need to use this switch inside a dimension. Something like:

dimension: whatever {

     type: string

     sql: -- if prod -- ${table}.whatever

            -- if dev -- ‘a text string to mask sensitive figures’

;;

}

Is this something that can be done?

Basically we have sensitive data that cannot be exposed while in development mode, as it only resides on a PROD server. I want developers to get a placeholder string when using this DIM in DEV mode, and get real values when in PROD.

Thanks in advance 🙂

Yes, it seems like this should also work in other places (although I have not tried this). See also this post

Hey 🙂

Cheers for the reply and sorry bout the delay.

I went through your suggested article, doesn’t help here but thanks 🙂

Dawid
Participant V

What if I would like to do this for the connection parameter in model files?

clathrop
Participant II

We use a system like this:

  1. In the manifest file, create something like this
    constant: schema {
    # Production
    value: "prod"

    # Craig Dev
    # value: "dev_craig"

    # John Dev
    # value: "dev_john"

    # Susan Dev
    # value: "dev_susan"
    }
  2. In your views, do something like this 
    sql_table_name: @{schema}_combine.fct_rev_slim ;;

Now you can just comment out prod and uncomment the schema you want to use. If you want some safety, in your GitHub repo, put an action that checks to make sure value: “prod” isn’t commented out before a pull request merges. 

Note, we use DBT. That made working with our prod and dev schemas much easier. 

Top Labels in this Space
Top Solution Authors