We are facing a challenging situation with our data warehouse source databases.
Frequently these sources databases will be restored from backups. There is a high turnover of data entry persons who use the system and they make many mistakes. So the business will just restore from a backup and start over.
But at this point, the data in the data warehouse will have already been processed and needs to be corrected. There could be thousands of rows of fact data which is no longer valid. Is there an appropriate design pattern to handle this scenario?
For example, would you need a way to rebuild the data warehouse from scratch? Would you attempt to restore a backup of the data warehouse and then build ETLs to synchronize? Would you delete data from your fact tables and then re-insert?