Query Azure Data Lake with Serverless SQL
A retail company stores years of historical sales data in Azure Data Lake Storage Gen2 as Parquet files. Business analysts need to run complex SQL queries over this data to identify sales trends, and they want to visualize the results in Power BI dashboards. They prefer to avoid moving data into a separate database to minimize storage costs and latency. Which Azure service should they use to query the data directly in the lake?
Quick Answer
The answer is Azure Synapse Analytics, specifically its serverless SQL pool capability. This is the correct choice because it allows business analysts to query Azure Data Lake with serverless SQL directly against Parquet files in Azure Data Lake Storage Gen2 using standard T-SQL, eliminating the need to move or copy data into a separate database. On the Microsoft Azure Data Fundamentals DP-900 exam, this scenario tests your understanding of how serverless SQL pools enable on-demand querying of data lakes for ad-hoc analysis and Power BI integration, often contrasting with dedicated SQL pools that require provisioning and storage. A common trap is selecting Azure SQL Database or Azure Databricks, but remember that serverless SQL is purpose-built for querying data in place without infrastructure management. Memory tip: think “serverless = no server to manage, no data to move” — if the question emphasizes avoiding data movement and minimizing latency, serverless SQL pool is your answer.
⚠ Common exam trap
Many candidates confuse Azure Data Factory as a query service because it can transform data, but it is an orchestration tool, not an interactive SQL query engine for ad-hoc analysis.
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 provides the serverless SQL pool capability that allows you to query data directly in Azure Data Lake Storage Gen2 using T-SQL without moving or copying the data. This enables business analysts to run complex SQL queries over Parquet files in the lake and connect the results to Power BI for visualization, minimizing storage costs and latency by avoiding a separate database.
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
Correct. The serverless SQL pool in Azure Synapse Analytics can query Parquet files directly in the data lake, supports T-SQL, and integrates with Power BI.
- ✗
Azure SQL Database
Why it's wrong here
Azure SQL Database requires data to be loaded into tables, which adds latency and cost. It is not designed for direct querying of data lake files.
When this WOULD be correct
A company needs a fully managed relational database for transactional workloads with low-latency queries, and they are willing to import data from various sources into a structured schema for OLTP applications.
- ✗
Azure Data Factory
Why it's wrong here
Azure Data Factory is an orchestration and ETL service, not an interactive query engine. It can move data but does not support direct SQL queries on data lake files for analytics.
When this WOULD be correct
A company needs to ingest data from multiple on-premises sources into Azure Data Lake Storage Gen2 on a scheduled basis, transforming it during the process. Azure Data Factory would be the correct choice for this ETL orchestration.
- ✗
Azure Analysis Services
Why it's wrong here
Azure Analysis Services is used to build semantic models and requires data to be imported from sources. It does not directly query raw files in the data lake.
When this WOULD be correct
A company has a data warehouse in Azure SQL Database or Azure Synapse Analytics and wants to create a semantic model for business users to perform ad-hoc analysis and drill-downs, with the model cached in memory for fast performance. They would use Azure Analysis Services to connect to the warehouse and provide a tabular model for Power BI.
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
Correct. The serverless SQL pool in Azure Synapse Analytics can query Parquet files directly in the data lake, supports T-SQL, and integrates with Power BI.
✗Azure SQL DatabaseWrong answer — click to see why▾
Why this is wrong here
Azure SQL Database requires data to be imported into its relational store, contradicting the requirement to avoid moving data and minimize latency. It cannot directly query Parquet files in Data Lake Storage Gen2.
★ When this WOULD be the correct answer
A company needs a fully managed relational database for transactional workloads with low-latency queries, and they are willing to import data from various sources into a structured schema for OLTP applications.
Why candidates choose this
Candidates may associate SQL Database with running SQL queries and Power BI integration, overlooking the constraint that data must remain in the lake without being moved.
✗Azure Data FactoryWrong answer — click to see why▾
Why this is wrong here
Azure Data Factory is an ETL and data orchestration service, not a query engine. It cannot run complex SQL queries directly against Parquet files in Data Lake Storage Gen2.
★ When this WOULD be the correct answer
A company needs to ingest data from multiple on-premises sources into Azure Data Lake Storage Gen2 on a scheduled basis, transforming it during the process. Azure Data Factory would be the correct choice for this ETL orchestration.
Why candidates choose this
Candidates may confuse Azure Data Factory's data movement and transformation capabilities with querying, assuming it can also perform ad-hoc SQL analysis on data in the lake.
✗Azure Analysis ServicesWrong answer — click to see why▾
Why this is wrong here
Azure Analysis Services is a semantic modeling and OLAP engine, not designed for direct querying of data in a data lake. It requires data to be loaded into its in-memory cache or queried via DirectQuery from a relational source, not from Parquet files in Azure Data Lake Storage Gen2.
★ When this WOULD be the correct answer
A company has a data warehouse in Azure SQL Database or Azure Synapse Analytics and wants to create a semantic model for business users to perform ad-hoc analysis and drill-downs, with the model cached in memory for fast performance. They would use Azure Analysis Services to connect to the warehouse and provide a tabular model for Power BI.
Why candidates choose this
Candidates may confuse Azure Analysis Services with a query service for data lakes because of the word 'Analysis' and its ability to connect to Power BI, but it is not a direct query engine for data lake storage.
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
Serverless SQL pool
Serverless SQL pool is an on-demand, pay-per-query analytics service in Azure that lets you query data stored in data lakes without provisioning or managing any dedicated infrastructure.
Key term
Power BI
Power BI is a Microsoft business analytics tool that transforms raw data into interactive visual reports and dashboards for informed decision-making.
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 →
Same concept, more angles
2 more ways 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. An organization needs to run complex queries on petabytes of data stored in Azure Data Lake Storage. They want to use serverless compute to avoid managing infrastructure. Which Azure service should they use?
easy- A.Azure Analysis Services
- ✓ B.Azure Synapse Serverless SQL pool
- C.Azure HDInsight
- D.Azure SQL Database
Why B: Azure Synapse Serverless SQL pool is the correct choice because it provides serverless compute that can run complex T-SQL queries directly against data stored in Azure Data Lake Storage without requiring any infrastructure management. It uses a pay-per-query billing model and can scale automatically to handle petabytes of data, making it ideal for ad-hoc analytics on large-scale data lakes.
Variation 2. A data engineer needs to query data stored in CSV files in Azure Data Lake Storage Gen2 using T-SQL in Azure Synapse Analytics, without loading the data into the database. Which feature should they use?
medium- ✓ A.External tables
- B.Materialized views
- C.Stored procedures
- D.Indexed views
Why A: External tables in Azure Synapse Analytics allow you to query data stored in files (such as CSV in Azure Data Lake Storage Gen2) using T-SQL without loading the data into the database. They use the PolyBase engine to read the files directly, enabling on-the-fly querying of external data sources.
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.