Databricks-DE-Pro Data Modelling Practice Question
A data engineer is designing a Gold layer table for a retail company. The table must support efficient queries that filter on product_category (low cardinality) and sort by transaction_timestamp (high cardinality). The table is expected to grow to petabytes. Which Delta Lake table design should the engineer choose to optimize both filtering and sorting?
⚠ Common exam trap
The trap here is partitioning by a high-cardinality column like transaction_timestamp, which leads to many small partitions and poor performance.
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 by product_category and Z-ORDER BY transaction_timestamp.
Partitioning by the low-cardinality product_category enables efficient partition pruning for filters. Z-ORDER BY the high-cardinality transaction_timestamp clusters data within partitions, speeding up sorting and range queries on that column. This combined approach leverages both partitioning and Z-ORDER appropriately, avoiding the pitfalls of partitioning on high-cardinality columns.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Z-ORDER BY both product_category and transaction_timestamp.
Why it's wrong here
Z-ORDER on both columns can help with filtering on both, but it does not provide the same partition pruning benefits as partitioning on product_category. Also, Z-ORDER on a high-cardinality column like transaction_timestamp may not be as effective for sorting because Z-ORDER does not guarantee a total order. Partitioning is still needed for efficient filtering.
- ✗
Partition by product_category only, without Z-ORDER.
Why it's wrong here
Partitioning by product_category alone accelerates filters on that column but does nothing to optimize sorting or range queries on transaction_timestamp. Queries that sort by transaction_timestamp would still require scanning and sorting all data within each partition, which is inefficient at petabyte scale.
- ✓
Partition by product_category and Z-ORDER BY transaction_timestamp.
Why this is correct
Partitioning by product_category, which has low cardinality, avoids the small file problem and enables partition pruning for filters on that column. Z-ORDER BY transaction_timestamp clusters data within each partition to accelerate sorting and range queries on that column. This combination optimally supports both filtering and sorting.
- ✗
Partition by transaction_timestamp and Z-ORDER BY product_category.
Why it's wrong here
Partitioning by transaction_timestamp, a high-cardinality column, would create an excessive number of partitions, leading to metadata overhead and small files. Z-ORDER BY product_category would not efficiently support sorting on transaction_timestamp because Z-ORDER does not guarantee global sort order. This design is suboptimal.
About these practice questions
This Databricks-DE-Pro question is part of Courseiva's 267-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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-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.