Case Study 1: The Ten-Week Stream That Should Have Been a Cron Job

"Nobody asked what would be done differently with fresher data. Everyone assumed somebody else had."

Executive Summary

In 2023, Kestrel's single data engineer spent ten weeks building a Kafka Streams pipeline to deliver real-time conversion tracking to the growth team. It worked. It was used by two dashboards. The growth team's actual decision cycle was weekly, and their actual freshness requirement — when someone finally asked — was hourly.

An hourly batch job would have taken four days.

This case study reconstructs how a ten-week project got approved without anyone asking the question that would have killed it, what the pipeline cost to run and operate for the eighteen months it existed, how it was decommissioned, and what the team put in place so it could not happen again. It is referenced in Chapter 1's Case Study 1 as "the first real mistake"; here it gets the full treatment, because the mistake is common and the mechanism that produces it is structural rather than personal.

Skills applied: the batch-versus-streaming decision test (§3.2); the operational cost of streaming (§3.2); cost arithmetic on the frozen basis (§3.5); ADRs and reversal conditions (§3.7).

Background

The request, as it arrived in the ticket, February 2023:

"We need real-time conversion tracking. Right now we see yesterday's funnel and by the time we spot a problem in a campaign we've already burned a day of budget. Can we get this live?"

Read that carefully. It contains a genuine problem, a proposed solution, and a stated justification — and the justification does not actually support the solution.

The genuine problem: a campaign performing badly burns budget before anyone notices.

The proposed solution: real-time conversion tracking.

The justification: "by the time we spot a problem we've already burned a day of budget."

The justification points at a detection latency problem. Detection latency is a monitoring problem. It was answered with a data pipeline — and an hourly batch job would have cut detection latency from 24 hours to 1 hour, capturing most of the available benefit for a twentieth of the effort.

Nobody did that comparison, because nobody framed it as one.

Why the request was approved. Four factors, and only one is about the requirement:

  1. The growth team was the loudest internal customer and had a real complaint.
  2. "Real-time" is unfalsifiable as stated. Nobody argues for staler data.
  3. The engineer wanted to build it. Worth stating plainly rather than hiding: a Kafka Streams pipeline is a more interesting problem than the fifth fact table, and engineers are people.
  4. The approving manager had no framework for evaluating the request beyond "is this technically feasible" — which it was.

The Problem

The pipeline that got built:

  web/app ──▶ kestrel.clickstream.v1 ──▶ Kafka Streams app ──▶ conversion_state
                    (12 partitions)         (windowed agg,        (RocksDB)
                                             30-min sessions)          │
                                                                       ▼
                                                              ┌────────────────┐
                                                              │ Postgres sink  │
                                                              │ live_funnel    │
                                                              └────────────────┘
                                                                       │
                                                              2 BI dashboards

In words: clickstream events flow from Kafka into a Kafka Streams application that maintains windowed session state in an embedded RocksDB store, writes aggregates to a PostgreSQL table, and two dashboards read it.

What it delivered: funnel counts with about eight seconds of end-to-end latency.

What it cost to build: ten weeks, one engineer, in a year when the company had exactly one data engineer and no fact table for shipments, returns, or inventory.

💸 Cost Check — The pipeline's eighteen months

Infrastructure, on the frozen basis. Three Kafka brokers (already needed for other work, so attribute half), one Streams instance sized for the 2,900/sec peak, one small PostgreSQL sink. Roughly $640/month, or $11,520 over eighteen months.

Now the part that dwarfs it — operational time, from the team's own incident log:

Category Incidents Hours
Consumer lag alerts (mostly false, during traffic spikes) 31 22
State store corruption after ungraceful restarts 4 26
Rebalance storms after deploys 7 14
Backfills after upstream schema changes 3 38
Version upgrades 2 16
Total 47 116 hours

116 hours is about three working weeks of the only data engineer's time, spread across eighteen months in unpredictable chunks — worse than three contiguous weeks, because each interruption also cost context on whatever else was in progress.

Total cost of ownership: ten weeks to build plus three weeks to operate — roughly thirteen engineer-weeks, against four days for the alternative. The infrastructure bill was the least interesting number in the analysis, and it is the only one that would ever have appeared on a cost dashboard.

The backfill line deserves emphasis: three backfills consumed 38 hours, more than any other category, because reprocessing a stream is genuinely hard. Each meant resetting consumer group offsets, clearing the state store, replaying from a retained offset, and reconciling against what was already in the sink without double-counting. The equivalent for a batch job is re-running it with a date parameter.

⚠️ Failure Mode — The state store that could not be rebuilt

The worst of the four state store incidents took eleven hours.

Retention on kestrel.clickstream.v1 was seven days. The Streams application's state store held 30-minute session windows, but the changelog topic backing it — the mechanism by which state is restored after a failure — had been created with the cluster default retention rather than log compaction.

When an instance was terminated ungracefully during a deploy, the state store was corrupted and the changelog no longer had enough history to rebuild it. The only recovery was reprocessing from the source topic — seven days of data, six hours — during which the dashboards showed a partially rebuilt funnel that looked plausible and was wrong.

Nobody had checked the changelog topic's retention. It was a default. Defaults are decisions someone else made for a situation that was not yours, and in streaming systems they are load-bearing in ways that stay invisible until the recovery path is exercised for the first time — which is always during an incident.

Chapter 15 §15.6 covers changelog topics and compaction. The general lesson: exercise your recovery path deliberately, before it is exercised for you.

The Analysis

In late 2024 the team — by then three engineers — audited the pipeline. Three questions.

1. Who actually uses it? Two dashboards. From 90 days of BI access logs:

Dashboard Distinct viewers Views/week Median session
Live Funnel 4 6.2 90 seconds
Campaign Monitor 2 1.4 45 seconds

Six people, under eight views a week between them. And the finding that ended the debate: 94% of views occurred between 09:00 and 11:00 on Mondays and Tuesdays.

That is not the usage pattern of a real-time system. It is the usage pattern of a weekly review.

2. What decisions were made from it? They interviewed all six users. Every one described the same workflow: Monday morning, look at last week's funnel; if a campaign looks bad, investigate; adjust budget Monday or Tuesday.

Not one could describe a decision made from data less than a day old. Two said they liked seeing it update live. One said, unprompted, that they had assumed the real-time capability was "free, since we already had the data."

3. What would batch cost? An hourly job aggregating the previous hour: about four days to build, roughly $96/month to run at the volumes in §3.2, and effectively zero operational burden — it either runs or it fails loudly, and re-running it is a parameter change.

📐 Design Decision — What the streaming version genuinely bought

It would be dishonest to present this as valueless, so here is the honest accounting.

What streaming delivered: eight-second latency instead of one hour. For six people looking on Monday mornings, worth approximately nothing. For an automated bidding system adjusting campaign spend without a human — the thing the growth team described when they talked about burning budget — it would have been worth a great deal.

The pipeline was built for the requirement the growth team would have had if they had built the automation they talked about. They never built it, and nobody asked whether they were going to.

That is the sharpest version of the lesson, and it generalizes: a streaming pipeline serving a human is almost always over-built; a streaming pipeline serving a machine is often exactly right. The question "who or what consumes this, and how fast does it act" separates the two cases and takes thirty seconds.

The Decision

Decommission; replace with an hourly batch job.

It took three weeks, most of it on a detail nobody anticipated: the two dashboards had been built against the shape of the streaming sink table, whose grain matched nothing in the warehouse — one row per session per funnel step, updated in place. The batch replacement produced one row per hour per funnel step, append-only. Both dashboards had to be rebuilt.

That is a coupling cost (§3.6) nobody priced when the pipeline was built. The sink table was never published as an interface; it was an implementation detail two dashboards had been built against, and by the time anyone noticed, it was an interface.

Three controls were adopted alongside the decommissioning.

1. A latency justification is required for any sub-hourly pipeline. One paragraph in the ADR, naming the consumer and how fast it acts. If the consumer is a human, the request goes to hourly batch unless the paragraph explains why not.

2. Every serving table is either published or private, and private means access-controlled. The sink table was readable by the BI tool because that was easiest. Making private tables actually private prevents the coupling rather than documenting it.

3. Usage instrumentation on every dashboard and table, reviewed quarterly. The 94%-Monday finding had been available the whole time and nobody had looked.

What Happened

The hourly batch job shipped in five days — one more than estimated, because of the dashboard rebuild. It has run since without a single incident of the kind that consumed 116 hours.

Two follow-on effects worth recording.

The growth team did not notice the change for eleven days, and when they did, the comment was that the dashboard seemed faster. Nobody mentioned the freshness.

The team built a genuinely streaming pipeline eighteen months later, for inventory allocation — an automated system reserving stock across three warehouses when an order is placed, with a hard latency requirement in the hundreds of milliseconds because a customer is waiting. The experience from the conversion pipeline made that project substantially better: they knew to configure changelog compaction, to exercise the recovery path in staging, and to budget for operational load rather than discovering it.

The ten weeks were not wasted, exactly. They bought a lesson that could have been learned in a design review, at roughly a hundredth of the price.

Lessons

  1. "Real-time" is a request to interrogate, not to fulfill. Ask what acts on the data and how fast. Thirty seconds, and it decides the project.

  2. A detection-latency complaint is a monitoring problem. The actual pain — burning a day of budget before noticing — was mostly solved by an hourly job and fully solved by an alert.

  3. Price the operational cost, not just the build cost. 116 hours across 47 incidents, in unpredictable chunks, from a one-person team. That number never appears in an estimate and it dominated the total.

  4. Backfills are the hidden cost of streaming. Three of them consumed more hours than any other incident category. In batch, a backfill is a date parameter.

  5. Defaults in streaming systems are load-bearing. A changelog topic's retention default cost eleven hours the first time the recovery path was exercised — during an incident, which is when most recovery paths get exercised.

  6. An unpublished table that two dashboards read is an interface. Invisible until decommissioning, and it added three weeks.

  7. Instrument usage and actually look at it. The Monday-morning distribution was recorded from day one and went unqueried for eighteen months.

  8. The engineer wanted to build it. Name this factor out loud in design reviews. It is not misconduct, it is human, and it is far easier to account for when it is on the table.

Questions for Discussion

  1. Rewrite the original ticket as it should have been written, given what the growth team actually needed. What would have to change about how requests are taken in for that version to be the one that arrives?

  2. Factor 3 was that the engineer wanted to build it. How should a team account for this without turning it into an accusation? Design a review question that surfaces it neutrally.

  3. The operational cost was 116 hours across 47 incidents, and estimating that in advance is hard. What heuristic would you use to budget operational load for a new streaming pipeline, and how would you calibrate it after the first year?

  4. The state store incident traced to a cluster default. How many defaults does a typical streaming deployment rely on unreviewed? Design an audit a four-person team would actually perform.

  5. Control 1 requires a latency justification naming the consumer. Write the version of that paragraph that would have justified the streaming build — for the automated bidding system that was never built. What evidence would you have required that it was actually coming?

  6. Control 2 makes private tables access-controlled. What does that cost in friction, and how would you keep it from becoming a process people route around?

  7. The team later built a streaming pipeline well, using what they learned here. Argue that the ten weeks were therefore a worthwhile investment. Then argue against it. Which is more persuasive, and what does your answer say about how you would run a team?