DP-203 Develop data processing Practice Question
You are processing a large dataset in Azure Synapse Analytics using a dedicated SQL pool. You need to load data from Parquet files in Azure Data Lake Storage Gen2 into a staging table, then transform and load into a fact table. The fact table is partitioned by date. You want to maximize query performance and minimize data movement. Which technique should you use?
⚠ Common exam trap
The trap here is assuming that BULK INSERT supports Parquet files; it does not, and using it would fail or require conversion.
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 PolyBase to load data into a staging table, then use CREATE TABLE AS SELECT (CTAS) to transform and load into the fact table
PolyBase is the most efficient way to load Parquet files into a staging table in a dedicated SQL pool. CTAS then transforms and loads the data into a new table, allowing you to define the distribution and partitioning to optimize query performance. This minimizes data movement because the transformation and load happen within the SQL pool. The other options either use unsupported features, cause inefficient updates, or add unnecessary data movement.
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 Azure Data Factory Copy activity to load data into the fact table, then run an UPDATE statement to transform
Why it's wrong here
Using Copy activity to load directly into the fact table bypasses the staging and transformation steps. Running UPDATE statements on a large fact table is inefficient and causes data movement. This approach does not minimize data movement and can lead to performance issues. It also lacks the ability to partition and distribute optimally during load.
- ✗
Use CETAS to export the transformed data to Azure Data Lake Storage Gen2, then use PolyBase to load into the fact table
Why it's wrong here
CETAS (Create External Table As Select) exports data to external storage, which adds an extra step of writing to and reading from Data Lake Storage Gen2. This increases data movement and latency. While it can be useful for data sharing, it is not the most efficient for loading into a dedicated SQL pool fact table when the data is already in the pool.
- ✗
Use BULK INSERT to load data directly into the fact table
Why it's wrong here
BULK INSERT is not supported for Parquet files in Azure Synapse Analytics dedicated SQL pools; it is primarily for delimited text files. Also, loading directly into the fact table without transformation may not align with the partitioning and distribution. It could lead to poor performance and does not allow for transformation during load.
- ✓
Use PolyBase to load data into a staging table, then use CREATE TABLE AS SELECT (CTAS) to transform and load into the fact table
Why this is correct
PolyBase efficiently loads Parquet files from Azure Data Lake Storage Gen2 into a staging table. CTAS then creates a new table with the transformed data, which can be partitioned and distributed optimally. This approach minimizes data movement because CTAS writes directly to the new table, and you can define the distribution and partitioning to match the fact table's requirements, improving query performance.
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 509-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 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.