DP-203 Design and implement data storage Practice Question
You need to design a storage solution for IoT device telemetry data that will be queried by time range. The data is append-only and arrives at high velocity. Which TWO features should you use to optimize query performance and reduce costs?
⚠ Common exam trap
Candidates often confuse indexing (B) with partitioning, but for append-only analytical workloads, indexes add write overhead and cost without benefit, while date partitioning directly enables partition elimination for time-range queries.
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 in columnar format (e.g., Parquet)
Option A is correct because storing append-only IoT telemetry in a columnar format such as Parquet enables column pruning and compression, so time-range queries scan only the needed timestamp/value columns and consume far less I/O and storage, reducing cost. Option D is correct because partitioning the data by date (e.g., a partition per day or month) lets the query engine perform partition pruning, skipping entire partitions outside the requested time range, which dramatically improves time-range query performance and lowers scanned data volume. Option B is not appropriate because indexing every column on high-velocity append-only data adds heavy write and storage overhead while providing little benefit for range scans on time-series data. Option C does not belong because row-level security is an access-control feature, not a query-performance or cost optimization. Option E is not relevant because geo-redundant storage improves durability and disaster recovery across regions but does not optimize time-range query performance and typically increases 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.
- ✓
Store data in columnar format (e.g., Parquet)
Why this is correct
Parquet stores data column-wise, so time-range queries read only the timestamp and relevant value columns rather than entire rows, cutting I/O dramatically. Its built-in compression and encoding shrink the append-only telemetry at rest, lowering storage costs while sustaining the high-velocity ingestion the scenario demands.
- ✗
Create indexes on all columns
Why it's wrong here
Indexing every column adds write overhead and storage cost on high-velocity append-only ingestion, and time-range queries need partitioning or clustered columnstore on the timestamp, not broad indexes. It is tempting because indexes accelerate selective lookups in transactional workloads, but here they slow ingestion and inflate cost.
- ✗
Enable row-level security
Why it's wrong here
Row-level security filters which rows a user may read; it does not partition or cluster telemetry by timestamp, so time-range scans still read everything. It is tempting because RLS genuinely restricts data access per user in multi-tenant reporting scenarios, but this stem demands time-based partitioning and indexing, not access control.
- ✓
Partition the data by date
Why this is correct
Date partitioning aligns with the append-only, time-range query pattern: the engine prunes irrelevant partitions, scanning only the required dates rather than the full dataset. This directly satisfies the time-range query constraint while cutting compute and storage costs for high-velocity telemetry.
- ✗
Enable geo-redundant storage
Why it's wrong here
Geo-redundant storage replicates blobs to a secondary region for durability during regional outages; it does not accelerate time-range queries or lower query cost. GRS is the correct choice when disaster recovery across regions is the requirement, not when optimising append-only telemetry reads.
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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
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.