Case Study 1: Twelve Million Files

"The data was 341 gigabytes. Reading it took 71 minutes. Nothing was wrong with the disk."

Executive Summary

Kestrel's clickstream consumer wrote to object storage every 30 seconds across twelve Kafka partitions. After fourteen months this had produced roughly 14.7 million Parquet files averaging 27 kilobytes each, holding about 400 gigabytes of data.

A full-history query — the kind the data science team runs quarterly — took 71 minutes, of which about 62 were spent issuing and waiting on GET requests. The bytes could have been read in under a minute.

This case study covers the diagnosis, the compaction job, and — the part worth studying — the three things that went wrong the first time they tried to fix it. Compaction is conceptually trivial and operationally full of edges.

Skills applied: the small-files arithmetic (§9.6); Parquet structure and statistics (§9.2); atomic swaps and the _SUCCESS marker (§9.1, §9.3); sorting for compression and pruning (Chapter 8 §8.2, §8.3).

Background

The consumer, as written in Chapter 15's increment. A Kafka consumer reading kestrel.clickstream.v1, buffering, and writing a Parquet file on whichever came first: 30 seconds elapsed or 50,000 records buffered.

The 30-second interval was chosen for a good reason — freshness. Data is queryable within half a minute of arriving, which mattered when the streaming path was built.

At 162 events per second average, 30 seconds buffers about 4,860 events. Never close to the 50,000 record trigger. So the time trigger fired every time, and the file size was determined entirely by the arrival rate.

$$\frac{86{,}400 \text{ s/day}}{30 \text{ s}} \times 12 \text{ partitions} = 34{,}560 \text{ files/day}$$

Over 425 days: 14.7 million files.

The Problem

The data science team's quarterly feature extract reads the full clickstream history. In early 2025 it took 71 minutes. A year earlier the same query on a third of the data had taken 9 minutes.

The scaling was worse than linear — 3× the data, 7.9× the time — which is the signature that the cost is per-file rather than per-byte.

The diagnosis took under an hour, once someone counted files rather than bytes:

import boto3
paginator = boto3.client("s3").get_paginator("list_objects_v2")
n, total = 0, 0
for page in paginator.paginate(Bucket="kestrel-bronze", Prefix="events/v1/"):
    for obj in page.get("Contents", []):
        n += 1
        total += obj["Size"]
print(f"{n:,} objects, {total/1e9:.1f} GB, avg {total/n/1e3:.1f} KB")

14,688,000 objects, 399.4 GB, avg 27.2 KB

🔎 Read the Plan — Count files before you profile anything

The query profile showed the scan taking 62 of 71 minutes and did not say why. It reported bytes read, rows produced, and time — all of which looked proportionate. Nothing in the profile said "you are issuing 14.7 million requests."

For any object-storage dataset, the four numbers to know are: object count, total size, average size, and partition count. Three lines of Python, and they diagnose a whole class of problem that no query profile surfaces directly.

The tell in this case was the superlinear scaling: 3× the data, 7.9× the time. Anything worse than linear in a scan means the cost is per-something-else — per file, per partition, per request — and counting is how you find out which.

Kestrel now runs this as a scheduled report over every bronze prefix. It is the lake_audit.py from this chapter's Part D.

The arithmetic

At 30 ms per GET and 100-way parallelism:

$$\frac{14{,}688{,}000 \times 0.03 \text{ s}}{100} = 4{,}406 \text{ s} = 73 \text{ minutes}$$

which is the observed 71 minutes to within measurement error. The whole runtime was request latency, and the bytes were incidental.

Request cost, at the frozen $0.0004 per 1,000:

$$14{,}688{,}000 \times \frac{\$0.0004}{1{,}000} = \$5.88 \text{ per full scan}$$

Trivial, and it is the number people reach for. The 71 minutes is the cost, and it is not on any invoice.

The Analysis

Before writing the compaction job, the team measured what it would buy. They compacted one day's partition by hand.

Two results were larger than expected and both are worth understanding.

The 36% size reduction from compaction alone. No data was removed. The saving is entirely compression: a 27 KB file has almost nothing for a compressor to exploit, while a 151 MB file sorted by session_id and event_ts has enormous amounts of repetition available. Chapter 8 §8.3's encodings need volume to work.

The 97× improvement on the selective query. This is pruning, and it only appeared because they sorted. In the unsorted files, every one of the 34,560 files could contain any session_id, so every footer had to be read. In the sorted output, session_id min/max statistics are selective, so three of four files are skipped entirely on their footer.

Sorting was not part of the original plan. It was added after someone asked what the row group statistics would look like, and it produced more of the improvement than the compaction did.

The Decision

A nightly compaction job. Which is where it got interesting, because the first three versions were all wrong.

Attempt 1 — delete then write

# WRONG. Do not do this.
delete_objects(prefix)
write_compacted(rows, prefix)

It worked in testing. In production it ran while a data scientist was querying the partition, and they got a partial result with no error — a subset of the rows, silently.

This is the failure the entire chapter is about. There is a window between the delete and the write in which the partition is empty or partial, object storage has no transaction to hide it, and a reader gets fewer rows and does not know.

Attempt 2 — write to a temp prefix, then swap

# Better. Still wrong.
write_compacted(rows, tmp_prefix)
delete_objects(final_prefix)
copy_objects(tmp_prefix, final_prefix)     # 34,560 copies... no, 4 copies
delete_objects(tmp_prefix)

Better — the window is now four copies rather than a full write — and the window still exists. Between the delete and the copies, the partition is empty.

More seriously, it failed once: the copy step errored on object three of four, leaving the partition with two of four files. No error surfaced to any reader. The partition was silently missing half its rows for eleven hours, until the morning's row-count check caught it.

⚠️ Failure Mode — There is no atomic multi-object operation

Attempts 1 and 2 are both attempts to make a multi-object change look atomic on a store that offers atomicity only per object. It cannot be done. Every protocol you can build on plain object storage has a window.

What you can do is make the window small, make it detectable, and make failure loud:

• Marker last. Write all data files, then write _SUCCESS. A reader that requires _SUCCESS never sees a partial write in progress. It does not protect against a replacement, where the marker already exists from the previous version.
• Version the prefix. Write to partition/v2/, then flip a pointer object naming the current version. The flip is a single-object write, which is atomic. This is the pattern that works, and it is a hand-rolled, worse version of a transaction log.
• Never delete before writing. Old files cost storage; a partial partition costs correctness.

Or use a table format, which is Chapter 10, and which is what Kestrel ended up doing. The whole of §10.3 is a properly engineered version of the pointer flip above, with the edges handled.

Attempt 3 — version the prefix and flip a pointer

def compact(partition: str, target_mb: int = 256) -> None:
    src     = f"events/v1/{partition}/current"
    version = f"v{int(time.time())}"
    dest    = f"events/v1/{partition}/{version}"

    n_before = count_rows(src)
    write_compacted(read(src), dest, target_mb=target_mb,
                    sort_by=["session_id", "event_ts"])
    n_after = count_rows(dest)

    # Verify BEFORE flipping. A compaction that loses rows and flips anyway is
    # strictly worse than no compaction, because it destroys the evidence.
    if n_after != n_before:
        raise CompactionError(
            f"{partition}: {n_before:,} rows in, {n_after:,} out")

    # ONE object write. This is the only atomic operation available, and the
    # whole design is arranged around it.
    put_object(f"events/v1/{partition}/_CURRENT", version)

    # Old versions expire by lifecycle policy after 7 days -- not deleted here,
    # so an in-flight reader on the old version is unaffected.

Three properties that made this the version that shipped:

The row count is verified before the flip. A compaction that silently loses rows is worse than no compaction, because it destroys the input.

The flip is one object write, which is atomic. Readers see either the old version or the new one, never a mixture.

Old versions are not deleted, only expired by lifecycle policy after seven days. A reader that resolved _CURRENT before the flip keeps reading a complete, consistent set of files.

What Happened

The job ran over the backlog across four nights, compacting oldest-first.

19.4× faster, and 37.9% smaller with no data removed.

Three follow-on effects:

The storage saving was not the point and paid for the work anyway. $3.48 a month is nothing. The 67 minutes returned to a quarterly job, and the removal of a whole class of "why is this slow" investigation, is the return.

The consumer's commit interval was left at 30 seconds. This was debated and the two-tier pattern (§9.6's 📏 note) won: small files for freshness, nightly compaction for history. Raising the interval to five minutes would have reduced the file count tenfold and delayed queryability tenfold, and the streaming path exists precisely to be fresh.

The _CURRENT pointer became a maintenance burden, and this is the honest part. Every reader had to be taught to resolve it. Three tools could not — a BI connector, a notebook someone had written, and an external partner's export — and each needed a compatibility view. That friction is what pushed the team to Delta Lake nine months later (Chapter 10), where the same guarantee is provided by the format and every engine understands it.

Lessons

1. Count files before you profile. Object count, total size, average size, partition count. Three lines, and they diagnose a class of problem no query profile surfaces.

2. Superlinear scaling in a scan means the cost is per-something-else. 3× data, 7.9× time.

3. The latency is the cost, not the request charges. $5.88 against 71 minutes. Only one of those appears on an invoice.

4. Sorting during compaction produced more improvement than compaction did. 36% smaller and 97× faster on selective queries, from a clause that costs nothing at write time.

5. There is no atomic multi-object operation. Every protocol on plain object storage has a window. Make it small, detectable, and loud.

6. Never delete before writing. Old files cost storage; a partial partition costs correctness.

7. Verify the row count before the flip. A compaction that loses rows and commits anyway destroys its own input.

8. A hand-rolled pointer protocol works and creates friction with every tool that does not know about it. That friction is the argument for a table format.

Questions for Discussion

1. The 30-second commit interval was chosen for freshness, which was a genuine requirement. At what point should the team have anticipated the file-count consequence? Is there a design review question that would have surfaced it?

2. Attempt 2 failed and was undetected for eleven hours until a row-count check caught it. Design the check that would have caught it in minutes. What does it cost to run?

3. Sorting produced most of the benefit and was an afterthought. What other "free at write time" decisions in this book have outsized effects on read cost? Make a list.

4. The team kept the 30-second interval and added nightly compaction. Argue for raising the interval to five minutes instead. What would you need to know about the consumers to decide?

5. Three tools could not resolve the _CURRENT pointer and needed compatibility views. Estimate the ongoing cost of that. At what number of incompatible readers does a hand-rolled protocol stop being viable?

6. The storage saving was $3.48 a month and the time saving was 67 minutes a quarter. How would you present this work's value to a manager? Which number leads?

7. Old versions expire after seven days rather than being deleted at flip time. What does seven days buy, and what would make you choose a different number?