Databricks Data Engineer: Partitioning Plus Z-ORDER on a 6 TB Clickstream Table

Partitioning Plus Z-ORDER on a 6 TB Clickstream Table

A 6 TB Delta table silver.events holds four years of clickstream data. Analysts query it mostly for the trailing 90 days, filtering on event_date and customer_id (about 30 million distinct values); a smaller set of dashboards scans all of history. The table is partitioned by event_date and rewritten weekly with ZORDER BY (customer_id). That rewrite takes 50 minutes and touches nearly every file, recent partitions hold more than 12,000 small files, and a 90-day lookup for one customer still scans roughly 200 GB. Writes to the table must stay under 15 minutes to keep the hourly upstream job on schedule. The account is on a runtime that supports liquid clustering and predictive optimization.

1. Explain why the current layout underperforms and recommend a replacement layout.

Read the worked answer

The two mechanisms are fighting each other:

Date partitioning prunes on event_date but does nothing for customer_id. A customer lookup inside a 90-day window still reads every file in 90 partitions.
Z-ORDER on customer_id gives locality only within the files it rewrites, and its effect decays as new files are appended between rewrites. With 30 million distinct values spread across four years, the clustering is also diluted across far more data than any single query touches. The weekly full rewrite is what makes it expensive: every rewrite rewrites nearly the whole table to restore an ordering that the next week of appends will erode.

The better layout is liquid clustering with no partition columns: ALTER TABLE silver.events CLUSTER BY (customer_id, event_date), or the same clause on a rebuilt table. Liquid clustering keeps layout information in the log and clusters incrementally, so OPTIMIZE only touches data that is not yet clustered, which directly addresses the 50-minute rewrite. Key order within the CLUSTER BY list is not significant. Both event_date and customer_id should be keys because both appear in the dominant predicates.

2. How would you migrate a 6 TB table to the new layout with minimal disruption, and what must be dropped first?

Read the worked answer

Partitioning and Z-ORDER are incompatible with liquid clustering, so the first step is to remove both: ALTER TABLE silver.events DROP PARTITIONING and confirm no ZORDER options remain on any OPTIMIZE job. Then add clustering keys.

Two viable paths:

In place. Drop partitioning, add the clustering keys, and run OPTIMIZE silver.events FULL to rewrite pre-existing data into the clustered layout. This is a large one-time rewrite, so schedule it outside the SLA window and expect it to take hours. Everything written afterwards is clustered incrementally.
Rebuild. CREATE TABLE ... CLUSTER BY (customer_id, event_date) AS SELECT ... from the old table and swap names, which gets you a clean layout but needs double storage and a cutover plan.

Either way, validate on a clone first: run the top ten production queries against the cloned table and compare bytes scanned in the query profile. Also confirm the hourly writer still finishes in 15 minutes with the new layout, since clustering adds a small amount of work per write.

3. After the migration, which queries will still scan too much data, and how do you prove the change worked?

Read the worked answer

Benefits depend on predicates that match the clustering keys. Expect improvement for any query that filters on customer_id and/or event_date with equality or range predicates. Expect little or none for:

The all-history dashboards that filter on other columns (for example event_type), because skipping works on file-level statistics for the clustering columns.
Queries that apply functions to the clustering column, such as date_trunc('month', event_date) = ..., which cannot be pushed into skipping.
Queries that select most of the table anyway, where no clustering can help.

Verification comes from execution metrics, not from intuition: read the query profile for files/bytes read versus table size, compare the same SQL before and after, and sample DESCRIBE DETAIL and DESCRIBE HISTORY to see the clustering state and last optimize. Trend it over a couple of weeks so that you catch the slow decay that Z-ORDER had. If the dashboards remain the top scanner, that is a separate problem (aggregation or a pre-computed table), not a clustering fix.