There are legitimate reasons for doing a complete reload but more often than not I find that it is due to lazy design. It is just easier to reload everything than it is to detect changes. Maybe, if I get some time I will do some posts on common change detection patterns for SSIS.

Back to the design that you describe: I’d worry about the down time and possibility of error resulting in an empty table for end users. In the design described in the blog post the data is fully loaded and verified on the target system before the existing data is dropped. And, finally, the staging table is simply re-named to replace the target table. All of these procedures are very quick and reduce the chance for error. It isn’t perfect, but it is fairly bulletproof.

In your situation, it sounds as though the data is loaded onto one server/database (DW_Staging), verified, and finally moved to the target server/database (DW). Even if the databases co-exist on the same server it is going to take time to move data. And, this moving takes place after you have deleted all of the existing data. So, I would argue that it isn’t as good of a solution.

Anyhow, like I said, the solution is going to depends on the details of your situation. But, this should at least give you something to think about. Thanks for the comment and good luck!

This post was mentioned on Twitter by MrVitaminP: New blog post: “BI Release Management” – http://tr.im/CGXs...