This is a common scenario in the real time data processing.
A permanent solution for this is to design a model with operational metadata content which will run the jobs as many number of time a day as required with the clear distinction of unique run_id for each time the job has run.
Well, that is more of the job of the Data/ETL architect. But from the developer’s point of view, assuming there is no automated process and if you have to take care of this laod, then these are the situations possible.
This is a situation, where the file is arrived, it has started processing the data and it failed with some error.
Connect with the source provider, provide the error details, ask them for the corrected file.
If they, provide you the file, then
Clear all the data which is committed to the data warehouse or the stage layer database and restart the process.
There is also an option to flag these records as not processed, which will be purged at a later point in time based on the data retention rules.
If there is a failure the second time, then you repeat the same steps.
The Data is processed, but the business says that the values are all wrong. As an application developer, you do the data lineage, preform the logical verification to see if the wrong values are generated by the ETL code.
If the values are based on the data which is coming from the source. You will intimate them and ask them to provide the right files.
This usually, takes longer than normal as the source provider has to check what happened and get back to you. Of course, the source provider has to stick to the SLA and provide you with the file to reprocess.
If there is a public holiday or a technical outage due to which the source provider does not provide the file, then you have to reprocess the data in the next day or the later time.
In those cases and if there is no automated design to figure out the dates and process the loads then you have to identify the best window to run this job based on it’s business/technical dependencies with the batch or the real time data processing.
Note: The best practice for public holidays of the source providers is to know and store or taken into consideration when the jobs are being scheduled for processing.
There will be cases, where you might have to do a full load instead of the incremental load (flat file) which is not delivered due to run time data issues or wrong data population.
In those cases, the data which has to be loaded should be identified and made sure that the all the pre dependent jobs are run before setting this data load. This is usually done during the off business hours or during the weekends, when there is no business impact. Of course, all necessary back ups should be taken accordingly before performing any of the steps.
For all situations the data owner of the business owner should be kept in loop and all the necessary approvals need to be taken.