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:

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

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

DP-800 Exam Tips (Part 1)

Remember these key points for the exam:

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

Go to the DP-800 Exam Prep Hub main page