Your Reads Are Drowning in Tiny Files

Diagnose the file shape, not just the table size

Run DESCRIBE DETAIL my_table. The command is documented to “Return information about schema, partitioning, table size, and so on.” Delta detail output includes numFiles and sizeInBytes; divide the latter by the former to estimate average file size.

DESCRIBE DETAIL my_table;
-- avg file size = sizeInBytes / numFiles

Use the result as a directional diagnosis, not a universal pass/fail threshold. Databricks' current autotune ladder targets 256 MB for tables smaller than 2.56 TB, scales linearly from 256 MB to 1 GB between 2.56 TB and 10 TB, and targets 1 GB above 10 TB. If your average is orders of magnitude below the applicable target, reads can pay per-file open and planning overhead for very little data.

Repair existing fragmentation with OPTIMIZE, but fix the write path with optimized writes and auto compaction. Otherwise every maintenance run merely resets the clock.

1. Create a clean Delta table

CREATE OR REPLACE TABLE ld_small_files (
  id  BIGINT,
  grp STRING
) USING DELTA;

2. Create sixty small appends

This notebook cell issues 60 separate, single-row INSERT statements. The figures below are from an illustrative run; file counts can differ when automatic or background compaction is active.

for i in range(1, 61):
    spark.sql(f"INSERT INTO ld_small_files VALUES ({i}, 'g{i % 4}')")

3. Measure before

DESCRIBE DETAIL ld_small_files;
SELECT COUNT(*) AS row_count FROM ld_small_files;

-- Illustrative-run output:
-- numFiles ≈ 60
-- sizeInBytes is tiny relative to 60 files
-- row_count = 60

DESCRIBE DETAIL supplies the table-size metadata; the row count protects against mistaking physical rewrite for a logical data change.

4. Bin-pack the backlog

OPTIMIZE ld_small_files;
-- Run it again: unchanged data has no additional bin-packing work.
OPTIMIZE ld_small_files;

The documented grammar is OPTIMIZE table_name [FULL] [WHERE predicate] [ZORDER BY (col_name1 [, ...] )]. This repair intentionally uses plain bin-packing. Data layout and clustering belong to Issue 003; ZORDER is only the legacy branch named by the command syntax.

5. Measure after

DESCRIBE DETAIL ld_small_files;
SELECT COUNT(*) AS row_count FROM ld_small_files;

-- Illustrative-run output:
-- numFiles = 2
-- sizeInBytes is roughly unchanged
-- row_count = 60

6. Stop fragmentation at the write path

ALTER TABLE ld_small_files SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'  = 'true'
);

Optimized writes reduce the number of files created by a write. Auto compaction then combines eligible small files after a successful write. Databricks states: “Auto compaction combines small files within table partitions to reduce small file problems. It runs synchronously on the cluster that performs the write, after the write succeeds, and only compacts files that haven't been compacted previously.”

The table property for optimized writes is autoOptimize.optimizeWrite, with session key spark.databricks.delta.optimizeWrite.enabled. Auto compaction uses table property delta.autoOptimize.autoCompact and session key spark.databricks.delta.autoCompact.enabled; spark.databricks.delta.autoCompact.maxFileSize controls maximum output size, and spark.databricks.delta.autoCompact.minNumFiles controls the trigger. For the legacy value true, the documented behavior is: “Use 128 MB as the target file size. No dynamic sizing.”

“Optimized writes are also enabled for CTAS statements and INSERT operations when using SQL warehouses. In Databricks Runtime 13.3 LTS and above, all tables registered in Unity Catalog have optimized writes enabled for CTAS statements and INSERT operations for partitioned tables.”

For manual output sizing, the SQL reference states: “OPTIMIZE ... — When using Databricks Runtime, to control the output file size, set the Spark configuration spark.databricks.delta.optimize.maxFileSize. The default value is 1073741824 (1 GB).” Superseded files are removed by VACUUM; retention semantics belong to Issue 005.

7. Verify compaction behavior

DESCRIBE DETAIL ld_small_files;
SELECT COUNT(*) AS row_count FROM ld_small_files;
DESCRIBE HISTORY ld_small_files;
-- Confirm an OPTIMIZE row whose operationParameters.auto = true.

Databricks documents the history check directly: “To confirm that auto compaction ran on a table, review the table history. Auto compaction appears as an OPTIMIZE operation with the operationParameters.auto field set to true.”

For tables larger than 1 TB, the recommendation is explicit: “For tables larger than 1 TB, Databricks recommends running OPTIMIZE on a schedule to further consolidate files.”

-- 1. numFiles vs sizeInBytes for the suspect table
DESCRIBE DETAIL my_table;
-- 2. avg file size = sizeInBytes / numFiles (256 MB target for tables < 2.56 TB)
-- 3. if avg << 256 MB: OPTIMIZE my_table;
-- 4. stop it recurring: ALTER TABLE my_table SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite'='true','delta.autoOptimize.autoCompact'='true');
-- 5. confirm: DESCRIBE HISTORY my_table → OPTIMIZE with operationParameters.auto = true

Pair it with the before/after pattern: DESCRIBE DETAIL for physical file shape and COUNT(*) for logical row parity.

Use this when

• A table receives many appends or streaming micro-batches.
• Row counts are steady while numFiles keeps growing.
• A table larger than 1 TB is not yet on a scheduled OPTIMIZE.

Avoid this when

• A Unity Catalog managed table is already under automatic file-size tuning.
• Databricks Runtime 11.3 LTS+ or SQL warehouse background compaction is active; check history before adding another schedule.
• The autotuned 256 MB–1 GB ladder applies; do not force the legacy true setting's fixed 128 MB target without a specific reason.