Finding Schema Drift Between Database Environments
For the person who owns several database environments and the pipelines reading from them; the platform settings that absorb drift downstream are named where they appear.
On this page
Your migration works everywhere except the one database nobody has compared against the repository in months, and it fails there on an object no branch of the repository knows about. That gap is schema drift. You find it by putting the live catalog next to the version-controlled definition and reading the differences, on a schedule you choose rather than at deployment time.
What database schema drift is
Schema drift is the divergence between the structure a live database actually has and the definition that is supposed to describe it. That definition lives in Git, as migration files or as a declarative model of the schema, and it is what the application code, the test suite and the next migration are all written against. Drift accumulates when a change reaches the database without passing through that definition first.
| Dimension | Planned change | Drift |
|---|---|---|
| Trigger | A release, or scheduled refactoring | A hotfix, a manual patch, a half-applied migration |
| Record | A versioned migration file or model commit | Nothing outside the catalog itself |
| Testing | Ran in development and staging first | Applied straight to a live database |
| Visibility | A commit, a pull request, a changelog | None, until something fails |
Nothing on the drift side of that table is a mistake in itself. An index added at 2am to end an outage was the right call. The drift is what happens afterwards, when the definition in Git is not updated to match, and the two descriptions of the same database quietly disagree.
Drift is also directional, which matters when you go looking for it. An object that is in the database and not in the definition arrived out of process, and the question is whether to keep it. An object that is in the definition and not in the database means a migration did not run, or did not finish, and the question is why. The same comparison surfaces both, but they are different incidents with different fixes, and the difference list is where you tell them apart.
An example of schema drift
An e-commerce database slows to a crawl at 2am because an orders query has no index to use. The on-call engineer connects with a SQL client and runs the ALTER TABLE that creates it, and the latency drops. The incident closes at 3am. The migration file that would record the index is never written, and from that moment production carries an object that exists in no branch of the repository. The next developer to rebuild a local database from the migrations gets an environment without it, and the query that was fast in production is slow again on their machine for reasons nobody can reproduce.
A partly applied migration produces the same divergence without anyone touching a client. The engine decides how much of a failed multi-statement migration survives. PostgreSQL 18 runs DDL inside the surrounding transaction block: BEGIN initiates a transaction block, and all statements after a BEGIN command are executed in a single transaction until an explicit COMMIT or ROLLBACK is given[1], so a migration that dies at statement four leaves nothing behind. MySQL 8.4 does not offer that. Atomic DDL is not transactional DDL, and DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement[2]. Three statements commit, the fourth fails, and the schema now matches neither the version before the migration nor the version after it.
Three more sources are worth watching for. An ORM configured to create or alter tables at application boot writes DDL that no migration file ever recorded, on whichever environment it starts in. An environment rebuilt from a snapshot taken before the last few migrations comes back missing them, which looks like a restore rather than a change. And an analytics or business intelligence tool pointed straight at a production catalog leaves its own objects behind there: a temporary table, a materialized view, or a column altered to suit a report.
Why drift breaks deployments and pipelines
The cost of drift arrives late, which is what makes it expensive. A drifted database serves traffic correctly for months, because the application only ever touches the objects it knows about. The bill comes due when the next migration runs: it was written against the definition in Git, the database no longer matches that definition, and a routine deploy becomes an incident in the middle of a release window.
Downstream, the same divergence breaks extraction jobs rather than deployments. Typical ETL patterns fail when incoming columns and fields change, because they tend to be tied to those source names[3]. A column renamed in an operational database stops an extraction that referenced it by name, and a column whose type was widened upstream can pass through a job that truncates it silently, which is worse than a crash because the analytics table keeps filling with values that look plausible.
Two consequences follow that are easy to miss. Rebuilding an environment from source control reproduces the definition, not the database, so every manual index and grant added during a past incident is absent from the rebuild. And a permission or constraint changed by hand is invisible to any review that reads the repository, because the repository never learned about it.
How to handle schema drift in data engineering pipelines
An ingestion pipeline binds to column names and types when it is built, which is why a change upstream stops it. The alternative is to bind late, reading the incoming fields at execution time instead. Azure Data Factory natively supports flexible schemas that change from execution to execution, so you can build generic data transformation logic without the need to recompile your data flows, and it treats schema drift flows as late-binding flows[3].
Checking "Allow schema drift" in a source transformation reads all incoming fields from the source during execution and passes them through the entire flow to the sink. Columns that arrive without being in the source projection are the drifted ones, and by default they arrive as a string data type; checking "Infer drifted column types" makes the flow infer their types instead. To act on them, a column pattern in a Derived Column or Aggregate transformation matches on any combination of name, stream, position, origin, or type, so a transformation refers to a shape rather than to a list of names. At the far end, "Allow schema drift" in the sink transformation with the Auto-mapping slider on writes every incoming column to the destination[3].
Late binding buys continuity and costs you the schema views: the drifted column names are not available in the flow while you build it. A renamed column enters as a new attribute and the old one fills with nulls, so the pipeline keeps running and the reconciliation moves downstream to whoever reads the table.
Schema drift in Databricks
Databricks handles the same problem at the ingestion boundary of a Delta table. Auto Loader tracks the schema of the files it reads from cloud object storage and evolves the target according to cloudFiles.schemaEvolutionMode. The evolution is not free of interruption: when evolving the schema automatically, the stream initially fails, and on restart the evolved schema is used[4]. Plan for that restart, because a job that treats any failure as an alert will page someone for a column addition.
Unsupported type changes are captured in the rescuedDataColumn rather than dropped, which is the setting that decides whether a surprise in the source costs you data or only costs you a column to inspect later. On the Delta side, with mergeSchema enabled, new columns are added automatically; otherwise the query fails and you restart the stream to add the new columns to the schema, and the Delta table does not require a rewrite either way. Type widening is supported in Databricks Runtime 16.4 and above with schemaEvolutionMode set to addNewColumnsWithTypeWidening, where supported data type changes are widened automatically[4].
Schema drift in Microsoft Fabric
Dataflow Gen2 puts the same decision in the destination settings, and the update method decides whether you get to make it at all: schema options on publish only work when the update method is replace, and when you append data, you can't change the schema[5].
| Setting | Mapping | On refresh | If the query schema changes |
|---|---|---|---|
| Automatic settings | Managed for you | Table dropped and recreated | Mapping adjusts when you republish |
| Dynamic schema | You update it yourself | Table dropped and recreated | Allowed on republish |
| Fixed schema | You update it yourself | Only the rows are replaced | Publish fails |
The trade in that table is relationships and measures against tolerance for change. A dropped and recreated table might lose relationships or measures that were added to it before; a fixed schema keeps them and refuses the change instead. Automatic settings currently work only with a Lakehouse or an Azure SQL database as the destination, and when loading into the warehouse, only fixed schema is supported. If a column isn't in your initial settings and you add it manually, "(none)" appears in the mapping until you set it[5].
Finding the drift between a model and a database
Everything above absorbs drift downstream. Finding it at the source is a different job: put the whole structure of the database next to the definition it should match, and read the list of differences. DbSchema does that by comparing its .dbs design model, a plain XML file you keep in Git next to the application code, against a connected database.
The comparison runs when you ask for it, and only then. Choose Schema → Compare Model with Database and DbSchema reads the live catalog and puts every table, column, data type, foreign key and index next to what the model holds. Schema → Refresh Schema from Database pulls the current database state into the model and reports what changed since the last sync. Nothing watches for drift in the background, which is deliberate: you decide when a comparison runs and against which connection. For a scheduled check, the same synchronization is scripted with Groovy automation scripts or the DbSchemaCLI and run headless from a pipeline. Schema synchronization is in the Pro edition, refresh included; the free Community Edition connects, reverse-engineers, draws the diagrams and runs the SQL editor.
Every difference in the Sync Dialog carries its own decision, and each one changes a different thing. Updating the model writes the live structure into the .dbs file on your disk, which is how the 2am index gets adopted into the definition and committed. Pushing the difference the other way generates the DDL and steps through it against the live database, one statement at a time, and that is the only choice that alters the database. Skipping a difference changes neither side; DbSchema recomputes the comparison from the catalog on every run, so a skipped difference reappears next time rather than being remembered as accepted.
Adopting drift into the model is the decision worth being careful about, because it is silent. An index someone added under pressure is usually worth keeping; a column an ORM created at boot usually is not. Read each difference before you take it into the model, then commit the file, so the next comparison starts from a definition you agreed to. Turning the other direction into something safe to run on production is covered in turning a schema diff into a safe migration script, and the wider practice of keeping the design in Git in schema versioning for PostgreSQL.
Find the drift before a release finds it for you. Download DbSchema at https://dbschema.com/download.html, reverse-engineer each environment into a model, and compare the model against the database you trust least. Schema synchronization and saving the model to a .dbs file are in the Pro edition.
Frequently asked questions
What is database schema drift?
Schema drift is the gap between the structure a live database has and the definition in version control that is supposed to describe it. The section on what schema drift is sets it against a planned change, and says why the direction of a difference decides what you do about it.
What are common examples of schema drift?
The five usual ones are an index added by hand during an incident, a migration that committed part of itself and failed, an ORM that alters tables at application boot, an environment restored from a snapshot older than the last few migrations, and a reporting tool writing its own objects into a production catalog. The worked example follows the first two, including what each engine leaves behind when a multi-statement migration dies partway.
Why does schema drift break data pipelines?
Extraction jobs bind to source column names when they are built, so a rename upstream stops them at the next run and a widened type can pass through them silently truncated. The section on why drift breaks deployments and pipelines has the deployment side of the same divergence, which arrives later and costs more.
How do you handle schema drift in Databricks?
Auto Loader evolves the target schema according to cloudFiles.schemaEvolutionMode, and unsupported type changes land in the rescuedDataColumn instead of being dropped. The section on schema drift in Databricks has the restart that evolution costs you, and the runtime version that widens types automatically.
How does Microsoft Fabric handle schema changes?
Most destinations support both append and replace as update methods, and Fabric KQL databases and Azure Data Explorer do not support replace[5], which is the update method a schema change on publish requires. The section on schema drift in Microsoft Fabric has what automatic settings, dynamic schema and fixed schema each do to the destination table on refresh.
How do you find schema drift using DbSchema?
DbSchema reads the live catalog when you ask it to and lists every table, column, type, key and index that differs from the .dbs design model. Where the model is ahead of the database, Schema → Create or Upgrade Schema in Database generates the DDL that brings that database up to the model. The section on finding drift between a model and a database says which side each of the other choices changes.
What happens if I skip a difference in DbSchema?
Neither side changes, and DbSchema rebuilds the comparison from the live catalog on the next run, so the same difference is listed again. The opposite decision, taking a difference into the model, is the one worth reading first, and the section on finding drift between a model and a database says why.
Sources
Find the drift between your model and your database
DbSchema compares a .dbs design model against a connected database and lists every difference side by side, so you can keep each one in the model, commit it to the database, or leave it alone. Synchronization runs when you ask for it. Schema synchronization is a Pro Edition feature; the free Community Edition connects, reverse-engineers and draws the diagrams.
