DP-203 Design and implement data storage Practice Question
You are designing a data storage solution for a financial analytics platform. The platform ingests CSV files into Azure Data Lake Storage Gen2 and processes them with Azure Synapse Analytics serverless SQL pools. Queries frequently filter on a transaction date column and a region column, but the files are currently organized in a flat folder structure. You need to minimize the amount of data scanned by serverless SQL queries while keeping the files queryable using standard T-SQL OPENROWSET. What should you do?
⚠ Common exam trap
The trap here is assuming that converting to Parquet alone solves the scanning problem, when folder-based partitioning is what enables partition elimination 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
✓
Partition the data by transaction date and region using a Hive-style folder hierarchy, then query with OPENROWSET and a wildcard path.
Organizing files into a Hive-style folder hierarchy by transaction date and region allows the serverless SQL pool to perform partition elimination. When the query filters on those virtual columns, only the relevant folders are read, reducing scanned bytes and cost. The data remains fully queryable with standard T-SQL OPENROWSET, satisfying both the performance and compatibility requirements.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Move the files into an Azure Blob Storage container and query them using the Blob Storage REST API.
Why it's wrong here
Moving data to Blob Storage does not provide partition elimination for serverless SQL queries, and the REST API is not a T-SQL query interface. This also removes the hierarchical namespace benefits of Data Lake Storage Gen2. The requirement is to keep standard T-SQL OPENROWSET queries while scanning less data, which this option does not achieve.
- ✓
Partition the data by transaction date and region using a Hive-style folder hierarchy, then query with OPENROWSET and a wildcard path.
Why this is correct
A Hive-style hierarchy such as /year=2024/month=03/region=us/ enables serverless SQL pools to perform partition elimination when the query filters on those columns and uses FILEPATH or the appropriate wildcard path. This reduces the bytes scanned, lowering cost and latency, and keeps the data queryable through standard T-SQL OPENROWSET without any additional service.
- ✗
Convert all CSV files to Parquet and place them in a single folder without subfolders.
Why it's wrong here
Parquet is columnar and compressed, which helps, but without a folder hierarchy the serverless SQL pool cannot eliminate partitions by date or region. It would still scan every Parquet file for each query, so the filtering benefit is limited and costs remain high. This does not meet the requirement to minimize scanned data based on the two filter columns.
- ✗
Create an external table in the serverless SQL pool over the entire folder path and add a clustered columnstore index.
Why it's wrong here
Serverless SQL pools support external tables, but you cannot create a clustered columnstore index on an external table; indexes are not supported on external data. Even if the table were created, it would not inherently prune files by date or region. This approach does not reduce the data scanned and is technically invalid for the scenario.
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
One of 509 original DP-203 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 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.