Retention Clock

A policy says records are kept for seven years. A team implements it as "delete rows older than seven years" and fails the audit, because the seven years were never counted from the insert. They are counted from the end of the relationship, the last transaction, the closure of a claim, or the tax year-end after the event.

The retention clock is attached to a business event that usually lives in a different table, and sometimes in a different system, from the data it governs.

Why it matters

Deleting by row age destroys live records and keeps dead ones, and both directions are findings: early deletion loses evidence you were obliged to hold, late deletion holds personal data without a basis. Neither failure produces an error, so the first signal is an auditor's sample.

The clock also moves. A reopened claim, a payment nine years after the fact or a new contract restarts the period for records already scheduled for deletion. A system that stored a deletion date at insert is now wrong in a way nothing detects.

Implementation patterns

• Recompute, never store. The retention job derives the due date from the governing event on every run, so a late-arriving event changes the schedule automatically.
• A record class on every dataset, each with its period and its governing event. Five classes is usually enough for an estate; twenty means the register rots.
• Separate classes into separate stores. Once two retention periods share a table, a lifecycle rule cannot be correct. Splitting by class is usually cheaper than building a per-row engine.
• Legal hold as an overlay that wins, applied per matter and per subject, with a documented release. A global pause on deletion is not a hold: it creates a second violation while it runs.
• Crypto-shredding for append-only stores, where destroying a per-subject key is the only practical deletion.
• Evidence by default. The job records what it deleted, under which class and on which event, because the audit question is "prove it".

Industry example

Financial services shows the pattern most clearly. Under MiFID II, in force across the EU since 2018, records of orders and communications must be kept for at least five years, measured from the transaction rather than from when a row was written, while marketing data attached to the same customer is governed by consent and must go far sooner. The same person's rows therefore have different clocks in adjacent tables, which is why the class, not the customer, is the unit of retention design. Any institution that unified them to one period has either kept too much or lost records it was obliged to produce.

Failure scenarios

• Partition-granularity deletion: a daily partition of 30m rows is removed when the partition ages, which is late for every record in it.
• The stored delete_after column, wrong the moment a late event lands.
• Copies with their own default: the warehouse, the search index and the vendor extract all kept "forever".
• The permanent legal hold, applied during litigation and never released, leaving years of data outside policy.
• Deletion with no evidence, indistinguishable at audit from no deletion at all.

Trade-offs

Per-record retention is precise and expensive: it needs the governing event available to the job, rewriteable storage or a key-destruction scheme, and an evidence trail. Partition-level rules are nearly free and imprecise by up to the partition period. Choose partitions when a dataset holds one class and the imprecision is within policy tolerance; choose per-record when classes are mixed or the regulator counts days.

When not to use it

A dataset with a single class, uniform volume and a tolerant period does not need a retention engine — a dated partition and a lifecycle rule is the right answer and a service around it is waste. Similarly, data under an active legal hold should not be inside the automated path at all: the hold is the decision, and automation that can silently override it is a liability.

Interview question

Q: You inherit an estate where every table has created_at and a nightly job deletes rows older than seven years. Tell me what is wrong, what you would change first, and how you would prove to an auditor that it now works.

What a strong answer covers: the governing event versus the insert · recomputation rather than a stored date · class-per-dataset and splitting mixed tables · derived copies inheriting nothing · holds as an overlay with release · crypto-shredding where rewrite is impossible · and evidence as a first-class output, since the audit tests the log rather than the policy.

Quick check

Quiz: Why is a stored deletion date a defect? A late-arriving governing event moves the clock and the stored date does not follow it, so the record is deleted early or kept late with no error.

Flashcard: What is the cheapest fix for a table holding two retention classes? — Split it, so a partition-level lifecycle rule becomes correct again instead of building a per-row retention engine.