Snowflake

You can create the following types of Snowflake connections:

Snowflake Connection

Available when using an authoring Data Collector version 4.0.0 or later.

To create a Snowflake connection, the appropriate Snowflake stage library must be installed on the selected authoring Data Collector:
  • Data Collector 5.4.0 or later - Requires the Snowflake stage library, streamsets-datacollector-sdc-snowflake-lib.
  • Data Collector 4.0.0 to 5.3.x - Requires version 1.7.x or later of the Snowflake Enterprise stage library, streamsets-datacollector-snowflake-lib.

For a description of the Snowflake connection properties, see Snowflake Connection Properties.

After you create a Snowflake connection, you can use the connection in the following stages or pipelines:
Engine Stage or Pipeline

Data Collector 5.6.0 or later

  • Snowflake Bulk origin

Data Collector 4.0.0 or later

  • Snowflake destination
  • Snowflake File Uploader destination
  • Snowflake executor

Transformer 4.0.0 or later

  • Snowflake origin
  • Snowflake Lookup processor
  • Snowflake destination

Transformer for Snowflake 5.0.0 or later

  • Transformer for Snowflake pipeline

For information about features added to the connection with different engine releases, see the connection requirements for the engine.

Snowflake Roles and Privileges

When you configure Transformer for Snowflake pipelines or Snowflake stages in Data Collector or Transformer pipelines, the associated Snowflake role must include all of the privileges required to perform the necessary Snowflake tasks.

For example, in Transformer for Snowflake pipelines, the specified role determines the Snowflake objects that can be used and the tasks that can be performed, such as writing to a table or using an email notification integration.

Snowflake stages in Data Collector or Transformer pipelines generally require that the associated role include specific privileges. For more information, see the following documentation:

Using Custom Roles

When you create a custom role to include the privileges required by a Transformer for Snowflake pipeline, or Snowflake stage in a Data Collector or Transformer pipeline, you can associate the role with the connection in the following ways:
Assign the custom role as the default role (Data Collector/Transformer only)
When using Snowflake stages in Data Collector or Transformer pipelines, in your Snowflake account, you can assign the custom role as the default role for the Snowflake user account specified in the Snowflake connection.
A Snowflake user account is associated with a single default role.
Configure the connection to use a specified role
Specify the name of the custom role in the Role property of the Snowflake connection.
Transformer for Snowflake pipelines require explicitly specifying a role to use. If you do not specify a role in the connection, you must specify a role in the pipeline. When both role properties are defined, Transformer for Snowflake uses the role defined in the pipeline.
For Snowflake stages in Data Collector or Transformer pipelines, if you do not specify a role in the connection, the connection uses the default role for the Snowflake user account defined in the connection.
When a Snowflake stage uses a connection, you cannot configure the stage to override the role or user account associated with the connection.

Snowflake Connection Properties

When creating a Snowflake connection, configure the following properties on the Snowflake Connection Info tab:

Note: Snowflake JDBC driver versions 3.13.25 or higher convert underscores to hyphens, by default. When needed, you can bypass this behavior by setting the allowUnderscoresInHost driver property to true. For more information and alternate solutions, see this Snowflake community article.
Snowflake Connection Info Property Description
Custom Snowflake URL Custom JDBC URL to use when using a virtual private Snowflake installation.
Include Organization Enables specifying the Snowflake organization.

Available with Snowflake Enterprise stage library 1.12.0 or later.

Organization Snowflake organization.

Available with Snowflake Enterprise stage library 1.12.0 or later.

Snowflake Region Region where the Snowflake warehouse is located. Select one of the following:
  • An available Snowflake region.
  • Other - Enables specifying a Snowflake region not listed in the property.
  • Custom JDBC URL - Enables specifying a virtual private Snowflake installation.

Available when Include Organization is disabled.

Custom Snowflake Region Custom Snowflake region. Available when using Other as the Snowflake region.
Account Snowflake account name.
Authentication Method
Authentication method to connect to Snowflake:
  • User Credentials
  • Key Pair Path - Use to specify the location of a private key file.
  • Key Pair Content - Use to provide the contents of a private key file.
  • OAuth
  • None
Note: OAuth authentication is not supported by Transformer for Snowflake. Do not use OAuth authentication in connections to be used with Transformer for Snowflake pipelines.

This property is available with authoring Data Collector 5.6.0 or later.

User Snowflake user name.

The user account or the custom role that overrides the default role for this user account must have the required Snowflake privileges.

The required privileges depend on where you use the connection. For details, see Snowflake Roles and Privileges.

Password Snowflake password.

Required when using User Credentials authentication.

Private Key Path Path to the private key file.

Required when using Key Pair Path authentication.

Private Key Content Contents of the private key file.

Required when using Key Pair Content authentication.

Private Key Password Optional password for the specified private key file or content. Use for encrypted private keys.

Available when using Key Pair Path or Key Pair Content authentication.

OAuth Token OAuth token to use. You can use a Snowflake security integration access token, refresh token, or code grant.

Required when using OAuth authentication.

OAuth Client ID Client ID for Snowflake security integration access tokens.

Available when using OAuth authentication.

OAuth Client Secret Client secret for Snowflake security integration access tokens.

Available when using OAuth authentication.

OAuth Redirect URI Redirect URI for Snowflake security integration access tokens.

Available when using OAuth authentication.

Role For connections used with Transformer for Snowflake, this property defines the role used with the pipeline. You can override this role in pipeline properties as needed.

For connections used in Snowflake stages in Data Collector or Transformer pipelines, this role overrides the default role for the user account specified in the connection. You cannot override this role in Snowflake stages.

Note: The custom role must have the required Snowflake privileges, which are dependent on the stage where you use the connection. For details, see Snowflake Roles and Privileges.
Warehouse Optional default Snowflake warehouse to use.

You can override this property in the pipeline or stage where you use the connection.

Available with authoring Data Collector 5.10.0 or later.

Database Optional default Snowflake database to use.

You can override this property in the pipeline or stage where you use the connection.

Available with authoring Data Collector 5.10.0 or later.

Schema Optional default schema to use.

You can override this property in the pipeline or stage where you use the connection.

Available with authoring Data Collector 5.10.0 or later.

Use Private Link Snowflake URL Enables using a private link URL.

You can specify the URL in the Custom JDBC URL property above. Or, you can define the appropriate values for the Account property and either the Snowflake Region or Organization properties.

Available with authoring Data Collector 5.6.0 or later.

Connection Properties Additional Snowflake connection properties to use.

To add properties, click Add and define the property name and value. Use the property names and values as expected by Snowflake.

Available with Snowflake Enterprise stage library 1.11.0 or later.

Snowpipe Connection

Available when using an authoring Data Collector version 4.0.0 or later.

To create a Snowpipe connection, the appropriate Snowflake stage library must be installed on the selected authoring Data Collector:
  • Data Collector 5.4.0 or later - Requires the Snowflake stage library, streamsets-datacollector-sdc-snowflake-lib.
  • Data Collector 4.0.0 to 5.3.x - Requires version 1.7.x or later of the Snowflake Enterprise stage library, streamsets-datacollector-snowflake-lib.

For a description of the Snowpipe connection properties, see Snowpipe Connection Properties.

After you create a Snowpipe connection, you can use the connection in the following stage:
Engine Stage
Data Collector 4.0.0 or later Snowflake destination

Snowpipe Connection Properties

Define a Snowpipe connection when configuring the Data Collector Snowflake destination to use the Snowpipe load method. Before creating a Snowpipe connection, make sure to complete the Snowpipe prerequisite tasks.

When creating a Snowpipe connection, configure the following properties on the Snowpipe tab:

Snowpipe Property Description
Private Key PEM Private key PEM. Generated in Snowflake as part of the Snowpipe prerequisite tasks.
Tip: To secure sensitive information, you can use credential stores or runtime resources.
Private Key Password Private key password. Generated in Snowflake as part of the Snowpipe prerequisite tasks.
Public Key PEM Public key PEM. Generated in Snowflake as part of the Snowpipe prerequisite tasks.
Use Custom Snowpipe Endpoint Enables using a custom Snowpipe endpoint.
Use TLS Enables the use of TLS to connect to the custom Snowpipe endpoint.
Custom Snowpipe Host Host name for the custom Snowpipe endpoint.
Custom Snowpipe Port Port number for the custom Snowpipe endpoint.