Blogs / Snowflake / Toggle Snowflake Warehouse based on User in Looker

Toggle Snowflake Warehouse based on User in Looker

Aug 22, 2019

SHARE

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.

Toggle Snowflake Warehouse based on User in Looker

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

Toggle Snowflake Warehouse based on User in Looker

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

Toggle Snowflake Warehouse based on User in Looker

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.

Toggle Snowflake Warehouse based on User in Looker

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’]}}
Toggle Snowflake Warehouse based on User in Looker

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.

Toggle Snowflake Warehouse based on User in Looker

Conclusion

Looker provides the option to use a different warehouse for different user sessions based on requirements with out of the box available features.

Read more about similar Self Service BI topics here and learn more about Visual BI Solutions Microsoft Power BI offerings here. 


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!