The Deletion That Came Back

Day 271. A “we miss you — new arrivals in your size” email lands with a woman who closed her account on day 224 and was told all her personal data had been deleted. It is her 9th such email. The shop database does not have her. silver.dim_customer does — name, email, street — in a row written at 02:14 on day 233, four days after the runbook was signed off. No human re-created her. A dbt run --full-refresh did, from a raw layer that had never been asked to forget anything.

Art. 17 gives the right; Art. 12(3) gives one month. In a platform that is not one statement but a list: every place a copy came to rest, and what keeps it there. 14 places here. Thirty days after the runbook reported success, the average erased subject was still held by name and email in 7.77 of them (range 3–9).

Four reasons a deletion does not arrive — the whole taxonomy, four different fixes:

The runbook is correct on the day it runs, and it is a statement about derived tables. Derived tables are re-derived. Worse: the OLTP delete emits a CDC event whose before image is a complete copy of the person — the act of erasing her is the last thing that writes her name into the lake.

• 245 of 321 (76.32%) subjects were erased and then rebuilt back into the warehouse.
• Median survival of an erasure: 11 days (mean 12.48, max 34); 40.41% were back within a week.
• The 76 still absent on day 359 are exactly those erased after the last full refresh.

A fifth shape of failure. 11 named the constant, 12 the drift, 13 the unwatched. This one is none: the fix was applied, verified, correct — then reversed by a scheduled job. The reversible covers every hand-made correction to something that can be rebuilt (a manual price fix in a mart, a patched dimension row, a backfilled conversion).

Art. 19 obliges notification of each recipient to whom the data have been disclosed — i.e. every reverse ETL destination. The marketing audience is a filtered upsert sync (Lesson 13’s exact bug), so it is the union of everyone ever active and cannot be told that someone left.

No error was raised anywhere: the sync log is clean, and every warehouse test is green because gold.customer_vip_status is right. The only evidence lived in the one system the warehouse cannot query.

Sale facts are retained under a statutory obligation with the customer id replaced by a salted hash. Recital 26’s test is re-identification by any means reasonably likely to be used; Art. 4(5) says pseudonymised data is still personal data. On a marketplace of quantity-1 items, 186,473 of 187,060 sold items (99.69%) are unique on day + brand + category + size + condition + price — every one of those attributes public on the sold-listing page.

The residual is the point: at the strongest setting all 4 survivors sold a luxury item (28.57% of that tier, 0% of the other two). Mass-market sellers hide in a crowd; a Hermès seller has none.

Crypto-shredding (per-subject data key; destroy the key, 32 bytes) is the only mechanism whose cost does not grow with the number of copies. Caveats: the key store’s own backups are then the one place a hard delete must really work; regulators have not uniformly accepted it as erasure; and it does nothing for free text or for the re-identification above.

Daily row-count delta on silver.dim_customer (signups carry a weekday pattern, so the series has real variance: every non-refresh day sits in [+44, +84]).

The expectation was wrong. Five of the six refreshes are the five largest daily increases in the table’s history: +103 (3.94σ), +112 (4.89σ), +113 (4.99σ), +102 (3.84σ), +142 (8.04σ). The exception matters most: the first refresh added only +73 (0.79σ, inside the band) because only ten subjects had been erased by then. The detector gets easier to trip the longer the bug runs — the cheapest day to catch it is the day it is hardest to see.

1. An erasure register: hashed id, dates, basis for anything retained. 321 rows = 10,272 bytes.
2. Anti-join it inside every model, so a full refresh reproduces the erasure — 0 resurrections by construction rather than by vigilance.
3. A machine-readable registry of destinations (Art. 30, made useful): the verification job iterates it, so a sync added next quarter is covered the day it is registered.
4. Sync state, not membership (Lesson 13) so a destination can be told someone is gone.
5. Crypto-shredding where rewriting is off the table.
6. Minimise at ingest — tokenise at the edge; the cheapest copy to erase is the one never made.

If a correction is not represented as data the pipeline reads, it is not a correction — it is a pause.

-- 01 · erasure verification. One per system in the registry.
--      Lesson 13's boundary reconciliation, target set to zero.
SELECT count(*) AS still_present
FROM   silver.dim_customer d
JOIN   governance.erasure_register r ON r.subject_id = d.customer_id
WHERE  r.erased_day <= 217;
-- 10   (ten people confirmed as deleted, sitting in the warehouse)

-- 02 · how anonymous is the retained fact table, really?
WITH f AS (
  SELECT md5(seller_id || ':pepper') AS seller_pseudo, sold_day // 7 AS sold_week,
         brand, category, size, condition, (price_cents // 2500) * 2500 AS price_bucket
  FROM   gold.sale_facts
), k AS (
  SELECT sold_week, brand, category, size, condition, price_bucket,
         count(DISTINCT seller_pseudo) AS k
  FROM   f GROUP BY ALL
), joined AS (
  SELECT f.seller_pseudo, min(k.k) AS k_min
  FROM   f JOIN k USING (sold_week, brand, category, size, condition, price_bucket)
  GROUP  BY 1
)
SELECT count(*) AS erased_sellers,
       count(*) FILTER (WHERE k_min = 1) AS uniquely_identifiable
FROM   joined
WHERE  seller_pseudo IN (SELECT md5(subject_id || ':pepper') FROM governance.erasure_register);
-- 97 · 79   (81.44%)

-- 03 · the fix. The register is inside the model, so a full refresh
--      reproduces the erasure instead of reversing it.
CREATE OR REPLACE TABLE silver.dim_customer AS
SELECT c.* FROM bronze.customers c
WHERE  NOT EXISTS (SELECT 1 FROM governance.erasure_register r
                   WHERE  r.subject_hash = sha256(lower(trim(c.email))));
-- re-running query 01 afterwards: 0

1. When we delete a customer, which systems does that reach — and who wrote the list down?
2. Which of our tables are rebuilt from a source that was never told about the deletion?
3. Is there a query I can run today returning the number of people confirmed as erased who are still in the warehouse? What does it return?
4. What do we tell someone about their data in our backups, and is it written down?
5. The tables we keep for accounting — could an outsider work out whose row it is from the columns we left in?
6. If we deleted an engineer’s laptop CSV export from six months ago, would anyone know it existed?

1. Run the generator; confirm 321 requests and 245 resurrections.
2. Implement the runbook as three DELETEs, then --full-refresh as CREATE OR REPLACE TABLE … AS SELECT … FROM bronze. Watch the deletions vanish. There is no error to catch in that loop.
3. Add the register, anti-join it inside the model, re-run: verification must return 0. Four lines.
4. Write the verification job as a loop over a registry list of (system, query); fail loudly on non-zero. Add a system and watch the check cover it for free.
5. Compute k-anonymity at the three generalisation levels; then do it for your own most sensitive fact table.
6. Break it: implement crypto-shredding, then restore a backup of the key store and notice you have just un-erased somebody.
7. Push to GitHub with a README stating what the verification job guarantees and which of the fourteen systems it does not cover.

Deletion is not an operation you perform on a data platform; it is a property the platform has to preserve. Anything done by hand to a derived table is undone the next time something derives it — 245 of 321 here, median 11 days. So the register goes inside the models, destinations you don’t own get read back and diffed, copies you can’t rewrite get their key destroyed, and rows you must keep get generalised until the person is no longer the only one who could have sold that blouse. The number to ask for tomorrow: how many people confirmed as erased are still in the warehouse? On day 217 the answer was 10 — 16 emails instead of 480.