DP-203 Design and implement data storage Practice Question
You are designing a storage layer for an Azure Synapse Analytics dedicated SQL pool that ingests 4 TB of CSV files daily into a fact table. The files are landed in Azure Data Lake Storage Gen2 by an external ETL process. You need to load the data with the highest possible throughput while minimizing the load window. What should you do?
⚠ Common exam trap
The trap here is assuming that any supported ingestion method (PolyBase, Data Factory, COPY) will automatically deliver maximum throughput without considering file layout and parallelism.
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 COPY statement with a shared access signature (SAS) token and split the source files into multiple evenly sized files.
The COPY statement is the preferred high-throughput ingestion mechanism for dedicated SQL pools, especially when reading from ADLS Gen2 with SAS authentication. Splitting the source files into evenly sized chunks enables parallel reads across distributions, which is essential for loading 4 TB within a tight window. Combining COPY with proper file sizing directly addresses the throughput and load-window 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.
- ✓
Use the COPY statement with a shared access signature (SAS) token and split the source files into multiple evenly sized files.
Why this is correct
The COPY statement is the recommended high-throughput ingestion method for dedicated SQL pools and supports SAS authentication to ADLS Gen2. Splitting large source files into multiple evenly sized files allows the COPY statement to parallelize reads across distributions, dramatically reducing the load window. This combination is the documented best practice for maximizing ingestion throughput in Synapse dedicated SQL pools.
- ✗
Use the COPY statement with a shared access signature (SAS) token and set MAXDOP to a value equal to the number of files.
Why it's wrong here
COPY is a valid high-throughput ingestion method, but setting MAXDOP equal to the number of files is not a supported or recommended tuning approach. MAXDOP controls parallelism at the compute level and should be aligned with the number of distributions and file layout, not arbitrarily to file count. This configuration could cause resource contention and does not guarantee the fastest load.
- ✗
Use PolyBase with an external table and enable REJECT_TYPE VALUE with a threshold of 0.
Why it's wrong here
PolyBase is capable of high-throughput loads, but setting REJECT_TYPE VALUE with threshold 0 forces the load to fail on any rejected row, which is risky for large daily files that may contain minor formatting anomalies. This does not improve throughput; it only changes error handling. The scenario asks for maximum throughput with minimal load window, not strict rejection semantics.
- ✗
Use Azure Data Factory with a copy activity that has a single pipeline and a single parallel copy thread.
Why it's wrong here
Azure Data Factory can orchestrate loads, but a single pipeline with a single parallel copy thread severely limits throughput. For 4 TB of daily data, this would create a bottleneck and extend the load window. While Data Factory is useful for orchestration, it does not provide the same native high-throughput parallel ingestion as the COPY statement with properly sized files.
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.