You can create a pipeline that writes back data from an Anaplan Data Orchestrator dataset to a target table in PostgreSQL.

Prerequisites

You need a connection to PostgreSQL to create a pipeline. Make sure you meet the prerequisites in these sections before you create a connection to PostgreSQL and a writeback pipeline.

Create a connection to PostgreSQL

You need your PostgreSQL credentials to connect the PostgreSQL data with Data Orchestrator. See the PostgreSQL documentation for more information.

To create a connection:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Connections on the left-side panel.
  4. Select Create connection.
  5. Select PostgreSQL and then select Next.
    If you can't find the connector, enter a search term in the Find... field.
  6. Enter these details on the Connection details screen, and then 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. Enter your PostgreSQL credentials on the Connection credentials screen, and then select Next:
    • Username: The PostgreSQL user name required to authenticate.
    • Password: The PostgreSQL password required to authenticate.
    • Host: The PostgreSQL host domain. The host is provided by the administrator of your PostgreSQL instance.
    • Port: The PostgreSQL port number. The port number is provided by the administrator of your PostgreSQL instance.
    • Database: The PostgreSQL database name.
    • CA Certificate: The PostgreSQL connector supports TLS/SSL encryption. TLS/SSL encryption enables you to establish a secure, encrypted connection to PostgreSQL instances. It protects data in transit from being intercepted or read by unauthorized parties. The connection is secured using a digital certificate installed on the server. This provides an essential layer of data protection.
  8. After the connection test is complete, select Done.

Note: If you need to edit the connection, you must enter the CA certificate again.

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 dataset (derived dataset) from Data Orchestrator to PostgreSQL.

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 Anaplan from the Connection type dropdown. 
    2. Select Datasets from the Choose connection dropdown.
    3. Enter a new Label to change the source display name in the designer view.
    4. Select Source location > Source, choose a Data Orchestrator dataset, and then select Confirm.
      The dataset is used as the source for your pipeline.
    5. Select Columns, choose which columns from the dataset you want to include, and then select Done.
      You can also optionally choose a cursor column.
  7. Optionally, select the add icon that appears between the Source and Sink nodes.
    You can add steps to your pipeline to process data.
  8. Select the Sink icon, and then complete these steps in the right-side panel:
    1. Select PostgreSQL from the Connection type dropdown.
    2. Select the PostgreSQL connection you created from the Choose connection dropdown.
    3. Enter a new Label to change the sink name that displays in the designer view.
    4. Select Target location > Table, select an PostgreSQL table, and then select Done.
    5. Select Target mapping > Mapping, map the Data Orchestrator source dataset values to the PostgreSQL target values, and then select Done.
    6. Select a Write option for the target table: Append, Full replace, or Upsert.
      This determines how data is written to the target table. See the Write options section below for more details.
  9. Select Publish, and then select Run to execute the data transfer.

Write options

Review this table to determine which write option to select.

Write optionsDescription
Append

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

If you select Append, a staging table in PostgreSQL is required:

  • To automatically create a staging table in PostgreSQL:
    1. Expand the Advanced options section.
    2. Select Create a staging table automatically from the Staging table dropdown.
  • To manually specify an existing staging table in PostgreSQL: 
    1. Expand the Advanced options section.
    2. Select an existing staging table from the Staging table dropdown.

See the Staging table section below for more information.

Note: Append load isn't suitable for target tables with primary keys. 

Full replace

Deletes all existing data in the target table and replaces it with the source data.

If you select Full replace, a staging table in PostgreSQL is optional. A staging table is recommended and selected by default in the Advanced options section: 

  • If you don't want to create a staging table, don't select ‌the Stage data before replacing the target checkbox.
  • If you want to create a staging table: 
    1. Select the Stage data before replacing the target checkbox.
    2. Specify the staging table details:
      • To automatically create a staging table, select Create a staging table automatically from the Staging table dropdown.
      • To manually specify an existing staging table, select an existing staging table from the Staging table dropdown.

See the Staging table section below for more information.

Upsert

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

If you select Upsert, a staging table in PostgreSQL is required:

  1. Select a Primary key.
    The columns you select are used to identify existing rows when using the upsert write option.
  2. Expand the Advanced options section, and specify the staging table details:
    • To automatically create a staging table, select Create a staging table automatically from the Staging table dropdown.
    • To manually specify an existing staging table, select an existing staging table from the Staging table dropdown.

See the Staging table section below for more information.

Staging tables

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

To use a staging table, you have the option to:

  • Automatically let Data Orchestrator create a staging table in PostgreSQL.
  • Manually specify an existing staging table in PostgreSQL.

This table describes how staging tables are used in PostgreSQL for each option.

Staging table optionsResults
Automatically create a staging table in PostgreSQL

After you run the pipeline:

  • Data Orchestrator automatically creates a temporary staging table in PostgreSQL. PostgreSQL then merges the data in the temporary table with the target PostgreSQL table you selected for the mapping. 
  • After the data has been successfully loaded into the target table, Data Orchestrator deletes the temporary staging table.
Manually specify an existing staging table from PostgreSQL

After you run the pipeline:

  • The data is pushed from Data Orchestrator to the PostgreSQL staging table you specified.
  • PostgreSQL automatically merges the Data Orchestrator data in the staging table with the target PostgreSQL table you selected for the mapping.

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

Verify the writeback in PostgreSQL

Once the pipeline is successfully completed, you can log in to your PostgreSQL database 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).