Optimizing a Delta Table with Skew and Z-Order
A Delta table FactOrders has 8 TB and is partitioned by OrderDate (daily, 3 years). Queries filter by CustomerId and OrderDate. One customer, CustomerId=999, accounts for 30% of rows. A MERGE job updates 5% of rows nightly and takes 6 hours. The table has 900,000 files. OPTIMIZE runs weekly but takes 4 hours. The team wants the nightly job under 2 hours and faster ad-hoc queries.
1. Why is daily partitioning and the current layout hurting performance?
Read the worked answer
Daily partitioning creates ~1,095 partitions, many small, and queries that filter only by CustomerId must scan all partitions. The 900,000 files mean excessive metadata and small-file overhead. Skew on CustomerId=999 makes joins and MERGE shuffle one task dominate. OPTIMIZE weekly is too infrequent to control file count. Recommend partitioning by month instead of day, and using ZORDER BY (CustomerId) to co-locate the hot customer. Consider liquid clustering if available. Compact more frequently with optimizeWrite and autoCompact.
2. How would you handle the skew from CustomerId=999 in the MERGE?
Read the worked answer
Detect skew in Spark UI (one task with 30% of shuffle). Options: salt the key for the skewed customer by appending a random suffix and explode the matching dimension rows; or split the MERGE into two jobs - one for the hot customer using broadcast join and targeted file rewrite, one for the rest. Also consider skewJoin hints or AQE skew join handling. Validate row counts after merge to ensure no duplicates from salting.
3. What change to the OPTIMIZE schedule and settings would you recommend?
Read the worked answer
Run OPTIMIZE more frequently on affected partitions (e.g., nightly on the last 7 days) rather than the whole table weekly. Set delta.targetFileSize to 128–256 MB and enable delta.autoOptimize.optimizeWrite and autoCompact. Use ZORDER BY (CustomerId) on hot partitions. Monitor file count and optimize duration; if OPTIMIZE still takes 4 hours, restrict to changed partitions using WHERE OrderDate >= current_date() - 7.