Case Study 1: The Customer Who Broke the Partition Key

"It ran in twenty-two minutes for a year. Then it took seventy-one minutes, twice a week, on days nobody could find a pattern in."

Executive Summary

In late 2025, Kestrel's nightly order aggregation began missing its 6am SLA roughly twice a week, apparently at random. Runtime jumped from a stable 22 minutes to 71 minutes on the bad nights, with no code change, no data volume change anyone could see, and no infrastructure change.

The cause was a single new customer: a wholesale buyer who had opened a business account and was placing large batch orders through the API. That one customer_id accounted for 8% of all order lines, and the pipeline was partitioned by customer_id.

This case study follows the diagnosis — which took nine days, mostly because the symptom's randomness sent everyone looking for a scheduling or infrastructure cause — and then works through the four candidate fixes and why the least sophisticated one won.

Skills applied: partitioning and skew (§4.2); stragglers (§4.7); the layer-count diagnostic (Chapter 2); reversal cost (Chapter 3 §3.1).

Background

The pipeline. kestrel_daily.aggregate_customer_orders reads silver.order_items, joins to silver.orders and dim_customer, and writes gold.fct_customer_daily — one row per customer per day with order counts and revenue measures. It is a Spark job, 200 partitions, and it had run in 21–24 minutes every night for fourteen months.

The partition key. customer_id. Chosen in 2024 because the job's dominant operation is a group-by on customer, and partitioning on the group-by key eliminates a shuffle. That reasoning was correct and remains correct; the key had 1.9 million distinct values and looked entirely safe.

The symptom, starting 2025-10-09. Runtime jumped to 68–74 minutes, roughly twice a week. On the other nights it was normal. The 6am SLA — the dashboard fresh for the prior day by 06:00 America/New_York — was missed on the bad nights by about twenty minutes.

The Problem

What made this take nine days was the pattern of the randomness. The team's first four hypotheses were all about scheduling and infrastructure, and all four were reasonable:

Hypothesis 1: resource contention. Another job overlapping. Checked the scheduler; no overlap on the bad nights, and there was overlap on some good nights.

Hypothesis 2: a noisy neighbor on shared infrastructure. Plausible, unfalsifiable from inside, and it absorbed two days. Cluster metrics showed no unusual host-level contention.

Hypothesis 3: data volume. Total row counts on bad nights against good nights: 2% higher on average, well within normal variation. This check is where the answer was available and was missed, because they looked at the total and not at the distribution.

Hypothesis 4: a slow node. Speculative execution was already enabled and was not firing, which should have been a strong signal — speculative execution duplicates a straggler on another node, and its not firing means the slow task was not slow because of its node.

On day seven someone opened the Spark UI for a bad run instead of reading aggregate metrics.

The Analysis

The Spark UI's stage detail told the whole story in one screen:

Stage 3: aggregate by customer_id     200 tasks
  ┌──────────────────────────────────────────────────────────────┐
  │ Duration    min 4 s   25th 6 s   median 8 s   75th 11 s      │
  │             max 47 min                          ◀── one task  │
  │ Shuffle Read  min 41 MB  median 68 MB  max 5.8 GB            │
  │ Records       min 210 K  median 344 K  max 29.1 M            │
  └──────────────────────────────────────────────────────────────┘

One task out of two hundred processed 29.1 million records — 85 times the median — and took 47 minutes while 199 tasks finished in under twelve seconds.

🔎 Read the Plan — The max column is the one that matters

Every distributed execution UI shows min, 25th percentile, median, 75th, and max for task duration and shuffle size. Almost everyone reads the median and almost every skew problem is visible only in the max.

The reason is that aggregate job metrics — total runtime, total records, total shuffle — are averages in disguise, and an average hides exactly the thing that determines your runtime. The job took 71 minutes because one task took 47. Nothing about the total told anyone that.

The habit worth building: for any parallel job, look at the ratio of max task duration to median task duration before you look at anything else. Under about 3×, you have normal variation. Above about 10×, you have skew and the rest of the investigation is finding which key.

Here it was 47 minutes over 8 seconds — a ratio of about 350×.

Finding the key took one query:

SELECT customer_id, COUNT(*) AS lines
  FROM silver.order_items oi
  JOIN silver.orders o USING (order_id)
 WHERE o.order_date >= CURRENT_DATE - 30
 GROUP BY customer_id
 ORDER BY lines DESC
 LIMIT 10;

 customer_id  |   lines
--------------+-----------
      1884203 | 1,431,882      <- 8.1% of all order lines in 30 days
       220417 |     4,006
       991238 |     3,880
        84117 |     3,715
       ...    |     ...

One customer, 1.43 million order lines in thirty days against a second-place figure of 4,006. A ratio of 357×.

Customer 1884203 was a wholesale buyer — a regional outfitter reselling Kestrel's products — onboarded on 2025-10-06, three days before the symptom began. They submit orders through the API in large batches, typically two or three times a week.

That is the randomness. The job was slow on the days that customer placed a batch.

⚠️ Failure Mode — The onboarding that changed a system property

Nothing in engineering changed. No deploy, no configuration, no infrastructure. A sales team signed a customer, which is what sales teams are for, and a data pipeline's core assumption silently stopped holding.

The assumption was never written down. "Order volume per customer is roughly uniform" was an implicit premise of the partitioning decision made in 2024, and it was true when it was made. Nobody recorded it, so nobody could check it, and no monitoring watched it.

This generalizes well beyond partitioning. Every architectural decision rests on assumptions about the shape of the data, and business events change that shape without passing through engineering: a new market, an enterprise customer, a partnership, a bulk import, an acquisition.

The practice: write the assumption down beside the decision, and where you can, monitor it. Chapter 3's ADR template has a "what would reverse this" section for exactly this purpose. The reversal condition here would have been "if any single customer exceeds 2% of order lines, revisit the partition key" — one query, run weekly, and this incident does not happen.

The Decision

Four fixes were on the table.

Option A — salt the hot key. Partition on a composite of customer_id and a bucket number, spreading one logical customer across (say) 32 partitions, then aggregate in two stages.

For: addresses the general problem, works for any future hot customer. Against: two-stage aggregation is more complex, and it requires either knowing which keys are hot (a maintenance burden) or salting everything (which reintroduces the shuffle the partitioning was chosen to avoid). Estimated three to four days plus ongoing complexity.

Option B — change the partition key to order_id. High cardinality, no natural hotspot.

For: eliminates skew completely and permanently. Against: reintroduces a full shuffle on the group-by, which was the entire reason customer_id was chosen. Benchmarked at 34 minutes on a normal night — worse than the 22-minute baseline every single night, in exchange for eliminating a 71-minute night twice a week.

That benchmark is worth pausing on: the intuitive fix made the average worse while fixing the tail. Whether that is a good trade depends entirely on whether you are measured on average runtime or on SLA adherence, and Kestrel is measured on the SLA.

Option C — increase the partition count from 200 to 2,000.

For: one-line change. Against: does nothing. All rows for one customer_id hash to the same partition regardless of how many there are. More partitions means smaller partitions for everyone except the one that matters. This option was proposed, benchmarked, and produced a 69-minute bad night — and it is worth recording because it is the most commonly proposed non-fix for skew.

Option D — split the aggregation. Handle the wholesale segment as a separate job with its own partitioning, and exclude it from the main job.

For: the two workloads genuinely are different — a handful of wholesale accounts with enormous volumes, and 1.9 million retail customers with small ones. Fast to implement. Against: a hard-coded segment boundary, and a second job to maintain. If wholesale grows to fifty accounts, this becomes its own problem.

📐 Design Decision — Why the least sophisticated option won, and what it cost

They chose D, with a twist: instead of hard-coding the customer, the split is driven by dim_customer.segment, which already existed and already distinguished retail from wholesale. The wholesale job partitions by order_id, accepts its shuffle, and runs in four minutes because the segment is small.

Implementation: one day. Bad nights: gone. Normal-night runtime: 23 minutes, essentially unchanged.

What was given up: generality. If a retail customer ever goes hot — a reseller operating on a consumer account, say — this fix does not help, and Option A would have. The team accepted that and added the skew monitor below to catch it, on the reasoning that a monitored gap is better than an unmonitored complexity.

The reasoning that decided it: the workloads were genuinely different, and the fix made that difference explicit rather than papering over it with a hashing trick. Salting is what you do when one population has an outlier. Splitting is what you do when you actually have two populations — and Kestrel had two populations. Match the fix to the shape of the problem, not to the sophistication of the technique.

What Happened

The split shipped on day eleven. Alongside it, three controls:

1. A skew monitor. Weekly, on every partitioned table:

SELECT partition_key_value,
       COUNT(*) AS rows,
       COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () AS share
  FROM <table>
 GROUP BY 1
 ORDER BY 2 DESC
 LIMIT 20;

Alert if any single key exceeds 2% of rows, or if max/median exceeds 10×.

2. A max-to-median task duration check on every Spark job, emitted as a metric after each run and alerting above 10×. This is a general control that would catch skew from any cause, not just this one — the "build the general control" lesson from Chapter 2's Case Study 1.

3. The assumption, written down. platform/docs/adr/adr-004-partitioning.md now records the partition key for each table and the distribution assumption it depends on, with the monitoring query that checks it.

The skew monitor fired twice in the following six months. Once for a bulk product-catalog import that concentrated in one category_id — caught before it broke anything. Once falsely, on Black Friday, when a single promotion code appeared on 31% of orders and a promotion-keyed table went briefly skewed; the alert was correct and the situation was fine, and the team added a known-exception list rather than widening the threshold.

That last detail is a small but important one. Widening a threshold to silence a known exception degrades the check for everything else. An exception list keeps the check sharp.

Lessons

1. Read the max, not the median. Every skew problem is invisible in aggregate metrics and obvious in the max-to-median ratio. Build the habit of looking at it first.

2. High cardinality is not even distribution. 1.9 million distinct customers, one of them at 8.1%.

3. Business events change data shape without passing through engineering. A sales team signed a customer and a partitioning assumption stopped holding. No deploy, no code change.

4. Write the assumption down beside the decision. "Order volume per customer is roughly uniform" was never recorded, so it could never be checked.

5. More partitions does not fix skew. All rows for one key hash to one partition regardless. This is the most commonly proposed non-fix.

6. The intuitive fix made the average worse to fix the tail. Repartitioning on order_id cost 12 minutes every night to save 49 minutes twice a week. Whether that is good depends on whether you are measured on average or on SLA — know which.

7. Match the fix to the shape of the problem. Salting is for an outlier in one population; splitting is for two populations. Kestrel had two populations, and making that explicit was simpler and clearer than a hashing trick.

8. Do not widen a threshold to silence a known exception. Use an exception list, or the check degrades for everything it was meant to catch.

Questions for Discussion

1. Four hypotheses about scheduling and infrastructure consumed seven days. What would have to be in a runbook for "job is intermittently slow" to have gotten someone to the Spark UI on day one?

2. Hypothesis 3 checked total row counts and found nothing. The answer was in the distribution. Write the version of that check that would have found it, and say why the total-count version is the one people write.

3. Option B fixed the tail and made every normal night 55% slower. Construct the argument for choosing it anyway. What kind of organization or SLA would make it right?

4. Option D depends on dim_customer.segment being correct. What happens if a wholesale customer is misclassified as retail? Design the check.

5. The team accepted a known gap — a hot retail customer would still break the main job — in exchange for simplicity, and added monitoring instead. Is "a monitored gap beats unmonitored complexity" a sound general principle? Name a case where it is not.

6. The skew monitor's 2% threshold produced one true positive and one true-but-uninteresting alert in six months. Is that a well-calibrated check? What would you change, and what would you refuse to change?

7. The Black Friday alert was correct and the situation was fine. The team added an exception list rather than widening the threshold. What is the maintenance cost of an exception list over five years, and how would you keep it from silently growing?