An analyst query against a partitioned Delta fact table filters WHERE transaction_date = '2025-10-01' AND store_id = 'NYC-042'. The table is partitioned by transaction_date. The query consistently takes 45+ minutes and the Spark UI shows 800 of 1,000 total data files being scanned. The expected behavior is that partitioning on transaction_date should limit the scan to approximately 50 files for that date. Which root causes, diagnosable from the Spark UI and query plan, best explain this behavior — and what is the correct remediation?
Answer & explanation
Correct answer: B
WHY B: Two legitimate, diagnosable root causes explain why partition pruning fails despite a partition filter, visible in the query plan. (1) Data type mismatch: If transaction_date is a DATE column but the filter literal '2025-10-01' is interpreted as a STRING, Spark's type coercion may prevent the optimizer from safely doing partition elimination — the filter appears as a post-scan predicate over all files rather than a partition pruning step. This is a common and subtle production issue; the query plan in the Spark UI would show the full scan with the filter applied after reading, not before. The fix is to ensure the literal type matches the column type (e.g., using DATE '2025-10-01' or a properly typed parameter). (2) For external tables with Hive-style partitioning, manually added partition directories that are not registered in the metastore cause the planner to be unaware of those partitions and scan all files. Running MSCK REPAIR TABLE (Hive) or ALTER TABLE RECOVER PARTITIONS registers the missing partitions. Delta Lake managed tables maintain their own partition metadata via the transaction log and do not need MSCK REPAIR, but external Hive-partitioned tables do. WHY NOT A: If partition pruning were functioning correctly, the scan would be ~50 files. Scanning 800 files confirms partition pruning is NOT occurring — ZORDER would help with store_id filtering but cannot fix the broken partition pruning root cause. WHY NOT C: CartesianProduct is a JVM-level join operation between two unrelated DataFrames or subqueries. A simple AND predicate between two filter conditions on the SAME table never produces a CartesianProduct — this reveals a fundamental misunderstanding of query plans. WHY NOT D: AQE optimizes shuffle partition count and join strategies at runtime, but it does NOT affect partition elimination on Delta tables. Partition elimination is a static optimization done during query planning (before execution), completely independent of AQE. WHY NOT E: Delta's OPTIMIZE command compacts data files, not the transaction log. Delta log checkpointing is a separate automatic process. An oversized transaction log would slow query planning latency, not cause a fallback to full table scans.