Tag: Microsoft Certification

azure, Databases, DP-800, Microsoft Certification, SQL August 1, 2026August 1, 2026

Handle changes by using change event streaming (CES), change data capture (CDC), Change Tracking, Azure Functions with SQL trigger binding, or Azure Logic Apps (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%)
   --> Integrate SQL solutions with Azure services
      --> Handle changes by using change event streaming (CES), change data capture (CDC), Change Tracking, Azure Functions with SQL trigger binding, or Azure Logic Apps

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

Modern applications rarely operate in isolation. A single database update often needs to trigger downstream actions such as updating search indexes, synchronizing data warehouses, refreshing caches, sending notifications, invoking APIs, or triggering AI pipelines.

Microsoft SQL Server and Azure SQL provide several mechanisms to detect and react to data changes. The DP-800 exam expects candidates to understand the capabilities, strengths, limitations, and appropriate use cases for each technology.

The primary technologies include:

Change Data Capture (CDC)
Change Tracking
Change Event Streaming (CES)
Azure Functions with SQL Trigger Binding
Azure Logic Apps

Understanding when and why to use each technology is more important than memorizing implementation details.

Why Change Detection Matters

Applications often need to know when data changes occur without continuously querying every table.

Examples include:

Synchronizing CRM and ERP systems
Triggering AI workflows after new customer data arrives
Updating recommendation engines
Refreshing search indexes
Sending order confirmation emails
Replicating data into Microsoft Fabric
Populating analytical data lakes
Updating Power BI semantic models

Without an efficient change detection mechanism, applications would have to repeatedly scan entire tables, resulting in:

Poor performance
Increased costs
Higher latency
Unnecessary resource utilization

Overview of Available Technologies

Technology	Detects Inserts	Updates	Deletes	Provides Changed Values	Typical Use
Change Tracking	Yes	Yes	Yes	No	Lightweight synchronization
Change Data Capture	Yes	Yes	Yes	Yes	ETL and replication
Change Event Streaming	Yes	Yes	Yes	Event stream	Event-driven architectures
Azure Functions SQL Trigger	Yes	Yes	Yes	Current row	Serverless processing
Azure Logic Apps	Yes	Yes	Yes	Depends on connector	Workflow automation

Change Data Capture (CDC)

What is CDC?

Change Data Capture records every data modification that occurs within selected database tables.

Unlike Change Tracking, CDC stores:

The type of operation
Before and after values (where applicable)
Transaction information
Log Sequence Numbers (LSNs)
Timestamps

CDC reads changes directly from the SQL Server transaction log instead of requiring application modifications.

How CDC Works

User modifies data.
SQL writes changes to the transaction log.
CDC captures the changes.
Changes are written into CDC system tables.
Applications or ETL tools read the captured changes.

			
Application
      │
      ▼
SQL Table
      │
Transaction Log
      │
      ▼
CDC Capture Process
      │
      ▼
CDC Change Tables
      │
      ▼
ETL / Azure Data Factory / Fabric

		

Information Stored by CDC

For every change, CDC stores:

Insert
Update
Delete
Transaction sequence
Changed columns
Original values
New values
Commit time
Log sequence number

This provides a complete history of modifications.

Advantages of CDC

Minimal application changes

Applications continue performing normal INSERT, UPDATE, and DELETE operations.

Incremental processing

Instead of processing millions of rows:

			
Yesterday:
10 million rows
Today:
Only 1,250 rows changed
CDC processes only 1,250 rows.

		

This dramatically improves ETL performance.

Supports Historical Analysis

CDC retains detailed change history.

Example:

Customer Name

Original:

John Smith

Updated:

John A. Smith

CDC preserves both versions.

Common CDC Use Cases

Azure Data Factory incremental loads
Microsoft Fabric ingestion
Data warehouse updates
Database replication
AI training pipelines
Audit solutions
Event publishing
Synchronizing microservices

Limitations

CDC:

Uses additional storage
Requires SQL Agent jobs (SQL Server)
Introduces some overhead
Retention must be managed
Generates additional transaction log activity

Change Tracking

What is Change Tracking?

Change Tracking is a lightweight feature that records which rows have changed, but does not store the actual changed values.

Instead, it stores metadata indicating:

Row changed
Row deleted
Version number

Applications retrieve the latest row directly from the table.

How Change Tracking Works

Instead of saving old values:

CustomerID 101 changed.

The application retrieves:

			
SELECT *
FROM Customers
WHERE CustomerID = 101

Only the current version is available.

Advantages

Very lightweight.

Minimal storage.

Minimal performance impact.

Simple synchronization.

Fast processing.

Limitations

Cannot determine:

Old value

↓

New value

Only knows:

Row changed

No historical audit.

No before-and-after comparison.

Best Use Cases

Mobile synchronization

Offline applications

Client synchronization

Web applications

Caching

Incremental refresh

Applications only needing current data

CDC vs Change Tracking

Feature	CDC	Change Tracking
Detect Inserts	Yes	Yes
Detect Updates	Yes	Yes
Detect Deletes	Yes	Yes
Stores Old Values	Yes	No
Stores New Values	Yes	No
Historical Data	Yes	No
Storage Usage	Higher	Lower
ETL Friendly	Excellent	Limited
Synchronization	Good	Excellent
Auditing	Excellent	Poor

Choosing Between CDC and Change Tracking

Choose CDC when:

Building ETL pipelines
Loading data warehouses
Creating audit systems
Tracking complete history
AI model retraining
Replication

Choose Change Tracking when:

Synchronizing mobile devices
Synchronizing applications
Detecting row changes only
Performance is critical
History is unnecessary

Change Event Streaming (CES)

What is Change Event Streaming?

Change Event Streaming is an event-driven approach that publishes database changes as events immediately after they occur.

Instead of applications polling for changes:

			
Did anything change?
Did anything change?
Did anything change?

The database immediately emits an event.

Event-Driven Architecture

			
INSERT Order
      │
      ▼
Database
      │
      ▼
Event Published
      │
 ┌────┼────┐
 ▼    ▼    ▼
Function
Logic App
Service Bus

		

One database change can notify many downstream services simultaneously.

Advantages

Near real-time processing

Low latency

Highly scalable

Excellent for cloud-native applications

Supports asynchronous processing

Works well with event hubs and messaging systems

Common Scenarios

Order processing

Inventory updates

Recommendation engines

AI pipelines

Search indexing

Notifications

Microservices

IoT

Streaming analytics

Benefits over Polling

Polling example:

Check database every minute

Potential issues:

Delayed processing
Unnecessary database queries
Higher compute costs

Event streaming:

			
Change occurs
↓
Immediate notification

Much more efficient.

Azure Functions with SQL Trigger Binding

Overview

Azure Functions provide a serverless compute platform capable of automatically executing code when database changes occur.

SQL Trigger Binding enables Azure Functions to react to SQL data modifications without requiring custom polling logic.

Typical workflow:

			
Database Change
↓
SQL Trigger
↓
Azure Function
↓
Business Logic

		

Common Scenarios

Automatically:

Send emails
Generate invoices
Update search indexes
Invoke AI models
Call REST APIs
Update Cosmos DB
Write to Azure Storage
Publish Service Bus messages

Benefits

Serverless

Automatic scaling

Pay only for executions

Minimal infrastructure management

Easy integration with Azure services

Supports event-driven architectures

Example Scenario

A customer places an order.

INSERT Orders

The SQL trigger starts an Azure Function.

The function:

Validates inventory
Sends confirmation email
Updates recommendation engine
Notifies shipping
Publishes event

No manual polling required.

Azure Logic Apps

What Are Logic Apps?

Azure Logic Apps are low-code workflow automation services that integrate SQL databases with hundreds of Microsoft and third-party services.

Rather than writing custom code, workflows are built visually.

Example:

			
SQL Row Updated
↓
Logic App
↓
Teams Notification
↓
Outlook Email
↓
SharePoint Update
↓
CRM Update

		

Common SQL Integrations

SQL Server

Azure SQL Database

Microsoft Dataverse

Dynamics 365

Salesforce

Microsoft Teams

SharePoint

Azure Storage

Azure Service Bus

Azure Event Grid

Power Automate

Typical Workflow

			
Customer Created
↓
Logic App
↓
Create CRM Record
↓
Send Welcome Email
↓
Create Help Desk Ticket
↓
Notify Sales Team

		

Advantages

Low-code

Rapid development

Hundreds of connectors

Visual designer

Built-in retry policies

Error handling

Scheduling

Monitoring

Enterprise integration

Limitations

Logic Apps are ideal for orchestration and workflow automation but are not intended for high-throughput transactional processing where custom code or event streaming solutions may provide better scalability and lower latency.

Choosing the Right Technology

Requirement	Recommended Solution
Incremental ETL	CDC
Data Warehouse Loading	CDC
Audit History	CDC
Mobile Sync	Change Tracking
Cache Refresh	Change Tracking
Event-Driven Processing	Change Event Streaming
Serverless Business Logic	Azure Functions SQL Trigger
Workflow Automation	Azure Logic Apps
AI Pipeline Trigger	Azure Functions or CES
Multi-System Integration	Logic Apps

Best Practices

Enable Only What You Need

Enable CDC or Change Tracking only on tables that require change detection.

Monitor Storage

CDC tables can grow quickly.

Implement retention policies and cleanup jobs.

Prefer Event-Driven Architectures

Avoid continuous polling whenever possible.

Use:

CES
Azure Functions
Event Grid
Service Bus

for scalable cloud-native applications.

Separate Operational and Analytical Workloads

Use CDC to move transactional data into analytical platforms instead of querying production systems directly.

Secure Integration Endpoints

Protect Azure Functions and Logic Apps using:

Microsoft Entra ID
Managed identities
Azure Key Vault
Least privilege access
Network restrictions where appropriate

Monitor Reliability

Track:

Failed executions
Retry attempts
Dead-letter queues
Function failures
Logic App run history
Event delivery failures

DP-800 Exam Tips

Remember these common exam distinctions:

CDC records complete data changes, including inserted, updated, and deleted values, making it ideal for ETL, auditing, and replication.
Change Tracking records only that a row changed, making it a lightweight solution for synchronization scenarios.
Change Event Streaming supports near real-time, event-driven architectures by publishing change events to downstream consumers.
Azure Functions with SQL Trigger Binding are best when database changes should execute custom serverless code automatically.
Azure Logic Apps are the preferred choice for orchestrating business workflows and integrating SQL databases with Azure and third-party services through low-code connectors.
When selecting a technology, evaluate latency requirements, scalability, historical tracking needs, operational overhead, and integration requirements rather than choosing a single solution for every scenario.

Summary

Modern SQL applications extend well beyond traditional databases, serving as event sources for cloud-native architectures, AI pipelines, analytics platforms, and business workflows. Microsoft provides several complementary technologies to detect and process database changes, each optimized for different scenarios.

For the DP-800 exam, you should understand not only how these technologies work, but also when to choose one over another. CDC excels at incremental ETL and auditing, Change Tracking offers lightweight synchronization, Change Event Streaming enables real-time event-driven systems, Azure Functions execute custom business logic in response to changes, and Azure Logic Apps simplify workflow automation across enterprise services.

A solid understanding of these tools will help you design scalable, maintainable, and performant AI-enabled database solutions in Azure.

Practice Exam Questions

Question 1

A company loads data from an Azure SQL Database into a Microsoft Fabric warehouse every hour. The ETL process should retrieve only rows that have changed since the previous load, including the previous and new values of updated rows.

Which technology should you recommend?

A. Change Tracking

B. Change Data Capture (CDC)

C. Azure Logic Apps

D. Azure Functions with SQL Trigger Binding

Correct Answer: B

Explanation

CDC is specifically designed for incremental data movement scenarios. It captures inserts, updates, and deletes directly from the transaction log and stores detailed information about each change, including before and after values where applicable.

Why the other options are incorrect:

A: Change Tracking identifies changed rows but does not store previous values.
C: Logic Apps orchestrate workflows but do not capture database changes.
D: Azure Functions respond to events but are not intended to maintain historical change data for ETL.

Question 2

A mobile application periodically synchronizes customer records with an Azure SQL Database. The application only needs to know which rows have changed since the last synchronization and does not require historical values.

Which feature is most appropriate?

A. Change Event Streaming

B. Azure Functions SQL Trigger

C. Change Tracking

D. CDC

Correct Answer: C

Explanation

Change Tracking is optimized for synchronization scenarios. It records that rows have changed while minimizing storage and processing overhead.

Why the other options are incorrect:

A: CES is designed for event-driven architectures.
B: Azure Functions execute custom code rather than maintaining synchronization metadata.
D: CDC stores detailed change history, which is unnecessary here.

Question 3

An online retailer wants every new order inserted into the Orders table to immediately trigger inventory updates, shipping notifications, and fraud detection.

Which solution best supports this requirement?

A. Scheduled polling queries

B. Change Tracking

C. Change Event Streaming (CES)

D. Nightly ETL jobs

Correct Answer: C

Explanation

CES enables near real-time event publishing whenever database changes occur. Multiple downstream systems can subscribe to the same event without repeatedly querying the database.

Why the other options are incorrect:

A: Polling introduces unnecessary latency and database load.
B: Change Tracking is intended for synchronization rather than event processing.
D: Nightly ETL introduces unacceptable delays.

Question 4

A database update should automatically execute custom C# code that calls several REST APIs and writes audit information to Azure Storage.

Which Azure service should you recommend?

A. Azure Functions with SQL Trigger Binding

B. CDC

C. Change Tracking

D. SQL Agent Job

Correct Answer: A

Explanation

Azure Functions with SQL Trigger Binding automatically execute custom code when qualifying database changes occur, making them ideal for serverless business logic.

Why the other options are incorrect:

B: CDC records changes but does not execute code.
C: Change Tracking simply records row modifications.
D: SQL Agent jobs rely on scheduled execution rather than event-driven processing.

Question 5

Which statement correctly compares Change Tracking and Change Data Capture?

A. CDC captures complete change history while Change Tracking records only that rows changed.

B. Change Tracking captures previous values while CDC does not.

C. Both features store identical information.

D. CDC only tracks INSERT operations.

Correct Answer: A

Explanation

CDC stores detailed information about every change, including inserts, updates, deletes, timestamps, and transaction metadata. Change Tracking only identifies which rows have changed.

The remaining options are incorrect because they reverse the capabilities or incorrectly describe CDC.

Question 6

A business analyst wants to automate the following workflow without writing custom code:

Detect a new customer record.
Send an Outlook email.
Post a Microsoft Teams notification.
Update a SharePoint list.

Which solution is the best choice?

A. CDC

B. Azure Logic Apps

C. Change Tracking

D. SQL CLR

Correct Answer: B

Explanation

Azure Logic Apps provide low-code workflow automation with hundreds of built-in connectors, making them ideal for orchestrating business processes across Microsoft services.

Why the other options are incorrect:

A: CDC captures changes but does not automate workflows.
C: Change Tracking only records modified rows.
D: SQL CLR requires custom coding and is not intended for cloud workflow automation.

Question 7

A development team currently polls the database every minute to determine whether new records have been inserted.

What is the primary disadvantage of this design?

A. It reduces database normalization.

B. It prevents indexing.

C. It increases transaction isolation.

D. It generates unnecessary database workload and introduces latency.

Correct Answer: D

Explanation

Polling repeatedly queries the database even when no changes exist, increasing resource consumption while delaying event processing.

Event-driven solutions such as CES or Azure Functions eliminate this inefficiency.

Question 8

Which technology is most appropriate when an organization must maintain a complete historical record of all row changes for regulatory auditing?

A. Azure Logic Apps

B. Change Tracking

C. Change Data Capture

D. Azure Functions

Correct Answer: C

Explanation

CDC preserves detailed information about inserts, updates, deletes, transaction sequence numbers, and timestamps, making it ideal for compliance and auditing.

The other technologies either automate workflows or identify changes without preserving historical values.

Question 9

Which feature is specifically intended to minimize synchronization overhead by storing only metadata about changed rows?

A. Azure Functions SQL Trigger

B. Change Tracking

C. Change Event Streaming

D. Azure Event Grid

Correct Answer: B

Explanation

Change Tracking records lightweight metadata that indicates which rows have changed, allowing applications to retrieve only the latest row versions.

The other options serve different purposes:

Azure Functions execute code.
CES publishes events.
Event Grid distributes events but does not track database modifications.

Question 10

A solution architect is selecting a technology for an event-driven microservices architecture. Multiple independent services must react immediately whenever product inventory changes.

Which solution best satisfies this requirement?

A. Nightly ETL processing

B. Change Tracking

C. Database polling every five minutes

D. Change Event Streaming (CES)

Correct Answer: D

Explanation

CES is designed for event-driven systems where multiple subscribers consume database change events in near real time. It minimizes latency and reduces unnecessary database queries.

Why the other options are incorrect:

A: Nightly processing is far too slow.
B: Change Tracking is intended for synchronization rather than event broadcasting.
C: Polling introduces unnecessary workload and delays.

Exam Tips

For the DP-800 exam, remember these key distinctions:

Change Data Capture (CDC) is best for incremental ETL, auditing, replication, and historical change tracking.
Change Tracking is designed for lightweight synchronization when only the fact that a row changed is needed.
Change Event Streaming (CES) enables near real-time event-driven architectures by publishing database changes to downstream consumers.
Azure Functions with SQL Trigger Binding are ideal for executing custom serverless code in response to database changes.
Azure Logic Apps provide low-code workflow automation for integrating Azure SQL with Microsoft and third-party services.
On the exam, Microsoft often presents multiple technologies that could work. Choose the one that best aligns with the business requirement, considering factors such as latency, historical tracking, automation, scalability, and operational overhead, rather than selecting the most feature-rich option.

Go to the DP-800 Exam Prep Hub main page

AI, Azure AI, Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Evaluate external models, including multimodal, multilanguage, sizes, and structured output (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:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement models and embeddings
      --> Evaluate external models, including multimodal, multilanguage, sizes, and structured output

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

One of the most important responsibilities of a SQL AI Developer is selecting the appropriate AI model for a given business problem. Microsoft SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure AI services increasingly integrate with external Large Language Models (LLMs) and embedding models to provide intelligent capabilities such as natural language querying, document summarization, semantic search, recommendation engines, and Retrieval-Augmented Generation (RAG).

Not every model is suitable for every workload. Larger models generally provide better reasoning but incur higher costs and latency. Smaller models offer faster responses and lower costs but may lack advanced reasoning capabilities. Some models support images and audio (multimodal), while others specialize in text or code. Additionally, many enterprise applications require structured outputs such as JSON rather than free-form text.

For the DP-800 exam, candidates should understand how to evaluate external models based on business requirements, performance, cost, scalability, and AI capabilities.

What Are External Models?

An external model is an AI model that runs outside the database engine and is accessed through an API or AI service.

Examples include:

Azure OpenAI models
Azure AI Foundry-hosted models
Open-source models hosted on Azure AI Foundry or Kubernetes
Other cloud-hosted foundation models exposed through REST APIs

Instead of performing AI inference inside SQL Server, the application or database calls an external service.

Example architecture:

			
Application
      │
      ▼
Azure SQL Database
      │
      ▼
Azure OpenAI Service
      │
      ▼
AI Model
      │
      ▼
Generated Response

		

This approach allows SQL-based applications to leverage continuously improving AI models without modifying the database engine.

Factors When Evaluating External Models

Several characteristics should be considered before selecting a model.

These include:

Accuracy
Reasoning capability
Response quality
Cost
Latency
Throughput
Context window size
Structured output support
Multilingual capability
Multimodal capability
Security and compliance
Availability
Scalability

Selecting the right model is often a balance between these factors rather than maximizing any single characteristic.

Evaluating Multimodal Models

What Is a Multimodal Model?

A multimodal model can process multiple types of input rather than only text.

Common input types include:

Text
Images
Documents
Charts
Audio
Video (supported by some models)

Example:

A customer uploads:

Invoice PDF
Photograph of damaged goods
Written description

A multimodal model can analyze all three inputs together.

Business Scenarios

Multimodal models are useful for:

Document analysis
Invoice processing
Insurance claims
Medical imaging
Manufacturing quality inspections
Product recognition
OCR-enhanced workflows
Diagram interpretation

Example:

Instead of asking:

“Describe this invoice.”

The application uploads the invoice itself.

The model extracts:

Vendor
Invoice number
Total
Purchase date
Line items

Advantages

Multimodal models:

Reduce preprocessing
Improve accuracy
Handle real-world data
Simplify AI workflows
Support richer user experiences

Limitations

They typically:

Cost more
Require more compute resources
Have higher latency
Process larger payloads
May not be necessary for text-only applications

Evaluating Multilingual Models

Many enterprise applications serve users around the world.

A multilingual model understands and generates responses in multiple languages without requiring translation.

Example languages include:

English
Spanish
French
German
Portuguese
Japanese
Chinese
Korean
Arabic

Example

Customer question:

Spanish:

¿Cuál es el estado de mi pedido?

The AI responds correctly in Spanish.

Business Benefits

Multilingual models:

Improve customer experience
Eliminate translation pipelines
Simplify global deployments
Maintain conversational context across languages
Reduce development complexity

Evaluation Criteria

When comparing multilingual models, evaluate:

Number of supported languages
Translation quality
Cultural understanding
Domain-specific terminology
Consistency across languages
Response quality

Common Use Cases

Global customer support
International e-commerce
Government services
Travel applications
Healthcare portals
Financial institutions

Evaluating Model Size

Model size generally refers to the relative complexity and capability of an AI model. While parameter counts are not always publicly disclosed for commercial models, larger models typically provide stronger reasoning at the cost of increased compute requirements.

Generally:

Small model

Faster
Lower cost
Lower latency

Large model

Better reasoning
Better code generation
Better summarization
Higher cost
Higher latency

Small Models

Ideal for:

Chatbots
Classification
Data extraction
Intent detection
Basic summarization

Advantages:

Fast responses
Low operational cost
High throughput
Efficient scaling

Medium Models

Good balance between:

Performance
Cost
Accuracy

Typical uses:

Customer support
SQL generation
Business assistants
Document summarization

Large Models

Best for:

Complex reasoning
Long documents
Advanced coding
RAG
Planning
Agentic AI

Trade-offs include:

Higher inference costs
Greater latency
Increased resource consumption

Latency vs. Accuracy

Every AI solution involves balancing response speed and output quality.

Example:

Customer chatbot

Acceptable latency:

2–3 seconds

Scientific research assistant

Acceptable latency:

10–20 seconds

because answer quality matters more than speed.

Trade-Off Example

Requirement	Preferred Model
Fast API responses	Smaller model
High-quality reasoning	Larger model
Thousands of concurrent users	Smaller or medium model
Legal document analysis	Larger model
AI coding assistant	Larger model
FAQ chatbot	Smaller model

Context Window Size

The context window defines how much information the model can process in a single request.

A larger context window allows the model to consider more text simultaneously.

Examples include:

Long contracts
Large knowledge bases
Entire manuals
Meeting transcripts
Large SQL schemas

Benefits

Larger context windows reduce the need to split documents into smaller chunks and help preserve context across lengthy inputs.

Limitations

Larger contexts generally:

Increase processing time
Increase inference cost
Consume more tokens

Applications should include only relevant information rather than maximizing context size unnecessarily.

Structured Output

Many enterprise applications require machine-readable responses instead of conversational text.

Example:

Instead of:

“The customer’s order total is $425 and ships tomorrow.”

Return:

			
{
  "customer":"John Smith",
  "orderTotal":425,
  "shipDate":"2026-07-29"
}

		

Structured output allows applications to parse responses reliably.

Why Structured Output Matters

Applications can:

Deserialize JSON
Populate SQL tables
Call stored procedures
Trigger workflows
Validate data
Build dashboards

without performing fragile text parsing.

Common Structured Formats

JSON
JSON arrays
Objects
Lists
Tables
XML (less common)
Markdown tables (for presentation)

JSON remains the most common structured format for modern AI integrations.

Function Calling and Tool Use

Many modern models support function calling (also called tool calling), where the model requests that the application invoke predefined functions or APIs instead of generating all information directly.

Example workflow:

			
User
  │
  ▼
LLM
  │
Calls:
GetCustomerOrders()
  │
Application
  │
SQL Database
  │
Results
  │
LLM
  │
Final Answer

		

This approach improves accuracy by combining model reasoning with authoritative business data.

Cost Considerations

AI model selection has a direct impact on operational cost.

Factors affecting cost include:

Model complexity
Input tokens
Output tokens
Images processed
Audio processed
Request volume
Concurrency
Context window size

A higher-capability model should only be selected when its additional reasoning or multimodal features provide measurable business value.

Benchmarking Models

Before deploying an external model into production, evaluate it against representative workloads.

Typical metrics include:

Response accuracy
Hallucination rate
Latency
Cost per request
Throughput
Reliability
Structured output validity
Multilingual quality
Safety and policy compliance

Use realistic prompts and datasets that reflect production scenarios.

Security and Responsible AI

When integrating external models with SQL-based applications:

Protect sensitive data.
Apply the principle of least privilege.
Use managed identities where possible.
Store secrets securely (for example, in Azure Key Vault).
Validate AI-generated outputs before acting on them.
Avoid sending unnecessary personally identifiable information (PII) to external services.
Monitor prompts and responses for safety, quality, and compliance.

Azure OpenAI Model Selection Guidance

Although Microsoft’s available models evolve over time, the evaluation process remains consistent.

When choosing a model, consider:

Does the workload require multimodal input?
Is multilingual support necessary?
What response latency is acceptable?
How much reasoning capability is required?
Is structured JSON output needed?
Will the model participate in a RAG workflow?
What are the expected request volumes?
What is the available budget?

The best model is the one that satisfies the business requirements while meeting performance, cost, and governance objectives.

Best Practices

Match model capability to business requirements.
Avoid selecting the largest model unless its advanced capabilities are needed.
Use structured outputs whenever applications consume AI responses programmatically.
Benchmark multiple models using representative production scenarios.
Minimize token usage to reduce costs and improve response times.
Use multimodal models only when image, audio, or document understanding is required.
Validate generated content before updating databases or executing business processes.
Monitor quality, latency, and cost continuously after deployment.

DP-800 Exam Tips

Remember these key distinctions for the exam:

Multimodal models process multiple input types, such as text and images.
Multilingual models understand and generate content in multiple languages without requiring separate translation services.
Smaller models typically provide lower latency and lower cost, making them suitable for high-volume, straightforward tasks.
Larger models generally provide stronger reasoning, summarization, and code generation but require more compute resources and incur higher costs.
Structured outputs, particularly JSON, are preferred when AI responses must be consumed by applications, APIs, or SQL processes.
Function calling allows models to invoke trusted business logic or database operations instead of relying solely on generated responses.
Model selection should always balance accuracy, latency, scalability, cost, security, and maintainability.

Summary

Selecting an external AI model is one of the most important architectural decisions in AI-enabled database solutions. The ideal model depends on the workload, whether that involves multilingual customer support, multimodal document analysis, structured data extraction, or advanced reasoning over enterprise data.

For the DP-800 exam, focus on understanding the trade-offs among model capabilities rather than memorizing specific model names. Be prepared to evaluate models based on multimodal support, multilingual performance, reasoning quality, latency, cost, context window size, and structured output capabilities. Equally important is understanding how these models integrate with Azure SQL and Azure AI services to build scalable, secure, and maintainable AI-enabled database solutions.

Practice Exam Questions

Question 1

You are developing an AI-enabled application that summarizes support tickets stored in Azure SQL Database. The application must support English, Spanish, French, German, and Japanese without deploying separate models for each language.

Which type of model best satisfies this requirement?

A. A monolingual English language model with prompt translation
B. A multilingual language model trained on multiple languages
C. A computer vision model with OCR capabilities
D. A speech recognition model

Correct Answer: B

Explanation:
Multilingual large language models (LLMs) are specifically trained to understand and generate text in many languages, eliminating the need to deploy separate models for each supported language. While prompt translation can work, it introduces additional latency and possible translation inaccuracies. Computer vision and speech models are not designed for multilingual text generation.

Question 2

An organization wants an AI model that can analyze scanned invoices, extract tables, understand handwritten notes, and answer user questions about the document.

Which model capability is required?

A. Structured output only
B. Text embedding generation
C. Multimodal processing
D. Sentiment analysis

Correct Answer: C

Explanation:
Multimodal models process multiple input types—including images, documents, handwritten text, and natural language—allowing them to interpret invoices and answer questions. Embedding models create vector representations but do not analyze images directly.

Question 3

You need an AI model that consistently returns data in valid JSON matching a predefined schema for direct insertion into a SQL table.

Which capability should you prioritize?

A. Long context window
B. Large parameter count
C. Function calling only
D. Structured output support

Correct Answer: D

Explanation:
Structured output capabilities ensure responses conform to predefined schemas such as JSON, reducing parsing errors and simplifying database integration. Function calling invokes external operations but does not guarantee JSON schema compliance.

Question 4

Your application performs simple product categorization and sentiment analysis on thousands of customer reviews every minute. Response time and operational cost are more important than handling complex reasoning tasks.

Which model size is the most appropriate?

A. The largest available reasoning model
B. A medium-sized multimodal model
C. A small language model optimized for classification tasks
D. A vision-language model

Correct Answer: C

Explanation:
Simple classification workloads generally do not require large reasoning models. Smaller models provide lower latency, reduced infrastructure costs, and sufficient accuracy for routine categorization and sentiment analysis.

Question 5

A financial institution evaluates several external AI models before deployment.

Which factor should receive the highest priority when handling confidential customer information?

A. Number of supported programming languages
B. Data privacy and regulatory compliance
C. Maximum context window size
D. Availability of image generation

Correct Answer: B

Explanation:
For regulated industries, protecting sensitive information and complying with regulations are primary evaluation criteria. Features such as image generation or larger context windows are secondary if the model cannot satisfy organizational security and compliance requirements.

Question 6

Your organization must choose between two external language models.

Model A produces slightly more accurate answers but averages 8 seconds per response.

Model B is slightly less accurate but consistently responds in under one second.

Which consideration is being evaluated?

A. Tokenization strategy
B. Embedding dimensions
C. Latency versus accuracy tradeoff
D. Database normalization

Correct Answer: C

Explanation:
Model evaluation frequently involves balancing response quality against latency. Interactive applications often prioritize faster responses, while analytical workloads may tolerate longer processing times for greater accuracy.

Question 7

A development team is comparing two embedding models.

One produces 768-dimensional vectors while another produces 3,072-dimensional vectors.

What is generally true?

A. Higher-dimensional embeddings always guarantee better search results.
B. Larger embeddings often improve semantic representation but require more storage and computation.
C. Embedding dimensions have no effect on vector databases.
D. Smaller embeddings always produce higher recall.

Correct Answer: B

Explanation:
Higher-dimensional vectors can capture richer semantic information but increase storage requirements, indexing costs, and similarity search computation. Larger dimensions do not automatically produce better search quality.

Question 8

A healthcare application requires AI-generated discharge summaries that follow a strict template so they can be automatically imported into Azure SQL Database.

Which model feature is most important?

A. Image generation capabilities
B. Speech synthesis support
C. Larger token limits only
D. Structured output generation

Correct Answer: D

Explanation:
Structured outputs enable AI-generated responses to consistently match required formats, such as JSON or predefined schemas, simplifying automated ingestion into databases and reducing validation errors.

Question 9

Why might an organization intentionally choose a smaller external language model instead of the newest, largest model?

A. Smaller models are always more accurate.
B. Smaller models always support more languages.
C. Smaller models often provide lower cost, reduced latency, and sufficient performance for many workloads.
D. Smaller models eliminate the need for prompt engineering.

Correct Answer: C

Explanation:
Many enterprise workloads involve straightforward tasks where the largest model offers minimal additional benefit. Smaller models frequently provide faster responses, lower inference costs, and simpler deployment while meeting performance requirements.

Question 10

An AI-enabled SQL application must process both text and uploaded product images to answer customer questions.

Which model should be recommended?

A. A multimodal language model
B. A text embedding model only
C. A relational database engine
D. A recommendation engine

Correct Answer: A

Explanation:
Multimodal models can simultaneously process textual and visual information, enabling users to ask questions about images and receive context-aware responses. Text embedding models only generate vector representations and cannot directly analyze images.

Exam Tips

For the DP-800 exam, remember these key evaluation principles when selecting external AI models:

Select multilingual models when supporting multiple languages without translation pipelines.
Choose multimodal models whenever applications must process images, documents, audio, or mixed media.
Prefer structured output capabilities when AI responses must populate SQL tables or APIs reliably.
Evaluate model size based on workload complexity, balancing cost, latency, throughput, and reasoning ability.
Consider privacy, compliance, and data residency before selecting external AI services.
Compare models using multiple metrics, including accuracy, latency, throughput, token limits, context window size, scalability, and operational cost.
Remember that larger models are not always the best choice—the optimal model is the one that best satisfies the application’s functional, performance, security, and budget requirements.

Go to the DP-800 Exam Prep Hub main page

AI, Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Create and manage external models (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:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement models and embeddings
      --> Create and manage external models

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

One of the major additions to Microsoft SQL Server 2025, Azure SQL Database, and Azure SQL Managed Instance is the ability to directly integrate with external Artificial Intelligence (AI) models. Rather than exporting data to a separate application, developers can invoke Large Language Models (LLMs), embedding models, or other AI services directly from SQL code. This significantly simplifies the development of intelligent database applications.

For the DP-800 exam, candidates should understand how external models are configured, managed, secured, monitored, and consumed from SQL databases. They should also understand the architectural considerations involved in connecting SQL Server to external AI services such as Azure OpenAI Service, Azure AI Foundry models, GitHub Models, or other OpenAI-compatible endpoints.

What Are External Models?

An external model is an AI model that is hosted outside the SQL database but can be invoked securely from SQL statements.

Instead of training or hosting the model inside SQL Server, SQL sends requests to the model through a configured endpoint.

Examples include:

Azure OpenAI GPT models
Azure AI Foundry models
OpenAI API models
GitHub Models
Cohere models
Meta Llama models
Mistral AI models
Other OpenAI-compatible endpoints

The SQL database becomes an intelligent application layer capable of performing AI operations while leaving model hosting and scaling to specialized AI services.

Why Use External Models?

External AI models provide capabilities such as:

Natural language generation
Text summarization
Classification
Translation
Sentiment analysis
Content generation
Question answering
Embedding generation
Semantic search
Retrieval-Augmented Generation (RAG)

Without external models, these tasks would require exporting database data into an application layer before AI processing.

Benefits of External Models

Using external models provides several advantages:

Reduced Application Complexity

Applications can invoke AI directly from SQL instead of implementing additional middleware.

Centralized Data Processing

Data remains closer to where it is stored, reducing unnecessary movement.

Simplified Development

Developers write SQL instead of building custom AI integration layers.

Enterprise Security

Authentication occurs through secure credentials and managed identities.

Scalability

The external AI provider handles model hosting, GPU infrastructure, scaling, and updates.

External Model Architecture

A typical architecture consists of:

			
Application
      │
Azure SQL Database
      │
External Model Definition
      │
Credential
      │
HTTPS Endpoint
      │
Azure OpenAI / AI Foundry / OpenAI

		

SQL sends HTTPS requests to the configured endpoint and returns the model’s response to the calling application.

Components of an External Model

An external model configuration typically includes:

Model name
Endpoint URL
Authentication method
API version
Deployment name
Credentials
Optional timeout settings
Model capabilities

Supported AI Services

DP-800 focuses primarily on Microsoft’s AI ecosystem.

Common supported services include:

Azure OpenAI Service

Most common deployment option.

Supports:

GPT-4
GPT-4.1
GPT-4o
GPT-4 Turbo
Embedding models

Azure AI Foundry

Provides access to multiple foundation models from various providers.

Examples include:

Meta Llama
Mistral
Cohere
Phi models
DeepSeek (where available)

OpenAI-Compatible APIs

SQL can communicate with services implementing the OpenAI API specification.

Creating an External Model

The general process includes:

Step 1

Deploy a model in Azure AI Foundry or Azure OpenAI.

Step 2

Create authentication credentials.

Examples include:

API Keys
Microsoft Entra ID authentication
Managed Identity

Step 3

Create an external model definition inside SQL.

This associates:

endpoint
deployment
credentials
model metadata

Step 4

Test connectivity.

Execute SQL queries that invoke the model.

Step 5

Monitor usage.

Review:

failures
latency
token consumption
throttling

Authentication Methods

Security is a major exam topic.

Supported authentication methods include:

API Keys

Simple to configure.

Advantages:

Easy setup

Disadvantages:

Requires secure storage
Must be rotated regularly

Microsoft Entra ID

Recommended for enterprise deployments.

Benefits:

Central identity management
Conditional Access
Role-Based Access Control
No hardcoded secrets

Managed Identity

Preferred when SQL services interact with Azure services.

Advantages:

No passwords
Automatic credential rotation
Strong security posture

Managing Credentials

Credentials should never be hardcoded into SQL scripts.

Best practices include:

Azure Key Vault
Managed Identity
Secure credential objects
Secret rotation
Least privilege

Model Configuration Considerations

When selecting a model, evaluate:

Latency
Cost
Context window
Maximum tokens
Supported languages
Multimodal support
Structured outputs
Function calling
Embedding support
Regional availability

Model Version Management

AI models evolve frequently.

Developers should:

Test new versions
Validate prompt compatibility
Measure output quality
Compare latency
Evaluate token costs
Deploy gradually

Avoid automatically replacing production models without validation.

Monitoring External Models

Important operational metrics include:

Request count
Failed requests
Average latency
Token usage
Cost
Timeout frequency
Authentication failures
Rate limiting
Model availability

Monitoring may be performed using Azure Monitor, Azure OpenAI metrics, Application Insights, and Log Analytics.

Error Handling

Applications should anticipate failures such as:

Network interruptions
Authentication failures
Invalid prompts
Model timeouts
Rate limiting
Endpoint unavailability
Quota exhaustion

Applications should implement:

Retry logic
Exponential backoff
Logging
Graceful degradation
User-friendly error messages

Cost Management

External AI services typically charge based on token usage.

Cost optimization strategies include:

Select smaller models when appropriate.
Minimize unnecessary prompts.
Cache reusable responses.
Use embeddings instead of repeated generation where applicable.
Monitor token consumption.
Apply rate limits where appropriate.

Security Best Practices

Microsoft recommends:

Use Microsoft Entra ID whenever possible.
Store secrets securely.
Rotate API keys regularly.
Restrict network access.
Enable auditing.
Monitor authentication failures.
Apply least privilege.
Encrypt data in transit.
Avoid sending sensitive information unnecessarily.

Best Practices for DP-800

Candidates should remember the following:

External models are hosted outside SQL.
SQL communicates with models over secure HTTPS endpoints.
Azure OpenAI and Azure AI Foundry are primary Microsoft AI services.
Managed Identity is generally preferred over API keys in Azure.
Never hardcode secrets.
Monitor token usage and latency.
Plan for retries and transient failures.
Validate model updates before production deployment.
Balance performance, cost, and model capabilities.
Use the smallest model that satisfies business requirements.

DP-800 Exam Tips

For the exam, be prepared to:

Differentiate between external models and local database objects.
Understand authentication methods.
Identify secure credential storage mechanisms.
Select appropriate model types.
Monitor AI usage and performance.
Recommend enterprise security practices.
Manage model lifecycle and versioning.
Understand cost optimization strategies.
Configure reliable AI integrations.
Recognize scenarios where Azure OpenAI or Azure AI Foundry is the preferred solution.

Key Takeaways

Creating and managing external models enables SQL databases to leverage modern AI capabilities without hosting AI infrastructure locally. By securely connecting SQL Server or Azure SQL to services like Azure OpenAI or Azure AI Foundry, developers can incorporate intelligent features such as summarization, classification, semantic search, and RAG directly into database applications. Success depends on proper authentication, secure credential management, monitoring, version control, cost optimization, and selecting the right model for each workload.

Practice Exam Questions

Question 1

A developer wants to enable an Azure SQL Database application to generate natural language summaries using GPT-4o hosted in Azure OpenAI. What is the primary purpose of creating an external model?

A. To copy the AI model into SQL Server memory

B. To allow SQL to securely invoke an externally hosted AI model

C. To convert SQL queries into Python scripts

D. To replace stored procedures with AI-generated code

Correct Answer: B

Explanation: External models define the connection between SQL and an externally hosted AI service. The model remains hosted in Azure OpenAI or another provider, while SQL securely sends requests to it.

Question 2

Which authentication method is generally recommended for Azure SQL Database accessing Azure OpenAI in an enterprise environment?

A. Username and password authentication

B. Shared administrator account

C. Managed Identity

D. Anonymous authentication

Correct Answer: C

Explanation: Managed Identity eliminates the need to store secrets, supports automatic credential rotation, and integrates with Microsoft Entra ID, making it Microsoft’s recommended authentication approach for Azure resources.

Question 3

An organization wants to minimize operational overhead while securely accessing external AI models. Which authentication mechanism best satisfies this requirement?

A. API keys stored in application code

B. SQL logins

C. Managed Identity

D. Local Windows accounts

Correct Answer: C

Explanation: Managed Identity removes the need to manually manage secrets and provides secure, automatic authentication between Azure services.

Question 4

Which factor should be monitored most closely to help control the operational cost of external language models?

A. Token consumption

B. Number of database indexes

C. Memory allocated to SQL Server

D. CPU utilization on the SQL Server

Correct Answer: A

Explanation: Most external LLM providers charge based on token usage. Monitoring prompt and completion tokens helps organizations estimate and manage AI costs.

Question 5

A developer needs to securely store API credentials used by an external model.

Which solution follows Microsoft security best practices?

A. Store the API key in Azure Key Vault

B. Save the API key in a table within the application database

C. Embed the API key in application source code

D. Place the API key in a configuration file committed to source control

Correct Answer: A

Explanation: Azure Key Vault provides secure storage, access policies, auditing, and secret rotation capabilities, making it the recommended location for sensitive credentials.

Question 6

Why should organizations validate new versions of external AI models before deploying them into production?

A. New versions always increase latency.

B. New versions cannot process SQL data.

C. Model behavior, output quality, and performance characteristics may change.

D. SQL Server requires a database restart after every model update.

Correct Answer: C

Explanation: AI model updates can alter response quality, reasoning, formatting, latency, and cost. Testing ensures compatibility with existing applications and prompts.

Question 7

Which capability is provided by Azure AI Foundry that benefits SQL developers?

A. It hosts only Microsoft-developed language models.

B. It provides access to multiple foundation models from different providers.

C. It automatically creates SQL indexes.

D. It replaces Azure SQL Database.

Correct Answer: B

Explanation: Azure AI Foundry offers access to numerous foundation models from Microsoft and third-party providers, enabling developers to select the most appropriate model for their workloads.

Question 8

An external model begins returning timeout errors during peak business hours.

Which application design strategy should be implemented?

A. Disable authentication.

B. Delete and recreate the database.

C. Increase the number of SQL indexes.

D. Implement retry logic with exponential backoff.

Correct Answer: D

Explanation: Transient failures, including timeouts, are common in distributed systems. Retry logic with exponential backoff improves resilience without overwhelming the external service.

Question 9

Which statement best describes an external AI model?

A. It is stored entirely within the SQL database.

B. It executes as a SQL stored procedure.

C. It is hosted externally and accessed through a secure endpoint.

D. It permanently replaces relational queries.

Correct Answer: C

Explanation: External AI models remain hosted outside the database. SQL communicates with them using secure HTTPS requests through configured endpoints.

Question 10

When selecting between multiple external AI models, which combination of evaluation criteria is most appropriate?

A. Number of SQL tables and indexes

B. Latency, cost, capabilities, context window, security, and accuracy

C. File system capacity only

D. Number of database users

Correct Answer: B

Explanation: Choosing the right external model requires balancing functional capabilities with operational considerations such as latency, cost, accuracy, security, supported features, and context window size.

Go to the DP-800 Exam Prep Hub main page

AI, Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry – Part 1 (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:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement models and embeddings
      --> Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry

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

One of the most important aspects of building AI-enabled database applications is maintaining the accuracy of vector embeddings. Embeddings represent the semantic meaning of data at a specific point in time. Whenever the underlying source data changes, the associated embeddings may become outdated. If stale embeddings remain in a vector index, semantic search, Retrieval-Augmented Generation (RAG), recommendation engines, and AI assistants can produce inaccurate or misleading results.

For the DP-800 exam, candidates should understand the various methods available to detect changes to relational data and automatically regenerate embeddings. Microsoft SQL Server 2025 and Azure SQL provide several mechanisms to detect data changes, each with different tradeoffs in performance, scalability, complexity, and latency.

The exam focuses on selecting the most appropriate embedding maintenance strategy based on business requirements.

What Is Embedding Maintenance?

Embedding maintenance is the process of keeping vector embeddings synchronized with the underlying relational data.

Whenever data changes, one or more of the following actions may be required:

Generate a new embedding.
Replace the old embedding.
Update the vector index.
Remove deleted vectors.
Refresh search indexes.

Without proper maintenance, semantic search quality gradually degrades.

Why Embedding Maintenance Is Important

Suppose a product catalog contains this description:

“Wireless Bluetooth Noise-Cancelling Headphones”

An embedding is generated from that description.

Later, the product description changes to:

“Wireless Bluetooth Noise-Cancelling Headphones with Spatial Audio and USB-C Fast Charging”

If the embedding is not regenerated:

AI searches may not return the product.
Vector similarity decreases.
RAG answers become outdated.
Recommendation quality drops.

Keeping embeddings synchronized ensures AI applications remain accurate.

Common Embedding Maintenance Workflow

Most embedding maintenance solutions follow this lifecycle:

			
User Updates SQL Data
          │
          ▼
Change Detection
          │
          ▼
Generate New Embedding
          │
          ▼
Store Updated Vector
          │
          ▼
Refresh Vector Search Index

		

The primary difference between maintenance methods is how they detect changes.

Choosing the Right Maintenance Strategy

Microsoft provides several approaches:

Method	Typical Latency	Complexity	Best For
Table Triggers	Immediate	Low	Small databases
Change Tracking	Low	Medium	Incremental synchronization
Change Data Capture (CDC)	Medium	Medium	ETL and analytics
Azure Functions SQL Trigger	Near real-time	Medium	Event-driven cloud apps
Azure Logic Apps	Near real-time	Low	Low-code automation
Change Event Streaming (CES)	Real-time	High	Streaming architectures
Microsoft Foundry Pipelines	Scheduled or event-driven	Medium	AI data pipelines

Table Triggers

What Are They?

Table triggers automatically execute SQL code whenever data changes.

Example events include:

INSERT
UPDATE
DELETE

Triggers provide immediate notification that data has changed.

Embedding Workflow Using Triggers

			
UPDATE Product
       │
       ▼
Trigger Executes
       │
       ▼
Identify Changed Row
       │
       ▼
Queue Embedding Job

		

The trigger usually should not generate the embedding itself because AI model inference may take several seconds.

Instead, the trigger inserts a work item into a processing queue.

Advantages

Immediate detection
Simple implementation
Works entirely within SQL
No polling required

Disadvantages

Can increase transaction duration
Poor choice for expensive AI operations
May reduce OLTP performance
Difficult to scale for very high transaction volumes

Best Practice

Use triggers only to record changes—not to call AI models directly.

Change Tracking

What Is Change Tracking?

Change Tracking is a lightweight SQL Server feature that records which rows have changed without recording every individual data modification.

Applications periodically retrieve changed rows and regenerate only affected embeddings.

Workflow

			
Application
      │
      ▼
Read Change Tracking
      │
      ▼
Changed Rows
      │
      ▼
Generate Embeddings
      │
      ▼
Update Vector Table

		

Advantages

Lightweight
Low storage overhead
Incremental processing
Excellent for synchronization

Limitations

Does not capture previous values
Does not store complete history
Requires periodic polling

Best Use Cases

RAG applications
Semantic search
Incremental embedding refresh
Azure SQL synchronization

Change Data Capture (CDC)

What Is CDC?

Change Data Capture records detailed information about every change made to a table.

It captures:

Inserts
Updates
Deletes
Previous values
New values
Log sequence numbers (LSNs)

CDC reads the SQL transaction log rather than relying on triggers.

Workflow

			
Transaction Log
       │
       ▼
CDC Tables
       │
       ▼
Embedding Pipeline
       │
       ▼
Vector Updates

		

Advantages

Complete history
High reliability
Efficient large-scale processing
Ideal for ETL

Disadvantages

More storage than Change Tracking
Higher administrative overhead
Not truly instantaneous

Best Use Cases

Enterprise ETL
Large databases
Historical auditing
Batch embedding refresh

Comparing Change Tracking and CDC

Feature	Change Tracking	CDC
Tracks changed rows	Yes	Yes
Stores previous values	No	Yes
Transaction log based	No	Yes
Full history	No	Yes
Storage overhead	Low	Medium
Synchronization	Excellent	Excellent
Auditing	Limited	Excellent

Azure Functions with SQL Trigger Binding

Azure Functions provide serverless compute that automatically executes code when SQL data changes.

Instead of polling SQL continuously, the SQL trigger binding reacts to data modifications.

Typical workflow:

			
SQL Change
      │
      ▼
Azure Function
      │
      ▼
Generate Embedding
      │
      ▼
Store Vector

		

Advantages

Serverless
Automatic scaling
Pay-per-execution
Near real-time processing
Excellent Azure integration

Best Use Cases

Cloud-native AI applications
Azure SQL Database
RAG systems
Intelligent search solutions

Azure Logic Apps

Azure Logic Apps provide a low-code workflow engine.

Instead of writing custom code, developers configure workflows visually.

Typical workflow:

			
SQL Change
      │
      ▼
Logic App Trigger
      │
      ▼
Call Azure OpenAI
      │
      ▼
Update Embedding Table

		

Advantages

Low-code development
Hundreds of built-in connectors
Easy integration with Azure services
Fast implementation

Limitations

Less flexible than custom code
Higher latency than Azure Functions
Complex workflows can become difficult to maintain

Best Use Cases

Business automation
Small AI workflows
Rapid prototyping
Citizen developers

Choosing Between Triggers, Change Tracking, CDC, Azure Functions, and Logic Apps

Scenario	Recommended Method
Small OLTP database	Table Trigger + Queue
Incremental synchronization	Change Tracking
Historical auditing	CDC
Serverless AI processing	Azure Functions
Low-code workflow	Azure Logic Apps

DP-800 Exam Tips (Part 1)

Remember these key points for the exam:

Triggers provide immediate notification but should not directly perform expensive AI inference.
Change Tracking records which rows changed and is optimized for lightweight synchronization.
CDC captures detailed change history and is ideal for enterprise ETL and auditing.
Azure Functions with SQL trigger binding enable scalable, serverless, event-driven embedding generation.
Azure Logic Apps offer a low-code approach for automating embedding workflows with Azure services.
Select the maintenance method based on the required balance of latency, scalability, operational complexity, and business requirements.

Go to the DP-800 Exam Prep Hub main page

AI, Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry – Part 2 (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:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement models and embeddings
      --> Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry

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.

Change Event Streaming (CES)

What Is Change Event Streaming?

Change Event Streaming (CES) is an event-driven architecture that publishes database changes as a continuous stream of events. Instead of periodically polling the database for updates, applications subscribe to events as they occur.

In AI-enabled database solutions, CES enables embeddings to be regenerated almost immediately after data changes, making it well suited for near real-time AI applications.

Typical event streaming technologies include:

Azure Event Hubs
Azure Service Bus
Apache Kafka-compatible services
Microsoft Fabric Eventstreams
Azure Event Grid (for certain event-driven scenarios)

Although the underlying messaging technology can vary, the goal remains the same: publish changes once and allow multiple downstream consumers to react independently.

CES Workflow

			
Application Updates Product
          │
          ▼
Database Change Event
          │
          ▼
Event Stream
          │
          ▼
Embedding Service
          │
          ▼
Generate New Embedding
          │
          ▼
Update Vector Table
          │
          ▼
Refresh Vector Index

		

Unlike triggers, the database transaction completes first before downstream processing begins.

Advantages of CES

Near Real-Time Processing

Embeddings are regenerated within seconds instead of waiting for scheduled synchronization jobs.

Loose Coupling

The database does not directly invoke AI services.

Instead:

Database → Event Stream → AI Service

Each component evolves independently.

Scalability

Multiple consumers can process the same event stream simultaneously.

Examples include:

Embedding generation
Analytics
Notifications
Data warehouse loading
Audit logging

Reliability

Most event streaming platforms support:

Message durability
Retry policies
Dead-letter queues
Checkpointing
Replay capability

Limitations of CES

CES introduces additional infrastructure.

Organizations must manage:

Event brokers
Message retention
Consumer groups
Retry policies
Monitoring
Event ordering
Duplicate message handling

Consequently, CES is best suited to enterprise-scale systems rather than small departmental applications.

Best Use Cases for CES

CES is particularly appropriate for:

Large AI-powered search platforms
High-volume ecommerce catalogs
Recommendation engines
Enterprise RAG applications
Distributed microservices
Real-time personalization
AI copilots
Event-driven architectures

Microsoft Foundry for Embedding Maintenance

What Is Microsoft Foundry?

Microsoft Foundry (Azure AI Foundry) provides an end-to-end platform for building, evaluating, orchestrating, and managing AI applications.

Within embedding maintenance scenarios, Foundry can orchestrate the entire embedding lifecycle, including:

Detecting changes
Invoking embedding models
Validating outputs
Updating vector stores
Monitoring AI workloads
Managing model versions

Instead of writing custom orchestration code, developers can leverage Foundry pipelines and workflows.

Foundry Workflow

			
SQL Database
      │
      ▼
Change Detection
      │
      ▼
Foundry Pipeline
      │
      ▼
Embedding Model
      │
      ▼
Vector Generation
      │
      ▼
Azure SQL Vector Column
      │
      ▼
Vector Search

		

Advantages of Microsoft Foundry

Centralized AI Management

Developers manage:

Models
Prompts
Pipelines
Evaluations
Monitoring

within a unified environment.

Model Flexibility

Foundry supports many foundation models, including:

OpenAI GPT models
Phi models
Llama models
Mistral
Cohere
Other supported models

This flexibility allows organizations to switch models without redesigning their database architecture.

Integrated Evaluation

Foundry provides tools to evaluate:

Response quality
Latency
Cost
Safety
Groundedness
Hallucination rates

These capabilities help organizations choose the most appropriate embedding model over time.

Choosing the Appropriate Embedding Maintenance Method

The DP-800 exam expects candidates to recommend the most suitable approach for a given scenario.

Scenario 1

A small inventory system updates only a few records each day.

Recommended solution:

Table Trigger + Background Queue

Reason:

Simple implementation with minimal infrastructure.

Scenario 2

An ecommerce application updates thousands of products every hour.

Recommended solution:

Change Tracking

Reason:

Incremental synchronization with low overhead.

Scenario 3

A financial organization requires complete auditing of every database modification.

Recommended solution:

Change Data Capture (CDC)

Reason:

Captures historical values and detailed change information.

Scenario 4

A cloud-native AI chatbot must update embeddings immediately after documents change.

Recommended solution:

Azure Functions with SQL Trigger Binding

Reason:

Serverless, scalable, near real-time processing.

Scenario 5

A business analyst wants to automate embedding generation without writing code.

Recommended solution:

Azure Logic Apps

Reason:

Visual workflow designer with numerous connectors.

Scenario 6

A global ecommerce platform updates millions of products continuously.

Recommended solution:

Change Event Streaming (CES)

Reason:

Highly scalable event-driven architecture.

Scenario 7

An enterprise AI team manages multiple models and complex AI workflows.

Recommended solution:

Microsoft Foundry

Reason:

Centralized orchestration, evaluation, and lifecycle management.

Hybrid Architectures

Many enterprise solutions combine multiple technologies.

Example:

			
Azure SQL Database
        │
        ▼
Change Tracking
        │
        ▼
Azure Function
        │
        ▼
Azure OpenAI Embedding Model
        │
        ▼
Vector Table
        │
        ▼
Azure AI Search

		

			
CDC
   │
   ▼
Event Hub
   │
   ▼
Microsoft Foundry Pipeline
   │
   ▼
Embedding Generation
   │
   ▼
Azure SQL Vector Store

		

Hybrid solutions often provide the best balance between scalability, reliability, and operational simplicity.

Performance Considerations

When designing an embedding maintenance strategy, consider:

Latency

How quickly must embeddings be updated?

Seconds
Minutes
Hours
Overnight

Volume

How many records change?

Hundreds
Thousands
Millions

Cost

Real-time updates generally cost more than scheduled batch updates because they invoke AI services more frequently.

Reliability

Determine how failures are handled.

Best practices include:

Retry policies
Dead-letter queues
Logging
Checkpointing
Idempotent processing (safe repeated execution)

Scalability

Solutions should scale horizontally without affecting OLTP performance.

Avoid placing expensive AI inference directly inside database transactions.

Security Considerations

Embedding maintenance processes should follow Microsoft security recommendations.

Authentication

Prefer:

Managed Identity
Microsoft Entra ID

Avoid hardcoded API keys whenever possible.

Secret Storage

Store credentials in:

Azure Key Vault

Do not embed secrets in:

SQL scripts
Stored procedures
Source code
Configuration files checked into source control

Least Privilege

Embedding services should receive only the permissions required to:

Read source data
Generate embeddings
Update vector columns

Common Mistakes

Many candidates incorrectly assume:

❌ Triggers should directly call AI models.

Instead:

✔ Triggers should enqueue work.

❌ CDC and Change Tracking are identical.

Instead:

✔ CDC stores detailed history.

✔ Change Tracking stores lightweight synchronization information.

❌ Real-time processing is always best.

Instead:

✔ Choose the solution that balances latency, complexity, scalability, and cost.

❌ Azure Logic Apps are intended only for business workflows.

Instead:

✔ Logic Apps can orchestrate AI-powered embedding updates using Azure connectors.

DP-800 Exam Tips

For the exam, remember the following associations:

Requirement	Recommended Solution
Immediate notification	Table Trigger
Lightweight synchronization	Change Tracking
Full audit history	CDC
Serverless event processing	Azure Functions
Low-code automation	Azure Logic Apps
Massive real-time streaming	Change Event Streaming (CES)
AI orchestration and lifecycle management	Microsoft Foundry

Also remember:

Triggers are appropriate for detecting changes, but expensive AI operations should execute outside the transaction.
Change Tracking is optimized for incremental synchronization with minimal overhead.
CDC is best when historical change information is required.
Azure Functions provide scalable, event-driven embedding generation.
Azure Logic Apps are ideal for low-code integration workflows.
CES supports highly scalable, distributed, event-driven architectures.
Microsoft Foundry centralizes AI model management, orchestration, evaluation, and monitoring.

Key Takeaways

Choosing the right embedding maintenance strategy is essential for ensuring that vector representations remain synchronized with relational data. The optimal solution depends on business requirements for latency, scalability, complexity, cost, and governance. Smaller systems may benefit from triggers or Change Tracking, while enterprise AI applications often use Azure Functions, CES, or Microsoft Foundry to automate embedding generation at scale. Understanding the strengths and tradeoffs of each option is a key objective of the DP-800 certification exam.

Practice Exam Questions

Question 1

A company stores product descriptions in Azure SQL Database and generates vector embeddings for semantic search. Product descriptions change only a few times per week, and the company wants a lightweight mechanism to identify modified rows before regenerating embeddings.

Which feature should be recommended?

A. Change Tracking

B. AFTER UPDATE triggers

C. SQL Agent Jobs

D. Transaction Replication

Correct Answer: A

Explanation

Change Tracking records which rows have changed with minimal overhead, making it ideal for periodically identifying records whose embeddings need regeneration.

Why the other answers are incorrect:

B: Triggers execute synchronously and increase transaction time.
C: SQL Agent is not available in Azure SQL Database.
D: Replication is intended for data synchronization, not change detection for AI workflows.

Question 2

A financial services company must regenerate embeddings immediately after a customer profile changes because AI-powered recommendations must always reflect the latest data.

Which maintenance approach best satisfies this requirement?

A. Nightly batch processing

B. Azure Logic Apps scheduled every hour

C. AFTER INSERT and UPDATE table triggers

D. Weekly CDC processing

Correct Answer: C

Explanation

Table triggers execute immediately after data modifications, making them suitable when embeddings must remain synchronized with transactional data.

Why the other answers are incorrect:

A: Introduces unacceptable latency.
B: Scheduled workflows are not immediate.
D: CDC is asynchronous.

Question 3

A retailer updates millions of inventory records daily. Embedding generation is computationally expensive, and the organization wants processing to occur asynchronously without affecting transaction performance.

Which architecture is the best choice?

A. Table triggers that call Azure OpenAI directly

B. Change Data Capture combined with Azure Functions

C. Manual nightly exports

D. Recursive stored procedures

Correct Answer: B

Explanation

CDC captures database changes asynchronously, while Azure Functions can process those changes independently to generate embeddings.

Why the other answers are incorrect:

A: External service calls should not occur inside triggers.
C: Manual exports are inefficient.
D: Stored procedures are not designed for event-driven processing.

Question 4

A company wants a low-code solution that automatically updates embeddings whenever new documents are added while integrating with Azure AI services.

Which service should be recommended?

A. SQL CLR

B. Azure Kubernetes Service

C. Azure Logic Apps

D. SQL Replication

Correct Answer: C

Explanation

Azure Logic Apps provide low-code workflow automation and easily integrate SQL Database with Azure AI services.

Why the other answers are incorrect:

A: CLR is unsupported in Azure SQL Database.
B: AKS is unnecessary for simple workflows.
D: Replication does not generate embeddings.

Question 5

A global retailer wants multiple downstream applications—including AI pipelines, analytics systems, and notification services—to receive database change events independently.

Which technology is best suited?

A. SQL Agent

B. Change Event Streaming (CES)

C. Table triggers

D. Dynamic Data Masking

Correct Answer: B

Explanation

CES publishes change events that multiple consumers can process independently, making it ideal for scalable event-driven architectures.

Why the other answers are incorrect:

A: SQL Agent is scheduler-based.
C: Triggers execute only within the database transaction.
D: Dynamic Data Masking is unrelated.

Question 6

An organization wants a centralized AI platform that manages embedding generation, model lifecycle, monitoring, governance, and orchestration across multiple databases.

Which solution best meets these requirements?

A. Microsoft Foundry

B. SQL Server Agent

C. Azure Backup

D. Elastic Query

Correct Answer: A

Explanation

Microsoft Foundry provides enterprise AI orchestration, governance, monitoring, and centralized management of embedding workflows.

Why the other answers are incorrect:

B: SQL Agent schedules jobs only.
C: Azure Backup is unrelated.
D: Elastic Query supports distributed querying, not AI orchestration.

Question 7

A company stores thousands of product descriptions in an Azure SQL Database. New rows are added every few hours, while updates to existing descriptions are relatively rare. The organization wants an efficient solution that minimizes database overhead while identifying only rows that require regenerated embeddings.

Which approach should be recommended?

A. Enable Change Tracking and periodically process changed rows.

B. Create AFTER INSERT and AFTER UPDATE triggers that immediately regenerate embeddings.

C. Rebuild embeddings for every record every night.

D. Disable change detection and regenerate embeddings manually.

Correct Answer: A

Explanation

Change Tracking records which rows have changed without capturing full before-and-after values, making it lightweight and well suited for identifying documents requiring updated embeddings.

Why the other answers are incorrect:

B: Triggers increase transaction duration.
C: Full regeneration wastes resources.
D: Manual processes are unsuitable for production.

Question 8

A development team uses Azure SQL Database and wants embedding generation to occur automatically whenever qualifying data changes. The solution should require minimal infrastructure management while supporting serverless execution.

Which option best meets these requirements?

A. SQL Agent jobs

B. Azure Logic Apps with a daily recurrence trigger

C. Azure Functions using SQL trigger binding

D. Manual PowerShell execution

Correct Answer: C

Explanation

Azure Functions with SQL trigger binding provide event-driven, serverless processing that reacts automatically to SQL changes.

Why the other answers are incorrect:

A: SQL Agent is unavailable in Azure SQL Database.
B: Polling introduces unnecessary latency.
D: Manual execution is not scalable.

Question 9

A company has implemented Microsoft Foundry to orchestrate its AI workloads. Multiple databases contribute documents that require embeddings, and administrators want centralized orchestration, monitoring, and model lifecycle management.

Which embedding maintenance approach is most appropriate?

A. Table triggers on every database

B. Change Tracking only

C. Microsoft Foundry orchestration

D. Manual nightly SQL scripts

Correct Answer: C

Explanation

Microsoft Foundry provides centralized orchestration for AI pipelines, including embedding generation, monitoring, governance, and model management.

Why the other answers are incorrect:

A: Triggers do not provide orchestration.
B: Change Tracking only detects changes.
D: Manual scripts do not scale well.

Question 10

An organization maintains embeddings for customer support articles. The business requires that embedding updates remain resilient even if the external AI model becomes temporarily unavailable. Failed requests should be retried without affecting database transactions.

Which architecture best satisfies these requirements?

A. Generate embeddings inside SQL table triggers.

B. Use an asynchronous event-driven process such as CDC or CES combined with Azure Functions or Microsoft Foundry.

C. Regenerate every embedding immediately within the user transaction.

D. Require users to manually regenerate embeddings after every update.

Correct Answer: B

Explanation

An asynchronous architecture decouples database transactions from AI processing. Failed embedding generation requests can be retried without impacting database writes, improving resiliency and scalability.

Why the other answers are incorrect:

A: External service failures may block transactions.
C: Tightly coupling AI services to transactions reduces reliability.
D: Manual updates are inefficient and error-prone.

Go to the DP-800 Exam Prep Hub main page

AI, Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Identify which columns to include in embeddings (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:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement models and embeddings
      --> Identify which columns to include in embeddings

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

Embeddings are one of the most important technologies behind modern AI-powered applications such as semantic search, Retrieval-Augmented Generation (RAG), recommendation systems, document similarity, and intelligent chatbots. Rather than storing simple keywords, embeddings represent text as high-dimensional numerical vectors that capture semantic meaning.

One of the most important design decisions when implementing embeddings is determining which database columns should be embedded. Selecting the appropriate columns directly affects:

Search relevance
AI response quality
Storage requirements
Embedding generation cost
Update frequency
Query performance

The DP-800 exam expects candidates to understand how to evaluate database schemas and determine which columns should contribute meaningful semantic information to an embedding.

Why Column Selection Matters

Every embedding represents the semantic meaning of the text supplied to the embedding model.

For example, consider a Products table.

ProductID	Name	Description	Category	Price	SKU
101	Surface Laptop	Lightweight business laptop with AI features.	Laptop	1299	SL-100

If only Name is embedded:

Surface Laptop

the embedding contains very little context.

If Name + Description + Category are embedded:

			
Surface Laptop
Lightweight business laptop with AI features.
Laptop

the vector contains much richer semantic information.

This significantly improves semantic search accuracy.

General Rule

Good embedding columns contain:

Meaning
Context
Natural language
Descriptive text

Poor embedding columns contain:

IDs
Numbers
Codes
Random values
Technical metadata

Common Column Types

Excellent Candidates

These almost always improve embeddings.

Examples include:

Product descriptions
Document bodies
Knowledge base articles
Support tickets
FAQs
Customer reviews
Employee biographies
Blog articles
Research papers
Medical notes
Legal clauses

These contain rich natural language.

Example

"This laptop includes an NPU for AI acceleration and 18-hour battery life."

An embedding model can understand concepts such as:

AI laptop
battery life
portable computer
Windows device

Very Good Candidates

These provide additional context.

Examples

Product Name
Job Title
Category
Department
Brand
Tags
Keywords
Topics

Example

			
Category:
Gaming Laptop
Brand:
Contoso
Description:
High-performance RTX graphics...

		

Combining these improves semantic similarity.

Sometimes Useful

Examples

City
Country
Industry
Region
Language

These may improve retrieval depending on the application.

For example

			
Restaurant
Italian
Orlando

provides valuable context.

Usually Poor Candidates

These rarely improve embeddings.

Examples

Identity columns
GUIDs
Invoice numbers
SKUs
Phone numbers
ZIP codes
Timestamps
Row versions

Example

CustomerID = 28471

This carries no semantic meaning.

Never Useful

Examples

Binary files
Password hashes
Encryption keys
Checksums
Internal IDs

These should never be embedded.

Combining Multiple Columns

Rather than embedding each column individually, applications often concatenate multiple descriptive columns into one text document before generating the embedding.

Example

Instead of embedding

Title

only

combine

			
Title
Category
Description
Features

Example generated text

			
Surface Laptop
Category:
Business Laptop
Features:
Touchscreen
NPU
AI Copilot+
18-hour battery
Description:
Premium lightweight laptop designed for professionals...

		

This produces much richer embeddings.

Typical SQL Example

			
SELECT
Name +
CHAR(13) +
Description +
CHAR(13) +
Category
AS EmbeddingText
FROM Products;

		

This creates a single block of text for embedding generation.

Avoid Including Irrelevant Data

Do not include unrelated information simply because it exists.

Bad example

			
Description
LastModifiedDate
ModifiedBy
RecordID
Checksum
InternalVersion

		

Only Description contributes semantic meaning.

Include Business Context

Business metadata often improves search.

Instead of

Description

consider

			
Product Name
Category
Brand
Description
Features

		

The additional context helps distinguish similar products.

Example: Knowledge Base

Table

Title	Problem	Solution	Author

Good embedding text

			
Title
Problem Description
Solution

Avoid

Author

unless searching by author.

Example: HR Resume Database

Good columns

Name
Skills
Certifications
Experience
Summary

Poor columns

EmployeeID
HireDate
PayrollNumber

Example: Healthcare

Useful

Diagnosis
Symptoms
Treatment
Clinical Notes

Not useful

Patient ID
Visit Number
Insurance Number

Example: Retail

Useful

Product Name
Description
Category
Features
Brand

Not useful

Inventory Count
Warehouse Bin
SKU

Example: Support Tickets

Useful

Ticket Title
Problem Description
Resolution

Avoid

Ticket Number
Assigned Agent ID
Status Code

Structured Data vs Natural Language

Embedding models perform best with natural language.

Instead of

			
RAM=32
CPU=i9
GPU=RTX4090

consider

			
This gaming laptop includes 32 GB RAM, an Intel Core i9 processor, and an NVIDIA RTX 4090 GPU.

Natural language improves semantic understanding.

Sensitive Information

Avoid embedding:

Passwords
SSNs
Credit card numbers
API keys
Authentication tokens
Encryption secrets

Even if embeddings are stored securely, unnecessary sensitive information should never be included.

Column Size Considerations

Very large text columns increase:

Token count
API costs
Storage
Processing time

Strategies include:

Chunk long documents
Remove boilerplate text
Eliminate duplicate content
Exclude irrelevant sections

Embedding Maintenance

If embedded columns change frequently:

Regenerate embeddings
Track changes using Change Tracking or CDC
Automate updates using Azure Functions, Logic Apps, or Microsoft Foundry

Only regenerate embeddings when relevant columns change.

SQL Server 2025 and Azure SQL

Modern SQL AI solutions support storing vectors directly in SQL databases, allowing developers to:

Store embeddings in vector columns
Perform vector similarity searches
Integrate external embedding models
Build semantic search and RAG applications entirely within SQL-centric architectures

Choosing the correct columns is one of the most important design decisions before generating these vectors.

Best Practices

Embed descriptive text instead of identifiers.
Combine multiple related text columns into a single embedding input.
Include contextual metadata such as category or brand when it improves retrieval.
Exclude numeric identifiers, timestamps, and internal metadata.
Avoid embedding sensitive or confidential information.
Chunk long documents before generating embeddings.
Keep embeddings synchronized with changes to the source columns.
Evaluate search quality periodically and refine the selected columns if retrieval results are poor.

DP-800 Exam Tips

For the exam, you should be able to:

Identify which columns provide meaningful semantic content.
Recognize when multiple columns should be combined into one embedding.
Distinguish between descriptive text and operational metadata.
Explain why identifiers and numeric values generally should not be embedded.
Understand the tradeoffs between embedding more context versus increasing token usage and storage costs.
Recommend strategies for maintaining embeddings when source data changes.
Select appropriate columns for common business scenarios such as product catalogs, knowledge bases, customer support systems, healthcare records, HR profiles, and document repositories.

Practice Exam Questions

Question 1

A company is building a semantic search application for an online product catalog. The Products table contains the following columns:

ProductID
ProductName
Category
Description
Price
SKU

Which combination of columns should be included when generating embeddings?

A. ProductID, SKU, Price

B. ProductName, Category, Description

C. ProductID, ProductName, Price

D. SKU, Category, Price

Correct Answer: B

Explanation

Embeddings should be generated from columns containing meaningful natural language and business context. Product names, categories, and descriptions provide semantic information that improves search relevance.

Why the other answers are incorrect:

A: IDs, SKUs, and prices contain little semantic meaning.
C: ProductID and Price contribute little to semantic understanding.
D: SKU and Price are operational values, not descriptive content.

Question 2

A knowledge base stores the following columns:

ArticleTitle
ProblemDescription
Resolution
CreatedDate
ArticleID

The organization wants to optimize AI-powered question answering.

Which columns should be embedded?

A. CreatedDate and ArticleID

B. Resolution only

C. ArticleTitle, ProblemDescription, and Resolution

D. ArticleID, Resolution, and CreatedDate

Correct Answer: C

Explanation

The title, problem description, and resolution contain the contextual information required for semantic retrieval.

Why the other answers are incorrect:

A: Dates and IDs provide no semantic value.
B: Using only the resolution omits important context.
D: IDs and dates should generally not be embedded.

Question 3

An HR department is building an AI assistant to help recruiters locate qualified candidates.

Which candidate profile columns are most appropriate for embeddings?

A. EmployeeID, HireDate, PayrollNumber

B. ResumeSummary, Skills, Certifications, Experience

C. Salary, EmployeeID, OfficeNumber

D. ManagerID, DepartmentID, HireDate

Correct Answer: B

Explanation

Resume summaries, skills, certifications, and experience contain rich natural language describing candidate qualifications.

Why the other answers are incorrect:

A: Administrative identifiers have no semantic value.
C: Salary and IDs are not useful for semantic similarity.
D: Organizational metadata does not describe expertise.

Question 4

A retail company stores product specifications as structured fields:

RAM = 32
CPU = Intel Core i9
GPU = RTX 4090

What is the best practice before generating embeddings?

A. Embed each numeric value separately.

B. Ignore the specification fields completely.

C. Store each field in separate vector columns.

D. Convert the structured values into descriptive natural language.

Correct Answer: D

Explanation

Embedding models understand natural language far better than isolated structured values. Converting structured data into descriptive text produces higher-quality embeddings.

Why the other answers are incorrect:

A: Individual values lose important context.
B: Specifications provide valuable search information.
C: Multiple separate embeddings reduce semantic completeness.

Question 5

A legal document repository contains these columns:

DocumentTitle
ContractText
Author
VersionNumber
LastModifiedDate

Which columns are most appropriate for embeddings?

A. DocumentTitle and ContractText

B. VersionNumber and LastModifiedDate

C. Author and VersionNumber

D. LastModifiedDate and Author

Correct Answer: A

Explanation

Titles and contract text provide meaningful semantic information for legal document retrieval.

Why the other answers are incorrect:

B: Version numbers and dates are operational metadata.
C: Author names generally contribute little to semantic similarity.
D: Dates and authors are rarely useful for AI retrieval.

Question 6

A company stores customer support tickets with these columns:

TicketID
Title
Description
Resolution
AssignedEngineer

Which column should generally not be included when generating embeddings?

A. Resolution

B. Description

C. AssignedEngineer

D. Title

Correct Answer: C

Explanation

The assigned engineer is administrative information and usually has no relationship to the semantic content of the issue.

Why the other answers are incorrect:

A: Resolutions provide valuable context.
B: Descriptions are one of the best embedding sources.
D: Titles summarize the issue.

Question 7

A company wants to reduce embedding generation costs while maintaining high-quality semantic search.

Which strategy is most appropriate?

A. Embed every column in every table.

B. Embed only columns containing meaningful business context and descriptive text.

C. Embed only numeric columns.

D. Embed every database row regardless of relevance.

Correct Answer: B

Explanation

Embedding only semantically meaningful columns minimizes storage, token usage, and generation costs while maintaining search quality.

Why the other answers are incorrect:

A: Creates unnecessary costs.
C: Numeric values rarely provide semantic meaning.
D: Irrelevant data wastes resources.

Question 8

A medical database contains the following fields:

PatientID
Diagnosis
Symptoms
TreatmentPlan
InsurancePolicyNumber

Which field should not normally be included in embeddings?

A. Symptoms

B. Diagnosis

C. InsurancePolicyNumber

D. TreatmentPlan

Correct Answer: C

Explanation

Insurance policy numbers are identifiers and contribute no semantic value to AI retrieval.

Why the other answers are incorrect:

A: Symptoms are highly descriptive.
B: Diagnoses contain important clinical meaning.
D: Treatment plans improve retrieval relevance.

Question 9

An organization notices that AI search results have become less accurate after adding several metadata columns to the embedding input.

What is the most likely cause?

A. Semantic quality decreased because irrelevant metadata diluted the embedding.

B. Embedding vectors became encrypted.

C. The embedding model requires additional indexes.

D. SQL Server cannot process metadata columns.

Correct Answer: A

Explanation

Adding irrelevant metadata introduces noise into the embedding, reducing its ability to represent the true semantic meaning of the content.

Why the other answers are incorrect:

B: Embeddings are not automatically encrypted by adding metadata.
C: Indexes improve retrieval speed, not embedding quality.
D: SQL Server can process metadata columns; they simply should not be embedded unnecessarily.

Question 10

A company stores lengthy product manuals exceeding the token limit of its embedding model.

What is the recommended approach?

A. Truncate every document to the first paragraph.

B. Increase the vector dimension.

C. Store the manuals without embeddings.

D. Divide the manuals into logical chunks before generating embeddings.

Correct Answer: D

Explanation

Chunking large documents allows each section to remain within model limits while preserving semantic meaning. It also improves retrieval because searches return only the most relevant portions of a document.

Why the other answers are incorrect:

A: Truncation discards valuable information.
B: Vector dimensions do not increase a model’s token limit.
C: Without embeddings, semantic search cannot be performed effectively.

DP-800 Exam Tips

For the exam, remember these key principles:

Embed descriptive, natural-language columns such as names, descriptions, summaries, articles, reviews, and documentation.
Avoid identifiers and operational metadata, including IDs, SKUs, timestamps, version numbers, and status codes.
Combine related descriptive columns (for example, title + category + description) into a single text input to provide richer context.
Convert structured specifications into natural language when appropriate to improve semantic understanding.
Exclude sensitive information unless absolutely required and permitted by your security policies.
Chunk large documents before generating embeddings to stay within model token limits and improve retrieval quality.
Review embedding quality periodically and refine the selected columns based on search relevance and user feedback.

Go to the DP-800 Exam Prep Hub main page

AI, Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Design and implement chunks for embeddings (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:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement models and embeddings
      --> Design and implement chunks for embeddings

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

One of the most important design decisions when building AI-enabled database solutions is determining how data should be divided before generating embeddings. This process, known as chunking, directly affects the quality of semantic search, Retrieval-Augmented Generation (RAG), recommendation systems, and AI-powered question answering.

Embedding models convert text into numerical vector representations that capture semantic meaning. However, they have input token limits and perform best when the input represents a coherent concept rather than an entire document. Consequently, documents are usually divided into manageable sections called chunks, and an embedding is generated for each chunk individually.

For the DP-800 exam, Microsoft expects candidates to understand how to design chunking strategies, determine appropriate chunk sizes, preserve context through overlap, store chunk metadata, and balance retrieval quality with storage and processing costs.

What Is Chunking?

Chunking is the process of dividing a document or other textual content into smaller units before generating embeddings.

Instead of embedding an entire 100-page document, the document is divided into logical sections such as:

Chapters
Sections
Paragraph groups
Pages
Individual articles
Code blocks
FAQ entries

Each chunk is embedded independently and stored in a vector database or SQL table.

For example:

Original document

			
Employee Handbook
    Chapter 1
    Chapter 2
    Chapter 3
    Chapter 4

		

After chunking:

			
Chunk 1 → Introduction
Chunk 2 → Employee Benefits
Chunk 3 → Leave Policies
Chunk 4 → Workplace Conduct
Chunk 5 → Remote Work
Chunk 6 → Security Policies

		

Each chunk receives its own embedding vector.

Why Chunk Documents?

Large Language Models and embedding models have practical limitations.

Reasons for chunking include:

Token limits
Improved semantic accuracy
Faster vector searches
Reduced hallucinations
Better retrieval precision
Lower processing costs
Easier document updates

Embedding an entire document often produces a vector that represents many unrelated topics simultaneously, reducing search accuracy.

Chunking in a Retrieval-Augmented Generation (RAG) System

A typical RAG workflow follows these steps:

			
Documents
      │
      ▼
Chunk Documents
      │
      ▼
Generate Embeddings
      │
      ▼
Store Vectors
      │
      ▼
User Query
      │
      ▼
Generate Query Embedding
      │
      ▼
Similarity Search
      │
      ▼
Retrieve Best Chunks
      │
      ▼
Provide Context to LLM
      │
      ▼
Generate Response

		

Without proper chunking, the retrieved context may be incomplete, overly broad, or irrelevant.

Characteristics of Good Chunks

An effective chunk should be:

Semantically coherent
Self-contained
Small enough for efficient retrieval
Large enough to preserve context
Easy to trace back to the original source
Consistent in formatting

Good chunks represent one primary idea or closely related concepts.

Example:

Good chunk:

Employees may work remotely up to three days each week with manager approval.

Poor chunk:

Employees may work remotely…

(ends halfway through explanation)

Incomplete chunks reduce retrieval quality.

Choosing an Appropriate Chunk Size

There is no universal chunk size.

Instead, chunk size depends on:

Document type
User questions
Embedding model limits
Retrieval strategy
Desired context

General guidelines:

Document Type	Typical Chunk Strategy
FAQ	One question and answer per chunk
Product documentation	One section per chunk
Legal contracts	One clause per chunk
Research papers	One subsection per chunk
Books	Several paragraphs per chunk
Source code	One function, class, or module per chunk
Knowledge articles	One topic per chunk

Microsoft generally emphasizes semantic chunking over arbitrary character counts.

Fixed-Length Chunking

The simplest strategy divides text into equal-sized pieces.

Example:

			
Chunk 1
1–500 characters
Chunk 2
501–1000 characters
Chunk 3
1001–1500 characters

		

Advantages:

Simple
Fast
Easy to automate

Disadvantages:

Breaks sentences
Splits ideas
Reduces semantic quality

Because it ignores meaning, fixed-length chunking is generally less effective than semantic approaches.

Semantic Chunking

Semantic chunking divides documents based on meaning rather than length.

Examples include:

Headings
Sections
Chapters
Topics
Complete paragraphs
Individual procedures
Entire FAQ entries

Example:

Instead of:

			
Characters 1–1000
Characters 1001–2000

Use:

			
Vacation Policy
Medical Leave
Parental Leave
Travel Reimbursement

Semantic chunking produces embeddings that more accurately represent individual concepts.

Sliding Window (Overlapping) Chunking

One common challenge occurs when important information spans the boundary between two chunks.

For example:

			
Chunk 1
Employees may work remotely
after manager approval.
Chunk 2
After manager approval,
employees must...

		

The phrase “after manager approval” belongs to both chunks.

To preserve context, systems use overlapping chunks.

Example:

			
Chunk 1
Sentence 1
Sentence 2
Sentence 3
Sentence 4
Chunk 2
Sentence 3
Sentence 4
Sentence 5
Sentence 6

		

The overlapping sentences help ensure continuity during retrieval.

Advantages of Overlapping Chunks

Overlap helps:

Preserve context
Improve semantic similarity
Prevent broken explanations
Improve RAG responses
Increase retrieval accuracy

Most enterprise RAG systems use overlap rather than completely independent chunks.

Drawbacks of Excessive Overlap

Too much overlap creates problems:

Duplicate embeddings
Larger storage requirements
Longer indexing time
Increased search costs
More duplicate search results

The overlap should be sufficient to maintain context without introducing excessive redundancy.

Chunking Structured Data

Not all embeddings originate from unstructured documents.

Structured SQL data can also be chunked.

Examples include:

Product descriptions
Customer support articles
Knowledge base records
Employee profiles
Medical summaries

Rather than combining multiple rows, each logical business record is typically embedded separately.

Example:

			
Products
Laptop A
Laptop B
Laptop C

Each product becomes its own chunk.

Chunking Source Code

Source code should not be chunked arbitrarily.

Preferred chunk boundaries include:

Functions
Methods
Classes
Modules
Stored procedures
SQL scripts

Example:

Instead of:

Characters 1–1000

Use:

			
CreateCustomer()
DeleteCustomer()
CalculateTax()
GenerateInvoice()

This preserves the logical structure of the code and improves semantic retrieval for developer-focused AI assistants.

Chunk Metadata

Every chunk should include metadata that enables accurate retrieval and traceability.

Common metadata includes:

Document ID
Chunk ID
Source file name
Page number
Section heading
Chunk sequence number
Creation date
Last modified date
Author
Security classification

Example:

Field	Value
Document	HR Handbook
Page	14
Section	Leave Policy
Chunk	5
Version	3.2

Metadata supports source attribution, document reconstruction, filtering, and citation generation.

Reconstructing Context

Many RAG applications retrieve more than one chunk for a user query.

For example:

			
Chunk 21
Chunk 22
Chunk 23

Because each chunk contains metadata indicating its position in the document, the application can present adjacent chunks together to provide richer context to the language model.

Testing Chunk Quality

Designing chunking strategies is an iterative process. After implementing a strategy, evaluate it using representative queries and measure retrieval quality.

Common evaluation criteria include:

Retrieval precision – Are the returned chunks relevant to the query?
Retrieval recall – Are important chunks consistently found?
Context completeness – Does each chunk contain enough information to answer the question?
Duplicate retrieval – Are overlapping chunks causing redundant results?
Latency – Does the chunking strategy affect search performance?
Storage efficiency – How much additional storage is required for embeddings and metadata?

Testing with real-world questions often reveals whether chunks are too large, too small, or improperly aligned with document structure.

Best Practices

Microsoft recommends following several best practices when designing chunks for embeddings:

Prefer semantic chunking over fixed-length splitting whenever possible.
Keep each chunk focused on a single topic or concept.
Use overlap to preserve context across chunk boundaries.
Preserve document structure by chunking at headings, sections, or clauses.
Store comprehensive metadata with every chunk.
Choose chunk sizes appropriate for the document type and expected user queries.
Avoid creating chunks that are so small they lose context or so large they dilute semantic meaning.
Re-evaluate chunking strategies as documents, embedding models, or application requirements evolve.
Test retrieval quality with representative workloads before deploying to production.
Balance retrieval accuracy against storage costs, indexing time, and search performance.

DP-800 Exam Tips

For the DP-800 exam, you should understand how chunk design influences the effectiveness of AI-enabled database solutions. Be prepared to compare fixed-length, semantic, and overlapping chunking strategies and recognize when each is appropriate. Expect scenario-based questions that ask you to optimize retrieval quality, preserve context, reduce hallucinations, or improve the performance of Retrieval-Augmented Generation (RAG) systems. Also know the importance of metadata, chunk boundaries, and testing strategies in building scalable, production-ready embedding solutions.

Practice Exam Questions

Question 1

You are creating a Retrieval-Augmented Generation (RAG) solution for an organization’s HR policy documents. Each document averages 40 pages.

What is the primary reason for dividing the documents into chunks before generating embeddings?

A. SQL databases cannot store documents larger than 1 MB.

B. Embedding models only support numeric data.

C. Smaller chunks improve semantic retrieval by capturing focused context.

D. Chunking compresses documents into fewer tokens.

Answer: C

Explanation:
Embedding models generate vector representations for pieces of text. Smaller, semantically coherent chunks produce embeddings that represent specific concepts, making similarity searches much more accurate than embedding an entire document.

Question 2

A development team is designing chunk sizes for technical manuals.

Which chunking strategy generally produces the best retrieval quality?

A. Split documents into semantically meaningful sections of moderate length

B. One chunk for every document

C. Split every five characters

D. Create one chunk for every paragraph regardless of context

Answer: A

Explanation:
Chunks should balance context and specificity. Semantically meaningful sections preserve enough information for accurate retrieval without becoming overly broad.

Question 3

Why are overlapping chunks commonly used when generating embeddings?

A. To reduce storage requirements

B. To eliminate duplicate vectors

C. To preserve context across chunk boundaries

D. To increase SQL query speed

Answer: C

Explanation:
Without overlap, important information that spans chunk boundaries can be lost. Overlapping text ensures that concepts appearing near boundaries remain available during retrieval.

Question 4

Which factor has the greatest influence on selecting an appropriate chunk size?

A. The SQL Server version

B. The font used in the original document

C. The amount of database memory

D. The type of content and expected user queries

Answer: D

Explanation:
Different document types require different chunk sizes. FAQs, manuals, legal documents, and source code each benefit from different chunking strategies based on how users search for information.

Question 5

A company stores product manuals that include headings, tables, and explanatory paragraphs.

Which chunking strategy is most appropriate?

A. Ignore document structure and split every 1,000 characters.

B. Chunk according to logical document sections while preserving headings.

C. Generate one embedding for every sentence.

D. Combine all manuals into one embedding.

Answer: B

Explanation:
Preserving document structure helps maintain semantic meaning and improves retrieval quality because headings provide valuable contextual information.

Question 6

What is a disadvantage of creating extremely small chunks?

A. Higher SQL licensing costs

B. More accurate semantic understanding

C. Loss of surrounding context during retrieval

D. Lower storage requirements

Answer: C

Explanation:
Very small chunks may not contain enough surrounding information for an LLM to interpret their meaning correctly, resulting in lower-quality retrieval.

Question 7

When implementing chunking for a RAG application, which metadata should typically be stored alongside each embedding?

A. SQL login credentials

B. Original document identifier and chunk position

C. Azure subscription ID

D. CPU utilization statistics

Answer: B

Explanation:
Metadata allows applications to identify the source document, reconstruct surrounding context, provide citations, and retrieve neighboring chunks when generating responses.

Question 8

A legal department requires every retrieved answer to reference complete contractual clauses.

Which chunking strategy is most appropriate?

A. Randomly divide text every 500 characters.

B. Generate one embedding for the entire contract.

C. Chunk by individual words.

D. Chunk according to clause boundaries with slight overlap.

Answer: D

Explanation:
Legal documents rely on clearly defined clauses. Chunking by clause preserves legal meaning while overlap prevents loss of context between adjacent sections.

Question 9

Which statement best describes the relationship between chunk size and retrieval performance?

A. Larger chunks always produce better search results.

B. Smaller chunks always eliminate hallucinations.

C. There is an optimal balance between context size and retrieval precision.

D. Chunk size has no impact on vector search.

Answer: C

Explanation:
Chunks that are too large reduce retrieval precision, while chunks that are too small lose context. Effective systems balance these competing factors.

Question 10

During testing, users report that retrieved passages frequently stop in the middle of explanations.

Which modification would most likely improve retrieval quality?

A. Increase overlap between adjacent chunks.

B. Reduce the embedding vector dimensions.

C. Remove document metadata.

D. Convert embeddings to integers.

Answer: A

Explanation:
Increasing overlap preserves continuity across chunk boundaries, allowing retrieval systems to return more complete passages and improving the quality of generated responses.

Go to the DP-800 Exam Prep Hub main page

azure, Databases, DP-800, Microsoft Certification, SQL August 1, 2026August 1, 2026

Recommend Azure Monitor configurations, including Application Insights and Log Analytics (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%)
   --> Integrate SQL solutions with Azure services
      --> Recommend Azure Monitor configurations, including Application Insights and Log Analytics

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

Modern SQL applications extend far beyond storing and retrieving data. Today’s applications often expose APIs, integrate with AI services, support microservices, and serve users around the world. As systems become more distributed, monitoring application health, database performance, security, and user activity becomes increasingly important.

Azure Monitor is Microsoft’s unified monitoring platform for collecting, analyzing, visualizing, and acting upon telemetry from Azure resources, applications, virtual machines, containers, databases, and on-premises environments. For SQL AI developers preparing for the DP-800 certification, understanding Azure Monitor—and specifically Application Insights and Log Analytics—is essential for designing highly observable, reliable, and performant database solutions.

The DP-800 exam expects candidates to know when and how to recommend monitoring configurations that support troubleshooting, performance optimization, security monitoring, operational excellence, and AI-enabled database applications.

Understanding Azure Monitor

Azure Monitor is a comprehensive monitoring service that provides:

Metrics collection
Log collection
Distributed tracing
Alerting
Dashboards
Workbooks
Performance analytics
Diagnostic settings
Resource health monitoring

Azure Monitor collects telemetry from virtually every Azure service, including:

Azure SQL Database
Azure SQL Managed Instance
SQL Server on Azure VM
Azure App Service
Azure Functions
Azure Kubernetes Service (AKS)
Azure Container Apps
Data API Builder (DAB)
Azure OpenAI
Azure AI Search
Microsoft Fabric
Virtual Machines

Azure Monitor Architecture

A simplified monitoring architecture looks like this:

			
Applications
       │
Databases
       │
Azure Services
       │
Diagnostic Settings
       │
Azure Monitor
       │
 ┌───────────────┐
 │ Metrics       │
 │ Logs          │
 │ Traces        │
 │ Alerts        │
 └───────────────┘
       │
Application Insights
       │
Log Analytics
       │
Dashboards / Alerts / Workbooks

		

Core Azure Monitor Components

Azure Monitor consists of several integrated services.

Metrics

Metrics are numerical measurements collected at regular intervals.

Examples include:

CPU utilization
Memory usage
DTU utilization
vCore utilization
Storage usage
Active sessions
Requests per second
Response times

Metrics are lightweight and optimized for near real-time monitoring.

Logs

Logs contain detailed event information.

Examples:

SQL errors
Login attempts
Application exceptions
API requests
Deadlocks
Security events
Query execution details

Logs support historical analysis and forensic investigations.

Alerts

Azure Monitor alerts notify administrators when predefined conditions occur.

Examples include:

CPU > 80%
Database unavailable
Deadlock detected
Slow API response
Failed deployments
Authentication failures

Alerts can trigger:

Email
SMS
Azure Functions
Logic Apps
Webhooks
ITSM integrations

Dashboards

Dashboards combine metrics and logs into a centralized monitoring view.

Typical dashboard elements include:

Database performance
API latency
Error rates
Availability
Query duration
Resource utilization

What Is Application Insights?

Application Insights is an Azure Monitor feature designed to monitor applications.

It automatically collects telemetry such as:

HTTP requests
Dependencies
SQL calls
Exceptions
Page views
Response times
Availability tests
Distributed traces

Application Insights helps developers understand application behavior rather than infrastructure performance alone.

Telemetry Collected by Application Insights

Application Insights automatically captures:

Requests

Every REST or GraphQL request can be monitored.

Information includes:

URL
Duration
Response code
Success or failure
Timestamp

Dependencies

Dependencies include calls made by applications to external resources.

Examples:

Azure SQL Database
Azure OpenAI
Azure AI Search
Storage Accounts
REST APIs
Service Bus
Cosmos DB

Dependency tracking identifies slow downstream services.

Exceptions

Application Insights records:

SQL exceptions
.NET exceptions
Java exceptions
Node.js exceptions
Python exceptions

Developers can investigate stack traces and failure frequency.

Performance Counters

Examples include:

CPU
Memory
Thread count
Request queue
Process utilization

Availability Tests

Availability tests periodically verify that applications remain accessible.

Types include:

URL ping tests
Multi-step web tests (legacy)
Standard availability tests

Useful for:

REST APIs
Data API Builder endpoints
Web applications

Distributed Tracing

Modern applications often involve:

Application

↓

REST API

↓

Data API Builder

↓

Azure SQL Database

↓

Azure OpenAI

↓

Azure AI Search

Application Insights correlates all these operations into a single transaction, allowing developers to trace requests end-to-end.

Benefits include:

Root cause analysis
Performance bottleneck identification
Dependency tracking
Service latency analysis

What Is Log Analytics?

Log Analytics is Azure Monitor’s centralized log repository and query engine.

Logs from multiple Azure resources are stored in a Log Analytics Workspace.

Examples include:

SQL diagnostics
Application Insights logs
Azure Activity Logs
VM logs
Azure Firewall logs
Microsoft Defender logs

Log Analytics Workspaces

A Log Analytics Workspace stores telemetry collected across Azure.

Benefits include:

Centralized logging
Long-term retention
Cross-resource analysis
Kusto Query Language (KQL) support
Security investigations

Multiple Azure resources can send data to a single workspace.

Kusto Query Language (KQL)

Log Analytics uses KQL for querying data.

Example:

			
requests
| where success == false
| order by timestamp desc

Example:

			
dependencies
| summarize avg(duration) by target

Example:

			
exceptions
| summarize count() by type

The DP-800 exam expects familiarity with Log Analytics and awareness that KQL is the query language used to analyze collected telemetry.

Diagnostic Settings

Azure resources send telemetry through Diagnostic Settings.

Diagnostic Settings determine where logs are stored.

Possible destinations include:

Log Analytics Workspace
Storage Account
Event Hub
Partner solutions

For Azure SQL Database, diagnostic logs commonly include:

SQLInsights
Automatic tuning
Deadlocks
Query Store Runtime Statistics
Errors
Wait statistics
Timeouts

Monitoring Azure SQL Database

Important Azure SQL metrics include:

CPU percentage
DTU percentage
vCore utilization
Data IO
Log IO
Storage percentage
Sessions
Workers
Connections

These metrics help identify capacity issues before users experience failures.

Monitoring Data API Builder (DAB)

DAB deployments should enable:

Request logging
Response times
Authentication failures
GraphQL execution errors
REST endpoint usage
SQL dependency tracking

Application Insights provides excellent visibility into DAB performance.

Monitoring AI-Enabled SQL Applications

Applications integrating Azure OpenAI or Azure AI Search should monitor:

API latency
Request failures
Token usage (where available)
Dependency duration
Timeout frequency
Retry attempts

Dependency tracking in Application Insights helps identify whether delays originate from the database or external AI services.

Azure Monitor Alerts

Common production alerts include:

Condition	Alert
CPU > 80%	Warning
DTU > 90%	Critical
Deadlock detected	Critical
Failed SQL login	Security
API response > 2 seconds	Warning
Storage > 85%	Capacity alert
Application unavailable	Critical

Alerts should prioritize actionable events while minimizing alert fatigue.

Workbooks

Azure Monitor Workbooks create interactive reports using:

Metrics
Logs
Charts
Maps
Tables
KQL queries

Typical workbook examples:

SQL performance dashboard
API performance trends
AI service latency
Database growth analysis
Security monitoring

Retention Policies

Organizations should configure log retention based on:

Compliance requirements
Storage costs
Investigation needs
Security policies

Short retention reduces storage costs, while longer retention supports audits and forensic analysis.

Best Practices for Monitoring SQL Solutions

Microsoft recommends:

Enable Application Insights for applications.
Send diagnostic logs to Log Analytics.
Enable distributed tracing.
Configure proactive alerts.
Monitor dependencies.
Use dashboards for operational visibility.
Review telemetry regularly.
Monitor failed authentication attempts.
Monitor slow SQL queries.
Use KQL for troubleshooting.

Common DP-800 Exam Scenarios

You may be asked to determine:

Which monitoring service collects application telemetry.
When to use Application Insights versus Log Analytics.
How to troubleshoot slow SQL queries.
Which service stores centralized logs.
How to monitor Data API Builder.
Which service provides distributed tracing.
How to configure alerts for production systems.
Which Azure Monitor feature supports long-term log analysis.

DP-800 Exam Tips

Remember these key points:

Azure Monitor is the overarching monitoring platform.
Application Insights monitors application performance and dependencies.
Log Analytics centralizes logs and supports KQL queries.
Diagnostic Settings send Azure resource logs to destinations such as Log Analytics.
Application Insights supports distributed tracing.
Azure Monitor Alerts automate operational notifications.
Workbooks provide customizable dashboards and reports.
Azure SQL Database metrics help identify capacity and performance issues.
Use Application Insights to monitor Data API Builder and AI-enabled applications.
KQL is the primary language for querying Log Analytics data.

Practice Exam Questions

Question 1

A company wants to monitor the performance of a .NET application that accesses Azure SQL Database through Data API Builder. The solution must automatically capture request latency, SQL dependencies, exceptions, and distributed traces.

Which Azure service should you recommend?

A. Azure Storage Explorer

B. Azure Monitor Metrics

C. Application Insights

D. Azure Advisor

Answer: C

Explanation: Application Insights is designed to monitor application performance by collecting requests, dependencies, exceptions, distributed traces, and performance telemetry automatically.

Question 2

Your organization needs a centralized repository for logs collected from Azure SQL Database, Azure App Service, Azure Functions, and Application Insights.

Which Azure service should you use?

A. Azure Log Analytics Workspace

B. Azure Backup

C. Azure Key Vault

D. Azure Files

Answer: A

Explanation: A Log Analytics Workspace provides centralized storage and analysis for telemetry collected from multiple Azure resources.

Question 3

An administrator wants to query failed HTTP requests over the past 24 hours using Kusto Query Language (KQL).

Which Azure service provides this capability?

A. Azure Portal Metrics Explorer

B. Azure Cost Management

C. Azure Monitor Alerts

D. Log Analytics

Answer: D

Explanation: Log Analytics stores log data and enables querying through Kusto Query Language (KQL) for detailed analysis and troubleshooting.

Question 4

A development team wants to receive an email whenever Azure SQL Database CPU utilization exceeds 85% for more than five minutes.

Which Azure Monitor feature should be configured?

A. Diagnostic Settings

B. Azure Policy

C. Azure Monitor Alerts

D. Application Insights Availability Tests

Answer: C

Explanation: Azure Monitor Alerts evaluate metric or log conditions and can notify administrators through email, SMS, webhooks, or automated workflows.

Question 5

Which Azure Monitor feature is responsible for routing Azure SQL Database diagnostic logs to a Log Analytics Workspace?

A. Azure Monitor Metrics

B. Diagnostic Settings

C. Availability Tests

D. Resource Locks

Answer: B

Explanation: Diagnostic Settings configure where Azure resource logs are sent, including Log Analytics Workspaces, Storage Accounts, and Event Hubs.

Question 6

A developer needs to identify which downstream dependency is causing increased response times in an AI-enabled application.

Which Application Insights capability should they use?

A. Backup Reports

B. Dependency Tracking

C. Cost Analysis

D. Resource Graph

Answer: B

Explanation: Dependency Tracking records calls to Azure SQL Database, Azure OpenAI, Azure AI Search, REST APIs, and other services, making it easier to identify performance bottlenecks.

Question 7

Your organization wants to monitor whether a public REST endpoint remains accessible from multiple geographic regions.

Which Application Insights feature is most appropriate?

A. Live Metrics

B. Snapshot Debugger

C. Availability Tests

D. Smart Detection

Answer: C

Explanation: Availability Tests periodically check endpoint accessibility and response times from multiple locations, helping detect outages before users report them.

Question 8

Which Azure Monitor capability provides end-to-end visibility by correlating requests across multiple services such as Data API Builder, Azure SQL Database, and Azure OpenAI?

A. Azure Advisor

B. Distributed Tracing

C. Cost Management

D. Azure Policy

Answer: B

Explanation: Distributed Tracing correlates operations across application components, enabling developers to follow a single request through multiple services and identify performance bottlenecks.

Question 9

A database administrator wants to build an interactive dashboard that combines charts, tables, KQL queries, and performance metrics into a single operational view.

Which Azure Monitor feature should be recommended?

A. Azure Workbooks

B. Azure Bastion

C. Microsoft Purview

D. Azure Resource Graph

Answer: A

Explanation: Azure Workbooks create interactive monitoring dashboards that combine metrics, logs, charts, visualizations, and KQL queries for operational reporting.

Question 10

An organization wants to monitor a production SQL solution while minimizing unnecessary notifications that could overwhelm administrators.

Which recommendation represents a monitoring best practice?

A. Generate alerts for every informational event.

B. Disable monitoring during peak usage.

C. Configure actionable alerts based on meaningful thresholds and business impact.

D. Collect only CPU metrics.

Answer: C

Explanation: Effective monitoring focuses on actionable alerts that indicate genuine operational issues. Carefully chosen thresholds reduce alert fatigue while ensuring that critical events receive timely attention.

Go to the DP-800 Exam Prep Hub main page

azure, Data Development, Databases, DP-800, Microsoft Certification August 1, 2026August 1, 2026

Configure and implement DAB deployment (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%)
   --> Integrate SQL solutions with Azure services
      --> Configure and implement DAB deployment

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

Modern applications frequently require secure, scalable APIs to expose database objects without developers having to build and maintain extensive backend code. Data API Builder (DAB) is a Microsoft open-source runtime that automatically exposes Azure SQL Database, SQL Server, Azure Cosmos DB, PostgreSQL, and MySQL databases through REST and GraphQL endpoints.

While creating DAB configuration files is important, equally critical is deploying DAB securely and reliably into development, testing, staging, and production environments. The DP-800 exam expects SQL AI Developers to understand how DAB fits into CI/CD pipelines, containerized environments, Azure App Service, Azure Container Apps, Kubernetes, authentication systems, and infrastructure automation.

Understanding deployment strategies helps ensure that APIs remain secure, available, scalable, and maintainable.

What Is Data API Builder Deployment?

Deployment refers to the process of publishing the DAB runtime together with its configuration so that applications can consume database APIs.

A deployment includes:

Installing the DAB runtime
Providing the configuration file
Supplying environment variables
Configuring authentication
Connecting to databases
Deploying to the chosen hosting platform
Configuring monitoring
Configuring scaling
Managing updates

Unlike traditional applications, DAB is largely configuration-driven. Most deployments involve changing configuration rather than application code.

Common Deployment Targets

Microsoft supports several deployment options.

Local Development

Developers often begin locally using:

Windows
Linux
macOS

Example:

dab start

Advantages include:

Fast testing
Easy debugging
Local SQL Server integration
Rapid API validation

Local deployments should never expose production credentials.

Azure App Service

Azure App Service is one of the simplest production deployment options.

Benefits include:

Fully managed hosting
HTTPS enabled
Automatic scaling
Managed Identity
Deployment slots
Azure Monitor integration

Typical architecture:

			
Client
     |
Azure App Service
     |
Data API Builder
     |
Azure SQL Database

		

Azure Container Apps

Many organizations package DAB inside a Docker container.

Advantages include:

Container portability
Autoscaling
Microservices architecture
Revision management
Simple CI/CD integration

Container Apps are becoming increasingly common for cloud-native solutions.

Azure Kubernetes Service (AKS)

Larger organizations often deploy DAB using Kubernetes.

Benefits include:

High availability
Rolling updates
Horizontal scaling
Container orchestration
Service mesh integration

Although AKS offers the most flexibility, it is also the most complex deployment option.

Docker

DAB is commonly deployed as a Docker container.

Example Dockerfile:

			
FROM mcr.microsoft.com/data-api-builder
COPY dab-config.json /App/

Benefits include:

Consistent environments
Easy version control
Portable deployments
Works across cloud providers

DAB Configuration During Deployment

Every deployment needs access to:

dab-config.json
Database connection information
Authentication settings
Runtime configuration

The configuration file should be packaged together with the deployment or mounted as a configuration volume.

Environment Variables

Production deployments should avoid hardcoded settings.

Instead, use environment variables.

Examples:

			
SQL_CONNECTION_STRING
AZURE_CLIENT_ID
AZURE_TENANT_ID
JWT_AUDIENCE

Benefits include:

Improved security
Easier environment changes
Better DevOps automation

Secure Connection Strings

Never store credentials directly inside configuration files.

Instead use:

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

Example:

Instead of:

Password=MyPassword123

Use:

Password=${SQL_PASSWORD}

Managed Identity

One of Microsoft’s recommended deployment practices is using Managed Identity.

Instead of storing SQL credentials:

			
Application
    |
Managed Identity
    |
Azure SQL

		

Benefits include:

No stored passwords
Automatic credential rotation
Azure AD authentication
Reduced attack surface

DP-800 heavily emphasizes Managed Identity.

Authentication Configuration

Production deployments usually configure authentication providers such as:

Microsoft Entra ID
JWT providers
OAuth 2.0
Static development authentication (development only)

Authentication should be enabled before exposing APIs publicly.

HTTPS

Production DAB deployments should always use HTTPS.

Benefits include:

Encrypts traffic
Protects authentication tokens
Prevents packet interception
Supports secure REST and GraphQL endpoints

Azure App Service enables HTTPS automatically.

Reverse Proxies

Many production deployments place DAB behind:

Azure API Management
Azure Front Door
Azure Application Gateway
NGINX
Traefik

Advantages:

Centralized security
Rate limiting
Caching
Authentication
Request logging

CI/CD Deployment

DAB deployments fit naturally into DevOps pipelines.

Typical pipeline:

			
Developer
      |
Git Repository
      |
Build Pipeline
      |
Unit Tests
      |
Create Docker Image
      |
Deploy
      |
Smoke Tests
      |
Production

		

Azure DevOps Deployment

Typical stages include:

Restore dependencies
Build
Validate DAB configuration
Build container
Push image
Deploy
Run validation tests

GitHub Actions

GitHub Actions commonly automate DAB deployment.

Example workflow:

			
Push
↓
Build
↓
Run Tests
↓
Create Container
↓
Publish Image
↓
Deploy Azure

		

Infrastructure as Code

Many organizations deploy DAB using:

Bicep
ARM templates
Terraform

Benefits include:

Repeatability
Version control
Consistent infrastructure
Automated provisioning

Configuration Validation

Before deployment, validate:

JSON syntax
Entity definitions
Authentication settings
Database connectivity
GraphQL relationships
Stored procedure mappings

Validation reduces deployment failures.

Monitoring

Production deployments should include monitoring.

Useful Azure services include:

Azure Monitor
Application Insights
Log Analytics
Azure Diagnostics

Monitor:

Request latency
Errors
Authentication failures
API throughput
CPU
Memory

Logging

Logs assist troubleshooting.

Typical events:

Startup failures
Invalid requests
Authentication failures
Database connection errors
SQL execution errors

Logs should never expose sensitive information.

Scaling DAB

Scaling depends on the hosting platform.

Azure App Service

Scale up
Scale out

Azure Container Apps

Autoscaling
Revision-based deployments

AKS

Horizontal Pod Autoscaler
Multiple replicas

High Availability

Production deployments commonly use:

Multiple DAB instances
Load balancers
Regional redundancy
Health probes

These reduce downtime.

Deployment Slots

Azure App Service supports deployment slots.

Example:

			
Production
↓
Staging Slot
↓
Validation
↓
Swap

		

Benefits:

Zero-downtime deployment
Easy rollback
Safe production updates

Versioning

Multiple API versions may run simultaneously.

Example:

			
v1
v2
v3

Benefits include:

Backward compatibility
Easier client migration
Controlled feature rollout

Rollback Strategy

Every deployment should support rollback.

Common methods:

Previous Docker image
Previous deployment slot
Previous Git tag
Previous release pipeline

Rollback minimizes production risk.

Security Best Practices

Recommended practices include:

HTTPS only
Managed Identity
Least privilege
Azure Key Vault
Authentication enabled
Authorization configured
Secure secrets
Monitor logs
Enable auditing
Disable unused endpoints

DP-800 Exam Tips

Remember these key points:

DAB deployments commonly use Azure App Service, Azure Container Apps, Docker, or AKS.
Avoid hardcoded secrets.
Prefer Managed Identity over SQL usernames/passwords.
Store secrets in Azure Key Vault.
Automate deployments using GitHub Actions or Azure DevOps.
Validate configurations before deployment.
Use deployment slots to minimize downtime.
Monitor deployments with Azure Monitor and Application Insights.
Use HTTPS for every production deployment.
Implement rollback strategies.

Practice Exam Questions

Question 1

Your organization wants to deploy Data API Builder with automatic operating system patching, built-in HTTPS, deployment slots, and minimal administrative overhead.

Which deployment target best meets these requirements?

A. Azure Kubernetes Service

B. Azure App Service

C. Self-managed virtual machine

D. Docker Desktop

Answer: B

Explanation: Azure App Service is a fully managed platform that provides HTTPS, automatic OS maintenance, deployment slots, autoscaling, and simplified application hosting.

Question 2

A company wants to eliminate database passwords from its DAB deployment while securely authenticating to Azure SQL Database.

What is the recommended authentication method?

A. Store SQL credentials in Git

B. Use SQL Authentication with encrypted passwords

C. Use Azure Managed Identity

D. Create a shared administrator account

Answer: C

Explanation: Managed Identity removes the need to store credentials, uses Microsoft Entra ID authentication, and automatically manages credential rotation.

Question 3

Which deployment practice provides the greatest protection for database connection strings?

A. Embed the connection string in the DAB configuration file

B. Store the connection string in application source code

C. Save credentials in a shared documentation file

D. Store secrets in Azure Key Vault and reference them during deployment

Answer: D

Explanation: Azure Key Vault securely stores secrets outside application code and integrates with Managed Identity and deployment pipelines.

Question 4

During deployment, a development team wants every code commit to automatically build, validate, test, and deploy DAB.

Which approach should they use?

A. Manual deployment using PowerShell

B. SQL Server Management Studio

C. A CI/CD pipeline using GitHub Actions or Azure DevOps

D. Windows Task Scheduler

Answer: C

Explanation: CI/CD pipelines automate builds, testing, validation, packaging, and deployment, reducing manual effort and deployment errors.

Question 5

Why should production DAB deployments use HTTPS?

A. It increases SQL query speed.

B. It compresses GraphQL responses.

C. It encrypts network communication between clients and the API.

D. It eliminates authentication requirements.

Answer: C

Explanation: HTTPS protects sensitive information such as authentication tokens and API traffic from interception during transmission.

Question 6

Which Azure service is specifically designed to collect application telemetry, performance metrics, and diagnostics for deployed DAB applications?

A. Azure Application Insights

B. Azure Storage Explorer

C. Azure Bastion

D. Azure Data Factory

Answer: A

Explanation: Application Insights provides monitoring, distributed tracing, diagnostics, performance metrics, and failure analysis for deployed applications.

Question 7

A team wants to release a new DAB version without interrupting production users and retain the ability to roll back immediately if problems occur.

Which Azure App Service feature should they use?

A. Reserved instances

B. Deployment slots

C. Availability zones

D. Geo-replication

Answer: B

Explanation: Deployment slots allow applications to be validated before swapping into production and enable quick rollback if issues are discovered.

Question 8

Why are environment variables commonly used during DAB deployment?

A. They automatically optimize SQL queries.

B. They eliminate authentication requirements.

C. They reduce GraphQL response sizes.

D. They separate configuration from application code and simplify deployment across environments.

Answer: D

Explanation: Environment variables allow different settings for development, testing, and production without modifying the application or configuration files.

Question 9

Which deployment platform provides the highest level of container orchestration and scalability for large enterprise DAB deployments?

A. Azure Kubernetes Service

B. Azure App Service

C. Windows Server

D. Docker Desktop

Answer: A

Explanation: AKS offers advanced orchestration, automatic scaling, rolling updates, service discovery, and high availability for enterprise containerized workloads.

Question 10

Before promoting a DAB deployment to production, what validation activity is most important?

A. Disable authentication temporarily.

B. Increase CPU resources.

C. Validate configuration files, authentication settings, and database connectivity.

D. Remove monitoring to improve performance.

Answer: C

Explanation: Validating configuration, connectivity, and authentication helps prevent deployment failures and ensures the API functions correctly before reaching production users.

Go to the DP-800 Exam Prep Hub main page

azure, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Expose database objects, stored procedures, and views, including GraphQL relationships (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%)
   --> Integrate SQL solutions with Azure services
      --> Expose database objects, stored procedures, and views, including GraphQL relationships

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

Modern applications rarely communicate directly with a database. Instead, they interact with APIs that expose only the data and operations that applications require. Microsoft’s Data API builder (DAB) provides a secure and efficient way to expose Azure SQL Database, Azure SQL Managed Instance, SQL Server, and Azure Database for PostgreSQL as REST and GraphQL APIs without requiring developers to build custom API services.

One of the primary responsibilities of a SQL AI Developer is deciding which database objects should be exposed, how they should be exposed, and how relationships between entities should be represented, particularly in GraphQL.

For the DP-800 exam, candidates should understand how to expose:

Tables
Views
Stored procedures
Relationships between entities
GraphQL navigation
REST resources
Security considerations
Performance considerations

Why Expose Database Objects?

Instead of allowing applications to connect directly to a database, organizations commonly expose selected database objects through APIs because APIs provide:

Better security
Controlled access
Versioning
Authentication
Authorization
Business logic abstraction
Simplified client development

Rather than allowing direct SQL access, applications interact with HTTP endpoints such as:

GET /api/Products

or GraphQL queries like:

			
query {
  products {
    ProductID
    Name
    Price
  }
}

		

Objects That Can Be Exposed

Microsoft Data API builder can expose several database object types.

1. Tables

Tables are the most common objects exposed.

Example:

			
Products
Customers
Orders
Employees

Each table becomes an entity.

Example DAB configuration:

			
{
  "entities": {
    "Products": {
      "source": "Products"
    }
  }
}

		

REST endpoints generated:

			
GET /api/Products
POST /api/Products
PATCH /api/Products
DELETE /api/Products

GraphQL automatically generates:

			
products
product_by_pk

and corresponding mutations.

2. Views

Views provide a secure way to expose pre-filtered or joined data.

Example:

vwSalesSummary

Instead of exposing many tables, clients consume the view.

Benefits include:

Simplified queries
Hidden table structure
Security abstraction
Read-only reporting

Example:

			
CustomerName
OrderCount
TotalSales

instead of requiring joins.

Views are especially useful for reporting applications.

3. Stored Procedures

Stored procedures expose business logic rather than raw tables.

Example:

EXEC usp_CreateOrder

Instead of allowing clients to insert rows manually.

Advantages include:

Validation
Business rules
Transactions
Consistent processing

Data API builder supports stored procedures as API operations.

Example REST endpoint:

POST /api/CreateOrder

Why Use Stored Procedures?

Stored procedures provide:

Better security
Centralized business rules
Reduced network traffic
Transaction handling
Parameter validation

Example:

Instead of:

			
Insert Order
Insert Items
Update Inventory
Calculate Discount
Commit Transaction

		

The application calls:

CreateOrder()

The stored procedure performs every operation safely.

Exposing Views vs Tables

Tables	Views
Raw data	Processed data
Often updateable	Often read-only
Complete schema	Simplified schema
Less abstraction	Greater abstraction
Better for CRUD	Better for reporting

Exposing Stored Procedures

Stored procedures typically become REST POST operations because they execute actions.

Example:

			
POST
/api/ProcessPayment

Input:

			
{
   "OrderID":1054
}

The procedure performs the transaction.

GraphQL Relationships

One of GraphQL’s greatest advantages is navigating relationships between entities.

Instead of making several REST calls:

			
Customers
Orders
OrderDetails

GraphQL can retrieve all related information in one request.

Example:

			
query {
  customers {
    CustomerName
    orders {
        OrderID
        OrderDate
        orderDetails {
             ProductName
             Quantity
        }
    }
}

		

GraphQL traverses relationships automatically.

Understanding Relationships

Suppose the database contains:

			
Customers
Orders
Products
OrderDetails

Relationships:

			
Customer
   |
   | 1:M
   |
Orders
   |
   | 1:M
   |
OrderDetails
   |
   | M:1
   |
Products

		

GraphQL follows these relationships naturally.

One-to-Many Relationships

Example:

Customer

↓

Orders

Example query:

			
query{
 customers{
   CustomerName
   orders{
      OrderID
      OrderDate
   }
 }
}

		

The response includes each customer’s orders.

Many-to-One Relationships

Example:

OrderDetails

↓

Product

			
query{
 orderDetails{
    Quantity
    product{
       Name
       Price
    }
 }
}

		

Many-to-Many Relationships

Many-to-many relationships are typically implemented through junction tables.

Example:

			
Students
Courses
StudentCourses

GraphQL can expose navigation through the junction table.

REST vs GraphQL for Relationships

REST

			
GET Customers
GET Orders
GET OrderDetails

Multiple requests required.

GraphQL

One query retrieves everything.

Advantages:

Reduced network traffic
Less over-fetching
Less under-fetching
Better performance

Relationship Configuration in Data API Builder

Relationships are defined inside the configuration.

Example concept:

			
Customers
   hasMany
Orders

and

			
Orders
belongsTo
Customers

This allows nested GraphQL queries.

CRUD Support

Depending on configuration, exposed entities may support:

Create

POST

Read

GET

Update

			
PUT
PATCH

Delete

DELETE

Not every entity must support every operation.

For example:

Views

Read Only

Tables

Read + Write

Restricting Exposed Objects

Best practice is not to expose every table.

Expose only:

Required tables
Required views
Required procedures

Avoid exposing:

Audit tables
Internal configuration
Security tables
Temporary tables
Logging tables

Least privilege always applies.

Security Considerations

When exposing database objects:

Require HTTPS
Use Microsoft Entra authentication
Apply least privilege
Use role-based authorization
Expose only necessary objects
Validate procedure parameters
Avoid exposing sensitive columns
Audit endpoint usage

Performance Considerations

Good API design includes:

Return only needed fields
Use pagination
Cache reference data
Optimize SQL queries
Index frequently queried columns
Avoid unnecessary nested GraphQL queries
Use views for complex reporting

Common DP-800 Exam Tips

Know when to expose:

Object	Typical Use
Table	CRUD operations
View	Reporting and simplified queries
Stored Procedure	Business logic and transactions
GraphQL Relationship	Nested related data
REST Endpoint	Resource-oriented operations

Summary

For the DP-800 exam, you should understand that Data API builder can expose tables, views, and stored procedures as secure REST and GraphQL endpoints. Tables are commonly used for CRUD operations, views simplify reporting and hide underlying schemas, and stored procedures encapsulate business logic and transactional operations. GraphQL relationships allow clients to traverse related entities in a single request, reducing network calls and simplifying application development. Developers should expose only the objects required by the application, apply least-privilege security principles, and optimize endpoints for performance and maintainability.

Practice Exam Questions

Question 1

Your organization wants external applications to retrieve product information without exposing the underlying table structure or requiring complex joins. Which database object should you expose?

A. A view

B. A database trigger

C. A SQL Agent job

D. A temporary table

Correct Answer:

A. A view

Explanation

Views present a simplified, controlled representation of data by encapsulating joins and filters. They hide the underlying schema, making them ideal for reporting and read-only access. Triggers, SQL Agent jobs, and temporary tables are not intended to expose data to applications.

Question 2

Which type of database object is best suited for encapsulating business logic that performs multiple database operations within a single transaction?

A. A view

B. A stored procedure

C. A synonym

D. An index

Correct Answer:

B. A stored procedure

Explanation

Stored procedures centralize business logic, validate inputs, manage transactions, and execute multiple SQL statements as a single unit of work. Views are primarily for querying data, while synonyms and indexes do not execute business logic.

Question 3

An application uses GraphQL to retrieve customer information and all associated orders in a single request.

Which GraphQL capability makes this possible?

A. Automatic indexing

B. HTTP caching

C. Entity relationships

D. SQL triggers

Correct Answer:

C. Entity relationships

Explanation

GraphQL relationships allow clients to traverse related entities through nested queries, enabling retrieval of customers and their orders in a single request. This is one of GraphQL’s primary advantages over traditional REST APIs.

Question 4

A developer exposes a database table through Data API builder and wants clients to retrieve records using REST.

Which HTTP method should clients use?

A. DELETE

B. PATCH

C. POST

D. GET

Correct Answer:

D. GET

Explanation

REST uses the GET method to retrieve resources. POST creates resources, PATCH updates existing resources, and DELETE removes resources.

Question 5

Which object is most appropriate for exposing aggregated sales totals without allowing users to modify the underlying data?

A. A stored procedure

B. A table

C. A view

D. A trigger

Correct Answer:

C. A view

Explanation

Views are commonly used to expose aggregated or summarized information while hiding the complexity of the underlying tables. Many reporting views are read-only, preventing accidental modifications.

Question 6

A Data API builder configuration includes only the Products and Categories entities.

What happens if a client attempts to access the Employees table?

A. The request succeeds because all tables are exposed automatically.

B. The table is exposed only through GraphQL.

C. The request fails because Employees is not configured as an exposed entity.

D. Data API builder creates the endpoint automatically.

Correct Answer:

C. The request fails because Employees is not configured as an exposed entity.

Explanation

Data API builder exposes only the entities explicitly defined in its configuration. Objects not configured remain inaccessible through both REST and GraphQL endpoints.

Question 7

Why should developers avoid exposing every database table through REST or GraphQL endpoints?

A. Because GraphQL cannot access multiple tables.

B. To follow the principle of least privilege and reduce security risks.

C. Because Data API builder supports only five entities.

D. To improve SQL syntax compatibility.

Correct Answer:

B. To follow the principle of least privilege and reduce security risks.

Explanation

Exposing only required objects reduces the attack surface, protects sensitive data, and aligns with security best practices. Internal, audit, configuration, and security tables should generally remain inaccessible.

Question 8

Which GraphQL feature reduces the need for multiple REST API calls when retrieving related data?

A. Stored procedures

B. Pagination

C. HTTP status codes

D. Nested queries using relationships

Correct Answer:

D. Nested queries using relationships

Explanation

GraphQL allows nested queries that follow entity relationships, enabling clients to retrieve related objects in a single request. This minimizes network traffic and simplifies application development.

Question 9

Which database object is generally the best choice for exposing an operation that validates inventory, creates an order, updates stock levels, and commits the transaction?

A. A stored procedure

B. A view

C. A nonclustered index

D. A foreign key

Correct Answer:

A. A stored procedure

Explanation

Stored procedures encapsulate complex business processes, ensure transactional consistency, and centralize business rules. Views and indexes cannot perform transactional workflows.

Question 10

A GraphQL query retrieves customer information along with orders and order details.

What is the primary benefit of this approach compared to making several REST requests?

A. SQL Server automatically creates indexes.

B. Database permissions are no longer required.

C. Authentication becomes optional.

D. Multiple related resources can be retrieved in a single request, reducing network overhead.

Correct Answer:

D. Multiple related resources can be retrieved in a single request, reducing network overhead.

Explanation

GraphQL enables clients to retrieve exactly the required data—including related entities—in a single query. This reduces round trips, minimizes over-fetching and under-fetching, and often improves application performance.

Go to the DP-800 Exam Prep Hub main page