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

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?

Accepted Answer

Broadcast (map-side) join

Answer

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?

Accepted Answer

Rows read and transferred upward

Answer

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:

Accepted Answer

Reading only the columns a query needs

Answer

Columnar storage lets engines skip unreferenced columns, cutting I/O.

Question 5

Stale table statistics most directly cause which problem?

Accepted Answer

Poor planner cardinality estimates and bad plans

Answer

The optimizer relies on statistics to estimate row counts and choose plans.

Data Engineering Practice Test