DP-203 Design and implement data storage Practice Question
A retail analytics team stores Parquet files in Azure Data Lake Storage Gen2 partitioned by year, month, and day. Queries in Azure Synapse serverless SQL pools filter on a transaction date column, but performance is poor because the engine scans all files in the folder hierarchy. You need to reduce the amount of data scanned without changing the file layout. What should you implement?
⚠ Common exam trap
The trap here is believing that simply querying through an external table automatically prunes partitions, when elimination requires explicit path-based predicates such as filepath().
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
✓
Use the filepath() function in the OPENROWSET query to filter on the year, month, and day segments of the folder path.
Files stored in a year/month/day hierarchy can be pruned by exposing the path segments as virtual columns through the filepath() function and filtering on them. The serverless engine then skips folders that do not match, cutting the amount of data scanned. External tables without partition elimination, dedicated pool loading, or result set caching do not address the scan volume for arbitrary filtered queries.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use the filepath() function in the OPENROWSET query to filter on the year, month, and day segments of the folder path.
Why this is correct
The filepath() function exposes virtual columns derived from the folder path, so filtering on those columns lets the serverless engine eliminate entire folders and read only matching files. This reduces scanned data without moving or rewriting the Parquet files.
- ✗
Enable result set caching on the serverless database and set the cache retention period to seven days.
Why it's wrong here
Result set caching stores the output of previously executed queries, so it only helps when an identical query is re-run. It does not reduce scanning for new or differently filtered queries, and serverless SQL pools have limited caching behavior compared to dedicated pools.
- ✗
Create an external data source and external table over the folder and query the external table instead of using OPENROWSET directly.
Why it's wrong here
An external table provides a familiar schema over the files, but without explicit partition elimination it still scans the same set of files. It does not by itself reduce the data read unless partition pruning metadata is defined, so performance would remain largely unchanged.
- ✗
Create a partitioned table in a dedicated SQL pool and load the Parquet files into it using PolyBase.
Why it's wrong here
A dedicated SQL pool is a provisioned resource with its own cost and does not use the serverless engine's file-scanning optimizations. Loading data into it changes the storage and compute model, which exceeds the stated requirement of reducing scanned data without altering the file layout.
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.