06.9 Z-Ordering & Data Skipping
A distributed query becomes dramatically cheaper when it can avoid reading data that cannot possibly match the predicate.
Core mental model
Partition pruning → eliminate partitions
Data skipping → eliminate files
Z-Ordering → improve physical locality so statistics can eliminate more files
Z-Ordering is a physical-layout technique. Data skipping is the query optimization capability that benefits from useful file-level statistics.
1. Why data skipping exists
Imagine a table with 10,000 Parquet files and a query filtering on a highly selective customer.
If every file must be opened, the engine wastes I/O. If statistics show that only a small number of files can contain the value, most of the table can be skipped.
2. File statistics
Conceptually:
file-001: customer_id min=1, max=5000
file-002: customer_id min=5001, max=10000
file-003: customer_id min=85000, max=91000
For WHERE customer_id = 90042, files whose ranges cannot contain 90042 can potentially be eliminated.
See Databricks — Data skipping.
3. Partition pruning vs data skipping
Partition pruning eliminates entire partitions. Data skipping eliminates files inside the remaining search space.
Table
├── date=2026-01
├── date=2026-02
│ ├── file-A customer 1–1000
│ ├── file-B customer 1001–5000
│ ├── file-C customer 90000–91000 ← candidate
│ └── file-D customer 20000–30000
└── date=2026-03
Good physical design can allow both mechanisms to work together.
4. Selectivity
Skipping is most valuable when predicates are selective.
high selectivity
↓
few matching files
↓
large scan reduction
↓
high potential benefit
Choose physical-layout columns based on query access patterns, not simply business importance.
5. What Z-Ordering does
Example:
OPTIMIZE main.sales.orders
ZORDER BY (customer_id);
With multiple columns:
OPTIMIZE main.sales.orders
ZORDER BY (customer_id, product_id);
The objective is not to create a logical index. It is to improve physical locality so file statistics can become more useful for selective filters.
See Databricks — Z-Order.
6. Z-Order is not an index
Traditional index → index structure → lookup
Z-Order → physical data organization → locality → statistics → file skipping
Do not describe Z-Order as a B-tree-like index.
7. One-dimensional intuition
Random distribution can cause almost every file to contain a target customer. Better locality can concentrate related values so a selective predicate touches fewer files.
Z-Ordering attempts to improve multidimensional locality without requiring a rigid single-column range layout.
8. Multiple Z-Order columns
Avoid blindly doing:
ZORDER BY (customer_id, product_id, region, supplier_id, company_code);
More columns do not automatically mean better performance. Start with the predicates that matter most and measure.
9. The query-pattern test
Collect real queries and ask:
- Which predicates are frequent?
- Which are selective?
- Which queries dominate runtime?
- Which scan the most data?
- Which columns are repeatedly used together?
This is stronger than choosing columns from the schema alone.
10. When Z-Order helps
Z-Ordering is a candidate when:
- the table is sufficiently large
- queries repeatedly filter on candidate columns
- predicates are selective
- physical locality can reduce scanned files
- maintenance cost is justified
It is less useful when queries scan most of the table, predicates have low selectivity, the table is tiny, or the dominant bottleneck is joins/aggregations.
11. Z-Order and OPTIMIZE
OPTIMIZE + ZORDER BY
↓
rewrite selected active files
↓
improve locality
↓
future queries may skip more files
Z-Ordering is therefore a physical maintenance operation.
12. Maintenance cost
For a large table, repeated Z-Ordering can become expensive.
Evaluate:
Z-Order compute
+ data rewritten
+ storage churn
+ operational complexity
versus
scan reduction
+ SLA improvement
+ compute savings
The correct decision is economic, not ideological.
13. Z-Order after MERGE
CDC-heavy tables can experience repeated file rewrites.
CDC → MERGE → replacement files → fragmentation → layout maintenance
If mutation is continuous, excessive Z-Order frequency can consume substantial compute.
14. Z-Order vs liquid clustering
| Dimension | Z-Order | Liquid clustering |
|---|---|---|
| Mechanism | OPTIMIZE-based layout | Evolving clustering layout |
| Typical fit | Existing suitable tables | Many new workloads |
| Adaptation | Explicit maintenance | Designed to evolve |
| Workload changes | More manual | More adaptive |
Do not assume liquid clustering automatically wins. Benchmark representative queries.
See Databricks — Liquid clustering.
15. Data skipping is not magic
A predicate matching most rows provides little opportunity for skipping.
For example, if almost every file contains positive values, WHERE amount > 0 cannot eliminate much data.
The engine can only skip what physical statistics prove is irrelevant.
16. Predicate formulation
Prefer predicates that preserve optimizer reasoning about the underlying column.
For example, a date range is often clearer for pruning than wrapping the column in a function:
WHERE order_date >= DATE '2026-01-01'
AND order_date < DATE '2027-01-01'
Always verify actual behavior with query plans and runtime metrics.
17. SAP/BW example
Suppose an SAP procurement fact contains:
- purchasing organization
- supplier
- material
- company code
- document date
- purchase order
- amount
A dashboard repeatedly filters on purchasing organization and document date.
Evaluate:
- partitioning by date?
- clustering by access pattern?
- Z-Order for an existing table?
- purchasing-organization selectivity?
- actual files skipped?
Do not automatically partition by every BW characteristic.
18. Hands-on Lab — baseline
Run a representative selective query and record:
- elapsed time
- files scanned
- files skipped
- bytes read
- rows returned
Save the baseline. Then apply an appropriate layout strategy and repeat with a comparable workload.
19. Hands-on Lab — one column vs multiple columns
Compare a carefully selected single-column Z-Order strategy with a carefully selected multi-column strategy.
Measure:
- optimization time
- bytes rewritten
- files scanned
- files skipped
- query latency
Do not assume the multi-column version is better.
20. Hands-on Lab — selectivity
Compare a highly selective predicate such as customer_id = 90042 with a low-selectivity predicate such as region IS NOT NULL.
Observe how the potential value of data skipping changes.
21. Troubleshooting
Z-Order did not improve the query
Check predicate selectivity, whether the query actually filters on the Z-Ordered column, table size, files skipped, bytes scanned, competing bottlenecks and mutation frequency.
OPTIMIZE cost is too high
Check scope, cadence, mutation rate, table size, query benefit and whether liquid clustering or predictive optimization is more appropriate.
Queries still scan most files
Check low selectivity, statistics, predicate formulation, physical locality, partition design and whether the query is actually I/O-bound.
22. Architect decision framework
Slow query
↓
Dominant predicates
↓
Selectivity
↓
Partition pruning
↓
File statistics / data skipping
↓
Z-Order or liquid clustering
↓
Measure scan reduction
↓
Compare maintenance cost
↓
Adopt only if ROI is positive
23. Interview questions
What is data skipping?
A technique using file-level information to avoid reading files that cannot satisfy a predicate.
What is Z-Ordering?
A physical data-layout technique used with OPTIMIZE to improve multidimensional locality and potentially increase data skipping.
Is Z-Order an index?
No.
Should every filter column be Z-Ordered?
No. Choose based on workload, selectivity and measured benefit.
Z-Order or partitioning?
They solve different problems. Partitioning reduces the search space at partition level; data skipping works at file level.
Z-Order or liquid clustering?
Evaluate platform capabilities, workload and migration cost. Liquid clustering is an important modern option while Z-Order remains relevant for suitable existing workloads.
24. Architect mental model
Query predicate
↓
Partition pruning
↓
File statistics
↓
Data skipping
↓
Parquet scan
↓
Spark execution
And:
Z-Order
↓
physical locality
↓
more useful statistics
↓
potentially more file skipping
The goal is not "Z-Order the table." The goal is "reduce the amount of data the query must read."
Production checklist
- Start with real query patterns.
- Measure predicate selectivity.
- Separate partition pruning from data skipping.
- Treat Z-Order as physical maintenance.
- Avoid excessive Z-Order dimensions.
- Measure files scanned and skipped.
- Measure bytes read, not only latency.
- Include maintenance cost in the decision.
- Compare against liquid clustering for modern designs.
- Revisit the strategy when workloads change.