Case Study 1: Nine Days to Answer One Question

"Legal didn't ask us to delete anyone. They asked how long it would take. That was the whole request, and it took nine days to answer, and the answer was 'we can't.'"

Executive Summary

Kestrel's legal counsel asked a single question during an unrelated vendor review: "if a customer asks us to delete everything, how long does that take?"

Nobody knew. The investigation took nine working days and produced a list of twenty locations where a customer's data lived, of which eight had a deletion mechanism, three expired on their own inside the statutory window, and nine did not.

The most expensive finding was not on the list. The clickstream is keyed by device_id, not by customer_id — so the three erasure requests Kestrel had already processed, and marked complete, had never touched the largest dataset in the platform.

Twelve weeks of work produced coverage of 11 of 20 locations, a generated manifest, a verification step, and a documented position on the nine that remain.

Skills applied: deletion coverage (§31.5); the resolver and the generated manifest; the distinction between a location that needs a delete path and one that expires in time; and Chapter 20's SCD Type 2, arriving as a legal problem.

Background

The question was casual and the panic was not. It came up in a meeting about a vendor contract, was not on any agenda, and was asked in the tone of someone confirming a detail.

What existed at the time:

• An erasure procedure, written eighteen months earlier, consisting of a runbook with four SQL statements against Postgres and Snowflake.
• Three requests processed under it, all marked complete.
• No manifest, no inventory, and no verification step.

The runbook was not negligent. It was written by a competent engineer who deleted from the four places they knew about, and those four places were the four places anyone would have named. The problem is that naming places from memory is the wrong method, and nothing in the procedure revealed that.

The Problem

Day one produced a list of six locations and a feeling that it was incomplete. Day nine produced twenty, and the way the last fourteen were found is the useful part:

how each location was found                          count
─────────────────────────────────────────────────────────
named from memory by the platform team                   6
found by grepping models for `customer_id`               4
found by reading the Terraform for storage resources     3
found by asking each team "do you hold customer data?"   3
found in the vendor register kept by procurement         2
found by accident, during an unrelated conversation      2

⚠️ Failure Mode — the last two rows are the finding

Two locations were found by accident, in week two, when an engineer mentioned in a standup that the data science team kept training snapshots. Nobody had thought to ask, and the data science team had not thought to volunteer, because from their side it was obviously known.

The uncomfortable inference is not that two were missed. It is that the discovery method had no stopping condition. After nine days the team had twenty locations and no way to know whether the number was twenty or twenty-four — because every method in the table above is a search, and a search that returns nothing new tells you nothing about what remains.

This is the difference between an inventory and a manifest, and it is the reason §31.5 insists on generating the list rather than compiling it:

• An inventory is a snapshot of a search. It was correct on the day it was made, has no stopping condition, and decays from the moment it is written.
• A manifest is derived from a property of the data itself — here, the classification tag on any column that identifies a person. It is complete by construction, and a new table joins it without anybody remembering to add it.

The nine days were not wasted, because the search is how you bootstrap the tags. But the deliverable of the nine days should have been the tagging, not the list, and Kestrel spent a further two weeks converting one into the other.

The Analysis

The twenty locations, sorted by what they need:

HAS A MECHANISM (8)                       covered
NO MECHANISM, EXPIRES IN <30d (3)         acceptable, if documented
GAP: OUTLIVES 30d, NO MECHANISM (9)       work

The middle group was the most contested and the least expected. Three locations — two Kafka topics at 7 and 3 days, and application logs at 15 — needed nothing at all, because a 30-day deadline outlives their retention.

📐 Design Decision — the argument against building a Kafka delete path

The first plan had tombstone production and compaction for both topics, estimated at three weeks.

The argument that killed it: the orders.cdc topic retains 7 days. A request received today has a 30-day deadline. Every record in that topic today will be gone in a week, without anyone doing anything, and any record written after the request arrives is written from a source that has already been deleted.

Three weeks of hard distributed-systems work, deleted from the plan in one meeting.

But it was made conditional on two things, and getting these written down was the real output:

• The retention is enforced, not configured. Somebody verified it against the broker rather than against the Terraform. It matched — and the clickstream topic did not: it was configured for 3 days and a per-partition override had it at 30 on two partitions, from a debugging session eleven months earlier. That is the kind of thing this check exists to find.
• A change to the retention is an alert. retention_config.py now runs nightly and pages on any TTL increase on a topic in the manifest, because a topic silently moving from 7 days to 90 days converts a compliant location into an undetected gap.

The generalizable form: before building a control, check whether the data outlives the obligation. It is the cheapest question in the chapter and almost nobody asks it, because the instinct on hearing "we must be able to delete" is to build deletion.

The nine gaps, and what each actually needed:

Two of these are not engineering problems and were the hardest. The analyst CSV exports existed because analysts needed customer email addresses for a support workflow, and the answer was §31.6's masked view plus a permissioned lookup tool — removing the reason rather than forbidding the behaviour. The support desk vendor required a contract amendment, which took eleven weeks and ran alongside everything else.

The Discovery That Changed the Project

In week three, while writing the resolver, someone noticed that the clickstream had no customer_id.

clickstream event
  { "event_id": ..., "device_id": "a3f9...", "session_id": "77c1...",
    "url": ..., "ts": ..., "ip": ..., "user_agent": ... }

14,000,000 events per day, 4.19 TB/year, and not one customer_id in any of them. The join to a customer happens downstream, in a model that maps device_id to customer_id via login events.

Which means the three erasure requests already processed — and marked complete — had deleted the customer from Postgres, from Snowflake, and from the email vendor, and had left every clickstream event they ever generated exactly where it was.

🔐 Privacy & Governance — a person is not one identifier

This is the single most transferable finding in the case study, and the failure is structural rather than careless.

The runbook deleted by customer_id because customer_id is what a customer is, in the data model everyone works in daily. The clickstream identifies the same person by device_id, the support desk by email address, the email vendor by its own subscriber ID, and the mobile app by an install ID.

Five identifier spaces for one person, and a deletion that operates in one of them is a deletion that misses four.

The fix is to make resolution its own step, before any deletion happens:

text resolve(email) -> customer_id 1 device_ids n (from login events, all history) session_ids n (from device_ids) vendor_sub_id 1 (from the email platform's API) support_ticket_ids n (by email match)

Two properties matter and both were learned the hard way:

It must use all history, not current state. A device the customer used in 2024 and has not touched since is still theirs, and a resolver built on dim_customer current rows misses it. Kestrel's reads the full SCD Type 2 history (Chapter 20).

It must be the same code for deletion and for export. §31.10. If the access request resolves a person differently from the erasure request, one of the two is wrong and you will not find out which until someone compares them.

And the humbling part: the three completed requests had to be re-processed, and Kestrel had to decide whether that constituted a reportable failure. That decision was not the platform team's to make, which is §31.12 in practice.

The Decision

Six changes, and the order was deliberate.

One: tag first, manifest second. Every column identifying a person got a classification tag (§30.6), and manifest.py generates the location list from those tags. The list is no longer maintained; it is derived.

Two: the resolver is a separate, tested component. Five identifier spaces, full history, shared between erasure and access requests.

Three: verification is a required step. After deletion, re-run the resolver and assert zero rows everywhere. The DAG fails if anything remains, including in a table added since the last request.

Four: bronze migrates to Iceberg. Justified by this requirement.

Five: the three "expires in time" locations are documented, with monitoring on the TTL.

Six: a written position on each of the four gaps that will remain — backups, Time Travel, Fail-safe, and the residual copies — reviewed by counsel.

🧱 Kestrel Platform — what the Iceberg migration actually cost and returned

The migration had been proposed twice before, on query-performance grounds, and rejected twice. It was approved in nine days on this one.

Cost: six weeks of engineering, a rewrite of 4.19 TB of historical JSON and the Parquet derived from it, and a cutover that ran both formats in parallel for two weeks.

Return, in the order the team ranked them afterwards:

• A deletion from bronze went from rewriting essentially the whole table to writing a small deletion vector. One erasure request had cost more compute than a night of Spark ($3,840.00); it now costs effectively nothing until the next compaction.
• Schema evolution stopped requiring a rewrite, which nobody had asked for and which two teams noticed within a month.
• Time travel on bronze, which made three unrelated investigations tractable.
• Query performance, the argument that had failed twice, which was real and was ranked fourth.

The lesson the team drew is about how infrastructure work gets funded. The same migration, with the same benefits, was unfundable as a performance improvement and immediately fundable as a compliance requirement. That is worth knowing, and it is worth being honest that it is a fact about organizations rather than about engineering.

What Happened

The verification step has fired twice since. Both times a new table carrying customer_id had appeared, been tagged automatically by the ingestion check (§31.11), joined the manifest, and had no deletion mechanism. The DAG failed, an engineer added the mechanism, and the request completed within its deadline.

That is the entire point of the twelve weeks. Not the coverage number, which is 55% and will not reach 100%. The point is that a gap now announces itself instead of being discovered by an investigation nine days long.

Lessons

1. "How long would it take to delete someone?" is the cheapest question in this chapter and almost nobody has asked it. Kestrel's answer took nine days and was "we can't."

2. ⚠️ A search has no stopping condition. After nine days the team had twenty locations and no way to know whether the true number was twenty or twenty-four.

3. An inventory decays; a manifest is derived. The deliverable of a discovery exercise should be the tags that let you generate the list, not the list.

4. 🔐 A person is not one identifier. Five identifier spaces at Kestrel, and a deletion operating in one of them missed four — including the largest dataset in the platform, on three requests already marked complete.

5. The resolver must use full history, not current state, and must be the same code for deletion and for export, or one of the two is wrong and you will not find out which.

6. 📐 Before building a control, check whether the data outlives the obligation. Three weeks of Kafka tombstone work was deleted from the plan in one meeting — conditional on the retention being verified and monitored, and the verification immediately found a per-partition override at 30 days on a topic configured for 3.

7. Verification is what makes the pipeline real. It has fired twice, both times on a table nobody had thought about, and both times inside the deadline.

8. Two of the nine gaps were not engineering problems. The answer to uncontrolled CSV exports was removing the reason to make them, not forbidding them.

9. 🧱 The same migration was unfundable as a performance improvement and immediately fundable as a compliance requirement. That is a fact about organizations, and it is worth knowing.

10. Coverage of 55% is worth more than the previous nine-day guess, and the number that matters is not the coverage — it is that a new gap announces itself.

Questions for Discussion

1. The nine-day investigation had six discovery methods and no stopping condition. Design a method that does have one, and be honest about what it assumes.

2. Three erasure requests were marked complete and were not. What should Kestrel have done about the three customers, and who decides?

3. The Kafka decision saved three weeks on two conditions. What is the third condition nobody listed, and what would break it?

4. Kestrel has five identifier spaces. Count yours. How many does your deletion process cover?

5. The Iceberg migration failed twice on performance and succeeded once on compliance. Is that a healthy way for infrastructure work to get funded? What would you do with the knowledge?

6. Coverage is 55% and will not reach 100%. At what percentage would you be comfortable telling a regulator you can honor an erasure request, and what makes the difference?

7. The verification step fires when a new table has no deletion mechanism — which means it fails a request that has a legal deadline. Is failing loudly the right behaviour, or should it warn and continue? Argue both.