Connect to Snowflake

Summary of Content

Requirements
Create a Connection
      Connect to Snowflake from Sigma
      Connect to Sigma using Snowflake Partner Connect
Whitelisting Sigma's IP Addresses
Granting Permissions
      ‍Update Grants with SQL
      Update Grants via SnowSQL
      Update Grants With the Snowflake User Interface
Related Resources

Requirements

Create a Connection

You can connect to your Snowflake data warehouse by creating a connection in Sigma or creating a connection via Snowflake Partner Connect. When creating a connection, you will specify a Snowflake user and role that Sigma will use to connect to the database. You can change which user and role is used to connect at any time. Sigma will have access to all of the tables and schemas that the user has access to.

To restrict what data different Sigma users see, you can set up permissions in Sigma using teams.

Connect to Snowflake from Sigma

  1. Open your Admin Portal by selecting Administration in the user menu at the top right of your screen.
  2. Select the Connections page from the left hand panel.
    Screen_Shot_2020-10-28_at_8.13.41_PM.png
  3. Click the Create Connection button.
  4. Name your connection.
  5. Select Snowflake under warehouse type.
    You will then be prompted to specify your Connection Credentials.
    Screen_Shot_2020-05-15_at_1.51.13_PM.png
  6. Under Account, enter the account name of your Snowflake instance.
  7. Under Warehouse, enter your warehouse’s name as listed in Snowflake.
  8. If you have OAuth enabled on your organization, and you would like to use it on the connection, switch on OAuth access. Learn more.
    Please note: Steps 9 - 11 are not applicable if you choose to use OAuth without a service account.
  9. Under User, enter your Snowflake username.
  10. Under Password, enter your Snowflake password.
  11. [optional] Under Role, you can specify a Snowflake role to be used on this connection.
  12. [optional] Under Connection Features, you can set a connection timeout and/or enable write access.
  13. After completing the form, click the Create button.

Connect to Sigma using Snowflake Partner Connect

If you do not already have a Sigma Organization created, you can use Snowflake Partner Connect to create an organization and connect your Snowflake database with just a few clicks. You must be an ACCTADMIN on Snowflake to use Partner Connect.  To sign up with Snowflake Partner Connection:

  1. Go to Snowflake Partner Connect in your Snowflake console.
  2. Switch the user role to `ACCOUNTADMIN`.
  3. Select Sigma from the Partner Connect list.
  4. The Connect to Sigma modal will appear. Click Connect.
  5. Sigma will now open in a new window, and you will be prompted to create your new organization.

Whitelisting Sigma's IP Addresses

If you have IP whitelisting enabled on Snowflake, you will need to whitelist Sigma's egress IP addresses.

Sigma's IP addresses are listed on all individual connection pages in your Sigma Admin Portal. To view them, (1) Open your Admin Portal to the Connection page. (2)  Select any connection or click 'Create Connection' (3) Look for the IP addresses listed under connection credentials. 

Please use the following instructions to whitelist IP addresses on Snowflake.

Granting Permissions

Sigma can see the databases, schemas and tables granted to the user profile used to connect to Sigma.

If you connect to Sigma utilizing Partner Connect, a new user PC_SIGMA_ROLE is created on your Snowflake instance. By default, this new user has all of the PUBLIC grants. To see additional data in Sigma, you can change the user used to connect to Snowflake or update the permission grants to the PC_SIGMA_ROLE user.

You can grant privileges to Sigma via three different methods: Snowflake UI, SQL editor in the Snowflake Console (Worksheets tab), or SnowSQL. In all methods we’ll be granting USAGE privileges to the required Databases and Schemas and SELECT privileges to the required Tables. If you connected via partner connect, you'll grant privileges to the PC_SIGMA_ROLE. If you created your Sigma Org outside of Snowflake you’ll want to give privileges to the role you specified during Connection setup in Sigma.

‍Update Grants with SQL

Enter the commands below into the SQL editor in the Worksheets tab for the Database you wish to give Sigma access to.
Replace YOUR_DATABASE_NAME with the name of your database. Replace YOUR_SCHEMA with the name of the schema you'd like to grant permissions to. Replace YOUR_ROLE with the role you used to connect to Sigma. 

Enter the following commands into the SQL editor in the Worksheets tab for the Database you wish to give Sigma access to:

use WAREHOUSE PC_SIGMA_WH;
use DATABASE YOUR_DATABASE_NAME;
grant usage on database YOUR_DATABASE_NAME to role YOUR_ROLE;‍

Enter the following command into the SQL editor for the schema you wish to give Sigma access to:

grant usage on schema YOUR_SCHEMA to role YOUR_ROLE;

Enter the following command into the SQL editor to grant access to all tables in the schema to Sigma:

grant select on all tables in schema YOUR_SCHEMA to role YOUR_ROLE;

‍

Update Grants via SnowSQL

Enter the following SQL commands into SnowSQL:

Replace YOUR_DATABASE_NAME with the name of your database. Replace YOUR_SCHEMA with the name of the schema you'd like to grant permissions to. Replace YOUR_ROLE with the role you used to connect to Sigma.

use WAREHOUSE PC_SIGMA_WH;
use DATABASE YOUR_DATABASE_NAME;
grant usage on database YOUR_DATABASE_NAME to role YOUR_ROLE;
grant usage on schema YOUR_SCHEMA to role YOUR_ROLE;
grant select on all tables in schema YOUR_SCHEMA to role YOUR_ROLE;

‍

Update Grants With the Snowflake User Interface

When granting permissions through the UI, you need to grant privileges at the Database, Schema and Table level.

  1. Select the Database you would like to grant privileges to. On the right-hand side of the screen there is a button labeled + Grant Privileges. Click that, then grant USAGE to the role you used to connect to Sigma.
  2. Click into the Database you just granted permissions for. Select “Schema” then click on the specific schema you’d like to grant permissions for. Click +Grant Privileges and then grant USAGE to the role you used to connect to Sigma.
  3. Click into the schema that you just granted privileges to. Select the table that you would like to be visible in Sigma. Click +Grant Privileges and then grant SELECT to the role you used to connect to Sigma. You will have to grant access to each table individually.
  4. You can also use SQL to grant access to all of the tables in the schema at once, using the SQL below. Replace YOUR_SCHEMA with the name of the schema you'd like to grant permissions to. Replace YOUR_ROLE with the role you used to connect to Sigma.
grant select on all tables in schema YOUR_SCHEMA to role YOUR_ROLE;

 

Related Resources

Permissions
OAuth with Snowflake
Snowflake Usage Dashboards
Troubleshoot Your Connection

Note: Sigma queries the Snowflake connection once nightly to index the catalogs, schemas, tables, and views. This process is automated and reads from the Snowflake metadata in the cloud services layer. Under normal usage this process is will result in 0 credit consumption; however in some cases when there is minimal usage in a 24 hour period, credit consumption may occur. To learn more about the cloud services layer read through these snowflake docs.

Was this page helpful?
Yes No