Suppose your ETL worked fine at the night window but the users calls you at 08:30 and tell you that the numbers does not figure.

Well, there are several reasons for this problems: the data came badly from the OPERATIONAL SYSTEM, something happened to you ETL, or the sales for example where real bad.

In order to eliminate the first two you must build control process that decides whether the data are good enough for the users.

There are two controls you should have: business control and flow control.

The first check the sum, avg… in comparison to the last day, last week etc.

The second, make sure that the COUNT(*) is not significaly different from the last time.

In order to maintain this, you should include thresholds that the operational team can change it without entering the SSIS solution.

As a result the data will get into the production DWH only if it passed the two tests.

Good luck.

Ronen