This guide contains instructions regarding the required roles and privileges that are required by Supermetrics for Snowflake.
Create DATABASE in Snowflake dedicated to your Supermetrics data transfers
- Note: You can also use an already existing DATABASE. When doing so, please start from the 3rd point instead (creating a Snowflake SCHEMA for your transfer).
1. Required GLOBAL privilege: “CREATE DATABASE”
2. If you don’t have a role containing this privilege, contact your ACCOUNTADMIN to either grant the role this privilege or create the database for you.
- NOTE: If the Admin creates the database on your behalf, they will need to grant you sufficient privileges to use the database and create schemas, i.e. DATABASE privileges “USAGE” and “CREATE SCHEMA”:
3. Create a Snowflake SCHEMA for your transfer. A Snowflake schema contains a collection of tables or views under a specific Snowflake database. You can either use 1 Snowflake schema within a Destination you created in Supermetrics for all of your transfers or you can set up multiple Snowflake schemas if you prefer to load the data of your transfers into separate Snowflake schemas.
- NOTE: If you plan on using multiple Snowflake schemas for your Supermetrics transfers, you will need to create a separate Destination per Snowflake schema, as described here:
Creating a destination: Prerequisites for Snowflake »
There are two possible “OWNER” scenarios:
- Creating a schema owned by role “PUBLIC”. NOTE: This schema will be visible for all Snowflake users in your account
- Creating a schema owned by a non-”PUBLIC” role, either by one of the other “System-defined roles”:
or a “Custom Role”:
The “OWNER” is defined by what “ROLE” you have selected when creating the schema:
If a user creates a non-”PUBLIC” schema with another role (e.g. SYSADMIN) on your behalf, that user needs to grant your role the SCHEMA privileges “USAGE” and “CREATE TABLE”, so you can create a working “Destination” and “Transfer” at team.supermetrics.com:
4. Once the schema has been created, you can go to team.supermetrics.com/destinations in order to create the Snowflake destination:
NOTE: If your Snowflake user has multiple roles to choose from, Supermetrics will use your “DEFAULT” role. Thus, if your user has the default role “PUBLIC” but the Schema to which you want to load your Ad data is not “PUBLIC”, then the connection cannot be established from our Supermetrics product. In this case, you will need to change the default role of your user in Snowflake:
User 1 creates schema “FB_ADS” with role “custom_role_1” under a Snowflake database “SUPERMETRICS_DB” for user 2 who only has the roles “PUBLIC” and “custom_role_2” available. The company policy is to avoid creating “PUBLIC” DB and schemas in Snowflake. The role “PUBLIC” is set as default for user 2 and user 2 is the one who is to set up multiple DESTINATIONS and TRANSFERS at team.supermetrics.com.Here are the required steps user 1 and 2 need to perform before the DESTINATION can be created:
- User 1 needs to make sure user 2 can use the DB “SUPERMETRICS_DB” and create Schemas under that DB, i.e. user 1 needs to grant DB privileges “USAGE” and “CREATE SCHEMA” to user 2’s role “custom role 2”:
GRANT USAGE, CREATE SCHEMA ON DATABASE SUPERMETRICS_DB TO ROLE "custom_role_2"
- The step above is only required if user 2 is meant to create additional schemas under “SUPERMETRICS_DB” in the future. Otherwise, the DB privilege “USAGE” is sufficient
- In order for User 2 to be able to use schema “FB_ADS” as destination, User 1 needs to grant the needed SCHEMA privileges “USAGE” and “CREATE TABLE”:
GRANT USAGE, CREATE TABLE ON SCHEMA FB_ADS TO ROLE "custom_role_2"
- NOTE: User 1 needs to make sure that the correct role “custom_role_1” and DB “SUPERMETRICS_DB” are selected in Snowflake in order to be able to grant the privileges to user 2:
- Once User 2 can see and use DB “SUPERMETRICS_DB”, User 2 needs to create the schema/s that will serve as destination/s for Supermetrics, e.g. User 2 wants to create a schema “GOOGLE_ADS” in addition to the already existing schema “FB_ADS”:
CREATE SCHEMA GOOGLE_ADS
- NOTE: As above, user 2 needs to make sure that the correct role “custom_role_2” and DB “SUPERMETRICS_DB” are selected.
- As the last step in Snowflake, user 2 needs to make sure that his user has the correct role set as default. As it’s currently set at role “PUBLIC”, and the DB and schema privileges were granted to role “custom_role_2”, the destination creation at Supermetrics will FAIL. Thus, user 2 needs to set his default role to “custom_role_2” (as that’s the role with the required privileges for “SUPERMETRICS_DB” and schema “FB_ADS”:
ALTER USER IF EXISTS USER2 SET DEFAULT_ROLE = 'custom_role_2'
- Once the schema/s are ready, user 2 can proceed with creating the destination at team.supermetrics, as described here: