Connectors / Service / Snowflake (Okta)

Snowflake offers a cloud-based data storage and analytics service, generally termed "data warehouse-as-a-service". (Use this service to authenticate using Okta) (updated: 1616759860334)

Snowflake (Okta)

The only data warehouse built for the cloud.

Overview

Snowflake (Okta) offers an alternative way of connecting to Snowflake, by allowing you to authenticate with Okta.

Authentication

IMPORTANT!: You will need to have both your Snowflake and Okta account details available,as you will be pulling information from both to complete the authentication set up.

Within the workflow builder, highlight the Snowflake (Okta) connector.

In the Snowflake (Okta) connector properties panel to the right of the builder, click on the Authenticate tab and the 'Add new authentication' button.

snowflake-okta-auth

This will result in a Tray.io authentication pop-up modal. The first page will ask you to name your authentication and select the type of authentication you wish to create ('Personal' or 'Organisational').

The next page asks you for your 'Client ID', 'Client secret', 'Authorization URL', 'Token URL' and 'Role' credentials.

snowflake-okta-popup

In order to get these fields, head to your Okta dashboard. Click on the 'Applications' tab in the navigation bar.

snowflake-okta-auth-step-1

Next navigate to your chosen app from the list of applications. Your 'Client ID' and 'Client secret' credentials can be found here.

If you do not have an application set up, you will need to do this now. You can follow this guide.

PLEASE NOTE: You will also need to create a security integration between Snowflake andOkta. To do so, please follow this guide from Snowflake.

To get both of the URLs, click on the 'Settings' tab inside of your authorization server and click on the link beside 'MetaData URI'

snowflake-okta-auth-step-4

This will open a new page in your browser containing a JSON object.

Search this document (using 'control+F' or 'command+F') for token_endpoint and authorization_endpoint. This will highlight both of the URLs needed.

They should look like this:

authorization_endpoint:"https://your-okta-domain-name.oktapreview.com/oauth2/000000000/v1/authorize"

token_endpoint:"https://your-okta-domain-name.oktapreview.com/oauth2/000000000/v1/token"

To get the 'Role' credential, head to your Snowflake dashboard.

PLEASE NOTE: Snowflake automatically assigns new users with a default role of 'PUBLIC'. Wehave included this default in the Tray.io authentication pop-up modal, as well as the associatedscope.

The role currently assigned to your user appears under your username in the navigation bar.

You can view all of the roles your user has available (including which one is the current default) by clicking on the arrow beside your username and selecting 'Switch Role'.

snowflake-okta-auth-step-5

IMPORTANT!: You will have to assign the associated scope to your role in the authenticationsetup, as it is this scope which will request the role from your Snowflake account. If you areusing 'PUBLIC', you can check the 'Session:role:public' box in the Tray.io authentication pop-upmodal. If not, you can add your scope inside of the 'Extra permission scopes' field. These rolescopes will also have to be added to your authorization server. Please see below.

To add your Snowflake role scopes to your authorization server, navigate back to your Okta dashboard and click on the 'Scopes' tab located inside of your authorization server and click on the 'Add Scope' button.

You can create a new scope for each role from your Snowflake account if you wish.

snowflake-okta-auth-step-6

You will also have to make sure that you have these scopes enabled in your Access Policy.

To check this, navigate to the 'Access Policies' tab and select the rule associated with your authorization server. If you do not have one you will have to create one.

Next, check that you either have 'Any scopes' selected or that you have added these scopes under 'The following scopes' option.

snowflake-okta-auth-step-7

Your scopes should now have been added successfully.

PLEASE NOTE: Scopes are case-sensitive. When entering a new scope in the Tray.ioauthentication pop-up modal, it must be in lower-case (e.g 'session:role:public'). However, itwill be displayed with a capital letter (e.g 'Session:role:public').

Once you have added these fields to your Tray.io authentication popup window, click the 'Create authentication' button.

Go back to your settings authentication field (within the workflow builder properties panel), and select the recently added authentication from the dropdown options now available.

Your connector authentication setup should now be complete.

Please refer to the standard Snowflake documentation for instructions on using operations.

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.

All Operations

Latest version:

1.1

Create database

Create a database.

Create schema

Create a schema.

Create stage

Create a new named internal or external stage to use for loading data from files into Snowflake tables and unloading data from tables into files.

Create table

Create a table.

Create warehouse

Creates a new virtual warehouse in the system. Creating a warehouse includes specifying its size. Initial creation of a warehouse may take some time to provision the servers, unless the warehouse is set to be created initially in a SUSPENDED state.

Custom query

Send a custom SQL query to Snowflake.

Delete database

Removes a database from the system.

Delete files from stage

Removes files that have been staged (i.e. uploaded from a local file system or unloaded from a table)

Delete rows from table

Delete rows from the target table using optional query and additional (non-target) tables.

Delete schema

Delete a schema from the current or specified database.

Delete stage

Removes the specified named internal or external stage from the current/specified schema. The status of the files in the stage depends on the stage type.

Delete table

Delete a table from the current or specified schema.

Delete warehouse

Removes the specified virtual warehouse from the system.

Insert rows in table

Updates a table by inserting one or more rows into the table.

List copy history

This operation can be used to query Snowflake data loading history along various dimensions within the last 7 days.

List databases

Lists the databases for which you have access privileges, including the dropped databases that are still within the Time Travel retention period.

List files in stage

List the files currently in a stage.

List schemas

Lists the schemas for which you have access privileges, including the dropped schemas that are still within the Time Travel retention period.

List stages

Lists the stages for which you have access privileges.

List table columns DDL

List table rows

Lists rows in one or more tables.

List tables

Lists the tables for which you have access privileges, including the dropped tables that are still within the Time Travel retention period.

List warehouses

Lists the warehouses for which you have access privileges.

Load data to table

Load data from staged files to an existing table. The files must already be staged.

Unload data from table

Unload data from a table (or query) into one or more files in a named internal stage, a named external stage or an external stage.

Update database

Modifies the properties for an existing database, including changing the name of the database and changing the Time Travel data retention period (if you are using Snowflake Enterprise Edition or higher).

Update row in table

Updates specified rows in the target table with new values.

Update schema

Modifies the properties for an existing schema, including renaming the schema or swapping it with another schema, and changing the Time Travel data retention period (if you are using Snowflake Enterprise Edition or higher).

Update stage

Modifies the properties for an existing named internal or external stage.

Update table

Modifies the properties, columns, or constraints for an existing table.

Update warehouse

Suspends or resumes a virtual warehouse, or aborts all queries (and other SQL statements) for a warehouse. Can also be used to rename or set/unset the properties for a warehouse.