Set up a Snowflake connection for Python (Beta)

If you set up Python with your Snowflake connection, you can write and run Python code in Sigma as a notebook-style experience, elevating the level of complex analysis that you can perform on your data and reducing friction between business analytics and data science.

When you run Python code in Sigma, a stored procedure in your Snowflake account runs the code. Follow the steps in this document to configure your Snowflake account to run Python code from your Sigma organization:

1. (Optional, but recommended) Set up Snowflake to run Python code from Sigma.
2. Create or update your Snowflake connection from Sigma.

📘

To run Python, you must use key pair or OAuth authentication to connect to Snowflake from Sigma.

User requirements

The following permissions and privileges are required to perform these one time setup steps in Sigma and Snowflake:

• In your Sigma organization, you must be assigned the Admin account type.

• In Snowflake, you must have access to a role granted the relevant privileges to complete the optional steps:

  • If you create a warehouse, your role must be granted the CREATE WAREHOUSE privilege.
  • If you plan to create a network rule and external access integration, your role must be granted the CREATE NETWORK RULE, CREATE INTEGRATION, and CREATE EXTERNAL ACCESS INTEGRATION privileges.

Set up Snowflake to run Python code from Sigma

Before connecting Sigma to your Snowflake account, complete the following optional but recommended steps in Snowflake:

1. (Optional) Create a warehouse to isolate the Python compute load from your SQL query load.
2. (Optional) Allow access to third-party systems to permit users writing Python in Sigma to call API endpoints.

After you complete these steps, create or update your Snowflake connection from Sigma.

🚩

If you previously created a stored procedure manually in your Snowflake account to run Python code from Sigma, do the following:

1. Drop the existing stored procedure that you created, replacing the placeholders with the path:

   SQL

   DROP PROCEDURE IF EXISTS <DB>.<SCHEMA>.RUN_PYTHON_CODE(VARCHAR, VARCHAR)

2. Update your Snowflake connection from Sigma.

Sigma automatically creates a new stored procedure to run Python code from Sigma.

Create a warehouse

Sigma recommends that you create a dedicated warehouse to run the Python code from Sigma. A dedicated warehouse helps allocate the relevant memory to run Snowpark Python in Snowflake, and prevents Python code execution from slowing down SQL queries run in Sigma, in cases of an overloaded warehouse.

The warehouse that you create and use depends on your expected usage and other considerations. Refer to the following table to compare the tradeoffs:

Allow access to third-party systems

If you want users to be able to work with third-party systems in their Python code in Sigma, such as call public API endpoints, complete the following steps. This process is optional and not required to run Python code in Sigma.

Create a network rule

Create a network rule with the following SQL syntax. You can create the network rule in any database and schema in your Snowflake account.

Replace the example value 'example.com' with one or more third-party URLs that you want users running Python code from Sigma to be able to access, such as public API endpoints.

📘

If you already have a network rule set up to grant host_port egress to specific URLs, you can reuse it instead of creating a new one.

Run the following SQL command:

CREATE OR REPLACE NETWORK RULE py_script_network_rule
    MODE = EGRESS
    TYPE = HOST_PORT
    VALUE_LIST = ('example.com');

For more details about creating a network rule in Snowflake, see CREATE NETWORK RULE in the Snowflake documentation.

Create an external access integration

In a Snowflake database and schema, create an external access integration to allow access to the external network locations specified in the network rule that you created.

📘

If you reuse a previously created network rule, reference the name of that rule in the ALLOWED_NETWORK_RULES argument.

Run the following SQL command:

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION py_script_access_integration
    ALLOWED_NETWORK_RULES = (py_script_network_rule)
    ENABLED = TRUE;

After creating the external access integration, record the name. You need to know the name of the external access integration when adding or modifying your Snowflake connection to Sigma.

💡

You can create the external access integration in any database and schema in your Snowflake account.

For more details about creating an external access integration in Snowflake, see CREATE EXTERNAL ACCESS INTEGRATION in the Snowflake documentation.

Grant privileges

After creating the external access integration, grant privileges on the integration to allow Sigma to use it:

• If you plan to use key pair authentication to connect Snowflake to Sigma, grant the privileges to the role used for key pair authentication, or the default role assigned to the user set up for key pair authentication.
• If you plan to use OAuth authentication to connect Snowflake to Sigma, grant the privileges to the role used by the service account, or the default role assigned to the user set up as the service account.

Run the following SQL command, replacing the <role> value with the relevant role:

GRANT USAGE ON INTEGRATION py_script_access_integration TO ROLE <role>;

Create or update your Snowflake connection from Sigma

To start running Python code on your Snowflake account, update your existing Snowflake connection with Sigma, or create one.

Requirements

• You must be assigned the Admin account type.

• You must connect to Snowflake using key pair authentication or OAuth authentication. Basic authentication is not supported.

• If you connect to Snowflake using OAuth, the role associated with your user must be granted the following privileges:

  • CREATE PROCEDURE and USAGE on the schema used as the Python write-back destination.
  • If you use an external access integration, USAGE on the external access integration.

• If you connect to Snowflake using key pair authentication:

  • You must know the public key fingerprint associated with the Snowflake user used for key pair authentication. To retrieve the public key fingerprint:

    • After the public key is assigned to the Snowflake user, run the DESCRIBE USER command. The RSA_PUBLIC_KEY_FP property in the output contains the public key fingerprint assigned to the user.
    • Prepend the public key fingerprint with SHA256: to match the formatting required by Sigma

  • The role associated with the key pair user must be granted the following privileges:

    • CREATE PROCEDURE and USAGE on the schema used as the Python write-back destination.
    • If you use an external access integration, USAGE on the external access integration.

Create or update your connection

When you create or update your Snowflake connection to start running Python code from Sigma, you must configure two Python-specific settings:

1. Follow the steps to Connect to Snowflake.

2. Choose your authentication method and complete the relevant steps:

   • Connect to Snowflake with key pair authentication.

     For Public key fingerprint, provide the public key fingerprint that you collected as part of the prerequisites. The public key fingerprint must be prepended by SHA256:.

   • Connect to Snowflake with OAuth.

3. Complete the steps to Configure write access. Write access is required to run Python code.

4. Complete the steps to Configure connection features.

5. In the Python section, turn on the toggle for Enable Python queries.

6. (Optional) For Warehouse, provide the warehouse name to use to run Python code that you created in Create a warehouse.

7. (Optional) For Packages, specify the names of packages in the Snowflake conda channel that you want to make available to users running Python in Sigma.

   By default, the following packages are included: pygments, requests, scipy, scikit-learn, snowflake-snowpark-python, and urllib3.

8. If you use OAuth to authenticate your connection, for Python write-back destination, specify the schema in which to create the stored procedure used to run Python code. For example, SIGMA.SIGMA_PYTHON.

   Connections that use key pair authentication create the stored procedure in the specified Write schema.

9. (Optional) For External access rule, specify the name of the external access integration that you created in Create an external access integration.

10. Follow the steps to Finish creating your connection.

After you finish creating your connection, Sigma creates a stored procedure in the specified write schema. When the stored procedure is created, the OWNERSHIP privilege is granted to either the role associated with the user used for key pair authentication, or the role of the user that set up the connection with Snowflake in Sigma, if OAuth is used to authenticate to the connection.

The stored procedure is used to run Python code from Sigma and runs in Snowflake with caller's rights. For details on who Python code runs as from Sigma, see Who the Python code runs as.