DP-203 Design and implement data storage Practice Question
A healthcare company stores patient records in Azure Data Lake Storage Gen2. The data must be organized for efficient querying by a Synapse Analytics serverless SQL pool. The data is currently stored as many small CSV files in a flat directory. You need to improve query performance and reduce the cost of scanning unnecessary data. What should you do?
⚠ Common exam trap
The trap here is focusing on file count or storage service rather than the data format and partitioning, which are the primary drivers of query performance and cost in serverless SQL pools.
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
✓
Convert the data to Parquet format and partition it by admission date.
Converting to Parquet, a columnar format, enables column pruning and better compression, reducing the amount of data scanned. Partitioning by admission date allows the serverless SQL pool to prune partitions based on query filters. Together, these changes optimize query performance and lower cost.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Combine all CSV files into one large CSV file.
Why it's wrong here
Combining files into a single large CSV file may reduce the number of files but does not provide columnar storage or partition pruning. CSV is row-based, so queries still read all columns even if only a few are needed. This approach does not leverage the performance benefits of columnar formats or partitioning, and large files can be unwieldy.
- ✗
Create an external table in the serverless SQL pool that points to the CSV files.
Why it's wrong here
Creating an external table over CSV files does not change the underlying format; queries will still scan all columns and files unless filters are applied. While external tables provide a schema, they do not inherently improve performance or reduce cost for CSV data. The data remains row-based and unpartitioned, leading to full scans.
- ✗
Move the data to Azure Blob Storage and enable hierarchical namespace.
Why it's wrong here
Moving to Blob Storage with hierarchical namespace (ADLS Gen2) is already the current storage. Enabling hierarchical namespace does not change the data format or partitioning. It provides directory semantics but does not improve query performance for CSV files. The key issue is the file format and layout, not the storage service.
- ✓
Convert the data to Parquet format and partition it by admission date.
Why this is correct
Parquet is a columnar format that enables efficient compression and column pruning, reducing I/O and cost. Partitioning by admission date allows the serverless SQL pool to skip irrelevant partitions, further minimizing data scanned. This combination significantly improves query performance and reduces cost for date-filtered queries.
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
About these practice questions
Courseiva writes every DP-203 question from scratch — 509 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 and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-203 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-203 exam.