Home / DE Professional practice test / Data Modelling / Question

Databricks Data Engineer Professional · free practice question

A data engineer is designing a star schema for a large analytical workload in Databricks. The central fact table `fact_orders` (500 GB, growing ~5 GB/day) is…

Exam-style question on Data Modelling from the free DE Professional practice test, with the answer and a full explanation below.

Updated November 16, 2026

Data Modelling Medium

A data engineer is designing a star schema for a large analytical workload in Databricks. The central fact table fact_orders (500 GB, growing ~5 GB/day) is joined frequently with dim_product (10 MB) and dim_customer (200 MB) on product_id and customer_id respectively. Analysts filter primarily by order_date and product_category. Which combination of design decisions BEST optimizes query performance for this star schema?

  1. APartition fact_orders by order_date, add CLUSTER BY (product_category) on the same table, and broadcast both dimensions with a broadcast() hint in each query
  2. BPartition fact_orders by product_id and customer_id so joins with both dimensions prune partitions, and keep the dimension joins as shuffle sort-merge joins
  3. CPartition fact_orders by product_id, run OPTIMIZE ... ZORDER BY (order_date, product_category) on it, and broadcast both dimensions with a broadcast() hint
  4. DUse CLUSTER BY (order_date, product_category) on fact_orders and broadcast both dimensions, raising spark.sql.autoBroadcastJoinThreshold or hinting for dim_customer
Answer & explanation

Correct answer: D

WHY D: Liquid clustering on fact_orders with CLUSTER BY (order_date, product_category) physically co-locates records sharing the same date and product category, enabling data skipping when analysts filter by these columns. Liquid clustering is the Databricks-recommended layout for large fact tables with growing data and analytical query patterns. dim_product (10 MB) falls at the default spark.sql.autoBroadcastJoinThreshold (10 MB) and is broadcast automatically, while dim_customer (200 MB) is broadcast after raising the threshold or with an explicit broadcast() hint, a standard star-schema tuning that eliminates shuffle-based joins; the small dimension tables need no additional layout optimization.

WHY NOT A: Liquid clustering and Hive-style partitioning cannot be combined on the same Delta table. The documentation states: 'Clustering is not compatible with partitioning or ZORDER.' Liquid clustering can be enabled only on unpartitioned tables (ALTER TABLE ... CLUSTER BY requires the table to be unpartitioned), so attempting both results in an error.

WHY NOT B: Both product_id and customer_id are high cardinality (potentially millions of distinct values), so partitioning on them creates millions of tiny partitions, the scenario Databricks documentation warns against: 'Tables where a typical partition key could leave the table with too many or too few partitions' are not suited to partitioning. Joins on dimension foreign keys are served by broadcast joins of the small dimension tables, not by partition pruning on the fact table, and leaving them as shuffle sort-merge joins wastes that advantage.

WHY NOT C: Partitioning by high-cardinality product_id creates too many partitions for the same reason. ZORDER can handle high-cardinality columns, but Databricks now recommends liquid clustering over ZORDER for new tables, and ZORDER operates within each partition's boundary: on a table over-partitioned by product_id, ZORDER on order_date cannot combine files across product partitions.

Take the full DE Professional practice test free →

Would you pass today?

This question is one of 60 in the free, timed Databricks Data Engineer Professional practice test. Take it and get a section-by-section score.

Start the free test →