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?
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.