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

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

Prerequisites

• Users must have Microsoft Entra ID for the external authentication.
• Users must have a premium Power Apps license.
• Users must have Snowflake account.

How to get your 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. 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.

Create new security integration in Snowflake

1. In Microsoft Azure, go to your Snowflake OAuth Resource app and click on Endpoints.

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

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

4. Replace the Audience List URL in line 7 with Application ID URI from Step 1. Keep the quotation marks.

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

Update existing security integration in Snowflake

ALTER SECURITY INTEGRATION  <existing integration name>
set external_oauth_audience_list = ('<existing power bi audience list url>', '< Application ID URI from Step 1>')

Using the credentials

While creating the connection in power platform, use the credentials as shown in below snapshot.

1. Client Id: Snowflake OAuth Client ID from registered Client app in Azure
2. Client Secret: Snowflake OAuth Client secret from registered Client app in Azure
3. Resource URL: Application ID URI from registered Resource app in Azure

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

• If there "Instance URL" error please refer Account Identifiers document.
• The connector may time out with large query results.
• Limitations of Snowflake SQL API.

Throttling Limits

Name	Calls	Renewal Period
API calls per connection	100	60 seconds

Actions

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

Cancel the Execution of a Statement

Operation ID: Cancel

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

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.

Parameters

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

Returns

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

Convert result set rows from array to objects

Operation ID: Convert

Convert result set rows from array to objects

Parameters

Name	Key	Required	Type	Description
schema	Schema		string
Data	Data		string

Returns

Name	Path	Type	Description
Data	Data	array of object	Result set data.
Schema	Schema	array of object	Result set schema

Submit SQL Statement for Execution

Operation ID: ExecuteSqlStatement

Submit a SQL Statement for Execution

Parameters

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.

Returns

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

FAQ

1. How to handle responses and partitions?
   Refer Snowflake SQL API documentation here
2. How to handle partition 1 and above?
   Use "Convert result set rows from array to objects" action in the connector to transform the data.

Creating a connection

The connector supports the following authentication types:

Default	Parameters for creating connection.	All regions	Not shareable

Default

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

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

Operation ID:

Cancel

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

Operation ID:

GetResults

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	EMAIL
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

Operation ID:

Convert

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 SQL Statement for Execution

Operation ID:

ExecuteSqlStatement

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