Toggle Snowflake Warehouse based on User in Looker

Snowflake’s multi-warehouse architecture allows querying any database using any warehouse by set in setting the context in the Snowflake Web UI or in the SnowSQL. How can we leverage this feature when we are consuming Snowflake data in Looker? This blog helps us to configure Looker users to use different Snowflake’s Warehouses.

Looker is a great choice to use in conjunction with Snowflake because, among other reasons, Looker does nearly all its processing on the database using SQL. By utilizing the database for processing, Looker takes advantage of Snowflake’s on-demand, elastic compute.

In this example we see two users accessing the same Snowflake database from Looker. One user is a Sales user interested in aggregated Sales data and the other a data scientist performing complex analysis. The sales user’s workloads are typically lighter and take a few seconds to complete. On the other hand, the data scientist is writing complex queries that use analytical functions takes minutes. In this scenario, I want the Sales user to use a separate warehouse with less compute and data scientist to use different warehouse with a larger warehouse. Snowflake’s shared data, multi-cluster architecture with tight integration to Looker allows us to do just that.

How we do that?

Using User’s attribute in Looker.

Prerequisites

In Snowflake: To configure this feature, we need at least two Warehouses.

In Looker: Access to create User Groups, Users, User Attributes and Database Connection in Looker.

Let’s start with the procedure

We have configured two Virtual Warehouses in Snowflake with below parameters

We have created two User Groups namely “Data Scientists” and “Sales Users” in Looker and assigned users respectively.

Now comes the key part of the configuration, create a user attribute.

What is the User Attribute?

User attributes provide a customized experience for each Looker user. A Looker admin defines a user attribute and then applies a user attribute value to a user group or to individual users.

User attributes can be used to configure Database Connections, Data Actions, Filters, Scheduled Dashboards, and Looks, Access Filters, Connecting to Git Providers, Liquid Variables, Google BigQuery Data Limits, Embedded Dashboards. Click here for more information.

We have created a user attribute for Snowflake Warehouse. This user attribute can be called in the Database connection. We can assign User Groups and Snowflake Warehouse to use it for their queries under the “Group Values” tab.

That’s all, now we are ready to configure the toggle switch in our Snowflake database connection.

Use the below parameter in the “Additional Parameters” section.

            warehouse={{ _user_attributes[‘snowflake_warehouse’]}}

Now run Looker queries or dashboards from different user groups and observe query “History” in Snowflake, we should observe the respective warehouse have been used for executing the query.