A table written by a streaming job has become unusably slow. It holds 400 GB across 8 million files. Diagnose and fix.

What the interviewer is testing

Whether you recognise the small file problem and understand why the cost is per file rather than per byte.

The diagnosis

400 GB is unremarkable. 8 million files is the problem. Average file size is 50 KB, which is far below the range object storage and columnar formats perform in.

The cost is per file: each requires listing, opening, reading a footer and planning, and on object storage each is a network round trip of tens of milliseconds. Query planning alone — before any data is read — may take longer than the query should. Metadata operations degrade first and worst, and eventually planning times out.

The cause is almost certainly a streaming job committing every micro-batch, multiplied across partitions: a job committing every minute across 20 partitions produces 28,800 files a day.

The fix

Immediately: compaction. Rewrite into files in the 128 MB to 1 GB range. Open table formats support this as a maintenance operation, including while queries continue. Expect a dramatic improvement — often an order of magnitude.

Then prevent recurrence, which is the part that matters:

• Increase the commit interval so each write produces a reasonably sized file, accepting the latency. Committing every 5 minutes rather than every minute reduces file count fivefold.
• Reduce partition fanout. If each micro-batch writes to 20 partitions, each commit creates 20 files. Partitioning by hour rather than by minute, or repartitioning before write, concentrates output.
• Schedule ongoing compaction as a maintenance job with an owner, plus snapshot expiry, since metadata and old snapshots also accumulate.

What a strong answer adds

The explicit trade being made: commit interval is a direct exchange of freshness for query performance, and it should be set from the actual freshness requirement rather than from "as fast as possible". Many streaming tables serve dashboards refreshed hourly.

Common weak answers

Adding compute, which does not help when the bottleneck is metadata and round trips. Compacting once without fixing the writer.