Retention sweep

What a retention sweep is

A retention sweep is the mechanism that makes a retention policy true. The policy says a class of data may be kept for a defined period and no longer. The sweep is the scheduled process that finds records past that period, removes them from every store that holds them, and leaves evidence. Without the sweep, the policy is a statement of intent that decays quietly while the tables grow.

The unit of work is a data class with a clock, not a table. Marketing engagement events might carry twenty-four months from last activity, support transcripts twenty-four months from closure, invoices seventy-two months from the relevant filing date, and application logs ninety days. Each has a different rule, a different store and a different exception. A single job called delete old data will either be too broad to be safe or too narrow to matter.

The output of a sweep is three things: rows removed, evidence written, and a metric that tells you whether the backlog is shrinking. A job that deletes successfully and reports nothing is indistinguishable from a job that has been failing silently for a year, which is the most common state of retention programmes in companies that have never been audited on one.

What it is not: erasure, archiving, anonymisation

It is not an erasure request. Under the DPDP Act a data principal can ask for erasure, and the fiduciary must act on that request for that person. A sweep runs on a schedule and covers everyone whose clock has expired. The two paths should share a deletion service and a deletion log, because a reviewer will ask whether both actually remove data from the same places, and the honest answer is often that they do not.

It is not archiving. Moving rows from a hot table to a cold table preserves them, which is the opposite of retention. An archive is a legitimate tool for legal hold and for statutory records, and it is not a deletion. If the archive is readable, queryable and backed up, the data is still there. Teams that rename the table to archive_2023 and call the retention project complete have moved the problem and renamed it.

It is not anonymisation either, although the two are related. Anonymisation transforms a record so that it no longer identifies a person, and the result may be kept indefinitely if the transformation is genuine. A sweep may choose to anonymise rather than delete where the aggregate retains analytical value. What it must not do is replace a name with a pseudonym and keep everything else, then describe the output as anonymous.

Writing a retention rule that can be executed

A rule has to name the data class, the store, the clock, the anchor, the action and the exception. The clock is a duration. The anchor is the event the duration runs from: last activity, ticket closure, invoice date, contract end. The action is delete or de-identify, and the exception is the legal hold that can pause it. A rule missing any of those five cannot be implemented without someone guessing.

Anchors are where most rules quietly break. Twenty-four months from last activity sounds precise until you ask which activity counts, whether an email open resets the clock, and what happens when the marketing platform and the warehouse disagree about the date. Pick one authoritative anchor, compute it in one place, and write the definition down. Derived anchors such as last engagement should be materialised, not recomputed differently in four systems.

Time zones and calendar arithmetic deserve a line in the policy. Twenty-four months from 31 January is not a well-defined date, and a duration of 730 days produces different results from two calendar years. Choose one convention, apply it in UTC with a documented offset for reporting, and test the leap year and month-end cases. These are small bugs that produce large, embarrassing disputes about whether a record should still exist.

Tombstones: why a marker beats a hard delete

A tombstone is a small record that says an entity was deleted, with a timestamp and a reason, while the payload is removed. It exists because hard deletion breaks things in distributed systems: a foreign key loses its parent, a replica re-inserts the row it still has, a downstream system has no way to distinguish a deletion from a row it has not yet received, and a retried job cannot tell whether it already ran.

The tombstone carries no personal data beyond an identifier, and that identifier is often a keyed hash. It lets every consumer of the stream learn that the entity is gone, lets the sweep be idempotent, and lets a restore know what to re-delete. It is also the record that answers the question a customer asks: when exactly did you delete my data. A tombstone with a timestamp is a better answer than a promise.

The tombstone must itself be scoped. Keep it only as long as needed to propagate and to prove the deletion, typically the replication window plus the audit period, and keep it out of the sweep that deletes the payload. A tombstone that lives forever with a plain email address in it is a retention problem wearing a compliance badge.

The cascade: every place a copy lives

Most first attempts delete the primary row and stop. The data then survives in at least six other places: a read replica, a search index, a warehouse table, a cache, an analytics export, and a nightly backup. Each is a copy with its own retention, and each will produce the record again the next time someone queries it. A deletion that covers one of six stores is a twenty percent deletion reported as complete.

The inventory is the actual work. For each data class, list every store, who owns it, how the deletion propagates, and how long the propagation takes. Streams and queues are easy to forget, and so are derived features: a churn score computed from a deleted customer, a recommendation embedding trained on their history, a dashboard that cached the number. The test is not whether the primary database is clean. It is whether a search across the estate returns nothing after the propagation window.

Then measure the propagation. A deletion that reaches the warehouse in fifteen minutes and the vector index in three days is a three-day window in which a support agent can retrieve a passage about a person who has left. Publish the propagation time per store, and alert when a store misses its window. This is the same discipline as a suppression list in email, applied to the whole estate.

Backups are the part nobody wants to solve

A backup is immutable by design, which is exactly what makes it useful and exactly what makes deletion hard. You cannot run a delete against a snapshot you are not supposed to modify. There are three honest strategies. Expire backups on a schedule shorter than the longest retention rule, so the copy disappears on its own. Replay the deletion log against any restored backup before it is handed to an application. Or encrypt each subject’s data with a per-subject key and destroy the key, which renders the ciphertext useless without touching the backup.

Each has a cost. Short expiry limits your recovery window, which is a business decision someone senior has to make. Replay requires that the deletion log is complete, ordered and durable, which is a real engineering commitment and the reason tombstones matter. Crypto-shredding requires key management per subject, which is expensive at scale and awkward when a row contains data about several people, but it is the only method that works against an immutable archive without rewriting it.

The dishonest strategy is to declare backups out of scope. Some policies do this explicitly, arguing that a backup is not a live processing system. That argument survives until a restore puts deleted data back into production, or until a regulator asks how a person’s data can be erased while three copies remain. Write the strategy down, name the recovery window it costs you, and get it signed.

Proof of deletion: the receipt and the manifest

A deletion receipt is a record that a specific rule ran against a specific scope at a specific time and affected a specific number of records. It contains the rule identifier, the job version, the start and end timestamps, the store, the count, the operator or schedule that triggered it, and a keyed hash of the affected identifiers so that a sample can be verified later without storing the identifiers themselves. It does not contain the deleted data, because a deletion log full of personal data defeats the exercise.

The keyed hash matters. A plain hash of an email address is reversible by anyone with a list of candidate addresses, which is a small space. Use a keyed hash with a key held separately, or store only counts and a random sample of record identifiers that were themselves deleted. The goal is to prove that a particular record was in scope without retaining the record.

Beyond the receipt, three artifacts make a claim credible. A verification query run after the sweep that returns zero rows for the expired cohort. A periodic sample where an engineer picks ten deleted identifiers and confirms they are absent from every store. And an attestation from the data owner that the rule matches the policy, reviewed annually. Receipts prove the job ran. The sample proves the job worked. The attestation proves the rule was right.

Exceptions: legal hold and statutory retention

Some data must outlive the retention rule. In India, GST records are commonly retained for seventy-two months from the due date of the annual return, company books for eight years under the Companies Act, and employment records for the periods set by labour law. A dispute or an investigation creates a legal hold that suspends deletion for specific entities, not for the whole table. The sweep has to know about all of these or it will delete something a court asked you to keep.

Model the exception as a hold record attached to an entity or a scope, with an owner, a reason, a start date and a review date. The sweep queries active holds and excludes the matching rows, then reports the excluded count so that an unexplained drop in deletions is visible. A hold that nobody owns and nobody reviews becomes permanent, and permanent holds are how a retention programme quietly stops working.

The interaction with erasure is the delicate part. A person can ask for erasure and still have an invoice that tax law requires you to keep. The correct response is to erase everything outside the statutory record, retain the invoice with the minimum data the law demands, and explain the exception in the response. Deleting the invoice to satisfy the request creates a tax problem. Keeping everything to satisfy the tax problem creates a data protection problem.

The mistakes that make a sweep look successful

The first is the soft delete. A deleted_at column, a status of archived, or a filtered view is a UI convenience. The row is still there, still backed up, still joinable by anyone with the table. It becomes deletion only when a later job actually removes it, and that job is usually the one nobody scheduled.

The second is the archive table. Rows are copied to a table with a name that suggests cold storage, the primary is truncated, and the retention clock resets. The data is more exposed than before, because archive tables attract less attention and rarely have row-level security. The third is date arithmetic, where a rule written as twelve months is implemented as 365 days and drifts by a day a year, or computed in the wrong time zone so that a month-end job deletes a cohort a day early.

The fourth is logging the payload. A sweep that writes the deleted record into an application log for debugging has moved the data, not removed it, and logs are typically the least governed store in the estate. Log the identifier, the rule and the count. The fifth is running the sweep as the database owner with no dry-run mode. A retention job with a bad predicate and no preview is an outage with a compliance justification.

Running the sweep safely

Every sweep needs a dry run that reports what it would delete before it deletes anything. Run it against production read replicas, produce counts by class and store, and require a human to approve the first execution of any new rule. After that, the rule can run on a schedule with the counts still published, so that a sudden tenfold increase is visible before it becomes a support ticket.

Batch the deletes. A single statement that removes four million rows will hold locks, bloat the transaction log and, on some engines, block replication for minutes. Delete in bounded batches with a pause, and make the job resumable from a cursor so that a restart does not begin again. Record the batch boundaries in the receipt, because a partially completed sweep that reports success is worse than one that reports failure.

Then reconcile. Once a month, compare the count of records in the live store against the age distribution the policy predicts. A cohort that should have vanished and did not is a finding. A cohort that vanished early is a worse finding. The reconciliation is a query, not a meeting, and it is the only check that catches a rule which stopped matching because an upstream team renamed a column.

Measuring a retention programme

Four metrics cover most of it. Coverage, the share of personal data stores that have a registered rule, which should approach one hundred percent and is usually below half at the start. Backlog, the number of records past their retention date, which should trend to zero and stay there. Proof coverage, the share of executed sweeps with a receipt and a verification query. And propagation time, the p95 delay between a deletion and the last store returning zero.

Add a fifth for the failure that matters most: orphan stores, systems discovered during an audit that hold personal data and were not in the inventory. Every one of them is a place where a deletion claim is false. The number should be zero and it never is, which is why the inventory is a living document rather than a spreadsheet completed once for a questionnaire.

Report these to the same forum that reviews security findings. Retention is a control with an owner, a schedule and an audit trail, and it competes with feature work. A dashboard that shows backlog and orphan stores is the only way it gets engineering time before an incident forces the issue.

Worked example: twenty-four months on a Growth plan

Take a brand on the Growth plan, which includes 25,000 marketing contacts and 1,00,000 emails a month for ₹7,999 plus 18% GST, or ₹9,438.82. The policy says marketing engagement data is kept for twenty-four months from last activity, support transcripts for thirty-six months from closure, and invoices for seventy-two months. Nobody has run a sweep, and the warehouse holds four years of events.

The first dry run finds 18,000 contacts with no activity in twenty-four months, 400,000 engagement events tied to them, and 9,000 transcripts past their window. It also finds three stores nobody listed: a search index used by the support agent, a customer data platform that syncs nightly, and a set of CSV exports in a shared drive with no owner. The dry run is the inventory, and the inventory is the finding.

The execution deletes in batches over a week, writes 27,000 tombstones, replays them against the search index and the CDP, and expires the exports after confirming with the owner that nothing regulatory depends on them. The receipts record counts and keyed hashes. The verification query returns zero for the expired cohorts. The reconciliation a month later shows the age distribution the policy predicts for the first time, and the next dry run finds a backlog of roughly the monthly inflow instead of four years of accumulation.

None of this required a new platform. It required a rule with an anchor, a deletion service that every store respects, a tombstone that survives a restore, and a receipt that someone signs. The cost is engineering days rather than licence fees, and the benefit is that the next erasure request takes hours instead of a project, because the machinery already exists.