Fix 2000x1MB Parquet files via compaction and prevent with batched writes / Delta auto-optimize.
system-designlakehousesmall-filescompaction
Start here
The problem in plain words
Imagine a shop that keeps its sales records in a cupboard. Every minute, a clerk writes the last minute of sales on a fresh sheet of paper and drops it in the cupboard. After a few weeks there are thousands of sheets, each with a handful of lines.
Now your manager asks, "What did we sell last week?" You have to pull out every sheet, read the top, find the lines you need, and put it back. Reading the numbers takes seconds. Finding and opening thousands of sheets takes most of the afternoon.
That is the small files problem. A data system that saves its data in many tiny files spends more time opening files than reading them. The fix is simple to say: every so often, gather the small sheets and copy them onto a few big pages. Then change how the clerk writes, so tiny sheets stop piling up in the first place.
Words you will meet
File
One saved chunk of data in storage, like a spreadsheet saved on your laptop. Big data tables are stored as many files in one folder.
Parquet
A common file format for tables of data. It stores each column together, so a query can read only the columns it needs. Each Parquet file also has a small footer that describes what is inside.
Lakehouse
A set-up where the data sits as plain files in cheap storage (a data lake), with a table layer on top that lets you query it like a database table.
Streaming write
Data arrives all day long, and the job saves it every few seconds or minutes instead of once a day. Each save creates new files.
Listing
Asking the storage service, "what files are in this folder?" Storage returns the names a page at a time, so a folder with many files needs many requests.
Query planning
The work a query engine does before reading any data: it lists the files, opens their footers and decides what to read. With many files this step becomes slow.
Compaction
Copying many small files into a few large ones, then retiring the small ones. The data stays the same, only the packaging changes.
Partition
A sub-folder that groups data, usually by date, like one cupboard shelf per day. A query for one day only looks at that day's shelf.
How tiny files pile up, and why queries get slow
Press Play to watch it move
Each arrow is one thing happening. Play it step by step, or read the list below.
1.New events keep arriving all day.
2.Every few seconds the job saves what it has so far. Each save becomes one new small file, about 1 MB.
3.A query starts. The engine lists the folder and has to open all 2,000 files, even though the data is only about 2 GB in total.
4.Most of the time went into opening files, not reading numbers. The analyst waits for a simple question.
The numbers, as things you can picture
2,000 files of 1 MBThat is about 2 GB of data, roughly a few hundred phone photos. A good engine should read that in seconds. Instead it makes 2,000 separate trips.
About 20 files of 100 MBThe same 2 GB, packed into big files. Twenty trips instead of 2,000. This is the target of the fix.
What the interviewer wants
Explain why many small files make queries slow, show how to merge them safely while people keep querying, and describe how to stop the problem from coming back.
Interview room
2000 small 1MB Parquet files after streaming writes: fix via compaction and prevent via batching/auto-optimize.
Interviewers reward incident-first teaching, a clear control loop, and named owners. Tool logos without that loop score poorly.
Weak vs strong answers on Small Files Problem in Lakehouse
Weak: list products. Strong: tell the failure story, define the mechanism, draw the path, state rollout and monitors.
Scope control
Say what is out of scope for v1. Come back only if follow-ups demand it. This is a seniority signal for Small Files Problem in Lakehouse.
Clarifying then committing
Ask a few high-use questions about SLAs, owners, and scale, then sketch. Endless questions without a diagram look evasive.
Target size as a monitored SLO
Track avg file size and files per partition. Alert when a daily partition exceeds a file-count budget. File metrics belong next to lag metrics.
Sealed vs open partitions
Compact yesterday aggressively. Treat the open streaming partition gently to reduce commit conflicts. Say this split schedule.
Vacuum discipline
Compaction without vacuum/retention cleanup burns storage. Pair rewrite jobs with expiration policy.
Interview framing detail 1
For lb-sd-33, spend the first minutes making the problem concrete: who gets hurt when this fails, what SLA is implied, and what is explicitly out of scope. Then teach the prerequisite concept before proposing boxes. A candidate who names vendors first usually loses the plot. Write two clarifying questions on the board and answer them with assumptions if the interviewer shrugs. Keep the first diagram small enough to redraw when a follow-up changes a constraint.