Tutorial: Export published reference data to a database
This tutorial shows how to maintain reference data in ONE and export it to external databases for downstream applications.
Scenario overview
You maintain a reference table of US states in ONE and need to export the published data to a database that feeds multiple applications. This ensures all systems use the same standardized state codes and names.
What you’ll learn
-
How to manage reference data records.
-
How to publish reference data changes.
-
How to create transformations that export reference data.
-
How to update source catalog items with published reference data.
What you’ll need
For this tutorial, prepare:
-
Source catalog item: Any catalog item containing reference-type data (for example, product codes, country lists, or status values).
-
Reference data table: A corresponding reference table where you’ll manage the authoritative version of this data.
-
Database connection: Access to write data back to your source system or data warehouse.
Prerequisites
Before starting this tutorial, ensure you have:
-
Appropriate role on reference data tables:
-
Owner or Editor role to create and modify reference data tables.
-
Approver role to publish changes (if using approval workflows).
-
At minimum Viewer role to access table data.
-
-
Working knowledge of data transformations plans.
-
Sample data or catalog items to work with.
For information about roles and permissions, see User roles. |
Step-by-step instructions
-
Review your source catalog item
-
Go to your source catalog item that contains the reference data.
-
Display the record preview to see the current data structure.
-
Note the existing codes, names, or values that need to be managed as reference data.
-
-
Modify the reference data table
-
Go to Manage Data > Reference Data.
-
Open your reference data table (or create one if it doesn’t exist).
-
Add new records, modify existing ones, or delete outdated entries as needed.
-
Review your changes in the draft state.
-
-
Publish your changes
-
Select the records you want to publish.
-
Select Publish to make the changes available for downstream use.
-
Verify the published status of your records.
-
-
Create a transformation plan
-
Go to Manage data > Transformation plans.
-
Create a new transformation plan.
-
Configure the input to use the published reference table.
-
Add a Database Writer output step.
-
Configure the Database Writer to target the original catalog item.
-
Set the write mode to Replace to overwrite existing data.
-
-
Execute the transformation
-
Validate the transformation plan configuration.
-
Run the transformation plan.
-
Monitor the execution progress.
-
-
Verify the results
-
Return to your source catalog item.
-
Display the record preview again.
-
Confirm that the data now reflects your published reference data changes.
-
Verify that your added, modified, or removed records appear correctly.
-
Expected outcome
The catalog item now contains the exact data from your published reference table, ensuring consistency across all systems that consume this data.
Next steps
After completing this tutorial:
-
Validate data quality: Create Validation Rules.
-
Handle duplicates: Deduplicate Data.
-
Learn best practices: Best Practices.
-
Use published data: Work with Published Reference Data.
-
Troubleshoot issues: Troubleshooting.
Was this page useful?