Cloud Portal ONE Desktop ONE DQ&C ONE MDM ONE RDM ONE Runtime Server
User Community Service Desk Downloads

Reference Data Output Step

Writes transformed data to a reference data table with support for upsert (update/insert), append, or replace operations.

This step is automatically added when you create an import transformation from within a reference data table. It’s also available when adding new steps to transformation plan.

Overview

Write data to reference data tables with Upsert, Append, or Replace operations. Unlike the Database Output step (which writes to external databases), this step is for reference data within the platform.

Use this step to:

  • Import data into reference data tables from catalog items.

  • Synchronize reference data with source systems on a schedule.

  • Apply transformations before loading into reference tables.

  • Merge data from multiple sources into a single table.

Step configuration

Configure:

  • Target table: The reference data table where data loads

  • Update method: How to handle existing records (Upsert, Append, Replace)

  • Matching attributes: Which attributes identify existing records (Upsert only)

  • Workflow stage: Destination version stage (Draft or Published)

Target table

When auto-created from a reference data table’s import menu, the target table is pre-selected and locked. When adding manually, choose any table you have permissions to edit.

The target table determines available attributes, record capacity, required permissions, and destination version stage.

Update method

Select how the step should handle incoming data relative to existing records:

Upsert (default)

Updates existing records and inserts new ones based on matching attribute values.

Requires at least one matching attribute.

Behavior: Matching values found → update; no match found → insert as new; null values → insert as new.

Append

Inserts all records as new without checking for matches.

Use when all records are new and you want to add without affecting existing data. No additional configuration needed.

Replace

Deletes all existing records and inserts new records.

Use with caution - all existing data will be removed.

Use when the source is authoritative for the entire table. No additional configuration needed.

Matching attributes

Only visible and required for Upsert.

Matching attributes determine which records correspond between table and incoming data:

  1. Select Select in "Select matching attributes" and choose one or more attributes that uniquely identify records.

Composite key matching

When multiple attributes are selected, all must match (AND logic):

Incoming Record Existing Record Result

Country="USA", Region="CA"

Country="USA", Region="CA"

Match → Update

Country="USA", Region="CA"

Country="USA", Region="NY"

No match → Insert new

Country="USA", Region="CA"

Country="Canada", Region="CA"

No match → Insert new

Choosing matching attributes

Best practices:

Use attributes that uniquely identify records

Product ID, Customer ID, Account Number, SKU, Serial Number, or Code. Avoid descriptive fields (Product Name, Description) that change over time.

Ensure matching attributes exist in both source and target

Same attribute name and compatible data type. Use Transform Data step to rename or convert if needed.

Handle null values appropriately

Null values never match (treated as new records). Use Transform Data step to provide default values for null matching attributes.

Workflow stage (optional)

Controls which version stage receives data:

Draft (default)

Records land in draft stage and must be published before other features can access them.

Published

Records immediately appear in published version and are available to other features.

New records always start in Draft regardless of this setting. This setting affects which version(s) get updated when modifying existing records.

Permissions required

You need:

  • Editor or Owner role on the target reference data table

  • Permissions to create transformation plans

Step lifecycle and data flow

Data flows through the transformation plan:

  1. Input steps provide data

  2. Transformation steps process and modify data

  3. Reference Data Output step evaluates matching logic and updates or inserts records

  4. Records load into specified workflow stage

Common transformation patterns

Filter and import only specific records

  1. Add Filter step after the input.

  2. Configure filter condition (e.g., Status = 'ACTIVE').

  3. Connect to Reference Data Output.

Standardize matching attributes

  1. Add Transform Data step after the input.

  2. Apply transformations: upper($ProductCode), trim($CustomerName), toString($DateField, 'yyyy-MM-dd').

  3. Connect to Reference Data Output.

Enrich data before import

  1. Add Join step after the input.

  2. Join with another catalog item to add information (e.g., join Products with Suppliers).

  3. Connect to Reference Data Output.

Clean and validate data

  1. Add Filter step to remove invalid records.

  2. Add Transform Data step to standardize values.

  3. Add Delete Attributes step to remove temporary columns.

  4. Connect steps in sequence to Reference Data Output.

Comparison with other output steps

Aspect Reference Data Output Database Output Data Transformation CI Output

Target destination

Reference data tables in ONE

External databases via connections

Catalog items in ONE (read-only)

Update logic

Upsert, Append, or Replace based on matching attributes

Insert/Update/Merge with custom merge logic

Publish as new catalog item version

Workflow stages

Draft or Published

Not applicable (database)

Not applicable (catalog item)

Governance

Subject to reference data approval workflows and versioning

Direct write to database (no approval)

Automatic catalog indexing and versioning

Use case

Maintain curated reference data

Export transformed data to external systems

Create/update catalog items for transformation inputs

Advanced configuration

Mapping attributes

When source and target attributes have different names, add a Transform Data step before Reference Data Output to rename columns.

Handling data type conversions

When source attributes don’t match target data types, add a Transform Data step to convert values (e.g., toInteger()/toFloat(), toString(), toDate()).

Bulk updates

To update many records at once:

  1. Use Upsert with matching attributes that uniquely identify records

  2. The transformation updates all matching records in a single run

  3. Results land in Draft state for review before publishing

Troubleshooting

Error: "Target table not found"

Cause: Table deleted or you don’t have access.

Solution: Verify table exists in Reference Data module and you have Editor or Owner role.

Error: "No matching records found"

Not an error. Upsert found no matching records, so all were inserted as new (expected on first import).

Records imported but not visible

Cause: Imported records in Draft state or permission issue.

Solution: Go to table’s Data tab to view Draft records. Publish to make visible in published version.

Duplicate records created

Cause: Matching attributes not configured correctly or values don’t match between source and target.

Solution: Verify matching attributes are selected and match exactly (case-sensitive). Add Transform Data step to standardize matching attribute format.

Performance slow for large imports

Cause: Large data volumes or complex transformations.

Solution: Add Filter step to import only necessary records. Simplify transformation logic. Test with smaller data first.

See also

Was this page useful?