Optimization of Delta Tables: How it saves Costs

Overview

As data volumes grow, even well designed data platforms, including the ones which use metadata driven ingestion (for example, configuration driven or metadata driven architectures commonly used in enterprise data platforms) can become slow and expensive to operate. Queries that are used for downstream (Power BI reports) tasks or other tasks, simply takes longer as storage keeps growing. This results in a slow increase of costs in the background, which when not monitored can lead to expensive consequences.

This blog introduces Delta table optimization techniques that one can use in the delta warehouse, while also explaining how to run them and most importantly, showing how to verify that the optimization actually reduces query time and thereby costs. The goal of the blog is to make a beginner confident enough to not only understand these concepts, but also deploy optimization jobs as part of a real data platform.

Please be aware that if you have Delta tables that are queried frequently or are consumed by tools like Power BI, this blog will be especially useful. Additionally in the image below, the files have been shown in various non-uniform blocks to reflect real world scenarios where the size of data is not evenly distributed.

Before and After Optimization Overview

Tabular Comparison

Optimization Concepts To Know

There are many optimization techniques that operate at the Delta table level. Some of them are Z-Ordering, Compacting smaller files to larger ones, Delta caching. In this blog, however, we will mainly consider two techniques that can be configured and implemented easily. The two optimization methods are:

1. Vacuum
2. Liquid Clustering (Optimize)

Both techniques are independent, configurable and can be designed to trigger together or separately. The simplified general flow is shown below:

Simplified Optimization Flow introducing Two techniques

Vacuum: Cleaning Up Old Data

What is Vacuum?

VACUUM is a Delta lake maintenance operation that physically removes old, unreferenced files from storage. Over time, Delta tables accumulate outdated files due to updates, deletes, and merges which are part of incremental load strategies. Vacuum simply cleans them up. Vacuum can also clean externally managed objects. For ex: Azure Databricks has Delta tables that are externally managed using data present in a storage account. Vacuum also helps clean the old and unreferenced files present in the storage account.

Why Vacuum Matters

• Frees up storage space
• Reduces storage cost, for example in, AWS Buckets or Azure Storage
• Keeps the table lean and manageable

How Vacuum works

• Runs per Delta table
• Automatically detects whether the table is managed or external
• Cleans up data, irrespective of external or managed

Minimum Configuration Options when Designing

When designing the Vacuum maintenance operation for your delta lake, it is important to have a configurable design that can work well. For that, the basic configuration options that can be added (along with individual points for each configuration):

• run_vacuum

This flag controls whether to run Vacuum or not.

Default this to TRUE so Vacuum always runs, but users have an option to disable this for a particular run

• retention_hours

Controls how long the old data is kept

Default is 168 hours (7 days)

Lower values are possible, but not recommended due to best practises and data safety.

NOTE: Reducing retention below 7 days can break time travel and recovery scenarios.

Official Documentation: Azure Databricks — Vacuum

Liquid Clustering: Faster Queries, Lower Compute Cost

The Problem with Traditional Layouts

By default, data is organized using partitioning and Z-ordering. While helpful, this approach becomes less effective as:

• Data grows continuously
• Query patterns evolve
• Tables are accessed frequently by BI tools

What is Liquid Clustering?

Liquid clustering reorganizes data by logically grouping rows based on selected columns. These columns are ideally primary keys or frequently filtered columns. This drastically improves data skipping and query performance.

Liquid Clustering via SQL

Liquid clustering can also be triggered directly using SQL commands in Databricks SQL Editor.

For example, one can run an OPTIMIZE command with CLUSTER BY on a specific table to reorganize data based on selected columns. While this is useful for experimentation or one-off optimization, it is not recommended as a long term approach, especially in production environments.

Therefore, the best practises for production grade system are:

• Optimization should be executed via scheduled or on-demand jobs
• Operations should be logged and monitored
• Configuration should be externalized (metadata or parameters)

Why Liquid Clustering Matters?

• Queries scan less data
• Execution time drops significantly
• Compute usage decreases → direct cost savings

In practise, we have observed (across client engagements):

• 50%+ query time reduction
• In some cases, upto 400% improvement in performance

when we implemented this solution for our clients.

Minimum Configuration Options when Designing

• run_optimize

This flag, just like run_vacuum, presents an option to the user.

Default is TRUE.

• cluster_columns

Important to provide the user with the columns they want to cluster by.

Default would be primary keys.

NOTE: Defaulting to primary keys if not configured, should be designed by the user as this is not the default behavior of the feature.

One important point to remember is that there is a maximum of 4 clustering columns possible per table (As designed by Databricks when this article was written). If the primary keys exceeds that, this needs to be handled. Either one could simply skip optimization or can prioritize the primary keys based on a logic. Please refer to the original Azure Databricks — Liquid Clustering documentation for more info.

Designing the Optimization Job

Now that we have discussed two kinds of optimization techniques that can be used, let us think about how can we design such a job. For the job, we should ideally have it configurable using some of the options shown in the previous sections. Additionally, we can also configure the job to handle different options, so the user has an option to trigger the optimization:

• per source level
• per object level
• to run for all sources and all objects.

It is also advised to schedule the optimization job to run for all sources and all objects periodically (weekly, bi-weekly or monthly). This needs to be scheduled when no other jobs are being run, especially in production, as when running the OPTIMIZE command, there will be rewrites and we do not want other operations to also be accessing and writing to the same delta table. This is because OPTIMIZE operations rewrite data files, and concurrent write operations may lead to unnecessary conflicts or performance degradation

Basic Skeleton of Job Orchestration using ADF as example

Verifying Optimization Actually Worked

Optimization is only valuable if it produces measurable improvements. This is the reason why it is important to verify if this optimization operation did add value. Some of the other reasons why verification matters include:

• Confirming if the Jobs ran successfully
• Proves query performance improvements
• Helps justify cost savings to stakeholders

This would also be clearly differentiated if we compare a scenario where we have an optimized table vs a non-optimized table. Considering Power BI refreshes, which typically reads data from multiple delta tables, this would accumulatively add a lot of value showcasing the use of Optimization.

How to Verify Optimization in Databricks

1. Open Databricks
2. Navigate to SQL Editor shown in the left pane.
3. Run the following commands:

DESCRIBE HISTORY <catalog>.<schema>.<table>;

-- Additional Verification methods/commands

-- The below command provides table level metadata such as: Number of files, table size, partitioning and clustering info
DESCRIBE DETAIL <catalog>.<schema>.<table>;

-- The below command can be used to inspect table properties related to Delta features, including optimization-related metadata
SHOW TBLPROPERTIES <catalog>.<schema>.<table>;

-- Below command verifies the effect of Vacuum. Compare this before and after Vacuum
-- Typically the total and active files should reduce after Vacuum
SELECT count(*) as active_files FROM delta.`<table_path>`;
SELECT count(*) as total_files FROM delta.`<table_path>` WHERE _metadata.file_size IS NOT NULL;

-- Below command is to validate time travel behavior
-- Delta Lake allows querying older versions of a table using VERSION AS OF or TIMESTAMP AS OF.
-- After Vacuum has run with a retention period, older versions beyond that retentions are no longer available.
SELECT * FROM <catalog>.<schema>.<table> VERSION AS OF xxx LIMIT 10; -- xxx is an older version
-- The command should give a result that indicates an error that the version is no longer available 

What to Look For

In the table history, you should see entries for:

• VACUUM
• OPTIMIZE

These confirm that the optimization jobs have executed successfully. After Optimization, you should typically observe fewer files and a more compact layout (Ex: In storage account).

Final Thoughts

Delta table optimization is not just a technical improvement, it’s a cost optimization strategy. By combining Vacuum and Liquid Clustering, one can ensure:

• Faster queries
• Lower compute usage
• Reduced storage costs
• Better BI performance

In cloud-based data platforms, reduced query runtime directly translates to lower compute consumption, making optimization a tangible cost control mechanism rather than just a performance enhancement. For beginners, this approach demonstrates how metadata driven automation can turn best practises into repeatable, production ready jobs.

References

1. Azure Databricks — Vacuum
2. Azure Databricks — Liquid Clustering documentation
3. How Liquid Clustering Actually Works in Databricks