Connect to Snowflake

Snowflake is a cloud data platform that provides a data warehouse-as-a-service designed for the cloud.

This page describes how to connect Snowflake to Holistics as a data source.

High-level overview

There are 2 main steps to get started using Snowflake with Holistics:

1. Create a Holistics user on Snowflake and grant access.
2. Connect Snowflake to Holistics as a data source.

Step-by-step Instructions

Step 1: Create a Holistics user on Snowflake

To avoid conflicts with other regular operations in your cluster, we recommend creating a new role and a new user just for Holistics operations, but this is not mandatory.

Let's go to the Snowflake Console page, click on Worksheets, and run the following commands for creating the Holistics user.

1. Change role to ACCOUNTADMIN

   USE ROLE ACCOUNTADMIN;

2. Create a new role HOLISTICS_ROLE using the following command:

• Create a new role HOLISTICS_ROLE

  CREATE ROLE IF NOT EXISTS "HOLISTICS_ROLE";
• Assign privileges of the data warehouse to that role Assume you are using a predefined warehouse (DEMO_WH) and database (HDEMO) You should grant the 2 privileges: OPERATE and USAGE to the HOLISTICS_ROLE

  GRANT USAGE ON WAREHOUSE "DEMO_WH" TO ROLE "HOLISTICS_ROLE";
  GRANT OPERATE ON WAREHOUSE "DEMO_WH" TO "HOLISTICS_ROLE";
  When you visit the warehouse's tab (console#/warehouses), and click on the DEMO_WH warehouse, you can see the privileges of this warehouse have been granted to HOLISTICS_ROLE

• Grant access to the database/schema:

  -- grant read-only database access (repeat for all database/schemas)
  GRANT USAGE ON DATABASE "HDEMO" TO ROLE "HOLISTICS_ROLE";
  GRANT USAGE ON SCHEMA "HDEMO"."ECOMMERCE" TO ROLE "HOLISTICS_ROLE";
  
  -- rerun the following any time a table is added to the schema
  GRANT SELECT ON ALL TABLES IN SCHEMA "HDEMO"."ECOMMERCE" TO ROLE "HOLISTICS_ROLE";
  -- or
  GRANT SELECT ON FUTURE TABLES IN SCHEMA "HDEMO"."ECOMMERCE" TO ROLE "HOLISTICS_ROLE";

Note:

Optionally, add in the ON FUTURE keyword to persist GRANT statements on newly created objects. We recommend running this for tables in all schemas that Holistics will use so you are not required to re-run GRANT statements as new tables are created.

3. Create User for Holistics

   Then, you need to create the user that will be connected to Holistics and assign the role to the user.

   CREATE USER "HOLISTICS_USER"
   MUST_CHANGE_PASSWORD = FALSE
   DEFAULT_WAREHOUSE ="DEMO_WH"
   DEFAULT_ROLE = "HOLISTICS_ROLE"
   PASSWORD = "MY_PASSWORD";
   GRANT ROLE "HOLISTICS_ROLE" TO USER "HOLISTICS_USER";

Configure Key-pair Authentication for Snowflake User

Notice

Currently, Snowflake key-pair authentication is only available when Holistics Canal is enabled.

To use Snowflake key pair authentication for enhanced authentication security, follow these additional steps to configure your Snowflake User:

1. Generate your key pair
   1. Generate Private Key. Example command:

   openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
   2. Generate Public Key. Example command:

   openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
   3. Store your key pair securely
2. Assign the Public Key to your Snowflake User (reference)

   ALTER USER example_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Persist query model

To persist query model to Snowflake using Holistics, you should:

• Create a schema for Holistics to write data back to your warehouse
• Set up the Snowflake user account for Holistics which has full privileges on that schema to create, drop, rename, and alter tables.

-- create schema for Holistics to write back to
USE DATABASE "HDEMO";
CREATE SCHEMA IF NOT EXISTS "HOLISTICS_DERIVED_MODEL";
USE ROLE ACCOUNTADMIN;
GRANT OWNERSHIP ON SCHEMA "HOLISTICS_DERIVED_MODEL" TO ROLE SYSADMIN REVOKE CURRENT GRANTS;
GRANT ALL ON SCHEMA "HOLISTICS_DERIVED_MODEL" TO ROLE "HOLISTICS_ROLE";

Step 2: Connect Snowflake to Holistics

• From Holistics app's header bar, open Organization Settings menu -> Data Sources.
• Click New Data Source button, and select Snowflake as the database type. Then fill in the form:

  • Display Name - Give the connection a name.

  • Host - Enter the Snowflake hostname without the http prefix. It will look like <account_name>.snowflakecomputing.com (i.e: holistics.ap-southeast-2.snowflakecomputing.com)

  • Port - The default is 443.

  • Database Name - Snowflake database on which the user has been granted USAGE rights in Snowflake HDEMO

  • Username - Snowflake user name for dedicated Holistics user.

  • Password - Snowflake password for dedicated Holistics user.

  • Warehouse - Snowflake warehouse for which the user has been granted USAGE and OPERATE rights in Snowflake DEMO_WH

    
• Click on Test connection to ensure the connection works, and then Save to finish.

Now you can start using Holistics to import, transform, and model data in your Snowflake data warehouse.

Configure Key-pair Authentication in Holistics Data Source

Notice

Currently, Snowflake key-pair authentication is only available when Holistics Canal is enabled.

First, make sure that you have already configured key-pair authentication for your Snowflake User.

Then, follow these steps to configure your Holistics Data Source:

1. Because Holistics Canal is still in Beta, you need to manually enable it first. Click Advanced Holistics Settings
2. Check Enable Holistics Canal
3. Fill in the Private Key of your Snowflake User that you have already generated.

Step 3: Timezone Setup for Timezone Settings feature

If you want to use Timezone Settings feature in Holistics, additional configurations are needed. Head over to Database Setup for Timezone Settings for detailed instructions on how to do this.

FAQs

1. If I have already had users for Holistics (i.e: user TEST), how can I set up permission for this user?

   Using HOLISTICS_ROLE which we guided before, you need to grant HOLISTICS_ROLE to those users and also need to change DEFAULT_ROLE of them to HOLISTICS_ROLE

   GRANT ROLE "HOLISTICS_ROLE" TO USER "TEST";
   ALTER USER "TEST" DEFAULT_ROLE = "HOLISTICS_ROLE";

Troubleshooting

1. I get "The warehouse name is not available. Please check your account again!" when trying to connect Snowflake to Holistics, even if the role HOLISTICS_ROLE is correctly set up. It contains the "OPERATE, USAGE" privileges for the data warehouse.

   Please check to ensure that the DEFAULT_ROLE of the user is the HOLISTICS_ROLE, isn't written in lower case holistics_role (or other string). Remember that Snowflake is case-sensitive.