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.Customer
ADD LoyaltyPoints INT;

The SQL Database Project still contains only:

CustomerID
Name

The live database now contains:

CustomerID
Name
LoyaltyPoints

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 Products
ADD 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: Employee
Column missing from project:
Department

Drift During Deployment

Suppose:

Project:

Customer
Orders
Invoices

Production:

Customer
Orders
Invoices
AuditLog

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:

1. Update the SQL Database Project.
2. Commit the change to source control.
3. 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.

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