How to compact and optimize Delta files in Databricks: step by step

João Barros 28 de September de 2026 4 min read

Learn how to compact and optimize Delta files in Databricks to reduce small files, improve I/O and speed up queries. This task is useful when incremental loads create many small files that harm read performance and processing cost.

Prerequisites

Databricks account with an active workspace and cluster (Scala/Python supported).
Existing Delta table or Delta directory in DBFS with multiple small files.
Read/write permissions on the Delta table or DBFS path.

Step 1: Diagnose the small files problem

Before optimizing, it is useful to confirm that there are many small files and know the average size. This helps decide the compaction strategy and the target size.

# Exemplo em PySpark num notebook Databricks
from pathlib import Path
from pyspark.sql.functions import col, input_file_name

path = '/mnt/data/delta/my_table'  # ou o caminho da tabela Delta
file_info = dbutils.fs.ls(path)
# Mostrar contagem e tamanhos
len_files = len(file_info)
sizes = [f.size for f in file_info if f.size is not None]
print(f"Ficheiros: {len_files}, tamanho médio: {sum(sizes)/len(sizes) if sizes else 0} bytes")

Step 2: Use OPTIMIZE to compact Delta files

The OPTIMIZE command groups small files into larger files per partition. It is simple and recommended for Delta tables managed in Databricks. It can be run in SQL in the notebook or via spark.sql().

-- Exemplo SQL num notebook Databricks
OPTIMIZE delta.`/mnt/data/delta/my_table`;

-- Ou para uma tabela registada
OPTIMIZE my_database.my_table;

Step 3: Use ZORDER for faster queries on specific columns

ZORDER physically reorganizes files to improve colocation of values in a column (useful for filters by date, id, geography). It is typically used after OPTIMIZE or together with it.

-- Exemplo: optimizar por coluna de lookup 'customer_id'
OPTIMIZE my_database.my_table
ZORDER BY (customer_id);

-- Em Python, via spark.sql
spark.sql("OPTIMIZE my_database.my_table ZORDER BY (customer_id)")

Step 4: Optimize by appropriate partitioning and avoid unnecessary repartition

Partitioning by columns that reduce the amount of data read is important. If your table is not partitioned and the data is large, consider rewriting to add partitioning. Excessive repartitioning can create many small files again — use coalesce when reducing partitions.

# Reescrever adicionando partição por 'year' e 'month' (exemplo mínimo)
df = spark.read.format("delta").load("/mnt/data/delta/my_table")
df_with_parts = df.withColumn('year', year(col('date'))).withColumn('month', month(col('date')))
df_with_parts.write.format("delta").mode("overwrite").partitionBy('year','month').save('/mnt/data/delta/my_table_partitioned')

# Para reduzir ficheiros sem mudar partições: coalesce antes de write
small_df = df.repartition(10)  # escolher número adequado
small_df.write.format("delta").mode("overwrite").option("overwriteSchema","true").save('/mnt/data/delta/my_table_compacted')

Step 5: Automate with Workflows and avoid production impact

Running OPTIMIZE/ZORDER during controlled windows reduces impact. Use Databricks Workflows to schedule maintenance and apply policies (e.g., only optimize partitions with many files). It is also possible to limit the scan with WHERE to optimize specific partitions.

-- Optimizar só uma partição (por exemplo year=2025)
OPTIMIZE my_database.my_table WHERE year = 2025 ZORDER BY (customer_id);

Verify the result

Confirm that the number of files decreased and the average size increased. Measure query time before and after using a representative query. Also check cluster metrics (I/O and read time).

# Contar ficheiros após OPTIMIZE
file_info_after = dbutils.fs.ls('/mnt/data/delta/my_table')
print(f"Ficheiros depois: {len(file_info_after)}")

# Teste de consulta rápido
%sql
SELECT count(*) FROM my_database.my_table WHERE customer_id = 12345;

Conclusion

Compacting and optimizing Delta files in Databricks reduces small files, improves I/O and speeds up queries. Next steps: schedule OPTIMIZE with Databricks Workflows, experiment with ZORDER on common filter columns and monitor latency. Tip: start by optimizing hotspot partitions and measure gains before applying globally — which partition is most critical in your case?