DP-900 Describe an analytics workload on Azure Practice Question
A financial analytics company stores petabytes of transaction data in Parquet files in Azure Data Lake Storage Gen2. Data analysts need to run complex SQL queries that join multiple large tables and return results within seconds. The company also wants to integrate with Power BI for visualization and Azure Data Factory for ETL orchestration. They require a massively parallel processing (MPP) engine to handle the scale. Which Azure service should they choose?
⚠ Common exam trap
A common mix-up: candidates confuse Azure Synapse Analytics dedicated SQL pool with Azure SQL Database, assuming both are 'SQL' and thus interchangeable, but the key differentiator is the MPP architecture required for petabyte-scale workloads.
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 is the correct choice because it provides a massively parallel processing (MPP) engine that distributes data across 60 distributions, enabling fast execution of complex SQL queries on petabyte-scale data stored in Parquet files in Azure Data Lake Storage Gen2. It natively integrates with Power BI for visualization and Azure Data Factory for ETL orchestration, meeting all stated requirements.
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 dedicated SQL pool
Why this is correct
Correct. The dedicated SQL pool in Azure Synapse Analytics is an MPP engine optimized for large-scale analytical workloads. It can query data directly in ADLS Gen2 via PolyBase, supports complex joins, and integrates with Power BI and Azure Data Factory.
- ✗
Azure SQL Database
Why it's wrong here
Azure SQL Database is a fully managed relational database service built on the classic SQL Server OLTP engine, not a massively parallel processing (MPP) system. It scales vertically (up to a limited number of vCores) rather than horizontally across many compute nodes, so it cannot efficiently distribute petabyte-scale tables across distributions for complex analytical joins. While it supports standard T-SQL and can be used for small-to-medium analytics, its query optimizer and storage layout are optimized for transactional row-level operations, making it severely underpowered for high-concurrency, billion-row aggregations. Additionally, it does not natively support PolyBase technology for querying data directly in ADLS Gen2, so petabyte-scale data would first need to be loaded into the database, incurring prohibitive time and cost.
When this WOULD be correct
A company needs a fully managed relational database for a line-of-business application with moderate data volumes (e.g., <1 TB), requires high availability, and wants to run standard SQL queries with low latency. Azure SQL Database would be the correct choice for OLTP workloads.
- ✗
Azure Cosmos DB
Why it's wrong here
Incorrect. Azure Cosmos DB is a NoSQL database for globally distributed transactional workloads. While it supports SQL-like queries, it is not an MPP data warehouse and cannot efficiently execute complex joins over petabytes of data.
When this WOULD be correct
A company needs a globally distributed, multi-model database for a real-time application with high throughput and low latency, such as an IoT telemetry ingestion system. The question would specify requirements for NoSQL data, global distribution, and schema flexibility, not complex SQL analytics.
- ✗
Azure Analysis Services
Why it's wrong here
Incorrect. Azure Analysis Services is a semantic modeling engine used to create tabular models for business intelligence. It is not a data warehouse and does not natively store or query petabytes of raw data; it imports data from other sources.
When this WOULD be correct
A company needs to create a semantic data model for business users to perform ad-hoc analysis and drill-downs in Power BI, with pre-aggregated measures and KPIs, using data from multiple sources without requiring direct SQL querying of 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
Correct. The dedicated SQL pool in Azure Synapse Analytics is an MPP engine optimized for large-scale analytical workloads. It can query data directly in ADLS Gen2 via PolyBase, supports complex joins, and integrates with Power BI and Azure Data Factory.
✗Azure SQL DatabaseWrong answer — click to see why▾
Why this is wrong here
Azure SQL Database is not a massively parallel processing (MPP) engine and cannot efficiently handle petabyte-scale data with complex joins across large tables within seconds. It is designed for OLTP workloads, not large-scale analytics.
★ When this WOULD be the correct answer
A company needs a fully managed relational database for a line-of-business application with moderate data volumes (e.g., <1 TB), requires high availability, and wants to run standard SQL queries with low latency. Azure SQL Database would be the correct choice for OLTP workloads.
Why candidates choose this
Candidates may assume that because Azure SQL Database supports SQL and can connect to Power BI and Azure Data Factory, it is suitable for large-scale analytics, overlooking the need for MPP architecture to handle petabyte-scale data and complex joins within seconds.
✗Azure Cosmos DBWrong answer — click to see why▾
Why this is wrong here
Azure Cosmos DB is a NoSQL database designed for globally distributed, low-latency access to semi-structured data, not for complex SQL queries with joins across petabytes of Parquet files in Data Lake Storage. It lacks MPP engine capabilities for large-scale relational analytics.
★ When this WOULD be the correct answer
A company needs a globally distributed, multi-model database for a real-time application with high throughput and low latency, such as an IoT telemetry ingestion system. The question would specify requirements for NoSQL data, global distribution, and schema flexibility, not complex SQL analytics.
Why candidates choose this
Candidates may confuse Cosmos DB's support for SQL API and its ability to handle large volumes of data with the need for a massively parallel processing engine, overlooking its fundamental NoSQL architecture and lack of MPP for complex joins.
✗Azure Analysis ServicesWrong answer — click to see why▾
Why this is wrong here
Azure Analysis Services is an OLAP engine for semantic models and aggregations, not an MPP SQL query engine. It cannot directly run complex SQL queries on petabytes of Parquet data in Data Lake Storage Gen2 with sub-second response times.
★ When this WOULD be the correct answer
A company needs to create a semantic data model for business users to perform ad-hoc analysis and drill-downs in Power BI, with pre-aggregated measures and KPIs, using data from multiple sources without requiring direct SQL querying of raw data.
Why candidates choose this
Candidates may confuse Azure Analysis Services with a data warehousing solution because it supports large-scale analytics and integrates with Power BI, overlooking that it lacks MPP SQL capabilities for raw data querying.
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
Power BI
Power BI is a Microsoft business analytics tool that transforms raw data into interactive visual reports and dashboards for informed decision-making.
Key term
Azure Data Factory
Azure Data Factory is a cloud-based data integration service that lets you create, schedule, and orchestrate data pipelines to move and transform data from various sources to destinations.
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.