Connectors / Service / Snowflake

Snowflake

Snowflake

The only data warehouse built for the cloud.

Overview

Snowflake makes it easy to amass all your data, enable rapid analytics, and derive data-driven insights for all your business users.

IMPORTANT!: Snowflake has the option of implementing a Network Policy in order to restrict access based on IP addresses.

If you are not using a Network Policy, then your database is publicly accessible.

If you have enabled a Network Policy, then you will need to enable Tray.io to connect to your database by white listing ALL the static IP addresses listed below.

  • 52.40.200.248
  • 52.39.10.61
  • 52.26.59.155

Please also be aware of our other on-prem options.

Authentication

PLEASE NOTE: You can now also authenticate the Snowflake connector using Okta. Please refer to the Snowflake (Okta) documentation for instructions on how to set this up.
IMPORTANT!: Before continuing note that in order to set up your authentication correctly, you will need to authenticate with a user who has been assigned to a role which has been granted the appropriate privileges for both Warehouses, Databases, Schemas and Tables.

When operating in the context of ACCOUNTADMIN within your Snowflake account dashboard, you will need to have run a sequence of commands such as follows:

Please note that the privileges granted are to a warehouse, then to the databases, then their schemas and individual tables.

This must be done to allow access to the tables. The exact table permissions (select, insert etc.) can be set according to what tasks you wish to carry out with the Tray.io connector.

When using the Snowflake connector, the first thing you will need to do is go to your Tray.io account page, and select the relevant workflow. Once in the workflow dashboard itself, search and drag the Snowflake connector from the connectors panel (on the left hand side) onto your workflow.

With the new Snowflake connector step highlighted, in the properties panel on the right, click on 'New Authentication' which is located under the 'Settings' heading.

This will result in a Tray.io authentication pop-up window. The first page will ask you to name your authentication appropriately, and state which type of authentication you wish to make ('Personal' or 'Organisational').

As you can see, the next page asks you for your 'Account', 'Username' 'Password' and 'Region' (though the latter is optional depending on how you fill the authentication in).

There are two ways you could fill in the 'Account' field within the authentication modal.

If the URL for your Snowflake instance is https://ls28623.us-east-1.snowflakecomputing.com then the 'Account' details would be: ls28623.us-east-1. Meaning you can add the region (us-east-1) next to the account name within the account field itself.

The second option is to add ONLY the 'Account' name from the URL aka: ls28623 in the account field, and pass the 'Region' details: us-east-1 into the region field itself below.

Both of the cases are exemplified in the pictures below.

The 'Username' and the 'Password' are the same as the username and password for your Snowflake account.

There are several other authentication settings you can set up, while most are use case specific, be sure to set a default Warehouse for your authentication, as this needs to be picked up automatically when you are running Snowflake operations.

A Warehouse in Snowflake terms is a set of computing resources you have assigned to manage your databases and tables, which can be adjusted depending on the power and capacity that is required.

If you set a default 'Database' and 'Schema' then you can run queries and statements by directly referencing the table themselves:

If you do not set a default 'Database' and 'Schema' then you will need to enter them "fully-qualified", somehting in the form of the following: <db_id>.<schema_id>.<object_id>:

Once you have clicked the 'Create authentication' button, go to back to your authentication field (within the workflow dashboard properties panel from earlier), and select the recently added authentication from the dropdown options now available.

Your connector authentication setup should now be complete.

Notes on using Snowflake

Load data to table

The Load data to Table operation has files as an input field.

What is required here is a list / array of filenames to load.

It is not possible to use this operation to stream files to a Snowflake data warehouse.

Insert rows in table

When using the Insert rows in table operation, it is important to note that, for all String/Varchar values in a table, Snowflake will only accept them enclosed in single quotes ' '. Please see the example below for instructions on doing this correctly using interpolated mode

Available Operations

The examples below show one or two of the available connector operations in use.

Please see the Full Operations Reference at the end of this page for details on all available operations for this connector.

Example usage

TRAY POTENTIAL: Tray.io is extremely flexible. By design there is no fixed way of working with it - you can pull whatever data you need from other services and work with it using our core and helper connectors. This demo which follows shows only one possible way of working with Tray.io and the Snowflake connector. Once you've finished working through this example please see our Introduction to working with data and jsonpaths page and Data Guide for more details.

Below is an example of a way in which you could potentially use the Snowflake connector to insert new records into the Snowflake table and read all the records from said table.

This scenario imagines a situation whereby you have received some customer data via the Webhook trigger and now need to process it into Snowflake. However users may have received this data in any number of other ways such as from the Salesforce or SurveyMonkey connector, through the CSV Editor, and so on.

Upon receiving the records, the workflow checks whether the table in which we want to add these records already exists in the Snowflake database. If it does not exist, then the workflow creates a new table and then adds the received records to that table. Once the records are added, the workflow continues to read the records from the selected Snowflake table.

This example demonstrates the following operations of the Snowflake connector:

  • Custom query: Executes a raw specified SQL query on the chosen warehouse.
  • Insert rows in table: Inserts one or more rows based on your chosen warehouse, database, table schema, and table combination.
  • List table rows: Finds and reads the rows based on your chosen warehouse, database, table schema, and table combination.

The steps will be as follows:

  1. Pull the client records from the source (a webhook trigger in this example) and create the table (if doesn't exist) in the Snowflake database.
  2. Push the records one by one into the Snowflake table using the Loop connector.
  3. Read the records from the Snowflake table.

Your complete workflow should look similar to this:

1. Pull records & create a table

Select the Webhook trigger from the trigger options available, and set the operation to 'When webhook is received, auto-respond with HTTP 200'. Be sure to click the 'Enable' button before continuing. This makes the workflow ready to receive incoming calls.

The records received by the Webhook are in JSON format and is as follows:

[{
"client_id": 82,
"name": "Renaldo Castaignet",
"email": "rcastaignet0@slashdot.org",
"contact_no": "578-493-4455"
},
{
"client_id": 79,
"name": "Arlen Grumbridge",
"email": "agrumbridge1@instagram.com",
"contact_no": "292-491-7708"
},
{
"client_id": 57,
"name": "Veronika Fitter",
"email": "vfitter2@google.com.au",
"contact_no": "457-411-5533"
},
{
"client_id": 68,
"name": "Tirrell Gladbach",
"email": "tgladbach3@discuz.net",
"contact_no": "714-514-0108"
},
{
"client_id": 90,
"name": "Emmalynne Gibbard",
"email": "egibbard4@accuweather.com",
"contact_no": "625-905-3040"
}
]

Remember that the format in which you receive data will vary depending on where it is coming from - pulling records from Salesforce, Webform, etc., will have different results/views.

Next, add the Snowflake connector and set the operation to 'Custom query'. Add the following query in the 'Query' field.

Create table if not exists client_info (client_id int, name varchar, email varchar, contact_no varchar)

The above query will verify if the specified table (in this case, client_info) exists in the Snowflake database. If it exists, then the workflow proceeds. If it doesn't, then it creates the table based on the table information provided in the above SQL query.

2. Add records

Add a Loop connector with operation as 'Loop List' and the 'List' field set to $.steps.trigger.body. You can use the connector-snake to generate this automatically.

JSONPATHS: For more information on what jsonpaths are and how to use jsonpaths with Tray.io, please see our Intro page and Data Guide for more details.
CONNECTOR-SNAKE: The simplest and easiest way to generate your jsonpaths is to use our feature called the Connector-snake. Please see the main page for more details.

Next, add a Snowflake connector inside the Loop connector and set the operation to 'Insert rows in table'.

In the 'Table' field, add the name of the table that you created in the previous step i.e. client_info in this example.

'Table' does not need to be hard-coded. For example, you may have pulled it from a custom data field in your trigger, such as `$.trigger.table` or `$.trigger.snowflake_table`

Click the 'Add to Rows' button available under the 'Rows' field to add a single 'Row'. To this Row, you can add multiple data using the 'Add more than one item to Data' link.

Select the number here. The number represents the number of columns you have in your selected table. The client_info table in this example has four columns, as you can see based on the query used in the first 'Create table if not exists' step. Once done, click the 'Add' button.

Now, you have four 'Column' and 'Value' pairs available under the 'Column entry' section. Add all the 'Column' names one by one in the 'Column' field. As you can see in the image below, we have added the following four column names: client_id, name, email, and contact_no.

For all String/Varchar values in a table, Snowflake will only accept them enclosed in single quotes ' '.

If you are hardcoding string values this is simple.

If you wish to provide the 'Value' Tirrell Gladbach for the 'name' column then you simply put it in single quotes as shown in the image below.

However we are not hardcoding the values here, we are dynamically passing them in as jsonpaths - looping through the webhook's data to insert it one-by-one into the Snowflake table.

To do this with the required ' ', we need to use interpolated mode.

We can use the connector-snake to find the jsonpath for each column from the loop step. Note that by providing the jsonpath the type selector for each 'Value' field is auto-set to 'jsonpath':

Follow the below steps for all the 'Value' fields with column data type String/Varchar. From the create table query used in the first step, you can identify that the columns 'name', 'email', and 'contact_no' are of data type 'string'.

  1. For all the above fileds, change the type selector back to 'String'. This will show the entire jsonpath for the particular 'Value' field. If we consider the jsonpath for the name column it should appear similar to: $.steps.loop-1.value.name.
  2. Now we need to add single quotes to this jsonpath. To do so, enclose the jsonpath in open and close curly brackets, and add single quotes at both ends. The result should look similar to this: '{$.steps.loop-1.value.name}'.
  1. Click anywhere else on the builder to remove the focus from the field. On doing so, the jsonpath should look as shown in the image below.

Make sure to repeat the above steps for all the fields with column's data type as String/Varchar.

3. Read records

Add another Snowflake connector outside the loop as a next step and set the operation as 'List table rows'.

In the 'Table' field, add the table name of the table you created in the first step and added the record to the same in the previous step, i.e., client_info.

This List table rows operation in this step will list all the rows from the selected table. This will ensure that the intended data is inserted into the selected table.

You can always check the ‘Debug’ tab to inspect your logs at each step of the workflow to check it's progress.

The fetched records in the last step should look similar to this:

BEST PRACTICES: Whenever you do decide to create your own workflow, please make sure you take a look at our Managing data best practices guide.