Skip to main content

Snowflake Component

Execute SQL queries and manage statements in Snowflake.

Component key: snowflake

Changelog ↓

Description​

Snowflake is a cloud data platform. This component allows you to execute SQL queries and manage statement operations within your Snowflake database.

API Documentation​

This component utilizes the Snowflake SQL API.

Connections​

Snowflake Key Pair Authentication​

Snowflake supports key-pair authentication using a public-private key pair. This authentication method provides enhanced security for programmatic access to Snowflake resources.

For detailed information, refer to the Snowflake key-pair authentication guide.

Prerequisites​

  • Active Snowflake account with appropriate user permissions
  • Access to a command line environment or tool capable of generating RSA key pairs
  • Ability to run SQL commands in Snowflake to assign the public key to a user

Setup Steps​

  1. Generate a public-private key pair using OpenSSL or another key generation tool. The following command generates an encrypted private key:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8

This creates a private key file (e.g., rsa_key.p8).

  1. Generate the corresponding public key:
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

  1. Remove the header and footer lines from the public key file, then concatenate the remaining lines into a single line. The public key should be formatted as a continuous string without line breaks.

  2. In Snowflake, assign the public key to the user account by running the following SQL command. Replace USERNAME with the actual username and PUBLIC_KEY_STRING with the formatted public key:

ALTER USER USERNAME SET RSA_PUBLIC_KEY='PUBLIC_KEY_STRING';
  1. Verify the public key assignment by running:
DESCRIBE USER USERNAME;

In the output, verify that the RSA_PUBLIC_KEY_FP property displays the fingerprint of the assigned public key.

Configure the Connection​

  • Enter the Account Name (e.g., myorg-account123)
  • Enter the Username associated with the public key
  • Enter the Private Key content from the rsa_key.p8 file (include the full private key, including the header and footer lines)
  • If the private key is encrypted, enter the Private Key Passphrase
Private Key Security

It is highly recommended to use private key encryption for live integrations and should be kept secure and never shared.

InputNotesExample
Account Identifier

Your Snowflake account identifier. Format: [organization]-[account] or [account].[region]. Find this in the organization's account panel. Learn more

myorg-account123
Passphrase

The passphrase for the provided private key. Leave blank if your key is not encrypted.

Private Key

The private key in PEM format for Snowflake Key Pair Authentication. Generate a key pair and register the public key with your Snowflake user. Learn more

Snowflake Username

Your Snowflake username. This is typically your login name in uppercase.

MYUSER

Snowflake OAuth 2.0​

Snowflake uses OAuth 2.0 for authentication and making API calls. This connection type requires creating a security integration within Snowflake and configuring OAuth settings.

For additional details, refer to the Snowflake OAuth custom client documentation.

Prerequisites​

  • Active Snowflake account with appropriate permissions to create security integrations
  • User account with roles other than ACCOUNTADMIN, SECURITYADMIN, or ORGADMIN (these roles are blocked from OAuth authentication by default)

Setup Steps​

  1. Log in to Snowflake and create a new worksheet to configure the security integration.

  2. Create a new security integration by copying the following SQL statement into Snowflake and selecting the Play button at the top right of the screen. Replace INTEGRATIONNAME with a descriptive name:

CREATE SECURITY INTEGRATION
  INTEGRATIONNAME
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  OAUTH_REDIRECT_URI = '<OauthCallbackUrl />'
  OAUTH_CLIENT_TYPE = 'PUBLIC'

Note: The OAUTH_REDIRECT_URI should be set to the OAuth callback URL: https://oauth2.prismatic.io/callback

  1. Retrieve the Authorization URL, Token URL, and Client ID by running the following SQL statement. Replace INTEGRATIONNAME with the name used in step 2:
DESCRIBE INTEGRATION INTEGRATIONNAME
  1. Retrieve the Client Secret by running the following SQL statement:
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('INTEGRATIONNAME')

Copy the value listed as OAUTH_CLIENT_SECRET from the returned results.

  1. Enable the security integration by running the following SQL statement:
ALTER SECURITY INTEGRATION INTEGRATIONNAME SET ENABLED = TRUE

Configuring Scopes​

Scopes control which Snowflake role is used during the OAuth session. If no scope is specified, the user's default role is used.

Scope Format: session:role:<ROLE_NAME>

Common Scopes:

ScopeDescription
session:role:PUBLICUse the PUBLIC role (available to all users)
session:role:SYSADMINUse the SYSADMIN role for database/warehouse management
session:role:USERADMINUse the USERADMIN role for user/role management
session:role:ANALYSTUse a custom ANALYST role (if configured)

Examples:

  • Single role: session:role:SYSADMIN
  • Multiple roles (space-separated): session:role:SYSADMIN session:role:PUBLIC

Configure the Connection​

  • Enter the Authorization URL from step 3
  • Enter the Token URL from step 3
  • Enter the Client ID from step 3
  • Enter the Client Secret from step 4
  • Optionally configure Scopes
InputNotesExample
Authorize URL

The OAuth 2.0 Authorization URL for your Snowflake account. Format: https://[account-identifier].snowflakecomputing.com/oauth/authorize. Learn more

https://myorg-account123.snowflakecomputing.com/oauth/authorize
Client ID

Client ID of your Snowflake OAuth integration. Obtain this from your security integration configuration. Learn more

AbCdEfGh123456
Client Secret

Client Secret of your Snowflake OAuth integration. Generated when creating the security integration. Learn more

Headers

Additional headers to supply to authorization requests.

Scopes

Controls which Snowflake role is used during the session. Format: session:role:<ROLE_NAME>. If not specified, the user's default role is used. Space separate multiple scopes. Learn more

session:role:SYSADMIN
Token URL

The OAuth 2.0 Token URL for your Snowflake account. Format: https://[account-identifier].snowflakecomputing.com/oauth/token-request. Learn more

https://myorg-account123.snowflakecomputing.com/oauth/token-request

Actions​

Execute SQL​

Executes one or more SQL statements in your Snowflake DB. | key: executeSql

InputNotesExample
Account Locator

The account locator for your Snowflake account. Find this in the organization's account panel. Learn more

xy12345
Bindings

Values of bind variables in the SQL statement. Each binding has a type and value. Learn more

Database

The database name in which the statement should be executed.

MYDB
Debug Request

Enabling this flag will log out the current request.

false
Number of statements to execute

The number of statements to execute. Use 0 to indicate a variable number of statements can be included in the request.

1
Parameters

Session parameters to set for this request. Learn more

Role

The role name to use when executing the statement.

ACCOUNTADMIN
Schema

The schema name in which the statement should be executed.

PUBLIC
Poll for asynchronous results

When true, the action will handle polling for results on queries that take longer than 45 seconds to execute. When false, the action returns immediately after executing the query.

Connection

The Snowflake connection to use.

Snowflake Identifier URL

The Snowflake URL for your account. Format: https://[account-identifier].snowflakecomputing.com

https://myorg-account123.snowflakecomputing.com
SQL statements to run

The SQL statement(s) to execute in Snowflake. Can be a single statement or multiple statements separated by semicolons.

Timeout

Timeout in seconds for statement execution. If the execution takes longer than the specified timeout, the execution is automatically canceled.

60
Warehouse

The warehouse name to use when executing the statement.

COMPUTE_WH

{
  "data": {
    "code": "090001",
    "statementHandle": "01b1e5d3-0606-7c93-0001-4aae0000e506",
    "sqlState": "00000",
    "message": "Statement executed successfully.",
    "createdOn": 1234567890123,
    "statementStatusUrl": "/api/v2/statements/01b1e5d3-0606-7c93-0001-4aae0000e506",
    "resultSetMetaData": {
      "numRows": 2,
      "format": "jsonv2",
      "rowType": [
        {
          "name": "USER_ID",
          "database": "MYDB",
          "schema": "PUBLIC",
          "table": "USERS",
          "type": "FIXED",
          "scale": 0,
          "precision": 38,
          "nullable": false
        },
        {
          "name": "USER_NAME",
          "database": "MYDB",
          "schema": "PUBLIC",
          "table": "USERS",
          "type": "TEXT",
          "length": 16777216,
          "nullable": true
        },
        {
          "name": "CREATED_AT",
          "database": "MYDB",
          "schema": "PUBLIC",
          "table": "USERS",
          "type": "TIMESTAMP_NTZ",
          "scale": 9,
          "nullable": true
        }
      ],
      "partitionInfo": [
        {
          "rowCount": 2,
          "uncompressedSize": 256,
          "compressedSize": 128
        }
      ]
    },
    "data": [
      [
        "1",
        "john.doe@example.com",
        "2024-01-15 10:30:00.000"
      ],
      [
        "2",
        "jane.smith@example.io",
        "2024-01-16 14:22:10.000"
      ]
    ]
  }
}

Get Statement Handle​

Retrieve the current status of a executed statement from Snowflake. | key: getStatementHandle

InputNotesExample
Account Locator

The account locator for your Snowflake account. Find this in the organization's account panel. Learn more

xy12345
Debug Request

Enabling this flag will log out the current request.

false
Partition

The partition number to retrieve from the result set.

1
Connection

The Snowflake connection to use.

Snowflake Identifier URL

The Snowflake URL for your account. Format: https://[account-identifier].snowflakecomputing.com

https://myorg-account123.snowflakecomputing.com
Statement Handle ID

The unique identifier of the statement handle returned from an asynchronous query execution.

01b1e2f3-a4b5-6c7d-8e9f-0a1b2c3d4e5f

{
  "data": {
    "code": "090001",
    "statementHandle": "01b1e5d3-0606-7c93-0001-4aae0000e506",
    "sqlState": "00000",
    "message": "Statement executed successfully.",
    "createdOn": 1234567890123,
    "statementStatusUrl": "/api/v2/statements/01b1e5d3-0606-7c93-0001-4aae0000e506",
    "resultSetMetaData": {
      "numRows": 500,
      "format": "jsonv2",
      "rowType": [
        {
          "name": "ORDER_ID",
          "database": "SALESDB",
          "schema": "PUBLIC",
          "table": "ORDERS",
          "type": "FIXED",
          "scale": 0,
          "precision": 38,
          "nullable": false
        },
        {
          "name": "CUSTOMER_NAME",
          "database": "SALESDB",
          "schema": "PUBLIC",
          "table": "ORDERS",
          "type": "TEXT",
          "length": 16777216,
          "nullable": true
        },
        {
          "name": "ORDER_TOTAL",
          "database": "SALESDB",
          "schema": "PUBLIC",
          "table": "ORDERS",
          "type": "FIXED",
          "scale": 2,
          "precision": 10,
          "nullable": true
        },
        {
          "name": "ORDER_DATE",
          "database": "SALESDB",
          "schema": "PUBLIC",
          "table": "ORDERS",
          "type": "DATE",
          "nullable": true
        }
      ],
      "partitionInfo": [
        {
          "rowCount": 500,
          "uncompressedSize": 25600,
          "compressedSize": 6400
        }
      ]
    },
    "data": [
      [
        "1001",
        "Acme Corporation",
        "15999.99",
        "2024-01-15"
      ],
      [
        "1002",
        "Global Industries",
        "8750.50",
        "2024-01-16"
      ],
      [
        "1003",
        "Tech Solutions Inc",
        "23500.00",
        "2024-01-17"
      ]
    ],
    "partition": 0
  }
}

Changelog​

2025-11-25​

Changed the Passphrase field in the Key Pair Authentication connection to optional, allowing you to use unencrypted private keys without entering a passphrase.