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 provisions fixed compute and storage that is billed continuously, regardless of query volume, and it loads data rather than querying CSV files in place. It is tempting because it runs T-SQL at scale, but the infrequent, pay-per-data-processed, no-infrastructure requirement points to serverless SQL pool.
- ✗
Azure Data Factory
Why it's wrong here
Azure Data Factory is an orchestration and data-integration service for pipelines and scheduled movement, not a query engine, so it cannot run ad-hoc SQL over CSV files. It is tempting because it reads Data Lake Storage Gen2, but the pay-per-data-processed, serverless query requirement is met by Azure Synapse serverless SQL pool.
- ✓
Azure Synapse Serverless SQL pool
Why this is correct
Synapse serverless SQL pool queries CSV files in Data Lake Storage Gen2 directly, charging per terabyte of data processed. It provides on-demand, infrastructure-free querying, matching the infrequent, unpredictable ad-hoc workload and the pay-per-query constraint without provisioning compute.
- ✗
Azure Analysis Services
Why it's wrong here
Azure Analysis Services hosts semantic tabular models for BI reporting over pre-processed data; it does not query raw CSV files in Data Lake Storage Gen2 directly. It is tempting because it delivers SQL-like querying, but it requires a provisioned server and a loaded model, not serverless per-query billing.
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
SQL Querying Basics
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
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 851-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.