This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
--> Implement CI/CD by using SQL Database Projects
--> Detect schema drift by using SQL Database Projects
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
Understanding schema drift is essential for modern DevOps and database lifecycle management (DLM). The DP-800 exam expects candidates to understand how SQL Database Projects establish the desired database state, how schema drift occurs, how to detect it before deployment, and how to prevent accidental overwrites of production databases.
What is Schema Drift?
Schema drift occurs when the actual database schema no longer matches the schema stored in source control or the SQL Database Project.
In other words:
- Source control contains the expected design
- Database contains the actual implementation
If someone changes the production database directly, the database “drifts” away from the project.
Example:
Project contains:
CREATE TABLE Sales.Customer( CustomerID INT PRIMARY KEY, Name NVARCHAR(100));
A DBA later runs:
ALTER TABLE Sales.CustomerADD LoyaltyPoints INT;
The SQL Database Project still contains only:
CustomerIDName
The live database now contains:
CustomerIDNameLoyaltyPoints
This difference is schema drift.
Why Schema Drift Is Dangerous
Schema drift creates several problems:
- unexpected deployment failures
- overwritten production changes
- missing documentation
- inconsistent environments
- broken CI/CD pipelines
- difficult troubleshooting
- unreliable rollback
Organizations using DevOps aim to eliminate manual production changes because every manual change introduces drift.
Common Causes of Schema Drift
Manual Changes
A DBA executes:
ALTER TABLE ProductsADD InternalNotes NVARCHAR(200);
The project is never updated.
Emergency Production Fixes
A production outage occurs.
An engineer fixes the database immediately.
The fix is never committed back into Git.
Hotfix Deployments
A hotfix bypasses the normal deployment pipeline.
The database project remains outdated.
Third-Party Applications
Vendor software automatically creates:
- indexes
- tables
- triggers
- stored procedures
These objects may not exist in source control.
Automatic Maintenance Scripts
Jobs create:
- audit tables
- archive tables
- logging procedures
If unmanaged, these appear as schema drift.
Desired State vs Actual State
SQL Database Projects follow a declarative model.
Instead of saying:
Execute these SQL commands.
They say:
The database should look like this.
Deployment tools compare:
Desired State (Project)
↓
Current Database
↓
Generate Deployment Script
The comparison process naturally identifies drift.
SQL Database Projects as the Source of Truth
A SQL Database Project should become the organization’s single source of truth.
Everything should originate from:
- Git
- pull requests
- code reviews
- approved deployments
Not from:
- SSMS manual edits
- Azure Data Studio changes
- production hotfixes
- direct ALTER TABLE statements
How Schema Comparison Works
The deployment engine compares:
Project Object
↓
Database Object
It evaluates:
- tables
- columns
- indexes
- views
- procedures
- functions
- triggers
- users
- roles
- constraints
- sequences
Every difference is identified before deployment.
Schema Compare
Schema Compare compares:
Source
↓
Target
Possible comparisons include:
Project → Database
Database → Project
Database → Database
Project → Project
The generated report identifies:
- missing objects
- additional objects
- modified objects
- renamed objects
- changed permissions
Example Drift Detection
Project contains:
CREATE TABLE Employee( EmployeeID INT, Name NVARCHAR(100));
Database contains:
CREATE TABLE Employee( EmployeeID INT, Name NVARCHAR(100), Department NVARCHAR(50));
Schema Compare reports:
Table: EmployeeColumn missing from project:Department
Drift During Deployment
Suppose:
Project:
CustomerOrdersInvoices
Production:
CustomerOrdersInvoicesAuditLog
If the deployment option allows dropping extra objects:
Deployment may attempt:
DROP TABLE AuditLog;
This could remove an important production table.
Understanding deployment options is therefore critical.
Deployment Reports
Before publishing, SQL Database Projects can generate:
- deployment report
- deployment script
The deployment report shows:
- objects added
- objects removed
- objects modified
Reviewing the report is a best practice.
Deployment Script Review
Instead of deploying immediately:
Generate Script
↓
Review
↓
Approve
↓
Deploy
This catches accidental schema drift before changes reach production.
Ignore Options
Some differences are expected.
Deployment settings allow ignoring:
- whitespace
- object order
- permissions
- filegroups
- partition schemes
- users
- role memberships
- extended properties
Ignoring irrelevant differences reduces false positives.
Drift Detection in CI/CD Pipelines
Typical pipeline:
Developer commits
↓
Build project
↓
Run validation
↓
Compare schema
↓
Detect drift
↓
Generate report
↓
Approve
↓
Deploy
If drift exists:
Pipeline can:
- fail
- warn
- require manual approval
Preventing Schema Drift
Best practices include:
Use Source Control
Every schema change should originate in Git.
Require Pull Requests
No direct commits to the main branch.
Block Direct Production Changes
Restrict:
- ALTER
- CREATE
- DROP
to deployment pipelines.
Automate Deployments
Avoid manual publishing whenever possible.
Review Deployment Reports
Always inspect changes before production deployment.
Synchronize Hotfixes
If an emergency fix is applied directly to production:
- Update the SQL Database Project.
- Commit the change to source control.
- Redeploy from the project.
Detecting Drift with SqlPackage
SqlPackage can compare a DACPAC with a target database.
Example:
SqlPackage /Action:DeployReport
or
SqlPackage /Action:Script
These operations generate reports showing differences before deployment.
Azure DevOps and GitHub Actions
CI/CD pipelines commonly:
- build the SQL Database Project
- produce a DACPAC
- compare with the target database
- generate deployment scripts
- detect unexpected schema changes
- require approval before deployment
This ensures every deployment is repeatable and auditable.
Handling Intentional Drift
Sometimes production intentionally differs.
Examples:
- monitoring tables
- audit tables
- replication objects
- vendor-managed objects
Possible approaches:
- exclude those objects
- maintain separate projects
- use deployment filters
- configure ignore settings
Schema Drift vs Data Drift
These terms are different.
| Schema Drift | Data Drift |
|---|---|
| Structure changes | Data values change |
| Tables | Rows |
| Columns | Records |
| Indexes | Business data |
| Constraints | Transactions |
Example:
Schema drift:
ADD COLUMN Salary
Data drift:
Salary changed from 50000 to 70000
Best Practices
- Treat the SQL Database Project as the single source of truth.
- Never make untracked production schema changes.
- Use pull requests and code reviews for every schema modification.
- Generate deployment reports before publishing.
- Review deployment scripts for unintended object drops.
- Integrate schema comparison into CI/CD pipelines.
- Keep production synchronized with source control after emergency fixes.
- Use deployment options carefully to avoid deleting valid production objects.
- Automate validation whenever possible.
- Document intentional schema differences.
DP-800 Exam Tips
Remember these key exam points:
- Schema drift means the database no longer matches the project.
- SQL Database Projects define the desired database state.
- Schema Compare identifies differences before deployment.
- Deployment reports should always be reviewed.
- CI/CD pipelines should automatically detect drift.
- Source control should remain the authoritative definition of the schema.
- Direct production modifications increase deployment risk.
- Emergency fixes should always be merged back into the SQL Database Project.
Practice Exam Questions
Question 1
A database administrator manually adds a column to a production table without updating the SQL Database Project. What has occurred?
A. Data corruption
B. Schema drift
C. Query regression
D. Database fragmentation
Answer: B
Explanation: Schema drift occurs whenever the deployed database differs from the schema stored in the SQL Database Project.
Question 2
What is considered the desired state during SQL Database Project deployments?
A. The production database
B. The deployment report
C. The SQL Database Project
D. The Query Store
Answer: C
Explanation: SQL Database Projects define the desired schema used to generate deployment changes.
Question 3
Which tool compares a SQL Database Project with an existing database?
A. SQL Profiler
B. Database Mail
C. Activity Monitor
D. Schema Compare
Answer: D
Explanation: Schema Compare analyzes differences between project schemas and deployed databases.
Question 4
Why should deployment reports be reviewed before publishing?
A. To improve indexing
B. To compress data
C. To identify unexpected schema changes
D. To rebuild statistics
Answer: C
Explanation: Deployment reports identify additions, deletions, and modifications before changes are applied.
Question 5
Which practice best minimizes schema drift?
A. Allow direct production changes
B. Disable source control
C. Store only stored procedures in Git
D. Require all schema changes through source control
Answer: D
Explanation: Requiring every schema modification to flow through source control prevents unmanaged changes.
Question 6
Which deployment option helps prevent accidental removal of valid production objects?
A. Review deployment scripts before publishing
B. Disable indexes
C. Shrink the database
D. Disable Query Store
Answer: A
Explanation: Reviewing generated scripts allows teams to identify unintended DROP statements before deployment.
Question 7
An emergency production fix was made directly on the database. What should happen next?
A. Ignore the change
B. Remove the production change immediately
C. Update the SQL Database Project and commit the change
D. Rebuild every index
Answer: C
Explanation: Production hotfixes should be reflected in the project and committed to source control to eliminate schema drift.
Question 8
Which pipeline stage commonly detects schema drift?
A. Backup compression
B. Schema comparison before deployment
C. Statistics updates
D. Data import
Answer: B
Explanation: CI/CD pipelines typically compare the desired schema with the target database before deployment.
Question 9
Which statement correctly describes schema drift?
A. It refers to changes in business data.
B. It indicates poor query performance.
C. It describes missing backups.
D. It occurs when the deployed schema differs from the SQL Database Project.
Answer: D
Explanation: Schema drift specifically concerns differences between database structure and the project’s intended schema.
Question 10
Why are ignore settings sometimes configured during schema comparison?
A. To disable security
B. To ignore expected differences that should not trigger deployments
C. To improve backup speed
D. To compress deployment packages
Answer: B
Explanation: Ignore settings reduce false positives by excluding acceptable differences such as permissions or extended properties from deployment comparisons.
Go to the DP-800 Exam Prep Hub main page
The Data Community 