Tag: Database Projects

Data Governance, Databases, DP-800, Microsoft Certification, SQL

Update a SQL database project and deploy changes (DP-800 Exam Prep)

 
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
      --> Update a SQL database project and deploy changes

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

For the DP-800 exam, you should understand how to modify a SQL Database Project, validate the changes, build the project into a DACPAC, compare the project to a target database, generate deployment scripts, publish changes safely, and integrate the entire deployment process into a CI/CD pipeline.

What Is a SQL Database Project?

A SQL Database Project is a source-controlled representation of a database schema. Rather than directly modifying a production database, developers modify the project files, commit those changes to source control, and deploy them through an automated pipeline.

A SQL Database Project typically contains:

  • Tables
  • Views
  • Stored procedures
  • Functions
  • Triggers
  • Security objects
  • Roles
  • Users
  • Schemas
  • Permissions
  • Reference data (optional)
  • Project configuration

The project serves as the single source of truth for the database schema.

Why Update the Database Project?

Every database change should begin in the project—not in the production database.

Typical changes include:

  • Adding new tables
  • Modifying columns
  • Creating indexes
  • Updating stored procedures
  • Adding functions
  • Changing permissions
  • Creating new schemas
  • Modifying constraints

Example:

Original table:

CREATE TABLE Sales.Customer
(
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100)
);

Business requirement:

Store customer email addresses.

Updated project:

CREATE TABLE Sales.Customer
(
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    EmailAddress NVARCHAR(255)
);

After the project is updated, the deployment process determines the necessary ALTER TABLE statement.

Typical Deployment Workflow

The recommended workflow is:

Developer
↓
Modify SQL Database Project
↓
Validate
↓
Build DACPAC
↓
Commit to Git
↓
Pull Request
↓
Code Review
↓
Merge
↓
CI/CD Pipeline
↓
Deploy Development
↓
Deploy Test
↓
Deploy Production

This workflow provides consistency, repeatability, and auditability.

Updating Database Objects

Developers modify individual object files.

For example:

Tables
    Customer.sql
Views
    ActiveCustomers.sql
Procedures
    usp_CreateOrder.sql
Functions
    fn_TotalSales.sql

Each object exists as its own SQL file.

Benefits include:

  • Easier source control
  • Better merge handling
  • Clear code reviews
  • Object-level change history

Schema Validation

Before deployment, the project should validate successfully.

Validation checks include:

  • Syntax errors
  • Missing object references
  • Invalid dependencies
  • Duplicate object names
  • Constraint issues
  • Circular references

Early validation prevents deployment failures.

Building the Project

Once validated, the project is built into a DACPAC.

A DACPAC contains:

  • Database schema
  • Metadata
  • Deployment model

It does not include:

  • User data
  • Transaction logs
  • Database backups

The DACPAC becomes the deployment artifact used throughout the pipeline.

What Happens During Deployment?

Deployment compares:

Desired State (DACPAC)
↓
Target Database
↓
Difference Analysis
↓
Deployment Script
↓
Database Update

The deployment engine generates only the necessary changes.

Example:

Project:

EmailAddress column exists

Target database:

EmailAddress missing

Generated deployment:

ALTER TABLE Sales.Customer
ADD EmailAddress NVARCHAR(255);

Declarative Deployment Model

SQL Database Projects use a declarative deployment model.

Developers describe the desired database schema rather than writing migration scripts manually.

Instead of:

Run these SQL commands.

You define:

The database should look like this.

The deployment engine determines the required SQL statements.

Incremental Deployments

Deployments are incremental.

Only differences are deployed.

If no differences exist:

No deployment changes

If one object changes:

Only that object is updated.

This minimizes deployment time and risk.

Deployment Reports

Before publishing, SQL Database Projects can generate a deployment report.

The report identifies:

  • New objects
  • Modified objects
  • Removed objects
  • Security changes
  • Dependency changes

Reviewing the report before production deployment is a best practice.

Deployment Scripts

Instead of deploying immediately, teams often generate a deployment script.

Benefits include:

  • DBA review
  • Change approval
  • Compliance auditing
  • Troubleshooting
  • Rollback planning

Example workflow:

Build
↓
Generate Script
↓
Review
↓
Approve
↓
Deploy

Publish Profiles

A publish profile stores deployment settings.

Typical settings include:

  • Target server
  • Database name
  • Authentication
  • Deployment options
  • Object exclusions
  • Ignore settings

Rather than entering these settings each time, teams reuse publish profiles.

Deployment Options

Deployment options control deployment behavior.

Common examples include:

  • Block deployment on data loss
  • Drop objects not in source
  • Ignore permissions
  • Ignore users
  • Ignore role memberships
  • Ignore whitespace differences
  • Ignore filegroups

Proper configuration reduces deployment risk.

Handling Schema Drift

Before deployment, the deployment engine compares:

Project

↓

Production

If unexpected differences exist:

  • deployment report identifies them
  • deployment script reflects them
  • pipeline may fail
  • manual approval may be required

This helps prevent accidental overwriting of production changes.

Deploying Through CI/CD

Modern SQL deployments are automated.

Typical Azure DevOps or GitHub Actions workflow:

Developer Commit
↓
Build
↓
Validate
↓
Create DACPAC
↓
Run Tests
↓
Schema Comparison
↓
Generate Deployment Script
↓
Approval
↓
Deploy

Automation reduces manual errors.

Safe Deployment Practices

Good deployment practices include:

  • Always build before deployment.
  • Validate object dependencies.
  • Review deployment reports.
  • Use pull requests.
  • Test deployments in lower environments.
  • Generate deployment scripts.
  • Back up production before deployment.
  • Avoid direct production edits.

Environment-Specific Deployments

The same DACPAC can deploy to:

  • Development
  • Test
  • QA
  • Staging
  • Production

Environment-specific settings come from publish profiles or pipeline variables.

Rollback Considerations

Unlike application deployments, database rollbacks can be difficult because:

  • Data may have changed.
  • Schema changes may be irreversible.
  • Dropped columns may lose data.
  • Constraint changes may affect applications.

Best practices include:

  • Backup databases
  • Generate deployment scripts
  • Test deployments
  • Use staged rollouts
  • Block deployments that could cause data loss

Common Deployment Problems

Missing Dependencies

Example:

Procedure references a table that does not exist.

Validation catches this before deployment.

Schema Drift

Someone manually modified production.

Deployment identifies unexpected differences.

Data Loss Warnings

Example:

ALTER TABLE Employee
DROP COLUMN Salary;

The deployment engine warns that existing data will be lost.

Permission Errors

The deployment account lacks sufficient permissions.

Required permissions often include:

  • ALTER
  • CREATE
  • DROP
  • EXECUTE
  • CONTROL (depending on deployment scope)

Using SqlPackage

Microsoft’s SqlPackage utility is commonly used for automated deployments.

Common actions include:

Build DACPAC
↓
Generate Deploy Report
↓
Generate Script
↓
Publish Database

Examples:

Generate deployment report:

SqlPackage /Action:DeployReport

Generate deployment script:

SqlPackage /Action:Script

Publish:

SqlPackage /Action:Publish

Azure DevOps Integration

Azure DevOps pipelines commonly perform the following:

  • Restore dependencies
  • Build SQL project
  • Produce DACPAC
  • Validate project
  • Run tests
  • Publish artifacts
  • Deploy to development
  • Require approval
  • Deploy to production

Approvals and gates help prevent accidental production deployments.

GitHub Actions Integration

GitHub Actions follows a similar workflow:

Push
↓
Build SQL Project
↓
Generate DACPAC
↓
Validate
↓
Deploy

Secrets such as connection strings are stored using GitHub Secrets rather than in project files.

Best Practices

  • Treat the SQL Database Project as the authoritative database definition.
  • Make schema changes only within the project.
  • Keep all database objects in source control.
  • Build the project after every change.
  • Validate dependencies before deployment.
  • Review deployment reports and generated scripts.
  • Deploy through automated CI/CD pipelines.
  • Test deployments in non-production environments.
  • Protect production deployments with approvals.
  • Keep publish profiles and pipeline configurations under version control where appropriate, excluding sensitive information.

DP-800 Exam Tips

Remember these important exam points:

  • SQL Database Projects use a declarative deployment model.
  • Building the project creates a DACPAC.
  • Deployments compare the desired schema with the target database.
  • Deployment reports identify planned changes before publishing.
  • Publish Profiles simplify repeatable deployments.
  • CI/CD pipelines automate building, validating, and deploying database changes.
  • Schema drift should be detected before deployment.
  • Production changes should originate from the SQL Database Project rather than direct database modifications.

Practice Exam Questions

Question 1

A developer adds a new stored procedure to a SQL Database Project. What should be the next step before deployment?

A. Restart the SQL Server service.

B. Build and validate the SQL Database Project.

C. Export the production database.

D. Rebuild all indexes.

Answer: B

Explanation: Building validates the project, checks dependencies, and produces the DACPAC used for deployment.

Question 2

What artifact is produced when a SQL Database Project is successfully built?

A. BACPAC

B. MDF file

C. DACPAC

D. Transaction log

Answer: C

Explanation: Building a SQL Database Project produces a DACPAC that contains the database schema and metadata.

Question 3

What is the primary purpose of a deployment report?

A. To store backup data

B. To monitor CPU usage

C. To list planned schema changes before deployment

D. To compress the database

Answer: C

Explanation: Deployment reports allow administrators to review proposed schema changes before they are applied.

Question 4

Which deployment model is used by SQL Database Projects?

A. Declarative deployment

B. Manual migration

C. Script-first deployment

D. Procedural deployment

Answer: A

Explanation: SQL Database Projects describe the desired end state, allowing the deployment engine to determine the required SQL statements.

Question 5

Why are Publish Profiles useful?

A. They encrypt databases.

B. They permanently store passwords inside source code.

C. They save deployment settings for reuse.

D. They improve query execution plans.

Answer: C

Explanation: Publish Profiles store deployment configuration such as server names, database names, and deployment options.

Question 6

What should a deployment pipeline typically do before publishing database changes?

A. Delete all indexes.

B. Generate and review a deployment script.

C. Disable all constraints.

D. Shrink the database.

Answer: B

Explanation: Reviewing generated deployment scripts helps identify unintended schema changes before deployment.

Question 7

Why is schema validation performed during the build process?

A. To increase transaction log size.

B. To encrypt the database.

C. To identify syntax errors and dependency issues before deployment.

D. To compress database files.

Answer: C

Explanation: Validation ensures that the project is internally consistent and can be successfully deployed.

Question 8

Which statement best describes incremental deployment?

A. Every database object is recreated during each deployment.

B. Only security objects are deployed.

C. Data is copied without changing the schema.

D. Only differences between the project and target database are deployed.

Answer: D

Explanation: SQL Database Projects compare the desired schema with the existing database and deploy only the necessary changes.

Question 9

Which practice best supports reliable database deployments?

A. Making schema changes directly in production.

B. Keeping the SQL Database Project as the authoritative source.

C. Editing production objects with SSMS only.

D. Avoiding source control.

Answer: B

Explanation: Using the SQL Database Project as the single source of truth supports consistent, repeatable, and auditable deployments.

Question 10

A team wants to automate database deployments across Development, Test, and Production environments. What is the recommended approach?

A. Manually execute SQL scripts on every server.

B. Use separate copies of the project for each environment.

C. Build one DACPAC and deploy it through a CI/CD pipeline using environment-specific settings.

D. Create a new SQL Database Project for every deployment.

Answer: C

Explanation: A single validated DACPAC can be deployed to multiple environments while Publish Profiles or pipeline variables provide environment-specific configuration.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification

Detect schema drift by using SQL Database Projects (DP-800 Exam Prep)

 
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.

Schema DriftData Drift
Structure changesData values change
TablesRows
ColumnsRecords
IndexesBusiness data
ConstraintsTransactions

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

DP-700, Microsoft Certification, Microsoft Fabric

Implement database projects (DP-700 Exam Prep)

 
This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Implement and manage an analytics solution (30–35%)
   --> Implement lifecycle management in Fabric
      --> Implement 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 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Introduction

As organizations adopt DevOps and DataOps practices, managing database changes through source control and automated deployments has become a critical requirement. Traditionally, database development was performed directly against production systems, often leading to inconsistent environments, deployment risks, and limited change tracking.

Database projects address these challenges by allowing database objects and schema definitions to be treated as code. This approach enables version control, collaboration, automated testing, continuous integration (CI), and continuous deployment (CD).

In Microsoft Fabric, database projects are particularly important when working with Fabric Data Warehouses. Database projects allow teams to manage warehouse schemas using modern software development practices and integrate them into broader lifecycle management processes.

For the DP-700 exam, you should understand the purpose of database projects, how they support DevOps workflows, their relationship to source control and deployment pipelines, and how they are used to manage Fabric Warehouse schemas.

What Is a Database Project?

A database project is a collection of files that define database objects and schema structures as source code.

Instead of creating objects directly within a database, developers define them in project files.

Examples include:

  • Tables
  • Views
  • Stored procedures
  • Functions
  • Security objects
  • Schemas
  • Constraints

The database project becomes the authoritative source for database definitions.

Why Database Projects Matter

Without database projects:

  • Schema changes may be undocumented.
  • Developers may overwrite each other’s work.
  • Production environments can drift from development environments.
  • Rollbacks become difficult.

Database projects provide:

  • Version control
  • Repeatable deployments
  • Change tracking
  • Environment consistency
  • Team collaboration

These capabilities align with modern DataOps and DevOps practices.

Database-as-Code

Database projects support the concept of Database-as-Code.

Database-as-Code means:

  • Database objects are stored as code files.
  • Changes are tracked through source control.
  • Deployments are automated.
  • Changes can be reviewed before implementation.

Instead of manually executing SQL scripts against production systems, organizations deploy controlled changes from source-controlled projects.

Database Projects and Fabric Warehouses

In Microsoft Fabric, database projects are primarily associated with Data Warehouses.

Fabric Warehouse objects such as:

  • Tables
  • Views
  • Stored procedures
  • Security definitions

can be managed through database projects.

This allows warehouse development to follow the same lifecycle management practices used for application development.

Components of a Database Project

A database project typically contains:

Schema Definitions

Definitions of database structures.

Examples:

CREATE TABLE Sales
(
    SalesID INT,
    Amount DECIMAL(18,2)
);

Views

Reusable query definitions.

Example:

CREATE VIEW vwSalesSummary
AS
SELECT SalesID, Amount
FROM Sales;

Stored Procedures

Reusable business logic.

Example:

CREATE PROCEDURE uspLoadSales
AS
BEGIN
    -- ETL logic
END;

Security Objects

Objects such as:

  • Users
  • Roles
  • Permissions

can also be defined and managed.

Database Projects and Source Control

One of the primary benefits of database projects is Git integration.

Database project files can be stored in repositories such as:

  • Azure DevOps
  • GitHub

Benefits include:

  • Change tracking
  • Auditability
  • Collaboration
  • Rollback capability

For DP-700, understand that database projects are often managed using the same source control processes as notebooks, pipelines, and other Fabric assets.

Version Control Workflow

A typical workflow looks like:

Developer
    ↓
Database Project
    ↓
Git Repository
    ↓
Validation
    ↓
Deployment

Benefits include:

  • Controlled releases
  • Code review processes
  • Consistent environments

Branching Strategies

Database projects commonly use standard Git branching practices.

Main Branch

Contains production-ready code.

main

Development Branch

Contains active development work.

main
 │
 └── develop

Feature Branches

Used for individual enhancements.

main
 │
 ├── feature/new-customer-table
 ├── feature/security-update
 └── feature-reporting-view

Feature branches reduce conflicts and improve collaboration.

Database Project Deployment

After changes are approved, they must be deployed.

The deployment process typically:

  1. Compares source and target schemas.
  2. Identifies differences.
  3. Generates deployment actions.
  4. Applies approved changes.

This process reduces manual effort and deployment errors.

Schema Comparison

Schema comparison is a key database project capability.

It identifies differences between:

  • Development and test environments
  • Test and production environments
  • Project definitions and deployed databases

Examples:

ObjectDevelopmentProduction
Sales TableExistsMissing
Customer ViewUpdatedOld Version

Schema comparison helps maintain consistency across environments.

Database Projects and CI/CD

Database projects are frequently integrated into CI/CD pipelines.

CI/CD stands for:

  • Continuous Integration
  • Continuous Deployment

Typical process:

Developer Changes
        ↓
Git Commit
        ↓
Build Validation
        ↓
Testing
        ↓
Deployment Pipeline
        ↓
Production

Benefits:

  • Faster releases
  • Reduced risk
  • Increased automation
  • Improved reliability

Database Projects and Fabric Deployment Pipelines

Deployment pipelines complement database projects.

Database projects manage:

  • Database definitions
  • Source code
  • Schema changes

Deployment pipelines manage:

  • Promotion between environments
  • Release processes

Typical environment flow:

Development
      ↓
Test
      ↓
Production

This separation of responsibilities is important for the exam.

Managing Warehouse Objects Through Projects

Database projects help manage common warehouse objects.

Tables

Examples:

  • Fact tables
  • Dimension tables

Views

Used for:

  • Business reporting
  • Data abstraction
  • Security

Stored Procedures

Used for:

  • ETL operations
  • Data loading
  • Data quality checks

Security Definitions

Used for:

  • Role management
  • Permission assignments

Benefits of Database Projects

Improved Collaboration

Multiple developers can work simultaneously.

Repeatable Deployments

Deployments become consistent across environments.

Auditability

All changes are tracked through source control.

Rollback Capability

Previous versions can be restored.

Reduced Human Error

Automation reduces deployment mistakes.

Common DP-700 Exam Scenarios

Scenario 1

Multiple developers are modifying warehouse schemas.

Requirement:

Track changes and prevent overwriting.

Solution:

Implement a database project with Git integration.

Scenario 2

A company needs consistent schemas across development, test, and production environments.

Solution:

Use database projects and deployment pipelines.

Scenario 3

An accidental schema change is deployed.

Requirement:

Restore a previous version.

Solution:

Rollback using source control history.

Best Practices

Store All Database Objects in Source Control

Treat schemas as code.

Use Feature Branches

Avoid direct modifications to production branches.

Perform Code Reviews

Review schema changes before deployment.

Automate Deployments

Use deployment pipelines whenever possible.

Maintain Environment Consistency

Use schema comparison tools to identify drift.

Document Changes

Use meaningful commit messages.

Example:

Added customer dimension surrogate key support

DP-700 Exam Focus Areas

You should understand:

✓ Purpose of database projects

✓ Database-as-Code concepts

✓ Source control integration

✓ Git repositories

✓ Schema comparison

✓ Deployment processes

✓ CI/CD integration

✓ Branching strategies

✓ Warehouse schema management

✓ Rollback and versioning

✓ Relationship to deployment pipelines

Practice Exam Questions

Question 1

What is the primary purpose of a database project?

A. Execute Spark workloads

B. Store warehouse data files

C. Manage database schemas as source-controlled code

D. Monitor Fabric capacities

Answer: C

Explanation

Database projects allow database objects and schemas to be defined, tracked, and managed as code. This supports version control, collaboration, and automated deployments.

Question 2

Which Fabric item is most commonly managed through a database project?

A. Data Warehouse

B. Eventstream

C. Notebook

D. Lakehouse Shortcut

Answer: A

Explanation

Database projects are primarily associated with Fabric Data Warehouses and their schema objects.

Question 3

What is a key benefit of storing database projects in source control?

A. Increased storage capacity

B. Change tracking and version history

C. Faster SQL execution

D. Reduced OneLake usage

Answer: B

Explanation

Source control provides auditability, rollback capabilities, collaboration support, and historical tracking of schema changes.

Question 4

Which Git branching strategy allows developers to work on isolated enhancements?

A. Feature branch

B. Production branch

C. Release branch

D. Main-only development

Answer: A

Explanation

Feature branches enable developers to work independently and merge approved changes later.

Question 5

What is the purpose of schema comparison in a database project?

A. Increase warehouse performance

B. Compare Fabric capacities

C. Identify differences between database environments

D. Create deployment pipelines

Answer: C

Explanation

Schema comparison identifies discrepancies between source and target databases, helping maintain consistency.

Question 6

Which process typically occurs after a developer commits database project changes?

A. Data replication

B. Build validation and testing

C. Spark optimization

D. Capacity scaling

Answer: B

Explanation

CI/CD workflows generally include validation and testing before deployment occurs.

Question 7

Which database object can be managed within a database project?

A. Table

B. View

C. Stored Procedure

D. All of the above

Answer: D

Explanation

Database projects can manage tables, views, stored procedures, functions, schemas, and security objects.

Question 8

What is the primary role of deployment pipelines when used with database projects?

A. Store source code

B. Manage Git repositories

C. Promote changes between environments

D. Execute SQL queries

Answer: C

Explanation

Deployment pipelines move validated changes through development, test, and production environments.

Question 9

A team needs the ability to restore a previous schema version after a failed deployment.

Which capability should they use?

A. Version control rollback

B. Capacity monitoring

C. Spark session recovery

D. Dataflow refresh

Answer: A

Explanation

Source control systems maintain historical versions, enabling rollback to previous states when necessary.

Question 10

What does the term “Database-as-Code” refer to?

A. Storing data in code files

B. Executing SQL through notebooks

C. Converting SQL to Spark code

D. Managing database objects through source-controlled definitions

Answer: D

Explanation

Database-as-Code treats database definitions as managed source code artifacts that can be versioned, reviewed, tested, and deployed through automated processes.

Exam Tip

For the DP-700 exam, remember that database projects are fundamentally about applying software engineering practices to database development. Questions often focus on source control, schema management, CI/CD, deployment consistency, and collaboration. If a scenario involves managing warehouse schemas across multiple environments, tracking changes, enabling rollbacks, or automating deployments, a database project is often a key part of the solution.

Go to the DP-700 Exam Prep Hub main page.