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

Corrupting the data

Answer

Removing partition keys

Answer

Forcing row-based storage

Question 2

Compaction in a data lake table addresses performance by:

Accepted Answer

Merging many small files into fewer larger ones

Answer

Deleting all old partitions

Answer

Converting Parquet to CSV

Answer

Dropping table statistics

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

It changes on every query

Answer

It is larger than total cluster RAM

Answer

It is queried only once

Question 4

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

Accepted Answer

Insufficient memory for the operation

Answer

A missing primary key

Answer

Wrong column order in SELECT

Answer

An expired index

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

Prevents any filtering

Answer

Forces full scans always

Answer

Disables compression

Question 6

LIMIT with an ORDER BY on a non-indexed column still requires the engine to:

Accepted Answer

Sort the full result set before returning rows

Answer

Skip the sort entirely

Answer

Use a covering index automatically

Answer

Return rows in insertion order

Question 7

Bucketing a table by the join key primarily improves performance by:

Accepted Answer

Avoiding a shuffle since matching keys are co-located

Answer

Compressing the table further

Answer

Removing the need for statistics

Answer

Eliminating all joins