Skip to main content

Celonis Product Documentation

Snowflake (REST) Connection

You can use the Snowflake (REST) Extractor to extract data from a Snowflake database using the Snowflake SQL API.

This is the recommended option for Snowflake. The alternative is our Snowflake (JDBC) Extractor which can sometimes be less reliable because it uses an open-source driver.

The REST Extractor provides the following benefits:

  • It uses the Snowflake SQL API rather than a third-party open source Snowflake driver.

  • The SQL API gives more control over error handling.

  • It extracts threads in parallel from native Snowflake partitions returned by the Snowflake SQL API. This results in a performance increase of approximately 1.6 times.

Authentication and permissions

The Extractor authenticates with the Snowflake database using OAuth2.

  1. Log into your Snowflake instance.

  2. Create a new user role, grant permission, and creating a user. Run the below commands as Useradmin in the Worksheets window.

    Creation of user, role, permissions

    # Create a new role for celonis
    create role celonis_role;
    
    # Grant permissions for that role 
    grant select on all tables in schema MY_DB.PUBLIC to role celonis_role;
    
    # Create a new user for celonis (needed in Step 5).
    create user celonis password='celonis123';
    
    # Grant celonis permissions to celonis user
    grant role celonis_role to user celonis;
  3. Create a security integration group. Run the below commands as Accountadmin in the Worksheets window.

    Security group integration

    # Create security integration for the oauth
    create security integration oauth_kp_int
     type = oauth
     enabled = true
     oauth_client = CUSTOM
     oauth_client_type = 'CONFIDENTIAL'
     oauth_redirect_uri = 'https://auth.redirect.celonis.cloud/snowflake_redirect'
    
     oauth_issue_refresh_tokens = true
     oauth_refresh_token_validity = 7776000;
    
    # Allow celonis user to use the integration as Accountadmin
    ALTER USER celonis ADD DELEGATED AUTHORIZATION OF ROLE celonis_role TO SECURITY INTEGRATION oauth_kp_int;
  4. Retrieve the Client ID and Client secret for the OAuth integration as Accountadmin. This information is relevant in Step 5 to set up the data connection in Celonis.

    Cliend ID and Client secret

    # Get the client-id & client-secret for the integration; Note that the integration name is case-sensitive and must be uppercase and enclosed in single quotes.
    
    select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH_KP_INT');
  5. Log into Celonis. Navigate to Eventcollection → Data Pool → Data Connection → New Data connection:

    1. Select the Snowflake Tile.

    2. Provide the standard data connection configuration information.

    3. Select OAuth in the Credentials section:

      1. Provide Client ID and Client secret from Snowflake based on Step 4.

    4. Save the connection.

    5. You are redirected to log into Snowflake.

      1. Use the username and password created in Step 2.

      2. Allow the security integration to access the Snowflake account.