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.
There are 2 main steps to get started using Snowflake with Holistics:
- Create a Holistics user on Snowflake and grant access.
- Connect Snowflake to Holistics as a data source.
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 operates, but this is not mandatory.
Let's go to Snowflake Console page, click on Worksheets and run the following commands for creating the Holistics user.
Change role to
USE ROLE ACCOUNTADMIN;
Create a new role
HOLISTICS_ROLEusing the following command:
- Create a new role
CREATE ROLE IF NOT EXISTS "HOLISTICS_ROLE";
- Assign privileges of the data warehouse to that role Assume you are using predefined warehouse (
DEMO_WH) and database (
HDEMO) You should grant the 2 privileges: OPERATE and USAGE to the
HOLISTICS_ROLEWhen you visit the warehouse's tab (console#/warehouses), click on the
GRANT USAGE ON WAREHOUSE "DEMO_WH" TO ROLE "HOLISTICS_ROLE";
GRANT OPERATE ON WAREHOUSE "DEMO_WH" TO "HOLISTICS_ROLE";
DEMO_WHwarehouse, you can see the privileges of this warehouse have been granted to
- 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";
GRANT SELECT ON FUTURE TABLES IN SCHEMA "HDEMO"."ECOMMERCE" TO ROLE "HOLISTICS_ROLE";
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.
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_ROLE = "HOLISTICS_ROLE"
PASSWORD = "MY_PASSWORD";
GRANT ROLE "HOLISTICS_ROLE" TO USER "HOLISTICS_USER";
Persist transform model or import data to Snowflake
- 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 http prefix. It will look like
Port - The default is 443.
Database Name - Snowflake database on which the user has been granted USAGE rights in Snowflake
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
- 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.
If I have already had users for Holistics (i.e: user
TEST), how can I set up permission for this user?
HOLISTICS_ROLEwhich we guided before, you need to grant
HOLISTICS_ROLEto those users and also need to change
DEFAULT_ROLEof them to
GRANT ROLE "HOLISTICS_ROLE" TO USER "TEST";
ALTER USER "TEST" DEFAULT_ROLE = "HOLISTICS_ROLE";
I get when "The warehouse name is not available. Please check your account again!" when trying to connect Snowflake to Holistics, even if the role
HOLISTICS_ROLEis correctly set up. It contains the "OPERATE, USAGE" privileges for the data warehouse.
Please check to ensure that the
DEFAULT_ROLEof the user is the
HOLISTICS_ROLE, isn't written in lower case
holistics_role(or other string). Remember that Snowflake is case-sensitive.