DP-900 Describe core data concepts Practice Question
Your company, Contoso Ltd., operates a global e-commerce platform. The data engineering team ingests over 10 TB of raw clickstream data daily into Azure Data Lake Storage Gen2. The data is partitioned by date and hour. Business analysts need to query this data using Azure Synapse Serverless SQL to generate daily sales reports. However, the reports are taking over 30 minutes to run, and the team needs to improve query performance without moving data to a dedicated SQL pool. You are asked to recommend a solution. Which action should you take?
⚠ Common exam trap
Many exam-takers assume converting file format (Parquet) alone is sufficient, but the question specifically targets reducing data scanned via partition elimination, which is a more direct optimization for partitioned data in serverless SQL.
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
✓
Create external tables using a partition elimination strategy and ensure the data is partitioned by date.
Azure Synapse Serverless SQL can use external tables with partition elimination to skip irrelevant partitions (e.g., date/hour folders) during query execution. This reduces the amount of data scanned, directly improving query performance without moving data. Partition elimination works by filtering on the partition column (e.g., date) in the WHERE clause, allowing the query engine to read only the necessary files.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Convert the data from JSON to Parquet format and apply Snappy compression.
Why it's wrong here
Converting JSON files to Parquet with Snappy compression would reduce file size and enable column pruning, which can definitely speed up scans and lower cost in a serverless SQL pool. However, this alone does not prevent the engine from reading every file that matches the FROM clause; without a date-based folder structure and partition elimination, the query still incurs the full I/O of the entire dataset. The stem does not mandate the current format or rule out this change, but the largest performance improvement comes from partition pruning, so this is not the correct answer.
- ✗
Use Azure Data Factory to copy the data into Azure SQL Database and create indexes.
Why it's wrong here
Using Azure Data Factory to copy the data into Azure SQL Database and then creating indexes moves the workload out of the data lake and into a provisioned relational service. This contradicts the serverless SQL pool model, which is designed to query data in place, and it introduces extra copy latency, storage cost, and administrative overhead. Even though indexes would help queries inside Azure SQL Database, this does not improve the ability to run efficient serverless queries against the original files, and it is not the intended architecture for serverless external data access.
- ✗
Create a dedicated SQL pool and distribute the data across 60 distributions.
Why it's wrong here
Creating a dedicated SQL pool and distributing the data across 60 distributions provisions a separate, always-on capacity that takes the data into internal storage rather than leaving it in the data lake. Serverless SQL pool does not use distributions; that concept applies to dedicated pools where data is sharded across compute nodes. Moving data to a dedicated pool also adds cost and management burden, and it does not satisfy the requirement to improve performance without relocating the data for serverless queries.
- ✓
Create external tables using a partition elimination strategy and ensure the data is partitioned by date.
Why this is correct
Creating external tables with a partition elimination strategy and partitioning the data by date lets the serverless SQL engine skip whole file ranges that do not satisfy the query's predicate, for example when a WHERE clause filters on a date column. You should store the data in a hive-style layout such as /orders/year=2024/month=03 and define the external table so the engine can map the folders to partition columns. This reduces the byte count scanned dramatically and is the biggest lever for performance and cost in a serverless SQL pool.
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
Column
A column is a vertical set of values in a database table that stores one specific type of attribute for every row.
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
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.