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.
| Item | Notes |
| 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 more information, see Import data from Snowflake. |
| 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 Snowflake | A target table must already exist within your Snowflake database. |
Create a writeback pipeline
To create a writeback pipeline:
- Select Data Orchestrator from the top-left navigation menu.
- Choose a dataspace from the list.
- Select Pipelines from the left-side panel.
- Select New.
- Enter a Name for your pipeline and then select Create.
You are taken to the pipeline designer view. - Select the Source box, and then complete these steps in the right-side panel:
- Select Datasets from the Available sources dropdown.
- Select Source location, choose a Data Orchestrator dataset, and then select Confirm.
The dataset is used as the source for your pipeline. - Enter a new Label if you want to change the source display name in the designer view.
By default, the name of the source you selected displays as the label name.
- Select the Sink box, and then complete these steps in the right-side panel:
- Choose a Snowflake target connection from the Available targets dropdown.
This target is where you want to write the data. - Select Target location, select a Snowflake table, and then select Done.
- Select Target mapping, map the Data Orchestrator source dataset values to the Snowflake target values, and then select Done.
- 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:
- Choose a Snowflake target connection from the Available targets dropdown.
| Write options | Description |
| 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. |
| Replace existing table | Deletes 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. |
- 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. - Select Publish.
- Select Run pipeline 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.
Automatically create staging table
Manually create staging table
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, replace existing table, or upsert).
