DP-900 Describe an analytics workload on Azure Practice Question
A financial services company stores petabytes of transaction data in Parquet format in Azure Data Lake Storage Gen2. Data analysts need to run complex SQL queries that join multiple large tables and aggregate billions of rows, with results expected within seconds. The company wants to use a massively parallel processing (MPP) engine that supports T-SQL and can be paused to reduce costs during off-hours. They also need native integration with Azure Data Factory and Power BI. Which Azure service should they use?
⚠ Common exam trap
Many candidates confuse Azure Synapse Analytics with Azure SQL Database or Azure Databricks, not realizing that only Synapse combines MPP architecture, native T-SQL support, pause capability, and direct integration with Azure Data Factory and Power BI for petabyte-scale analytics.
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
Azure Synapse Analytics (formerly SQL DW) is the correct choice because it provides a massively parallel processing (MPP) engine that distributes data across 60 distributions, enabling complex T-SQL queries on petabyte-scale Parquet data with results in seconds. It supports native T-SQL, can be paused to reduce costs during off-hours, and offers built-in integration with Azure Data Factory and Power BI through its SQL endpoints and linked service connectors.
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
Why this is correct
Azure Synapse Analytics provides a massively parallel processing (MPP) architecture that distributes data across multiple compute nodes, enabling petabyte-scale queries to complete in seconds. Its dedicated SQL pool supports full T-SQL, including complex aggregations and joins, and can be paused to stop compute billing while data remains stored. Tight integration with Azure Data Factory for pipelines and native Power BI connectivity make it the enterprise data warehouse choice for large-scale analytical workloads.
- ✗
Azure HDInsight
Why it's wrong here
Azure HDInsight is incorrect because it provides open-source analytics clusters like Spark or Hadoop, which do not offer native T-SQL support for massively parallel processing (MPP) SQL queries or the ability to pause the dedicated SQL compute for cost reduction. It is tempting as it handles petabytes of data and integrates with Azure Data Lake Storage Gen2, making it suitable for scenarios requiring specific open-source big data frameworks like Apache Spark for complex data transformations.
When this WOULD be correct
A company needs to run custom MapReduce jobs or use open-source big data frameworks like Hadoop, Spark, Hive, or HBase on Azure, and they require full control over cluster configuration and scaling, with no need for T-SQL or pause/resume capability.
- ✗
Azure Databricks
Why it's wrong here
Azure Databricks is a managed Apache Spark analytics platform that excels at distributed data engineering and machine learning workloads, but it is not a SQL data warehouse. Although it can process petabyte-scale data using Spark SQL from notebooks or clusters, the query dialect is Spark SQL, not T-SQL, and it lacks the dedicated SQL pool semantics found in Synapse. While clusters can be terminated to stop compute, you must reconfigure and restart them each time, whereas Synapse offers a one-click pause, and the primary interaction model is notebook-centric rather than SQL-centric, making it a poor fit for this T-SQL-based warehouse scenario.
When this WOULD be correct
A company needs to run advanced analytics and machine learning on big data using Apache Spark, with collaborative notebooks for data scientists. They require integration with Azure Data Lake Storage and want to leverage Delta Lake for ACID transactions. The workload involves iterative data processing and model training, not low-latency T-SQL queries.
- ✗
Azure SQL Database
Why it's wrong here
Azure SQL Database is a Platform-as-a-Service relational database designed for online transaction processing (OLTP), not petabyte-scale analytical querying. It runs on a single logical node with a fixed service tier, so it cannot natively distribute a huge aggregation across multiple nodes; scaling involves moving to a larger service tier or manual sharding. Its compute resources cannot be paused without deleting the database, and you continue paying for allocated DTUs or vCores even when idle, making it cost-ineffective for infrequent massive queries.
When this WOULD be correct
A company needs a fully managed relational database with high availability and built-in intelligence for transactional workloads, such as an e-commerce platform requiring ACID compliance and sub-second query latency on moderate data volumes (e.g., terabytes).
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 AnalyticsCorrect answer▾
Why this is correct
Azure Synapse Analytics provides a massively parallel processing (MPP) architecture that distributes data across multiple compute nodes, enabling petabyte-scale queries to complete in seconds. Its dedicated SQL pool supports full T-SQL, including complex aggregations and joins, and can be paused to stop compute billing while data remains stored. Tight integration with Azure Data Factory for pipelines and native Power BI connectivity make it the enterprise data warehouse choice for large-scale analytical workloads.
✗Azure HDInsightWrong answer — click to see why▾
Why this is wrong here
Azure HDInsight does not natively support T-SQL; it uses HiveQL or Spark SQL. It also lacks the ability to be paused to reduce costs, and its integration with Azure Data Factory and Power BI is not as seamless as Synapse Analytics.
★ When this WOULD be the correct answer
A company needs to run custom MapReduce jobs or use open-source big data frameworks like Hadoop, Spark, Hive, or HBase on Azure, and they require full control over cluster configuration and scaling, with no need for T-SQL or pause/resume capability.
Why candidates choose this
Candidates may associate HDInsight with big data processing and MPP, but overlook the specific requirements for T-SQL support and pause capability, which are not features of HDInsight.
✗Azure DatabricksWrong answer — click to see why▾
Why this is wrong here
Azure Databricks does not natively support T-SQL; it uses Spark SQL and Python/Scala APIs. It also cannot be paused like a dedicated SQL pool in Synapse, and its primary strength is in data engineering and machine learning, not low-latency complex SQL queries on petabyte-scale data with native Power BI integration.
★ When this WOULD be the correct answer
A company needs to run advanced analytics and machine learning on big data using Apache Spark, with collaborative notebooks for data scientists. They require integration with Azure Data Lake Storage and want to leverage Delta Lake for ACID transactions. The workload involves iterative data processing and model training, not low-latency T-SQL queries.
Why candidates choose this
Candidates may associate Azure Databricks with big data processing and MPP capabilities, overlooking that it does not support T-SQL natively and is optimized for Spark-based workloads rather than traditional SQL analytics with pause/resume features.
✗Azure SQL DatabaseWrong answer — click to see why▾
Why this is wrong here
Azure SQL Database is not massively parallel processing (MPP) and cannot handle petabyte-scale data with complex queries on billions of rows 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 with high availability and built-in intelligence for transactional workloads, such as an e-commerce platform requiring ACID compliance and sub-second query latency on moderate data volumes (e.g., terabytes).
Why candidates choose this
Candidates may confuse Azure SQL Database's T-SQL support and Power BI integration with the MPP capabilities required for petabyte-scale analytics, overlooking its architectural limitations for massive data volumes.
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
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
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.