Develop data processing →mediumMultiple Select

DP-203 Develop data processing Practice Question

Which TWO actions can you take to optimize the performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

⚠ Common exam trap

Many exam-takers confuse the purpose of indexes and distribution types, mistakenly thinking that adding indexes on all columns will speed up loading, when in fact it degrades performance, and they overlook that ROUND_ROBIN is specifically designed for fast staging loads, not for query performance.

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 ROUND_ROBIN distribution for the staging table

Option B is correct because a ROUND_ROBIN distributed staging table spreads incoming rows evenly across all distributions without requiring a distribution key, which maximizes parallel ingestion throughput and avoids data movement during the load before the data is redistributed into the final table. Option E is correct because CTAS performs a parallel, minimally logged bulk operation that creates a new table with the desired distribution and indexing in one step, and combining it with partition switching lets you swap the fully loaded table into the target quickly and efficiently. Option A is not appropriate because nonclustered indexes on every column slow down bulk loads and consume extra storage; dedicated SQL pools rely primarily on clustered columnstore indexes. Option C is wrong because the optimal row group size for columnstore compression is around 1,048,576 rows (1 million), not 100,000, which yields smaller, less efficient row groups. Option D is incorrect because change tracking is used for incremental data synchronization scenarios and does not improve bulk load performance.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Create nonclustered indexes on all columns of the target table
Why it's wrong here
Nonclustered indexes on every column slow bulk inserts because each row must update every index structure, and dedicated SQL pools favour clustered columnstore for large loads. They suit selective point lookups on small tables, not high-volume ingestion into a columnstore fact table.
✓
Use ROUND_ROBIN distribution for the staging table
Why this is correct
ROUND_ROBIN distributes staging rows evenly across all distributions without requiring a distribution key, avoiding skew and data-movement overhead during the load. This maximises parallel ingestion throughput into the staging table before the final CTAS into the production table.
✗
Set the row group size to 100,000 rows for optimal compression
Why it's wrong here
A 100,000-row row group is far below the 1,048,576-row maximum that maximises columnstore compression and query performance; small row groups waste space and add overhead. This setting would suit tables where the full million rows cannot be accumulated before a load commits.
✗
Enable change tracking on the target table
Why it's wrong here
Change tracking records row-level changes for downstream consumption, adding overhead during bulk loads and offering no load-performance gain. It would be correct when synchronising changed data to external systems. Dedicated SQL pool load tuning uses PolyBase, CTAS, and distribution choices instead.
✓
Use CREATE TABLE AS SELECT (CTAS) with partition switching
Why this is correct
CTAS writes transformed data into a new table using minimal logging and parallel inserts, then partition switching swaps it in as metadata, avoiding row-by-row movement. This satisfies the large-volume load constraint by reducing transaction logging and lock contention versus INSERT statements.

Visual reference

Go deeper

DP-203 Develop data processing Practice Question

Why each option matters

Option-by-option breakdown

Related to this question