Configure Snowflake for Datacoral

As part of setting up the Datacoral platform with Snowflake, you will be asked to provide the following fields.

Step 1: Run the commands in Snowflake

Run the following commands to setup the Datacoral user in Snowflake and provide the right permissions.

------------------ ONLY CHANGE THE VALUES IN THE SET STATEMENTS ---------------
SET ROLE_NAME = '<choose a name for Datacoral role>';
SET USER_NAME = '<choose a user name>';
SET USER_PASSWORD = '<choose a password>';
SET DATABASE_NAME = '<choose a name for database>';
-- Please make sure to provide warehouse name below in upper case
SET WAREHOUSE_NAME = '<choose a name for destination warehouse>';
--------------------- DO NOT CHANGE BELOW THIS LINE ---------------------------
BEGIN;
-- 1. Use the SYSADMIN role for the next steps. The system administrator (SYSADMIN)
-- role includes the privileges to create warehouses, databases, and all
-- database objects (schemas, tables, etc.).
USE ROLE SYSADMIN;
-- 2. Create a new Snowflake database for all Datacoral created and owned tables.
-- Datacoral will create separate schemas for each Datacoral connector and the
-- tables corresponding to loadunits or materialized views within each schema.
CREATE DATABASE IF NOT EXISTS identifier($DATABASE_NAME) -- pick a database name
COMMENT = 'DATACORAL_OWNED';
-- 3. Create a new Snowflake warehouse for compute use by Datacoral. Size this
-- small and expect it to grow as the data and materialized views grow.
-- This isolates the Datacoral owned pipelines from the rest of the work loads.
CREATE WAREHOUSE IF NOT EXISTS identifier($WAREHOUSE_NAME) -- pick a warehouse name
WITH
WAREHOUSE_SIZE = XSMALL
WAREHOUSE_TYPE = STANDARD
AUTO_RESUME = TRUE
AUTO_SUSPEND = 60
INITIALLY_SUSPENDED = TRUE
COMMENT = 'DATACORAL_OWNED';
-- 4. Use the SECURITYADMIN for the rest of the steps. The security administrator
-- (SECURITYADMIN) role includes the privileges to create and manage users and roles.
USE ROLE SECURITYADMIN;
-- 5. Create a new Role that will be used by the Snowflake User used by Datacoral.
CREATE ROLE IF NOT EXISTS identifier($ROLE_NAME) COMMENT = 'DATACORAL_OWNED';
GRANT ROLE identifier($ROLE_NAME) TO ROLE SYSADMIN;
-- 6. Grant permissions of the database, warehouse
GRANT USAGE, MONITOR ON WAREHOUSE identifier($WAREHOUSE_NAME) TO identifier($ROLE_NAME);
GRANT USAGE, CREATE SCHEMA, MONITOR ON DATABASE identifier($DATABASE_NAME) TO ROLE identifier($ROLE_NAME);
-- 7. Create a new Snowflake user and assign it a Snowflake Role that
-- gives the user access to the database and warehouse created in Step 1 & 2.
CREATE USER IF NOT EXISTS identifier($USER_NAME)
PASSWORD = $USER_PASSWORD
DEFAULT_WAREHOUSE = $WAREHOUSE_NAME
DEFAULT_NAMESPACE = $DATABASE_NAME
DEFAULT_ROLE = $ROLE_NAME
COMMENT= 'DATACORAL_OWNED';
-- 8. Grant the newly created role to the Snowflake user.
GRANT ROLE identifier($ROLE_NAME) TO USER identifier($USER_NAME);
COMMIT;

Here is the explanation of the user defined fields

Field NameDescription
AccountThis can be found in the URL that you use to login to Snowflake. For example wm00000 from https://wm00000.snowflakecomputing.com/console#/internal/worksheet. The account name sometimes contains a region or cloud provider name as well.
UserUser name used to login to Snowflake
RoleRole with the right privileges dedicated to Datacoral. The Usershould have this role
PasswordPassword for User
DatabaseName of the Snowflake database to get data populated by Datacoral
WarehouseName of the Snowflake warehouse to isolate Datacoral workloads

Step 2: Verify that the above commands

DESCRIBE USER identifier($USER_NAME);

The output of the above statement should contain the following based on the names you chose.

propertyvaluedefaultdescription
NAME<USER_NAME>
COMMENTDATACORAL_OWNEDnulluser comment associated to an object in the dictionary
DISPLAY_NAME<USER_NAME>nullDisplay name of the associated object
LOGIN_NAME<USER_NAME>nullLogin name of the user
............
DEFAULT_WAREHOUSE<WAREHOUSE_NAME>nullDefault warehouse for this user
DEFAULT_NAMESPACE<DATABASE_NAME>nullDefault database namespace prefix for this user
DEFAULT_ROLE<ROLE_NAME>nullPrimary principal of user session will be set to this role
............

Step 3: Add Elastic IP to your network policy

Look for the elastic ip of your Datacoral installation in the installation settings. Add it to the network policy by running the following statements. Note that you will need to add /32 to the end of the elastic ip value to indicate that you are allowing traffic from a single IP address.

use role securityadmin;
create network policy DATACORAL_NETWORK_POLICY allowed_ip_list=('<elastic-ip>/32');

Step 4: Follow along the Datacoral onboarding flow

Once the above steps are done, you can provide the credentials to your snowflake cluster in the onboarding flow.