Data Engineering Optimizing Query Performance 2

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?

Accepted Answer

The index is on a different column than the predicate

Answer

The column has too few distinct values

Answer

Indexes never help high-cardinality columns

Answer

The table is too small for the index

Question 2

Which join strategy is typically fastest when joining a very large table to a very small lookup table?

Accepted Answer

Broadcast (map-side) join

Answer

Sort-merge join

Answer

Nested loop on the large table

Answer

Cross join then filter

Question 3

What does pushing a WHERE filter down to the storage/scan layer (predicate pushdown) primarily reduce?

Accepted Answer

Rows read and transferred upward

Answer

Number of CPU cores used

Answer

The size of the result schema

Answer

Network port usage

Question 4

A columnar format like Parquet improves analytical query speed mainly because it allows:

Accepted Answer

Reading only the columns a query needs

Answer

Storing more rows per file

Answer

Faster row-by-row inserts

Answer

Avoiding any compression

Question 5

Stale table statistics most directly cause which problem?

Accepted Answer

Poor planner cardinality estimates and bad plans

Answer

Corrupted data on disk

Answer

Loss of foreign keys

Answer

Slower disk writes

Question 6

Which is the best first step when a query is suddenly slow in production?

Accepted Answer

Examine the query execution plan

Answer

Add more RAM to the server

Answer

Rewrite the application code

Answer

Drop all indexes and rebuild

Question 7

Partition pruning speeds up queries by:

Accepted Answer

Skipping partitions that cannot match the filter

Answer

Compressing every partition

Answer

Merging all partitions into one file

Answer

Caching the entire table in memory