You can create a new pipeline that writes back data from an Anaplan Data Orchestrator dataset to Snowflake.

Prerequisites

Before you can create a pipeline, you need the prerequisite items listed in this table.

ItemNotes
Snowflake account You must have active access to a Snowflake account and instance where the target tables reside. 
Configured Snowflake connection 

You must configure a valid Snowflake connection in Data Orchestrator. 

This process requires setting up key-pair authentication using a 2048-bit RSA private key in PKCS#8 format. The private key itself must be unencrypted. You must also include schema details for Snowflake when you create the connection.

For information about how to create a connection with Snowflake, see the Create a connection with Snowflake section below.

Appropriate permissions The Snowflake user role associated with the connection must have the necessary privileges to write data to the target table. This typically includes Snowflake USAGE privilege on the database and schema, and INSERT privilege on the target table.
Target table in SnowflakeA target table must already exist within your Snowflake database.  

Create a connection with Snowflake

Use the Snowflake connector in Data Orchestrator to create a connection.

You need your Snowflake credentials to connect the Snowflake data with Data Orchestrator. View the Snowflake documentation for more information about your credentials.

To create a connection:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Connections from the left-side panel.
  4. Select Create connection.
  5. Select Snowflake and then select Next.
    If you can't find the connector, enter a search term in the Find... field.
  6. On the Connection details screen, enter these details and select Next:
    • Name: Create a name for your connection. The name can contain alphanumeric characters and underscores.
    • Description: Enter a description about your connection.
  7. On the Connection credentials screen, enter your Snowflake credentials and select Next.
    For information about the fields on the Connection credentials screen, see Authentication options for Snowflake connections.
  8. After the connection test is complete, select Done.

Create a writeback pipeline

When you set up the writeback pipeline, you'll use your connection to export either a source dataset or a transformation view from Data Orchestrator to Snowflake.

To create a writeback pipeline:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Pipelines from the left-side panel.
  4. Select Create pipeline.
  5. Enter a Name for your pipeline and then select Create.
    You are taken to the pipeline designer view.
  6. Select the Source icon, and then complete these steps in the right-side panel:
    1. Select the Connection type dropdown and select Anaplan. 
    2. Select the Choose connection dropdown and select Datasets.
    3. Select Source location, choose a Data Orchestrator dataset, and then select Confirm.
      The dataset is used as the source for your pipeline.
    4. Enter a new Label if you want to change the source display name in the designer view.
      By default, the name of the source dataset you selected displays as the label name.
  7. Select the Sink icon, and then complete these steps in the right-side panel:
    1. Select the Connection type dropdown and select Snowflake.
    2. Select the Choose connection dropdown and select the Snowflake connection you created.
    3. Select Target location, select a Snowflake table, and then select Done.
    4. Select Target mapping, map the Data Orchestrator source dataset values to the Snowflake target values, and then select Done.
    5. Select the desired Write option in the right-side panel.
      This determines how data is written to the target table. Your write options are described in this table:
Write optionsDescription
Append

Adds all rows from the source data to the existing rows in the target table.

If you select Append as the write option, determine if you want to configure a staging table. To configure the staging table, see the Staging table section below.

To prevent data mismatch errors with Snowflake, make sure your data in Data Orchestrator and in Snowflake have a schema alignment. This means the tables in Data Orchestrator and in Snowflake must have the same columns and data types.

Full replaceDeletes all existing data in the target table and replaces it with the source data.
Upsert

Updates existing rows and adds new rows based on a specified key.

If you select Upsert as the write option, select Conflict columns, which are used to identify existing rows when you use this write option. 

You also need to determine if you want to configure a staging table. To configure the staging table, see the Staging table section below.

To prevent data mismatch errors with Snowflake, make sure your data in Data Orchestrator and in Snowflake have a schema alignment. This means the tables in Data Orchestrator and in Snowflake must have the same columns and data types.

  1. Enter a new Label if you want to change the sink display name in the designer view.
    By default, the name of the target table you selected displays as the label name.
  2. Select Publish.
  3. Select Run to execute the data transfer.

Staging table preparations

Staging tables are used to compare and process data, before Data Orchestrator loads the data to the final target table in Snowflake.

When you configure the Sink details, Data Orchestrator uses a staging table in Snowflake if you select the Append or Upsert write option. The Specify staging table name checkbox displays after you select the Append or Upsert write option. By default, the checkbox is unselected.

By default, the checkbox is unselected, and Data Orchestrator creates the staging table in Snowflake. If Data Orchestrator doesn't have access to create tables in Snowflake, you must manually create and provide the staging table information in Data Orchestrator.

There are two ways to create staging tables:

  • Let Data Orchestrator automatically create a staging table in Snowflake.
  • Manually create a staging table in Snowflake.

Verify the writeback in Snowflake 

Once the pipeline is successfully completed, you can log in to your Snowflake account to verify the data load. 

Navigate to the target database, schema, and table specified in the pipeline configuration. The data written from the Data Orchestrator dataset is available in the target table, based on the selected write option (append, full replace, or upsert).