Delta Table Maintenance Myths: Are You Still Running Unnecessary Jobs?

Recently, I saw a post on LinkedIn where hundreds of people liked some advice on Delta Table maintenance. You would ask what is wrong with it? The story here is short: the post contained advice that was accurate a few years ago, or is still valid for companies using legacy Hive or external tables. However, the author did not mention this important context. This is often a drawback of AI-generated posts, where LLMs sometimes forget to include critical details.

In this post, I decided to tackle the most common myths around Delta Table maintenance. Generally speaking, many old problems have been solved by new runtimes and new features in Databricks. That said, I think it is still worth understanding these processes so you can avoid unnecessary costs from running maintenance jobs that are no longer needed.

Myth 1: Small Files

Small files can degrade performance, but they’re not always the silent killer. While a table with 50,000 tiny files may scan significantly slower than the same data compacted into a few hundred well-sized files. Run daily or after large batch loads.

Right now, we have Auto Compaction. Auto Compaction combines small files within table partitions to automatically reduce small file problems. It runs after a write to a table has succeeded and executes synchronously on the cluster that performed the write. It also only compacts files that have not been compacted before.

When you insert new rows, the enabled-by-default Auto Compaction will trigger the OPTIMIZE command under the hood to improve file sizes. You can test this yourself by adding rows one by one to a table and then running the DESCRIBE HISTORY command. You will notice that from time to time, an OPTIMIZE command is executed automatically.

The result of auto compaction can be seen on the screen below.

When should you still run OPTIMIZE?

According to documentation for tables that aren’t managed tables in Unity Catalog, because managed tables are optimised by Predictive Optimalization.

Auto compaction and optimized writes each reduce small file problems, but are not a full replacement for OPTIMIZE. Especially for tables larger than 1 TB, Databricks recommends running OPTIMIZE on a schedule to further consolidate files.

Myth 𝟮. 𝗩𝗔𝗖𝗨𝗨𝗠

Each UPDATE and MERGE operation generates new files while retaining the old ones to support time travel. If VACUUM is not run, storage usage will continue increasing indefinitely. Schedule it to run weekly with a suitable retention period.

After November 11, 2024, Predictive Optimization is enabled by default in your account. This means that for managed tables, Predictive Optimization will assess the benefit of maintenance and apply it when required. In practice, this means the VACUUM command is executed automatically on your tables and you do not need to run it manually.

To track the results of Predictive Optimization, you can use the system table below:

SELECT * FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = 'VACUUM'
ORDER BY operation_timestamp DESC;

Myth 𝟯. 𝗔𝗡𝗔𝗟𝗬𝗭𝗘 𝗧𝗔𝗕𝗟𝗘

Outdated column statistics can cause the query optimizer to choose inefficient join strategies and scan more data than necessary. Many teams overlook this maintenance step and then question why performance degrades. Make sure to run it after substantial data changes.

This process is also automated for managed tables in Unity Catalog by Predictive Optimization. The background process runs the ANALYZE command on your table automatically. The results of this action can again be found in the system table:

SELECT * FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = 'ANALYZE'
ORDER BY operation_timestamp DESC;

Myth 𝟰. 𝗟𝗶𝗾𝘂𝗶𝗱 𝗖𝗹𝘂𝘀𝘁𝗲𝗿𝗶𝗻𝗴

Without clustering, a query that filters by customer_id may still end up scanning most of the table because the data is spread across many files. Liquid clustering groups related data together, allowing the engine to skip irrelevant files entirely. Enable it on the columns most frequently used in filters.

Delta format already stores min and max values for column statistics for Parquet files in a table. If you do not use Liquid Clustering, Databricks will use these statistics from the transaction log to skip files. Liquid Clustering takes this further by improving file layout using a Hilbert Curve. This combination significantly improves file skipping during a table scan. Moreover, because of ZCube, Liquid Clustering is incremental, which means you do not need to reprocess the entire table each time you modify data.

Liquid Clustering is considered a modern alternative to partitioning. With traditional partitions, data is stored in separate physical folders on storage, which means changing a partition key requires recreating files and can result in small file problems. Liquid Clustering avoids these issues. That said, in certain scenarios, partitioning is still beneficial, for example, when you need to replace an entire partition.

Databricks also has a feature called Auto Clustering, where Predictive Optimization selects keys for Liquid Clustering automatically based on table and column usage. In short, if a column is frequently used in filters, Predictive Optimization will select it as a cluster key without you needing to configure anything.

Myth 5: Optimized Writes

Optimized Write is a Delta Lake feature designed to minimize the number of files created when writing to a Delta table, helping prevent the “small file problem.” When many small files are produced during write operations or accumulate over time distributed processing engines like Spark can experience performance slowdowns due to the overhead of reading and managing so many files. Optimized Write addresses this by consolidating data into fewer, larger files, improving overall efficiency.

Optimized Writes are enabled by default for the following operations in Databricks Runtime 9.1 LTS and above:

• MERGE
• UPDATEwith subqueries
• DELETE with subqueries
• CTASstatements and INSERToperations when using SQL warehouses

Myth 6: ZORDER

ZORDER is still the best way to improve query performance.

ZORDER works by co-locating related data within files when you run OPTIMIZE, which improves file skipping during queries. The problem is that this only applies to the files that exist at the time OPTIMIZE runs. When you add new records after that, the new files are not ordered, and the benefit gradually decreases until you run OPTIMIZE again. This means you are dependent on a regular maintenance job to keep performance consistent.

Liquid Clustering solves this differently. Because of ZCube, it works incrementally, which means new data is clustered as part of the write process without requiring a full table optimisation. You do not need to schedule anything to keep the clustering effective over time.

If you have existing tables using ZORDER, it is worth planning a migration to Liquid Clustering. You cannot use both at the same time on the same table, but the process of switching is straightforward: drop the existing ZORDER definition and enable Liquid Clustering with your chosen keys.

Old Advice vs. What Is Automated Today

Before you build any maintenance pipeline, it is worth knowing what Databricks already handles for you. Here is a quick overview:

When These Features Do Not Apply

It is important to understand that the automation described in this post applies specifically to managed tables in Unity Catalog. If your environment uses any of the following, the old manual approach is still the right one:

External tables. Predictive Optimization does not manage external tables. You are still responsible for running VACUUM, OPTIMIZE, and ANALYZE manually on these.

Legacy Hive metastore tables. Tables registered in the legacy Hive metastore are not covered by Predictive Optimization. If your workspace has not been migrated to Unity Catalog, you need to keep your maintenance jobs running.

Tables with Predictive Optimization explicitly disabled. If someone on your team turned it off at the catalog, schema, or table level, automation will not run.

The Hidden Cost of Redundant Maintenance Jobs

This is something not many people talk about. Running OPTIMIZE and VACUUM jobs on tables that are already managed by Predictive Optimization does not cause errors, so teams often leave them in place without realising they are paying for duplicate work.

To understand the cost, consider a simple example. Suppose you have a daily OPTIMIZE job that runs on a cluster with 4 workers for 30 minutes. On Databricks. Running this every day generates DBU consumption for each table. If you have 20 tables with similar jobs, that is a meaningful amount of wasted spend.

The fix is straightforward. Query the Predictive Optimization history to confirm that your tables are being maintained automatically, then remove the redundant jobs. This is one of the easiest cost optimisations you can make in a Databricks environment.

Summary

Part of the maintenance advice that circulates on LinkedIn today is outdated for teams using managed Unity Catalog tables on modern Databricks runtimes. Before building a maintenance pipeline, it is worth checking:

• Is it a managed Unity Catalog table? If yes, Predictive Optimization is likely already handling VACUUM and ANALYZE.
• Are you on DBR 9.1 LTS or above? If yes, Optimized Writes and Auto Compaction are already active by default.

The cases where manual maintenance still makes sense are external tables, legacy Hive metastore tables, and any environment where these defaults have been explicitly disabled. If you are in one of those situations, the older advice still applies and is worth following.

Knowing which world you are in is the most important step.

If you found this article insightful, please click the ‘clap’ button and follow me on Medium and LinkedIn. For any questions or advice, feel free to reach out to me on LinkedIn.