DP-900 Describe an analytics workload on Azure Practice Question
A financial services company runs critical end-of-day reports in an Azure Synapse Analytics dedicated SQL pool. These reports require guaranteed resource allocation and must complete within a fixed time window. However, ad-hoc analytical queries from data scientists often consume resources, causing contention and delaying the critical reports. Which feature should the company implement to ensure the critical reports always receive sufficient resources?
⚠ Common exam trap
It's easy for candidates to confuse performance optimization features (caching, materialized views, distribution) with resource governance, which is the only mechanism to guarantee resource allocation and priority in a shared environment.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
A. Create a workload group for the critical reports with a high importance setting and assign a minimum percentage of resources.
Workload groups in Azure Synapse Analytics dedicated SQL pool allow you to assign a minimum percentage of resources (e.g., CPU and memory) to a specific workload, ensuring guaranteed resource allocation. By setting high importance for the critical reports, the system prioritizes them over ad-hoc queries, preventing resource contention and ensuring they complete within the fixed time window.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
A. Create a workload group for the critical reports with a high importance setting and assign a minimum percentage of resources.
Why this is correct
Workload groups in a dedicated SQL pool (formerly Azure SQL Data Warehouse) enable both importance-based scheduling and resource isolation. By setting the critical reports' workload group to High importance, they are queued ahead of lower-priority queries, while assigning a minimum percentage of CPU and memory guarantees those reports always have enough resources to run. This directly mitigates the risk of ad-hoc queries or heavy ETL jobs consuming all available concurrency slots and delaying the end-of-day processing.
- ✗
B. Enable result set caching on all queries to reduce execution time.
Why it's wrong here
Result set caching only improves performance when the exact same query is re-executed and the cache has not been invalidated by data changes. For end-of-day reports that run once per day on fresh data, the cache will be cold, so the first execution still compiles and runs from scratch. Furthermore, caching does not provide any resource reservation or priority; other concurrent queries can still consume CPU, memory, and concurrency slots, leaving critical reports stalled.
When this WOULD be correct
A company runs the same dashboard queries repeatedly and wants to improve response time for end users without changing underlying resources. Enabling result set caching would be correct to return cached results for identical queries.
- ✗
C. Implement materialized views for the aggregations used in the critical reports.
Why it's wrong here
Materialized views precompute aggregations so queries can read pre-built summary data instead of scanning and aggregating large fact tables, which can cut execution time significantly. But they do not grant any priority or reserve resources: a materialized view still queries through the same concurrency pool, and competing workloads can equally consume slots and memory. Additionally, view maintenance itself consumes resources after data changes, and the view does not help when the report must reflect the very latest data that requires a refresh.
When this WOULD be correct
A company has a dedicated SQL pool with complex aggregation queries that run slowly due to repeated full table scans. Implementing materialized views would pre-compute these aggregations, significantly reducing query execution time and improving overall performance.
- ✗
D. Use hash distribution for the fact tables to improve query parallelism.
Why it's wrong here
Hash distribution distributes table rows across all distribution points based on a hash of chosen columns, which can reduce data movement during joins and aggregations and improve query speed. However, it is a physical table design choice that affects storage and query plans, not how resources are allocated or prioritized under contention. It does nothing to prevent a runaway reporting query or a large ad-hoc scan from consuming all available concurrency and starving the critical end-of-day workload.
When this WOULD be correct
A question asks: 'A company has large fact tables and needs to optimize join performance for complex analytical queries. Which table distribution strategy should they use?' In that scenario, hash distribution on join keys would be correct.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-900 exam frequently reuses these exact scenarios with slightly different constraints.
✓A. Create a workload group for the critical reports with a high importance setting and assign a minimum percentage of resources.Correct answer▾
Why this is correct
Workload groups in a dedicated SQL pool (formerly Azure SQL Data Warehouse) enable both importance-based scheduling and resource isolation. By setting the critical reports' workload group to High importance, they are queued ahead of lower-priority queries, while assigning a minimum percentage of CPU and memory guarantees those reports always have enough resources to run. This directly mitigates the risk of ad-hoc queries or heavy ETL jobs consuming all available concurrency slots and delaying the end-of-day processing.
✗B. Enable result set caching on all queries to reduce execution time.Wrong answer — click to see why▾
Why this is wrong here
Result set caching reduces latency for repeated queries but does not guarantee resource allocation or prevent resource contention, so it cannot ensure critical reports receive sufficient resources under load.
★ When this WOULD be the correct answer
A company runs the same dashboard queries repeatedly and wants to improve response time for end users without changing underlying resources. Enabling result set caching would be correct to return cached results for identical queries.
Why candidates choose this
Candidates may think caching speeds up queries and thus reduces contention, but it does not reserve resources or prioritize critical workloads.
✗C. Implement materialized views for the aggregations used in the critical reports.Wrong answer — click to see why▾
Why this is wrong here
Materialized views pre-compute aggregations to speed up queries, but they do not guarantee resource allocation or prevent resource contention from ad-hoc queries. The core issue is resource contention, not query performance.
★ When this WOULD be the correct answer
A company has a dedicated SQL pool with complex aggregation queries that run slowly due to repeated full table scans. Implementing materialized views would pre-compute these aggregations, significantly reducing query execution time and improving overall performance.
Why candidates choose this
Candidates may think that faster queries via materialized views will reduce resource consumption and thus avoid contention, but this does not address the need for guaranteed resource allocation under contention.
✗D. Use hash distribution for the fact tables to improve query parallelism.Wrong answer — click to see why▾
Why this is wrong here
Hash distribution improves query parallelism but does not guarantee resource allocation or prevent contention. The question requires guaranteed resources for critical reports, which hash distribution cannot provide.
★ When this WOULD be the correct answer
A question asks: 'A company has large fact tables and needs to optimize join performance for complex analytical queries. Which table distribution strategy should they use?' In that scenario, hash distribution on join keys would be correct.
Why candidates choose this
Candidates may think that improving query performance via distribution will indirectly help critical reports, but they overlook that the core issue is resource contention, not query speed.
Analysis generated from the official DP-900blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
Key term
Dedicated SQL pool
A Dedicated SQL pool is a cloud-based analytics service in Azure Synapse Analytics that provides a managed, scalable environment for running large-scale data warehousing queries using Transact-SQL.
About these practice questions
This DP-900 question is part of Courseiva's 820-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.