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?

  • I think I would be inclined to implement an approval method for the data in the source databases. Unless the data entered has been approved it will not be processed to the data warehouse. – Shawn Melton Jun 21 '13 at 21:50
  • 1
    Are we talking about a particularly large warehouse? Billions of rows and TBs of data? What is preventing you from periodically running a scratch rebuild, which is often easier than dealing with reconciliation? – Mark Storey-Smith Jun 22 '13 at 1:08
  • @MarkStorey-Smith: I think a rebuild is a possibility; is that how this is typically handled in the field? – 8kb Jun 22 '13 at 3:41
  • 1
    When talking about the business going to the restore option so easily, I'd take the hint that the databases aren't so big, hence also the processed data would be similar. Otherwise they would be more watchful with the process. If that's the case, the lazy me would most likely go for @MarkStorey-Smith's idea, rebuild from scratch and you're done. If the source data is way off, and rebuild from scratch is not an option, then yes, your option is to create ETL packages to sync the data and refresh the cubes and dimensions. – Marian Jun 22 '13 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.