Connect to Snowflake

Sigma provides robust connection support for Snowflake data access.

Sigma queries the Snowflake connection every 24 hours 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 results in 0 credit consumption; however in cases where there is minimal usage in a 24 hour period, credit consumption may occur. See Understanding Overall Cost in Snowflake documentation.

Contents

Requirements
Create a connection
      Connect to Snowflake from Sigma
      Connect to Sigma using Snowflake Partner Connect
Add Sigma's IP addresses to the allowlist
Grant permissions
      ‍Update grants through SQL
      Update grants through SnowSQL
      Update grants through the Snowflake user interface
Related resources

Requirements

• Admin privileges in your Sigma organization; see account types.
• We recommend that you avoid granting excessive permissions to the account you use when connecting to your data store; for example, you do not require SYSADMIN-level access.

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

Follow these basic steps to create a connection:

1. Click the user icon at the top right of your screen. 
   The user icon is usually composed of your initials.
2. In the drop-down menu, select Add connection.
   
3. The Add new connections page appears.
   

4. In the Connection details, specify these:
   

   Name

   Specify the Name of the new connection. Sigma displays this name in the connection list.

   Here, we use Snowflake connection.

   Type

   Select the tile that represents the data warehouse you use.

   Here, click the Snowflake tile.

5. Specify your Connection Credentials.

   

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; see OAuth in Snowflake.
   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. In the Connection Features section, specify the following:

    Connection timeout

    The time before timeout (or cancellation), in seconds, that Sigma waits for the query to return results.

    Default is 120, or 2 minutes.

    Maximum is 600, or 10 minutes.

    Use friendly names

    This switch makes column names from the data source more readable.

    For example, a database column ORDER_NUMBER appears as Order Number.

    On by default.

13. In the Write Access section, decide if you require write access.
    This is necessary for the following features: 
    • CSV upload
    • Materialization
    • Input tables
    • Dataset warehouse views

    See Write access.

    Enable write access

    Off by default.

    If you enable write access, specify these fields:

    Write database

    Required

    The database where Sigma has write access.

    Write schema

    Required

    The schema where Sigma has write access.

    Materialization warehouse

    Optional

    A separate warehouse where Sigma writes materialization queries.

    
14. 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.
   

Add Sigma's IP addresses to the allowlist

If you have IP allowlists enabled on Snowflake, you must add Sigma's egress IP addresses.

Sigma's IP addresses are listed on all individual connection pages in your Sigma Admin Portal.

To view them, follow these steps:

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. 

Follow the instructions in Snowflake's Network Policies document.

Grant permissions

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

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

You can grant privileges to Sigma through three different methods:

• Snowflake UI
• SQL editor in the Snowflake Console, in the Worksheets tab
• SnowSQL

In all methods, we grant USAGE privileges to the necessary databases and schemas, and SELECT privileges to the necessary tables. If you connected through partner connect, grant privileges to the PC_SIGMA_ROLE. If you created your Sigma Org outside of Snowflake, you must give privileges to the role you specified during Connection setup in Sigma.

‍Update grants through SQL

Enter the following commands into the SQL editor in the Worksheets tab for the Database where you plan to give Sigma access.
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;

Enter the following command into the SQL editor for the views you plan to list on Sigma:

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

‍

Update grants through 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;
grant select on all views in schema YOUR_SCHEMA to role YOUR_ROLE;

Update grants through 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;
grant select on all views in schema YOUR_SCHEMA to role YOUR_ROLE;

Write access

To set up Sigma write access for a Snowflake connection,

1. Specify a database and schema where Sigma writes.
   The Snowflake user who connects to Sigma must have the following permissions:

   Database

   USAGE

   Schema

   USAGE, CREATE TABLE, CREATE VIEW, and CREATE STAGE

2. To grant the necessary permissions in Snowflake, open your Snowflake instance and follow the steps below.
3. 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_SIGMA_ROLE with the role you used to connect. If you connected using Snowflake Partner Connect, use PC_SIGMA_ROLE.
4. Enter the following commands into the SQL editor in the Worksheets tab for the Database you wish to give Sigma access to:
   

   use WAREHOUSE <YOUR_SIGMA_WH>;
   use DATABASE <YOUR_DATABASE_NAME>;
   grant usage on database <YOUR_DATABASE_NAME> to role <YOUR_SIGMA_ROLE>;

5. Enter the following command for the schema you plan to access:

   grant usage, create table, create view, create stage on schema <YOUR_SCHEMA> to role <YOUR_SIGMA_ROLE>;

Related resources

• Permissions
• OAuth with Snowflake
• Snowflake usage dashboards
• Troubleshoot your connection
• Understanding Overall Cost in Snowflake documentation