Azure Synapse Serverless SQL Pool for Ad-Hoc Pay-Per-Query Analytics
A data analyst needs to run ad-hoc SQL queries on terabytes of CSV files stored in Azure Data Lake Storage Gen2. The queries are infrequent and unpredictable. The analyst wants to pay only for the amount of data processed by each query, and does not want to manage any compute or storage infrastructure. Which Azure service should they use?
Quick Answer
The answer is Azure Synapse Serverless SQL pool because it enables ad-hoc queries on terabytes of CSV files in Azure Data Lake Storage Gen2 without requiring any provisioned compute or infrastructure management. This serverless endpoint processes data on-demand using T-SQL, charging only for the amount of data scanned per query—making it perfect for infrequent, unpredictable workloads where you want pay-per-query billing. On the DP-900 exam, this scenario tests your understanding of the difference between serverless and dedicated SQL pools: a common trap is choosing Azure SQL Database or Azure Databricks, but remember that serverless SQL pool is the only option that eliminates compute management entirely while billing per terabyte processed. A helpful memory tip is "Serverless for sporadic, serverless saves spending"—if the workload is unpredictable and you don’t want to manage resources, think serverless SQL pool for ad-hoc analytics on data lakes.
⚠ Common exam trap
Watch out — candidates often confuse Azure Synapse Serverless SQL pool with Azure Synapse Analytics dedicated SQL pool, assuming both require provisioning compute, but the serverless option is specifically designed for on-demand, pay-per-query workloads with no infrastructure management.
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 Serverless SQL pool
Azure Synapse Serverless SQL pool (C) is the correct choice because it allows querying data in Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute resources. It charges per terabyte of data processed, making it ideal for infrequent, unpredictable ad-hoc queries, and it eliminates infrastructure management.
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 requires provisioning and paying for allocated compute resources continuously, even when no queries are running, which is not cost-effective for infrequent queries.
- ✗
Azure Data Factory
Why it's wrong here
Azure Data Factory is an orchestration service for data pipelines, not a query engine. It cannot directly execute SQL queries against files in the data lake.
- ✓
Azure Synapse Serverless SQL pool
Why this is correct
Serverless SQL pool allows on-demand SQL querying of data in the data lake, paying only for the data processed per query, with zero infrastructure management.
- ✗
Azure Analysis Services
Why it's wrong here
Azure Analysis Services is used to build semantic models (tabular or multidimensional) for reporting, not for direct ad-hoc querying of raw files in the data lake.
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
Service
A service is a software component or system that performs a specific function and is available to be used by other programs or users over a network.
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
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 →
Same concept, more angles
1 more way this is tested on DP-900
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A data analyst needs to run ad-hoc SQL queries on large volumes of data stored as Parquet files in Azure Data Lake Storage Gen2. The queries are unpredictable, and the analyst wants to pay only for the compute resources consumed by each query. Which Azure Synapse Analytics compute model should be used?
hard- ✓ A.Serverless SQL pool
- B.Dedicated SQL pool
- C.Apache Spark pool
- D.Azure Data Explorer pool
Why A: Serverless SQL pool is the correct choice because it allows running ad-hoc SQL queries directly on data in Azure Data Lake Storage Gen2 without provisioning any fixed compute resources. It uses a pay-per-query billing model, charging only for the amount of data processed by each query, which aligns perfectly with the unpredictable query patterns described.
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.