Snowflake (Preview)

Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and more flexible than traditional offerings.

This connector is available in the following products and regions:

Service Class Regions
Logic Apps Standard All Logic Apps regions except the following:
     -   Azure Government regions
     -   Azure China regions
     -   US Department of Defense (DoD)
Power Automate Premium All Power Automate regions except the following:
     -   US Government (GCC)
     -   US Government (GCC High)
     -   China Cloud operated by 21Vianet
     -   US Department of Defense (DoD)
Power Apps Premium All Power Apps regions except the following:
     -   US Government (GCC)
     -   US Government (GCC High)
     -   China Cloud operated by 21Vianet
     -   US Department of Defense (DoD)
Contact
Name Snowflake
URL https://www.snowflake.com/support
Email support@snowflake.com
Connector Metadata
Publisher Snowflake Inc.
Website https://www.snowflake.com
Privacy policy https://www.snowflake.com/privacy-policy
Categories Data

This connector is based on the Snowflake SQL REST API. Snowflake’s Data Cloud is powered by an advanced data platform provided as a self-managed service. Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional offerings. The connector uses the Snowflake REST API V2 to submit synchronous and asynchronous queries and retrieve corresponding results.

Prerequisites

Provide information about any prerequisites that are required to use this connector. For example, an account on your website or a paid service plan.

How to get credentials

Set up Azure AD authentication for Snowflake by following these steps:

  1. In Step 1: Configure the OAuth Resource in Azure AD, follow steps 1-10 and define the scope as SESSION:ROLE-ANY by following these instructions.
  2. In Step 2: Create an OAuth Client in Azure AD, follow steps 1-13.
  3. Navigate to Authentication -> Platform configurations -> Add a platform -> Add "https://global.consent.azure-apim.net/redirect" -> Click Save. Ensure that the redirect URL is set in the Snowflake OAuth Client and not the Snowflake OAuth Resource.
  4. Go to the resource created in Step 1 and go to Expose an API -> Add a client application -> Add your APPLICATION_CLIENT_ID from earlier in step 3 above -> Click Save
  5. Follow Step 3: Collect Azure AD Information for Snowflake entirely.
  6. Copy and paste the text below into your Snowflake worksheet, which is where you execute your queries in Snowflake. Before you execute the query, make sure you make the following replacements so that your query succeeds. A. In Microsoft Azure, go to your Snowflake OAuth Resource app and click on Endpoints. B. To get the AZURE_AD_ISSUER in line 5, copy the link in the Federation metadata document field and open the link in a new tab. Copy the entityID link which should something look like this: https://sts.windows.net/90288a9b-97df-4c6d-b025-95713f21cef9/. Paste it into the query and make sure you have a / before the last quotation mark and that you keep the quotation marks. C. To get the Keys URL in line 6, copy the link in the OpenID Connect metadata document field and open the link in a new tab. Copy the jwks_uri which should look something like this: https://login.microsoftonline.com/90288a9b-97df-4c6d-b025-95713f21cef9/discovery/v2.0/keys. Paste it into the query and make sure you keep the quotation marks.
    D. Replace the Audience List URL in line 7 with Application ID URI from Step 1. Keep the quotation marks.
    E. If your Snowflake account uses the same email address as your Microsoft Azure account, then replace login_name in line 9 with email_address. If not, keep it as is and do not type in your login name. Keep the quotation marks.
    F. Make sure you've set your role as ACCOUNTADMIN. Now you can execute your query.
create security integration connector
       type = external_oauth
       enabled = true
       external_oauth_type = azure
       external_oauth_issuer = '<AZURE_AD_ISSUER>'     
       external_oauth_jws_keys_url = 'https://login.windows.net/common/discovery/keys'
       external_oauth_audience_list = ('https://analysis.usgovcloudapi.net/powerbi/connector/snowflake')
       external_oauth_token_user_mapping_claim = 'upn'
       external_oauth_snowflake_user_mapping_attribute = 'login_name'
       external_oauth_any_role_mode = 'ENABLE';

Get started with your connector

Submit SQL Statement for Execution

Check the Status and Get Results

Cancel the Execution of a Statement

Known issues and limitations

  1. If you get a 500 response when creating a new connection, that is a transient error. Please wait a few minutes and try again.
  2. If you get a 401 response and your Host field in Step 1 follows this format "orgname-accountname," replace the Host field with your "locator" URL.
  3. The connector may time out with large query results.

FAQ

  1. How can the connector be used within Power Apps? Currently, Power Apps does not support dynamic schema. You can still use the connector from Power Apps by calling a flow from the app instead of directly from an app.

Throttling Limits

Name Calls Renewal Period
API calls per connection 100 60 seconds

Actions

Cancel the Execution of a Statement

Cancel the Execution of a Statement

Check the Status and Get Results

Check the Status of the Execution of a Statement and Get the Results

Convert result set rows from array to objects

Convert result set rows from array to objects

Submit SQL Statement for Execution

Submit a SQL Statement for Execution

Cancel the Execution of a Statement

Cancel the Execution of a Statement

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

statement handle
statementHandle True string

statement handle

Request ID
requestId string

Request ID

Returns

Name Path Type Description
code
code string

code

sqlState
sqlState string

sqlState

message
message string

message

statementHandle
statementHandle string

statementHandle

statementStatusUrl
statementStatusUrl string

statementStatusUrl

Check the Status and Get Results

Check the Status of the Execution of a Statement and Get the Results

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

statement handle
statementHandle True string

statement handle

Request ID
requestId string

Request ID

partition
partition integer

partition

Returns

Name Path Type Description
code
code string
sqlState
sqlState string
message
message string
statementHandle
statementHandle string
createdOn
createdOn integer

Timestamp that specifies when the statement execution started. The timestamp is expressed in milliseconds since the epoch

statementStatusUrl
statementStatusUrl string
format
resultSetMetaData.format string

For v2 endpoints the only possible value for this field is jsonv2.

rowType
resultSetMetaData.rowType array of object
name
resultSetMetaData.rowType.name string
type
resultSetMetaData.rowType.type string
nullable
resultSetMetaData.rowType.nullable boolean
partitionInfo
partitionInfo array of object

Partition information

rowCount
partitionInfo.rowCount integer

Number of rows in the partition.

compressedSize
partitionInfo.compressedSize integer

the partition size before the decompression. This may or may not be present in the partitionInfo. Uncompressed size would always be there.

uncompressedSize
partitionInfo.uncompressedSize integer

the partition size after the decompression

nullable
nullable boolean

false if null is replaced with a string 'null' otherwise false

data
data array of array

Result set data.

items
data array of string
numRowsInserted
stats.numRowsInserted integer

Number of rows that were inserted.

numRowsUpdated
stats.numRowsUpdated integer

Number of rows that were updated.

numRowsDeleted
stats.numRowsDeleted integer

Number of rows that were deleted.

numDuplicateRowsUpdated
stats.numDuplicateRowsUpdated integer

Number of duplicate rows that were updated.

Convert result set rows from array to objects

Convert result set rows from array to objects

Parameters

Name Key Required Type Description
rowType
resultSetMetaData string
data
data string

Returns

Name Path Type Description
data
data array of object

Result set data.

items
data object

Submit SQL Statement for Execution

Submit a SQL Statement for Execution

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

Request ID
requestId string

The ID of the Request

Asynchronous
async boolean

Indicates if the query should be executed asynchronously

nullable
nullable boolean

If nullable is false, null values will be replaced by string

statement
statement string

The SQL statement to be executed - batches of statements not yet supported

timeout
timeout integer

Number of seconds before timeout occurs

database
database string

database

schema
schema string

schema

warehouse
warehouse string

warehouse

role
role string

role

timezone
timezone string

Time zone to use when executing the statement.

query tag
query_tag string

Query tag that you want to associate with the SQL statement.

binary output format
binary_output_format string

Output format for binary values.

date output format
date_output_format string

Output format for DATE values.

time output format
time_output_format string

Output format for TIME values.

timestamp output format
timestamp_output_format string

Output format for TIMESTAMP values.

timestamp ltz output format
timestamp_ltz_output_format string

Output format for TIMESTAMP_LTZ values.

timestamp_ntz_output_format
timestamp_ntz_output_format string

Output format for TIMESTAMP_NTZ values.

timestamp tz output format
timestamp_tz_output_format string

Output format for TIMESTAMP_TZ values.

multi statement count
multi_statement_count integer

Number of statements to execute when using multi-statement capability. 0 implies variable number of statements. Negative numbers are not allowed.

Returns

Name Path Type Description
code
code string
sqlState
sqlState string
message
message string
statementHandle
statementHandle string
createdOn
createdOn integer

Timestamp that specifies when the statement execution started. The timestamp is expressed in milliseconds since the epoch

statementStatusUrl
statementStatusUrl string
format
resultSetMetaData.format string

For v2 endpoints the only possible value for this field is jsonv2.

rowType
resultSetMetaData.rowType array of object
name
resultSetMetaData.rowType.name string
type
resultSetMetaData.rowType.type string
nullable
resultSetMetaData.rowType.nullable boolean
partitionInfo
partitionInfo array of object

Partition information

rowCount
partitionInfo.rowCount integer

Number of rows in the partition.

compressedSize
partitionInfo.compressedSize integer

the partition size before the decompression. This may or may not be present in the partitionInfo. Uncompressed size would always be there.

uncompressedSize
partitionInfo.uncompressedSize integer

the partition size after the decompression

nullable
nullable boolean

false if null is replaced with a string 'null' otherwise false

data
data array of array

Result set data.

items
data array of string
numRowsInserted
stats.numRowsInserted integer

Number of rows that were inserted.

numRowsUpdated
stats.numRowsUpdated integer

Number of rows that were updated.

numRowsDeleted
stats.numRowsDeleted integer

Number of rows that were deleted.

numDuplicateRowsUpdated
stats.numDuplicateRowsUpdated integer

Number of duplicate rows that were updated.