Snowflake

Connecting Looker to Snowflake is a two step process:

Create a Looker user on Snowflake and provision access.
Set up a database connection in Looker.

Create a Looker User on Snowflake

We recommend the following commands for creating the Looker user. Make sure to run each line individually.

-- change role to ACCOUNTADMIN use role ACCOUNTADMIN; -- create role for looker create role if not exists looker_role; grant role looker_role to role SYSADMIN; -- Note that we are not making the looker_role a SYSADMIN, -- but rather granting users with the SYSADMIN role to modify the looker_role -- create a user for looker create user if not exists looker_user password = '<enter password here>'; grant role looker_role to user looker_user; alter user looker_user set default_role = looker_role default_warehouse = 'looker_wh'; -- change role use role SYSADMIN; -- create a warehouse for looker (optional) create warehouse if not exists looker_wh -- set the size based on your dataset warehouse_size = medium warehouse_type = standard auto_suspend = 1800 auto_resume = true initially_suspended = true; grant all privileges on warehouse looker_wh to role looker_role; -- grant read only database access (repeat for all database/schemas) grant usage on database <database> to role looker_role; grant usage on schema <database>.<schema> to role looker_role; -- rerun the following any time a table is added to the schema grant select on all tables in schema <database>.<schema> to role looker_role; -- create schema for looker to write back to use database <database>; create schema if not exists looker_scratch; use role ACCOUNTADMIN; grant ownership on schema looker_scratch to role SYSADMIN revoke current grants; grant all on schema looker_scratch to role looker_role;

If you paste the above commands as a batch into the Snowflake connection panel, check the All Queries box to ensure all lines are run. By default, Snowflake runs only lines that are selected:

Adding the Connection

Follow the instructions for connecting Looker to your database, noting a few special settings:

SSL must be turned on.
Specify an account and warehouse via JBDC parameters. Use this format:
account=[account_name]&warehouse=[warehouse_name]
The account name is not the same as the database user name. The Snowflake instance account is usually the first part of your Snowflake instance host URL. If your instance URL is mycompany.snowflake.com, your account name is mycompany.

If your Snowflake instance host URL includes an AWS region, do not include it in the account name. For example, if your Snowflake instance host URL is mycompany.us-east-1.snowflakecomputing.com, enter mycompany as your account name.
Whitelisting IPs is not required.
Be sure to include a default schema in the Schema field. You will still be able to query other schemas, but Snowflake expects this parameter in the JDBC string.
In the Temp Database field, the schema name LOOKER_SCRATCH should be in all caps. Snowflake changes the name to all caps on its end, so you can’t connect if you use lowercase in the Looker Connection Settings page.

Here is an example setup:

Designating Snowflake Warehouses on a Per-Group or Per-User Basis

You can use Looker user attributes to assign separate Snowflake warehouses to individual Looker users or groups. This is useful, for example, if you have users who require different levels of computing power. You can assign a warehouse with greater computing resources to just those users who need it, while assigning a warehouse with lesser resources to users with lesser needs.

To designate warehouses on a per-group or per-user basis:

Add the groups or users in Looker.
Define a user attribute to store the Snowflake warehouse names:
In the user attribute you just defined, assign the warehouse name values to the groups or users:
In the Additional Params field in the Connection Settings page, add the following, replacing snowflake_warehouse with the name of the user attribute you defined:
```
warehouse={{ _user_attributes['snowflake_warehouse'] }}
```
For example:
To test the individual connection settings, you can sudo as a user to whom you assigned a warehouse name value.

You can see more detailed instructions for this procedure on the Red Pill Analytics blog.

Snowflake’s Autosuspend Feature

Snowflake warehouses have an autosuspend feature that is enabled by default. After a specified period, the warehouse will autosuspend. If the warehouse is suspended, all queries produce an error. This error is not visible via dashboards (no data is normally shown), but it is visible when querying with the Explore page.

Snowflake also has an autoresume feature that will resume the warehouse when it is queried. However, resuming the warehouse can take up to five minutes, causing queries to hang for five minutes before returning. These features can be configured in the Warehouses tab:

PDT Support

For persistent derived table support, create a Snowflake user for PDTs that has write access to your database and the default schema. In the Looker Connections Settings page, check the Persistent Derived Tables box, and in the PDT Overrides column, enter the username and password of the PDT user. See this documentation page for more information.

Feature Support

Looker’s ability to provide some features depends on whether the database dialect can support them.

In the current Looker release, Snowflake supports the following Looker features:

Next Steps

After you have connected your database to Looker, you’re ready to configure sign-in options for your users.