Our data warehouse is updated only once every 24 hours (at night). The ETL process takes a little over an hour to complete. I am wondering how to increase the frequency of the ETL process - so running it at noon in addition to running it at midnight - without disrupting any connections to the data warehouse.

In my mind I see a way to have TWO databases with the same schema... and so whenever an update occurs, anything needing to read into the data warehouse will be pointed at the NON updating db... but as soon as the ETL finishes something essentially "switches" out the two db's so that now anything trying to read will be reading from the newly updated database.

Is this possible?

BTW -> I HAVE thought about optimizing the ETL and just running it at noon - creating a (hopefully smaller) window of time where the DB is "not available" ... but I'd rather avoid this anyway. I want no disruption at all to the availability of the data.

MS Sql Server 2016

  • Look into table partitioning. But I would be more worried about the disruption to the source system than the data warehouse. Nov 21, 2018 at 2:28
  • Sure, let's pretend there is a secondary db of the source system though that the ETL can use for processing. Nov 21, 2018 at 2:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.