Case Study 2: The Table That Got Slower Every Week

"We adopted the format for the guarantees and skipped the maintenance, and ended up with a table that was worse than the Parquet directory it replaced."

Executive Summary

Six months after migrating a CDC target table to Delta Lake, a team found that queries against it had degraded from 4 seconds to 47 seconds, with no growth in data volume and no change in query text. Nothing had failed. Nothing had alerted.

The cause was compound: no compaction, so 340,000 small files; no vacuum, so 2.1 TB of tombstoned files behind a 190 GB table; and 1.2 million uncheckpointed commits, so simply resolving the current file set took 31 seconds before a single byte of data was read.

This case study is §10.5 as a lived experience. It is included because the failure is slow, invisible, entirely preventable, and — in the team's own assessment — left them worse off than the plain Parquet directory they migrated from, which is a genuinely uncomfortable finding for a chapter that spends most of its length recommending table formats.

Skills applied: maintenance obligations (§10.5); the transaction log and checkpoints (§10.2); merge-on-read amplification (§10.7); the small-files problem (Chapter 9 §9.6).

Background

The table. silver.orders, the target of a Debezium CDC stream (Chapter 14). Every change to kestrel_app.orders arrives as an insert, update, or delete and is merged into the table.

The migration. In January, silver.orders moved from a Parquet directory rebuilt nightly to a Delta table updated continuously by MERGE. The reasons were good ones:

• Deletes. Hard deletes in the source (Chapter 2 §2.2) had been unhandleable in a rebuild approach without a full comparison.
• Freshness. Nightly became near-continuous.
• Atomicity. No more partial-partition reads during the rebuild.

All three were achieved. The migration was, on its stated goals, a success.

The write pattern, which is where the problem came from:

# Every 60 seconds, merge the last minute of CDC events.
DeltaTable.forPath(spark, path).alias("t").merge(
    updates.alias("s"), "t.order_id = s.order_id"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().whenMatchedDelete(
    condition="s._op = 'd'"
).execute()

1,440 merges a day. 262,800 in six months.

The Problem

Nobody noticed for four months, which is the interesting part. The degradation was gradual and nothing crossed a threshold.

Data volume was essentially flat. silver.orders holds one row per order; Kestrel adds about 6,575 a day. The logical table grew about 6% over six months. The physical footprint grew 12×.

Three things were happening at once, and disentangling them took the team a week.

1. No compaction

Each merge writes new files for the affected partitions. With copy-on-write and 1,440 merges a day touching recent partitions, files accumulated relentlessly. By June: 340,200 files averaging 580 KB.

This is Chapter 9 §9.6's problem, and the table format did nothing to prevent it — it gave them OPTIMIZE and nobody ran it.

2. No vacuum

Every merge tombstones the files it replaced. Without VACUUM, tombstoned files stay on disk forever so that time travel works.

2.1 TB of the 2.3 TB was tombstoned files. The live table was 190 GB.

The storage cost is the visible part and the smallest:

$$2{,}300 \text{ GB} \times \$0.023 = \$52.90/\text{month against } \$4.37 \text{ for the live table}$$

$48.53 a month. Nobody noticed a $48 line item, which is exactly why it ran for six months.

3. No checkpoints — and this was the real killer

By June the log held 1,213,000 commit files. Checkpoints were being written, but the retention setting had never been reviewed, and the interaction between the checkpoint interval and the commit rate meant that resolving the current state required reading a checkpoint plus a large number of subsequent JSONs.

Resolving the file set took 31 of the 47 seconds. Before reading one byte of data.

⚠️ Failure Mode — The log becomes the bottleneck

This is the failure mode specific to table formats, and it does not exist on a plain Parquet directory. It surprises people because the log is presented as the thing that saves you from listing.

The log is a write-ahead structure whose read cost grows with commit count, and a high-frequency writer produces commits far faster than a batch writer. 1,440 commits a day is 526,000 a year. A streaming writer committing every 30 seconds is 1.05 million a year.

The three settings that govern it, and you must know all three:

Setting	Does what	Common default
delta.checkpointInterval	commits between checkpoints	10
delta.logRetentionDuration	how long commit JSONs are kept	30 days
delta.deletedFileRetentionDuration	how long tombstoned data files are kept	7 days

The trap: log cleanup only removes commits older than the retention duration and already covered by a checkpoint. If checkpointing falls behind — which it can under sustained high-frequency writes — cleanup cannot proceed, and the log grows without bound while every setting appears correctly configured.

The diagnostic, and it takes ten seconds:

bash aws s3 ls s3://.../silver_orders/_delta_log/ | wc -l aws s3 ls s3://.../silver_orders/_delta_log/ | grep checkpoint | tail -3

If the number of files is large and the newest checkpoint is old, you have found it.

The Analysis

The team's investigation ran in the wrong order, which is instructive.

Week 1: the query. They assumed the query had degraded and spent four days on it — the plan, the join order, the statistics. The plan was fine. The plan had always been fine.

Week 1, day 5: the file count. Someone ran Chapter 9's four numbers. 340,200 objects averaging 580 KB. That explained a large part of the 47 seconds and not all of it.

Week 2: the log. Instrumenting the client showed 31 seconds spent before the first data read. The _delta_log listing returned 1.2 million objects.

🔎 Read the Plan — Where the time goes in a lakehouse query

A lakehouse query has four phases, and most profiling tools show you only the last one:

text 1. Resolve the snapshot read checkpoint + subsequent commits ← 31 s here 2. Prune files apply predicates to log statistics ← fast 3. Read data files the part a query profile shows you ← 14 s 4. Compute joins, aggregation ← 2 s

Phase 1 does not appear in a Spark query profile. It happens in the client before the job is submitted, so the profile shows a 16-second query while the user waits 47 seconds — a discrepancy that is easy to dismiss as "cluster startup."

Time the phases separately:

```python import time from deltalake import DeltaTable

t0 = time.time(); dt = DeltaTable(path); t1 = time.time() files = dt.files(); t2 = time.time() df = dt.to_pandas(partitions=[("event_date","=","2025-11-28")]); t3 = time.time()

print(f"resolve snapshot {t1-t0:6.2f}s {len(dt.history(1))} history rows") print(f"list files {t2-t1:6.2f}s {len(files):,} files") print(f"read data {t3-t2:6.2f}s") ```

A snapshot resolution measured in tens of seconds is a maintenance problem, not a query problem — and it is invisible to every tool that starts measuring at job submission.

The Decision

Recovery, then prevention.

Recovery, over one weekend:

-- 1. Compact. 4.2 hours on a large cluster; 340,200 files -> 780.
OPTIMIZE silver.orders ZORDER BY (order_id);

-- 2. Vacuum. Retention 7 days, deliberately NOT lower -- see Chapter 10 §10.5.
--    Removed 2.1 TB. 40 minutes.
VACUUM silver.orders RETAIN 168 HOURS;

-- 3. The log. Checkpointing was forced and retention reduced, after
--    confirming that nothing needed time travel beyond 7 days.
ALTER TABLE silver.orders SET TBLPROPERTIES (
  'delta.checkpointInterval' = '10',
  'delta.logRetentionDuration' = 'interval 7 days'
);

Faster than the day it was migrated, because the compaction sorted and the original had not been.

Prevention — and the write-pattern change is the important one:

1. Merge every 15 minutes instead of every 60 seconds. 96 commits a day instead of 1,440.

This was debated, because it costs freshness — 15 minutes instead of 1. The freshness requirement was re-examined using the Chapter 3 §3.2 test — what decision changes if this is 15 minutes old? — and the answer for every consumer of silver.orders was "none." The 60-second interval had been chosen because it was the default in the example the engineer copied.

2. Nightly OPTIMIZE on partitions no longer being written.

3. Nightly VACUUM with 7-day retention.

4. A maintenance monitor — the delta_maintenance.py from this chapter's Part D — reporting file count, average size, tombstone ratio, and commits since checkpoint, with alerts at average file < 32 MB, tombstones > 25% of table, and commits since checkpoint > 100.

📐 Design Decision — Was the migration worth it?

The team asked this in their review, and the honest answer is more interesting than a straightforward yes.

What the migration genuinely delivered, and none of it was available before: hard deletes handled correctly, near-continuous freshness, atomic reads, and — six weeks after the recovery — a RESTORE that undid a bad merge in ninety seconds.

What it cost that the Parquet directory did not: three standing maintenance jobs, three configuration settings that must be understood and reviewed, a new failure mode (the log) that no profiling tool surfaces, and six months of degradation that reached 47 seconds before anyone investigated.

The team's conclusion: the migration was correct and incomplete. They adopted the format and not the operational practice, and for six months they were worse off than before — slower queries, 12× the storage, and a failure mode they did not know existed.

The generalizable rule: a table format is an operational commitment, not a file format choice. If you are not prepared to run compaction, vacuum, and log maintenance on a schedule, with monitoring, you will get a worse outcome than plain Parquet. Adopt the maintenance in the same sprint as the format, not when something gets slow.

What Happened

Eighteen months on, silver.orders is stable at 190–210 GB, 700–900 files, and a median query time between 3.5 and 4.5 seconds.

The maintenance monitor has fired six times:

• Twice for tombstone ratio, after unusually large backfills. Expected; vacuum caught up.
• Three times for file count on a different table that had been migrated without maintenance jobs — the same mistake, caught in days rather than months.
• Once, genuinely useful, when a schema change caused OPTIMIZE to fail silently for eleven nights. The job's own failure had not alerted; the monitor caught the consequence. That is worth noticing: a monitor on the outcome caught what a monitor on the job missed, which is Chapter 1's whole argument.

The 15-minute merge interval has never been questioned by a consumer.

Lessons

1. A table format is an operational commitment, not a file format choice. Adopt the maintenance in the same sprint as the format.

2. The degradation is gradual and crosses no threshold. 4 s → 47 s over six months, and nothing ever failed or alerted.

3. The log can become the bottleneck, and it is a failure mode plain Parquet does not have. Snapshot resolution took 31 of 47 seconds and is invisible to every query profile.

4. Time a lakehouse query in four phases, because tools measure only the last one.

5. Know all three settings: checkpoint interval, log retention, deleted-file retention. Log cleanup requires a checkpoint to have covered the commits, so checkpointing falling behind stops cleanup while every setting looks correct.

6. Commit frequency is a design parameter, not a default. 60 seconds versus 15 minutes is 15× the commits, and the interval had been copied from an example.

7. The storage cost is the visible symptom and the smallest one. $48/month went unnoticed for six months while queries got 11× slower.

8. A monitor on the outcome catches what a monitor on the job misses. OPTIMIZE failed silently for eleven nights; the file-count alert found it.

Questions for Discussion

1. The degradation crossed no threshold and nobody noticed for four months. Design an alert that would have fired in February. What is its false-positive rate on a table that is legitimately growing?

2. The investigation spent four days on the query plan before counting files. What would put "count the files" earlier in the sequence — a runbook, a dashboard, or training?

3. The 60-second merge interval was copied from an example. How many of your own configuration values came from an example? Propose a practical way to audit them.

4. The team concluded they were worse off than plain Parquet for six months. Does that change your view of when to adopt a table format? Write the precondition you would set.

5. Phase-1 snapshot resolution is invisible to query profiles. What other costs in this book are invisible to the tool people naturally reach for? Make a list.

6. VACUUM RETAIN 168 HOURS was chosen deliberately rather than something shorter, even though 2.1 TB was recoverable. Defend that choice to someone looking at a storage bill.

7. The monitor caught a silently failing OPTIMIZE job. Why did the job's own failure not alert, and what does that suggest about how you should monitor scheduled maintenance generally?