Scenario practice

Case studies are where DP-800 joins its skill groups together, and they are the best practice for the exam as a whole. In this lesson Elena gives Hana a full practice case study about a company she has never worked with, so that nothing in it is familiar from Finch Rewards. The case study is written the way the exam presents them, in sections. After the scenario come seven questions, and after each question comes the reasoning Elena wants Hana to practice: list the requirements, test each option, name the distractor patterns, decide.

Hana is asked to do the case study twice. The first time she reads slowly and writes her reasoning in full. The second time, a week later, she gives herself 20 minutes for all seven questions, which is about the pace the exam will need.

Overview

Prairie Kettle Cookware is an online retailer of pots, pans and kitchen tools based in Omaha, Nebraska. It sells about 3,800 products, has 410,000 customer accounts and receives about 2,000 product reviews a week. It plans to add an AI shopping assistant that answers customers' questions about products, care and warranties, using the company's own product data and reviews.

Existing environment

The product database, named kettle, runs on Azure SQL Database in the General Purpose tier. It contains the tables catalog.Product (ProductId, Sku, Name, Description, Price, IsActive), catalog.ProductReview (ReviewId, ProductId, CustomerId, Rating, Title, Body, PostedAt, Language), sales.Customer (CustomerId, Email, FullName, Phone, Province or State) and support.WarrantyDocument (DocumentId, ProductId, Title, Body), which holds long warranty and care documents of up to 30 pages. Reviews are written in English and French. Product codes have the form SKU followed by five digits.

The web storefront is an app on Azure App Service that uses a system-assigned managed identity. The database schema is kept in a GitHub repository but deployed by running scripts by hand. The company has an Azure OpenAI resource with an embeddings deployment and a chat deployment.

Requirements

Technical constraints

Reviews average 120 words; warranty documents average 9,000 words. The catalog.ProductReview table holds 1.4 million rows and grows by about 100,000 rows a year. The company expects to store embeddings for reviews and for chunks of warranty documents.

Question 1

Which search approach meets R1? A) vector search over embeddings of review and document text; B) full-text search over review and document text; C) hybrid search that combines full-text and vector results with reciprocal rank fusion; D) a LIKE filter on the Body column.

Reasoning. R1 has two halves: meaning in two languages, and exact codes. Option A meets the first and can miss the second, a partial solution. Option B meets the second and misses paraphrases, also partial. Option D misses both. Option C combines them. A multilingual embedding model is also needed for the French reviews, which is a model choice rather than a search choice. Answer: C.

Question 2

Warranty documents average 9,000 words. How should they be prepared for embedding to support R1 and R2? A) embed each document as one vector; B) split each document into overlapping chunks, embed each chunk and store it as a row that references the document; C) embed only the document title; D) store the document text in a json column and search it with JSON_VALUE.

Reasoning. A whole 9,000-word document is longer than most embedding models accept, and one vector would blur its meaning, so A fails. C loses almost all the content. D does not involve meaning-based search at all. B is the standard chunking approach, which can be done in the engine with AI_GENERATE_CHUNKS. Answer: B.

Question 3

Which method keeps review embeddings current under R3 and R9? A) an AFTER INSERT trigger on catalog.ProductReview that calls AI_GENERATE_EMBEDDINGS; B) change tracking on catalog.ProductReview with a job that runs every two minutes and generates embeddings for changed rows; C) a nightly job that regenerates every embedding; D) a separate third-party vector service that copies the table.

Reasoning. R3 requires freshness within five minutes and no slowing of inserts. Option A refreshes instantly but calls the model inside each insert, which slows writes and fails them if the model is unavailable: works, but adds load. Option C misses the five-minute limit and adds model cost. Option D breaks R9. Option B is built in, lightweight and runs after the write. An Azure Functions SQL trigger would also work, but it adds a service, so under R9 the built-in option is preferred. Answer: B.

Question 4

Which steps let the database call the embeddings deployment while meeting R4? Each correct answer presents part of the solution. Choose three. A) create a database scoped credential with IDENTITY = 'Managed Identity'; B) grant the logical server's managed identity the Cognitive Services OpenAI User role on the Azure OpenAI resource; C) create an external model that names the credential; D) store the API key in a configuration table and read it in a stored procedure.

Reasoning. 'Part of the solution' means the three must work together. The server's managed identity authenticates to Azure OpenAI (B), the credential tells the engine to use that identity (A), and the external model refers to the credential (C). Option D breaks R4. The order to build them is identity and role, then credential, then model, then EXECUTE on the model for the user that generates embeddings. Answer: A, B and C.

Question 5

How should R5 be met? A) Always Encrypted on Email and Phone; B) Dynamic Data Masking with the email mask on Email and a partial mask on Phone, without granting UNMASK to the support role; C) Row-Level Security on sales.Customer; D) remove the support role's SELECT permission on sales.Customer.

Reasoning. R5 asks for masked display for agents. Option A hides the values from everyone without the keys, including the storefront that sends order emails, and is more than asked. Option C filters rows, not columns. Option D stops agents from seeing customers at all. Option B is the direct fit. Answer: B.

Question 6

The assistant builds a prompt from product data, reviews and the customer's order history. Which design meets R6? A) send the full customer row as JSON so the model has context; B) build the prompt JSON from a view that excludes Email, FullName and Phone, and reference the customer only by an internal ID; C) apply Dynamic Data Masking and rely on the masks appearing in the prompt; D) ask the model in the system message not to repeat email addresses.

Reasoning. R6 says the data must never be sent. A sends it. C depends on the identity running the query lacking UNMASK, and masks such as partial phone numbers still reveal some digits, so it is weaker than not sending the column. D sends the data and asks the model not to use it, which is not a control. B never selects the columns at all, which is the reliable approach: what is not in the prompt cannot leak. Answer: B.

Question 7

Which combination meets R7 and R8? Each correct answer presents part of the solution. Choose three. A) move the schema into a SQL Database Project and deploy the dacpac from a GitHub Actions workflow that signs in with OIDC; B) add a CODEOWNERS rule requiring the data team to review changes under the schema folder, with branch protection on main; C) expose a view catalog.vActiveProduct through Data API builder, with the storefront's managed identity granted SELECT on the view only; D) grant the storefront's identity SELECT on catalog.Product so the API can read active products.

Reasoning. R7 needs review and automated deployment: A automates deployment without stored secrets, and B makes the review mandatory. R8 needs reading through an API without base-table permission: C does that through ownership chaining, and D breaks it. Answer: A, B and C.

{
  "entities": {
    "ActiveProduct": {
      "source": { "object": "catalog.vActiveProduct", "type": "view", "key-fields": ["ProductId"] },
      "rest": { "enabled": true },
      "graphql": { "enabled": true },
      "permissions": [ { "role": "anonymous", "actions": ["read"] } ],
      "cache": { "enabled": true, "ttl-seconds": 60 }
    }
  }
}

Scoring the practice

Hana's first attempt, with full written reasoning, takes 41 minutes and gets six of seven right. She misses question 3, choosing the Azure Functions trigger because it is faster; she overlooked R9, which says to minimize additional services. That is a classic partial-solution miss: her answer met R3 perfectly and ignored a requirement written further down the list. Elena points out that the exam often places a 'built-in' or 'minimize' requirement at the end of a list, where tired readers skim.

Her second attempt, a week later, takes 19 minutes and gets all seven right. The improvement comes from the method, not from memory: she now reads each question, names the requirement numbers it depends on, and checks the remaining requirements in the list before choosing.

What made the practice case study realistic

Elena built the Prairie Kettle case study to behave like a real exam case study in three ways. First, several requirements interact: R3 (freshness without slowing inserts) and R9 (minimize services) together decide question 3, and neither alone does. Second, some options are correct statements about good practice that do not answer the question, such as a well-designed trigger offered for a requirement that rules triggers out. Third, the case study mixes all three skill groups, so Hana cannot switch into one mode of thinking and stay there.

After her second attempt, Hana writes her own version of question 6 with a different requirement, that support agents may see full email addresses but the model may not. Writing questions, and especially writing believable wrong options, turns out to be one of the best ways to understand why the right answer is right. Elena suggests she write one question a week for the rest of the plan and swap them with Walt.