For reconciliations that are triggered via SFTP upload, sometimes users encounter the following scenario where file submissions for the day are uploaded correctly and on schedule, but the corresponding run contains submissions that seem out-of-sync, mismatched or wrongly paired, as described below:

• file_a_dayD and file_b_dayD are submitted at the same time for both data inputs A and B
• A new run is expected which will make use of both these day D files
• However, when the run is triggered, it picks up apparently older or "stale" data from either side. For example, the newest file from side A, file_a_dayD paired with an older file from side B, file_b_dayD-1 instead (file_b_dayD was not used)
• The user then performs a workaround by uploading the files for day D manually and the run succeeds with the expected results
• The next day, file_a_dayD+1 and file_b_dayD+1 are sent without issues
• A new run is expected which will make use of both these day D+1 files
• However, the reconciliation runs this time with file_a_dayD+1 and file_b_dayD. Side B is again lagging behind by one day or using an old file
• This issue persists day after day
  
  

Why does this happen?

This is usually caused by an earlier file that was not successfully routed, wrongly uploaded or missed. Typically the process has no submission window set up or the file was uploaded outside of the submission window.

There are two key product behaviours to take note of here.

• All SFTP-submitted files are retained until they are used in a run, overwritten by another file, or discarded from the pending file queue.
  • Let's consider the simple case where file B was uploaded on 31-Aug, but file A was not submitted for some reason.
  • File B will not be deleted even though no run was kicked off. The file remains and waits for file A to arrive.
  • If the next day 01-Sep files are submitted and 01-Sep file A happens to arrive first, it pairs with the previous day 31-Aug file B and both are consumed in a run.
  • When 01-Sep file B is received, it is unused as there is no longer a file for it to pair with on the other side. This creates the impression that the newest file B is ignored, but it is actually pending backend and ready to be used in the next run.
  • Note that if 01-Sep file B had arrived first, the issue would not have occurred.
    
    
• Manual runs do not impact SFTP submissions.
  • Manual reruns will not change the current state of files received through SFTP. Once the SFTP files are out-of-sync, any correction that needs to be made can only be done the same way, via SFTP.

The result is shown diagrammatically below, with the curved lines indicating the pair of files that used in the same run on a given day. At the end of any given day, say D+X, even if an automated run was already completed, it was using an old file, so that side will still have a new file that is unused and waiting in the file submission queue, which continues to disrupt the subsequent pairs of uploads. In the following example, the issue started due to the erroneous upload for file B (or missed upload for file A) on day D-1.

Can I check if I have a file pending in the queue for my process?

Users who have the data source administrator permission can access the Submissions page to monitor submissions (as described in Monitoring Submissions). On this page, if under the Actions column it says Received file for <process code> input <input name>, that is a sign that said file has been routed to the process in question successfully and is waiting in the queue, but not used in a run yet.

If you need assistance confirming whether your situation is truly a case of out-of-sync submissions as described above, feel free to reach out to Duco Support for assistance through the channels described here.

How can we fix it?

Below are some options to clear the old file from the pending queue.

1. Overwrite the existing file > when the new pair of files are being transferred, submit the file for the side that is running old data first. This overwrites the old data and removes it from the SFTP queue, so only the new ones remain on both sides. Once you have verified the issue has been corrected in the latest run, you can go back to the original schedule of file transfers.
2. Discard files from pending file queue > alternatively, if controlling the timing of the submissions is not an option, a data source administrator can go to the Submissions Routing and delete the data input for the side that has old data - this will unlink the process temporarily, clearing any files that may be waiting for that process. Once the input is deleted from the source, if there had been a pending file for that input, it will show in the Submissions tab as No action was taken as the file was overwritten; this means that the file has been dissociated with the process and has been marked to be deleted by the system. Then simply re-add the same data input to restore the submissions routing and submit your files as usual.
   
   
   
3. Use the old file in a run > users can SFTP a file only for the side that contains up-to-date data, so that it pairs with the old data on the other side in a new run. Alternatively, without submitting any new files, the same can be achieved using a submission window (the concept and instructions for how to set it up are explained in the article Submission Windows). Create a narrow, ad-hoc submission window using Run with submitted data option instead of Fail the run. A run will be triggered for the remaining old file at the end of the deadline, removing it from the queue. These runs can be marked as failed if required. 

Once the issue is resolved, as a longer-term solution, it is advisable to set up a permanent submission window that is enforced everyday at a certain timing that you choose. It will take effect if no automated run happens within the defined window, ensuring that once the problem happens in future, a run will be created on the same day itself to clear that file, so it will not cause problems for subsequent submissions.