DP-900 Describe an analytics workload on Azure Practice Question
A data warehouse team uses Azure Synapse Analytics dedicated SQL pool to serve both business executives running weekly reports and data scientists running complex ad-hoc queries on large fact tables. The ad-hoc queries often consume excessive resources and degrade performance for the weekly reports. The team needs to ensure that the weekly reports always get guaranteed resources regardless of other concurrent queries. Which Synapse feature should they use?
⚠ Common exam trap
Test-takers frequently confuse performance optimization features (like caching, materialized views, or indexes) with resource governance features, mistakenly believing that making queries faster inherently guarantees resource availability, whereas workload classification is the only option that provides explicit resource isolation and guarantees.
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
✓
Workload classification
Workload classification in Azure Synapse Analytics dedicated SQL pool allows the team to assign incoming queries to specific workload groups with predefined resource allocations. By classifying the weekly report queries into a group with guaranteed minimum resources (e.g., using `CREATE WORKLOAD CLASSIFIER` with `IMPORTANCE` and `REQUEST_MIN_RESOURCE_PERCENT`), the team ensures those queries always receive the necessary resources, even when ad-hoc data scientist queries are running concurrently. This directly addresses the need for predictable performance for critical reports.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Workload classification
Why this is correct
Workload classification is correct because it directly addresses concurrency and resource guarantee in Azure Synapse dedicated SQL pools. You create classifier rules that map incoming queries (by user, role, or label) to a workload group, which carries an importance level and a resource allocation boundary for CPU and memory. A high-importance query in its own group can preempt or run ahead of low-importance queries, ensuring mission-critical workloads get the resources they need and are protected from runaway queries.
- ✗
Result set caching
Why it's wrong here
Result set caching is incorrect because it only caches the final output of a completed query for fast reuse when the exact same query is submitted again; it contains no mechanism to control resources while a query executes. A particularly expensive query can still occupy all available concurrency slots and memory, possibly evicting cached results and blocking other queries. It improves latency for repeat queries but does nothing to isolate or prioritize one workload over another.
- ✗
Materialized views
Why it's wrong here
Materialized views are incorrect because they are database objects that physically store a pre-computed result set, letting the optimizer serve queries from the view rather than scanning base tables. They can dramatically reduce compute cost for aggregates, but they are purely an optimization technique—there is no built-in scheduling, priority, or resource limit attached to a materialized view. They do not arbitrate among concurrent workloads; they only make individual queries faster.
- ✗
Columnstore indexes
Why it's wrong here
Columnstore indexes are incorrect because they are a storage and access method that compresses data into columnar format and speeds up analytical scans; they improve I/O and CPU efficiency for queries but have no concept of workload importance or resource governance. They do not influence which query gets to run or how much memory is allocated, so a resource-intensive query can still run over them and starve other queries. They are a performance feature, not a workload management feature.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Data warehouse
A data warehouse is a central repository that stores large amounts of structured data from multiple sources, optimized for querying and analysis rather than day-to-day transactions.
Key term
Report
A report in Azure data services is a structured output that summarizes, visualizes, or details data stored in cloud databases or data warehouses, often used for business intelligence and monitoring.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.