Databricks-DE-Pro Data Modelling Practice Question
A retail company uses a Databricks Lakehouse with a star schema in the Gold layer. Their fact_sales table has billions of rows and is partitioned by sale_date. Analysts frequently run queries that filter on product_id and join to dim_product. Currently, queries scanning the entire fact table are slow. To improve performance for these queries, which approach is most appropriate?
⚠ Common exam trap
The trap here is assuming that partitioning by a frequently filtered column is always beneficial, when high-cardinality columns should instead be optimized with Z-ORDER.
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
✓
Apply Z-ORDER BY product_id on the fact_sales table.
Z-ORDER BY product_id clusters data within each partition, allowing Delta Lake to skip files that do not contain the filtered product_id values. This reduces I/O and speeds up queries that filter or join on product_id. Partitioning by product_id is impractical due to high cardinality, and the other options do not provide the needed optimization.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Partition the fact_sales table by product_id.
Why it's wrong here
Partitioning by product_id would create a very large number of small partitions because product_id is high-cardinality. This leads to the small file problem, increased metadata overhead, and degraded performance. Partitioning is better suited for low-cardinality columns like sale_date.
- ✗
Convert the fact_sales table to a Parquet table and use partition pruning.
Why it's wrong here
Converting to Parquet would lose Delta Lake features such as ACID transactions and time travel. While Parquet supports partition pruning, the existing partitioning by sale_date already provides that. This change would not address filtering on product_id and could introduce data consistency issues.
- ✓
Apply Z-ORDER BY product_id on the fact_sales table.
Why this is correct
Z-ORDER BY product_id co-locates related product_id values within each partition, enabling data skipping when filtering on product_id. This reduces the amount of data scanned during joins and filters, directly improving query performance for the described workload. It is a common optimization for high-cardinality columns used in filters and joins.
- ✗
Add a bloom filter index on product_id.
Why it's wrong here
Databricks Delta Lake does not support user-defined bloom filter indexes. While bloom filters are used internally for data skipping on some columns, there is no direct SQL command to create them. Thus, this is not a valid optimization technique in Databricks.
About these practice questions
One of 267 original Databricks-DE-Pro 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-DE-Pro 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-DE-Pro exam.