Tag: Quality Assurance

Data Development, Data Governance, Data Quality Assurance, DP-800, Microsoft Certification

Design and implement a testing strategy, including unit tests and integration tests (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
      --> Design and implement a testing strategy, including unit tests and integration tests

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 successful database solution is not simply one that deploys successfully—it must also behave correctly, consistently, securely, and efficiently after deployment. As organizations increasingly adopt DevOps and Continuous Integration/Continuous Deployment (CI/CD) practices, automated database testing has become a critical component of modern database development.

For the DP-800 exam, Microsoft expects candidates to understand how testing fits into SQL Database Projects, Azure DevOps, GitHub Actions, and database deployment pipelines. Candidates should understand the differences between unit testing, integration testing, regression testing, performance testing, and validation testing, as well as how automated testing reduces deployment risk.

Why Database Testing Matters

Database testing helps ensure that:

  • Database objects compile successfully.
  • Business logic returns correct results.
  • Schema changes don’t break existing applications.
  • Stored procedures function correctly.
  • Data integrity is maintained.
  • Performance remains acceptable after changes.
  • Security settings remain intact.
  • Deployment scripts execute successfully.

Without testing, even small schema changes can introduce:

  • Broken stored procedures
  • Invalid foreign keys
  • Data corruption
  • Performance regressions
  • Failed deployments
  • Security vulnerabilities

Testing is therefore an essential component of every CI/CD pipeline.

Database Testing in a CI/CD Pipeline

A typical SQL Database Project pipeline follows this workflow:

Developer writes code
        ↓
Commit to Git repository
        ↓
Continuous Integration (Build)
        ↓
Automated Unit Tests
        ↓
Build DACPAC
        ↓
Deploy to Test Environment
        ↓
Integration Tests
        ↓
Performance Validation
        ↓
User Acceptance Testing (UAT)
        ↓
Production Deployment

Testing occurs at multiple stages to detect issues as early as possible.

Types of Database Testing

Several testing categories appear throughout Microsoft’s documentation.

Testing TypePurpose
Unit TestingTests individual database objects
Integration TestingTests interactions between components
Regression TestingEnsures previous functionality still works
Performance TestingMeasures execution speed and scalability
Load TestingMeasures behavior under heavy workload
Security TestingValidates permissions and security
Smoke TestingBasic validation after deployment
Acceptance TestingConfirms business requirements are met

The DP-800 exam primarily focuses on unit testing and integration testing.

Unit Testing

What is Unit Testing?

A unit test verifies one small, isolated piece of functionality.

Examples include testing:

  • One stored procedure
  • One scalar function
  • One trigger
  • One view
  • One computed column
  • One business rule

A unit test should focus on only one object or behavior.

Characteristics of Good Unit Tests

Good unit tests are:

  • Small
  • Fast
  • Independent
  • Repeatable
  • Automated
  • Deterministic

A unit test should produce the same result every time it runs.

Example

Stored procedure:

CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerID INT
AS
SELECT *
FROM Sales.Orders
WHERE CustomerID = @CustomerID;

Unit test verifies:

  • Valid customer returns rows
  • Invalid customer returns zero rows
  • NULL parameter handled correctly
  • Correct columns returned

Benefits of Unit Testing

Advantages include:

  • Finds bugs early
  • Easier debugging
  • Faster deployments
  • Lower maintenance costs
  • Safer code refactoring
  • Better documentation

Unit Testing Frameworks

Common SQL testing frameworks include:

  • tSQLt
  • SQL Test (Redgate)
  • SSDT Test Framework
  • Azure DevOps automated scripts
  • GitHub Actions test execution

Microsoft commonly demonstrates automated testing using SQL scripts executed within Azure DevOps or GitHub Actions.

tSQLt Overview

tSQLt is an open-source unit testing framework for SQL Server.

It provides:

  • Assertions
  • Test isolation
  • Mock objects
  • Fake tables
  • Automated execution

Example:

EXEC tSQLt.AssertEquals

Although DP-800 is not centered on tSQLt syntax, understanding that SQL unit testing frameworks exist is beneficial.

Integration Testing

What is Integration Testing?

Integration testing verifies that multiple database components work together correctly.

Examples:

  • Stored procedure updates multiple tables
  • Trigger fires correctly
  • Views join tables properly
  • API writes data successfully
  • ETL process loads data correctly
  • Application interacts with SQL database

Unlike unit testing, integration testing validates interactions rather than isolated components.

Example

Customer places an order.

Integration test validates:

Application
      ↓
Stored Procedure
      ↓
Orders Table
      ↓
Inventory Table
      ↓
Audit Table
      ↓
Email Queue

Every component must function correctly.

Differences Between Unit and Integration Testing

Unit TestingIntegration Testing
Tests one objectTests multiple objects
FastSlower
IsolatedEnd-to-end
Few dependenciesMultiple dependencies
Easier debuggingMore complex debugging
Developer-focusedSystem-focused

Regression Testing

Regression testing verifies that new changes have not broken existing functionality.

Example:

Version 1:

Customer search works

Developer adds:

Email search

Regression testing verifies:

  • Customer search still works
  • Existing reports still function
  • Existing APIs still work

Regression tests are especially important before production deployments.

Smoke Testing

Smoke tests perform basic validation after deployment.

Typical smoke tests include:

  • Database accessible
  • Tables exist
  • Stored procedures compile
  • Views execute
  • Security objects exist
  • Basic queries succeed

Smoke tests determine whether further testing should continue.

Performance Testing

Performance testing validates:

  • Query execution time
  • Resource utilization
  • Index efficiency
  • Blocking
  • Deadlocks
  • Response time

Performance testing frequently uses:

  • Query Store
  • Execution Plans
  • DMVs
  • Extended Events

Performance testing should be included before production deployments.

Load Testing

Load testing measures behavior under expected workloads.

Examples:

  • 100 users
  • 1,000 users
  • 10,000 concurrent requests

Metrics include:

  • CPU utilization
  • Memory consumption
  • Wait statistics
  • Response times
  • Throughput

Security Testing

Security testing validates:

  • Authentication
  • Authorization
  • Row-Level Security
  • Dynamic Data Masking
  • Always Encrypted
  • Object permissions
  • Managed Identity access

Examples:

Verify:

SalesUser

cannot access

HR.EmployeeSalary

Test Environments

Testing should occur in multiple environments.

Typical environments:

Development
↓
Build
↓
Testing
↓
Quality Assurance
↓
User Acceptance Testing
↓
Production

Each environment validates progressively more realistic scenarios.

Test Data

Reliable testing requires reliable data.

Test data should be:

  • Predictable
  • Repeatable
  • Isolated
  • Representative
  • Non-production whenever possible

Avoid using sensitive production data unless properly masked.

Database Mocks

Sometimes dependencies should be replaced.

Examples include:

  • Fake tables
  • Mock services
  • Test APIs
  • Sample datasets

Mocking allows tests to run independently.

Test Automation

Automated testing is one of the primary goals of CI/CD.

Benefits include:

  • Faster feedback
  • Consistent execution
  • Reduced human error
  • Repeatability
  • Higher deployment confidence

Automation should execute every time code changes.

Testing in Azure DevOps

Typical Azure DevOps pipeline:

Commit
↓
Build SQL Project
↓
Run Unit Tests
↓
Generate DACPAC
↓
Deploy Test Database
↓
Run Integration Tests
↓
Publish Results
↓
Approve Deployment
↓
Production

Failed tests should stop the deployment pipeline.

Testing in GitHub Actions

GitHub Actions workflows often include:

  • Build SQL Database Project
  • Create DACPAC
  • Deploy temporary database
  • Execute SQL scripts
  • Run automated tests
  • Publish results
  • Clean up environment

This supports fully automated DevOps workflows.

Continuous Testing

Continuous testing means testing occurs automatically throughout the development lifecycle.

Benefits:

  • Earlier defect detection
  • Lower costs
  • Faster releases
  • Improved quality
  • Better developer confidence

Test Coverage

Good test coverage includes:

  • CRUD operations
  • Stored procedures
  • Functions
  • Triggers
  • Views
  • Constraints
  • Security
  • Error handling
  • Transactions
  • Performance

Higher coverage reduces deployment risk.

Best Practices

Microsoft recommends:

  • Automate all repeatable tests.
  • Keep tests independent.
  • Use source control for test scripts.
  • Execute tests during every build.
  • Use representative test data.
  • Separate unit and integration tests.
  • Include regression tests.
  • Test deployment scripts.
  • Fail deployments when tests fail.
  • Review test results regularly.

Common DP-800 Exam Tips

Remember these key distinctions:

TopicKey Point
Unit TestTests one database object
Integration TestTests interaction among components
Regression TestVerifies existing functionality remains intact
Smoke TestBasic deployment validation
Performance TestMeasures speed and scalability
Load TestMeasures behavior under heavy usage
Security TestValidates permissions and protection
CI/CDAutomates builds, tests, and deployments
SQL Database ProjectSupports automated testing before deployment
DACPACDatabase deployment artifact that should be validated through automated testing

Summary

For the DP-800 exam, you should understand how testing strategies improve the reliability of SQL database deployments. Unit tests validate individual database objects, while integration tests verify interactions between multiple components. Automated testing is a fundamental part of CI/CD pipelines using SQL Database Projects, Azure DevOps, and GitHub Actions. A comprehensive testing strategy should also include regression, smoke, performance, load, and security testing. By automating tests and executing them during every build and deployment, organizations can reduce deployment risk, improve database quality, and deliver changes with greater confidence.

Practice Exam Questions

Question 1

A database developer wants to verify that a single stored procedure returns the correct results for various input values without involving other database objects. Which type of testing should be used?

A. Load testing

B. Integration testing

C. Unit testing

D. Regression testing

Answer: C

Explanation:
Unit testing validates a single database object or piece of logic in isolation. It is designed to verify that one stored procedure, function, or trigger behaves correctly under controlled conditions.

Question 2

A CI/CD pipeline deploys a DACPAC to a test database and then executes scripts that verify stored procedures, triggers, and application workflows function together correctly. What type of testing is being performed?

A. Integration testing

B. Smoke testing

C. Performance testing

D. Security testing

Answer: A

Explanation:
Integration testing verifies that multiple components interact correctly. It ensures database objects and applications work together after deployment.

Question 3

What is the primary purpose of regression testing?

A. Measure concurrent user performance

B. Verify that previous functionality still works after changes

C. Test database backups

D. Validate database security permissions

Answer: B

Explanation:
Regression testing ensures that new changes do not introduce defects into existing functionality. It is an important safeguard before production deployments.

Question 4

Which characteristic is considered a best practice for unit tests?

A. They should depend on production data.

B. They should require manual execution.

C. They should test multiple independent business processes simultaneously.

D. They should be repeatable and deterministic.

Answer: D

Explanation:
Good unit tests should produce consistent results every time they run, be independent of external factors, and execute automatically.

Question 5

Why should automated tests be included in every CI/CD pipeline?

A. To eliminate the need for version control

B. To automatically replace database administrators

C. To detect defects early and prevent faulty deployments

D. To remove the need for production monitoring

Answer: C

Explanation:
Automated testing provides immediate feedback on code changes, identifies problems early, and helps prevent unsuccessful deployments.

Question 6

A team wants to verify that a newly deployed database is accessible, key tables exist, and critical stored procedures execute successfully before running more extensive tests. Which testing approach should they use?

A. Regression testing

B. Load testing

C. Smoke testing

D. Unit testing

Answer: C

Explanation:
Smoke testing performs a quick validation that essential functionality is operational before more comprehensive testing begins.

Question 7

Which testing type is specifically intended to measure database behavior under thousands of simultaneous users?

A. Unit testing

B. Load testing

C. Regression testing

D. Static code analysis

Answer: B

Explanation:
Load testing evaluates how well a database performs under expected or peak workloads, measuring metrics such as throughput, response time, and resource utilization.

Question 8

Which statement best describes a unit test?

A. It validates interactions between multiple applications.

B. It verifies production backup procedures.

C. It measures database performance under heavy workloads.

D. It tests a single database object independently.

Answer: D

Explanation:
Unit tests focus on individual components such as stored procedures, functions, or triggers, allowing developers to isolate and troubleshoot defects efficiently.

Question 9

A SQL Database Project build fails because an automated test detects incorrect results from a stored procedure. What should the CI/CD pipeline do?

A. Continue deployment to production.

B. Ignore the test because the build succeeded.

C. Disable future automated testing.

D. Stop the deployment until the issue is corrected.

Answer: D

Explanation:
One of the primary benefits of CI/CD is preventing defective code from reaching later environments. Failed automated tests should stop the deployment pipeline.

Question 10

Why is representative test data important when designing a database testing strategy?

A. It guarantees maximum query performance.

B. It helps ensure tests accurately reflect real-world scenarios while avoiding unnecessary production data exposure.

C. It automatically creates execution plans.

D. It eliminates the need for integration testing.

Answer: B

Explanation:
Representative test data enables realistic testing while reducing the risk of exposing sensitive production information. Well-designed test datasets improve the reliability and usefulness of automated tests.

Go to the DP-800 Exam Prep Hub main page

Business Intelligence, Data Analysis, Data Development, Data Governance, Data Integration, Data Quality Assurance, Data Security, Data Strategy, Data Warehousing, Performance Tuning

Quality Assurance (QA) for Data Projects or Data Applications

This post discusses Quality Assurance (QA) activities for data projects.

What is Quality Assurance (QA)?  Simply put, Quality Assurance, also called QA, Testing or Validation, is about testing an application or solution to ensure that all the stated/promised/expected requirements are met. It is a critically important activity for all software application development or implementations. Data applications are no different. They need to be tested to ensure they work as intended.

QA stands between development and deployment. And QA makes the difference between a delivered product and a high quality delivered product.

There are a number of things to keep in mind when you plan your Quality Assurance activities for data solutions. I present some of them in this post as suggestions, considerations, or prompting questions. The things mentioned here will not apply to all data applications but can be used as a guide or a check.

People / Teams

The number of people and teams involved in a project will vary depending on the size, scope and complexity of the project.

The technical team building the application needs to perform an initial level of validation of the solution.

If there is a Quality Assurance team that performs the validation tasks, then that team will need to perform the “official” validation.

The business analysts and end-users of the application also need to validate. Where possible, work with as many end users as efficiently possible. The more real users you have testing the application, the better the chances of finding issues early.

Where it makes sense, Test IDs that simulate various types of users or groups should be used to help test various usage and security scenarios. This is particularly useful in automated testing.

On large projects where there is a lot to be tested, it is best to break up the testing across multiple people or teams. This will help to prevent testing fatigue and sloppy testing and result in higher quality testing.

Plan ahead to ensure that access for all the relevant users is set up in the testing environments.

Communication

With all the teams and people involved, it is important to have a plan for how they will communicate. Things to consider and have a plan for include:

  • How will teams communicate within? Email, Microsoft Teams, SharePoint, Shared Files, are some options.
  • How will the various teams involved communicate with each other? In other words, how will cross-team communication be handled? As above, Email, Microsoft Teams, SharePoint, Shared Files, are some options.
  • How will issues and status be communicated? Weekly meetings, Status emails or documents, Shared files available on shared spaces are options.
  • How will changes and resolutions be tracked? Files, SDLC applications, Change Management applications are options.
  • How will teams and individuals be notified when they need to perform a task? Manual communication or automated notifications from tools are options.

Data

The most important thing to ensure in data projects is that the data is high quality, particularly the “base” data set. If the base data is incorrect, everything built on top of it will be bad. Of course, the correctness of intermediate and user-facing data is also just as important, but the validation of the base data is critical to achieving the correct data all over.

  • Ensure that table counts, field counts and row counts of key data are correct.
  • Does the data warehouse data match the source data?
  • Test detailed, low level records with small samples of data
  • Test to ensure that the data and the values conform to what is expected. For example, ensuring that there is no data older than 3 years old, or ensuring that there are no account values outside a certain range. The Data Governance Team may become involved in these activities across all projects.

Next in line is the “intermediate” data such as derived metrics, aggregates, specialized subsets, and more. These will also need to be verified.

  • Are the calculated values correct?
  • Are the aggregates correct? Test aggregate data with small, medium and large sets of data
  • Verify metric calculations

Then the user-facing data or data prepared for self-service usage needs to be validated.

  • Does the data on the dashboard match the data in the database?
  • Are the KPIs correctly reflecting the status?

Test the full flow of the data. The validity of the data should be verified at each stage of the data flow – from the source, to the staging, to the final tables in the data warehouse, to aggregates or subsets, to the dashboard.

Take snapshots of key datasets or reports so you can compare results post data migration.

Some additional data prep might be needed in some cases.

  • These include making sure that you have sourced adequate data for testing. For example, if you need to test an annual trend, then it might be best to have at least a year’s worth of data, preferably two.
  • You may need to scramble or redact some data for testing. Often Test data is taken from the Production environment and then scrambled and/or redacted in order to not expose sensitive information.
  • You may need to temporarily load in data for testing. For various reasons, you may need to load some Production data into the QA environment just to test the solution or a particular feature and then remove the data after the testing is complete. While this can be time consuming, sometimes it’s necessary, and it’s good to be aware of the need early and make plans accordingly.

Aesthetics & Representation of Data

Presentation matters. Although the most critical thing is data correctness, how the data is presented is also very important. Good presentation helps with understanding, usability, and adoption. A few things to consider include:

  • Does the application, such as dashboard, look good?  Does it look right? 
  • Are the components laid out properly so that there is no overcrowding?
  • Are the logos, colors and fonts in line with company expectations?
  • Are proper chart options used to display the various types of data and metrics?
  • Is the information provided in a way that users can digest?

Usage

The data application or solution should be user friendly, preferably intuitive or at least have good documentation. The data must be useful to the intended audience, in that, it should help them to understand the information and make good decisions or take sensible actions based on it.

The application should present data in a manner that is effective – easy to access, and easy to understand.

The presentation should satisfy the analytic workflows of the various users. Users should be able to logically step through the application to find information at the appropriate level of detail that they need based on their role.

A few things that affect usability include:

  • Prompts – ensure that all the proper prompts or selections are available to users to slice and filter the data as necessary. And of course, verify that they work.
  • Drill downs and drill throughs – validate that users can drill-down and across data to find the information they need in a simple, logical manner.
  • Easy interrogation of the data – if the application is ad-hoc in nature, validate that users can navigate it or at least verify that the documentation is comprehensive enough for users to follow.

Security

Securing the application and its data so that only authorized users have access to it is critical.

Application security comprises of “authentication”– access to the application, and “authorization” – what a user is authorized to do when he or she accesses the application.

Authorization (what a user is authorized to do within the application) can be broken into “object security” – what objects or features a user has access to, and “data security” – what data elements a user has access to within the various objects or features.

For example, a user has access to an application (authenticated / can log in), and within the application the user has access to (authorized to see and use) 3 of 10 reports (object-level security). The user is not authorized to see the other 7 reports (object-level security) and, therefore, will not have access to them. Now, within the 3 reports that the user has access to, he or she can only see data related to 1 of 5 departments (data-level security).

All object-level and data-level security needs to be validated. This includes negative testing. Not only test to make sure that users have the access they need, but testing should also ensure that users do not have access that they should not have.

  • Data for testing should be scrambled or redacted as appropriate to protect it.
  • Some extremely sensitive data may need to be filtered out entirely.
  • Can all the appropriate users access the application?
  • Are non-authorized users blocked from accessing the application?
  • Can user see the data they should be able to see to perform their jobs?

Performance

Performance of the data solution is important to user efficiency and user adoption. If users cannot get the results they need in a timely manner, they will look elsewhere to get what they need. Even if they have no choice, a poorly performing application will result in wasted time and dollars.

A few things to consider for ensuring quality around performance:

  • Application usage – is the performance acceptable? Do the results get returned in an acceptable time?
  • Data Integration – is the load performance acceptable?
  • Data processing – can the application perform all the processing it needs to do in a reasonable amount of time?
  • Stress Testing – how is performance with many users? How is it with a lot data?
  • How is performance with various selections or with no selections at all?
  • Is ad-hoc usage setup to be flexible but avoid rogue analyses that may cripple the system?
  • Is real-time analysis needed and is the application quick enough?

These items need to be validated and any issues need to be reported to the appropriate teams for performance tuning before the application is released for general usage.

Methodology

Each organization, and even each team within an organization, will have a preferred methodology for application development and change management, including how they perform QA activities.

Some things to consider include:

  • Get QA resources involved in projects early so that they gain an early understanding of the requirements and the solutions to assess and plan how best to test.
  • When appropriate, do not wait until all testing is complete before notifying development teams of issue discovered. By notifying them early, this could make the difference between your project being on-time or late.
  • Create a test plan and test scripts – even if they are high-level.
  • Where possible, execute tasks in an agile, iterative manner.
  • Each environment will have unique rules and guidelines that need to be validated. For example, your application may have a special naming convention, color & font guidelines, special metadata items, and more. You need to validate that these rules and guidelines are followed.
  • Use a checklist to ensure that you validate with consistency from deliverable to deliverable
  • When the solution being developed is replacing an existing system or dataset, use the new and old solutions in parallel to validate the new against the old.
  • Document test results. All testing participants should document what has been tested and the results. This may be as simple as a checkmark or a “Done” status, but may also include things like data entered, screenshots, results, errors, and more.
  • Update the appropriate tracking tools (such as your SDLC or Change Management tools) to document changes and validation. These tools will vary from company to company, but it is best to have a trail of the development, testing, and release to production.
  • For each company and application, there will a specific, unique set of things that will need to be done. It is best if you have a standard test plan or test checklist to help you confirm that you have tested all important aspects and scenarios of the application.

This is not an all-encompassing coverage of Quality Assurance for data solutions, but I hope the article gives you enough information to get started or tips for improving what you currently have in place. You can share your questions, thoughts and input via comments to this post. Thanks for reading!