Data Engineering Optimizing Query Performance 3 — Questions and Answers
Question 1: Data skew in a distributed join causes which symptom?
- A few tasks run far longer than the rest (Correct answer)
- All tasks finish at the exact same time
- The query returns wrong results
- Disk usage drops to zero
Correct answer: A few tasks run far longer than the rest
Skew concentrates rows on a few keys, overloading the tasks that process them.
Question 2: Which technique mitigates skew on a hot join key?
- Salting the key to spread rows across partitions (Correct answer)
- Removing the join entirely
- Increasing the result limit
- Disabling statistics
Correct answer: Salting the key to spread rows across partitions
Salting appends a random component to distribute a hot key over more partitions.
Question 3: A covering index improves a query because it:
- Contains all columns the query needs, avoiding table lookups (Correct answer)
- Stores the table twice for redundancy
- Forces a full scan to be faster
- Eliminates the need for a WHERE clause
Correct answer: Contains all columns the query needs, avoiding table lookups
When the index includes every referenced column, the engine answers from the index alone.
Question 4: SELECT * in an analytical query on columnar storage is discouraged mainly because it:
- Forces reading all columns, increasing I/O (Correct answer)
- Returns rows in random order
- Breaks the WHERE clause
- Disables partition pruning
Correct answer: Forces reading all columns, increasing I/O
Selecting every column negates columnar's ability to skip unused columns.
Question 5: Materializing an expensive, frequently-run aggregation into a summary table is best described as:
- A materialized view / precomputation tradeoff (Correct answer)
- Denormalization of foreign keys
- Index fragmentation
- Predicate pushdown
Correct answer: A materialized view / precomputation tradeoff
Precomputing results trades storage and refresh cost for faster reads.
Question 6: Which condition typically prevents an index from being used on a column?
- Wrapping the column in a function in the WHERE clause (Correct answer)
- Selecting fewer columns
- Using an equality predicate
- Having up-to-date statistics
Correct answer: Wrapping the column in a function in the WHERE clause
Functions applied to a column make the predicate non-sargable, blocking index use.
Question 7: When two large tables are joined, which strategy avoids broadcasting and instead shuffles both sides by key?
- Sort-merge / shuffle hash join (Correct answer)
- Broadcast join
- Nested loop join
- Index-only scan
Correct answer: Sort-merge / shuffle hash join
For two large inputs, both are repartitioned by the join key and merged or hashed.
Data Engineering Optimizing Query Performance 3
Data skew in a distributed join causes which symptom?