DP-203 Design and implement data storage Practice Question
You are designing a storage solution for a healthcare analytics platform. The platform ingests large volumes of structured patient records stored as Parquet files in Azure Data Lake Storage Gen2. Analysts query this data using Azure Synapse Analytics serverless SQL pools. To minimize query cost and improve performance, you need to choose an appropriate file organization and table type. What should you do?
⚠ Common exam trap
The trap here is assuming that any file format works equally well with serverless SQL pools, ignoring the cost and performance benefits of columnar storage and partitioning.
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
✓
Store data as Parquet files partitioned by date, and query them using an external table in a serverless SQL pool.
Storing data in Parquet format with date-based partitioning and querying via external tables in a serverless SQL pool minimizes data scanned and cost, while improving query performance. Parquet's columnar nature and compression reduce I/O, and partitioning enables partition elimination. This aligns with best practices for cost-effective analytics on large datasets in Azure Synapse Analytics.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Store data as CSV files partitioned by date, and query them using an external table in a serverless SQL pool.
Why it's wrong here
CSV is row-based and not compressed by default, leading to higher data scanned and increased cost in serverless SQL pools. While partitioning helps, the lack of columnar storage and compression makes this less efficient than Parquet. This option does not meet the goal of minimizing query cost and improving performance.
- ✗
Store data as JSON files and query them using OPENROWSET in a serverless SQL pool.
Why it's wrong here
JSON is not a columnar format and parsing it at query time adds CPU overhead, increasing cost and reducing performance. While OPENROWSET can query JSON, it is less efficient than Parquet for structured data. This option does not leverage the benefits of columnar storage and partitioning.
- ✗
Store data as Parquet files in a dedicated SQL pool table with hash distribution on patient ID.
Why it's wrong here
A dedicated SQL pool requires provisioning and incurs cost even when idle, which may not be cost-effective for intermittent analytical queries. The scenario specifies serverless SQL pools, so using a dedicated pool contradicts the design. Additionally, hash distribution is not relevant for serverless queries.
- ✓
Store data as Parquet files partitioned by date, and query them using an external table in a serverless SQL pool.
Why this is correct
Parquet is a columnar format that reduces I/O and cost in serverless SQL pools. Partitioning by date enables partition elimination, further reducing data scanned. External tables allow querying data in place without loading, which is ideal for ad-hoc analytics and cost control. This combination directly addresses the requirement to minimize cost and improve performance.
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.