Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Many small files in a Delta table

Snowflake, BigQuery & Databricks · Databricks

Many small files in a Delta table

Mediumwarehouses-52
scenariodatabricksdelta-lakesmall-filesoptimizestreaming

Question

A Delta table fed by a streaming job has millions of tiny files and queries are slow. What do you do?

Solution

A streaming job that writes every few seconds creates a new small file in every micro-batch, so after months a Delta table can have millions of tiny files. Each query then spends its time listing and opening files instead of reading data. The fix is to compact the files now, and to stop creating so many in the future.

Confirm the diagnosis

DESCRIBE DETAIL sales.events;

It shows numFiles and sizeInBytes. If a 200 GB table has 4 million files, the average file is about 50 KB, far from a healthy size of hundreds of MB. In the Spark UI, scans with huge numbers of tasks and short task times point the same way.

Fix the existing files

OPTIMIZE sales.events;

On a very large table, optimize in slices, such as a recent date range, so it finishes in reasonable time. Then run VACUUM to delete the old, now unreferenced files after the retention period. If the table is a Unity Catalog managed table, enabling predictive optimization will do this regularly without your jobs.

Prevent it

  • Turn on optimized writes and auto compaction on the table (delta.autoOptimize.optimizeWrite and delta.autoOptimize.autoCompact), so the writer produces fewer, larger files and small ones are merged after writes.
  • Increase the trigger interval. Writing every 5 minutes instead of every 5 seconds produces 60 times fewer files. Ask whether the business needs second-level freshness.
  • Reduce the number of write partitions before writing, if each micro-batch has few rows.
  • Avoid over-partitioning. Partitioning by hour and by a high-cardinality column multiplies files. Prefer liquid clustering.

Why not just run OPTIMIZE more

Compaction rewrites data, which costs compute, and concurrent writers can conflict with it in some cases. It is a repair, and prevention at write time is cheaper.

Order of the answer

First diagnose with DESCRIBE DETAIL. Then compact. Then change the writer settings and trigger so it does not return. Finally add a monitor on average file size or file count, so it does not creep back unnoticed.

PreviousNext