SUMMARY: Database drift blog series

Key points from this blog series:

  • Drift is the rot that forms in your database delivery pipeline when the source control baseline diverges from the state of production. The longer that drift is allowed to develop, the greater the chance that release failure will occur at some point.

  • The most common way that drift arises is when a change is made directly to the production database rather than via the database delivery pipeline (target drift ). For example, when an index is manually created in the database in the middle of the night to put out a production fire.

  • The simplest solution to keeping the baseline in-synch is therefore to simply prohibit direct changes. But a focus on process enforcement alone is not a panacea and could well backfire if your pipeline isn't quite up to the job.

  • Even if the pipeline is able to meet all of your change needs, another type of drift may still arise: source drift . To avoid that type of drift, code must always be written deterministically.

  • Having a successful pipeline therefore requires three things: process adherence, capable tooling and diligent change authorship. But there will always be environmental factors that thwart even the most determined efforts to avoid drift.

  • The data platform may have fundamental limitations, knowledge and expertise may vary between individuals or the migration framework underlying your pipeline may not be capable of handling every type of change.

  • While you work towards addressing these gaps, the occasional instance of drift may be unavoidable.

  • In which case, an adaptive approach to dealing with drift may be more effective: rather than trying to stamp out drift, expect that it will happen and focus instead on keeping the baseline in-synch. This may involve implementing a process for continually refreshing the baseline from production .

  • Doing so will help highlight drift as it occurs, pinpointing where changes are falling between the cracks. This will then allow you to examine the drift in real-time and make decisions about how to address it going forward.

  • As part of that assessment, you may even decide to allow certain low risk changes to bypass the pipeline in future. This shouldn't result in baseline erosion as long as, again, the changes are absorbed back into source control in a timely way.

  • While it might feel unsatisfying to let some changes go outside of the pipeline, it is important to not let perfect be the enemy of good. A lot of value can be gained through automation even when it is limited in scope, and having the flexibility to choose the right tool for the job can provide significant value in of itself.