Database drift: Why it happens & how to detect it (database schema drift)

Ideally, it shouldn’t happen. But, most teams don’t work with ideal database processes.

What is database drift?

Database drift (AKA schema drift or version drift) is a way to describe when the database schema for a database in one environment no longer matches the schema for the same database in a different environment. It could describe schemas for the same database in Test vs. Prod or in Prod vs. source control.

Why does database drift happen?

Database drift happens because hotfixes and patches are often applied to staging and production systems in panicked efforts to restore functionality or address critical system flaws rapidly. Often, these late-night, last-minute changes are not properly documented. Nor are these changes applied to lower environments. They are often at the heart of show-stopping surprises during later production deployments. 

Database drift can also occur due to differences in the database schema evolution across various development teams. For instance, when multiple teams are working on different features or services that interact with the same database, they might independently modify the database schema according to their immediate requirements. These modifications, if not properly synchronized or communicated across teams, lead to inconsistencies in the database structure when deploying to production. 

Additionally, the lack of a unified approach to database version control or the absence of a robust database change management system exacerbates the problem, allowing drift to occur undetected until it impacts system functionality or data integrity during critical operations.

What’s at risk?

In the worst case, database drift can result in data loss. In the best case, it is a delay and time lost reworking a failed execution.

Beyond the immediate risks of data loss and release delays due to failed deployments, database drift can compromise data integrity. Inconsistent data models across environments mean that data may not be handled consistently, leading to errors in development, testing, data processing, and reporting. This can severely impact decision-making processes that rely on accurate data.

Database drift can also introduce security vulnerabilities. Inconsistencies in database schemas, especially those involving configurations and access controls that differ from one environment to another, can open up gaps in security protections, privileges, and more. These gaps could potentially be exploited by malicious entities, leading to unauthorized access or data breaches. They can also put sensitive data at risk and lead the organization to break database compliance policies. 

Additionally, database drift can cause a significant drain on resources. Teams may need to spend excessive time identifying and resolving discrepancies between environments and change requests instead of focusing on new features or critical system improvements. This not only slows down project timelines but also inflates costs due to increased hours spent troubleshooting and rectifying issues.

When an organizations databases – relied upon for their enduring state and accessibility – experience drift, it can also erode trust in the reliability of the pipeline and IT infrastructure, both from internal stakeholders and external customers. When database environments are not aligned, it raises concerns about the organization’s ability to manage its data landscape effectively, which can deter potential business opportunities and partnerships.

Some fixes are as trivial as adding a missing index or making a small change to a database object. Others require more comprehensive revisions that may involve restructuring existing database schemas or integrating disparate data systems to ensure alignment across all environments. These extensive changes can be critical for maintaining system performance and functionality, particularly as applications scale and evolve.

Schema drift, the unsanctioned or unintended changes to a database schema, can significantly disrupt operational workflows across different teams within an organization. Understanding the impact of schema drift on key roles in the organization can help in devising more effective strategies to manage and mitigate these changes.

Application Developers: Functionality, availability, and UX

Application developers rely on a consistent database schema to ensure that their applications perform optimally, remain available, and deliver a seamless user experience. Database drift can disrupt application functionality and availability, leading to errors and bugs that negatively affect the user experience. For instance, if a database column that an application relies on for queries is altered or removed without corresponding updates to the application code, it can result in application failures or incorrect data being displayed to users. This disruption not only impacts user satisfaction but also places additional pressure on development teams to quickly address and rectify issues caused by changes outside their immediate control, striving to maintain system availability and functionality.

DevOps teams: Integration and continuous deployment

DevOps teams, which rely heavily on streamlined and predictable deployment processes, find schema drift particularly problematic. It introduces obstacles in maintaining a consistent and smooth CI/CD pipeline. Drift can cause failures in automated tests and deployments that assume a certain database schema, leading to delays and increased manual intervention to resolve discrepancies. This not only slows down the deployment cycle but also demands additional resources to diagnose and fix issues arising from schema inconsistencies.

Learn more: Bring DevOps Metrics to Your Database Pipelines with CI/CD Automation 

IT leaders: Compliance and governance

For IT leaders, schema drift poses a serious risk to database compliance and governance. Unplanned changes can lead to violations of regulatory standards, as the database may no longer adhere to industry compliance requirements. This can result in costly penalties and a tarnished reputation. Moreover, schema drift can complicate audit trails, making it difficult to trace data handling and processing, further exposing the organization to compliance risks.

Learn more: Streamlining Database Compliance with CI/CD Integration

DBAs: Data integrity and performance

DBAs face direct challenges from schema drift, primarily concerning data integrity and database performance. Drift can lead to data discrepancies, where the actual data stored becomes misaligned with the expected schema, potentially leading to data loss or corruption. Additionally, unexpected schema changes can affect database performance by creating inefficient queries or data relationships that were not accounted for, making the system slower and less reliable. Overall, database drift means more work for DBAs as they hunt down the problems and work to deploy solutions. 

Learn more: The Next-Gen DBA: How Database DevOps Automation Unlocks Efficiency & Innovation 

Three ways to deal with database drift

Addressing database drift effectively requires a combination of strategic organizational adjustments, process improvements, and technological interventions. Here are three key ways organizations can manage and mitigate the impact of database drift.

1. Create transparent organizational structures

Organizationally, teams need to be more transparent and cooperative as changes flow from development to production. 

Database administrators (DBAs) might sit on a separate team and act as a shared service to different application teams that all rely on the same database. But you can’t just fire off help tickets and expect that everything will turn out okay. Instead, you have to create open, transparent communication between development and operational teams. You have to build a shared sense of responsibility for database change deployments in all environments – production or otherwise.

2. Address “quick fix” processes

In addition to organizational habits, you have to adjust processes. 

Hotfixes and patches are often made in a hurry to higher environments because of a mismatch between application and database code changes or because of issues that were discovered very late (often in production). To prevent such scrambling, organizations need to invest in a more robust testing process. Teams need a unified and transparent path for application and database code as it moves from development to production. 

So long as application and database code take separate paths to production, there’s a risk of mismatch or error. Separate paths lead to conditions that produce database drift and service outages.

3. Automate systems wherever possible

Database drift can be addressed with appropriate automation solutions. 

Manual database deployment processes yield poorly documented, custom, hard-to-reproduce deployments that contribute to chaos and confusion. By investing in automation tools that can verify database code changes and package verified changes into an immutable artifact for downstream deployment. 

Database deployment automation can also perform transparent, auditable, and repeatable deployment, ending most of the database drift. And, with tools that can snapshot and compare databases as part of an automated and regular process, you can quickly catch any “out-of-process” changes and fix problems fast.

4. Get visibility into change management metadata and workflow analytics

Drift can also be detected, mitigated, and remedied with speed and ease when teams have deep visibility into change management metadata and workflow analytics. This requires collecting and analyzing detailed logs of all database changes, which helps in understanding the who, what, when, and why of each change. By deploying tools that track database changes in real-time, organizations can monitor the database environment continuously, allowing teams to detect unauthorized or unintended modifications that contribute to drift.

Utilizing workflow analytics can also play a significant role in preempting and resolving drift issues. By analyzing trends and patterns from change logs and other metadata, teams can identify potential risks and inefficiencies early in the development cycle. This proactive approach not only helps in catching deviations from the expected database state but also enables a quicker response to mitigate issues before they escalate.

Integrating change operation monitoring and pipeline analytics into the database management process fosters a more controlled and compliant environment. This enhanced visibility ensures that any drift is quickly identified and addressed, minimizing the risk of data integrity issues and operational disruptions. Moreover, it supports a culture of continuous improvement, where insights from past changes guide future optimizations, leading to a more resilient and efficient database infrastructure.

Automatically detecting database drift with Liquibase Pro

The first thing to do to automatically prevent drift is to detect when it’s happening at scale. Liquibase Pro offers automatic database drift detection. Here’s how it works.

First, get a free trial to Liquibase Pro. With the trial license key added to the team’s liquibase.properties file, they now can run the diff command as usual to compare two database schemas but this time add --format=json like so:

liquibase diff --format=json

Now, they’ll receive a structured JSON output object that lists the differences between the two databases (as configured in your liquibase.properties file or Maven POM file). By default, the result outputs to STDOUT, which provides maximum flexibility to parse the result and share it with other tools. You also have the option to output as a collection of files:

liquibase --outputFile=myfile.json diff --format=json

I have the JSON file. Now what?

Given that JSON can be understood by machines, you’ll need to write some code to parse the JSON and act on what you find. There are a lot of possibilities here that can easily match your workflow policies and prevent bad database deployments.

Examples on how to automate with Liquibase Pro and help your team

• Look for missing elements and automate running diffChangeLog between the source and target databases.
• Use JSON to count the number of differences and set a priority to the source-target pairs, pushing those with the most drift to the highest priority.
• Look for a critical column or table. If the critical, high-value element is in the JSON diff report, you could halt your CI/CD process before you run an update and trigger a notification to your team.

Once you have a file, you can process the data to generate reports, trigger actions, set up alerts, or whatever makes sense to make the process more transparent and easily shared with your team to ensure that your databases stay in sync.

Drift Detection Reports

The Drift Detection Reports capability runs a diff command to compare the current/source database state to a previous snapshot or a target environment. 

Drift between environments compares any two environments to find any differences. This can be executed from the CLI or integrated CI/CD tools. 

Then it prepares a Drift Report that summarizes the differences detected between database environments and indicates severity. 

With the same spirit but different purpose, Drift between previous snapshot initiates a few steps:

1. A snapshot of the target environment is taken and stored 
2. Drift detection (against that snapshot) is scheduled or executed before each deployment
3. The snapshot is refreshed after a deployment

Drift reports are a valuable aspect of database observability and Liquibase’s broader Report offerings, including Operations, Checks Run, and other detailed logs for all the context a team might need about a change or a database environment. 

Leading benefits of Drift Detection & Reports

In essence, Drift Detection acts as an early warning system in a DevSecOps context, highlighting deviations that could potentially disrupt database operations, compromise security, or violate compliance standards. It empowers teams to maintain tighter control over the database environment, ensuring that all changes are intentional, authorized, and aligned with organizational policies and security practices.

But the usefulness of Drift Detection doesn’t stop there. 

Improving CI/CD integration and DevOps best practices

Drift Detection plays a pivotal role in enhancing the alignment between application requirements and database states, which is critical for seamless CI/CD integration. By promptly identifying discrepancies between expected and actual database states, teams can address mismatches that might otherwise lead to deployment failures or application errors.

This integration into CI/CD development pipelines allows for automated verification of database states throughout different stages, elevating the quality and stability of releases. The automated nature of Drift Reports reduces the need for manual tracking, decreasing the risk of human errors while ensuring consistent monitoring and management of database schema updates.

Enabling observability and measurement

Implementing Drift Detection enhances the observability of database change workflows, providing crucial insights into their operational status, efficiency, and reliability. This heightened level of observability is vital for database administrators and DevOps teams to ensure that the databases operate and deploy as expected.

Drift Reports can provide detailed analytics that help in identifying trends and patterns in failed or problematic database changes. This capability allows teams to proactively address potential issues, optimizing the database's performance and stability before problems can escalate.

Continuously optimizing change management

Constant monitoring and reporting on database drift with Drift Detection enables organizations to embrace DevOps continuous optimization at the database layer, too. This ongoing refinement of processes helps in fine-tuning deployment methods and operational workflows, effectively reducing future instances of drift. 

The feedback provided by Drift Reports is integral to continuous improvement, allowing teams to adapt future database code for higher quality. Plus, the consistent documentation and audit trails captured by these reports and Liquibase’s Structured Logging improves compliance with regulatory standards and enhances overall security by quickly, clearly, and easily identifying unauthorized changes that could point at potential security threats.

Database drift detection for data pipelines

In data pipelines where data scientists, engineers, and business intelligence analysts work collaboratively to refine and enhance data utility and draw business insights, the risk of database schema drift becomes a different, but still significant concern. Schema drift, or unintended changes in database configuration, can severely disrupt data pipelines, leading to data anomalies that compromise analytics and core decision-making processes.

The introduction of Drift Detection in data pipeline change management allows data teams to proactively monitor and catch any deviations from the defined database schema, ensuring that all data flows and transformations remain consistent and reliable. For instance, when a data scientist adjusts a data model, this can inadvertently require changes to the underlying database schema. Without a system to detect these changes, such modifications could go unnoticed until they cause significant issues downstream, affecting data quality and the accuracy of business intelligence insights.

By ensuring that any database schema changes are automatically detected and reported, organizations can foster a more agile and responsive data management strategy, which is essential for supporting advanced data analytics and machine learning initiatives that drive business growth. Drift Detection within the data pipeline not only mitigates the risks associated with database drift but also enhances the collaborative capabilities of cross-functional teams, ensuring that the data infrastructure remains robust and aligned with the organization's operational and strategic goals. 

Get a handle on database drift

Ultimately, market expectations have evolved, making it necessary for teams to deliver new capabilities more quickly and at a higher quality. It is critical to ensure that database drift doesn’t creep up while attempting to speed up software releases. Database drift can do major damage in the form of service outages, brand and reputation damage, data loss, and increased time to resolution. Without making the necessary changes in culture, process, and tooling, attempts to speed up software delivery will fail.

Learn more about Drift Detection and Reports, plus other powerful database DevOps capabilities and concepts, in our Guide to Database Observability.