Databricks-DA-Assoc Executing Queries with Databricks SQL Practice Question
A data analyst needs to query a Delta table named sales and retrieve only the rows where the sale_date is in the year 2023. The table is partitioned by sale_date. Which SQL statement will most efficiently return the required data?
⚠ Common exam trap
The trap here is using a function on the partition column, which looks intuitive but disables partition pruning and causes a full table scan.
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
✓
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01'
A range predicate directly on the partition column allows Databricks to prune partitions, reading only the data for 2023. Applying a function such as year() or cast() to the column disables pruning and forces a full scan. Therefore, the range condition is the most efficient.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SELECT * FROM sales WHERE cast(sale_date as string) LIKE '2023%'
Why it's wrong here
Applying a cast to the partition column prevents partition pruning because the column is transformed. The optimizer cannot use the partition metadata to skip partitions. This results in a full scan and is inefficient. Avoid functions on partition columns in WHERE clauses.
- ✗
SELECT * FROM sales WHERE year(sale_date) = 2023
Why it's wrong here
Using the year() function on the partition column prevents partition pruning because the function is applied to the column, so the optimizer cannot filter partitions directly. This forces a full table scan, which is inefficient. To enable pruning, the filter should be on the raw partition column without a function.
- ✗
SELECT * FROM sales WHERE sale_date LIKE '2023%'
Why it's wrong here
The LIKE operator with a wildcard may not be as efficient as a range predicate because it might not be recognized as a partition filter. While it could work if the column is a string, it is less explicit and may not prune partitions optimally. Range conditions are preferred for partition pruning.
- ✓
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01'
Why this is correct
This range predicate on the partition column allows the optimizer to perform partition pruning, scanning only the partitions for 2023. It avoids applying a function to the column, so the filter can be pushed down to the file level. This is the most efficient way to retrieve the desired rows.
About these practice questions
Courseiva writes every Databricks-DA-Assoc question from scratch — 291 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-DA-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-DA-Assoc exam.