DP-900 Describe an analytics workload on Azure Practice Question
A financial services company stores years of market trade data as Parquet files in Azure Data Lake Storage Gen2. The data volume is terabytes and growing rapidly. Data analysts need to run complex SQL queries that join multiple tables (e.g., trades, instruments, counterparties) and return results within seconds. The company also wants to integrate with Power BI for visualization and Azure Data Factory for orchestration of ETL pipelines. Which Azure service should they choose as the primary analytics platform?
⚠ Common exam trap
Test-takers frequently confuse Azure Synapse Analytics serverless SQL pool with Azure SQL Database, assuming both are just 'SQL databases,' but the key differentiator is that serverless SQL pool is a distributed query service for data lakes, not a transactional database.
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 (serverless SQL pool)
Azure Synapse Analytics serverless SQL pool is the correct choice because it provides a distributed SQL query engine that can directly query Parquet files in Azure Data Lake Storage Gen2 using T-SQL, enabling complex joins across multiple tables with fast performance via automatic query optimization and pushdown computation. It integrates natively with Power BI for visualization and Azure Data Factory for ETL orchestration, making it the ideal primary analytics platform for large-scale, schema-on-read data lake scenarios.
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 SQL Database
Why it's wrong here
Azure SQL Database is a Platform-as-a-Service relational database engine built for online transaction processing (OLTP). It stores data in row-oriented pages on managed storage and is not designed to run petabyte-scale analytical queries directly against external files in a data lake. While it supports some external table features, it lacks the massive parallel processing (MPP) architecture and serverless file querying capabilities of Azure Synapse serverless SQL pool.
When this WOULD be correct
A question where the company needs a fully managed relational database for transactional workloads (e.g., an e-commerce order processing system) with low-latency writes, ACID compliance, and standard SQL access, and does not require direct querying of data lake files or massive parallel processing.
- ✓
Azure Synapse Analytics (serverless SQL pool)
Why this is correct
Correct. Azure Synapse serverless SQL pool can query large volumes of Parquet files directly with T-SQL, provides MPP performance, integrates with Power BI and Data Factory, and is designed for this type of analytical workload.
- ✗
Azure HDInsight with Spark
Why it's wrong here
Azure HDInsight with Spark is a managed big data cluster that can process large Parquet files, but it requires you to provision, size, and manage an active cluster. Querying requires writing Spark SQL, Python, or Scala code rather than using a serverless T-SQL interface, and you pay for cluster uptime even when idle. Azure Synapse serverless SQL pool provides the same big data capability with a pure SQL experience, automatic scaling, and per-query pricing, making it a better fit for teams that prefer SQL.
When this WOULD be correct
A company needs to run custom machine learning algorithms on terabytes of unstructured data (e.g., logs, images) using Python or Scala, with iterative processing and in-memory computation. They also require integration with Azure Machine Learning and do not need instant SQL query results or direct Power BI connectivity.
- ✗
Azure Analysis Services
Why it's wrong here
Azure Analysis Services is an OLAP semantic modeling engine that loads pre-aggregated data into an in-memory tabular model for fast dashboard queries. It is not a query engine that can natively read raw Parquet files from a data lake; you would first need to build and refresh a data model with processed data. It also does not provide the distributed query processing, file-level federation, or serverless cost model that Azure Synapse serverless SQL pool offers for directly analyzing data lake files.
When this WOULD be correct
A company needs to create a semantic data model for enterprise BI reporting, with pre-aggregated measures and KPIs, and wants to connect Power BI to a fast, in-memory tabular model for interactive dashboards. The data is already processed and stored in a relational data warehouse or Azure Synapse dedicated SQL pool.
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 (serverless SQL pool)Correct answer▾
Why this is correct
Correct. Azure Synapse serverless SQL pool can query large volumes of Parquet files directly with T-SQL, provides MPP performance, integrates with Power BI and Data Factory, and is designed for this type of analytical workload.
✗Azure SQL DatabaseWrong answer — click to see why▾
Why this is wrong here
Azure SQL Database is a relational OLTP system not designed for petabyte-scale analytics on Parquet files in Data Lake Storage; it cannot directly query external data in Parquet format without complex import processes, and it lacks the serverless SQL pool's ability to run T-SQL queries directly on data lake files.
★ When this WOULD be the correct answer
A question where the company needs a fully managed relational database for transactional workloads (e.g., an e-commerce order processing system) with low-latency writes, ACID compliance, and standard SQL access, and does not require direct querying of data lake files or massive parallel processing.
Why candidates choose this
Candidates may think Azure SQL Database can handle any SQL workload because it supports T-SQL, and they overlook the need for a massively parallel processing (MPP) architecture to query terabytes of data in seconds, especially when data resides in a data lake.
✗Azure HDInsight with SparkWrong answer — click to see why▾
Why this is wrong here
Azure HDInsight with Spark is primarily a batch processing and big data analytics platform, not optimized for low-latency SQL queries on large datasets. It lacks the serverless SQL pool's ability to run complex SQL queries on data in ADLS Gen2 with sub-second response times, and it does not natively integrate with Power BI and Azure Data Factory as seamlessly as Synapse.
★ When this WOULD be the correct answer
A company needs to run custom machine learning algorithms on terabytes of unstructured data (e.g., logs, images) using Python or Scala, with iterative processing and in-memory computation. They also require integration with Azure Machine Learning and do not need instant SQL query results or direct Power BI connectivity.
Why candidates choose this
Candidates may associate HDInsight with Spark with big data processing on large volumes of data, and mistakenly believe it can handle complex SQL queries quickly. They might overlook that Spark SQL is not as performant for interactive queries as Synapse's serverless SQL pool, and that HDInsight requires more management overhead.
✗Azure Analysis ServicesWrong answer — click to see why▾
Why this is wrong here
Azure Analysis Services is a semantic modeling and OLAP engine, not a primary analytics platform for running complex SQL queries directly on large-scale data in Data Lake Storage. It requires pre-built models and does not natively query Parquet files or support serverless SQL on data lakes.
★ When this WOULD be the correct answer
A company needs to create a semantic data model for enterprise BI reporting, with pre-aggregated measures and KPIs, and wants to connect Power BI to a fast, in-memory tabular model for interactive dashboards. The data is already processed and stored in a relational data warehouse or Azure Synapse dedicated SQL pool.
Why candidates choose this
Candidates may confuse Azure Analysis Services with a general analytics platform because of the word 'Analysis' and its integration with Power BI, overlooking that it is a modeling layer, not a query engine for raw data lakes.
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?”
Quick reference
Cloud Service Model Comparison
| Model | You Manage | Provider Manages | Examples |
|---|---|---|---|
| IaaS | OS, runtime, apps, data | Hardware, hypervisor, networking | EC2, Azure VMs, GCP Compute Engine |
| PaaS | Apps and data | OS, runtime, middleware, hardware | Elastic Beanstalk, Azure App Service |
| SaaS | Data and settings only | Everything else | Microsoft 365, Salesforce, Workday |
| FaaS / Serverless | Function code only | Infra, scaling, runtime | Lambda, Azure Functions, Cloud Run |
| CaaS | Containers and apps | Kubernetes, OS, hardware | EKS, AKS, GKE |
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Data lake
A data lake is a centralized storage repository that holds vast amounts of raw data in its native format until it is needed for analysis.
Key term
ETL
ETL stands for Extract, Transform, Load, a process that moves data from multiple source systems into a single database, data warehouse, or data lake for analysis and reporting.
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.