Record Tracking Best Practice Guide
This guide will help you understand the features of and relating to record tracking, their relationship to one another, and how to make business decisions that make sense depending on use case and needs within the product.
What is record tracking in Duco?
Enabling record tracking also allows one to view the match history of a particular record (as defined by some unique user-defined key fields) across runs. This means that Duco will recognise the record of a line item whose match status changes over time by keeping a historic record. Record tracking is needed for Exceptions Workflow, tracking breaks, and keeping investigation history.
Configurations for best results
Configuring record tracking correctly from the start is imperative to its success. One of the most important steps is configuring tracking identifiers, ie. fields that will successfully identify a record uniquely. In a perfect scenario, this would be one clearly unique field, such as a record ID.
In reality, this may require a combination of fields. The optimal fields to choose are any selection that will uniquely identify an item with certainty. Transaction IDs, dates, account numbers, counterparty identifiers, rates, currency, etc, are all examples of fields that could be leveraged here. What is key is that the field, or combinations of fields, contain values that are sufficiently discriminating to distinguish between records. Simply tracking on a Date field, or just the Currency, will not be specific enough for Duco to accurately track records as being the same over time. Those records which have the same values in all the record tracking fields will be considered as duplicates as Duco will not be able to identify those uniquely. If you are familiar with database terminology, you can think of the tracking identifiers as a "composite key" for a data set which has to be unique for each record.
Example of proper tracking keys:
Example of improper tracking keys:
How to configure tracking with roll-ups
Some data sets require roll-up rules to be applied before matching with the other side file. In such cases the process configurators will have a choice to either set the record tracking by the individual line items (roll-up constituents) or the rolled up parent records.
In the majority of use cases, you want to track based on constituents. If you have carry over, each time the data is carried over it gets rolled-up again, so you want to track the components that make up the roll-up. Otherwise, if you track at the top level, you are expecting the final roll-up to be the same, but if there is any variation it will get treated as a new record
A scenario in which you might track by the rolled up item is if you're creating a position from underlying records and you're interested in tracking the position by ISIN.
If you need guidance on whether to track via constituents or the rolled up item, contact your Customer Success Manager or file a support ticket.
Most common configuration mistakes
A common configuration mistake is changing your record tracking keys after they have been initially configured and the reconciliation has gone to production. If the record tracking keys change, it will break the lineage of the data history, as Duco is no longer able to identify that record as the same one. Also, all exception’s investigative work will be lost. That is why it is highly recommended to be mindful when initially setting up the record tracking process, as to avoid needing to change the tracking keys, and thus breaking the tracking history.
Another common configuration mistake when enabling record tracking is that the combination of keys chosen are not able to uniquely identify a record. When this happens, Duco moves these records into a duplicate bucket.
What are duplicates?
Tracking fields are configured to uniquely identify an item in a data set. They're also used to make sure comments, labels, exception states etc get auto-updated when rows re-appear (often via carry-over).
The Duplicates bucket is displayed when a run contains more than one line item that shares the same values across the tracked fields. For example, Side A submits two line items that have identical values for Currency, Trade Date, Notional, and Counterparty. Duco will allow one of the line items to be used by the run for the reconciliation, and any other items with the same values will be put into the duplicates bucket. Which items will be used for the run is determined by the algorithm and can not be manually determined (although duplicates can be manually removed from the bucket post-run).
Something important to be aware of is that duplicate detection is only looking at the tracked fields. This can mean items flagged as duplicates could have differences in fields that are not tracked - which is why correct configuration of the tracking fields is crucially important.
Duplicates may also be captured because of carry-over if the auto close feature is not being utilized. In this scenario, your data submitted may only contain a single line item with particular values in the tracked fields, but a carried-over item from a historic run could share these values, causing a duplicate to be identified.
Duplicates appear as a result of tracking fields that are not able to uniquely identify the item. These may be individual items that need to be reconciled and investigated. Hence, why choosing proper tracking keys is of utmost importance.
If this happens, you can mark these items as not duplicates. They will then be included in the run and become exceptions. They will be carried-over to the next run if they remain unresolved, and will not be treated as duplicates again in subsequent runs if new records matching the same tracking keys are submitted.
However, the best way to avoid duplicates is selecting the right amount and right categories for Duco to begin the tracking process. Any changes to the tracking keys after the initial set up, including reordering the keys or adding or removing keys will break the tracking history.
How does Duco select which item becomes a duplicate?
In order to keep the history of a record between runs, Duco uses values in the record tracking fields to create a memory of that record- called a data signature- which is unique for every record in the run. However, if there are two or more records that have the same values in all the record tracking fields, Duco is not able to create a unique data signature. This does not allow Duco to distinguish the records in the run. Since Duco cannot choose which one of those records has to stay, Duco identifies all of them as duplicates, allowing the users to choose the right record.
Can I turn off duplicates?
In order to solve the issues of duplicates, process builders may add additional record tracking keys to help identify the record uniquely. Alternatively, the record tracking capability can be switched off, however in that case several functions of the exceptions workflow will not be available. Because of the technical tracking aspect, there is no way for Duco to not detect duplicates, but you can turn off the view of duplicates in the UI.
You may hide the duplicates bucket by going to results control and turning off duplicates for both sides. This will remove the duplicates bucket from the UI and generated reports.
Another way to flag duplicates is by having the system administrator create a
“Duplicate” label which an end user can then apply. This way, it is flagged in the system as a problem that can be investigated and resolved.
What is carry over?
With carry over enabled, Duco carries forward any unmatched or partially matched items between reconciliation runs without you having to submit them again. This is important in the case of timing differences.
Consider this scenario: The front office generates a data extract at 6pm everyday, and the back office generates their extract at 7pm. Due to the timing difference, some records will appear in one system and not the other until the following day.
With carryover enabled, Duco will take all of the unmatched records from one day and attempt to match on them again.
To configure carry over, first make sure record tracking is properly configured. While record tracking can work without carry over, carryover cannot work without record tracking. For proper configurations, please refer to the Operational guide. Next, select the items you would like to carry over. You can choose to have either Unmatched or both Unmatched and Partially Matched items to carry over. This allows you to ensure that all breaks have been rectified at source. This is particularly appropriate when you are submitting only additional new items with new runs and are not reloading older data (e.g. for trade reconciliations).
Finally, add a retention period for the records to carry over. You can either select to have items carry over indefinitely or you can set your own specific time limit. If you set a particular time limit, Duco will internally mark a carry over expiry date on any records submitted during a run. When the carry over expires (the run date is after the expiry date of the record), it will no longer be moved to the next run. If you do not set a particular time limit, records will not be marked with an expiry date. In this case, records will only stop being carried over if the user accepts, closes or manually matches the record or the record becomes auto-matched.
How the retention period works
As an example:
First of all, a retention period of 0 means: "don't carry over any record", which is the default behaviour. You can specify a retention period for each data set. The retention period can be expressed in days, hours, or minutes. As an example, if you set a retention period of 5 days, when you execute a run and the run results in an unmatched record, Duco will "hold on" and try to match that record for the next 5 days from the date that it is being calculated from.
What is run with carry over only?
This allows you to fix any exceptions due to incorrect rules and reference data tables. You can then re-run the reconciliation with carry over data only to check that the exceptions have indeed been fixed. You can also use this feature to run the reconciliation with only one file in a case where the opposing side file is missing.
To make use of this option, please select the "Run with carry over only" option from the "Run process" dropdown. This option will only be available if you have carry over switched on and there are items which will be carried over to the next run.
If an item is in the carry over and in the file (a duplicate), the version from the file is used, and the version in the process already is moved to the duplicates bucket. This allows bad data to be updated in a subsequent submission.
Special features related to record tracking
The capabilities and features of record tracking go beyond just looking at historical runs. In reality, record tracking should be combined with carryover or auto-close exceptions in order to provide the best user experience. There are two scenarios in which auto-closure is necessary. The first is to keep an item closed after some manual actions have been applied, and the second is to have continuity in the investigation process.
For example, let’s imagine in a scenario with a loan, the incoming line of data appears every day over the life of the loan. You want to be able to tell that the line item coming in today is the same as yesterday and the day before. This is why you enable record tracking on the build.
Normally, these records match daily and all is well. However, let’s say one of these counterparties changes the name, which results in daily breaks until it is resolved by the counterparty.
In this case, you would not want to investigate these breaks every day. You can close the record, and then you want it to stay closed (i.e.- force close). This can be configured in “auto-close exceptions”. This setting allows scenarios like this to automatically be resolved without the need to investigate daily breaks. Comments and labels will still carry over with auto-close exceptions enabled.
Recently, we have implemented a new feature that allows clients to limit auto-close to the previous run only (at the process level). This will greatly improve process performance and run time speeds.
To configure this setting, first go to exceptions workflow settings
Then, enable auto close and select “limit auto-close to occurrences in the previous run only”. This will help to significantly improve performance and run time in the program. Please note that there should be continuity in the data before enabling this setting. If there is not, there is a possibility the record will be lost.
There is an option in exceptions workflow to use either record tracking or matched fields to determine whether a previous item is the same item (and should be auto-closed or auto-accepted).
Generally, we recommend to use the record tracking fields to provide the best consistency in performance. For more information here, refer to the operational guide below.
Auto-close unmatched items within tolerance
New exceptions may also be automatically closed when unmatched items in a run fall within a tolerance.
Another important thing to keep in mind as per the operational impact is being mindful when using workflow rules to auto close exceptions.
Users should be aware of how many breaks are being auto closed as a result of workflow rules. While a configuration could only have 10 breaks, another 100 could be hidden by auto closure rules.
Apply manual matching control
The manual matching control allows the user to select fields to restrict what trades can be matched.
Manual matching will only be possible if all selected fields are equal. If items have previously been manually closed, then there should not be an acceptance-style rule in place and the item should be investigated again manually.
If the same item occurs again “naturally” (e.g. through the user re-submitting the same file), open items in older runs will be auto-closed and accepted items will lead to automatically resolved items.
By default, exceptions in Duco start at 0.01 days. You can now customise this using the “exception age” feature. To configure this, you must be using calculated results.
For example, you can set a calculated result like so:
After creating the calculated result, run the process again to ensure the calc results take effect. Then, when configuring the settings of exception age, the calculated result then becomes available.
The value of the calculated result field will be when the exception starts to age. In the next run, the ageing will change based on the value of that calculated result.
Recent product updates
In the past, tracking a large number of items would slow down performance, as Duco would search through all of the historical records that existed for that line item.
Now, the number of historical runs that Duco searches for the purpose of tracking will now be configurable with a maximum number. We recommend setting this number to 1.
This will help significantly speed up long run times and lead to better performance. To change this feature, file a support ticket or contact your Customer Success Manager.
Effective use of features
Using the correct combination of the the above discussed features will depend on the use case needed. Keep reading for suggestions on combinations that we recommend.
Record tracking + autoclosure
Record tracking + auto-closure: based on the record tracking fields
This is the recommended setup for the majority of situations.
If Duco recognises a submitted record as the record that was submitted earlier, it will track it as the same record. The exception attributes will be copied over to the latest occurrence and the previous occurrence will be closed.
Depending on the specifics of the submitted data, it is recommended to limit the record tracking and auto-closure look back to the previous run.
Full population Transaction Reconciliations
This is the most common use case where carry over is not required, all the records that require reconciling are coming in every run.
Record tracking and auto-closure are required for ageing and exception investigation continuity. Record tracking limit can be set to the previous run. Auto-closure limits can be set to the previous run as well to ensure proper performance in the product.
Record tracking/ auto-closure based on the match fields
This setup is used ad-hoc, for specific data configuration when auto closing by the record tracking keys does not allow to close the unique exceptions properly. Tracking and auto-closure lookback limits should be set on a case per case basis.
If you hesitate choosing which configuration is correct for your data, reach out to support.
In this case, investigators want the break and age to roll if the position break is the same quantity vs be treated as a new break if the quantity is different.
This set up enables the investigative team to identify new vs. old breaks.
Best practice is to move investigated exceptions to Under Review / Pending and that status will be carried forward as part of the auto-close. If the values in tracking key fields change (e.g. qty) this is considered a new break so requires a new investigation.
Record tracking + carryover + auto-closure
Delta Transaction Reconciliations
Carry-over should be enabled to allow previous breaks to match off with newly submitted records. For best performance it should be set to a limited number of days.
Record tracking is required to switch on the carry-over and also for ageing and exception investigation continuity.
Auto-closure should be switched on to close any previous occurrences of the carried over items as well as have the continuity of the exception investigation.
Record tracking and closure limit should be set to the previous run i.e. N=1 . Carry-over is suggested be set to a defined number of days.
Trade reconciliations (there can be only one occurrence of a transaction with all the attributes including price)
Carry over and record tracking should be enabled. Auto-closure suggested to be set to the record tracking fields.
Both, record tracking and auto-closure lookback are recommended to be set to 1 run i.e. N=1
All in all, this guide is meant to provide you with combinations of features that make sense. If you need help determining which combination is appropriate for your data, file a support ticket.