Data Engineering Optimizing Query Performance 2 — Questions and Answers
Question 1: A query filtering on a high-cardinality column does a full table scan despite an index existing. What is the MOST likely cause?
- The index is on a different column than the predicate (Correct answer)
- The column has too few distinct values
- Indexes never help high-cardinality columns
- The table is too small for the index
Correct answer: The index is on a different column than the predicate
If the index does not cover the filtered column, the planner cannot use it and falls back to a scan.
Question 2: Which join strategy is typically fastest when joining a very large table to a very small lookup table?
- Broadcast (map-side) join (Correct answer)
- Sort-merge join
- Nested loop on the large table
- Cross join then filter
Correct answer: Broadcast (map-side) join
Broadcasting the small table to every node avoids shuffling the large table.
Question 3: What does pushing a WHERE filter down to the storage/scan layer (predicate pushdown) primarily reduce?
- Rows read and transferred upward (Correct answer)
- Number of CPU cores used
- The size of the result schema
- Network port usage
Correct answer: Rows read and transferred upward
Predicate pushdown filters early so fewer rows move through the pipeline.
Question 4: A columnar format like Parquet improves analytical query speed mainly because it allows:
- Reading only the columns a query needs (Correct answer)
- Storing more rows per file
- Faster row-by-row inserts
- Avoiding any compression
Correct answer: Reading only the columns a query needs
Columnar storage lets engines skip unreferenced columns, cutting I/O.
Question 5: Stale table statistics most directly cause which problem?
- Poor planner cardinality estimates and bad plans (Correct answer)
- Corrupted data on disk
- Loss of foreign keys
- Slower disk writes
Correct answer: Poor planner cardinality estimates and bad plans
The optimizer relies on statistics to estimate row counts and choose plans.
Question 6: Which is the best first step when a query is suddenly slow in production?
- Examine the query execution plan (Correct answer)
- Add more RAM to the server
- Rewrite the application code
- Drop all indexes and rebuild
Correct answer: Examine the query execution plan
The execution plan reveals scans, join methods, and bottlenecks before any change.
Question 7: Partition pruning speeds up queries by:
- Skipping partitions that cannot match the filter (Correct answer)
- Compressing every partition
- Merging all partitions into one file
- Caching the entire table in memory
Correct answer: Skipping partitions that cannot match the filter
When a query filters on the partition key, the engine reads only relevant partitions.
Data Engineering Optimizing Query Performance 2
A query filtering on a high-cardinality column does a full table scan despite an index existing.
What is the MOST likely cause?