MSAZ-319 · Lesson 9 of 12 · 20 min

Scenario practice

▶ Video coming soon A video version of this lesson is on the way.

Case studies on DP-600 describe a fictional company over several screens, then ask a set of questions about it. Lesson 2 described the format; this lesson practices it with a full case study Marisol wrote for Keiko, Red River Garden Centers, in the same structure as the exam's. On Saturday, December 5, Keiko worked through it in 25 minutes, the time she would expect to spend on a case study of six questions on exam day, then spent an hour going through the answers with Marisol. The case and the questions are below, followed by the analysis.

Read the case once quickly for its shape, then go to the first question, and return to the case for the details each question needs. That is the habit lesson 2 recommends, and it is how Keiko worked through it.

Case study: Red River Garden Centers

Overview. Red River Garden Centers is a chain of 14 garden centers with its head office in Fargo, North Dakota. It sells plants, tools, soil and outdoor furniture, and about 60 in 100 of its yearly sales fall between April and July. It has a small data team of three people: a data engineer who works in Python and SQL, and two analysts who build Power BI reports and know Power Query well but do not write code.

Existing environment. Red River has a Fabric capacity of size F16 in the Central US region. It has one workspace, RR-Analytics, which contains a lakehouse called lh_sales, a Dataflow Gen2 that loads the daily sales file from the point-of-sale system, and an import semantic model, Sales, used by 11 reports. The Sales model refreshes every night at 2:00 and takes 52 minutes. All three members of the data team are Admins of the workspace, and the 14 store managers are Viewers. The point-of-sale system also writes till events to an Azure Event Hubs namespace, which nobody reads at present.

Requirements. Planned changes: the company wants a second workspace for development so that changes can be tested before they reach the reports used by store managers. Store managers must see only their own store's data in the Sales model. Head office staff must see all stores. Supplier cost columns must be hidden from store managers. The operations manager wants a dashboard that shows sales by store in the last hour, refreshed within a minute of each sale, during the spring season. The analysts must be able to change the daily load without learning Python. The nightly refresh must finish before 2:30 so that the morning report email is not delayed. Administrative effort must be kept low.

Technical requirements. All changes to the Sales model must be versioned. The development and production workspaces must use different lakehouses. The solution must use the least privilege for every user.

The questions

The six case study questions.
No.Question
1Which storage mode should the Sales model use to meet the refresh requirement with the least administrative effort?
2How should you implement store-level security for store managers?
3How should the operations dashboard receive till events?
4What should you use to move changes from development to production with different lakehouses?
5What should you change about the data team's workspace access?
6The analysts must change the daily load without Python. Which item should hold the load logic?
A diagram of the Red River Garden Centers environment: the point-of-sale system sends a daily file to a Dataflow Gen2 that loads lh_sales, which feeds the Sales semantic model and 11 reports, and till events go to Event Hubs, unread. Planned additions are shown in dashed outlines: a development workspace, a deployment pipeline, an eventstream and an eventhouse.
Red River Garden Centers: current environment and planned changes.

Question 1: storage mode

The requirement is that the nightly refresh finishes before 2:30; at present it starts at 2:00 and takes 52 minutes. The deciding phrase is 'least administrative effort'. Options would be: keep import and add incremental refresh; switch to DirectQuery; switch to Direct Lake; or move the refresh earlier. Incremental refresh would probably work, but it needs RangeStart and RangeEnd parameters and a policy, and the dataflow must load in a way that keeps those partitions meaningful. DirectQuery would remove the refresh but put every report's queries on the lakehouse endpoint, slowing reports during the busy season. Moving the refresh earlier does not fix the cause and may run before the daily file arrives. Direct Lake over the lh_sales Delta tables removes the import entirely: the model reads the tables on demand and is reframed when the data changes. With an F16 and a chain of 14 stores, the tables are well within the guardrails. Direct Lake is the best answer.

Question 2: store-level security

The requirement has three parts: managers see their own store, head office sees all, and supplier costs are hidden from managers. Row-level security in the semantic model with a dynamic role, filtering the store table with USERPRINCIPALNAME() against a mapping table, covers the first part with one role for all fourteen managers. Head office staff are simply not added to that role, or are added to a role with no filter. Object-level security on the cost columns, set for the managers' role, covers the third part. A distractor would be a separate role per store, which works but adds administrative effort; another would be row-level security in the lakehouse SQL endpoint, which is the wrong layer for a Direct Lake model and could cause fallback to DirectQuery.

Question 3: real-time sales

The dashboard must reflect each sale within a minute. The till events already go to Event Hubs. An eventstream in Fabric can read from Event Hubs and write to a KQL database in an eventhouse, and a Real-Time Dashboard or a Power BI report on the KQL database can query it with a short refresh. The query would be close to the one in lesson 5: summarize sales by store in five-minute bins over the last hour. A distractor would be loading the events into the lakehouse with the daily dataflow, which cannot meet a one-minute requirement; another would be a pipeline on a one-minute schedule, which is more effort and less reliable than streaming.

Question 4: development to production

Two workspaces, one for development and one for production, with different lakehouses, and changes that move between them: this is a deployment pipeline with two stages and a deployment rule on the Sales model that points it to the right lakehouse in each stage. Because the technical requirements also ask that all model changes be versioned, the development workspace should be connected to a Git repository. A distractor would be Git alone, which versions changes but does not move content between workspaces with different connections; another would be downloading and uploading the .pbix by hand, which fails 'least administrative effort' and loses versioning.

Question 5: workspace access

All three team members are Admins, and the requirement is least privilege. The data engineer and the analysts need to create and edit items but not manage access, which is Contributor. One person, usually the team lead, keeps Admin or Member to manage access. Store managers are Viewers, which is fine for reading reports; for a Direct Lake model with row-level security they should not be given more. A distractor would be removing everyone's workspace access and sharing items one by one, which is least privilege in theory but too much administrative effort with 11 reports.

Question 6: load logic for analysts

The analysts know Power Query and must change the daily load without Python. The load already runs in a Dataflow Gen2, which uses Power Query, so the answer is to keep the logic there. A notebook would require Python or Spark SQL; a stored procedure would require SQL and a warehouse, which Red River does not have. The distractor here is the notebook, because notebooks are often the better choice for large volumes; but the deciding requirement is the analysts' skills, and one file a day for 14 stores is a small volume.

How Keiko did, and why

Keiko answered four of the six questions correctly. She chose incremental refresh for question 1, and the analysis shows why that was tempting: she had set up incremental refresh on Finance Margin in the fall and knew it would cut the refresh time. But she had not gone back to the requirements screen, where 'administrative effort must be kept low' sat in the last line, and she had not noticed that the model was already reading from a lakehouse, which is what makes Direct Lake possible without moving any data. For question 5, she chose Member for the data team, which is closer to least privilege than Admin but still lets them share items and add users, which the case does not need.

Both misses belong to patterns from lesson 8. The first is 'works, but ignores the deciding phrase'; the second is 'too much access'. Neither was a gap in knowledge. Keiko added both to her error log with a line each: 'return to the requirements screen before choosing' and 'for each role, ask whether a smaller one is enough'.

Keiko's answers to the Red River case study.
QuestionKeiko's answerCorrectPattern if missed
1 Storage modeIncremental refreshDirect LakeIgnores the deciding phrase
2 SecurityDynamic RLS with OLSDynamic RLS with OLS
3 Real-timeEventstream to eventhouseEventstream to eventhouse
4 DeploymentPipeline with rules and GitPipeline with rules and Git
5 AccessMemberContributorToo much access
6 Load logicDataflow Gen2Dataflow Gen2

Writing your own scenarios

Practice case studies are scarce, so Marisol suggested Keiko write her own, two in the spring, based on companies she knows. The value is not in the case itself but in writing the requirements: to write a good distractor, she has to know exactly which phrase rules it out. A useful template has five parts. An overview with the company's size and the shape of its business. An existing environment that names the capacity, workspaces, items and who has what access. Requirements that include at least one deciding phrase for each question, such as least effort, least privilege, or the skills of the team. Technical requirements that add a rule such as versioning or separate data per stage. And six questions, each with one correct option and three distractors labeled by pattern.

Keiko plans to base her first case on a fictional chain of hardware stores and the second on a regional food distributor with a streaming requirement, which will make her practice eventhouses and KQL. She will swap cases with another candidate from the Harrow & Finch study group, Daniel Okafor, who is preparing for the same exam, so that each of them answers a case they did not write.

What the case screens usually hold

Real case studies vary, but the same kinds of facts appear on the same screens, and knowing where to look saves time. The overview gives the business and its size, and sometimes a fact that matters later, such as a seasonal peak. The existing environment lists the capacity size and region, the workspaces and items, the current access and the data sources; questions about storage modes, gateways and security usually depend on this screen. The requirements describe what must change and often hold the deciding phrases. The technical requirements add rules that apply to every answer, such as versioning or least privilege. When a question seems to have two right answers, the second one is usually ruled out by a line on the technical requirements screen.

Worked problem

Timing the case

Keiko spent 25 minutes on the case: 4 minutes reading it once, then about 3.5 minutes on each of the six questions. On exam day she plans to leave at least 15 minutes for review out of the 100 minutes in the exam. If a case of six questions takes 25 minutes, how many minutes are left for the other items and review?

Show solution
  1. The exam is 100 minutes.
  2. The case takes 25 minutes, leaving 100 − 25 = 75 minutes.
  3. Holding 15 minutes for review leaves 75 − 15 = 60 minutes for the other items.
  4. With about 40 other items, that is about 1.5 minutes each.

Answer: About 60 minutes for the other items, roughly a minute and a half each, with 15 minutes held for review.

Common mistakes

  • MistakeRead every screen of the case in detail before the first question.

    InsteadSkim the case for its shape, then return for details each question needs.

  • MistakeAnswer case questions from general knowledge.

    InsteadEvery answer must fit the case's existing environment and requirements.

  • MistakeIgnore the technical requirements screen.

    InsteadRequirements such as versioning or least privilege often decide between two options.

  • MistakeChoose the most powerful tool for every task.

    InsteadMatch the tool to the team's skills and the data volume in the case.

  • MistakeSpend as long as needed on a case.

    InsteadBudget about 25 minutes for six questions and move on.

Summary and key terms

  • A case study describes a company over several screens and asks a set of questions about it.
  • Skim the case first, then return to it for each question's details.
  • The existing environment and the requirements together decide the answer; the deciding phrase is often on the requirements screen.
  • In the Red River case: Direct Lake, dynamic row-level security with object-level security, an eventstream into an eventhouse, a deployment pipeline with rules and Git, Contributor for the team, and a Dataflow Gen2 for the analysts.
  • Budget about 25 minutes for a case of six questions.
Case study
A set of exam questions about one fictional company described over several screens.
Existing environment
The part of a case that describes what the company already has.
Technical requirements
The part of a case that lists rules every solution must follow.
Eventstream
A Fabric item that captures and routes streaming events, for example from Event Hubs to an eventhouse.
Real-Time Dashboard
A dashboard in Fabric whose tiles run KQL queries against an eventhouse.

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. Why is Direct Lake better than incremental refresh for Red River?

    Show answer

    It removes the import with less setup, meeting the deadline with the least administrative effort.

  2. Why not one role per store?

    Show answer

    It works but adds administrative effort; one dynamic role covers all managers.

  3. Which item reads the till events from Event Hubs?

    Show answer

    An eventstream.

  4. Why keep the daily load in a dataflow?

    Show answer

    The analysts know Power Query and the volume is small.

  5. What should the data engineer's workspace role be?

    Show answer

    Contributor.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz