I85PREP
⌘ K
Databricks Data Engineer Professional study materials
Databricks Data Engineer Professional study resources

The nightly Z-ORDER job that costs 4 hours and still scans 290 GB

Databricks Data Engineer Professional · Put your knowledge to work in context.

All case studies

The nightly Z-ORDER job that costs 4 hours and still scans 290 GB

Your team runs a clickstream pipeline that lands vendor JSON into bronze and appends to silver.events, a Delta table of 8 TB that grows by about 40 GB per day. The silver write is a Structured Streaming job with a 30-second trigger, so roughly 2,900 new files arrive every day. A nightly job runs OPTIMIZE silver.events ZORDER BY (user_id) on a 32-node job cluster and takes just under 4 hours. Analysts filter on user_id plus a 3-day event_time window; their median query scans 290 GB even though 3 days of data is only about 120 GB. The table is partitioned by event_date. Predictive optimisation is not enabled on the table. The platform lead wants the nightly job cost cut and the analyst scans reduced without breaking consumers that read by partition column.

1. Explain why the Z-ORDER is not reducing analyst scan volume, and propose a layout that survives continuous appends.

Read the worked answer

Why it fails

  • OPTIMIZE ... ZORDER is a point-in-time rewrite. It clusters only the files present when it runs. The stream then appends ~2,900 unclustered files before the next run, and the analyst query must scan all of them.
  • Partitioning by event_date only prunes by date. Inside the 3-day window every file is still read, and the small-file count inflates per-file overhead, which is why a 120 GB footprint costs 290 GB of scanned bytes.
  • The nightly job rewrites the entire 8 TB to fix the ~40 GB that actually changed, so the 4-hour runtime is almost entirely wasted work.

Proposed layout

  1. Turn on delta.autoOptimize.optimizeWrite and delta.autoOptimize.autoCompact on the target so each micro-batch writes fewer, larger files.
  2. Convert to liquid clustering: ALTER TABLE silver.events CLUSTER BY (event_date, user_id), then run OPTIMIZE silver.events FULL once to rewrite existing data. Subsequent OPTIMIZE runs only rewrite files that do not match the clustering keys, so nightly cost scales with new data rather than total table size.
  3. Enable predictive optimisation if the table is UC-managed and you do not need precise control of when compaction runs.

Caveats to check with consumers Liquid clustering replaces partitioning. WHERE event_date = ... still prunes well, but DROP PARTITION, Hive-style path conventions and partition listing break. Cluster keys can be changed later without rewriting old data, but only new writes use the new keys until you run OPTIMIZE ... FULL again.

2. The streaming trigger is 30 seconds. Should you change it, and how would you sequence and validate the whole change?

Read the worked answer

Trigger interval The 30-second trigger is not the root cause, but it multiplies file count. Only change it if the latency SLA allows: if consumers tolerate 5 minutes, a longer trigger plus optimizeWrite cuts file growth by roughly an order of magnitude. If the SLA really is sub-minute, keep the trigger and rely on autoCompact plus a frequent compaction job scoped to recent data instead of a nightly full-table rewrite.

Sequencing

  1. SHALLOW CLONE the table to a scratch schema and validate the new layout there with the two or three most expensive analyst queries.
  2. Record a baseline from DESCRIBE DETAIL (numFiles, sizeInBytes) and from the query profile (files pruned, bytes scanned).
  3. Change the writer settings first (cheap, no rewrite), observe file sizes for a few days, then run OPTIMIZE ... FULL in a low-traffic window with a cap on the cluster size.
  4. Re-measure the same queries. Success looks like scanned bytes approaching the logical footprint and nightly compaction time scaling with daily ingest, not table size.

Rollback Keep the original table until the clone is validated; rewriting layout is a metadata change that cannot be undone by a simple revert, so a clone you can point consumers back to is the safe fallback.

3. A colleague suggests simply running the Z-ORDER job twice a day. Argue for or against with the numbers in the scenario.

Read the worked answer

Against. Doubling the schedule doubles a job that already spends 4 hours on 32 nodes, so you roughly double the dominant cost line while gaining only a few hours of freshness for the clustered portion of the table. The analyst queries still hit files written in the gap, and every run still rewrites the 8 TB baseline.

If the only goal is to keep user_id clustering tight for the recently written data, the cheaper version is an incremental OPTIMIZE limited to the last day's partitions (or, after switching to liquid clustering, a plain OPTIMIZE), which touches tens of GB rather than 8 TB. That gets most of the pruning benefit at a fraction of the DBU spend, and combined with autoCompact it may not need to run more than once or twice a day anyway.