Data Engineering Optimizing Query Performance 5

Question 1

A correlated subquery executed once per outer row is best optimized by:

Accepted Answer

Rewriting it as a join or window function

Answer

Converting row-by-row correlated subqueries into set-based joins avoids repeated execution.

Question 2

EXPLAIN ANALYZE differs from plain EXPLAIN because it:

Accepted Answer

Actually runs the query and reports real timings

Answer

EXPLAIN ANALYZE executes the query and shows actual versus estimated rows and time.

Question 3

When estimated rows differ wildly from actual rows in a plan, the fix is usually to:

Accepted Answer

Update/refresh table statistics

Answer

Large estimate-vs-actual gaps point to stale or missing statistics misleading the planner.

Question 4

Z-ordering / data clustering on commonly filtered columns helps by:

Accepted Answer

Co-locating related values so more files can be skipped

Answer

Clustering sorts related values together, improving data-skipping via file-level min/max stats.

Question 5

A query joining on columns of mismatched data types (e.g., string vs int) often slows down because:

Accepted Answer

Implicit casting prevents index use

Answer

Type mismatches force implicit conversions that make predicates non-sargable.

Data Engineering Practice Test