Implement AI capabilities in database solutions Study Guide — DP-800

Implement AI Capabilities in Database Solutions

This domain covers how to embed AI capabilities—vector search, hybrid search, retrieval-augmented generation (RAG), and embedding pipelines—directly inside Microsoft Fabric SQL Database and Azure SQL Database. It accounts for 25% of the DP-800 exam, making it the heaviest single domain. Expect questions that test exact syntax, index type selection, chunking decisions, trigger vs. CDC tradeoffs, and end-to-end RAG architecture choices.

External Model Evaluation and Management

Concepts

External models are AI model endpoints registered and called from within the database engine. In Microsoft Fabric, models are registered as external model objects that abstract the connection to Azure OpenAI, Azure AI Foundry, or other REST-compatible model providers. The database object stores the endpoint, API version, and credential reference—not the model weights.

Multimodal models accept more than one data type (text + image, text + audio). On the exam, recognize that multimodal capability resides in the model itself (e.g., GPT-4o); the database simply passes the correct payload. Fabric currently supports text embedding models and chat completion models for external model integration.

Structured output forces the model to return JSON conforming to a specific schema. In T-SQL-based RAG flows, structured output is requested by setting response_format to json_object or by supplying a JSON Schema in the REST body when calling sp_invoke_external_rest_endpoint. The exam tests whether you know to parse structured output with OPENJSON rather than free-text parsing.

Common traps

• The exam may imply you need a separate Azure service to call OpenAI—wrong; sp_invoke_external_rest_endpoint and CREATE EXTERNAL MODEL handle this inside the database.
• Structured output is not automatic; you must explicitly request json_object format or supply a schema.
• Multimodal does not mean the database stores images; it means the payload sent to the model can include image references.

Embedding Maintenance: Triggers, Change Tracking, Azure Functions, CDC, Foundry

Concepts

Embeddings become stale when source data changes. The exam tests which mechanism is appropriate for keeping embeddings current.

Change Tracking tracks which rows changed (not what changed). It requires ALTER DATABASE … SET CHANGE_TRACKING = ON and ALTER TABLE … ENABLE CHANGE_TRACKING. You query it with CHANGETABLE(CHANGES …).

CDC records the full before/after data of changed rows in system tables. It requires SQL Agent (or Fabric equivalent). CDC is heavier than CT but gives you the actual old/new column values.

Triggers can call sp_invoke_external_rest_endpoint to generate embeddings inline—but this means the embedding API call is inside your transaction, adding latency and failure risk to every write.

Azure Functions can subscribe to CDC events or be timer-triggered to batch-compute embeddings outside the transaction path—better for high-write tables.

Microsoft Fabric pipelines / AI Foundry provide fully managed orchestration: a data pipeline reads changed rows, calls the embedding model, and writes updated vectors back to the table. This is the preferred pattern for Fabric-native workloads.

Best practice: Add an embedding_updated timestamp or a needs_embedding bit column to track rows that have not yet been vectorized.

Common traps

• Triggers add latency to the write transaction—bad for high-throughput tables.
• Change Tracking does not give you the old column values; CDC does.
• The exam often presents CDC as the correct answer when audit history is required and CT when only "which rows changed" is needed.
• AI Foundry pipelines are managed but have pipeline latency, not sub-second.

Worked scenario

A product catalog table receives 10,000 inserts/hour. Embeddings must be refreshed within 5 minutes of a change, and the team wants to avoid any impact to insert throughput.

Reasoning: Triggers are eliminated (inside transaction). CDC with an Azure Function timer trigger every 2 minutes satisfies the 5-minute SLA without slowing inserts. Change Tracking would also work and is lighter—correct answer depends on whether column-level change data is needed.