Databricks-DA-Assoc Data Modeling with Databricks SQL Practice Question
Exhibit
CREATE TABLE sales_data (id INT, amount DOUBLE, event_date DATE) USING DELTA PARTITIONED BY (event_date);
Refer to the exhibit. The 'sales_data' table is growing rapidly. You notice queries filtering by 'event_date' are fast, but queries filtering by 'id' are slow. What is the most effective data modeling change to optimize for 'id' lookups?
⚠ Common exam trap
Candidates often suggest partitioning by high-cardinality columns like 'id', which leads to the 'small file problem' and degrades performance rather than improving it.
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
✓
Use Z-Ordering or Liquid Clustering on the 'id' column.
The table is currently partitioned by 'event_date', which creates a folder structure that only aids queries filtering on dates. Partitioning by a high-cardinality column like 'id' is a major anti-pattern as it leads to the 'small file problem'. Instead, the analyst should retain the partitioning on 'event_date' but apply Z-Ordering or Liquid Clustering on the 'id' column to facilitate efficient data skipping without creating excessive directory partitions.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Re-partition the table by 'id' instead of 'event_date'.
Why it's wrong here
Partitioning by a high-cardinality column like 'id' creates millions of small files, severely impacting metadata performance and storage efficiency. This approach would lead to increased listing times and poor query performance, effectively negating the benefits of Delta Lake’s metadata management and optimization features provided by the platform.
- ✓
Use Z-Ordering or Liquid Clustering on the 'id' column.
Why this is correct
Z-Ordering or Liquid Clustering allows the engine to organize data within the existing partitions to facilitate efficient skipping on the 'id' column. This keeps the physical folder structure manageable based on 'event_date' while providing high-performance access paths for queries filtering by specific 'id' values without creating excess files.
- ✗
Create a secondary table for 'id' lookups that is partitioned by 'id'.
Why it's wrong here
Creating a secondary table introduces data redundancy and requires complex synchronization logic to ensure both tables remain consistent. This creates unnecessary overhead and risk of data drift, failing to leverage the native indexing and clustering capabilities built into Databricks SQL for high-performance retrieval on large tables.
- ✗
Change the table type to a standard Parquet table without partitioning.
Why it's wrong here
Standard Parquet tables lack the transaction log and metadata management features of Delta Lake, making it impossible to perform reliable updates or schema evolution. Furthermore, removing partitioning without a secondary clustering strategy will force full table scans for every query, significantly reducing performance for any specific record retrieval.
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.