Data Engineering Optimizing Query Performance 4

Question 1

Too many small files ("small files problem") hurts query performance mainly by:

Accepted Answer

Adding per-file open/metadata overhead

Answer

Each tiny file incurs listing and open costs that dominate when files are numerous.

Question 2

Compaction in a data lake table addresses performance by:

Accepted Answer

Merging many small files into fewer larger ones

Answer

Compaction consolidates small files to reduce scan overhead.

Question 3

Caching a hot dimension table in memory across a cluster helps most when:

Accepted Answer

It is reused across many queries and fits in memory

Answer

Caching pays off for small, frequently reused datasets that fit in memory.

Question 4

A query spilling to disk during a sort or aggregation usually indicates:

Accepted Answer

Insufficient memory for the operation

Answer

When working memory is exceeded, the engine spills intermediate data to disk, slowing the query.

Question 5

Choosing a partition column with very high cardinality (e.g., user_id) often backfires because it:

Accepted Answer

Creates a huge number of tiny partitions

Answer

Over-partitioning produces excessive small partitions and metadata overhead.

Data Engineering Practice Test