Snowflake ​

Snowflake is a relational ANSI SQL data warehouse built for the cloud. Its unique architecture enables faster performance, ease of use, and greater flexibility than traditional data warehouses.

Use cases ​

Integrate Snowflake with your existing apps to build powerful data workflows. You can automate data validation, improve lead tracking, and enhance decision-making with the Snowflake connector. Explore our use cases to discover how you can streamline your Snowflake processes:

• Validate Salesforce contact information with JavaScript and upsert the data to Snowflake.
• Create Salesforce tasks and Snowflake rows from Marketo lead activity.

Connection setup ​

The Snowflake connector supports the following authentication types:

• OAuth 2.0
• Key-pair authentication

OAuth 2.0 authentication ​

You must create a custom integration and generate a client ID and secret in Snowflake to use OAuth 2.0 authentication.

Snowflake setup for OAuth 2.0 authentication ​

Complete the following steps to create a custom integration and retrieve client credentials in Snowflake:

CREATE SECURITY INTEGRATION WORKATO_OAUTH
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = CONFIDENTIAL
  OAUTH_REDIRECT_URI = 'https://www.workato.com/oauth/callback'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE;

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('WORKATO_OAUTH');

Connect to Snowflake with OAuth 2.0 authentication ​

Complete the following steps to set up an OAuth 2.0 authentication connection to Snowflake in Workato:

Key-pair authentication ​

Use key-pair authentication to connect to Snowflake without a password. This method uses a client-generated RSA key pair and provides strong security. You must use a command-line tool to generate the key pair.

Snowflake setup for key-pair authentication ​

Complete the following steps to create a key pair in Snowflake:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

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

ALTER USER <USERNAME> SET RSA_PUBLIC_KEY='<PUBLIC_KEY>';

Connect to Snowflake with key-pair authentication ​

Complete the following steps to set up a key-pair authentication connection to Snowflake in Workato:

Warehouse considerations ​

Snowflake uses per-second billing for all compute (loading, transforming, and querying), with a 60-second minimum each time a warehouse starts. Select warehouse properties based on query frequency, time between queries, number of concurrent active recipes, and query complexity.

Warehouse size ​

An X-Small warehouse is sufficient for most use cases. Larger warehouses complete queries faster at the same credit cost, making them better suited for long or complex queries where timeouts are a concern.

Multi-cluster warehouses ​

Multi-cluster warehouses with auto-scale enabled handle temporary load fluctuations by creating additional warehouses of the same size as needed. Use a multi-cluster warehouse if you expect concurrent jobs or jobs with large queries. Refer to the Snowflake Multi-cluster warehouses guide for more information.

Auto-suspend and auto-resume ​

Warehouses used in the connection must have auto-resume enabled — recipe jobs fail when attempting to run on a suspended warehouse.

You can configure a warehouse to auto-suspend after a period of inactivity based on your business needs. Keep in mind that warehouses maintain a cache of table data that improves query performance, but this cache is cleared on suspension. As a result, initial queries after auto-resume are slower. Use cases with frequent queries and minimal downtime between them benefit from a longer auto-suspend period.

Database timezone ​

Snowflake TIMESTAMP_NTZ data type stores "wallclock" time without timezone information, which can cause issues when integrating with external systems. When sending or reading data from these columns, the values must be converted to a default timezone for other applications to process accurately. To address this, select the timezone your database operates in — this applies only to TIMESTAMP_NTZ columns. Columns with explicit timezone values are unaffected.

• Writing timestamp values to TIMESTAMP_NTZ: Timestamp values are converted to the specified timezone (including DST offset where applicable) before storing only the wallclock value.
• Reading timestamps from TIMESTAMP_NTZ: Timestamp values are assigned the selected timezone and processed as timezone-aware timestamps, with DST offset applied where applicable.

Table and view ​

The Snowflake connector works with all tables and views available to the username used to establish the connection. These are available in drop-down menus in each trigger/action, or you can provide the exact name.

Select a table/view from the drop-down menu

Provide exact table/view name in a text field

Single row and batch of rows ​

The Snowflake connector supports reading and writing data as a single row or in batches. Batch triggers and actions require a batch size between 1 and 100, and return a list datapill (an array of rows) rather than a single row output.

Batch trigger output

Single-row triggers return an output datatree for mapping individual row data, while batch triggers return a list datapill that must be handled differently.

Single row output

Map the Rows datapill into the source list with batch-compatible operations, such as the Salesforce Create objects in bulk action. Refer to Work with lists in Workato for more information.

Using batch trigger output

WHERE condition ​

Use this input field to filter and identify rows to perform an action on. It's used in multiple triggers and actions in the following ways:

• Filter rows to be picked up in triggers
• Filter rows in Select rows action
• Filter rows to be deleted in Delete rows action

Use this clause as a WHERE statement in each request. This should follow basic SQL syntax.

Simple statements ​

String values must be enclosed in single quotes ('') and columns used must exist in the table/view.

A simple WHERE condition to filter rows based on values in a single column looks like this.

email = '[email protected]'

If used in a Select rows action, this WHERE condition returns all rows that have the value '[email protected]' in the email column. Wrap datapills with single quotes in your inputs.

Using datapills wrapped with single quotes in WHERE condition

If your column contains spaces, use the column identifier — double quotes (""). For example, currency code must be enclosed in double quotes to be used as an identifier.

"email address" = '[email protected]'

WHERE condition with column identifier enclosed with double quotes

Complex statements ​

The WHERE condition can also contain subqueries. The following example selects active users from the USERS table.

ID IN(SELECT USER_ID FROM USERS WHERE ACTIVE = TRUE)

When used in a Select rows action, this selects all rows from your main query that are related to rows in the USERS table and are active (ACTIVE = TRUE).

Using subquery in WHERE condition

Use Parameters ​

Use parameters in conjunction with WHERE conditions to add an additional layer of security against SQL injection. To use parameters in your WHERE conditions, you first need to declare bind variables in your input. Bind parameters must be declared in the form :bind_variable where the variable name is preceded with a :. When this is done, declare the parameter in the following section using the exact name you've given.

WHERE condition with bind variable

You can provide any number of bind variables, and each one should have a unique name. Workato distinguishes bind variables from column names and static values by ignoring any : inside single quotes ('), double quotes (") and square brackets ([]).

Special characters ​

When column names are defined with special characters, datapills for such columns aren't mapped correctly to the original column and return null. The most commonly used special characters are whitespace and -. Examples of these definitions are "column name" and "column-name".

The following workarounds are available for these situations.

Workaround 1 ​

Use an appropriate alias in the SQL statement. Datapills are mapped correctly to the alias.

SELECT ID, NAME
"whitespace column" as whitespace_column,
"dashed-column" as dashed_column
FROM CUSTOMERS

Workaround 2 ​

Access the value directly using the row index and column name.

Accessing data via row index and column name