DP-900 Describe an analytics workload on Azure Practice Question
A retail company processes petabytes of sales transaction data stored in Azure Data Lake Storage Gen2. They need to run recurring complex queries that involve large joins and aggregations. The queries must consistently complete within a fixed time window overnight. The company wants predictable performance and costs. Which Azure service should they use?
⚠ Common exam trap
Many candidates confuse serverless SQL pool's flexibility with dedicated SQL pool's predictability, overlooking that serverless is designed for ad-hoc exploration, not consistent, fixed-time batch processing.
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
✓
Azure Synapse Analytics Dedicated SQL pool
Azure Synapse Analytics Dedicated SQL pool provides reserved, fixed compute resources that ensure predictable performance and cost for recurring complex queries involving large joins and aggregations. It is designed for petabyte-scale data warehousing workloads with consistent SLAs, making it ideal for overnight batch processing within a 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.
- ✗
Azure Synapse Analytics Serverless SQL pool
Why it's wrong here
Azure Synapse Serverless SQL pool creates compute resources on demand at query time, so it is well suited for ad-hoc exploration of data in Azure Data Lake Storage. Its performance is inherently variable because capacity is shared and each query must wait for available compute, and it can be slowed by cold file reads and concurrent workloads. A nightly batch campaign that must finish within a fixed window cannot tolerate these runtime fluctuations, making serverless unreliable for consistent, repeatable overnight processing even though it is cheaper for occasional queries.
When this WOULD be correct
A company needs to run occasional, exploratory queries over large datasets in Data Lake Storage without provisioning dedicated resources, and they prioritize cost savings over consistent performance. For example, a data analyst running ad-hoc reports on sales data with no strict SLA.
- ✓
Azure Synapse Analytics Dedicated SQL pool
Why this is correct
A Dedicated SQL pool in Azure Synapse Analytics uses a Massively Parallel Processing (MPP) architecture that distributes petabyte-scale tables across multiple compute nodes, allowing large joins and aggregations to run in parallel. Because compute capacity is reserved and isolated, query response times remain stable even during repeated overnight batch runs, and costs are predictable based on provisioned DWU/cDWU rather than fluctuating per-query resource contention. This makes it the correct engine for recurring, complex, resource-intensive sales analytics.
- ✗
Azure SQL Database
Why it's wrong here
Azure SQL Database is a managed relational Platform-as-a-Service system designed for online transaction processing, not for petabyte-scale analytics. A single database is limited to a few terabytes, and its rowstore-based engine is optimized for high-concurrency point lookups and small writes, not for scanning and joining billions of rows across a sales history. Even scaling up to premium tiers or using sharding cannot deliver the distributed columnar compression and query parallelism that an MPP data warehouse provides, so complex aggregates over petabytes would experience severe I/O bottlenecks and unpredictable runtimes.
When this WOULD be correct
A company needs a fully managed relational database for an online transaction processing (OLTP) application with predictable performance, such as an e-commerce platform handling customer orders and inventory updates, requiring high availability and built-in intelligence.
- ✗
Azure Analysis Services
Why it's wrong here
Azure Analysis Services is a semantic modeling and OLAP engine that loads pre-processed, compressed data into memory to answer fast, interactive BI questions. It is not a distributed data warehouse and cannot run arbitrary SQL directly against petabytes of raw transaction detail; importing or querying that much data would exceed its design capacity and memory limits. Its role is downstream of a dedicated data warehouse, where it consumes aggregated or pre-built tables to publish a clean semantic layer for reports, not to perform the heavy ETL/ELT and aggregation required for overnight batch processing.
When this WOULD be correct
A company needs to create a semantic data model for business users to perform interactive analysis and reporting on aggregated data from multiple sources, with a focus on fast query response times and in-memory caching, rather than running recurring complex ETL-style queries on raw data.
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.
✓Azure Synapse Analytics Dedicated SQL poolCorrect answer▾
Why this is correct
A Dedicated SQL pool in Azure Synapse Analytics uses a Massively Parallel Processing (MPP) architecture that distributes petabyte-scale tables across multiple compute nodes, allowing large joins and aggregations to run in parallel. Because compute capacity is reserved and isolated, query response times remain stable even during repeated overnight batch runs, and costs are predictable based on provisioned DWU/cDWU rather than fluctuating per-query resource contention. This makes it the correct engine for recurring, complex, resource-intensive sales analytics.
✗Azure Synapse Analytics Serverless SQL poolWrong answer — click to see why▾
Why this is wrong here
Serverless SQL pool is designed for ad-hoc, on-demand queries over data in Data Lake, but its performance is unpredictable and depends on data volume and concurrency, making it unsuitable for consistently completing complex queries within a fixed time window.
★ When this WOULD be the correct answer
A company needs to run occasional, exploratory queries over large datasets in Data Lake Storage without provisioning dedicated resources, and they prioritize cost savings over consistent performance. For example, a data analyst running ad-hoc reports on sales data with no strict SLA.
Why candidates choose this
Candidates may think serverless is always the best choice for Data Lake queries due to its pay-per-query model and ability to handle large data, overlooking the need for predictable performance and fixed completion times.
✗Azure SQL DatabaseWrong answer — click to see why▾
Why this is wrong here
Azure SQL Database is designed for OLTP workloads with moderate data volumes, not for petabyte-scale analytics with complex joins and aggregations. It lacks the distributed query processing and massive parallelism needed to consistently complete such queries within a fixed time window.
★ When this WOULD be the correct answer
A company needs a fully managed relational database for an online transaction processing (OLTP) application with predictable performance, such as an e-commerce platform handling customer orders and inventory updates, requiring high availability and built-in intelligence.
Why candidates choose this
Candidates may confuse Azure SQL Database's managed SQL Server capabilities with the analytical processing needs of large-scale data warehousing, assuming it can handle any SQL workload due to its familiarity and ease of use.
✗Azure Analysis ServicesWrong answer — click to see why▾
Why this is wrong here
Azure Analysis Services is a semantic modeling and analytics engine, not a query engine for large-scale data processing. It cannot run complex SQL queries with large joins and aggregations directly on petabytes of data in Data Lake Storage Gen2, and it lacks the predictable performance and cost model of a dedicated SQL pool.
★ When this WOULD be the correct answer
A company needs to create a semantic data model for business users to perform interactive analysis and reporting on aggregated data from multiple sources, with a focus on fast query response times and in-memory caching, rather than running recurring complex ETL-style queries on raw data.
Why candidates choose this
Candidates may confuse Analysis Services with a data warehousing solution because it is used for analytics and can handle large datasets, but they overlook that it is not designed for direct querying of raw data at petabyte scale or for running complex SQL joins and aggregations.
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
Batch processing
Batch processing is a method of running high-volume, repetitive data jobs where a group of transactions is collected, processed together automatically, and then results are produced without real-time user interaction.
Key term
Data Lake Storage Gen2
Data Lake Storage Gen2 is a cloud-based storage service that combines a scalable data lake with enterprise-grade file system capabilities for big data analytics.
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.