Document toolboxDocument toolbox

Instructions for Snowflake Data Sharing

Owned by Derek Mannino (Unlicensed)
Last updated: Aug 03, 2023
4 min read

Customer Instructions for Snowflake Data Sharing. – PLEASE MAKE SURE YOU READ ALL INSTRUCTIONS BEFORE YOU BEGIN –

  • Create a standard snowflake account (https://www.snowflake.com/). The account must be in the Amazon (AWS) cloud, and must be in the us-west (Oregon) region.

  • Create an "account admin" user/password for the snowflake account. Do not share this password with Call Potential or any other tool. This is the master account password and should stay secret.

  • Log in to the web console after the account is provisioned, which takes a few minutes.

  • Execute the command

    SELECT CURRENT_ACCOUNT() AS ACCOUNT_ID;

    in a worksheet and send the value of ACCOUNT_ID to CallPotential at data@callpotential.com so that we can enable the data share in your account.

  • Call Potential will respond when the data is shared with your account ID.

  • Switch roles (upper right menu) to “ACCOUNTADMIN” to see the “Inbound” share from CallPotential

  • Click on the "Shares" tab, and click on "Inbound". You should see an "EXPORTS" (if customer is on V1) or V2EXPORTS (if customer is on V2) share from QAA98122 (Call Potential). In rare scenarios you can see both the shares in “Inbound Share”: 

  • If the customer is on V1

    • Click on the "Worksheets" tab, and paste the script below. Change the CALLPOTENTIAL password (on the last line) to your choice. Make sure the "All Queries" checkbox is checked, then click "Run".

    • USE ROLE ACCOUNTADMIN; CREATE ROLE IF NOT EXISTS CALLPOTENTIAL_ROLE; GRANT ROLE CALLPOTENTIAL_ROLE TO ROLE SYSADMIN; -- Create a warehouse CREATE WAREHOUSE IF NOT EXISTS CALLPOTENTIAL_WAREHOUSE WITH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = FALSE; GRANT USAGE ON WAREHOUSE CALLPOTENTIAL_WAREHOUSE TO ROLE CALLPOTENTIAL_ROLE; GRANT OPERATE ON WAREHOUSE CALLPOTENTIAL_WAREHOUSE TO ROLE CALLPOTENTIAL_ROLE; GRANT MONITOR ON WAREHOUSE CALLPOTENTIAL_WAREHOUSE TO ROLE CALLPOTENTIAL_ROLE; -- Create a non-administrator user that has read-only access to the data -- This user should be used by scripts that dump data to .csv, for example. CREATE USER IF NOT EXISTS CALLPOTENTIAL WITH DEFAULT_ROLE = CALLPOTENTIAL_ROLE DEFAULT_WAREHOUSE = CALLPOTENTIAL_WAREHOUSE; ALTER USER CALLPOTENTIAL SET DEFAULT_NAMESPACE = "CALLPOTENTIAL.EXPORTS"; GRANT ROLE CALLPOTENTIAL_ROLE TO USER CALLPOTENTIAL; -- Create the shared database CREATE DATABASE IF NOT EXISTS "CALLPOTENTIAL" FROM SHARE QAA98122."EXPORTS" COMMENT='Shared data from Call Potential'; GRANT IMPORTED PRIVILEGES ON DATABASE "CALLPOTENTIAL" TO ROLE "CALLPOTENTIAL_ROLE"; -- Set the password to your choice. You can run this command again to change it. ALTER USER CALLPOTENTIAL SET PASSWORD = 'MyPassword12345!';

  • If the customer is on V2 then

    • Click on the "Worksheets" tab, and paste the script below. Change the CALLPOTENTIAL password (on the last line) to your choice. Make sure the "All Queries" checkbox is checked, then click "Run".

    • USE ROLE ACCOUNTADMIN; CREATE ROLE IF NOT EXISTS CALLPOTENTIAL_ROLE; GRANT ROLE CALLPOTENTIAL_ROLE TO ROLE SYSADMIN; -- Create a warehouse CREATE WAREHOUSE IF NOT EXISTS CALLPOTENTIAL_WAREHOUSE WITH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = FALSE; GRANT USAGE ON WAREHOUSE CALLPOTENTIAL_WAREHOUSE TO ROLE CALLPOTENTIAL_ROLE; GRANT OPERATE ON WAREHOUSE CALLPOTENTIAL_WAREHOUSE TO ROLE CALLPOTENTIAL_ROLE; GRANT MONITOR ON WAREHOUSE CALLPOTENTIAL_WAREHOUSE TO ROLE CALLPOTENTIAL_ROLE; -- Create a non-administrator user that has read-only access to the data -- This user should be used by scripts that dump data to .csv, for example. CREATE USER IF NOT EXISTS CALLPOTENTIAL WITH DEFAULT_ROLE = CALLPOTENTIAL_ROLE DEFAULT_WAREHOUSE = CALLPOTENTIAL_WAREHOUSE; ALTER USER CALLPOTENTIAL SET DEFAULT_NAMESPACE = "CALLPOTENTIAL.V2EXPORTS"; GRANT ROLE CALLPOTENTIAL_ROLE TO USER CALLPOTENTIAL; -- Create the shared database CREATE DATABASE IF NOT EXISTS "CALLPOTENTIAL" FROM SHARE QAA98122."V2EXPORTS" COMMENT='Shared data from Call Potential'; GRANT IMPORTED PRIVILEGES ON DATABASE "CALLPOTENTIAL" TO ROLE "CALLPOTENTIAL_ROLE"; -- Set the password to your choice. You can run this command again to change it. ALTER USER CALLPOTENTIAL SET PASSWORD = 'MyPassword12345!';

       

  • The CALLPOTENTIAL database should be visible in the 'Databases" tab of the web console. It doesn't have any tables, but it does have many views.

  • Click on the "Worksheets" tab of the web console and start a fresh (empty) worksheet. Ensure the correct warehouse (CALLPOTENTIAL_WAREHOUSE), database (CALLPOTENTIAL), role (CALLPOTENTIAL_ROLE), and schema (EXPORTS/V2EXPORTS) are selected in the upper right. Then run the appropriate query from the queries noted below to ensure that the data sharing is working fine.

 

  • The CALLPOTENTIAL user can be used by other Business Intelligence tools to query the snowflake data directly. It is highly recommended to use the CALLPOTENTIAL user, and not the account admin, since the CALLPOTENTIAL user can't modify or delete objects in your account.

  • The snowsql command can be used by CLI scripts. See https://docs.snowflake.com/en/user-guide/snowsql-install-config.html for details on installing it. After it is installed, you can dump data to .csv, for example:

 

 

NOTE FOR COSTING:

Exploring compute cost | Snowflake Documentation

SnowFlake charges on a per “credit” basis. They define one “credit” as 60 minutes of querying time. This makes each minute of querying time $0.033 for the “x-small” size warehouse.