Describe an analytics workload on Azure →hardMultiple ChoiceObjective-mapped

DP-900 Describe an analytics workload on Azure Practice Question

A company uses Azure Synapse Analytics to run complex queries against large datasets stored in Parquet files in Azure Data Lake Storage Gen2. They notice that queries scanning entire partitions are slow due to high I/O overhead on the compute nodes. Investigation shows each daily partition contains thousands of small files (under 1 MB each). Which optimization should be implemented first to improve query performance?

⚠ Common exam trap

It's easy for candidates to confuse scaling out compute nodes (Option A) with solving a data layout problem, or mistakenly think columnstore indexes (Option B) apply to external tables, when in fact the issue is purely about file size and count in the storage layer.

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

✓

Compact small files into larger ones before querying

The high I/O overhead is caused by the thousands of small files per partition. When Synapse compute nodes read many small files, the overhead of opening, reading metadata, and closing each file dominates, even though the total data volume is small. Compacting these small files into fewer, larger files (e.g., 128 MB or more) reduces the number of file operations, improves read throughput, and allows more efficient predicate pushdown and parallelism.

Answer analysis

Option-by-option breakdown

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

✗
Increase the number of compute nodes
Why it's wrong here
Adding compute nodes scales out CPU and memory and raises the degree of parallelism, but every small file still incurs a fixed open-and-read metadata cost per distributed task. More nodes merely create more parallel tasks hitting the same tiny files, so per-file overhead and scheduling remains a bottleneck. The root cause is file size distribution, not insufficient parallelism, so this change yields only marginal gains.
✗
Use columnstore indexes on external tables
Why it's wrong here
Columnstore indexes are a relational table optimization in a SQL pool and cannot be created on external tables, which point to files rather than managed storage. External tables in Azure Synapse read files directly, and if those files are Parquet they are already columnar, so an index would add no benefit and is unsupported. Thus this option fails because it targets an in-database structure that does not apply to external file sources.
✓
Compact small files into larger ones before querying
Why this is correct
Compacting many small files into fewer large files (e.g., roughly 256 MB each) directly reduces the number of file open operations, metadata lookups, and read requests that distributed workers must perform. With larger contiguous files, the query engine can scan data more efficiently, use better I/O parallelism, and push predicates more effectively. This addresses the actual cause of poor performance: excessive per-file overhead dominating the scan across many tiny inputs.
✗
Change the partition column to a different date granularity
Why it's wrong here
Changing the partition column to a different date granularity merely reorganizes which folder each file belongs to; it does not merge or consolidate the underlying small files. For example, switching from daily to hourly partitions creates even more partitions and can fragment files further, worsening the small-file problem. Partitioning is a data layout choice for pruning, not a mechanism for controlling file size, so this option leaves the core inefficiency untouched.

Go deeper

DP-900 Describe an analytics workload on Azure Practice Question

Why each option matters

Option-by-option breakdown

Related to this question