How to compact small files in Lakehouse: step by step

This tutorial shows how to compact small files (small files) in Lakehouse to reduce metadata overhead and improve read performance. Compacting files is useful in continuous ingestion scenarios or when small Delta files degrade queries and increase costs.

Prerequisites

• Account with access to Microsoft Fabric and permissions for the Lakehouse.
• Workspace with a Lakehouse that contains Delta tables with many small files.
• Environment to run PySpark (notebook in Fabric or Synapse/Purview compatible).
• Basic knowledge of Delta Lake and Spark SQL.

Step 1: Identify tables with many small files

It is important to know which tables have many small files. A practical way is to query the Delta metadata system to see the number of files and the total size.

-- Exemplo SQL no SQL endpoint ou notebook
DESCRIBE DETAIL delta.`/lakehouse///`;

Look for a low total size per file (averageFileSize) or a high number of files (numFiles). If averageFileSize is very small (e.g. < 10 MB), there is benefit in compacting.

Step 2: Compaction planning (repartition/partitioning)

Decide the target file size and whether you will use repartitioning by column. Common goal: files between 50 MB and 256 MB. For partitioned tables, compact by partition to avoid unnecessary shuffles.

Step 3: Compact using repartition/coalesce with PySpark

This method reads the Delta table and rewrites it with fewer files using repartition or coalesce. Repartition creates a shuffle (more precise), coalesce reduces partitions without shuffle (fast when there is already good distribution).

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Ler Delta table
df = spark.read.format("delta").load("/lakehouse///")

# Exemplo: target ~100 MB por ficheiro -> ajustar número de partições
# Calcula número de partições simples (opcional)
# num_partitions = max(1, int(total_size_bytes / (100 * 1024 * 1024)))

# Reparticionar e sobrescrever a mesma tabela (atenção à concorrência)
df.repartition(10).write.format("delta").mode("overwrite").option("dataChange", "false").save("/lakehouse///")

Note: use option("dataChange","false") when you only reorganize files without changing data to keep minimal history. Always test in a non-production environment.

Step 4: Compact by partition (when applicable)

If the table is partitioned by a column (e.g.: dt), compact each partition independently to avoid costly shuffles.

# Exemplo PySpark por partição
from pyspark.sql.functions import col
partitions = [row['partition'] for row in spark.sql("SHOW PARTITIONS delta.`/lakehouse/<...>/`").collect()]
for p in partitions:
    part_path = f"/lakehouse////dt={p}"
    df_part = spark.read.format("delta").load(part_path)
    df_part.coalesce(1).write.format("delta").mode("overwrite").option("dataChange","false").save(part_path)

Choose coalesce(1) only for small partitions; otherwise pick an appropriate number.

Step 5: Compaction using OPTIMIZE (if available)

In environments that support Delta OPTIMIZE commands (e.g. Databricks or Fabric with SQL endpoints), using OPTIMIZE is the simplest and most integrated way.

-- Exemplo SQL
OPTIMIZE delta.`/lakehouse///`
ZORDER BY (coluna_importante);

OPTIMIZE compacts files and, with ZORDER, improves data locality for column-based queries. Not all environments support ZORDER.

Step 6: Schedule and automate compaction

After the manual process, automate the task with a scheduled job (notebook job or pipeline). Run compaction outside peak hours and include checkpoints and alerts.

# Exemplo de pseudocódigo para agendamento
# 1. Verificar tabelas com small files
# 2. Para cada tabela com averageFileSize < threshold -> executar compactação
# 3. Registar resultado e métricas

Verify the result

Confirm the reduction in the number of files and the increase in average file size using DESCRIBE DETAIL or LIST in the file system. Run read queries and compare times before/after and read costs.

DESCRIBE DETAIL delta.`/lakehouse///`;
-- Ou listar ficheiros
ls /lakehouse////

Conclusion

Compacting small files in Lakehouse improves performance and reduces I/O costs. Next steps: automate with jobs, test OPTIMIZE and monitor queries. Tip: start with a test copy of the table before rewriting production — which partition or target size will make the biggest difference in your scenario?