Home / DE Professional practice test / Cost & Performance Optimisation / Question

Databricks Data Engineer Professional · free practice question

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…

Exam-style question on Cost & Performance Optimisation from the free DE Professional practice test, with the answer and a full explanation below.

Updated November 16, 2026

Cost & Performance Optimisation

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?

  1. AThe 800-file scan indicates data skipping is working but only achieves partial pruning. This is expected behavior — partitioning on a date column is not sufficient for column-level skipping. Solution: run OPTIMIZE ... ZORDER BY (transaction_date, store_id) to further prune files.
  2. BThe 800-file scan despite a partition predicate on transaction_date indicates one of two root causes: (1) A data type mismatch between the filter literal and the partition column type (e.g., passing a STRING '2025-10-01' to a DATE-typed partition) prevents the optimizer from safely performing partition elimination. (2) For external tables, manually added partition directories not registered in the metastore would cause all files to be scanned; running MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS) registers missing partitions. For store_id, without layout optimization, data skipping is limited by per-file min-max statistics only.
  3. CThe 800-file scan is caused by the query plan generating a CartesianProduct between the transaction_date filter and the store_id filter. In the query profile, look for a CartesianProduct node and replace the AND predicate with a semi-join against a single-row DataFrame containing the target values.
  4. DThe 800-file scan indicates Adaptive Query Execution (AQE) has dynamically changed the number of shuffle partitions, causing additional partition data to be read. Disable AQE with spark.sql.adaptive.enabled=false to restore expected partition pruning behavior.
  5. EThe 800-file scan indicates the table's Delta transaction log has grown too large for the driver to parse within its timeout, causing the query planner to fall back to a full scan. Run OPTIMIZE on the table to compact transaction log files and reduce Delta log parse time.
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.

Take the full DE Professional practice test free →

Would you pass today?

This question is one of 60 in the free, timed Databricks Data Engineer Professional practice test. Take it and get a section-by-section score.

Start the free test →