DP-800: Developing AI-Enabled Database Solutions

Contoso has an Azure subscription in North Europe that contains the corporate infrastructure. The current infrastructure includes a Microsoft SQL Server 2017 database containing the following tables.

The FeedbackJson column has a full-text index and stores JSON documents in the following format.

Contoso support staff never have the UNMASK permission.

Contoso is deploying a new Azure SQL database that will become the authoritative data store for the following:

• AI workloads
• Vector search
• Modernized API access
• Retrieval Augmented Generation (RAG) pipelines

Sometimes, the ingestion pipeline fails because of malformed JSON and duplicate payloads.

Contoso engineers report that the following dashboard query runs slowly.

You review the execution plan and discover that it shows a clustered index scan.

VehicleIncidentReports often contains weather, traffic-condition, and location details. Analysts report difficulty finding similar incidents based on those details.

Contoso wants to modernize the Fleet Intelligence Platform to support AI-powered semantic search over incident reports.

Contoso identifies the following security requirements:

• Restrict support staff from viewing Personally Identifiable Information (PII) data, which is full email addresses and phone numbers.
• Enforce row-level filtering so analysts see only incidents for the fleets to which they are assigned. Analysts can be assigned to multiple fleets.

Contoso identifies the following telemetry requirements:

• Telemetry data must be stored in a partitioned table.
• Telemetry data must provide predictable performance for ingestion and retention operations.
• The latitude, longitude, and accuracy JSON properties must be filtered by using an index seek.

Contoso identifies the following maintenance-data requirements:

• Ensure that any change to a row in the MaintenanceEvents table updates the corresponding LastModifiedUtc value to the time of the change.
• Avoid recursive updates.

Contoso plans to implement semantic search over incident data with the following requirements:

• Embeddings must be stored in dedicated Azure SQL Database tables.
• Embeddings must be generated from rich natural-language fields.
• Chunking must preserve semantic coherence.

Hybrid search must combine the following:

• Vector similarity
• Keyword filtering or boosting

The development team will use Microsoft Visual Studio Code and GitHub Copilot and retrieve live metadata from the databases.

For querying data in the FeedbackJson column of the CustomerFeedback table, Contoso requires the following:

• Extract the customer feedback text from the JSON document.
• Filter rows where the JSON text contains a keyword.
• Calculate a fuzzy similarity score between the feedback text and a known issue description.
• Order the results by similarity score, with the highest score first.

You are creating a table that will store customer profiles. You have the following Transact-SQL code.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.