Develop data processing →mediumMultiple Choice

DP-203 Develop data processing Practice Question

You are implementing a data processing solution in Azure Synapse Analytics using Spark pools. The solution reads Parquet files from Azure Data Lake Storage Gen2, performs transformations, and writes the results to a dedicated SQL pool. You need to optimize the write performance to the dedicated SQL pool. Which technique should you use?

⚠ Common exam trap

The trap here is assuming that increasing JDBC batch size is sufficient for performance, when actually PolyBase's parallel staging is far more efficient for large volumes.

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 PolyBase connector with a staging location in Azure Blob Storage.

The PolyBase connector is the most efficient way to write large datasets from Azure Synapse Spark pools to a dedicated SQL pool. It stages the data in Azure Blob Storage or Data Lake Storage Gen2 and then uses PolyBase to load it in parallel, which is much faster than JDBC batch inserts. This approach minimizes the load on the SQL pool and leverages its bulk load capabilities.

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 PolyBase connector with a staging location in Azure Blob Storage.
Why this is correct
The PolyBase connector in Azure Synapse Spark pools writes data to a staging area in Azure Blob Storage or Data Lake Storage Gen2, then uses PolyBase to load it into the dedicated SQL pool. This is the recommended approach for large data loads because it leverages the parallel bulk load capabilities of PolyBase, significantly improving write performance compared to row-by-row inserts.
✗
Use the 'spark.sql.sources.partitionOverwriteMode' setting to overwrite partitions.
Why it's wrong here
This setting controls how Spark handles partition overwrites when writing to file-based sources, not to a dedicated SQL pool. It is irrelevant for writing to a SQL pool because the SQL pool does not use file partitioning in the same way. This option is a distractor related to file writes, not database writes.
✗
Use the JDBC connector with batch inserts and set the batch size to 10,000 rows.
Why it's wrong here
The JDBC connector can write to a dedicated SQL pool, but it performs individual inserts or batch inserts, which are slower than PolyBase for large volumes. Even with a batch size of 10,000, the write performance is limited by the SQL pool's transaction log and network overhead. This method is suitable for small datasets but not for optimizing large writes.
✗
Write the data to a Parquet file in Data Lake Storage Gen2 and then use a Synapse pipeline to load it.
Why it's wrong here
Writing to Parquet and then using a Synapse pipeline is a valid approach, but it adds an extra step and does not directly optimize the write from Spark to the dedicated SQL pool. The pipeline would still use PolyBase or COPY, but the question asks for the technique to use within the Spark job to optimize write performance. This option defers the write to another service.

Quick reference

Azure Blob Storage Tier Comparison

Tier	Storage Cost	Retrieval Cost	Latency	Use Case
Hot	Highest	Lowest	Immediate	Active data, frequent reads
Cool	Lower	Higher	Immediate	Data accessed < once / month
Cold	Lower still	Higher	Immediate	Data accessed < once / quarter
Archive	Lowest	Highest + rehydration delay	Hours	Long-term compliance retention

Go deeper

DP-203 Develop data processing Practice Question

Why each option matters

Option-by-option breakdown

Azure Blob Storage Tier Comparison

Related to this question