Snowflake Component

Execute SQL queries and manage statements in Snowflake.

Component key: snowflake

Description

Snowflake is a cloud data platform. This component allows executing SQL queries and managing statement operations within Snowflake databases.

API Documentation

This component utilizes the Snowflake SQL API.

Connections

Key Pair Authentication

key: snowflakeKeyPairConnection

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

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

Generate the corresponding public key:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

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.
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';

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

Create a connection of type Key Pair Authentication and enter:

Account Identifier: The Snowflake account identifier (e.g., myorg-account123)
Snowflake Username: The username associated with the public key
Private Key: The full content from the rsa_key.p8 file (include the header and footer lines)
Passphrase: The private key passphrase (if the key is encrypted)

Private Key Security

Private key encryption is highly recommended for production integrations. Keep keys secure and never share them.

Input	Notes	Example
Account Identifier	The Snowflake account identifier. Format: [organization]-[account]. Find this in the organization's account panel. Learn more. For the JWT token generation, account identifiers need follow snowflake's iss format requirements.	myorg-account123
Passphrase	The passphrase for the provided private key. Leave blank if the 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 the Snowflake user. Learn more
Snowflake Username	The Snowflake username for authentication. This is typically the login name in uppercase.	MYUSER

OAuth 2.0

key: snowflakeOauth2

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 excluding ACCOUNTADMIN, SECURITYADMIN, or ORGADMIN (these roles are blocked from OAuth authentication by default)

Setup Steps

Log in to Snowflake and create a new worksheet to configure the security integration.
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

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

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.

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:

Scope	Description
`session:role:PUBLIC`	Use the PUBLIC role
`session:role:SYSADMIN`	Use the SYSADMIN role for database/warehouse management
`session:role:USERADMIN`	Use the USERADMIN role for user/role management
`session:role:ANALYST`	Use 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

Create a connection of type OAuth 2.0 and enter:

Authorization URL: From step 3
Token URL: From step 3
Client ID: From step 3
Client Secret: From step 4
Scopes: Optionally configure scopes to control the Snowflake role
Headers: Optionally add additional authorization request headers

Input	Notes	Example
Authorize URL	The OAuth 2.0 Authorization URL for the Snowflake account. Format: https://[account-identifier].snowflakecomputing.com/oauth/authorize. Learn more	https://myorg-account123.snowflakecomputing.com/oauth/authorize
Client ID	The Client ID of the Snowflake OAuth integration. Obtain this from the security integration configuration. Learn more	AbCdEfGh123456
Client Secret	The Client Secret of the 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 the 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

Input	Notes	Example
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.	false
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

{
  "branch": "Asynchronous",
  "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

Input	Notes	Example
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

2026-02-06

Fixed an issue where the Key Pair Authentication connection applied unnecessary formatting to the Username field, which could cause JWT authentication failures for usernames that should not be uppercased or have dots replaced with hyphens.

2025-12-19

Fixed an issue where the Execute SQL action failed when polling for async query results due to an incorrect API endpoint URL.

2025-11-25

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

Execute SQL queries and manage statements in Snowflake.

Description​

API Documentation​

Connections​

Key Pair Authentication​

Prerequisites​

Setup Steps​

Configure the Connection​

OAuth 2.0​

Prerequisites​

Setup Steps​

Configuring Scopes​

Configure the Connection​

Actions​

Execute SQL​

Get Statement Handle​

Changelog​

2026-02-06​

2025-12-19​

2025-11-25​

Description

API Documentation

Connections

Key Pair Authentication

Prerequisites

Setup Steps

Configure the Connection

OAuth 2.0

Prerequisites

Setup Steps

Configuring Scopes

Configure the Connection

Actions

Execute SQL

Get Statement Handle

Changelog

2026-02-06

2025-12-19

2025-11-25