Snowflake

This guide will walk you through connecting Datawisp to your Snowflake databases. We assume that you have an existing Snowflake account which is already storing some data and that you are familiar with the basics of using Snowflake.

This guide requires that you have at least USERADMIN permissions on your Snowflake account. If you do not have these permissions, or aren't sure, e.g. because someone else is administering the account, please send this guide to them.

To get started, log into your Snowflake account. Create a new SQL worksheet, then paste and run the following commands:

-- create a role you can use to grant privileges to Datawisp
CREATE ROLE IF NOT EXISTS datawisp;

-- OPTIONAL: create a dedicated warehouse for Datawisp
-- the settings below are our recommendations, you may tune them to your needs
CREATE OR REPLACE WAREHOUSE datawisp_wh
    WAREHOUSE_SIZE = SMALL -- appropriate for small datasets, increase as needed
    AUTO_SUSPEND = 120
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE
    ;
-- create a user for Datawisp to log in with
CREATE OR REPLACE USER datawisp
    TYPE = SERVICE
    DEFAULT_WAREHOUSE = 'DATAWISP_WH' -- change if you skipped the last step
    DEFAULT_ROLE = 'DATAWISP'
    ;
GRANT ROLE datawisp TO USER datawisp;
-- change warehouse name if you're using an existing warehouse
GRANT USAGE ON WAREHOUSE datawisp_wh TO ROLE datawisp;

After successfully running these commands, grant permissions on the database you want to import to the newly created datawisp role. You can either do this manually through the Snowflake UI or using SQL commands:

SET mydb = 'MY_DATABASE'; -- replace with the name of the database to grant access to
GRANT USAGE ON DATABASE IDENTIFIER($mydb) TO ROLE datawisp;
GRANT USAGE ON ALL SCHEMAS IN DATABASE IDENTIFIER($mydb) TO ROLE datawisp;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE IDENTIFIER($mydb) TO ROLE datawisp;
GRANT SELECT ON ALL TABLES IN DATABASE IDENTIFIER($mydb) TO ROLE datawisp;
GRANT SELECT ON FUTURE TABLES IN DATABASE IDENTIFIER($mydb) TO ROLE datawisp;

Now open a new browser tab or window and go to Datawisp. We are going to switch back and forth between Snowflake an Datawisp a few times.

Head over to the Data Sources page and press Import.

Select Database and change the Connection type to Snowflake.

Choose a Title for the datasource.

Then go back to Snowflake. Click on the button in the bottom left, then hover over Account. This pops up a second menu with all your Snowflake accounts. There, hover over the account you're logged in with (the one with the Checkmark). This pops up a third menu. There, in the top right corner, click the Copy account identifier button. Go back to Datawisp and paste this under Account Identifier.

Under Database, type the name of the database in Snowflake that you wish to import.

Carefully verify that the database name is capitalized exactly as in Snowflake. Database names in Snowflake are UPPERCASE by default, so this is how you should enter them in Datawisp. Otherwise, you may later run into errors even if the connection initially succeeds and appears to work.

Under Username, type datawisp.

Then click on the ALTER USER command under RSA Public Key.

Head back to Snowflake and run this command.

For security reasons, Datawisp always creates a new RSA key when you import a Snowflake database. Until you complete the import, this key does not leave your PC. This may cause problems in certain edge cases such as the following:

• You send this guide to a colleague because you don't have USERADMIN privileges on Snowflake. Your colleague runs through all the steps, including the ALTER USER command at the end that sets the RSA key. Then your colleague sends the connection details to you. You fill in the data and press Connect. The connection fails because Datawisp attempts to connect with your key, but on Snowflake your colleague's key was registered.

Then go back to Datawisp and click Connect.

If everything worked, Datawisp will tell you that the connection to your Snowflake database was successful: