MSAZ-329 · Lesson 9 of 12 · 24 min
Scenario practice
▶ Video coming soon A video version of this lesson is on the way.
Case studies are where the whole outline meets in one place. A case study gives several screens of background about a fictional company, then asks a set of questions that each depend on one or two details hidden in that background. Ines found the case study on her diagnostic the hardest part, not because the questions were harder but because she lost time scrolling back and forth. This lesson works through a full practice case study written in the style of the exam, so that she, and you, can practise the reading method from lesson 2 on something realistic.
The company in this case study is fictional and invented for practice. Read it once quickly for the shape of the business, then a second time with the questions in mind. Nikolai's advice is to read the requirements tab first, because that is where most answers come from, and to treat the other tabs as the evidence the requirements refer to.
Case study: Kestrel Valley Foods
Overview
Kestrel Valley Foods is a food distributor based in Thunder Bay, Ontario. It buys from 300 farms and food makers and delivers to 1,200 grocery stores, restaurants and schools across northern Ontario and Manitoba. It runs two warehouses, one in Thunder Bay and one in Kenora, and a fleet of 140 refrigerated trucks.
Existing environment
Orders are entered in an Azure SQL Database named OrdersDB, which holds about 90 million order lines and receives about 40,000 new or changed lines a day. Customer and product master data are kept in a SharePoint list and an on-premises SQL Server database named MasterDB, which is reachable only through an on-premises data gateway. Each truck sends a temperature reading from each of its three compartments every 30 seconds to an Azure event hub named fleet-telemetry. Finance exports a CSV file of invoices to an Azure Data Lake Storage Gen2 account every night at an unpredictable time between midnight and 3 a.m.
The company has one Fabric capacity, an F64 in Canada Central, and a single workspace named KVF-Analytics that contains everything: a lakehouse named lh_main, a warehouse named wh_finance, ten pipelines, twelve notebooks and fifty reports. Forty people have the Member role on the workspace.
Problem statements
Order reports are a day old because the order load runs once a night and copies the whole OrdersDB each time, which takes three hours. Developers change notebooks and pipelines directly in the workspace, and twice this year a change broke the nightly load. The capacity is throttled most mornings between 6 a.m. and 8 a.m. Dispatchers learn that a truck compartment is too warm only when a store rejects a delivery. Finance analysts complain that they can see sales data for every region, while sales managers can see invoice amounts they should not.
Requirements
Planned changes: Kestrel Valley Foods will separate development, test and production workspaces, and will use a medallion design with bronze, silver and gold layers in lakehouses, with the finance warehouse kept for invoice reporting.
Technical requirements:
- R1. Order data in the silver layer must be no more than 15 minutes old during business hours.
- R2. The order load must not copy unchanged rows.
- R3. Changes to notebooks and pipelines must be reviewed before they reach production, and it must be possible to see who changed what.
- R4. Moving items from development to test to production must require the least manual effort, and connections must point at the right lakehouse in each stage.
- R5. Dispatchers must be alerted within two minutes when any compartment reads above 5 degrees Celsius for three readings in a row.
- R6. Temperature data must be kept for two years for food safety audits, at the lowest cost, with the last seven days queried most often.
- R7. The invoice load must start as soon as the nightly file arrives.
- R8. Sales managers must see sales only for their own region, and must not see invoice amounts. Finance analysts must see invoice amounts.
- R9. Customer email addresses must be hidden from everyone except the customer service team, but the column must remain in query results.
- R10. The cause of the morning throttling must be identified.
- R11. Developers must have the least privilege needed in production.
- R12. The solution must minimize development effort where possible.
Before reading the questions, Ines sketched a design like the one in the figure, with each requirement attached to the part it affects. Nikolai calls this 'pinning the requirements'. It costs a minute or two on a real case study and saves much more, because each question then points to one pin.
Questions
Each question below is followed by the reasoning, with the requirement that decides it. Try each before reading on.
Worked problem
Question 1: the order load
You need to meet R1, R2 and R12 for the order data. What should you use? (A) A pipeline with a Copy activity that copies OrdersDB in full every 15 minutes. (B) Database mirroring of OrdersDB into Fabric. (C) A Dataflow Gen2 with incremental refresh every 15 minutes. (D) A notebook that reads OrdersDB through JDBC every 15 minutes and merges changes.
Show solution
- R1 needs data no more than 15 minutes old; R2 rules out copying unchanged rows; R12 prefers the least development effort.
- A copies all 90 million rows every 15 minutes, which breaks R2 and would also be far too slow.
- D could meet R1 and R2, but needs custom merge logic, which conflicts with R12.
- C can refresh incrementally, but dataflows suit moderate volumes and the setup per table is more work than needed.
- B replicates changes continuously from a supported Azure SQL Database with no load logic, meeting all three.
Answer: B: mirror OrdersDB.
Worked problem
Question 2: deployment
You need to meet R3 and R4. Which two actions should you take? (A) Connect the development workspace to a Git repository and use branches with pull requests. (B) Create a deployment pipeline with development, test and production stages, and set deployment rules for lakehouse connections. (C) Export items as files and import them into each workspace by hand. (D) Give every developer Admin on all three workspaces so they can fix problems quickly.
Show solution
- R3 needs reviews and a history of who changed what: Git integration with pull requests gives both.
- R4 needs the least manual effort to move items between stages, with connections changed per stage: a deployment pipeline with deployment rules does this.
- C is manual, which breaks R4.
- D breaks R11 and does nothing for R3.
Answer: A and B.
Worked problem
Question 3: the temperature alert
You need to meet R5. What should you do? (A) Ingest fleet-telemetry with an eventstream into an eventhouse, and create an Activator rule on the stream that fires when a compartment's temperature is above 5 for three consecutive readings. (B) Load telemetry into the lakehouse each hour and email dispatchers from a pipeline when a notebook finds high readings. (C) Create a Power BI report with a red card for warm compartments. (D) Write a KQL query that dispatchers run when a store calls.
Show solution
- R5 needs an alert within two minutes, on a condition over a sequence of readings.
- B is too slow: hourly.
- C and D need someone to look; neither alerts.
- A reacts to events as they arrive and Activator can evaluate conditions over consecutive events per object, here each compartment.
Answer: A.
Question 4 is about R6. Temperature data must be kept for two years at the lowest cost, with the last seven days queried most often. In an eventhouse, a retention policy of 730 days keeps the data for two years, and a caching policy of 7 days keeps only the recent week in hot cache, where queries are fastest; older data stays queryable from cheaper storage. An option that sets the cache to 730 days would work but cost more; an option that sets retention to 7 days would lose the audit data. Turning on OneLake availability is also a reasonable addition if the question mentions other engines reading the data, but it is not what R6 asks for.
Question 5 is about R7. The invoice file arrives at an unpredictable time. A schedule at 3 a.m. would usually work but would sometimes wait three hours for nothing, and would fail on the rare night the file is late. A storage event trigger that starts the pipeline when a file is created in the folder meets R7 exactly. This is pattern 6 from lesson 8: the schedule meets 'nightly' but not 'as soon as'.
| Question | Requirement | Answer | Distractor pattern avoided |
|---|---|---|---|
| 1 | R1, R2, R12 | Mirroring | Works, but more effort |
| 2 | R3, R4 | Git integration and a deployment pipeline with rules | Too much privilege |
| 3 | R5 | Eventstream, eventhouse and Activator | Solves half the requirement |
| 4 | R6 | Retention 730 days, cache 7 days | Solves half the requirement |
| 5 | R7 | Storage event trigger | Solves half the requirement |
| 6 | R8 | RLS and object-level permissions | Right feature at the wrong level |
| 7 | R9 | Dynamic data masking | Right feature at the wrong level |
| 8 | R10, R11 | Capacity Metrics app; Contributor or Viewer in production | Fixing the symptom |
Question 6 is about R8. Sales managers must see only their region's sales and must not see invoice amounts; finance analysts must see invoice amounts. Two controls are needed. Row-level security on the sales table, with a predicate that compares the region column with the user's region, limits sales rows. Object-level or column-level security on the invoice amount column, granted to finance and denied to sales managers, hides the amounts. An option that moves invoices into a separate workspace would work but is more effort and does not limit regions; an option with only RLS meets half of R8.
Question 7 is about R9. Email addresses must be hidden from everyone except customer service, but the column must remain in query results. That is dynamic data masking, with UNMASK granted to the customer service role. Column-level security would remove the column from results for people without permission, which breaks the second half of R9. This was the exact trap that caught Ines on her diagnostic, and she was pleased to spot it at once.
Question 8 combines R10 and R11. To find the cause of morning throttling, the Fabric Capacity Metrics app shows which items and operations used the most capacity units in each 30-second window, including background operations such as notebook runs and semantic model refreshes. A question might offer 'upgrade to F128' as an option; that treats the symptom. For R11, developers in production need at most Viewer, or Contributor if they must run items, but not Member or Admin, and their changes reach production through the deployment pipeline rather than by editing directly.
Reading the case study efficiently
Ines timed herself on this case study twice. The first time, reading every tab carefully before the questions, took 34 minutes for eight questions. The second time, reading the requirements first, skimming the rest, pinning the requirements on a quick sketch and then answering, took 19 minutes. On the real exam a case study usually has fewer questions than this one, but the saving is the same in proportion. Her method, written on her practice card, is: requirements first; existing environment second, looking for sources, sizes and constraints such as gateways; problem statements third, because they often explain why a requirement exists; questions last, each with its deciding requirement named before looking at the options.
Another habit worth copying is noting numbers. Case studies hide sizes and times that rule options in or out: 90 million rows, 40,000 changes a day, readings every 30 seconds, an alert within two minutes, two years of retention, 7 days of hot data. Each number is in the case study for a reason, and most of them decide at least one question.
Common mistakes
MistakeRead every tab in full before looking at any question.
InsteadRead the requirements first, then skim the rest for sources, sizes and constraints, and go back as each question needs.
MistakeA scheduled load is fine when a file arrives at night.
InsteadIf the requirement says 'as soon as it arrives', an event trigger is needed.
MistakeUpgrading the capacity identifies the cause of throttling.
InsteadThe Capacity Metrics app identifies the cause; upgrading only treats the symptom.
MistakeOne security feature is enough for a requirement with two parts.
InsteadCheck each part: sales rows and invoice columns needed two controls.
MistakeCase study companies are based on real firms.
InsteadThey are fictional; take every detail from the case study and nothing from outside knowledge of a real company.
Summary and key terms
- Case studies combine many skills, and each question usually depends on one or two requirements.
- Read the requirements first and pin each one to the part of the design it affects.
- Note every number in the case study; most decide at least one question.
- Name the deciding requirement before reading the options for each question.
- Mirroring, deployment pipelines with Git, Activator, retention and caching policies, event triggers, RLS and masking all appear naturally in one scenario.
- Case study
- An exam section with a long description of a fictional company followed by several questions about it.
- Pinning requirements
- Sketching the design and attaching each requirement to the part it affects before answering.
- Deployment rule
- A setting in a deployment pipeline that changes a connection or parameter for a stage.
- Caching policy
- An eventhouse setting that keeps recent data in fast hot cache.
- Retention policy
- An eventhouse setting that controls how long data is kept.
Practice problems
Work each problem on paper first, then reveal the answer to check.
Which tab of a case study should you read first?
Show answer
The requirements tab.
Which feature meets R5, an alert within two minutes on three warm readings in a row?
Show answer
An Activator rule on an eventstream feeding an eventhouse.
What retention and caching settings meet R6?
Show answer
Retention of 730 days and a hot cache of 7 days.
Why is column-level security wrong for R9?
Show answer
It removes the column from results; the requirement says the column must remain, so masking is needed.
What should developers have in production under R11?
Show answer
At most Viewer, or Contributor if they must run items; changes arrive through the deployment pipeline.
Lesson quiz · 5 questions
Score 60% or more to complete this lesson.
Create a free account to save your progress, take quizzes and earn a certificate.