Preparing your Snowflake Data Warehouse
  • 18 May 2022
  • 2 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Preparing your Snowflake Data Warehouse

  • Dark
    Light
  • PDF

To connect your Snowflake as a Working with Data Warehouses, some configuration is needed in Snowflake. This page explains how to configure Snowflake to connect successfully.

In the most basic configuration, there are two areas of interest:

  • Database schema and users
  • Network Policies

Configure the database schema and users

Materialized views created with the Data Warehouse exist as tables in the sisense_views schema. Before connecting to the database, create this schema, the database roles, and database users with the commands below. Note that the “select on future tables” commands are only necessary if you want Sisense to have access to new tables that may be created in the future.

Create the Schema

CREATE SCHEMA sisense_views

Create and Configure Roles

CREATE ROLE sisense_read
CREATE ROLE sisense_write
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE sisense_write;
GRANT USAGE ON DATABASE <database_name> TO ROLE sisense_write;
GRANT OWNERSHIP ON SCHEMA <database_name>.sisense_views TO ROLE sisense_write;
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE sisense_read;
GRANT USAGE ON DATABASE <database_name> TO ROLE sisense_read;
GRANT USAGE ON SCHEMA <database_name>.sisense_views TO ROLE sisense_read;

Create and Configure Users

CREATE USER sisense_read PASSWORD = <password>;
CREATE USER sisense_write PASSWORD = <password>;
ALTER user sisense_read SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE;
ALTER user sisense_write SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE;
GRANT ROLE sisense_read TO USER sisense_read;
GRANT ROLE sisense_write TO USER sisense_write;
ALTER USER sisense_read SET DEFAULT_WAREHOUSE = <warehouse_name>;
ALTER USER sisense_read SET DEFAULT_NAMESPACE = <database_name>;
ALTER USER sisense_read SET DEFAULT_ROLE = sisense_read;
ALTER USER sisense_write SET DEFAULT_WAREHOUSE = <warehouse_name>;
ALTER USER sisense_write SET DEFAULT_NAMESPACE = <database_name>;
ALTER USER sisense_write SET DEFAULT_ROLE = sisense_write;

Grant Access to Read Additional Schemas

GRANT USAGE ON SCHEMA public TO ROLE sisense_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ROLE sisense_read;
GRANT SELECT ON FUTURE TABLES IN SCHEMA public TO ROLE sisense_read;
GRANT USAGE ON SCHEMA public TO ROLE sisense_write;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ROLE sisense_write;
GRANT SELECT ON FUTURE TABLES IN SCHEMA public TO ROLE sisense_write;

Configuring Network Policies

Snowflake network policies can block or allow access by IP at the account level and/or the user level. By default, a Snowflake account allows incoming connections from all sources, including Sisense. However, if your warehouse has policies that restrict this at the account level, additional user-level policies are required to allow you to connect your Snowflake Warehouse to Sisense.

Note, when a network policy is applied with an ALLOWED_IP_LIST, all IPs not listed will be blocked.

Checking Network Policies

Before going any further, check first to see if your account uses any network policies. Specifically, whether there are any policies applied at the account level. Run the following command to see the existing policies (Note, this only returns results when using the SECURITYADMIN or ACCOUNTADMIN role):

SHOW NETWORK POLICIES
DESCRIBE NETWORK POLICY <name>

If there are no network policies, or all described network policies only have values for BLOCKED_IP_LIST, then no further configuration is needed. If there is an existing network policy, see below to apply an override to just the accounts that will be used with Sisense.

Creating and Applying Network Policies

Use the commands below to create a network policy that explicitly allows traffic to and from Sisense Cloud. The Sisense Cloud IP address is 54.186.74.45 and 54.187.196.247 and the Sisense deployment address will be the IP of your Sisense URL (example: https://analytics.example.com)

CREATE NETWORK POLICY sisense_cloud allowed_ip_list=('54.186.74.45/32, 54.187.196.247/32')
ALTER USER sisense_read SET NETWORK_POLICY = sisense_cloud
ALTER USER sisense_write SET NETWORK_POLICY = sisense_cloud

For more on Data Warehouses, see Working with Data Warehouses.


Was this article helpful?