To allow Xplenty access to Snowflake:
In order to allow Xplenty access to your Snowflake account, login to your Snowflake account using your favorite SQL client and execute the following commands to create a user with proper permissions for Xplenty:
USE ROLE accountadmin; -- use this role to be able to grant permissions CREATE ROLE xplenty; -- create a role to assign to the Xplenty user CREATE USER xplenty PASSWORD='<password>' DEFAULT_ROLE = xplenty; -- create a user with a password GRANT ROLE xplenty TO USER xplenty; -- assign user to role -- run the following statements for each database you'd like Xplenty to access: GRANT USAGE ON DATABASE "<database_name>" TO ROLE xplenty; -- grant database access to Xplenty GRANT CREATE SCHEMA ON DATABASE "<database_name>" TO ROLE xplenty; -- grant schema creation privilege to Xplenty GRANT ALL ON SCHEMA "<schema_name>" TO ROLE xplenty; -- grant Xplenty privileges to do anything within the defined schema GRANT ALL ON SCHEMA public TO ROLE xplenty;
GRANT ALL ON ALL TABLES IN SCHEMA "<database_name>"."<schema_name>" TO ROLE xplenty; -- grant Xplenty privileges to all tables in the schema
-- run the following statements for each warehouse you'd like Xplenty to use: GRANT ALL ON WAREHOUSE "<warehouse_name>" TO xplenty; -- allow Xplenty to use a warehouse
To create a Snowflake connection in Xplenty:
- Click the Connections icon (lightning bolt) on the top left menu.
- Click New connection and choose Snowflake.
- Type a name for the connection.
- Enter your account name (e.g. xplenty if you login to xplenty.snowflakecomputing.com)
- Enter the user name and password you created for Xplenty to use.
- Enter the default database to use.
- Enter the default warehouse to use.
- Set the region to your Snowflake's account's region.
- Click Test Connection. If the credentials are correct, a message that the connection test was successful appears.
- Create Connection.
Xplenty uses quoted identifiers which means that by default, all identifiers (table and column names for instance) are case sensitive - as opposed to non-quoted identifiers which are case insensitive (and are stored in upper case). You may want to change the behavior by changing the
QUOTED_IDENTIFIERS_IGNORE_CASE setting for Xplenty's user or for your entire account. Note that tables and columns that were created with case sensitive names remain case sensitive, so it's important to make this change before you create any tables. If you want to switch a case sensitive identifier name to a case insensitive identifier name after it's been created use the
ALTER ... RENAME clauses in Snowflake. Read more on Snowflake identifiers for more information.