Case Study 2: The Four-Hour Outage That Found Nine Consumers

"We had checked the code, the query logs, and the file access timestamps. We were confident there were two consumers. We renamed the output for four hours on a Tuesday and nine people wrote to us."

Executive Summary

marketing_extract.sh wrote a CSV to a network share every night. Three discovery methods agreed it had two consumers. A pre-announced four-hour rename found nine.

method                              consumers found    effort
grep the codebase                                 1    minutes
warehouse access history                          2    an hour
file-share access timestamps                      4    a week
a pre-announced 4-hour rename                     9    4 hours

The seven the other methods missed were all people, not systems: an analyst's weekly notebook, a finance macro, a supplier-facing report, and four spreadsheets that read the file directly.

The migration would have shipped and broken all seven, and the discovery cost four hours on a non-critical output.

The technique is controversial, is genuinely used, and has a defensible form and an indefensible one. This case study is about the difference.

Skills applied: consumer discovery (§37.2); why instrumentation misses humans; and the ethics and mechanics of a deliberate, announced, bounded outage.

Background

marketing_extract.sh scored 11.4 — ninth of twelve, two consumers, not business-critical, 120 lines of shell. A small job, scheduled late, and expected to take three days.

The three non-destructive discovery methods were run first, in the order §37.2 recommends:

Grep the codebase. One hit: a downstream shell script that re-formatted the CSV.

Warehouse access history. The extract's source tables showed queries from one BI account. Combined with the grep, this gave "two consumers" — a number that felt low and was not obviously wrong for a marketing extract.

File-share access timestamps. A week of monitoring the share's read timestamps found four distinct machine accounts. Two matched the known consumers; two were unidentified service accounts.

⚠️ Failure Mode — instrumentation finds systems and misses people

All three methods share an assumption: that a consumer is a program. Each looks for a machine reading a thing.

Seven of Kestrel's nine consumers were a person opening a file.

text found by instrumentation (2) a downstream script, a BI extract found by the outage (7) an analyst's weekly notebook a finance Excel macro, run monthly a supplier-facing report, compiled by hand four spreadsheets linking the file directly

And the file-share timestamps did see some of them — that is why the count went from two to four — but a timestamp is not an identity. Two of the four were "unidentified service accounts" that turned out to be a shared reporting VM two different people remoted into. The method saw the machine and could not see that two humans with two unrelated purposes were behind it.

The deeper problem is periodicity. A monthly Excel macro reads the file twelve times a year. A week of access-timestamp monitoring has a 23% chance of seeing it at all, and if it does, it looks like noise.

So the general limits, worth knowing before you trust an instrumentation-based census:

  • It finds systems, not people.
  • It finds frequent consumers, not periodic ones, and the periodic ones are disproportionately finance, which is disproportionately who escalates.
  • It cannot distinguish two purposes behind one identity.

None of these is fixable by instrumenting harder. They are fixable by making the absence of the output noticeable to a human, which is the next section.

The Problem

The team's estimate was two consumers, from methods that agreed with each other.

Three methods agreeing is weaker evidence than it appears, because all three have the same blind spot — they observe machine access — and agreement among methods that share an assumption confirms the assumption, not the answer. This is Chapter 36 Case Study 1's reconciliation-independence problem, arriving in a census.

The migration plan was: rebuild the extract, shadow-run for two weeks, cut over, tell the two consumers.

The engineer who proposed the outage had migrated a similar job at a previous employer and had broken a monthly report nobody knew existed. Their argument was one sentence: "the only consumer we will find is the one who complains, so let's choose when that happens."

The Analysis

What Kestrel actually did, and every element of it matters:

1. chose a non-critical output              marketing_extract.sh, score 11.4
2. announced it 5 business days ahead       email to marketing + analytics
                                            + a note in the file's directory
3. chose a low-impact window                Tue 10:00-14:00, not month-end
4. RENAMED rather than deleted              marketing_extract.csv ->
                                            marketing_extract.csv.MIGRATION-TEST
5. left a README in its place               "this file has moved for a
                                            planned test until 14:00. If you
                                            need it, reply here: <link>"
6. staffed the channel                      two people watching, ready to
                                            restore in under a minute
7. restored at 14:00, on time

Nine responses in four hours. Seven were new.

🔎 Read the Plan — the README is what makes this a census rather than an outage

A deleted file produces confusion. A renamed file with an explanation produces a reply, and the difference is most of the technique's value.

What the README did:

  • It told people the absence was deliberate, so nobody spent an hour debugging their own spreadsheet before reaching out.
  • It gave a single, obvious place to respond, so the responses arrived in one thread rather than as four support tickets and three hallway conversations.
  • It asked the useful question. Not "are you using this?" but "what do you use it for?" — which is what §37.8's dead-code finding depends on, and which produced two answers of "I'm not sure anymore, actually."
  • It made restoration instant. A rename is reversible in one command; a deletion is not.

And the timing details are not decoration. Tuesday because Monday is catch-up and Friday is escalation-averse; 10:00 because it is after people have started and before anything is due; four hours because it is long enough to catch a daily process and short enough that nothing depends on it for a full cycle; not month-end because that is when the finance macro runs and breaking it is the one outcome that would have made this indefensible.

The whole design is an attempt to maximize signal and minimize harm, and it is what separates the defensible version from the indefensible one.

⚠️ Failure Mode — the version of this that should never be done

The technique has an obvious bad form and it is worth stating plainly, because the good form is otherwise easy to misuse.

Do not do it silently. An unannounced outage to "see who notices" is an outage. The information is the same and the cost is borne by people who did not agree to it, and it destroys the trust the data team needs for everything else in this chapter.

Do not do it on anything business-critical. Kestrel's rule is that the job must score below a threshold on §37.4's reach, must not be part of a regulatory or financial control, and must have a restore path measured in seconds.

Do not do it at a bad time. Month-end, quarter-end, a launch, a peak trading day. Kestrel checked the finance calendar before choosing the Tuesday, which is a thirty-second step that would have prevented the one genuinely damaging outcome available.

Do not do it more than once per job. It is a census, not a monitoring strategy. A team that does this repeatedly has taught its consumers that outages are normal, which is worse than not knowing who they are.

And get agreement first. Kestrel's platform lead cleared it with the marketing and analytics leads before the announcement. That conversation took ten minutes and is the difference between "a planned test" and "the data team broke my report."

The Decision

The seven new consumers changed the migration substantially.

consumer                        what it needed          outcome
downstream reformat script      the CSV                 migrated with the job
BI extract                      the source tables       repointed to gold
analyst's weekly notebook       the CSV                 given a warehouse view
finance Excel macro (monthly)   the CSV, exact format   CSV KEPT, generated
                                                        from the new model
supplier-facing report          a subset, by hand       became a real model
spreadsheet x2 (linked)         the CSV                 repointed to the kept CSV
spreadsheet x2 (linked)         --                      "not sure anymore" ->
                                                        stopped, with agreement

📐 Design Decision — keep the CSV, generate it from the new model

The instinct after finding four spreadsheet consumers is to migrate them to the warehouse. It is the right long-term answer and it was the wrong migration decision, for a reason worth generalizing.

Migrating four spreadsheets means four conversations, four people learning a new tool, and four chances for the migration to be blamed for a disruption that has nothing to do with the pipeline. It would have doubled the job's timeline on a job scored ninth of twelve.

Generating the identical CSV from the new model costs one dbt post-hook. The consumers experience nothing. The legacy job still dies, which is the actual goal, and the CSV becomes a documented output port of a maintained model rather than the side effect of a shell script nobody owns.

sql -- an output port, not a legacy artifact {{ config(post_hook="COPY (SELECT ... ) TO '/share/marketing_extract.csv' WITH (FORMAT csv, HEADER)") }}

The distinction that makes this defensible rather than lazy: the CSV is now a declared interface with an owner (§35.5's output port), documented in the catalog, with a deprecation path. Before, it was an unowned file with unknown consumers.

The general rule: migrating a pipeline and migrating its consumers are separate projects, and bundling them is how a three-day job becomes a quarter. Preserve the interface, kill the implementation, and schedule the consumer migration on its own merits — which Kestrel did, eight months later, for two of the four.

And two consumers were retired. Both replied "I'm not sure I still need this", both were asked to try a month without it, and neither asked for it back.

What Happened

Estimated Actual
Consumers 2 9
...found by instrumentation 2 2
...found by the outage — 7
Consumers retired 0 2
Outputs preserved 1 2 (the CSV, plus a new model)
Job duration 3 days 9 days
Consumers broken at cutover — 0

Nine days instead of three, and zero broken consumers. The team's assessment: the six extra days were the cheapest six days in the migration, because the alternative was discovering the finance macro at month-end, after cutover, as an escalation.

The technique was used once more in the estate, on legacy_ab_test.py — one known consumer, scored 5.9. The outage found zero additional consumers, and the job was deleted rather than migrated. A negative result is also a result, and it converted a planned migration into a deletion.

It was not used on the other ten jobs. All were either business-critical, part of a financial control, or had enough known consumers that the census would not change the plan.

🧱 Kestrel Platform — what replaced the technique

The outage is a census for a system that was not instrumented. The durable fix is instrumentation that sees humans, and Kestrel built three things:

```text every file output now goes through a small wrapper that logs (path, reading account, timestamp) -- so a share read is a record

every CSV output carries a header comment: "# generated by models/marts/marketing_extract.sql -- owner: #analytics # questions: #data-help"

every declared output port has an owner and appears in the catalog ```

The header comment is the one that has paid off most, and it is the cheapest. Three people in a year have asked a question in #data-help that began "I've got a file that says to ask here" — including one about an output nobody on the platform team knew existed.

It is Chapter 30 §30.9's instrument-the-asking, applied to a file: it does not find consumers, it makes consumers findable by making it easy for them to identify themselves. And unlike the outage, it works continuously and costs nobody anything.

Lessons

  1. Three discovery methods agreed on two consumers. There were nine. Agreement among methods that share an assumption confirms the assumption, not the answer.

  2. ⚠️ Instrumentation finds systems and misses people. Seven of nine consumers were a person opening a file.

  3. It also misses periodic consumers. A monthly macro is seen by a week of monitoring 23% of the time, and periodic consumers are disproportionately finance, which is disproportionately who escalates.

  4. And it cannot distinguish two purposes behind one identity — two people remoting into one reporting VM looked like one service account.

  5. 🔎 The README is what makes it a census rather than an outage. It says the absence is deliberate, gives one place to reply, and asks "what do you use it for?" rather than "are you using this?" — which produced two answers of "I'm not sure anymore."

  6. Rename, never delete. Reversible in one command.

  7. ⚠️ The indefensible version: silent, on something critical, at month-end, repeatedly, without agreement. Kestrel checked the finance calendar — a thirty-second step that avoided the one genuinely damaging outcome available.

  8. Get agreement first. Ten minutes, and it is the difference between "a planned test" and "the data team broke my report."

  9. 📐 Migrating a pipeline and migrating its consumers are separate projects. Keeping the CSV — as a declared output port generated from the new model — killed the legacy job without four spreadsheet conversations. Bundling them is how a three-day job becomes a quarter.

  10. Two consumers retired themselves when asked to try a month without it.

  11. Nine days instead of three, and zero broken consumers. The cheapest six days in the migration.

  12. A negative result is a result. The second use found no new consumers and turned a planned migration into a deletion.

  13. 🧱 The durable replacement is a header comment: "generated by X — questions: #data-help." Three self-identifications in a year, including one output nobody knew existed — Chapter 30 §30.9's instrument-the-asking, applied to a file.

Questions for Discussion

  1. Three methods agreed and were all wrong in the same direction. Where else do you rely on agreement between methods that share an assumption?

  2. The outage is controversial. Write the strongest objection to it, then say whether Kestrel's seven safeguards answer it.

  3. Kestrel used it on 2 of 12 jobs. What is your threshold — what makes a job eligible?

  4. The CSV was preserved as an output port. Is that a migration or a deferral? When does the deferral become a problem?

  5. Two consumers stopped using the file when asked. How much of what your platform produces would survive that question?

  6. The header comment found an output nobody knew existed. What would the equivalent be for a table, an API, or a dashboard?

  7. Could the four-hour outage have been replaced by something with the same yield and no outage? Design it, and be honest about whether it would have found the monthly finance macro.