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 |
support@snowflake.com |
Connector Metadata | |
---|---|
Publisher | Snowflake Inc. |
Website | https://www.snowflake.com |
Privacy policy | https://www.snowflake.com/privacy-policy |
Categories | Data |
Snowflake
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 |
support@snowflake.com |
Connector Metadata | |
---|---|
Publisher | Snowflake Inc. |
Website | https://www.snowflake.com |
Privacy policy | https://www.snowflake.com/privacy-policy |
Categories | Data |
Snowflake connector is based on the Snowflake SQL REST API. Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and more flexible than traditional offerings. The connector uses the Snowflake REST API V2 to submit synchronous and asynchronous queries and retrieve corresponding results.
- Users must have Microsoft Entra ID for the external authentication.
- Users must have a premium Power Apps license.
- Users must have Snowflake account.
Set up Azure AD authentication for Snowflake by following these steps:
- 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.
- In Step 2: Create an OAuth Client in Azure AD, follow steps 1-13.
- 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.
- 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
- Follow Step 3: Collect Azure AD Information for Snowflake entirely.
- If you have already established a connection using the Snowflake certified connector, follow the steps in section Update existing security integration in Snowflake. If you are establishing a new connection, follow the steps in section Create new security integration in Snowflake. Make sure you've set your role as ACCOUNTADMIN before executing the query.
In Microsoft Azure, go to your Snowflake OAuth Resource app and click on Endpoints.
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.
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.
Replace the Audience List URL in line 7 with Application ID URI from Step 1. Keep the quotation marks.
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.
CREATE SECURITY INTEGRATION <integration name>
type = external_oauth
enabled = true
external_oauth_type = azure
external_oauth_issuer = '<AZURE_AD_ISSUER>'
external_oauth_jws_keys_url = '<Keys URL from section "How to get your credentials:step 6">'
external_oauth_audience_list = ('<Application ID URI from registered resource app in Azure>')
external_oauth_token_user_mapping_claim = 'upn'
external_oauth_snowflake_user_mapping_attribute = 'login_name'
external_oauth_any_role_mode = 'ENABLE';
ALTER SECURITY INTEGRATION <existing integration name>
set external_oauth_audience_list = ('<existing power bi audience list url>', '< Application ID URI from Step 1>')
While creating the connection in power platform, use the credentials as shown in below snapshot.
- Client Id: Snowflake OAuth Client ID from registered Client app in Azure
- Client Secret: Snowflake OAuth Client secret from registered Client app in Azure
- Resource URL: Application ID URI from registered Resource app in Azure
- Submit SQL Statement for Execution
- Check the Status and Get Results
- Cancel the Execution of a Statement
- If there "Instance URL" error please refer Account Identifiers document.
- The connector may time out with large query results.
- Limitations of Snowflake SQL API.
Name | Calls | Renewal Period |
---|---|---|
API calls per connection | 100 | 60 seconds |
Action | Description |
---|---|
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 |
Operation ID: Cancel
Cancel the Execution of a Statement
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 |
Name | Path | Type | Description |
---|---|---|---|
Code | Code | String | Code |
sqlState | sqlState | String | sqlState |
Message | Message | String | Message |
statementHandle | statementHandle | String | statementHandle |
statementStatusUrl | statementStatusUrl | String | statementStatusUrl |
Operation ID: GetResults
Check the Status of the Execution of a Statement and Get the Results. While retrieving the results, the first partition always returns schema along with the data and further partitions returns only the data which may need to be converted using result set action.
Name | Key | Required | Type | Description |
---|---|---|---|---|
Instance | Instance | True | String | The uri of your Snowflake instance |
statement handle | statementHandle | True | String | statement handle |
Request ID | requestId | String | Request ID | |
Partition | Partition | Integer | Partition |
Name | Path | Type | Description |
---|---|---|---|
Code | Metadata.Code | String | |
SqlState | Metadata.SqlState | String | |
Rows | Metadata.Rows | Integer | Total number of rows |
StatementHandle | Metadata.StatementHandle | String | |
CreatedOn | Metadata.CreatedOn | Integer | Timestamp that specifies when the statement execution started. The timestamp is expressed in milliseconds since the epoch |
StatementStatusUrl | Metadata.StatementStatusUrl | String | |
Format | Metadata.Format | string | For v2 endpoints the only possible value for this field is jsonv2. |
Partitions | Partitions | array of object | Partition information |
RowCount | Partitions.RowCount | integer | Number of rows in the partition. |
CompressedSize | Partitions.CompressedSize | integer | the partition size before the decompression. This may or may not be present in the partitions. Uncompressed size would always be there. |
UncompressedSize | Partitions.UncompressedSize | integer | the partition size after the decompression |
Data | Data | array of array | Result set data. |
Schema | Schema | array of string | Result set schema |
Operation ID: Convert
Convert result set rows from array to objects
Name | Key | Required | Type | Description |
---|---|---|---|---|
schema | Schema | string | ||
Data | Data | string |
Name | Path | Type | Description |
---|---|---|---|
Data | Data | array of object | Result set data. |
Schema | Schema | array of object | Result set schema |
Operation ID: ExecuteSqlStatement
Submit a SQL Statement for Execution
Name | Key | Required | Type | Description |
---|---|---|---|---|
Instance | Instance | True | string | The uri of your Snowflake instance |
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. |
Name | Path | Type | Description |
---|---|---|---|
Code | Metadata.Code | String | |
SqlState | Metadata.SqlState | String | |
StatementHandle | Metadata.StatementHandle | String | |
CreatedOn | Metadata.CreatedOn | Integer | Timestamp that specifies when the statement execution started. The timestamp is expressed in milliseconds since the epoch |
StatementStatusUrl | Metadata.StatementStatusUrl | String | |
Format | Metadata.Format | String | For v2 endpoints the only possible value for this field is jsonv2. |
Partitions | Partitions | array of object | Partition information |
RowCount | Partitions.RowCount | Integer | Number of rows in the partition. |
CompressedSize | Partitions.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 | Partitions.UncompressedSize | Integer | the partition size after the decompression |
Data | Data | array of array | Result set data. |
Schema | Schema | array of string | Results set schema |
- How to handle responses and partitions?
Refer Snowflake SQL API documentation here - How to handle partition 1 and above?
Use "Convert result set rows from array to objects" action in the connector to transform the data.
The connector supports the following authentication types:
Default | Parameters for creating connection. | All regions | Not shareable |
Applicable: All regions
Parameters for creating connection.
This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.
Name | Type | Description | Required |
---|---|---|---|
Client ID | string | Snowflake OAuth Client ID | True |
Client Secret | securestring | Snowflake OAuth Client Secret | True |
Resource URL | string | Snowflake OAuth Audience URL(Resource URL) | True |
Name | Calls | Renewal Period |
---|---|---|
API calls per connection | 100 | 60 seconds |
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
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 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 |
---|---|---|---|
Partitions
|
Partitions | array of object |
Partitions |
RowCount
|
Partitions.RowCount | integer |
RowCount |
UncompressedSize
|
Partitions.UncompressedSize | integer |
UncompressedSize |
CompressedSize
|
Partitions.CompressedSize | integer |
CompressedSize |
Schema
|
Schema | array of object |
Schema |
name
|
Schema.name | string |
name |
database
|
Schema.database | string |
database |
schema
|
Schema.schema | string |
schema |
table
|
Schema.table | string |
table |
precision
|
Schema.precision | integer |
precision |
scale
|
Schema.scale | integer |
scale |
type
|
Schema.type | string |
type |
nullable
|
Schema.nullable | boolean |
nullable |
byteLength
|
Schema.byteLength | integer |
byteLength |
collation
|
Schema.collation | string |
collation |
length
|
Schema.length | integer |
length |
Data
|
Data | array of object |
Data |
ID
|
Data.ID | integer |
ID |
FIRSTNAME
|
Data.FIRSTNAME | string |
FIRSTNAME |
LASTNAME
|
Data.LASTNAME | string |
LASTNAME |
GENDER
|
Data.GENDER | string |
GENDER |
AGE
|
Data.AGE | integer |
AGE |
EMAIL
|
Data.EMAIL | string |
|
PHONE
|
Data.PHONE | string |
PHONE |
EDUCATION
|
Data.EDUCATION | string |
EDUCATION |
Rows
|
Metadata.Rows | integer |
Rows |
Format
|
Metadata.Format | string |
Format |
Code
|
Metadata.Code | string |
Code |
StatementStatusUrl
|
Metadata.StatementStatusUrl | string |
StatementStatusUrl |
RequestId
|
Metadata.RequestId | string |
RequestId |
SqlState
|
Metadata.SqlState | string |
SqlState |
StatementHandle
|
Metadata.StatementHandle | string |
StatementHandle |
CreatedOn
|
Metadata.CreatedOn | string |
CreatedOn |
Convert result set rows from array to objects
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
schema
|
schema | string |
schema |
|
data
|
data | string |
data |
Returns
Name | Path | Type | Description |
---|---|---|---|
data
|
data | array of object |
data |
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 |
---|---|---|---|
Partitions
|
Partitions | array of object |
Partitions |
RowCount
|
Partitions.RowCount | integer |
RowCount |
UncompressedSize
|
Partitions.UncompressedSize | integer |
UncompressedSize |
CompressedSize
|
Partitions.CompressedSize | integer |
CompressedSize |
Schema
|
Schema | array of object |
Schema |
name
|
Schema.name | string |
name |
database
|
Schema.database | string |
database |
schema
|
Schema.schema | string |
schema |
table
|
Schema.table | string |
table |
nullable
|
Schema.nullable | boolean |
nullable |
precision
|
Schema.precision | integer |
precision |
scale
|
Schema.scale | integer |
scale |
byteLength
|
Schema.byteLength | integer |
byteLength |
collation
|
Schema.collation | string |
collation |
length
|
Schema.length | integer |
length |
type
|
Schema.type | string |
type |
Data
|
Data | array of |
Data |
Rows
|
Metadata.Rows | integer |
Rows |
Format
|
Metadata.Format | string |
Format |
Code
|
Metadata.Code | string |
Code |
StatementStatusUrl
|
Metadata.StatementStatusUrl | string |
StatementStatusUrl |
RequestId
|
Metadata.RequestId | string |
RequestId |
SqlState
|
Metadata.SqlState | string |
SqlState |
StatementHandle
|
Metadata.StatementHandle | string |
StatementHandle |
CreatedOn
|
Metadata.CreatedOn | string |
CreatedOn |