Snowflake Channel

Go to the Channel Catalog to learn more about what plans have access to this channel. Sign into Quickbase, and then go to Pipelines > Channels.

Snowflake is a fully managed SaaS (software as a service) that provides a single platform for data warehousing, data lakes, data engineering, data science, data application development and secure sharing and consumption of real-time / shared data.

With this channel you can query data from Snowflake and use it for integration with any other system, for example Quickbase.

How to connect

1. On the My pipelines page, select Create Pipeline.

2. Search for or select a step, and then select it to add it to the pipeline.
   When you add a step to a pipeline, it is added to the canvas of the pipeline designer.

3.  Expand the Connection section of the step, and add the required information.

Connect to Snowflake

1. Expand Snowflake and click Connect to Snowflake .​
    

This dialog displays:

• Your Subdomain—This is the Account Identifier of your Snowflake instance. An account identifier uniquely identifies a Snowflake account within your business entity, as well as throughout the global network of Snowflake supported cloud platforms and cloud regions.

Note: In the case of https://lc32078.europe-west4.gcp.snowflakecomputing.com the subdomain is lc32078.europe-west4.gcp

• Client ID—The Client ID of your OAuth application (see below the details on how to create it and get the Client ID)

• Client Secret—The Client Secret of your OAuth application (see below the details on how to create it and get the Client Secret).

• Role—The role with which you wish to execute your Snowflake steps. This parameter is optional. If not provided, the integration will use the default role of the account you sign in with. This field accepts English values only.

The integration cannot be used by users with ACCOUNTADMIN or SYSADMIN role. Make sure you enter credentials of non-admin users.

To successfully work with the Snowflake channel, the role you use must have the following permissions:

• USAGE on the database

• USAGE and CREATE STAGE on the schema

• SELECT and INSERT on the table

     2. After clicking Connect to Snowflake, you will be prompted for user credentials.

     3. Now sign in to Snowflake using your individual account:

Whenever you connect, you will be redirected to the Snowflake login where you will log in with his individual account using your Snowflake Username and Password.

When you connect you only will see those databases tables and schemas that you have permissions.

How to create an OAuth application

To use the Snowflake channel with Pipelines you should create an OAuth 2.0 integration in Snowflake.

The following is an example of how to create it with the appropriate redirect URL.

Required privilege

To execute the following SQL commands you must use the ACCOUNTADMIN role or a role with the global CREATE INTEGRATION privilege.

CREATE SECURITY INTEGRATION PIPELINES
    TYPE= OAUTH
    ENABLED =TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = CONFIDENTIAL
    OAUTH_REDIRECT_URI ='https://www.pipelines.quickbase.com/authorize'
    OAUTH_ISSUE_REFRESH_TOKENS =TRUE;

If you use the EU instance you must use the correct domain.

After creating the integration you will be able to find your Client ID and Client Secret using the following command

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('PIPELINES');

Refer to the Snowflake documentation for more information on how to create a Custom OAuth integration.

How to reconnect the Snowflake channel

You may need to reconnect your account to a channel. Reasons may be (but not limited to):

• If you need to connect a different account.

• Authorization updates, such as a changed password.

• Editing the access rights that Pipelines has to the channel.

Note: There can be connection issues associated with network IP policies in Snowflake. You may see the Connect to Snowflake running for a long time and if you click Trouble connecting you may see a successful connection message, but the connection still has not been made. See Configure Snowflake OAuth for Custom Clients, Allowing Hostnames, and Activating Network Policies for Individual Users for more information.

How to reconnect

You may need to reconnect your account to a channel. Reasons may be (but not limited to):

• If you need to connect a different account.

• Authorization updates, such as a changed password.

• Editing the access rights that Pipelines has to the channel.

To reconnect:

1. Select a pipeline that already has this channel in it.

2. Open a step that contains this channel.

3. Under account, select Connect (or reconnect) and follow the process above, How to connect.
    

Steps

The steps you can use with Snowflake are: Records.

Create Record

The step could be used in case you need create a record in a table.

Field definitions:

• Warehouse—Dropdown with the warehouses you have access to.

• Database—Dropdown with the databases you have access to.

• Schema —Dropdown with the schemas you have access to.

• Table—Dropdown with the tables you have access to.

• Select Fields—The fields you want to fill for the record that will be created. If you select * all of the fields will be selected.

Max 50 selected columns or params.

Export Records to CSV

Use this step to export data from a single table or a view. (Create a view in Snowflake in case you need to query data using advanced SQL statements, having JOINs for example).

Field definitions:

• Warehouse—Dropdown with the warehouses you have access to.

• Database—Dropdown with the databases you have access to.

• Schema—Dropdown with the schemas you have access to.

• Table—Dropdown with the tables and the views you have access to.

• Select Fields—The fields you want to query from the selected table. Only these fields will be presented in the output. If you select * all of the fields will be selected.

• If you click on More you will see:

Order By field where you will be able to select from dropdown a field by which you want to order your data.

Order field where you will be able to select from dropdown the order of the output. By default (if you do NOT select anything) the order is Ascending.

Limit - The maximum number of items returned by the query

Import Records from CSV

If a resource already exists in a CSV file through a URL or a previous step (through the file transfer URL), you can import records into your Snowflake table in a single step.

Field definitions:

• Warehouse—Dropdown with the warehouses you have access to.

• Database—Dropdown with the databases you have access to.

• Schema—Dropdown with the schemas you have access to.

• Table—Dropdown with the tables and the views you have access to.

• CSV URL—URL to a resource containing CSV-formatted data - can be from an unauthenticated URL, or a file transfer URL from a previous step that handles files.

• Header row—Defines the structure of the CSV you are providing. If you have a header row in your CSV data, you can copy that. The column names must be separated by a comma and have the same order as in the CSV.

Field to map to column * - These options show up when you define your Header Row. It allows you to select the corresponding Snowflake field that you intend to map the column to.

Limits

Single statement execution

Using the channel you will be able to execute a single SQL statement per step.

Limitation of the SQL API

The following commands are not supported:

• The PUT command (in Snowflake SQL)

• The GET command (in Snowflake SQL)

• The CALL command with stored procedures that return a table (stored procedures with the RETURNS TABLE clause)

Export Records to CSV limits

The data export is currently limited to 300MB (this amount refers to the uncompressed data returned by the Snowflake REST API).

Import Records from CSV limits

CSV URL can be from an unauthenticated URL or a file transfer URL from a previous step that handles files. The Import from CSV step supports file transfer handle URLs from the following channels:

• Asana

• Amazon S3

• Autodesk

• Box

• Dropbox

• Jira

• Onedrive

• Procore

• Quickbase

• Salesforce

• Snowflake

• Trello

If you have more than 300,000 records in a payload, we recommend you split those payloads in to 50k or smaller. chunks

Use case examples

The following are some example of using the Snowflake channel.

Export Records to CSV - Example 1

We have a Snowflake table with 1M records. The use case is to add the records from that table to a Quickbase corresponding table.

The first step of the pipeline should be Export Records to CSV in Snowflake

The second step should be “Import with CSV“ in Quickbase

After running the pipeline the records from Snowflake are transferred to the corresponding Quickbase table.

5.3. Export Records to CSV - Example 2

We have an example app in Quickbase having a table that contains contacts information. In Snowflake the contacts are updated daily in a database table Contacts_Demo. The use case is to add the updated in the last 24 hours records from that table to the Quickbase corresponding table.

The first step is to create a view from the contacts table in Snowflake which retrieves only records updated during the last 24 hours.

The second step is to add an Export Records to CSV step from the Snowflake channel and to select the Snowflake view that you created in the previous step.

The third step is to add an Import with CSV step from the Quickbase channel

After running the pipeline, the records from Snowflake that were updated in the last 24 hours are transferred to the corresponding Quickbase table.

Import Records from CSV

We have an example app in Quickbase called Customer Management. Our app has a table with contacts information.

We want to move this data in an empty Snowflake table Contacts.

We create a pipeline with the Quickbase Export Records to CSV step and specify the table want to export the data.

We then add the Import from CSV step from the Snowflake channel and select the Snowflake table where we want to import the data.

After running the pipeline the records from Quickbase are transferred to the corresponding Snowflake table.