Case Study 1: The Benchmark That Flattered the Format

"We projected 4.2 terabytes and provisioned for it. The first month landed 9.7."

Executive Summary

A team migrating a clickstream from gzipped JSON to Parquet benchmarked the change, measured a 21× compression ratio, projected annual storage, sized a budget, and got approval.

The production ratio was 9.1×. Storage came in at 2.3× the projection, the first quarter exceeded its annual budget line, and a cost review three months in produced an uncomfortable meeting.

Nothing was dishonest and the benchmark was carefully run. It was run on a sample selected in a way that made the data far more compressible than production — and the mechanism was subtle enough that a second engineer reviewed the benchmark and did not catch it.

This case study is §11.6's ⚠️ callout as a lived event. It is included because the failure is extremely common, it is invisible to review, and the fix is a checklist rather than a skill.

Skills applied: measuring rather than asserting (§11.6); cardinality's effect on compression (§11.3, §11.6); benchmark honesty (Chapter 8, Case Study 2); frozen figures and derivation (Chapter 1 §1.8).

Background

The migration. A clickstream of about 11 million events a day, landing as gzipped JSON Lines. Queries were slow (Chapter 11 §11.7's problem) and the team proposed converting bronze to Parquet.

The benchmark, which is what a competent engineer would write:

# benchmark_formats.py -- reviewed and approved
sample = fetch_events(date="2025-06-14", limit=500_000)

write_jsonl_gz(sample, "sample.jsonl.gz")
write_parquet(sample, "sample.parquet", compression="zstd")

print(f"gzip JSON : {size('sample.jsonl.gz')/1e6:.1f} MB")
print(f"parquet   : {size('sample.parquet')/1e6:.1f} MB")
print(f"ratio     : {size('sample.jsonl.gz')/size('sample.parquet'):.1f}x")

gzip JSON : 214.8 MB
parquet   :  10.2 MB
ratio     :  21.1x

The projection, and the arithmetic is correct given the input:

$$11{,}000{,}000 \times 365 \times \frac{214.8 \text{ MB}}{500{,}000} \div 21.1 = 4.2 \text{ TB/year}$$

At the frozen S3 rate, $96.60 a month. Approved without discussion, because $96 a month is not a decision.

The Problem

The first full month landed 802 GB, against a projected 350 GB. Extrapolated: 9.7 TB/year, not 4.2.

The realized ratio was 9.1×, not 21.1×.

$96.60 a month became $223.10 — still small in absolute terms, and it is not the money that made this an incident. The problem was that the storage projection had been used to size the retention policy, which had been set at three years on the strength of the 4.2 TB number. At 9.7 TB/year, a three-year retention is 29 TB, and the compaction, scan, and lifecycle-transition costs that scale with it were all projected from the wrong base.

⚠️ Failure Mode — A wrong projection propagates into every downstream decision

The direct cost of the error was $126 a month. The consequential cost was larger and arrived later, because the projection had been used as an input to four other decisions:

1. Retention — three years, chosen because storage looked negligible.
2. Partition grain — daily, sized so partitions would be ~11 GB. They were 26 GB.
3. Compaction cadence — nightly, budgeted against the projected volume.
4. Lifecycle tiering — a transition to infrequent-access at 90 days, whose retrieval-cost modelling assumed the smaller base.

A number that is wrong by 2.3× and used as an input to four decisions produces four wrong decisions, and none of them announces itself. Chapter 1 §1.8's rule — every figure is a frozen input, arithmetic shown, code output, or a citation — exists precisely for this. A projection is the third category, and the third category is only as good as its input.

The Analysis

Finding it took two days, and the sequence is worth following because the first hypothesis was reasonable and wrong.

Hypothesis 1: the writer configuration differed. Production might be using a different codec, row group size, or compression level than the benchmark. Checked: identical.

Hypothesis 2: the production data has more columns. Checked: identical schema.

Hypothesis 3: the sample was not representative. This turned out to be right, and the way they confirmed it is the useful part. They took the benchmark's 500,000-event sample and a random 500,000 events from the production month, and compared per-column compressed sizes:

Four columns account for essentially the entire difference, and they are exactly the four §11.6's callout names: path, referrer, user_agent, and session_id.

🔎 Read the Plan — Compare per-column sizes, not totals

The total told them the sample was 2.5× more compressible and nothing about why. The per-column breakdown identified the cause in one table.

python import pyarrow.parquet as pq f = pq.ParquetFile("sample.parquet") rg = f.metadata.row_group(0) for i in range(rg.num_columns): c = rg.column(i) print(f"{c.path_in_schema:20} {c.total_compressed_size/1e6:7.2f} MB " f"{c.total_uncompressed_size/c.total_compressed_size:6.1f}x")

Do this on any Parquet file you are reasoning about. It takes ten seconds and it tells you which columns are actually costing you — which is almost never the ones you would guess, and is the input to every layout decision from Chapter 8 §8.8 onward.

A useful heuristic from the table above: a column whose compression ratio is near 1× is incompressible — random identifiers, hashes, IP addresses, encrypted fields. Those columns dominate a well-compressed file, and if one of them is not needed downstream, dropping it is worth more than any codec change.

Why the sample was unrepresentative

The sample was fetch_events(date="2025-06-14", limit=500_000), and the function's implementation was:

SELECT * FROM events WHERE event_date = %s ORDER BY event_ts LIMIT %s

ORDER BY event_ts LIMIT 500000 returns the first 500,000 events of the day, which is approximately midnight to 01:40 UTC.

At 01:00 UTC, the traffic on a US-centric site is:

• Overwhelmingly bots and crawlers, which have a small set of user-agent strings and hit a narrow set of paths.
• Long-running sessions from a small number of real users, so session_id repeats far more.
• Almost no referrers, because bot traffic arrives without one.
• Very few unique paths, because crawlers walk a sitemap rather than a search.

The sample was a slice of the least diverse ninety minutes of the day, and every one of those properties makes the data more compressible.

The engineer who wrote limit=500_000 was doing the obvious thing to get a manageable sample. The reviewer read the benchmark and confirmed the formats, the codec, and the arithmetic. Neither of them thought about which 500,000 events they were getting, because LIMIT looks like "a sample" and is actually "the first ones."

The Decision

Three changes.

1. Sampling is random and spans the period. The helper was rewritten and the old behavior removed so it could not be reached:

def fetch_sample(start, end, n):
    """A random sample spanning the whole period.

    NOT `ORDER BY ts LIMIT n`. That returns the first n events, which on a
    US-centric site is 90 minutes of overnight bot traffic -- narrow user
    agents, few referrers, repeated sessions, and therefore 2.5x more
    compressible than the day it is supposed to represent.
    """
    return query("""
        SELECT * FROM events
         WHERE event_ts >= %s AND event_ts < %s
           AND random() < %s
         LIMIT %s
    """, [start, end, sampling_fraction(start, end, n), n])

2. A benchmark honesty checklist, required in any format or performance benchmark's write-up:

• [ ] Is the sample random, or the first/last N?
• [ ] Does it span at least one full daily and weekly cycle?
• [ ] Does it include the known-messy cases — malformed records, nulls, extreme values?
• [ ] Are the cardinalities of the high-entropy columns comparable to production? (state them)
• [ ] Was the comparison run with identical writer settings?
• [ ] Is the projected figure within 30% of a real measurement on at least one full day?

3. Every projection carries its measured basis. A projection in a design document must state the measurement it derives from, its sample size, and how the sample was drawn — so a reviewer can attack the input rather than only the arithmetic.

📐 Design Decision — Benchmark a full period, or a random sample?

The team considered requiring a full day rather than a random sample.

For a full day: no sampling question at all, and it captures the daily cycle exactly.

Against: 11 million events is slow to write in nine formats, so the benchmark takes an hour instead of a minute — and a benchmark that takes an hour gets run once instead of iterated on.

They chose random sampling with a full-day validation: iterate on a fast random sample, then confirm the final candidate against one complete day before quoting a number in a document.

What it costs: two steps instead of one, and the discipline to actually do the second. In practice the validation step was skipped twice in the following year and caught in review both times, which is roughly the outcome you should expect from a process step that is not automated.

What Happened

Re-benchmarked with a random sample across a full week: 9.4×, against the realized 9.1×. Within 3%.

Downstream corrections:

• Retention reduced from three years to eighteen months, after a review found nobody had queried beyond fourteen months.
• Partition grain kept daily; 26 GB partitions are large but within range.
• Compaction cadence unchanged.
• Lifecycle tiering moved from 90 days to 45 days, on the corrected base.

Two follow-on effects worth recording.

The per-column analysis produced a finding nobody was looking for: event_id cost 4.09 MB per 500,000 events and was used by nothing. It was a UUID generated by the client, never joined on, never filtered on, and never displayed. Dropping it from silver — keeping it in bronze — removed about 16% of the silver layer's size for no loss.

The checklist caught a different benchmark four months later, comparing two query engines, which had used a sample of one hour of a Sunday. The re-run reversed the ranking.

Lessons

1. LIMIT n is not a sample. It is the first n, and "the first n" has a shape.

2. The four fields that decide a clickstream benchmark are paths, referrers, user agents, and session identifiers. All four are high-cardinality in production and low-cardinality in almost any convenient sample.

3. Compare per-column compressed sizes, not totals. The total tells you there is a difference; the breakdown tells you the cause, in one table, in ten seconds.

4. A column whose compression ratio is near 1× is incompressible and dominates the file. Check whether anything downstream needs it.

5. A wrong projection propagates into every decision that used it. $126 a month of direct cost, four wrong downstream decisions, none of which announced itself.

6. A projection must state its measured basis — sample size and how it was drawn — so reviewers can attack the input rather than only the arithmetic.

7. Iterate on a fast sample; validate on a full period before quoting. And expect the validation step to be skipped unless it is automated.

8. Review confirmed the formats, the codec, and the arithmetic, and missed the sample. Reviewers check what is visible in the code; how a sample was drawn frequently is not.

Questions for Discussion

1. The reviewer checked formats, codec, and arithmetic and missed the sampling. What would a review checklist have to say to catch it? Write the one line you would add to a pull-request template.

2. The direct cost was $126 a month and the consequential cost was four wrong decisions. How would you present this incident to a manager who sees only the $126?

3. event_id was 16% of the silver layer and used by nothing. How would you find other columns like it across a whole platform? What would stop you from dropping one you actually needed?

4. The team chose random sampling plus full-day validation, and the validation was skipped twice. Design the automation that makes skipping impossible, and estimate what it costs to build.

5. The realized ratio was 9.1× and the corrected benchmark gave 9.4×. What error band would you consider acceptable for a projection used to size a three-year retention policy?

6. Chapter 11's own benchmark makes the same class of mistake in its low-cardinality mode, and says so. Should a book's benchmark be run on real data instead? What would that cost a reader?

7. Retention was reduced from three years to eighteen months after finding nobody queried beyond fourteen. How would you find that out at your own organization, and what would you do about the one team that objects?