Databricks Performance Optimization: What Changed, What Still Matters, and What Should Be Automated

[AmitDECopilot]

Performance optimization in Databricks used to follow a familiar playbook:

1. Partition large Delta tables.

2. Compact small files with OPTIMIZE.

3. apply ZORDER BY on frequently filtered columns.

4. Run VACUUM.

5. Collect statistics.

6. Increase cluster size when queries remained slow.

These techniques were useful, and many continue to have value. However, the Databricks optimization model has evolved significantly over the past two years.

Today, optimization is not just about manually rearranging files. It is a combination of:

• Intelligent data layout

• Automated table maintenance

• Query-engine improvements

• Runtime optimization

• Workload-aware compute

• Better statistics and data skipping

• Continuous performance diagnosis

Databricks now recommends liquid clustering for modern table-layout use cases, particularly instead of traditional partitioning and Z-Ordering. At the same time, predictive optimization can automate operations such as OPTIMIZE, VACUUM, and ANALYZE for eligible Unity Catalog managed tables. 

This article provides a current framework for understanding Databricks performance optimization in 2026.

---

1. Performance optimization is now a layered problem

When a Databricks workload is slow or expensive, the issue rarely has only one cause.

A query may be slow because:

• Too many small files are being opened.

• The physical data layout does not match the filtering pattern.

• Statistics are unavailable or outdated.

• A join is producing an unexpectedly large intermediate dataset.

• Data skew is overloading a small number of tasks.

• The workload is scanning unnecessary columns.

• The query is spilling from memory to disk.

• The selected compute is poorly sized.

• Photon is unavailable or disabled.

• Repeated MERGE operations have degraded file layout.

• The query is technically fast but running on unnecessarily expensive infrastructure.

For this reason, Databricks optimization is best understood through four layers:

1. Data-layout optimization

2. Table-maintenance optimization

3. Query and execution-engine optimization

4. Compute and workload optimization

Increasing compute should not be the first response to every performance problem. A larger cluster can make an inefficient query finish faster while also making it more expensive.

The correct starting point is understanding what the workload is actually doing.

---

2. Layer One: Data-layout optimization

Data layout determines how records are distributed across files.

A query may contain a highly selective filter, but Databricks still needs an efficient way to determine which files are relevant. Poor data layout can force the engine to inspect hundreds or thousands of files even when the result contains only a few rows.

The major data-layout strategies are:

• Traditional partitioning

• Z-Ordering

• Liquid clustering

• Automatic liquid clustering

• File compaction

• Data-skipping statistics

Traditional partitioning

Partitioning physically separates data based on the value of one or more columns.

For example:

CREATE TABLE sales_transactions (
    transaction_id BIGINT,
    customer_id BIGINT,
    transaction_date DATE,
    region STRING,
    amount DECIMAL(18,2)
)
USING DELTA
PARTITIONED BY (region);

This can work when a column has:

• Low or moderate cardinality

• Stable access patterns

• Reasonably balanced data distribution

• Frequent use in query filters

However, poorly designed partitioning can create serious problems.

Partitioning by a high-cardinality field such as customer_id, transaction_id, or timestamp can create many small directories and files. Partitioning can also become ineffective when query patterns change over time.

Databricks now recommends evaluating liquid clustering before adopting custom partitioning strategies for new tables. 

Partitioning is not automatically wrong, but it should no longer be the default answer for every large Delta table.

---

Z-Ordering

Z-Ordering colocates related values from one or more columns into the same sets of files.

A common example is:

OPTIMIZE main.sales.transactions
ZORDER BY (customer_id, transaction_date);

Z-Ordering can improve queries that regularly filter on the selected columns because fewer files may need to be scanned.

However, Z-Ordering is a maintenance operation, not a permanent self-adjusting design. As new data arrives, organizations often need to run OPTIMIZE ZORDER again.

Z-Ordering may remain reasonable for:

• Existing Delta tables that already perform well

• Stable workloads with predictable filtering patterns

• Legacy architectures where migration has no measurable benefit

• Environments where liquid clustering is unavailable or inappropriate

The important point is that Z-Ordering and liquid clustering are not cumulative table-layout strategies. Liquid clustering replaces partitioning and Z-Ordering for the table on which it is enabled. Databricks explicitly states that clustering is not compatible with Z-Ordering. 

Therefore, this is not a valid optimization sequence:

Partition the table
        ↓
Apply Z-Order
        ↓
Enable liquid clustering

The correct decision is to choose the most appropriate layout strategy for that table.

---

Liquid clustering

Liquid clustering is one of the most important changes in the Databricks optimization model.

It allows records to be organized using clustering keys without requiring traditional directory-based partitioning. Databricks describes it as a replacement for both partitioning and Z-Ordering. 

A liquid-clustered table can be created as follows:

CREATE TABLE main.sales.transactions (
    transaction_id BIGINT,
    customer_id BIGINT,
    transaction_date DATE,
    region STRING,
    product_category STRING,
    amount DECIMAL(18,2)
)
USING DELTA
CLUSTER BY (customer_id, transaction_date);

An existing compatible table can be updated:

ALTER TABLE main.sales.transactions
CLUSTER BY (customer_id, transaction_date);

Newly written data can then be organized according to the clustering configuration when optimization occurs.

Liquid clustering is particularly useful when:

• Filter columns have relatively high cardinality.

• Traditional partitions would become too granular.

• Query patterns use multiple columns.

• The table grows continuously.

• Clustering keys may need to change.

• Data arrives out of order.

• The table receives frequent updates or merges.

The clustering keys should normally represent columns frequently used in selective filters—not simply the columns most commonly selected in the output.

For example:

SELECT
    transaction_date,
    SUM(amount)
FROM main.sales.transactions
WHERE customer_id = 102345
  AND transaction_date >= DATE '2026-01-01'
GROUP BY transaction_date;

In this workload, customer_id and transaction_date may be useful clustering candidates because they determine which records need to be read.

By contrast, clustering on amount simply because it appears in the SELECT list would generally provide little value.

---

Changing clustering keys

One advantage of liquid clustering is that clustering columns can be changed without redefining the table’s partition structure.

For example:

ALTER TABLE main.sales.transactions
CLUSTER BY (region, product_category, transaction_date);

However, changing the definition does not automatically rewrite every historical row according to the new keys. Existing files may retain their earlier organization until they are rewritten through optimization. Databricks documentation notes that rows clustered by previous keys are not immediately affected merely because the clustering columns were altered. 

For a full rewrite where appropriate, Databricks supports:

OPTIMIZE main.sales.transactions FULL;

OPTIMIZE FULL should be treated carefully on very large tables because rewriting a significant volume of data can be expensive. It should be used when the expected performance benefit justifies the work rather than as a routine command after every configuration change.

---

Automatic liquid clustering

Manual clustering still requires engineers to answer difficult questions:

• Which columns should be selected?

• Are current filters representative of future use?

• Should an old clustering key be removed?

• Is the benefit worth the maintenance cost?

• Have access patterns changed?

Automatic liquid clustering moves part of that responsibility to the platform. For eligible managed tables, Databricks can analyze workload behavior and choose or update clustering keys. Predictive optimization can then use those keys while maintaining the table.

This represents a major change in philosophy.

The traditional model was:

Engineers predict the access pattern and manually organize the data.

The emerging model is:

The platform observes the workload and continuously makes appropriate maintenance decisions.

That does not remove the need for engineering judgment. Teams still need to validate whether the resulting layout is appropriate and whether performance and cost are improving.

---

3. Data skipping: the real reason layout matters

Clustering does not make business logic simpler, nor does it remove the cost of joins and aggregations.

Its primary value is often that it improves data skipping.

Databricks automatically collects file-level statistics when data is written to Delta Lake and managed Apache Iceberg tables. These statistics can include:

• Minimum values

• Maximum values

• Null counts

• Record counts

During query execution, Databricks can use these statistics to determine that certain files cannot contain matching records. Those files can then be skipped. 

Suppose a table contains five years of transactions and a query requests one customer’s records for a seven-day period.

Without useful layout and statistics, the engine may inspect a large portion of the table.

With well-clustered data and effective statistics, many files can be excluded before they are read.

This leads to an important distinction:

• Compaction reduces the number of files.

• Clustering improves how values are colocated.

• Statistics describe the values stored in each file.

• Data skipping uses that information to avoid unnecessary reads.

These features complement one another, but they solve different parts of the problem.

---

4. Layer Two: Automated table maintenance

Data layout degrades over time.

Streaming ingestion, incremental loads, updates, deletes, and merges can introduce new files and reduce the effectiveness of the existing layout.

Historically, engineering teams scheduled commands such as:

OPTIMIZE main.sales.transactions;

VACUUM main.sales.transactions;

ANALYZE TABLE main.sales.transactions
COMPUTE STATISTICS;

The challenge was deciding:

• Which tables needed maintenance?

• How often should it run?

• Should the entire table be optimized?

• Was the maintenance cost greater than its benefit?

• Were statistics sufficiently current?

• Were unused files accumulating?

Predictive optimization addresses much of this operational overhead.

---

Predictive optimization

Predictive optimization automatically performs eligible maintenance operations for Unity Catalog managed tables.

The supported maintenance capabilities include:

• OPTIMIZE

• VACUUM

• ANALYZE

Databricks identifies tables that could benefit from maintenance, schedules the operation, and collects statistics as data is written.

This has several practical benefits:

• Fewer fixed maintenance jobs

• Less manual table-by-table monitoring

• Reduced small-file accumulation

• More consistent statistics

• Lower risk of unnecessary maintenance

• Better alignment between maintenance and actual workload activity

Predictive optimization is specifically tied to Unity Catalog managed tables. Organizations using external tables or unsupported architectures may still need manual maintenance processes.

That distinction is important. A recommendation such as “enable predictive optimization everywhere” is incomplete without considering table ownership and governance architecture.

---

Monitoring predictive optimization

Automation should never mean invisibility.

Databricks provides a predictive-optimization system table that records operation history. Teams can use it to understand which operations were performed and how the feature is maintaining their data estate.

A mature platform team should monitor:

• Tables receiving frequent optimization

• Duration of maintenance operations

• Data volume rewritten

• Storage reclaimed through vacuum

• Tables that remain unoptimized

• Cost versus measurable query benefit

• Unexpected clustering-key changes

• Workloads repeatedly degrading table layout

This is especially important in large environments where thousands of tables may be managed automatically.

---

Optimized writes and auto compaction

Small files are a common performance issue in distributed data systems.

A pipeline that writes many tiny files creates overhead in:

• File discovery

• Metadata processing

• Task scheduling

• File opening and closing

• Object-store requests

• Query planning

Optimized writes attempt to produce better-sized files during the write itself. Auto compaction combines small files after write operations under supported conditions.

For MERGE, UPDATE, and DELETE, modern Databricks runtimes enable relevant write optimization and compaction behavior automatically.

However, these features do not mean that every table will always have a perfect file layout. Large ingestion volumes, unusual write patterns, streaming micro-batches, and frequent mutations can still create maintenance needs.

The right question is no longer simply:

Did we run OPTIMIZE?

It is:

Is file layout materially affecting the workload, and is platform-managed maintenance already addressing it?

---

5. Layer Three: Query-engine optimization

Even a perfectly organized table can perform poorly when the query itself is inefficient.

Databricks query performance depends heavily on:

• Predicate selectivity

• Join strategy

• Shuffle volume

• Data skew

• Column projection

• Runtime planning

• Intermediate result size

• Memory pressure

• Engine capabilities

Photon

Photon is Databricks’ vectorized execution engine for SQL and DataFrame workloads.

It accelerates operations such as:

• Scans

• Filters

• Aggregations

• Hash joins

• Shuffles

• Delta Lake writes

• Supported SQL expressions

Photon generally does not require applications to be rewritten. Existing supported SQL and DataFrame operations can benefit when run on Photon-enabled compute. 

Photon and liquid clustering solve different problems:

• Liquid clustering attempts to reduce how much data must be read.

• Photon processes the data that remains more efficiently.

Similarly, predictive optimization maintains the table, while Photon improves execution.

These capabilities should not be discussed as competing alternatives.

---

Adaptive Query Execution

Spark initially creates a physical execution plan based on available statistics and estimates. However, estimates can be incomplete or inaccurate.

Adaptive Query Execution, or AQE, uses runtime statistics to revise parts of the plan while the query is executing.

AQE can help with:

• Coalescing small shuffle partitions

• Changing join strategies

• Handling skewed partitions

• Propagating empty relations

• Reducing unnecessary downstream work

Databricks describes AQE as a framework for dynamic planning and replanning based on runtime statistics. 

AQE does not excuse poor query design.

For example, it cannot make an unintended many-to-many join logically correct. It may improve the execution of that join, but the workload can still generate billions of unnecessary rows.

---

Join optimization

Joins frequently dominate the cost of data-engineering workloads.

Before tuning the cluster, validate:

1. Is the join key correct?

2. Is the expected relationship one-to-one, one-to-many, or many-to-many?

3. Are duplicate keys creating row multiplication?

4. Can filters be applied before the join?

5. Are unnecessary columns being carried through the shuffle?

6. Is one side small enough to broadcast?

7. Is the data heavily skewed?

8. Are statistics available?

9. Is the join using an equality condition or a range condition?

A safer pattern is:

WITH filtered_transactions AS (
    SELECT
        customer_id,
        transaction_date,
        amount
    FROM main.sales.transactions
    WHERE transaction_date >= DATE '2026-01-01'
),
active_customers AS (
    SELECT
        customer_id,
        customer_segment
    FROM main.customer.customers
    WHERE active_flag = true
)
SELECT
    c.customer_segment,
    SUM(t.amount) AS total_amount
FROM filtered_transactions t
JOIN active_customers c
    ON t.customer_id = c.customer_id
GROUP BY c.customer_segment;

This limits rows and columns before the join instead of joining entire tables and filtering afterward.

Databricks also provides specific guidance and features for join optimization, including range-join optimization for point-in-interval and interval-overlap conditions. 

Broadcast hints should be applied only after inspecting the plan and table sizes. A hard-coded hint can become harmful when data volume changes.

---

Dynamic file pruning

Dynamic file pruning can improve performance when filters from one side of a join can be used to skip files on the other side.

This can be especially valuable in star-schema patterns where a filtered dimension limits a large fact table.

Photon expands the situations in which dynamic file pruning can be used reliably. For MERGE, UPDATE, and DELETE, Photon-enabled compute is required for dynamic file pruning, while Photon also provides broader support for SELECT workloads. 

Dynamic file pruning again illustrates why optimization is layered:

• Statistics identify candidate files.

• Data layout determines how effectively values are grouped.

• The query plan supplies filter information.

• Photon improves execution support.

---

MERGE optimization

MERGE is central to incremental data engineering, but poorly designed merge logic can be expensive.

Consider:

MERGE INTO main.sales.transactions AS target
USING staging.transactions AS source
ON target.transaction_id = source.transaction_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

If the source contains only one day of data but the target contains five years, the target may still require substantial scanning unless additional pruning conditions are available.

A better approach may include a bounded business-date condition:

MERGE INTO main.sales.transactions AS target
USING staging.transactions AS source
ON target.transaction_id = source.transaction_id
AND target.transaction_date >= DATE '2026-07-01'
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

This is valid only when the business logic guarantees that older target records cannot match. Performance conditions must never change correctness.

Databricks also uses low-shuffle merge techniques to reduce file rewrites and lower the need to re-run optimization after merge-heavy workloads. 

The broader lesson is that incremental processing should restrict both:

• The source records being processed

• The portion of the target that could logically match

---

6. Layer Four: Compute and workload optimization

Data and query optimization cannot compensate for every compute problem.

Databricks workloads can run on different compute models, including SQL warehouses, jobs compute, all-purpose compute, and serverless options.

Important choices include:

• Serverless versus classic compute

• SQL warehouse size

• Minimum and maximum scaling

• Auto-stop configuration

• Job-specific versus shared clusters

• Runtime version

• Photon availability

• Worker and driver sizing

• Memory-intensive versus compute-intensive instance types

• Workload isolation

• Concurrency requirements

Serverless SQL warehouses include Photon, predictive I/O, and intelligent workload-management capabilities, while feature availability differs across warehouse types. 

A small warehouse may be appropriate for a well-pruned query running with limited concurrency. The same warehouse may struggle when dozens of users issue large joins simultaneously.

Conversely, a large warehouse may complete an inefficient query quickly but consume far more resources than necessary.

Performance must therefore be measured together with cost.

Useful metrics include:

• Execution duration

• Queue time

• Bytes scanned

• Files read

• Shuffle read and write

• Spill to disk

• Peak memory usage

• Number of tasks

• Compute uptime

• Cost per successful workload

• Cost per processed gigabyte or business transaction

---

7. Diagnose first, optimize second

The older optimization pattern often started with a command:

OPTIMIZE table_name;

The modern approach should start with evidence.

Step 1: Inspect the query profile

Look for:

• Full-table scans

• Large shuffle stages

• Skewed tasks

• Unexpected join types

• Disk spills

• Excessive file counts

• Poor predicate selectivity

• Large intermediate row counts

• Time spent waiting rather than executing

Step 2: Compare rows processed with rows returned

A query returning 100 rows after scanning 10 terabytes may have a layout or filtering problem.

A query returning billions of rows may simply require substantial resources. Not every expensive query is poorly optimized.

Step 3: Validate the data model

Check:

• Duplicate business keys

• Incorrect join grain

• Slowly changing dimension logic

• Many-to-many relationships

• Accidental Cartesian joins

• Repeated denormalization

• Unnecessary historical scans

Step 4: Check table health

Review:

• Number of files

• Average file size

• Recent merge activity

• Current clustering strategy

• Statistics coverage

• Table ownership type

• Predictive-optimization status

• Vacuum history

Step 5: Change one major variable at a time

Do not simultaneously:

• Change clustering

• Increase compute

• Rewrite the SQL

• Enable Photon

• Change caching

• Alter partition counts

If everything changes together, it becomes difficult to identify what actually helped.

Databricks query performance insights can also surface detected performance opportunities in query history. As of July 2026, the feature is documented as Beta, so teams should verify availability and workspace-preview settings before relying on it operationally.

---

8. How to benchmark optimization honestly

Performance claims should be reproducible.

Avoid statements such as:

Liquid clustering improves all queries by 45%.

The result depends on:

• Dataset size

• Data distribution

• Existing layout

• Query predicates

• Clustering keys

• Compute configuration

• Runtime

• Cache state

• Concurrency

• Write history

A sound benchmark should include:

Dataset details

• Total size

• Number of rows

• Number of files

• File-size distribution

• Cardinality of filter columns

• Degree of skew

• Update and insertion pattern

Compute details

• Databricks Runtime version

• Worker type

• Number of workers

• Autoscaling settings

• Photon configuration

• SQL warehouse type

• Cloud provider and region where relevant

Query categories

• Point lookup

• Date-range filter

• Multi-column filter

• Large aggregation

• Fact-to-dimension join

• Many-to-many join

• Incremental MERGE

• UPDATE and DELETE

Measurement approach

• Run each query multiple times.

• Separate cold-cache and warm-cache tests.

• Record median and tail latency.

• Capture bytes and files scanned.

• Capture shuffle volume.

• Record spill.

• Measure compute consumption.

• Change only one optimization variable at a time.

A useful comparison could include:

1. Unoptimized Delta table

2. Compacted table

3. Legacy Z-Ordered table

4. Liquid-clustered table

5. Photon versus non-Photon compute

6. Manually maintained versus predictively optimized table

This produces results that are useful beyond a single demonstration.

---

9. A practical 2026 decision framework

The following framework can help teams decide where to begin.

Situation Recommended starting point

New large Unity Catalog managed table	Evaluate liquid clustering and predictive optimization
Existing Z-Ordered table performing well	Keep it until measurement justifies migration
High-cardinality filter columns	Consider liquid clustering
Rapidly changing query patterns	Evaluate automatic liquid clustering
Excessive small files	Check optimized writes, auto compaction and OPTIMIZE history
External Delta table	Plan explicit maintenance where automation is unavailable
Slow join	Validate grain, filters, statistics, skew and shuffle
Expensive MERGE	Restrict source and target search ranges
Large scans despite selective filters	Check statistics, layout and data skipping
Frequent disk spill	Reduce intermediate data or review memory and compute sizing
Repeated analytical SQL	Enable or confirm Photon support
Unexplained slow SQL	Start with the query profile and performance insights

A reasonable order of operations is:

1. Confirm correctness and data grain.
2. Inspect the query profile.
3. Reduce unnecessary rows and columns.
4. Validate joins and skew.
5. Confirm statistics and data skipping.
6. Review table layout.
7. Enable platform-managed maintenance where appropriate.
8. Confirm Photon and AQE behavior.
9. Size compute for the actual workload.
10. Compare performance and cost after every major change.

---

10. What no longer belongs in the default playbook

Some practices should be reconsidered.

Applying partitioning automatically

Large table does not automatically mean partitioned table. Cardinality, distribution, and filter patterns matter more than raw size.

Applying Z-Order and liquid clustering together

They are alternative layouts, not cumulative stages.

Enabling Change Data Feed to enable clustering

Change Data Feed serves change-tracking use cases. It does not enable liquid clustering. Liquid clustering is configured through CLUSTER BY.

Running OPTIMIZE blindly every night

For managed tables, predictive optimization may make fixed schedules redundant. For other tables, maintenance should still be based on actual need.

Increasing compute before inspecting the plan

This may reduce elapsed time without correcting the underlying issue.

Creating Bloom filter indexes for new workloads

Databricks now marks Bloom filter indexes as deprecated and recommends removing existing indexes. Modern data-skipping and clustering capabilities are preferred.

Publishing benchmark numbers without execution evidence

Precise percentages create confidence only when the workload, environment, and methodology are clearly documented.

---

Conclusion

Databricks performance optimization has moved beyond a collection of isolated commands.

The earlier model depended heavily on engineers periodically running:

OPTIMIZE
ZORDER BY
VACUUM
ANALYZE

The modern model is increasingly workload-aware and automated.

Liquid clustering provides a more flexible data-layout strategy than traditional partitioning and Z-Ordering for many modern tables. Predictive optimization can automate table maintenance for eligible Unity Catalog managed tables. Photon improves the execution of scans, joins, aggregations, and shuffles. AQE changes plans based on runtime information. Query performance insights help engineers diagnose problems rather than relying on guesswork.

However, automation does not replace engineering discipline.

A poorly designed many-to-many join will still be expensive. An incorrect incremental boundary will still produce wrong results. A high-cardinality partitioning strategy will still create operational problems. A larger warehouse will still cost more money.

The most effective Databricks optimization strategy in 2026 is therefore not:

Run every optimization feature available.

It is:

Understand the workload, remove unnecessary work, use the appropriate table layout, allow the platform to automate repeatable maintenance, and measure performance together with cost.

The best-performing platform is not necessarily the one with the largest clusters or the greatest number of tuning commands.

It is the one that reads less data, moves less data, rewrites less data, and uses automation without losing observability or control.