Snowflake

The Snowflake destination writes data to Snowflake. You can use the Snowflake destination to write to any accessible Snowflake database, including those hosted on Amazon S3, Microsoft Azure, and private Snowflake installations. This destination is a Technology Preview feature. It is not meant for use in production.

The Snowflake destination stages data to an internal table before writing it to Snowflake.

When you configure the destination, you specify the Snowflake region, database, table, and schema to use. You also configure the user account and password. Make sure that the user account has the required Snowflake privileges.

You specify the write mode to use: overwriting or appending to an existing table. When appending to a table, the destination creates the table if it does not exist.

You specify how to map record fields to table columns and select the behavior for data type mismatches. You can configure the origin to preserve existing capitalization for column names. You can also specify the number of connections to use.

Note: When the pipeline runs on a Databricks cluster, use Databricks runtime 6.1 or above for optimal compatibility.

Required Privileges

To allow the Snowflake destination to write to a Snowflake table, the Snowflake user account specified in the destination must have the required Snowflake privileges.
Tip: To use a custom Snowflake role with the required privileges, the role must be the default role for the user account specified in the destination.

The user account specified in the destination must have the following privileges:

Object Privilege
Schema CREATE TABLE
Table SELECT, INSERT

Configuring a Snowflake Destination

Configure the Snowflake destination to write data to Snowflake. This destination is a Technology Preview feature. It is not meant for use in production.

  1. On the Properties panel, on the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Stage Library Stage library to use to connect to Snowflake:
    • Snowflake cluster-provided libraries - The cluster where the pipeline runs has Snowflake libraries installed, and therefore has all of the necessary libraries to run the pipeline.
    • Snowflake Transformer-provided libraries - Transformer passes the necessary libraries with the pipeline to enable running the pipeline.

      Use when running the pipeline locally or when the cluster where the pipeline runs does not include the Snowflake libraries.

    Note: When using additional Snowflake stages in the pipeline, ensure that they use the same stage library.
  2. On the Connection tab, configure the following properties:
    Connections Property Description
    Snowflake Region Region where the Snowflake warehouse is located. Select one of the following regions:
    • An available Snowflake region.
    • Other - Use to specify a Snowflake region not listed in the property.
    • Custom JDBC URL - Use to specify a Virtual Private Snowflake.
    Custom Snowflake Region Custom Snowflake region to use. Available when using Other as the Snowflake region.
    Custom Snowflake URL Custom JDBC URL to use for a virtual private Snowflake installation.
    Account Snowflake account name.
    User Snowflake user name. The user account must have the required Snowflake privileges.
    Password Snowflake password.
    Warehouse Snowflake warehouse.
    Database Snowflake database.
    Schema Snowflake schema.
  3. On the Table tab, configure the following properties:
    Table Property Description
    Table Name of the Snowflake table to write to.
    Keep Column Case Preserves the case used in field names when writing data to Snowflake.

    When not selected, field names are converted to all caps when writing to Snowflake, unless the names are enclosed in quotation marks.

    Column Mapping Mode Mode to map record fields to Snowflake table columns:
    • By Order - Record fields map to table columns based on order.
    • By Name - Record fields map to table columns based on names. The mapping is not case-sensitive.
    Mismatch Behavior Action to take when field data is the wrong data type for the mapped table column:
    • Error - Generates an error that stops the pipeline.
    • Ignore - Drops the invalid data from the record and inserts a null value in the table column.
    Write Mode Write mode for the stage:
    • Overwrite existing table - If the table exists, the destination drops and recreates the table.
    • Append rows to existing table or create table if none exists - If the table exists, the destination appends data to the table. If the table does not exist, the destination creates the table.
  4. Optionally, on the Advanced tab, configure the following property:
    Advanced Property Description
    Connection Pool Maximum number of connections that the origin uses to read from Snowflake.

    Default is 4.

    Increasing this property can improve performance. However, Snowflake warns that setting this property to an arbitrarily high value can adversely affect performance. The default is the recommended value.