Decide when a Delta table needs OPTIMIZE or Z-ORDER

At a glance

Learning objectives

• Decide if a table needs OPTIMIZE from file stats
• Choose Z-ORDER columns from query predicates
• Know when liquid clustering beats partitioning

What you need

• A Delta table with file-stats and history access
• The query predicates your consumers actually filter on
• [object Object]

Steps

1. Decide: files < 32 MB and many -> run OPTIMIZE. (from skill-delta-optimizer )
2. Decide: queries filter on 1-2 high-cardinality columns -> ZORDER BY those columns. (from skill-delta-optimizer )
3. Decide: high-cardinality, many predicates, frequent writes -> liquid clustering. (from skill-delta-optimizer )
4. Decide: predicate on one low-cardinality column (date) -> partition, or leave it; Delta handles pruning well already. (from skill-delta-optimizer )
5. Measure — compare numFiles and query bytes scanned before and after; if scanning did not drop, the Z-ORDER was on the wrong columns. (from skill-delta-optimizer )

Check yourself

• A table with thousands of sub-32 MB files is OPTIMIZEd and numFiles drops while the next query reports fewer bytes scanned.
• A Z-ORDER on a column no query filters on is caught by the before/after measurement and reverted instead of shipped.

Worked example

OPTIMIZE db.tbl ZORDER BY (user_id);  -- Delta

- Files < 32 MB and many → `OPTIMIZE`.
- Queries filter on 1-2 high-cardinality columns → `ZORDER BY` those columns.
- High-cardinality, many predicates, frequent writes → liquid clustering.
- Predicate on one low-cardinality column (date) → partition (or leave it; Delta
  handles pruning well already).

Compare `numFiles` and query `bytes scanned` before and after. If scanning
didn't drop, the Z-ORDER was on the wrong columns.

Sourced from

• Delta Optimization Advisor

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• If bytes scanned did not drop after Z-ORDER, the Z-ORDER was on the wrong columns — measure before and after.
• A predicate on one low-cardinality column (date) rarely needs Z-ORDER — partition, or leave it; Delta handles pruning well already.
• Liquid clustering is for high-cardinality, many-predicate, frequently written tables — not the default for every table.

Production considerations

• Track numFiles and bytes scanned per query as the standing success metric for layout changes.
• Re-run the decision rules when write patterns change; frequent writes with many predicates move the answer to liquid clustering.

Diagram

delta optimize decision
   |
   |- files < 32 MB and many --> OPTIMIZE
   |
   |- 1-2 high-cardinality predicates --> ZORDER BY those columns
   |
   |- many predicates + frequent writes --> liquid clustering
   |
   |- low-cardinality date predicate --> partition or leave it
   |
   --> measure numFiles + bytes scanned before/after

Registry stats