mediumMultiple ChoiceObjective-mapped
DP-203 Efficient load into Synapse dedicated pool Practice Question
You have a Synapse Analytics dedicated SQL pool. You need to load 100 GB of CSV data from Azure Data Lake Storage Gen2 into a fact table. The table has a hash-distributed column. Which pattern is most efficient for loading with minimal impact on concurrent queries?
⚠ Common exam trap
It's easy for candidates to assume PolyBase or COPY INTO are always the fastest for bulk loading, but they overlook that CTAS with the correct distribution key avoids the costly data redistribution step required by other methods.
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 CREATE TABLE AS SELECT (CTAS) with the hash-distributed column
CTAS with a hash-distributed column loads data directly into the target table with the same distribution scheme, avoiding data movement and minimizing resource contention. This pattern is optimized for bulk loading large datasets into a hash-distributed fact table, as it leverages the Synapse SQL pool's MPP architecture to parallelize the operation without blocking concurrent 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 PolyBase INSERT...SELECT with rowstore table
Why it's wrong here
INSERT...SELECT logs each row and may cause concurrency issues; not as efficient as CTAS for large loads.
- ✗
Use COPY INTO command with a round-robin distribution
Why it's wrong here
COPY INTO is efficient but round-robin distribution may cause data movement later; CTAS with hash distribution avoids extra steps.
- ✗
Use Azure Data Factory Copy activity with staging enabled
Why it's wrong here
Copy activity with staging uses PolyBase under the hood but adds overhead; CTAS is more direct.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-203 exam frequently reuses these exact scenarios with slightly different constraints.
✓Use CREATE TABLE AS SELECT (CTAS) with the hash-distributed columnCorrect answer▾
✗Use PolyBase INSERT...SELECT with rowstore tableWrong answer — click to see why▾
Why this is wrong here
INSERT...SELECT logs each row and may cause concurrency issues; not as efficient as CTAS for large loads.
✗Use COPY INTO command with a round-robin distributionWrong answer — click to see why▾
Why this is wrong here
COPY INTO is efficient but round-robin distribution may cause data movement later; CTAS with hash distribution avoids extra steps.
✗Use Azure Data Factory Copy activity with staging enabledWrong answer — click to see why▾
Why this is wrong here
Copy activity with staging uses PolyBase under the hood but adds overhead; CTAS is more direct.
Analysis generated from the official DP-203blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 760-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.