Databricks-DA-Assoc Data Modeling with Databricks SQL Practice Question
A data analyst is modeling a Delta table in Databricks SQL that stores product inventory snapshots. The table has columns snapshot_date (DATE), product_id (STRING), warehouse_id (STRING), and quantity_on_hand (INT). Queries frequently filter on snapshot_date and then join to a product dimension. The analyst wants to minimize the amount of data scanned for date-filtered queries while keeping the table simple and avoiding manual file management. Which approach best achieves this in Databricks SQL?
⚠ Common exam trap
The trap here is assuming that any performance feature, such as a Bloom filter or Z-ORDER, will accelerate all filters, when in fact each optimizes specific access patterns and must match the columns used in the WHERE clause.
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
✓
Partition the Delta table by snapshot_date using PARTITIONED BY (snapshot_date) in the CREATE TABLE statement.
Partitioning by snapshot_date aligns the physical layout with the dominant filter, enabling file pruning and lower data scanned. Delta Lake manages partitions automatically, so the analyst avoids manual file management. The other choices either target the wrong column, require ongoing maintenance, or change query results, so they do not satisfy the scenario's goals.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Convert the table to a view that filters snapshot_date to the last 30 days.
Why it's wrong here
A view that restricts snapshot_date to the last 30 days would change the data returned and break historical analysis beyond that window. It also does not reduce scanned data for arbitrary date filters because the view still reads underlying files. This approach alters semantics rather than optimizing storage layout, and it fails the requirement to keep the table simple and complete.
- ✗
Use Z-ORDER BY on product_id and warehouse_id when writing the table.
Why it's wrong here
Z-ORDER clustering improves data skipping for the columns specified, but the scenario's main filter is on snapshot_date. Clustering on product_id and warehouse_id would not efficiently prune files for date-range queries, so scanned data for date filters would remain high. Z-ORDER also requires periodic OPTIMIZE runs, adding maintenance that the analyst wanted to avoid.
- ✗
Create a Bloom filter index on product_id to speed up date-range queries.
Why it's wrong here
Bloom filter indexes help with equality lookups on high-cardinality columns like product_id, but they do not provide file pruning for date-range predicates. Date-filtered queries would still scan all files because snapshot_date is not part of the index. This approach does not address the primary access pattern and adds unnecessary complexity without reducing scanned data for the date filter.
- ✓
Partition the Delta table by snapshot_date using PARTITIONED BY (snapshot_date) in the CREATE TABLE statement.
Why this is correct
Partitioning by snapshot_date lets Databricks SQL prune files for date-filtered queries, reducing data scanned. Because Delta Lake manages partition metadata automatically, the analyst avoids manual file management. This directly matches the scenario's need to minimize scanned data for date predicates while keeping the table simple, and it works with subsequent joins to a product dimension.
About these practice questions
One of 291 original Databricks-DA-Assoc practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.