Large Table Rebuilds and Other Time Consuming Deployment Steps

Minimising the time required to develop a correct solution is a key principle of successful delivery. Development time can be minimised by using the simplest possible deployment specification. The simplest possible deployment specification occurs when the environment is static. If there is no data loading into your 5 billion row table, then you can take your time when you need to rebuild your table with a new distribution key.

Therefore our first choice deployment approach is to ensure the environment will be static when the deployment occurs, and we will want a deployment framework that can enforce this. We described how we achieved that on a busy data warehouse in Fully Automated Deployments to a Running Enterprise Data Warehouse.

However, some database structure changes can take hours to implement. They still need to be fully automated. Arguably the reasons to have full automation are even stronger on long running steps than trivial duration ones, because there is more that can go wrong.

In scenarios where it would not be acceptable to stop all of the data warehouse jobs for the duration of the deployment, we can’t use our first choice deployment approach and we are left with two main options:

  1. Modularise the solution so that there is a way to block only the processing jobs that apply to a particular group of database objects and deploy only to those objects.

  2. Specify the deployment steps so that they can be run while the processing jobs are running.

Option 1 helps reinforce good practices for structuring the environment, particularly to avoid problems when doing development with multiple teams. It’s a good idea to do this to get various benefits. But it doesn’t help when you want to deploy changes to a module that needs to stay active. A very simple solution might have just one process loading into one table, and it might be essential for it to stay active.

Option 2 might not be practical for some cases, but for others it can be sufficient. For example, when needing to rebuild a table to implement a distribution key change, our first choice is the following, with fast steps in green and the slow one in red:

If the above means the database would be paused for too long, we can take the following approach with additional actions shown in blue:

If the deployment framework is able to handle the second approach, and the steps can written appropriately, this approach allows a robust automated deployment of time consuming steps without having to pause the data warehouse for an extended period.