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.
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
Or
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:
✔ 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.
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.
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.
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 --> Generate 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 foundational technologies behind modern AI-powered applications such as semantic search, Retrieval-Augmented Generation (RAG), intelligent chatbots, recommendation systems, and knowledge assistants. Rather than treating text as simple strings of characters, embeddings transform text into high-dimensional numerical vectors that capture semantic meaning. This enables AI systems to compare concepts based on meaning rather than exact word matches.
For developers working with SQL databases, generating embeddings is often the first step in building AI-enabled database solutions. After embeddings are created, they can be stored in vector columns, indexed using vector indexes, and queried using vector similarity search. This makes it possible to retrieve relevant information efficiently and provide context to Large Language Models (LLMs).
For the DP-800: Developing AI-Enabled Database Solutions exam, candidates should understand when embeddings should be generated, how they are produced, where they are stored, how they are maintained, and how they integrate into SQL-based AI architectures.
What Are Embeddings?
An embedding is a numerical representation of data that captures its semantic meaning. Instead of representing text as characters or words, an embedding model converts the text into an array of floating-point numbers called a vector.
For example:
Text:
"Reset your account password."
Embedding (simplified):
[0.231, -0.118, 0.654, 0.092, ...]
Real embedding vectors typically contain hundreds or thousands of dimensions, depending on the model.
Although humans cannot interpret these numbers directly, embedding models position semantically similar text close together within vector space.
For example:
Text
Relationship
Reset password
Very similar
Change password
Very similar
Forgot my password
Similar
Employee vacation policy
Not similar
Although none of these sentences are identical, the first three express nearly the same concept and therefore produce vectors that are close together.
Why Generate Embeddings?
Embeddings enable SQL databases and AI applications to perform semantic retrieval instead of relying solely on exact keyword matching.
Benefits include:
Semantic search
Retrieval-Augmented Generation (RAG)
Similarity search
Intelligent recommendations
Duplicate detection
Document classification
Clustering similar content
Knowledge discovery
AI-powered assistants
Natural language querying
Without embeddings, searching generally depends on literal text matching.
Example:
Traditional search:
Password reset
Finds:
Password reset
May not find:
Forgot my login
Change my credentials
Reset account access
Semantic search using embeddings retrieves all of these because they express similar meanings.
Embedding Generation Workflow
Generating embeddings typically follows this workflow:
Source Data
│
▼
Prepare Text
│
▼
Chunk Documents
│
▼
Select Embedding Model
│
▼
Generate Embedding Vector
│
▼
Store Vector
│
▼
Vector Index
│
▼
Similarity Search
Each stage contributes to the overall effectiveness of AI retrieval.
Preparing Data Before Generating Embeddings
High-quality embeddings begin with well-prepared data.
Typical preparation steps include:
Removing duplicate documents
Cleaning formatting artifacts
Normalizing whitespace
Removing unnecessary HTML
Converting PDFs into text
Correcting OCR errors
Standardizing encoding
Removing irrelevant content
Identifying document boundaries
Poor-quality input results in poor-quality embeddings.
Choosing Which Data to Embed
Not every database column should be embedded.
Good candidates include:
Product descriptions
Knowledge articles
Documentation
Policies
Customer support content
Email templates
FAQs
User manuals
Research papers
Technical documentation
Less suitable candidates include:
Identity columns
Numeric identifiers
Dates
Foreign keys
Boolean flags
Audit columns
Calculated values
Embedding descriptive, natural-language content provides the greatest value.
Chunking Before Generating Embeddings
Embedding an entire document often produces a vector that represents multiple unrelated topics.
Instead, documents should usually be divided into meaningful chunks.
Example:
Original document:
Employee Handbook
After chunking:
Vacation Policy
Medical Leave
Expense Reimbursement
Remote Work
Each chunk receives its own embedding.
Benefits include:
Improved retrieval precision
Better semantic representation
More accurate RAG responses
Lower processing costs
Easier maintenance
Selecting an Embedding Model
An embedding model converts text into vectors.
Common considerations include:
Vector dimensions
Supported languages
Domain specialization
Cost
Accuracy
Maximum token length
Latency
Azure integration
Microsoft AI-enabled SQL solutions commonly use embedding models hosted through Azure AI Foundry, Azure OpenAI, or compatible external providers.
The embedding model used for indexing should also be used for query embeddings to ensure compatibility.
Embedding Dimensions
Each embedding consists of a fixed number of dimensions.
Examples:
384 dimensions
768 dimensions
1024 dimensions
1536 dimensions
3072 dimensions
Higher dimensions generally capture richer semantic relationships but require:
More storage
Larger vector indexes
Increased memory
More processing during similarity search
Choosing the appropriate dimension is a balance between accuracy and cost.
Batch Generation of Embeddings
Generating embeddings individually is inefficient for large datasets.
Instead, organizations commonly process documents in batches.
Advantages include:
Better throughput
Lower API overhead
Reduced operational costs
Easier scheduling
Improved monitoring
Batch processing is commonly used when:
Loading historical documents
Building initial vector indexes
Reindexing knowledge bases
Incremental Embedding Generation
Production systems rarely regenerate every embedding.
Instead, they generate embeddings only for new or modified content.
Common mechanisms include:
SQL table triggers
Change Tracking
Change Data Capture (CDC)
Change Event Streaming (CES)
Azure Functions with SQL Trigger Binding
Azure Logic Apps
Microsoft Foundry pipelines
Incremental updates reduce cost while keeping vector indexes synchronized with source data.
Storing Embeddings
After generation, embeddings are typically stored alongside their source data or in a dedicated vector table.
Example:
Document ID
Chunk
Embedding
101
Vacation Policy
Vector
102
Medical Leave
Vector
103
Benefits
Vector
In SQL Server 2025 and Azure SQL Database, embeddings can be stored in vector-compatible columns, enabling efficient similarity search.
Metadata Associated with Embeddings
Each embedding should include metadata that supports retrieval and maintenance.
Typical metadata includes:
Document ID
Chunk ID
Source filename
Page number
Section heading
Creation date
Last modified date
Embedding model used
Embedding version
Language
Security classification
Metadata enables filtering, traceability, citation generation, and re-embedding when models are updated.
Keeping Embeddings Current
Embeddings represent the content at the time they were generated. When source data changes, the corresponding embeddings become outdated.
Common maintenance workflow:
Row Updated
│
▼
Detect Change
│
▼
Regenerate Embedding
│
▼
Replace Old Vector
│
▼
Update Vector Index
Automating this process ensures that AI applications always retrieve current information.
Common Challenges When Generating Embeddings
Developers should be aware of several common issues:
Poor Chunking
Large or poorly defined chunks reduce retrieval accuracy.
Incorrect Model Selection
Using different embedding models for indexing and querying can produce incompatible vectors.
Stale Embeddings
Failing to regenerate embeddings after data changes leads to outdated search results.
Excessive Costs
Embedding every column or regenerating vectors unnecessarily increases API usage and storage costs.
Inadequate Metadata
Without metadata, it is difficult to identify sources, filter results, or reconstruct document context.
Best Practices
Microsoft recommends several best practices for generating embeddings in SQL-based AI solutions:
Generate embeddings only for meaningful textual content.
Chunk documents into semantically coherent sections before embedding.
Use the same embedding model for both indexing and query generation.
Store metadata with every embedding.
Automate embedding generation for new and modified content.
Use incremental updates instead of regenerating all embeddings.
Monitor embedding generation jobs for failures and latency.
Evaluate retrieval quality regularly using representative user queries.
Choose embedding dimensions that balance accuracy, storage, and performance.
Version embedding models so vectors can be regenerated consistently when models change.
Real-World Example
A company maintains a knowledge base of 75,000 technical support articles.
Instead of embedding each entire article, they:
Clean and normalize article text.
Divide each article into logical sections.
Generate an embedding for each section using an Azure-hosted embedding model.
Store vectors and metadata in Azure SQL Database.
Create a vector index.
Use vector similarity search to retrieve the most relevant sections.
Supply retrieved sections as context to a Large Language Model for answering user questions.
Automatically regenerate embeddings whenever articles are updated using Change Tracking and Azure Functions.
This architecture provides fast, accurate semantic retrieval while minimizing operational costs.
DP-800 Exam Tips
For the DP-800 exam, understand that generating embeddings is far more than simply calling an AI model. Microsoft expects candidates to understand the complete embedding lifecycle, including data preparation, chunking, model selection, embedding generation, storage, metadata management, incremental updates, and integration with vector search and RAG solutions. Be prepared for scenario-based questions that require choosing appropriate embedding strategies, maintaining embedding freshness, optimizing costs, and designing scalable AI-enabled database solutions that integrate Azure SQL with Azure AI services.
Practice Exam Questions
Question 1
A company is building a Retrieval-Augmented Generation (RAG) solution using Azure SQL Database. Why should documents generally be divided into chunks before generating embeddings?
A. To reduce the number of database tables required
B. To improve semantic retrieval by creating embeddings for focused pieces of content
C. To eliminate the need for vector indexes
D. To ensure embeddings contain fewer than 100 dimensions
Answer:B
Explanation: Chunking documents into semantically meaningful sections improves retrieval accuracy because each embedding represents a single concept or closely related ideas. Embedding an entire document often results in vectors that represent multiple topics, reducing search precision.
Question 2
Which type of database column is generally the best candidate for generating embeddings?
A. Product description
B. Order ID
C. Invoice number
D. Creation timestamp
Answer:A
Explanation: Embeddings are designed to represent semantic meaning. Descriptive text such as product descriptions, documentation, FAQs, and support articles provides meaningful information that can be searched semantically. Numeric identifiers and timestamps contain little semantic value.
Question 3
What is the primary purpose of an embedding model?
A. Compress relational tables
B. Encrypt database records
C. Convert text into numerical vectors representing semantic meaning
D. Generate SQL indexes automatically
Answer:C
Explanation: Embedding models transform text into high-dimensional vectors that preserve semantic relationships. These vectors enable similarity searches, semantic search, clustering, and Retrieval-Augmented Generation (RAG).
Question 4
Why should the same embedding model generally be used for both document indexing and query generation?
A. Different models produce incompatible vector spaces.
B. SQL Server only supports one model.
C. Using multiple models improves similarity scores.
D. Azure SQL automatically converts vectors between models.
Answer:A
Explanation: Embedding vectors generated by different models often exist in different vector spaces and cannot be compared accurately. Using the same model ensures similarity calculations remain meaningful.
Question 5
A development team updates product documentation daily.
Which approach minimizes costs while keeping embeddings current?
A. Regenerate every embedding every hour.
B. Regenerate embeddings only when the corresponding documents change.
C. Never regenerate embeddings.
D. Create duplicate embeddings for every document revision.
Answer:B
Explanation: Incremental embedding generation updates only modified content, reducing API usage, storage requirements, and processing time while maintaining accurate search results.
Question 6
What is a major benefit of storing metadata alongside embeddings?
A. It reduces vector dimensions.
B. It eliminates the need for chunking.
C. It enables filtering, traceability, and source attribution during retrieval.
D. It compresses embeddings automatically.
Answer:C
Explanation: Metadata such as document ID, page number, section heading, language, and security classification allows applications to identify the source of retrieved content, reconstruct document context, and apply filters during searches.
Question 7
Which technology can detect modified SQL data so only affected embeddings are regenerated?
A. SQL Server Agent alerts only
B. Change Tracking or Change Data Capture (CDC)
C. Database snapshots
D. Transaction log backups
Answer:B
Explanation: Both Change Tracking and Change Data Capture (CDC) identify inserted, updated, or deleted rows, making them well suited for triggering incremental embedding regeneration workflows.
Question 8
A company chooses an embedding model with significantly more vector dimensions than its previous model.
What is the most likely tradeoff?
A. Lower storage requirements
B. Reduced semantic accuracy
C. Increased storage and processing requirements
D. Elimination of vector indexes
Answer:C
Explanation: Higher-dimensional vectors typically capture more semantic detail but require additional storage, memory, and computational resources during indexing and similarity searches.
Question 9
Which workflow correctly represents the embedding generation process?
A. Generate vectors → Clean data → Chunk documents → Search
B. Chunk documents → Generate embeddings → Store vectors → Perform similarity search
C. Store vectors → Generate embeddings → Build documents
D. Query database → Generate vectors → Create documents
Answer:B
Explanation: The standard workflow is to prepare and chunk documents, generate embeddings, store them, create vector indexes if appropriate, and then use similarity search to retrieve relevant content.
Question 10
An organization regenerates embeddings every night even though very little data changes. Users report no improvement, but Azure AI costs continue to increase.
What is the best recommendation?
A. Increase the embedding dimensions.
B. Generate duplicate embeddings for verification.
C. Replace semantic search with keyword search.
D. Implement incremental embedding generation triggered by data changes.
Answer:D
Explanation: Incremental embedding generation regenerates vectors only when data changes, reducing unnecessary API calls, lowering costs, and maintaining up-to-date embeddings without repeatedly processing unchanged content.
End of Topic Summary
For the DP-800 exam, understand that generating embeddings is a foundational step in building AI-enabled SQL database solutions. Success depends on more than simply invoking an embedding model—you must also prepare and chunk data appropriately, select a suitable embedding model, generate compatible vectors, store them with useful metadata, and keep them synchronized with changing source data through incremental update mechanisms such as Change Tracking, CDC, Azure Functions, or Logic Apps. Microsoft expects candidates to understand the complete embedding lifecycle and how it supports semantic search, vector indexing, and Retrieval-Augmented Generation (RAG) solutions.
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.
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.
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.
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.
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 REST or GraphQL endpoints
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 connect directly to a database. Instead, they communicate with APIs that provide a secure, scalable, and well-defined interface for accessing and modifying data. Microsoft Data API Builder (DAB) simplifies this process by automatically exposing SQL Server and Azure SQL Database objects through REST and GraphQL endpoints with minimal custom code.
For the DP-800: Developing AI-Enabled Database Solutions certification exam, candidates should understand how to configure and secure REST and GraphQL endpoints, determine when each API style is appropriate, configure authentication and authorization, expose database objects as entities, and optimize endpoint performance.
This topic builds on previous areas such as configuring entities and Data API Builder configuration files. While entities define what database objects are exposed, endpoints determine how applications interact with those objects.
Learning Objectives
After completing this topic, you should be able to:
Explain the purpose of REST and GraphQL endpoints.
Understand how Data API Builder exposes SQL data.
Configure REST endpoints.
Configure GraphQL endpoints.
Understand endpoint routing.
Configure CRUD operations.
Secure API endpoints.
Implement authentication and authorization.
Optimize endpoint performance.
Choose between REST and GraphQL for various scenarios.
Troubleshoot common endpoint issues.
Why APIs Are Important
Without APIs:
Application
↓
Direct Database Connection
↓
SQL Database
Applications require:
Database credentials
Knowledge of table structures
SQL query logic
Network connectivity to the database
This approach introduces security and maintenance challenges.
With Data API Builder:
Application
↓
REST / GraphQL API
↓
Data API Builder
↓
Azure SQL Database
Benefits include:
Simplified development
Better security
Centralized authentication
Controlled data exposure
Consistent API design
Easier scalability
Understanding REST
REST (Representational State Transfer) is an architectural style that exposes resources through HTTP methods.
Common HTTP verbs include:
Method
Purpose
GET
Retrieve data
POST
Create data
PUT
Replace an existing resource
PATCH
Update part of a resource
DELETE
Remove data
Example:
GET /api/products
returns:
[
{
"ProductID":1,
"Name":"Laptop"
}
]
REST Endpoint Structure
Typical endpoint format:
https://server/api/entity
Examples:
GET /api/customers
GET /api/orders
POST /api/products
PATCH /api/orders/25
DELETE /api/customers/10
REST uses URLs to identify resources.
Understanding GraphQL
GraphQL is a query language developed to allow clients to request exactly the data they require.
Unlike REST, GraphQL typically uses a single endpoint.
Example:
/graphql
The client submits queries.
Example:
query {
products {
Name
Price
}
}
Only the requested fields are returned.
REST vs. GraphQL
Feature
REST
GraphQL
Endpoints
Multiple
Usually one
Data returned
Fixed by endpoint
Client specifies fields
Over-fetching
Possible
Minimized
Under-fetching
Possible
Rare
CRUD support
Native HTTP verbs
Queries and mutations
Learning curve
Lower
Slightly higher
Caching
Excellent HTTP support
More complex
Neither approach is universally better.
Microsoft expects developers to choose the appropriate API based on application requirements.
Data API Builder Architecture
Data API Builder sits between applications and the database.
Application
↓
REST / GraphQL
↓
Data API Builder
↓
Azure SQL Database
Responsibilities include:
Endpoint generation
Authentication
Authorization
SQL execution
CRUD operations
Entity mapping
Relationship handling
Configuring REST Endpoints
REST endpoints are enabled within the Data API Builder configuration.
Developers specify:
Entity
Source table
Permissions
Allowed operations
Example concept:
Entity
↓
Customers
↓
REST Enabled
Automatically creates endpoints similar to:
GET /api/customers
POST /api/customers
PATCH /api/customers/15
DELETE /api/customers/15
Configuring GraphQL Endpoints
When GraphQL is enabled, Data API Builder generates a GraphQL schema automatically.
Example query:
query {
customers {
CustomerName
City
}
}
Mutation example:
mutation {
createCustomer(...)
}
Developers do not manually write the GraphQL schema.
Endpoint Routing
Routing determines how incoming requests reach the appropriate entity.
REST example:
/api/products
Routes to:
Products Entity
GraphQL example:
/GraphQL
Routes all requests through:
GraphQL Engine
The GraphQL engine determines which entities participate in the query.
CRUD Operations
Data API Builder supports CRUD operations.
Operation
REST
GraphQL
Create
POST
Mutation
Read
GET
Query
Update
PATCH/PUT
Mutation
Delete
DELETE
Mutation
Organizations often disable unnecessary operations.
Example:
Internal reporting API:
Allowed:
GET
Disabled:
POST
PATCH
DELETE
This reduces security risks.
Endpoint Configuration Best Practices
Microsoft recommends exposing only the endpoints required by the application.
Good practices include:
Enable only necessary entities.
Disable unnecessary CRUD operations.
Hide internal tables.
Use descriptive endpoint names.
Keep URL structures consistent.
Avoid exposing sensitive objects.
Authentication
Authentication answers:
Who is making the request?
Common authentication methods include:
Microsoft Entra ID
Managed Identity
JWT Bearer Tokens
OAuth 2.0
API keys (where appropriate)
Microsoft strongly recommends Microsoft Entra ID for Azure-hosted solutions.
Microsoft Entra ID Integration
Data API Builder integrates with Microsoft Entra ID.
Authentication flow:
User
↓
Microsoft Entra ID
↓
Access Token
↓
REST / GraphQL
↓
Data API Builder
↓
Azure SQL Database
Benefits include:
Single sign-on
Central identity management
Multi-factor authentication
Conditional Access
Token-based authentication
Authorization
Authentication determines identity.
Authorization determines permissions.
Example:
Developer:
Can:
Read
Update
Auditor:
Can:
Read only
Guest:
Can:
View public data only
Authorization should follow the principle of least privilege.
Endpoint Security
APIs should never expose more information than necessary.
Security recommendations include:
Use HTTPS exclusively.
Require authentication.
Use Microsoft Entra ID where possible.
Implement role-based authorization.
Validate client input.
Disable unused operations.
Avoid exposing sensitive columns.
Log API access.
Monitor suspicious activity.
Protecting Sensitive Data
Poor API:
Employee
Name
Salary
SSN
PasswordHash
Better API:
Employee
Name
Department
Title
Sensitive fields should remain inaccessible.
Often this is accomplished through:
Database views
Entity configuration
Role-based permissions
Error Handling
REST commonly returns HTTP status codes.
Examples:
Code
Meaning
200
Success
201
Created
400
Bad Request
401
Unauthorized
403
Forbidden
404
Not Found
500
Internal Server Error
Applications should use these responses to handle failures appropriately.
GraphQL Error Responses
GraphQL responses may contain both successful data and error information.
Example concept:
{
"data":{
"products":null
},
"errors":[
{
"message":"Unauthorized"
}
]
}
Unlike REST, GraphQL often returns HTTP 200 while including error details in the response body.
Developers should inspect both the HTTP status code and the GraphQL response payload.
GraphQL helps minimize over-fetching because clients specify the required fields.
REST Performance
REST benefits from mature HTTP infrastructure.
Advantages include:
Browser caching
Proxy caching
Azure Front Door
Azure API Management caching
CDN support
REST is often preferred for:
Public APIs
High-volume read workloads
Static content
Mobile applications
GraphQL Performance
GraphQL reduces unnecessary network traffic.
Instead of:
GET Customer
GET Orders
GET Products
A single GraphQL query can retrieve all related information.
Example:
{
customer(id:1){
Name
Orders{
OrderDate
Total
}
}
}
This minimizes the number of client-server round trips.
Monitoring Endpoints
Production APIs should be monitored continuously.
Useful Azure services include:
Azure Monitor
Application Insights
Log Analytics
Azure API Management analytics
Monitor:
Request counts
Response times
Error rates
Authentication failures
Throughput
Latency
These metrics help identify bottlenecks and security issues.
Common DP-800 Exam Scenarios
You should be comfortable answering questions such as:
When should REST be preferred over GraphQL?
When is GraphQL more efficient than REST?
How are CRUD operations exposed through each API style?
Why should unnecessary CRUD operations be disabled?
Which authentication mechanism is recommended for Azure-hosted APIs?
How should endpoint authorization be implemented?
How can API performance be improved?
Why is HTTPS required?
How does GraphQL reduce over-fetching?
What monitoring information should be collected for production APIs?
DP-800 Exam Tips
Know the differences between REST and GraphQL.
Understand how Data API Builder automatically generates endpoints.
Remember that REST typically uses multiple endpoints, while GraphQL commonly uses a single endpoint.
Understand the mapping between CRUD operations and HTTP verbs.
Recognize the importance of Microsoft Entra ID authentication.
Apply least-privilege authorization principles.
Use HTTPS for all endpoint communication.
Know when GraphQL reduces over-fetching and when REST benefits from HTTP caching.
Understand how endpoint configuration affects security, scalability, and performance.
Summary
Configuring REST and GraphQL endpoints is a core competency for developers building modern SQL-backed applications with Microsoft Data API Builder. REST provides resource-oriented endpoints that align naturally with HTTP methods and benefit from widespread tooling and caching support. GraphQL offers a flexible query model that enables clients to retrieve exactly the data they need, reducing over-fetching and minimizing network traffic.
For the DP-800 exam, candidates should understand how Data API Builder automatically generates these endpoints from configured entities, how CRUD operations map to each API style, how to secure endpoints using Microsoft Entra ID and role-based authorization, and how to optimize performance through pagination, filtering, caching, and efficient query design. Mastering these concepts enables developers to build secure, scalable, and maintainable APIs that integrate SQL databases with modern cloud-native applications.
Practice Exam Questions
Question 1
You are deploying Microsoft Data API builder in front of an Azure SQL Database. The security team requires that users authenticate with Microsoft Entra ID before accessing either the REST or GraphQL endpoints.
Which authentication provider should you configure?
A. Anonymous authentication
B. Microsoft Entra ID authentication
C. Basic Authentication
D. SQL Authentication
Correct Answer:
B. Microsoft Entra ID authentication
Explanation
Microsoft Entra ID (formerly Azure Active Directory) is Microsoft’s recommended authentication mechanism for cloud services. Data API builder supports Microsoft Entra ID authentication, enabling secure token-based authentication for both REST and GraphQL endpoints.
Why the other answers are incorrect:
A: Anonymous authentication provides no identity validation.
C: Basic authentication transmits usernames and passwords and is generally discouraged.
D: SQL Authentication secures the database connection but is not intended for authenticating API consumers.
Question 2
A development team wants consumers of a REST endpoint to retrieve data using standard HTTP semantics.
Which HTTP method should clients use when reading data?
A. POST
B. PUT
C. GET
D. DELETE
Correct Answer:
C. GET
Explanation
REST follows standard HTTP conventions.
GET retrieves data.
POST creates resources.
PUT replaces existing resources.
DELETE removes resources.
Using the appropriate HTTP method improves interoperability and aligns with REST best practices.
Question 3
A GraphQL endpoint exposes Customer information.
A client application only requires the customer’s first name and email address.
What is the primary advantage of GraphQL in this scenario?
A. GraphQL automatically encrypts returned data.
B. GraphQL always executes faster than REST.
C. GraphQL allows clients to request only the required fields.
D. GraphQL eliminates authentication requirements.
Correct Answer:
C. GraphQL allows clients to request only the required fields.
Explanation
GraphQL enables clients to specify exactly which fields should be returned, reducing unnecessary data transfer and improving application efficiency.
The other options are incorrect because:
GraphQL does not provide encryption.
Performance depends on workload.
Authentication remains necessary.
Question 4
An organization wants to expose only the Products table through Data API builder.
The Orders and Customers tables must never be accessible.
What is the best configuration?
A. Configure only the Products entity in the DAB configuration.
B. Create views for all tables.
C. Grant db_owner permissions.
D. Disable GraphQL.
Correct Answer:
A. Configure only the Products entity in the DAB configuration.
Explanation
Only configured entities become accessible through DAB endpoints. Tables not defined in the configuration cannot be queried through the generated APIs.
Granting broad database permissions or disabling GraphQL does not prevent REST access.
Question 5
A developer receives HTTP 401 Unauthorized when calling a secured REST endpoint.
Which issue is the most likely cause?
A. The endpoint uses HTTPS.
B. The client failed to provide a valid authentication token.
C. The SQL query contains joins.
D. Pagination is enabled.
Correct Answer:
B. The client failed to provide a valid authentication token.
Explanation
HTTP 401 indicates that authentication failed or credentials were not supplied.
Typical causes include:
Missing bearer token
Expired token
Invalid token
Incorrect authentication configuration
The remaining options are unrelated to authentication failures.
Question 6
Your organization wants GraphQL clients to create new database records.
Which GraphQL operation should the clients perform?
An application experiences slower response times because every request repeatedly retrieves identical reference data.
Which feature would most likely improve endpoint performance?
A. Increase SQL authentication timeout.
B. Enable response caching where appropriate.
C. Replace GraphQL with SOAP.
D. Disable indexes.
Correct Answer:
B. Enable response caching where appropriate.
Explanation
Caching reduces repeated database reads for frequently requested data.
Benefits include:
Lower latency
Reduced database workload
Improved scalability
Disabling indexes would significantly reduce performance.
Question 8
Which statement best describes GraphQL schemas?
A. They define the structure of available queries, mutations, and data types.
B. They replace SQL indexes.
C. They encrypt REST endpoints.
D. They create database backups.
Correct Answer:
A. They define the structure of available queries, mutations, and data types.
Explanation
The GraphQL schema acts as the contract between clients and the API.
It specifies:
Available object types
Fields
Queries
Mutations
Relationships
It does not manage indexing, encryption, or backups.
Question 9
Your organization deploys Data API builder to production.
Which practice best protects REST and GraphQL endpoints?
A. Enable anonymous access for easier testing.
B. Store secrets directly in configuration files.
C. Require HTTPS and strong authentication.
D. Disable authorization checks.
Correct Answer:
C. Require HTTPS and strong authentication.
Explanation
Production APIs should always:
Use HTTPS
Authenticate users
Authorize requests
Protect credentials
Follow least-privilege principles
Anonymous access and embedded secrets introduce significant security risks.
Question 10
A developer modifies a Data API builder configuration file by adding a new entity.
What must occur before clients can use the new endpoint?
A. Restart or redeploy the Data API builder service so the updated configuration is loaded.
B. Rebuild the Azure SQL Database.
C. Delete the GraphQL schema.
D. Recreate the database indexes.
Correct Answer:
A. Restart or redeploy the Data API builder service so the updated configuration is loaded.
Explanation
After modifying the DAB configuration, the running service must reload the updated configuration. Depending on the hosting environment, this typically involves restarting the application or redeploying the container or service.
Database rebuilding, deleting the GraphQL schema, and recreating indexes are unrelated to exposing newly configured endpoints.
Exam Tips for DP-800
For the exam, you should be comfortable with:
Configuring REST and GraphQL endpoints using Microsoft Data API builder.
Understanding REST HTTP methods (GET, POST, PUT/PATCH, DELETE).
Understanding GraphQL queries, mutations, and schemas.
Configuring Microsoft Entra ID authentication.
Applying authorization using database permissions and DAB configuration.
Exposing only intended database objects.
Using HTTPS to secure endpoint communications.
Improving performance through caching and efficient endpoint design.
Deploying configuration changes safely.
Understanding the differences and appropriate use cases for REST versus GraphQL.
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 entities for REST and GraphQL, including data caching, pagination, searching, and filtering
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 increasingly expose data through APIs rather than allowing applications to connect directly to databases. APIs provide a secure abstraction layer that simplifies application development while protecting the underlying database.
For the DP-800 certification, Microsoft expects candidates to understand how Data API Builder (DAB) exposes SQL Server and Azure SQL Database objects through automatically generated REST and GraphQL endpoints. Candidates should know how to configure entities, control which operations are available, implement pagination, filtering, searching, caching, relationships, and understand the security implications of exposing database objects through APIs.
Unlike traditional custom-built APIs that require significant development effort, Data API Builder allows developers to expose database objects by using a configuration file. Developers describe database objects as entities, define the allowed operations, and configure API behavior.
Understanding entity configuration is an important skill because it enables organizations to rapidly build secure, scalable APIs without writing extensive backend code.
Learning Objectives
After completing this topic, you should be able to:
Explain how Data API Builder exposes SQL data.
Configure entities in a DAB configuration file.
Expose entities through REST and GraphQL.
Configure CRUD operations.
Configure relationships between entities.
Implement pagination.
Configure filtering and searching.
Configure sorting.
Understand caching behavior.
Apply security best practices.
What is Data API Builder (DAB)?
Data API Builder is an open-source Microsoft service that automatically creates REST and GraphQL APIs for relational databases.
Supported databases include:
Azure SQL Database
SQL Server
Azure SQL Managed Instance
Azure Database for PostgreSQL
Azure Cosmos DB (NoSQL support in certain scenarios)
Rather than developing controllers and endpoints manually, developers define a configuration file that describes:
Database connection
Authentication
Authorization
Entities
API settings
Runtime behavior
DAB automatically generates the endpoints.
Example architecture:
Application
│
REST / GraphQL
│
Data API Builder
│
Azure SQL Database
Why Use Data API Builder?
Benefits include:
Rapid API development
Less custom code
Built-in GraphQL
Automatic REST endpoints
Security integration
Microsoft Entra authentication
Managed Identity support
Authorization rules
Entity relationships
Simplified deployment
For many internal business applications, DAB eliminates the need to build an entire ASP.NET Web API project.
Understanding Entities
An entity represents a database object that Data API Builder exposes through an API.
Typically an entity maps to:
Table
View
Stored procedure (REST only in specific scenarios)
Example database:
Customers
CustomerID
FirstName
LastName
Email
Entity configuration:
Customers
Automatically becomes
REST
GET /api/Customers
POST /api/Customers
GET /api/Customers/{id}
GraphQL
customers
customer_by_pk
createCustomer
updateCustomer
deleteCustomer
Entity Configuration Basics
Each entity is defined inside the configuration file.
Typical properties include:
Source object
Permissions
REST settings
GraphQL settings
Relationships
Fields
Operations
Conceptually:
Entity
Source Table
REST enabled
GraphQL enabled
Permissions
Relationships
Entity Source
The source identifies the database object.
Examples include:
Table
View
Example concept:
Entity
Product
Source
dbo.Products
The entity name does not have to match the table name.
Example:
Database table
SalesOrders
Exposed as
Orders
This abstraction creates cleaner APIs.
Exposing REST Endpoints
REST endpoints are enabled per entity.
Typical operations include:
GET
POST
PUT
PATCH
DELETE
Example endpoints:
GET /api/products
GET /api/products/10
POST /api/products
PATCH /api/products/10
DELETE /api/products/10
Developers can disable operations they do not want clients to perform.
Example:
Allow:
GET
Disable:
DELETE
This creates read-only APIs.
Exposing GraphQL Endpoints
When GraphQL is enabled, DAB generates a GraphQL schema automatically.
Example query:
query
{
books
{
BookID
Title
Price
}
}
Mutation example:
mutation
{
createBook(...)
}
GraphQL allows clients to request exactly the fields they need.
Example:
{
products
{
Name
Price
}
}
instead of
SELECT *
This reduces network traffic.
Configuring CRUD Operations
Each entity can expose one or more CRUD operations.
Supported operations include:
Operation
REST
GraphQL
Create
Yes
Yes
Read
Yes
Yes
Update
Yes
Yes
Delete
Yes
Yes
Organizations frequently expose:
Read
Create
while disabling:
Delete
to protect data.
Primary Keys
Entities require primary keys for many operations.
Example
CustomerID
REST endpoint
GET /api/customers/25
GraphQL
customer_by_pk
Without a primary key:
Updates become difficult
Deletes become difficult
Relationships cannot always be generated
Composite Keys
Some tables use multiple columns as a primary key.
Example
OrderID
ProductID
REST requests must uniquely identify both values.
GraphQL also requires all key fields.
Candidates should understand that Data API Builder supports composite keys but requires complete key values for entity identification.
Entity Relationships
Relationships allow clients to retrieve related data.
Database:
Customers
Orders
Relationship
Customer
│
Many Orders
GraphQL example:
{
customers
{
CustomerName
orders
{
OrderDate
Total
}
}
}
REST clients can also retrieve related resources depending on configuration.
Relationships reduce the need for multiple API requests.
Data Caching
Caching improves API performance by reducing repeated database queries.
Although Data API Builder itself is intentionally lightweight, caching can be implemented through surrounding Azure services such as:
Azure Front Door
Azure API Management
Azure CDN
Reverse proxy caches
Client-side caching
HTTP caching headers
Benefits include:
Lower latency
Reduced database load
Better scalability
Faster responses
Example
Without cache:
1000 requests
↓
1000 SQL queries
With cache:
1000 requests
↓
50 SQL queries
↓
950 cached responses
Caching is especially useful for:
Product catalogs
Reference data
Geographic lookup tables
Public information
Configuration data
It is generally less appropriate for frequently changing transactional data.
Pagination
Returning every row from a large table is inefficient.
Pagination divides results into smaller pages.
Example
Instead of:
100,000 rows
Return:
100 rows
Page 1
then
Page 2
Page 3
Page 4
Benefits include:
Faster response time
Lower memory usage
Better user experience
Reduced network traffic
Common pagination parameters include:
Page Size
Offset
Limit
Continuation Tokens
GraphQL implementations may also support cursor-based pagination depending on the configuration and client.
Best Practices for Pagination
Microsoft recommends:
Choose reasonable page sizes.
Avoid unlimited result sets.
Return metadata when appropriate, such as:
Total records
Current page
Next page
Previous page
Limit maximum page size to prevent excessive resource consumption.
Example:
Good
50 rows
Better
100 rows
Poor
500,000 rows
Filtering
Filtering reduces the number of returned records.
Example:
Products
Price > 100
Instead of every product:
5000 products
Return only
150 products
Examples include:
Category = 'Electronics'
Status = 'Active'
Price > 100
City = 'London'
Benefits:
Reduced bandwidth
Faster queries
Better application performance
Improved user experience
Filtering should be pushed to the database whenever possible rather than filtering results in application code after retrieval.
Searching
Searching differs from filtering.
Filtering matches specific conditions.
Searching finds records based on user-provided values.
Example
Search text:
Laptop
Possible matches:
Gaming Laptop
Business Laptop
Laptop Bag
Search operations commonly involve:
LIKE
Full-text search
Prefix searches
Keyword searches
Organizations should consider indexing frequently searched columns to improve performance.
Sorting
Sorting determines the order of results.
Examples:
Ascending:
Product Name
A → Z
Descending:
Price
High → Low
Sorting can be combined with:
Filtering
Pagination
Searching
Example workflow:
Products
↓
Filter
↓
Search
↓
Sort
↓
Page Results
Combining these capabilities creates efficient, user-friendly APIs while minimizing unnecessary data transfer.
Performance Considerations
When configuring entities, developers should avoid exposing inefficient queries.
Recommendations include:
Use indexed columns for filtering.
Paginate large datasets.
Avoid returning unnecessary columns.
Limit expensive joins.
Optimize frequently executed queries.
Use appropriate database indexes.
Cache relatively static data where appropriate.
Monitor API and query performance using Azure monitoring tools and SQL performance features.
Proper entity design directly affects both API responsiveness and database workload.
Security Considerations
Exposing database objects through APIs introduces additional security considerations.
Best practices include:
Expose only required tables and views.
Disable unnecessary CRUD operations.
Use Microsoft Entra ID authentication whenever possible.
Implement role-based authorization.
Avoid exposing sensitive columns such as passwords, secrets, or internal identifiers.
Validate client input.
Use HTTPS for all API traffic.
Apply the principle of least privilege.
Monitor API usage and audit access.
Remember that DAB simplifies API generation but does not replace the need for a comprehensive security strategy.
Common DP-800 Exam Scenarios
You should be comfortable answering questions such as:
When should you expose a table as an entity versus creating a custom API?
How do REST and GraphQL differ when exposing SQL data?
When should pagination be implemented?
Which workloads benefit most from API caching?
How do filtering and searching differ?
Why should delete operations be disabled for certain entities?
Why are primary keys required for update and delete operations?
How do entity relationships simplify GraphQL queries?
Which API design practices improve performance for large datasets?
How should security be enforced when exposing database objects through Data API Builder?
Scenario-based questions may ask you to identify the most appropriate configuration for performance, scalability, or security, requiring an understanding of how entity settings affect API behavior.
DP-800 Exam Tips
Understand the differences between REST and GraphQL entity exposure.
Know how entities map to database tables and views.
Be familiar with CRUD operation configuration.
Understand why primary keys and relationships are important.
Know when to use pagination, filtering, sorting, and searching.
Understand that caching is typically implemented by Azure services surrounding DAB rather than by DAB itself.
Apply security best practices, including least privilege and selective exposure of database objects.
Recognize performance optimization techniques such as indexing filtered columns, limiting result sets, and avoiding over-fetching.
Summary
Configuring entities in Data API Builder is a foundational skill for developing modern SQL-based APIs. By defining entities that map to database objects, developers can automatically expose secure REST and GraphQL endpoints with minimal code. Effective entity configuration includes selecting appropriate CRUD operations, defining relationships, implementing pagination, filtering, searching, and sorting, and designing for scalability and security. For the DP-800 exam, candidates should understand not only how these features work individually but also how they combine to create performant, maintainable, and secure API solutions that integrate SQL databases with modern cloud applications.
Practice Exam Questions
Question 1
A company uses Azure SQL Database to store product information. Developers want to expose product data through Data API Builder. Customers should be able to view products but must not be able to modify or delete product records.
Which configuration should you implement?
A. Enable only the POST operation for the product entity B. Enable only read operations for the product entity C. Disable REST and enable GraphQL mutations only D. Create a stored procedure that handles all product requests
Correct Answer: B
Explanation
Data API Builder allows developers to control which operations are exposed for each entity. If customers should only view product information, the entity should expose read-only access.
A read-only entity configuration allows:
GET operations through REST
Query operations through GraphQL
but prevents:
INSERT
UPDATE
DELETE
Why the other options are incorrect:
A. POST allows creating new records, which violates the requirement.
C. GraphQL mutations allow data modification and should not be enabled.
D. A stored procedure is unnecessary for this requirement and does not directly address entity permissions.
Question 2
A developer exposes an Orders table as a Data API Builder entity. The table contains 5 million rows. Users need to browse orders through a web application.
What should the developer implement to improve API performance?
A. Return all rows and allow the browser to filter results B. Disable indexing because APIs handle optimization automatically C. Implement pagination with reasonable page sizes D. Duplicate the table into multiple databases
Correct Answer: C
Explanation
Pagination is the appropriate solution when exposing large datasets through APIs.
Benefits include:
Reduced response size
Lower memory consumption
Faster response times
Improved user experience
Why the other options are incorrect:
A. Returning millions of rows creates unnecessary network and database overhead.
B. Indexing remains important for database performance.
D. Database duplication does not solve the API result-size problem.
Question 3
A developer creates a GraphQL endpoint using Data API Builder. Users want to retrieve customers and their related orders in a single query.
What should the developer configure?
A. A relationship between the Customer and Order entities B. A separate database for each entity C. A REST-only endpoint for the Orders table D. A SQL Agent job to combine the tables nightly
Correct Answer: A
Explanation
Entity relationships allow related data to be retrieved together, especially through GraphQL queries.
Example:
{
customers {
CustomerName
orders {
OrderDate
}
}
}
The relationship configuration enables Data API Builder to understand how entities are connected.
Why the other options are incorrect:
B. Separate databases do not create entity relationships.
C. REST-only endpoints do not enable GraphQL relationship queries.
D. Scheduled jobs do not provide real-time relational querying.
Question 4
A company exposes a Product entity through Data API Builder. Users frequently search products by product name. Query performance has degraded as the product catalog grows.
What should you do first?
A. Remove filtering capabilities from the API B. Store product names in an external file C. Add appropriate database indexing for search columns D. Increase the API response size limit
Correct Answer: C
Explanation
Search operations frequently depend on database performance. Adding appropriate indexes improves query execution speed for commonly searched columns.
For example:
CREATE INDEX IX_Product_Name
ON Products(ProductName);
Why the other options are incorrect:
A. Removing functionality does not solve the performance problem.
B. External files are not an appropriate database optimization strategy.
D. Increasing response size can make performance worse.
Question 5
A developer wants users to retrieve only active products from a Data API Builder endpoint.
Which capability should be used?
A. Filtering B. Pagination C. Caching D. Sorting
Correct Answer: A
Explanation
Filtering restricts returned records based on conditions.
Example:
Status = 'Active'
Only matching records are returned.
Why the other options are incorrect:
B. Pagination controls the number of results returned, not which records qualify.
C. Caching improves performance but does not limit returned data.
D. Sorting changes order but does not restrict records.
Question 6
An application displays a product catalog. Product information changes only once per day, but thousands of users access the catalog every hour.
What approach provides the greatest performance benefit?
A. Disable indexes on product tables B. Implement caching for product API responses C. Return every product column for every request D. Disable pagination
Correct Answer: B
Explanation
Caching is ideal for frequently accessed, rarely changing data.
Examples of good caching candidates:
Product catalogs
Reference data
Geographic lookup information
Configuration settings
Caching reduces repeated database queries and improves scalability.
Why the other options are incorrect:
A. Removing indexes reduces performance.
C. Returning unnecessary data increases workload.
D. Disabling pagination can create large inefficient responses.
Question 7
A developer configures an entity in Data API Builder but update and delete operations fail. The table does not have a primary key.
What is the most likely reason?
A. GraphQL cannot access SQL databases B. REST endpoints require Azure Functions C. Entity identification requires a primary key D. Pagination prevents updates
Correct Answer: C
Explanation
Primary keys allow Data API Builder to uniquely identify individual records.
Operations such as:
Update
Delete
Retrieve by identifier
typically require a primary key.
Example:
GET /api/customers/100
requires knowing which row represents customer 100.
Why the other options are incorrect:
A. GraphQL supports SQL data sources.
B. REST endpoints do not require Azure Functions.
D. Pagination does not prevent updates.
Question 8
A developer creates a REST endpoint for a Customer entity. The application needs to retrieve customers located in a specific city.
Which capability should be used?
A. Sorting B. Filtering C. Caching D. Relationship mapping
Correct Answer: B
Explanation
Filtering returns only records matching specified criteria.
Example:
City = 'Seattle'
The database performs the filtering before returning results.
Why the other options are incorrect:
A. Sorting changes ordering only.
C. Caching improves performance but does not select records.
D. Relationships connect entities but do not filter records.
Question 9
A developer exposes a table containing employee information through Data API Builder. The table contains salary information that should never be visible to API consumers.
What should the developer do?
A. Expose the table but rely on client applications to hide the salary column B. Create an entity that exposes only required fields C. Increase the database timeout value D. Enable caching for the employee table
Correct Answer: B
Explanation
A secure API design exposes only the data required by consumers.
Possible approaches include:
Creating database views
Limiting exposed fields
Configuring entity permissions
Sensitive information should not be sent to clients and hidden only through application logic.
Why the other options are incorrect:
A. Client-side hiding is not a security control.
C. Timeout settings do not protect sensitive data.
D. Caching sensitive data can increase risk.
Question 10
A company uses GraphQL through Data API Builder. Developers want clients to request only the fields they need instead of receiving large unnecessary payloads.
Which GraphQL capability supports this requirement?
A. Field selection in queries B. Database replication C. SQL Server Agent scheduling D. Data compression only
Correct Answer: A
Explanation
One of GraphQL’s primary advantages is allowing clients to specify exactly which fields they need.
Example:
{
products {
Name
Price
}
}
Only the requested fields are returned.
Benefits include:
Reduced network traffic
Smaller responses
Improved application performance
Why the other options are incorrect:
B. Replication improves availability but does not control query fields.
C. SQL Agent scheduling is unrelated to API responses.
D. Compression reduces payload size but does not allow field selection.
Topic Summary
Key concepts tested in this section:
Concept
Key Exam Point
Entities
Map database objects to API resources
REST
Automatically exposes HTTP CRUD operations
GraphQL
Provides flexible queries and field selection
Primary keys
Required for identifying individual records
Relationships
Enable related entity retrieval
Pagination
Improves performance for large datasets
Filtering
Limits returned records
Searching
Finds matching records based on values
Sorting
Controls result ordering
Caching
Improves scalability for frequently accessed static data
