Databricks-Spark-Assoc Using Spark SQL Practice Question
You are using Spark SQL to analyze a Delta table named transactions that is partitioned by a column region. You need to run a query that filters on region and also on a non-partitioned column amount. The table has statistics collected on region and amount. Which of the following best describes how Spark SQL will optimize the query?
⚠ Common exam trap
The trap here is underestimating Delta Lake's data skipping capabilities on non-partition columns or confusing it with dynamic optimizations like AQE.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Spark SQL will use partition pruning based on the region filter and then apply data skipping on amount using the collected statistics to skip files where amount does not match the filter.
Spark SQL uses partition pruning for the region filter and Delta Lake's data skipping for the amount filter, thanks to collected statistics. This minimizes the amount of data read. The other options incorrectly assume limitations or alternative optimizations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Spark SQL will perform a full table scan because the filter on amount is not on a partition column, and then apply the filter after reading all data.
Why it's wrong here
This ignores Delta Lake's data skipping feature. Even though amount is not a partition column, Delta Lake stores min/max statistics for each file. Spark can use these statistics to skip files that cannot contain matching rows. Therefore, a full table scan is not necessary if statistics are available and the filter is selective.
- ✓
Spark SQL will use partition pruning based on the region filter and then apply data skipping on amount using the collected statistics to skip files where amount does not match the filter.
Why this is correct
Spark SQL leverages partition pruning to eliminate entire partitions based on the region filter. Additionally, Delta Lake collects statistics on non-partitioned columns like amount, enabling data skipping at the file level. When statistics indicate that a file's min/max range for amount does not overlap with the filter, that file is skipped. This combination drastically reduces I/O.
- ✗
Spark SQL will dynamically repartition the data based on the amount filter to optimize the query, using adaptive query execution.
Why it's wrong here
Adaptive Query Execution (AQE) can optimize shuffles and joins, but it does not dynamically repartition based on filter predicates to skip data. Data skipping is a static optimization based on file statistics. AQE might coalesce partitions after a shuffle, but it does not replace partition pruning or data skipping.
- ✗
Spark SQL will only use partition pruning on region and will not use any statistics for amount because statistics are only collected on partition columns.
Why it's wrong here
Delta Lake collects statistics on all columns by default, not just partition columns. The statistics include min/max values, null counts, and total counts. These are stored in the Delta transaction log and used for data skipping. So statistics on amount are available and can be used.
About these practice questions
Courseiva writes every Databricks-Spark-Assoc question from scratch — 295 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-Spark-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-Spark-Assoc exam.