DP-900 Describe an analytics workload on Azure Practice Question
A company stores terabytes of historical sales data as Parquet files in Azure Data Lake Storage Gen2. Business analysts need to run ad-hoc SQL queries that involve complex joins and aggregations over this data. They want to avoid provisioning a dedicated cluster or moving data into a separate database. The queries must be executed using standard T-SQL syntax. Which Azure service should they use?
⚠ Common exam trap
Many candidates confuse 'serverless SQL pool' with 'dedicated SQL pool' (Option A), assuming both require provisioning, or they mistakenly think Databricks (Option C) supports standard T-SQL, when it actually uses Spark SQL or Python.
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 (B) is the correct choice because it allows you to query Parquet files directly in Azure Data Lake Storage Gen2 using standard T-SQL syntax without provisioning any dedicated cluster or moving data. It automatically scales compute resources to handle complex joins and aggregations on terabytes of data, charging only for the data processed. This matches the requirement for ad-hoc, serverless querying with familiar T-SQL.
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 it's wrong here
A dedicated SQL pool in Azure Synapse Analytics requires you to provision a fixed cluster size (e.g., DWUs or cDWUs) and pays per hour even when idle. It is designed for high-performance data warehousing over data loaded into its columnar tables, not for ad hoc serverless queries directly over external Parquet files. To query Data Lake files, you would typically create external tables or use the COPY statement, but then you still manage capacity, scaling, and cost, contradicting the requirement of no provisioning and no data movement.
When this WOULD be correct
A company needs consistent, high-performance querying on large datasets with predictable workloads, and is willing to provision a dedicated cluster and manage data loading. The question would specify that performance and concurrency are critical, and the analysts can tolerate data movement and cluster management overhead.
- ✓
Azure Synapse Analytics serverless SQL pool
Why this is correct
Azure Synapse Analytics serverless SQL pool directly queries Parquet files in Azure Data Lake Storage using standard T-SQL via OPENROWSET, without provisioning any compute infrastructure. It scales automatically to handle terabytes of data, charges only for the data processed per query, and requires no data movement or loading into a dedicated store. This makes it the only option that combines ad hoc serverless access, T-SQL syntax, and a pure pay-per-query model for historical sales data.
- ✗
Azure Databricks
Why it's wrong here
Azure Databricks is a managed Apache Spark platform where you create clusters and pay for compute nodes. Querying Parquet data uses Spark SQL or DataFrames, which are not native T-SQL and lack the direct OPENROWSET-style serverless T-SQL semantics of Synapse serverless SQL. Although Databricks SQL provides SQL-like syntax, it still requires a running cluster and configuration, so it cannot satisfy the demand for a zero-provisioning, query-only T-SQL solution over data lake files.
When this WOULD be correct
A company needs to perform advanced analytics and machine learning on large datasets using Apache Spark, with support for collaborative notebooks and automated cluster management. The question would specify the need for Spark-based processing or ML workflows.
- ✗
Azure HDInsight
Why it's wrong here
Azure HDInsight is a managed cluster service that provisions Hadoop, Spark, Hive, or other big data frameworks on VMs, requiring you to choose cluster type, node sizes, and scaling, and incurring continuous compute cost. It does not offer a serverless T-SQL query interface; instead, you typically run HiveQL or Spark jobs that need cluster startup and management. While HDInsight can read Parquet from Azure Storage, it fails to meet the serverless, no-provisioning, or direct-T-SQL conditions of the requirement.
When this WOULD be correct
A question requiring processing of massive unstructured or semi-structured data (e.g., terabytes of clickstream logs) using custom MapReduce, Spark, or Hive jobs, where a managed cluster with flexible scaling is needed and T-SQL is not a requirement.
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 poolCorrect answer▾
Why this is correct
Azure Synapse Analytics serverless SQL pool directly queries Parquet files in Azure Data Lake Storage using standard T-SQL via OPENROWSET, without provisioning any compute infrastructure. It scales automatically to handle terabytes of data, charges only for the data processed per query, and requires no data movement or loading into a dedicated store. This makes it the only option that combines ad hoc serverless access, T-SQL syntax, and a pure pay-per-query model for historical sales data.
✗Azure Synapse Analytics dedicated SQL poolWrong answer — click to see why▾
Why this is wrong here
A dedicated SQL pool requires provisioning a fixed cluster and incurs ongoing costs even when idle, contradicting the requirement to avoid provisioning a dedicated cluster. It also typically involves moving data into the pool, which the question explicitly wants to avoid.
★ When this WOULD be the correct answer
A company needs consistent, high-performance querying on large datasets with predictable workloads, and is willing to provision a dedicated cluster and manage data loading. The question would specify that performance and concurrency are critical, and the analysts can tolerate data movement and cluster management overhead.
Why candidates choose this
Candidates may confuse 'dedicated SQL pool' with the general SQL analytics capabilities of Azure Synapse, not realizing that 'serverless' is the option for ad-hoc, on-demand querying without provisioning. They might also assume that dedicated pools are the standard way to run T-SQL on large data.
✗Azure DatabricksWrong answer — click to see why▾
Why this is wrong here
Azure Databricks is optimized for big data analytics and machine learning using Spark, not for ad-hoc SQL queries with standard T-SQL syntax. It requires provisioning a cluster and does not natively support T-SQL without additional configuration.
★ When this WOULD be the correct answer
A company needs to perform advanced analytics and machine learning on large datasets using Apache Spark, with support for collaborative notebooks and automated cluster management. The question would specify the need for Spark-based processing or ML workflows.
Why candidates choose this
Candidates may associate Azure Databricks with big data and Parquet files, overlooking the requirement for standard T-SQL syntax and the desire to avoid cluster provisioning.
✗Azure HDInsightWrong answer — click to see why▾
Why this is wrong here
HDInsight requires provisioning a dedicated cluster and does not support ad-hoc T-SQL queries without additional configuration; it is primarily for big data processing with Hadoop/Spark, not serverless SQL.
★ When this WOULD be the correct answer
A question requiring processing of massive unstructured or semi-structured data (e.g., terabytes of clickstream logs) using custom MapReduce, Spark, or Hive jobs, where a managed cluster with flexible scaling is needed and T-SQL is not a requirement.
Why candidates choose this
Candidates may associate HDInsight with big data and Parquet files, overlooking that it lacks serverless SQL capabilities and requires cluster management, unlike the serverless SQL pool.
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
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
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.
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.