How to link a Data Prep process directly to a reference data table, so cleansed and transformed data flows into Duco without a manual upload step.
Overview
Reference data tables are often fed from an upstream system that sends more data than you need, in a shape that doesn’t match your table. A securities master feed, for example, may arrive with dozens of columns and a mix of golden-source and non-golden-source records.
You can now set a reference data table’s File format to Data Prep and link it to a Data Prep process. Every time that process creates a snapshot, its output is written straight into the reference data table — filtered, mapped and typed exactly as your Data Prep configuration defines. The reference data table is then available to enrich reconciliations downstream, for example enriching trade-level ISINs with a security name.
This article covers:
- Preparing a Data Prep process whose output matches the columns you want in the table
- Linking that process to a reference data table
- Choosing between replace and append behaviour
- Submitting data, creating a snapshot, and verifying the result
- What changes about a reference data table once it is managed by Data Prep
Before you start
You need:
- A reference data table you can edit. It can be a brand-new table — you do not need to configure its columns first.
- A Data Prep process with at least one data set that produces the output you want to land in the table.
- Permission to submit data to the Data Prep process and to create snapshots.
The Data Prep process should already be configured to produce the exact set of fields you want in the reference data table. In the example used throughout this article, the Securities Master Data Prep (SMDPREP) process applies two transformations to an upstream securities master feed.
First, a filter rule removes records that are not from the golden source, so only trusted records reach the output.
Second, a Map and transform step narrows a wide upstream file down to the 11 fields that matter for the reference data table: RECORDVALIDTO, ISIN, CUSIP, SEDOL, FIGI, TICKER, BBGTICKER, RIC, SECURITYNAME, ASSETCLASS and EXCHANGEMIC.
Note: Whatever the Map and transform step outputs becomes the column structure of the linked reference data table. It is worth finalising the output fields before you link the process.
Step 1 — Link the Data Prep process to the reference data table
The link is configured on the reference data table, not in Data Prep.
- Open the reference data table and go to Settings > General.
Scroll to the File format section.
A table that has not yet been configured shows the Setup guide banner. You can ignore the prompt to upload a sample or add columns manually — linking a Data Prep process configures the columns for you
The Securities Master Data table before configuration. No columns have been defined yet. Open the File format dropdown and select Data Prep.
Data Prep now appears in the File format dropdown, alongside Delimited file (CSV, etc) and Excel file (*.xlsx). A Data Prep process field appears below.
Selecting Data Prep reveals the Data Prep process selector. - Click Search for a Data Prep process and start typing the process name.
Select the process that prepares your data.
The selector searches across the Data Prep processes available to you.
Step 2 — Choose replace or append behaviour
Below the Data Prep process field is a toggle: Append uploaded data to the table rather than replacing the contents of the table.
- Toggle off (default) — replace. Each snapshot clears the table and writes the snapshot contents. Use this when the upstream feed delivers a complete set of records every time, as with a daily full securities master file.
- Toggle on — append. Each snapshot adds its rows to whatever is already in the table. Use this when the upstream feed delivers deltas or incremental batches that need to accumulate
Important: In replace mode, each snapshot discards the existing contents of the table before writing the new rows. If the upstream feed sends a partial file, the table will only contain that partial set until the next snapshot. Use append mode for incremental feeds.
Settings save automatically as you change them.
Step 3 — Check the synchronised columns
Go to Columns in the settings menu. The columns from the Data Prep process output have been created for you, with names and data types carried across.
The header text confirms the arrangement: columns are managed by the linked Data Prep process.
Tip: Columns can still be removed from this screen if you don’t want a particular Data Prep output field in the table. Names and data types, however, follow the Data Prep process.
Click Exit settings to return to the table. Until the process produces a snapshot, the table is empty.
Submitting data
If your Data Prep process already runs on a schedule, you can skip this section — the next scheduled run will feed the table. To populate the table immediately, submit data manually.
- Open the Data Prep process and click Submit data.
- Click Upload files and choose your file.
- Select the target Input for the file.
- Click Submit data.
Track progress on the Submissions page. A notification confirms when the file has been processed.
Verifying the transformation
Before creating a snapshot, check that the process did what you expected.
Open Results for the data set to see the transformed records.
59 records passed the filter and were mapped to the 11 output fields. Open Filtered results to see the records your filter rules excluded.
Two records were excluded by the GOLDENSOURCE filter rule and will not reach the reference data table.
Note: At this point the data exists in Data Prep only. Nothing reaches the reference data table until a snapshot is created.
Creating a snapshot
A snapshot is what pushes the Data Prep output into the linked reference data table. You can create one manually, or configure Snapshot triggers in the Data Prep process settings to create snapshots automatically — the usual choice for a process that runs on a schedule.
To create one manually, click Create snapshot.
A confirmation appears once the snapshot exists.
Verifying the result
Open the reference data table. The snapshot contents appear, which may take a moment.
What changes in a Data Prep managed table
A reference data table fed by Data Prep behaves differently from one you maintain by hand or by file upload. Row-level editing is disabled, because the linked process is the source of truth.
- Add row is unavailable.
- Rows cannot be deleted manually. Hovering over the delete control shows the tooltip Rows are managed by a linked Data Prep process.
To change the contents of the table, change the Data Prep configuration and create a new snapshot.
Everything else about the table works as normal. You can open Row Details for any record:
You can also filter the data and configure which columns are visible in your view.
Troubleshooting / FAQ
The reference data table is still empty after my process ran. Why?
A Data Prep run does not populate the table on its own — a snapshot does. Check whether a snapshot was created for the run, and configure Snapshot triggers in the Data Prep process if you want this to happen automatically.
Can I edit or delete individual rows in the table?
No. Rows in a Data Prep managed table are controlled by the linked process. Amend the data set, filter rules or Map and transform step in Data Prep, then create a new snapshot.
How do I add or rename a column?
Change the output fields in the Map and transform step of the Data Prep process. The linked table’s columns follow that output. Individual columns can be removed from the table’s Columns settings if you don’t want them, but names and data types come from Data Prep.
Should I use replace or append?
Use replace when each file contains the complete data set, such as a daily full refresh. Use append when files carry increments or deltas that should build up over time.
Can one Data Prep process feed more than one reference data table?
The link is configured from the reference data table, so each table selects its own source process. Check with your Client Success representative before designing a fan-out arrangement across several tables.
Can I still upload a file to the table directly?
No. Setting File format to Data Prep makes the linked process the only route into the table. To go back to manual uploads, change File format back to Delimited file (CSV, etc) or Excel file (*.xlsx).