design for data load failures

[Ishwar]

how to design for data load failures? I have seen a system where if (days) data load has issues, ETL stops all days data to go into the destination tables. Any reading material for the same will help. I am more concerned about database model. I have following questions,

1) With which data I need to check the transformed data?

2) database model for example

3) Stop few data or the entire data load?

[ngalemmo]

This is something particular to an installation. It all depends on the business and what the user expects.

With that said, I have never implemented a process that, when doing database to database load processes, would ever reject data. The only situations would be when loading from flat files, and it was determined the file was corrupt in some manner. In which case the entire file is rejected.

[Ishwar]

In my current work (Retail data warehouse development), after studying underlying data sources I have picked up tables and columns to work on. Data validation will decide whether I have picked up the correct fields and tables from data sources.

What if some of setting in underlying data source changes, (for example addition of new functionality or addition of new promotion which I didn't consider while designing data warehouse)?
At which step this data should be checked?

What if user identifies issue in data? I need to check and reload the data for the data source?

Any thoughts!!!!

[ngalemmo]

If there is a material change in the structure of the source system (new tables, columns etc...) then effecting such changes is a matter of changing the DW model and load processes. That is part of the system lifecycle and would hardly be considered a 'load error'.

If simply adding a new promotion (or any new values to an existing structure) causes problems, then you need to reconsider how you are loading things in the first place. Any load process should gracefully handle new data values... such as adding a new row to the promotion dimension if a new promotion code is encountered.

[Ishwar]

I agree, but does that mean after implementing data warehouse there will not be any data validation?

[Sponsored content]