DP-900 Describe an analytics workload on Azure Practice Question
You are a data engineer for a large e-commerce company. The company uses Azure Data Lake Storage Gen2 to store customer transaction data. They also use Azure Databricks for data transformation and Azure Synapse Serverless SQL pool for ad-hoc queries. Recently, the data lake has grown to 10 TB, and query performance in Synapse Serverless has degraded significantly. Users complain that queries that used to take seconds now take minutes. You need to improve query performance without moving data to a dedicated SQL pool. The data is stored in Parquet format, partitioned by date. You notice that the queries often filter on CustomerID and Date. Current queries scan all partitions even when only a few days are needed. What is the most effective solution to improve performance?
⚠ Common exam trap
A common mix-up: candidates think materialized views (Option A) or scaling up (Option D) can fix performance issues caused by poor data partitioning, but they overlook that serverless SQL pools rely heavily on data layout and partition pruning for efficient query execution.
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
✓
Repartition the Parquet files by both date and CustomerID, and optimize file sizes to 1 GB each
Repartitioning the Parquet files by both date and CustomerID enables partition pruning in Azure Synapse Serverless SQL pool. When queries filter on CustomerID and Date, the engine can skip irrelevant partitions entirely, drastically reducing the amount of data scanned. Optimizing file sizes to around 1 GB ensures efficient parallelism and avoids the overhead of many small files, which degrades performance in a serverless environment.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create materialized views in the Serverless SQL database on the partitioned data
Why it's wrong here
Creating materialized views in a Serverless SQL database is unsupported because serverless pools have no local storage to persist precomputed results; they execute queries on demand against external Parquet files. A regular view would not help either, since row scans and partition pruning are determined entirely by the external data layout and filters. To gain materialization benefits, you would need a dedicated SQL pool or copy the data into a physically indexed store.
- ✗
Convert all Parquet files to CSV and use row-level security to limit data access
Why it's wrong here
Converting Parquet files to CSV is antithetical to analytical workloads because CSV is uncompressed, row-oriented text, requiring full reads and larger I/O, while Parquet's columnar format with compression and predicate pushdown minimizes scanned bytes. Row-level security filters rows after they are read, so it does not reduce the amount of data scanned or the I/O cost. Ultimately this increases latency and storage costs rather than optimizing a high-volume query workload.
- ✓
Repartition the Parquet files by both date and CustomerID, and optimize file sizes to 1 GB each
Why this is correct
Repartitioning Parquet files by date and CustomerID, with roughly 1 GB per file, aligns physical layout with common query predicates, enabling partition pruning so Spark and Synapse only access partitions relevant to filter values. The 1 GB target balances parallel query execution and avoids both many tiny files and overly large files that limit read parallelism. This directly minimizes data scanned and improves query performance for date-and-customer queries.
- ✗
Increase the service level of the Synapse workspace to improve query concurrency
Why it's wrong here
Increasing the Synapse service level (DWU) raises compute resources and concurrency slots, addressing contention and memory limits, but it has no effect on how much data is physically read from storage. A query that must scan all Parquet partitions will still scan all of them regardless of service level. The bottleneck described is data volume, not concurrency, so the only meaningful fix is to reduce scanned data via partitioning.
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 Databricks
Azure Databricks is a fast, easy, and collaborative Apache Spark-based analytics platform optimized for Azure that lets data teams prepare data, run machine learning models, and build data pipelines using a single workspace.
Key term
Data transformation
Data transformation is the process of converting data from one format, structure, or value into another to make it usable for analysis, storage, or reporting.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. 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.