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
      --> Create and manage reference/static data in source control

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

A database solution consists of more than just tables, views, stored procedures, and security objects. Many applications also depend on reference data (sometimes called lookup data) or static data that rarely changes but is essential for application functionality.

Examples include:

• Country codes
• Currency codes
• Product categories
• Sales territories
• Tax rates
• Department lists
• User roles
• Status codes
• ISO language codes

In modern DevOps practices, this data should be managed alongside the database schema using source control. Keeping reference data under version control ensures that every environment—development, testing, staging, and production—contains the correct data required by the application.

For the DP-800 exam, Microsoft expects candidates to understand how to manage static data within SQL Database Projects and CI/CD pipelines, including deployment strategies, version control practices, and synchronization techniques.

What Is Reference (Static) Data?

Reference data is information that changes infrequently and is used repeatedly by applications to enforce consistency and business rules.

Examples include:

Unlike transactional data, reference data is generally created by administrators rather than users.

Characteristics of Reference Data

Reference data is typically:

• Small in volume
• Read frequently
• Updated infrequently
• Shared across applications
• Required for business logic
• Consistent across environments

Because it changes rarely, it is well suited for storage in source control.

What Is Source Control?

Source control (also called version control) tracks changes to files over time.

Common source control systems include:

• Git
• Azure Repos
• GitHub
• GitLab

Within SQL Database Projects, source control stores:

• Database schema
• Stored procedures
• Views
• Functions
• Security objects
• Deployment scripts
• Reference data scripts

Why Store Reference Data in Source Control?

Managing static data in source control provides several benefits:

• Consistent deployments
• Reproducible environments
• Complete change history
• Easier collaboration
• Simplified rollback
• Automated deployments
• Reduced configuration drift

Without version-controlled reference data, development and production environments can become inconsistent.

Configuration Data vs. Reference Data

Candidates should understand the distinction.

Reference Data

Business information used by applications.

Examples:

• Product categories
• Country codes
• Payment methods

Configuration Data

Controls application behavior.

Examples:

• Feature flags
• Connection settings
• API endpoints
• Retry counts

Configuration data often differs between environments, while reference data should usually remain identical.

Examples of Reference Data

Country table:

CREATE TABLE dbo.Country
(
    CountryCode CHAR(2) PRIMARY KEY,
    CountryName NVARCHAR(100)
);

Static data:

INSERT INTO dbo.Country
VALUES
('US','United States'),
('CA','Canada'),
('MX','Mexico');

This script can be committed to Git and deployed automatically.

Why Not Manually Populate Lookup Tables?

Manual updates introduce problems:

• Human error
• Missing rows
• Environment inconsistencies
• Forgotten updates
• Difficult auditing

Automated deployment eliminates these risks.

Reference Data in SQL Database Projects

SQL Database Projects primarily manage schema objects.

Reference data is commonly deployed using:

• Post-deployment scripts
• SQLCMD scripts
• Seed scripts
• Data synchronization scripts

The database schema and required reference data become part of one deployment process.

Post-Deployment Scripts

A post-deployment script runs after the DACPAC deployment completes.

Typical uses include:

• Insert lookup values
• Seed tables
• Create administrative users
• Initialize configuration

Example:

:r .\SeedData\Countries.sql
:r .\SeedData\OrderStatus.sql
:r .\SeedData\Departments.sql

Each referenced script inserts the required static data.

Organizing Seed Data

A common project structure:

DatabaseProject
│
├── Tables
├── Views
├── Procedures
├── Security
├── PostDeployment
│
├── SeedData
│      Countries.sql
│      States.sql
│      PaymentTypes.sql
│      StatusCodes.sql
│
└── Scripts

Keeping seed data in dedicated folders improves maintainability.

Idempotent Seed Scripts

A deployment may execute multiple times.

Therefore, seed scripts should be idempotent, meaning they can run repeatedly without producing duplicate data.

Instead of:

INSERT INTO Status
VALUES ('Pending');

Use:

IF NOT EXISTS
(
    SELECT 1
    FROM dbo.Status
    WHERE StatusName='Pending'
)
INSERT INTO dbo.Status(StatusName)
VALUES ('Pending');

Running this script multiple times inserts only one row.

Using MERGE for Synchronization

Another common approach is the MERGE statement.

Example:

MERGE dbo.Status AS Target
USING
(
VALUES
('Pending'),
('Shipped'),
('Delivered')
) AS Source(StatusName)
ON Target.StatusName=Source.StatusName
WHEN NOT MATCHED THEN
INSERT(StatusName)
VALUES(Source.StatusName);

MERGE synchronizes reference data without creating duplicates.

Exam Tip: While MERGE is powerful, it has historically had edge cases in SQL Server. Many organizations still use it for static data synchronization, but others prefer separate INSERT, UPDATE, and DELETE statements for greater predictability. Understand both approaches for the exam.

Updating Existing Reference Data

Sometimes lookup values change.

Example:

UPDATE dbo.Country
SET CountryName='United States of America'
WHERE CountryCode='US';

These changes should be committed to source control so all environments receive the update.

Removing Reference Data

Occasionally obsolete values must be removed.

Example:

DELETE
FROM dbo.Status
WHERE StatusName='Obsolete';

Deletion scripts should be carefully reviewed to avoid breaking foreign key relationships.

Versioning Static Data

Reference data evolves over time.

Example:

Version 1

Pending
Shipped
Delivered

Version 2

Pending
Processing
Shipped
Delivered
Cancelled

Git records exactly when each change occurred.

Source Control Workflow

Typical workflow:

Developer updates lookup table
↓
Commit to Git
↓
Pull Request
↓
Code Review
↓
Merge
↓
CI Build
↓
Deploy to Test
↓
Validate
↓
Deploy to Production

This ensures every environment receives the same approved changes.

Reference Data and CI/CD

During deployment:

Build SQL Project
↓
Create DACPAC
↓
Deploy Schema
↓
Run Post-Deployment Scripts
↓
Insert Reference Data
↓
Run Automated Tests
↓
Publish

Reference data becomes part of the deployment pipeline.

Environment Consistency

One major objective of CI/CD is ensuring environments remain synchronized.

For example:

Development

Testing

Production

All environments should contain identical lookup values unless environment-specific configuration is intentionally required.

Reference Data vs. Transactional Data

Examples of transactional data include:

• Orders
• Customers
• Invoices
• Payments
• Audit logs

Transactional data should not be committed to Git.

Handling Sensitive Data

Reference data should generally not contain:

• Passwords
• API keys
• Secrets
• Tokens
• Personally identifiable information (PII)

Secrets should instead be stored in secure solutions such as:

• Azure Key Vault
• GitHub Secrets
• Azure DevOps Library
• Environment variables

Best Practices

Microsoft recommends:

• Store lookup data in source control.
• Keep seed scripts idempotent.
• Separate schema from reference data.
• Use post-deployment scripts.
• Automate deployments.
• Review reference data changes through pull requests.
• Avoid manual production updates.
• Keep environments synchronized.
• Never store secrets in source control.
• Test deployment scripts before production.

Common DP-800 Exam Tips

Remember these key points:

Summary

Reference (static) data is essential to many database applications and should be managed with the same discipline as database schema. By storing lookup data scripts in source control, developers can ensure consistent deployments across all environments, maintain a complete audit history of changes, and automate data seeding as part of CI/CD pipelines. SQL Database Projects commonly use post-deployment scripts, idempotent SQL, and MERGE statements to deploy and synchronize static data safely. Understanding how reference data differs from transactional and configuration data—and how to manage it securely—is an important objective for the DP-800 certification exam.

Practice Exam Questions

Question 1

A development team wants every deployment to automatically populate the Country lookup table with approved values. What is the recommended approach when using SQL Database Projects?

A. Manually insert the rows after each deployment.

B. Store the data in a post-deployment script under source control.

C. Copy the table directly from the production database.

D. Require application users to populate the table during startup.

Answer: B

Explanation:
Post-deployment scripts are the recommended mechanism for deploying reference data with SQL Database Projects. Keeping these scripts in source control ensures consistency across all environments.

Question 2

Which type of data is most appropriate to store in source control along with a SQL Database Project?

A. Customer orders

B. Audit logs

C. Country codes

D. User transaction history

Answer: C

Explanation:
Country codes are classic reference data that changes infrequently and should be version-controlled. Transactional data such as orders and audit logs should not be stored in source control.

Question 3

Why should reference data deployment scripts be idempotent?

A. To improve query performance.

B. To ensure they can be executed repeatedly without creating duplicate data.

C. To encrypt lookup tables.

D. To automatically generate indexes.

Answer: B

Explanation:
Idempotent scripts produce the same result regardless of how many times they are executed, preventing duplicate rows during repeated deployments.

Question 4

A SQL Database Project deploys successfully, but required lookup values are missing from several tables. Which deployment component was most likely omitted?

A. Database backups

B. Execution plans

C. Post-deployment scripts

D. Statistics updates

Answer: C

Explanation:
DACPAC deployments primarily create schema objects. Reference data is typically inserted through post-deployment scripts.

Question 5

Which statement best describes reference data?

A. It is typically small, shared across environments, and changes infrequently.

B. It changes frequently throughout the day.

C. It consists primarily of user-generated transactional records.

D. It should never be stored in Git.

Answer: A

Explanation:
Reference data is stable business information, such as lookup values, that is commonly deployed to every environment through automated processes.

Question 6

Which SQL statement is commonly used to synchronize source and target reference data during deployment?

A. TRUNCATE

B. ALTER

C. EXECUTE

D. MERGE

Answer: D

Explanation:
MERGE compares source and target data, allowing inserts, updates, and optional deletes within a single statement, making it useful for synchronizing static data.

Question 7

Which item should not typically be stored in source control with reference data scripts?

A. Country lookup values

B. Department codes

C. API keys and passwords

D. Payment status values

Answer: C

Explanation:
Sensitive information such as API keys, passwords, and secrets should be stored securely in services like Azure Key Vault or GitHub Secrets rather than in source control.

Question 8

What is the primary benefit of storing reference data scripts in Git?

A. They provide version history, collaboration, and consistent deployments.

B. They eliminate the need for backups.

C. They reduce database storage requirements.

D. They automatically improve query performance.

Answer: A

Explanation:
Source control provides change tracking, collaboration, auditing, rollback capabilities, and consistent deployments across environments.

Question 9

Which data type would not normally be considered reference data?

A. Order status values

B. Customer invoices

C. Currency codes

D. Sales regions

Answer: B

Explanation:
Customer invoices are transactional records generated during business operations. They should not be managed as static reference data.

Question 10

A development team wants development, testing, and production environments to contain identical lookup values after every deployment. Which DevOps practice best supports this goal?

A. Manually editing lookup tables after deployment.

B. Importing production backups into every environment.

C. Storing reference data scripts in source control and executing them automatically during CI/CD.

D. Allowing each environment to maintain its own independent lookup values.

Answer: C

Explanation:
Automating the deployment of version-controlled reference data ensures that all environments remain synchronized and eliminates manual configuration drift.

Go to the DP-800 Exam Prep Hub main page