Data Engineering Optimizing Query Performance 3

Question 1

Data skew in a distributed join causes which symptom?

Accepted Answer

A few tasks run far longer than the rest

Answer

All tasks finish at the exact same time

Answer

The query returns wrong results

Answer

Disk usage drops to zero

Question 2

Which technique mitigates skew on a hot join key?

Accepted Answer

Salting the key to spread rows across partitions

Answer

Removing the join entirely

Answer

Increasing the result limit

Answer

Disabling statistics

Question 3

A covering index improves a query because it:

Accepted Answer

Contains all columns the query needs, avoiding table lookups

Answer

Stores the table twice for redundancy

Answer

Forces a full scan to be faster

Answer

Eliminates the need for a WHERE clause

Question 4

SELECT * in an analytical query on columnar storage is discouraged mainly because it:

Accepted Answer

Forces reading all columns, increasing I/O

Answer

Returns rows in random order

Answer

Breaks the WHERE clause

Answer

Disables partition pruning

Question 5

Materializing an expensive, frequently-run aggregation into a summary table is best described as:

Accepted Answer

A materialized view / precomputation tradeoff

Answer

Denormalization of foreign keys

Answer

Index fragmentation

Answer

Predicate pushdown

Question 6

Which condition typically prevents an index from being used on a column?

Accepted Answer

Wrapping the column in a function in the WHERE clause

Answer

Selecting fewer columns

Answer

Using an equality predicate

Answer

Having up-to-date statistics

Question 7

When two large tables are joined, which strategy avoids broadcasting and instead shuffles both sides by key?

Accepted Answer

Sort-merge / shuffle hash join

Answer

Broadcast join

Answer

Nested loop join

Answer

Index-only scan