Data Warehouse Vs Business Intelligence
September 18, 2017
Primary Key Vs Surrogate Key
What is the difference between Primary Key and Surrogate Key?
October 13, 2017
Show all

Next day feed processing scenarios

Next day data processing

Next day data processing

If we don’t get the data for that particular day and if the source provider says that they will send the feed for the next day and let’s suppose the source is a flat file, how will we manage the loading of that data?

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.

Failed load


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.

Data is processed, but it’s wrong


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.

The file has not been delivered


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.

Other Situations


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.