DP-900 Describe an analytics workload on Azure Practice Question
Your company uses Azure Synapse Analytics to run analytical queries on large datasets. You need to ensure that queries against a frequently accessed fact table perform well without impacting other workloads. Which feature should you use?
⚠ Common exam trap
Watch out — candidates often confuse workload classification (which only manages queue priority) with performance optimization features, or assume partitioning alone guarantees performance isolation, when in fact result set caching directly addresses both speed and workload isolation for repeated queries.
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
✓
Enable result set caching for the database.
Result set caching stores query results in the Synapse SQL pool's cache, so repeated queries against the fact table return cached results instantly without re-scanning data. This ensures fast performance for frequently accessed queries while isolating resource usage from other workloads, as cached results do not consume concurrency slots or I/O resources.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create materialized views on the fact table.
Why it's wrong here
Creating materialized views on the fact table physically pre-computes and stores aggregation results, which can accelerate queries that exactly match the view's definition. However, these views consume substantial storage and require ongoing incremental maintenance whenever the fact table changes, adding overhead. For repeated identical analytical queries, result set caching directly serves the stored result set, whereas a materialized view still requires executing a query against the view and does not apply to arbitrary query patterns.
- ✓
Enable result set caching for the database.
Why this is correct
Enabling result set caching at the database level instructs Azure Synapse Analytics to store the complete output of qualifying queries in a Synapse-managed cache. When the same query is executed again with identical parameters and security context, the service returns the cached results without recomputation, dramatically reducing compute usage and response time. This cache is automatically invalidated when the underlying data changes, making it ideal for repeatable analytical workloads such as dashboards and business reports.
- ✗
Partition the fact table by a frequently filtered column.
Why it's wrong here
Partitioning the fact table by a frequently filtered column allows partition elimination, so queries scan only the relevant partitions rather than the entire table. While this reduces I/O and speeds up scans, every execution still needs to read the data and compute the query from scratch. It does not persist or reuse the final result set, so running the same analytical query repeatedly remains far less efficient than returning a cached result set.
- ✗
Use workload classification to prioritize the queries.
Why it's wrong here
Workload classification in Azure Synapse Analytics routes queries to specific workload groups, controlling concurrency, resource allocation, and importance. It can help stabilize performance by giving critical queries more resources or headroom, but it does not persist or reuse query outputs. Each execution still performs all processing steps, so classification by itself cannot speed up a frequently repeated analytical query as effectively as result set caching.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Azure Synapse Analytics
Azure Synapse Analytics is a cloud-based data integration, warehousing, and analytics service that brings together big data and data warehouse capabilities under one platform.
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.