Databricks Performance Optimization: Z-Ordering, Liquid Clustering and Stored Procedures

Quick Answer: Databricks performance optimization: Databricks performance work is mostly table maintenance, not query tuning. Z-Ordering and liquid clustering both work by co-locating similar rows into the same files so that Delta can skip entire files using per-file statistics — which means the benefit depends entirely on file size, and the maintenance commands (OPTIMIZE, VACUUM) have real costs that should be scheduled deliberately. Stored procedures in Unity Catalog help on a different axis: they turn a sequence of steps into one callable unit, which removes repeated setup work and makes pipelines easier to govern.

What this guide covers

• How Z-Ordering actually works, and the file-size rule that decides whether it helps
• Choosing the right column to cluster on
• Liquid clustering, and when it beats Z-Ordering
• OPTIMIZE, VACUUM, and the correct maintenance schedule
• Eliminating small files at the source
• Unity Catalog stored procedures — what they are for and what they are not
• Incremental pipelines with MERGE and the change data feed
• Query-level tuning: Photon, caching, joins and SQL warehouses

---

The real cause of a slow Databricks pipeline

Most “Databricks is slow” tickets are not query problems. They are table problems, and the symptom looks like a query problem.

A pipeline writes a Delta table every hour. Each write adds a few small files. After two months the table has 80,000 files, each holding a few thousand rows. Now a dashboard query against that table has to read the metadata for 80,000 files, work out which ones it needs, and open the ones that match — while the planner burns most of its time on that rather than on the actual data. The same query that took 20 seconds on 2,000 files takes eleven minutes on 80,000.

Nobody writes bad SQL. The table just got slowly worse.

The Databricks optimisation documentation is organised around this idea: the platform provides managed optimisations, and the biggest of them operate on the physical layout of the table rather than on the query.

---

Z-Ordering: co-locating rows so files can be skipped

Z-Ordering is a technique for laying out the rows of a Delta table so that rows with similar values in the ordering column end up in the same files. The mechanism is simple and the consequences are worth understanding precisely.

Delta tables carry per-file statistics: for every column, the minimum and maximum value in that file. When a query filters on a column, the engine reads those statistics and skips any file whose range cannot possibly contain a matching row.

Table layout	Filter	Files read	Rows read
Random order	event_date = '2026-10-14'	All files	Only matching rows, but from every file
Z-Ordered on event_date	event_date = '2026-10-14'	Only the files whose date range covers that day	Only matching rows, from far fewer files

The win comes from two places: fewer files opened, and less I/O. The second is the bigger one on object storage, where a read request has a fixed cost as well as a per-byte cost.

The rule that decides everything: file size

Data skipping only works when files are large enough for the statistics to be meaningful. With a 100 KB file holding two hours of data, the min/max range for a date column spans the whole day, so most files cannot be skipped. With a 500 MB file holding a month of data, each file covers a narrow date range and most files can be skipped.

Practical guidance:

Average file size	Z-Ordering effectiveness
Under ~10 MB	Minimal — statistics too coarse to skip
10–100 MB	Partial — worthwhile for selective filters
100 MB – 1 GB	Good — the practical sweet spot
Over 1 GB	Good, but query planning and read latency get slower

This is why Z-Ordering is a compaction feature first and a clustering feature second. If your table has small files, run OPTIMIZE to compact them; only then does the clustering actually deliver.

The Delta Lake optimisation documentation covers the OPTIMIZE command and the ZORDER clause, including the multi-column form.

Choosing the column

The ordering column should be the one your queries filter on most often, and — this matters — one whose values are correlated with each other, so that co-locating them actually helps.

-- Cluster on the date most queries filter by
OPTIMIZE silver.orders ZORDER BY (order_date);

-- Multi-column form, when queries filter on a combination
OPTIMIZE silver.events ZORDER BY (event_date, region);

Two failure modes to avoid. Clustering on a column nobody filters on is a rewrite with no benefit. And clustering on a high-cardinality, near-unique column — a transaction id, a request id — produces almost the same result as random order, because there is nothing to co-locate.

A useful heuristic: cluster on the column with low-to-moderate cardinality and a skewed distribution, typically a date, a status, a region or a category. Time is the most common and usually the most effective choice.

What it costs

OPTIMIZE rewrites the affected files. It is not free, and it is not instant.

• It consumes compute proportional to the data being rewritten.
• It produces new files, so it briefly increases storage before VACUUM reclaims the old ones.
• On a table where 90% of rows are already well-clustered, the benefit is small and the cost is real.

So the discipline is to cluster the tables that earn it — typically the silver and gold layers that get queried repeatedly — and leave the append-heavy bronze layer alone unless it is being filtered directly.

---

Liquid clustering: the alternative that maintains itself

Liquid clustering takes a different approach. Instead of a periodic rewrite that clusters the data, you declare clustering keys on the table, and the table maintains its own layout as data arrives — files are written already grouped, and the metadata tracks which files belong to which cluster.

CREATE TABLE silver.orders (
  order_id   STRING,
  order_date DATE,
  region     STRING,
  amount     DECIMAL(12,2)
)
USING DELTA
CLUSTER BY (order_date);

The practical differences:

Z-Ordering	Liquid clustering
How it works	A rewrite command regroups the data	The table maintains layout as data arrives
Maintenance command	OPTIMIZE ... ZORDER BY	None required
Clustering keys	Multiple columns allowed	A single column
Rewrite cost	Paid on each run	Avoided
Suitable for	Tables with historical data needing regrouping	Tables with steady incoming data and one dominant filter
Works with small files	Not until compacted	Automatically avoids creating them

The Delta Lake clustering documentation covers the behaviour and the constraint that a table uses one strategy or the other.

The honest guidance: liquid clustering suits the steady-state table that keeps receiving data and is filtered on one thing — an orders table partitioned by date, an events table filtered by event type. Z-Ordering suits the table you already have, with awkward history and a specific filter pattern you need to fix now. Many estates run both, on different tables, and that is fine.

The one thing not to do is declare Z-Ordering as a maintenance habit on a table already using liquid clustering. They are mutually exclusive, and combining them produces confusion rather than performance.

---

The maintenance schedule that actually works

Three commands, one loop, and a schedule that respects your cost model.

OPTIMIZE — compact the files

OPTIMIZE silver.orders;
OPTIMIZE silver.orders ZORDER BY (order_date);

The first form compacts small files only. The second also re-clusters. It rewrites the affected files and generates new ones.

VACUUM — reclaim the space

VACUUM silver.orders RETAIN 168 HOURS;

This removes files that are no longer referenced by the transaction log. The retention window is a genuine trade-off:

Retention	Storage cost	Consequence
Short (hours)	Lowest	Time travel and file-level restore windows are tiny
Medium (7–14 days)	Reasonable	Enough history for most incident investigations
Long (30+ days)	Highest	Comfortable audit and disaster recovery windows

Set the retention to match your actual recovery requirement and your compliance obligations, then document it. The default is a reasonable starting point for many teams and a poor one for regulated ones.

OPTIMIZE without VACUUM is incomplete. Compaction creates new files but leaves the old ones until they are vacuumed, so storage grows until it does. If you are running one, run both.

The schedule

Table type	OPTIMIZE	ZORDER	VACUUM
Bronze, append-only, rarely filtered directly	Rarely	No	Weekly
Silver, filtered and joined daily	Daily, after the load	Yes, on the primary filter column	Daily
Gold, aggregates, heavy query load	Daily	Yes	Daily
Any table with continuous small writes	Enable managed auto-compaction	Per the clustering strategy	Weekly

The practical sequence in a nightly job: load → optimize → update downstream → vacuum on a longer cycle. Our Auto Loader ingestion guide covers the write side and how to stop small files being created in the first place.

Fixing small files at the source

Maintenance is a repair job. Prevention is better.

• Write bigger files. Control the output size of each write. A target in the hundreds of megabytes per file is reasonable for analytical tables.
• Batch the writes. If a pipeline appends every thirty seconds, that is a hundred files an hour for no benefit.
• Use managed auto-optimisation. The platform can handle compaction for you when you would rather not schedule it.
• Compaction by partition boundary. When bronze is partitioned by ingestion date, compacting yesterday’s files once the day is complete is a natural, cheap batch job.

---

Stored procedures: the governance layer for pipelines

A Unity Catalog stored procedure is a named, parameterised unit of SQL logic stored in the catalogue. You call it with CALL, and it runs inside the platform’s permission and lineage model.

CREATE OR REPLACE PROCEDURE silver.sp_refresh_customer_summary(
  p_run_date DATE
)
RETURNS STRING
LANGUAGE SQL
BEGIN
  -- Rebuild the customer rollup for a given day
  MERGE INTO gold.customer_summary AS t
  USING (
    SELECT customer_id,
           sum(order_amount) AS lifetime_value,
           count(*)           AS order_count
    FROM silver.orders
    WHERE order_date = p_run_date
    GROUP BY customer_id
  ) AS s
  ON t.customer_id = s.customer_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;

  RETURN 'customer_summary refreshed for ' || CAST(p_run_date AS STRING);
END;

CALL silver.sp_refresh_customer_summary(DATE '2026-10-14');

The CREATE PROCEDURE reference documents the syntax and the supported parameter modes.

What they genuinely buy you

Governance. A procedure in Unity Catalog is a securable object. You grant EXECUTE on it rather than granting table-level write access to everyone who needs the refresh. That single change is often the difference between a governed platform and an open one.

One definition, many callers. If the daily job, a backfill, an ad-hoc analyst and a downstream pipeline all need the same refresh logic, one procedure means one implementation. Four copies of the same MERGE is four chances to diverge.

Reduced orchestration overhead. A multi-step pipeline that a job would otherwise assemble from several task steps becomes one call. Fewer moving parts means fewer places for a run to fail.

Query plans are reused. Spark caches the resolved plan for a prepared statement, which cuts planning time on repeatedly executed logic.

What they do not buy you

Procedures are not a substitute for pipeline design. Two honest limitations:

• They are SQL logic, not a programming model. No loops over dataframes, no custom Python inside a SQL procedure. If you need that, it belongs in a notebook or a job, not a procedure.
• They do not fix a bad pipeline. Wrapping a slow MERGE in a procedure makes the slow MERGE callable, not faster.

Use procedures for business logic that several callers need, and that should be governed. Do not use them to hide orchestration complexity — that is what a job or a pipeline is for.

---

Incremental pipelines: MERGE, change data feed and materialized views

The other half of pipeline performance is doing less work.

MERGE instead of full overwrite

A full overwrite of a large table every night is the most common avoidable cost in a lakehouse. MERGE updates only what changed:

from delta.tables import DeltaTable

delta = DeltaTable.forName(spark, "silver.orders")

(
    delta.alias("t")
    .merge(
        source=bronze_orders.alias("s"),
        condition="t.order_id = s.order_id AND t._ingested_at <= s._ingested_at"
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

The freshness predicate in the join condition matters as much as the merge itself. Without it, a replayed batch can overwrite newer rows with older values — a bug that only appears when history is replayed, and one that quietly corrupts data rather than failing.

Change data feed: skip the rows that did not change

Delta tables can emit a change data feed — row-level change information for anything that was inserted, updated or deleted. That lets a downstream table process only what changed instead of re-reading the whole upstream table.

# Read only what changed in the source since the last run
changes = (
    spark.readStream
    .format("delta")
    .option("readChangeFeed", "true")
    .option("startingVersion", last_processed_version)
    .load("silver.orders")
)

The change data feed documentation covers the options and the retention behaviour, and the CDC explainer covers how change capture fits into this pattern.

Materialized views and streaming tables

For aggregations that are queried repeatedly, computing them once and serving the result is almost always faster than recomputing on every query. The materialized views and streaming tables documentation covers both, including how they stay fresh.

Query-level tuning that remains relevant

Table maintenance is not the whole story. These still matter:

• Photon for SQL and DataFrame workloads, where the vectorised engine lowers both latency and cost per query. The Photon documentation covers when the premium is worth it.
• Broadcast joins for small dimension tables, which avoid a shuffle entirely.
• Caching deliberately — a cached table that is used more than twice in a job is usually worth the memory.
• SQL warehouse sizing and auto-stop, which is where a lot of idle spend hides. The SQL warehouse documentation covers the configuration.
• Serverless compute for jobs where you would rather not size clusters at all. The serverless compute documentation covers the model.

And the diagnostic that ties it together: the system tables give you query history, cluster usage and cost attribution without instrumenting anything yourself. If you are going to optimise, start there.

---

The order to work in

Performance work goes wrong when it starts with the query. The sequence that works:

1. Measure. Pull query history from system tables. Find the tables and queries that actually cost you.
2. Fix file sizes. Compact the worst table. Re-measure — a surprising share of “slow queries” disappear here.
3. Cluster the table that is queried most, on the column it is filtered by. Re-measure.
4. Make the pipeline incremental. MERGE and change data feed, so the write does not grow with history.
5. Tune the query. Joins, caching, Photon, warehouse sizing.
6. Schedule the maintenance. OPTIMIZE and VACUUM on the tables that earned them.

Steps 2 and 3 usually deliver more than step 5, and they compound — a well-laid-out table stays well-laid-out.

---

How to read these numbers

No pricing is quoted here, because Databricks cost depends on your contract, your cloud and your workload mix. The structural claims — that a rewrite costs compute, that vacuum reclaims storage, that skipping files reduces read requests — are properties of the table format, not price claims. Benchmark figures cited from our own reports are published with methodology and reproducible scripts.

---

Key terms

Term	What it means in practice
Z-Ordering	Laying out rows so similar values co-locate, enabling file-level data skipping.
Data skipping	Reading only the files whose statistics can contain matching rows.
File statistics	Per-file min and max values per column, the basis of skipping.
OPTIMIZE	Compacts small files into larger ones and optionally re-clusters. Costs a rewrite.
VACUUM	Deletes files no longer referenced by the transaction log. Reclaims storage.
Liquid clustering	A table maintaining its own layout against a declared clustering key.
Retention window	How long old versions and files are kept. Trades storage against recoverability.
MERGE	Upsert semantics against a Delta target — the backbone of incremental loads.
Change data feed	Row-level change information, so downstream tables process only what changed.
Stored procedure	A governed, parameterised SQL routine in Unity Catalog, invoked with CALL.
Materialized view	A precomputed result maintained by the platform for fast repeated queries.

---

Frequently asked questions

What does Z-Ordering actually do in Databricks?

Z-Ordering rewrites a Delta table so that rows with similar values in the ordering column are stored in the same files. Because Delta tracks per-file minimum and maximum values, queries filtering on that column can skip entire files instead of opening all of them. The benefit depends on file size: skipping only works when files are large enough for the statistics to be meaningful, so Z-Ordering on a table of small files does very little until it is compacted.

Should I use Z-Ordering or liquid clustering?

They are alternatives for the same goal, and a table uses one or the other. Z-Ordering suits a table you already have, where you need to fix the layout of historical data and you can filter on more than one column. Liquid clustering suits a table with steady incoming data filtered on a single dominant column, because it maintains the layout automatically without a periodic rewrite. Running both on one table is a mistake.

Is OPTIMIZE expensive?

It costs compute proportional to the data it rewrites, plus transient storage for the new files. It is worth running on silver and gold tables that are queried often, and rarely worth running on an append-only bronze table nobody filters directly. The discipline that matters is scheduling it deliberately on the tables that earn it, and pairing it with VACUUM so the space is actually reclaimed.

What does VACUUM do and what does it break?

VACUUM removes files that the transaction log no longer references, which is what stops storage growing without bound after compaction. It also removes the ability to time-travel back to older versions and it can break long-running queries that started before the vacuum. The retention window is a trade-off: a shorter window costs less storage and leaves less recoverability, so set it to match your actual recovery and compliance requirements and document the choice.

When are stored procedures worth using?

When several callers need the same business logic and that logic should be governed — granting EXECUTE on a procedure is much tighter than granting write access to the underlying tables. They also reduce orchestration overhead by turning multi-step logic into one call. They are not a substitute for pipeline design: they are SQL logic, not a programming model, and they do not make a slow transformation faster.

How do I stop a pipeline from creating small files?

Prevent it at the write rather than repairing it later. Control the output file size of each write to the hundreds of megabytes, batch frequent small appends into fewer larger ones, and use managed auto-compaction where you would rather not schedule maintenance. Where a table is partitioned by ingestion date, compacting a finished day’s files once is a natural and cheap batch job.

Does liquid clustering work with Z-Ordered tables?

No — a table uses one clustering strategy. You can declare a clustering key on a table, and the ZORDER clause in OPTIMIZE is the alternative approach; combining them on the same table produces confusion rather than performance. Many estates run both, on different tables, and that works fine.

What is the change data feed and when should I use it?

It is row-level change information emitted by a Delta table for anything inserted, updated or deleted. Use it when a downstream table processes the same upstream table repeatedly, because it lets that job read only what changed instead of re-reading the whole source. It is the standard mechanism behind efficient incremental and streaming pipelines.

---

Where to take this next

Table maintenance and incremental pipeline design are the two skills that separate a Databricks user from a Databricks engineer. They are also the two most commonly learned the hard way, which is why they get dedicated module time in the Databricks Training course — 12 weeks and 72 hours across eight modules, with a hands-on module on Delta Lake performance, Z-Ordering, liquid clustering and maintenance scheduling, followed by Unity Catalog governance, jobs and pipelines, MLflow, and a capstone that assembles a governed pipeline with a runbook and a cost estimate.

For the platform-neutral version of the same thinking — partitioning, compaction, incremental loads, cost control — the Data Engineering Training programme spans 16 weeks and 96 hours. The PySpark memory tuning guide covers the other half of the performance problem, at the executor level.

Our published comparisons — the Snowflake vs Databricks benchmark and the Enterprise Data Lake Benchmark — include reproducible workloads and methodology.

---

References

• Databricks — Optimizations
• Databricks — Delta Lake optimization and OPTIMIZE
• Databricks — Delta Lake clustering and liquid clustering
• Databricks — Delta Lake documentation
• Databricks — CREATE PROCEDURE reference
• Databricks — change data feed
• Databricks — materialized views and streaming tables
• Databricks — change data capture explainer
• Databricks — Photon engine
• Databricks — serverless compute
• Databricks — SQL warehouses
• Databricks — system tables
• SkilBrill — Snowflake vs Databricks benchmark

---

---

---

---

---

---