Transferring data to Snowflake

This guide explains how to transfer data to Snowflake to store and further process information.

Concept

Snowflake is an Active Destination. After you set Snowflake as the Destination of a Datastream, data is transferred to Snowflake each time data is fetched for the Datastream. For more information, see Destination types.

You can assign multiple Destinations to a Datastream. For more information on possible limitations, see Assigning multiple Destinations to a Datastream.

Prerequisites

Before you complete the procedure in this guide, perform all of the following actions:

  • To set up an Authorization to Snowflake using OAuth2 integration:

    • Ensure the account you use to connect to Snowflake has a SYSADMIN or higher system-defined role, or a custom role with the same or higher privileges. For more information on Snowflake roles, see the Snowflake documentation.

    • Create Snowflake security integration to get the Client id and Client secret values with the following expression:

      CREATE SECURITY INTEGRATION YOUR_INTEGRATION_NAME

      TYPE = OAUTH

      ENABLED = TRUE

      OAUTH_CLIENT = CUSTOM

      OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'

      OAUTH_REDIRECT_URI = 'https://oap.datatap.io/oauth2/callback/'

      OAUTH_ISSUE_REFRESH_TOKENS = TRUE

      BLOCKED_ROLES_LIST = ('SYSADMIN')

      ;

      For more information on using OAuth2 with Snowflake, see the Snowflake documentation.

  • To create tables in the Snowflake Destination, ensure the account you use to connect to Snowflake has the role USAGE or a role with the same or higher privileges.

Procedure

To transfer data from a Datastream to Snowflake, follow these steps:

  1. Add Snowflake as a Destination to the Workspace which contains the Datastream or to one of its parent Workspaces.

  2. Assign the Snowflake Destination to the Datastream.

  3. Configure transfer settings.

Adding Snowflake as a Destination

To add Snowflake as a Destination to a Workspace, follow these steps:

  1. Select the Workspace you work with in Adverity and then, in the platform navigation menu, click Destinations.

  2. Click + Add Destination.

  3. Click Snowflake.

  1. Click Setup a new Authorization.

  2. Click Next.

  1. Select one of the following options:

    • To connect to Snowflake with your username and password, click Snowflake.

    • To connect to Snowflake with OAuth2 authentication, click Snowflake (OAuth2).

  1. In the Authorization page, fill in the following fields:

    Client id

    The client ID of the Snowflake account. For more information on finding the client ID, see the Snowflake documentation.

    You see this field if you connect to Snowflake with OAuth2 authentication.

    Client secret

    The client secret of the Snowflake account. For more information on finding the client secret, see the Snowflake documentation.

    You see this field if you connect to Snowflake with OAuth2 authentication.

    Hostname

    The server name of the Destination database. Use the following format for the hostname:

    {account_name}.{region_id}.snowflakecomputing.com

    If your region is US-WEST, region_id is not required:

    {account_name}.snowflakecomputing.com

    Username

    The username of the Snowflake account.

    You see this field if you connect to Snowflake with your username and password or Key Pair authentication.

    Password

    The password of the Snowflake account.

    You see this field if you connect to Snowflake with your username and password.

    Private Key

    The Private Key associated with one of the public keys assigned to the Snowflake account. The Private Key must be PEM encoded and include boundaries, for example: (-----BEGIN[Private Key]-----END).

    You see this field if you connect to Snowflake with Key Pair authentication.

    Key password

    The password to decrypt the Private Key. Leave empty if the Private Key is not encrypted.

    You see this field if you connect to Snowflake with Key Pair authentication.

  2. Click Authorize.

  3. In the Configuration page, fill in the following fields:

    Name

    (Optional) Rename the Destination.

    Warehouse

    Specify the name of the warehouse where to transfer the data.

    Database

    Specify the name of the database where to transfer the data.

    For more information on advanced configuration settings, see Snowflake Destination reference.

  1. Click Create.

Assigning Snowflake as a Destination

To assign the Snowflake Destination to a Datastream, follow these steps:

  1. Select the Workspace you work with in Adverity and then, in the platform navigation menu, click Datastreams.

  1. Select the chosen Datastream.

  1. In the Destinations section, click + Add Destination.

  2. Click Assign Existing Destinations.

  1. Select the Snowflake checkbox in the list.

  2. Click Save.

Configuring transfer settings

To configure transfer settings, follow these steps:

  1. Select the Workspace you work with in Adverity and then, in the platform navigation menu, click Datastreams.

  1. Select the chosen Datastream.

  1. In the Destinations section, find the Snowflake Destination in the list, and click on the right.

  2. Click Destination Settings.

  1. Fill in the following fields:

    Table name

    Specify the target table in the Destination where to transfer data from the Datastream. The name can contain alphanumeric characters and underscores. For example, target_table. To specify a schema, use the syntax schemaName.tableName.

    By default, Adverity saves data from each Datastream in a different table named {datastream_type}_{datastream_id} (for example, mailgun_83).

    You can specify the same target table for several Datastreams. If a column is shared between Datastreams, Adverity performs a full outer join and concatenates values. If a column is not shared between Datastreams, Adverity writes null values in the relevant cells.

    Use placeholders to create unique, dynamic table names in the Destination. Use the following placeholders:

    Placeholder

    Description

    {datastream_id}

    The Datastream ID.

    {datastream_type}

    The Datastream Type.

    {extract_id}

    The Data Extract ID.

    {meta[*]}

    Replace * with a metadata placeholder to use metadata in the table name. For example, {meta[datastream_URI]} uses the Datastream URI as the table name. For more information on metadata and placeholders, see Using placeholders.

    {name}

    The automatically generated filename of the Data Extract.

    {scheduled_day}

    The day when the data fetch was scheduled to run.

    {scheduled_month}

    The month when the data fetch was scheduled to run.

    {scheduled_year}

    The year when the data fetch was scheduled to run.

  2. Click Save.