An intelligent app case study

On November 11, the last day of the prototype, Hana presented Ask Finch to Grace Oduya, Elena, Sunil, Carmen and Nikolai, with Dana Whitcombe from the AI review group joining online. The meeting had one purpose: decide what the February 2027 pilot at two stores, Store 12 in Winnipeg and Store 15 in Brandon, would look like. This lesson walks through the design they approved, each decision and the lesson of this course it came from, so that it can serve as a template for an exam case study and for real work.

The requirements

Grace's requirements were short. Store associates must be able to ask a product or how-to question in their own words and get, within half a second, a short list of matching products with stock at their store, and within three seconds, a written answer of no more than 80 words that cites its sources. Contractors must be able to search by SKU or brand. Nothing customers say may be stored. Answers must never invent a product. And the monthly running cost must be known before launch.

The architecture

Everything runs in the catalogue database, which moves for the pilot from the serverless test database to a provisioned General Purpose database in Canada Central, with a failover group to Canada East as MSAZ-282 set up for other databases. Products and guide chunks have half-precision embeddings from text-embedding-3-small in narrow tables. Full-text indexes cover product names, descriptions, usage notes and SKUs, and guide titles and text. Two external models point at two deployments of the embedding model: one for questions during the day and one for the nightly maintenance job. A chat model deployment answers questions. All three are called with the server's managed identity, which holds the Cognitive Services OpenAI User role, and the server's outbound firewall allows only the company's model host.

Two stored procedures form the whole interface. askfinch.Search takes a question and returns ranked products with local stock and ranked guide passages; askfinch.Answer takes the same question and the store, checks the cache, retrieves, builds the prompt, calls the chat model, checks the reply and returns the answer and its sources, or a no-answer status. Both run WITH EXECUTE AS ai_proc_owner. The application's identity has EXECUTE on those two procedures and nothing else.

CREATE OR ALTER PROCEDURE askfinch.Answer
    @question nvarchar(400), @answer nvarchar(1000) OUTPUT, @sources nvarchar(max) OUTPUT, @status varchar(20) OUTPUT
WITH EXECUTE AS 'ai_proc_owner'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @storeId int = CAST(SESSION_CONTEXT(N'StoreId') AS int);   -- set by the app from the user's token

    -- 1. Cache (lesson 11)
    EXEC askfinch.TryCache @question, @storeId, @answer OUTPUT, @sources OUTPUT, @status OUTPUT;
    IF @status = 'cached' RETURN;

    -- 2. Retrieve with hybrid search (lesson 7) into #productHits and #chunkHits
    EXEC askfinch.Retrieve @question, @storeId;

    -- 3. Package, 4. generate, 5. check (lesson 8)
    EXEC askfinch.Generate @question, @storeId, @answer OUTPUT, @sources OUTPUT, @status OUTPUT;

    -- Log every request without customer details (lesson 10)
    INSERT askfinch.RequestLog (AskedAt, StoreId, Question, Status, SourcesJson)
    VALUES (SYSUTCDATETIME(), @storeId, @question, @status, @sources);
END;

Keeping it current and deployable

Embeddings are maintained by change tracking and a stored hash, as lesson 3 described: an elastic job every 15 minutes re-embeds products and guide sections whose descriptive text changed, and a nightly job checks for any gaps. The full-text indexes update automatically. When the team changes embedding model in future, the plan is a new vector column, a full re-embedding on the nightly deployment, a side-by-side test on the evaluation questions, and a switch of the procedures to the new column, with the old column kept for a week as a fallback.

Deployment follows the database project practices from MSAZ-300. External models and credentials are part of the project, with the credential's identity but never a secret. One limit needed special handling: because the pilot uses exact search, there is no vector index, but if one is added later, it must be scripted outside the database project, since databases with vector indexes cannot be exported to a BACPAC or deployed with the index in a DACPAC, as lesson 6 explained.

Testing and evaluation

The 60 prototype questions were too few to support a go-live decision, so Grace's managers wrote 150 more, including 20 deliberately awkward ones: misspellings, French product names, questions about products Harrow & Finch does not sell, and two attempts to make the assistant reveal cost prices. Hana reran the whole set after the improvements from lessons 7, 8 and 11.

results = {  # judged by two store managers, disagreements settled by Grace
    "correct and useful": 191,
    "correct, missed a better product": 11,
    "said it could not answer (correctly)": 4,
    "wrong or unhelpful": 4,
}
total = sum(results.values())
good = results["correct and useful"] + results["said it could not answer (correctly)"]
for k, v in results.items():
    print(f"{k:38s} {v:4d}  {v * 100 / total:5.1f} in 100")
print(f"total {total}; acceptable answers {good} = {good * 100 / total:.1f} in 100")
print("target met (90 in 100 or more):", good * 100 / total >= 90)

correct and useful                      191   91.0 in 100
correct, missed a better product         11    5.2 in 100
said it could not answer (correctly)      4    1.9 in 100
wrong or unhelpful                        4    1.9 in 100
total 210; acceptable answers 195 = 92.9 in 100
target met (90 in 100 or more): True

Correct refusals count as acceptable because the right answer to which deck stain should I buy from a competitor's range is that Ask Finch can only recommend Harrow & Finch products. Both attempts to extract cost prices failed, because cost prices are never selected into the package, the design principle lesson 10 described: what is not sent cannot leak. Median time to the written answer was 1.9 seconds and the 95th percentile 3.4 seconds; search results appeared in a median of 0.19 seconds.

The 95th percentile missed the three-second target. The meeting decided not to block the pilot on it, because the slow answers were all chat calls during a short throttling period on the shared test deployment, and the pilot deployment has its own quota. The pilot success criteria include the 95th percentile, measured from the request log, and the timeout and fallback mean that, at worst, an associate sees results without an answer.

Governance

The AI review group, Carmen, Sunil and Dana, approved the pilot with conditions. A privacy impact assessment covers the model provider's data commitments and the regional deployment. A weekly review samples 30 answers and every answer flagged by staff with the not helpful button. An incident procedure says what to do if the assistant gives a dangerous answer, such as unsafe advice about electrical work: disable the answer procedure with one setting while search keeps working, review the log, fix the cause and record the outcome. And the assistant states in the app that its answers are generated and that staff should check the product label, a small line Grace asked for so associates treat it as a helper rather than an authority.

The pilot timeline

Monitoring after launch

The pilot dashboard brings together the signals the earlier lessons introduced. From the request log: requests per store per day, the share answered from cache, the share of no-answer results, the median and 95th percentile response times, and the not helpful flags. From the database: CPU, log write use, the HTTP_EXTERNAL_CONNECTION wait, and the search procedures' durations from Query Store. From Azure Monitor for the model resource: token use per deployment against quota and any 429 responses. From Cost Management: model charges to date against the budget. Each signal has an owner and a threshold, and the weekly review starts from the dashboard rather than from anecdotes.

Grace added one measure of her own: how often an associate who received an answer then searched again within a minute. A quick second search suggests the first answer did not help, even if nobody pressed not helpful, and it is cheap to compute from the log.

The meeting ended with a list of what would make the team stop the pilot early: any answer that gave unsafe advice and was acted on, any exposure of one store's data to another, or a not helpful rate above 25 in 100 for two consecutive weeks. Writing the stop conditions down before launch, Dana observed, is what makes it possible to launch at all: everyone knows in advance what failure looks like and who decides. Hana added the list to the runbook, and to her DP-800 notes as a reminder that case-study questions about pilots often hinge on exactly these criteria.

Case studies on the exam reward the habit this course has followed: read each requirement, map it to a feature, and check the constraint that rules alternatives out. Nikolai's question, who reads the data and how fast they need it, turned out to be a good way to find the constraint that matters most.