Chapter

Retention, Deletion and Data Lineage

Part H · Running Data Systems|4,076 words|about 18 min read|Volume H

48.0 What this chapter gives you#

Information has a useful lifetime. Keeping it forever can create unnecessary exposure; deleting it carelessly can destroy evidence, break references or make recovery impossible. The job is to define and implement a lifecycle rather than choose one slogan.
You will separate hiding a record, deleting a logical row, removing derived copies and sanitising storage media. You will also learn why an empty query result is not a universal certificate of erasure.
The policies and dates in this chapter are synthetic engineering examples. They are not legal retention periods, advice about a jurisdiction or a statement that a particular real organisation may delete a record. Actual legal, contractual and operational requirements need their own assessment.
The continuing shop’s agreed order facts are not silently erased or rewritten here. The deletion exercise uses a separately introduced optional contact note, NOTE-7, and a finite inventory of stores. This keeps lifecycle learning separate from the canonical order totals.

48.1 Purpose and record classes#

48.1.1 PLAIN — in simple words#

Start by asking why each kind of information exists. An order line, temporary import file, optional delivery note and access log serve different purposes and need not have the same lifetime.
A field can be useful at one stage and unnecessary later. Keeping a temporary instruction inside every downstream report can spread information beyond the work it was collected to support.
Decide the unit of lifecycle control. Sometimes an entire record is retired; sometimes an optional annotation is removed while a separately justified transaction record remains.
The purpose must describe actual use. Calling everything “for analytics” does not explain which question requires which fields, who may use them or when that use ends.

48.1.2 PLAIN — a picture in your head#

Mira keeps an agreed order form, a packing checklist and a sticky note saying where to leave a parcel. The order form may support one responsibility, while the sticky note serves only a particular delivery.
Copying the sticky note into every monthly sales report does not make the report more correct. It creates another copy that someone must control.
Where the comparison breaks: digital transformations can preserve identifying information indirectly through keys, small groups or joins. Removing a visible name does not automatically make the remaining data anonymous.

48.1.3 PLAIN — a worked example#

Introduce three fictional record classes: agreed_order_line, optional_contact_note and failed_import_payload. Give each an owner, stated purpose, access scope and lifecycle rule. No real legal period is assigned by the book.
NOTE-7 belongs to tenant T-A and a synthetic contact. Its purpose is one temporary follow-up. It is not required to calculate O-1042 or O-1043, whose agreed amounts remain 17,100 and 11,550 paise.
If NOTE-7 is copied into a search index and a support export, those are additional lifecycle responsibilities. The source table alone is no longer the complete inventory.
A record-class card can state: class, purpose, owner, collected fields, allowed uses, authorised readers, lifecycle trigger, exceptions, stores and evidence required for closure. A missing owner is itself an unresolved operational question.

48.1.4 PLAIN — what is really happening inside#

Schemas and pipelines decide where a field travels. A lineage description connects original inputs, transformations and derived outputs so lifecycle work can follow those routes.
Separating optional annotations from durable agreement facts can make independent lifecycle actions easier. The split must preserve valid references and document what an order means after an annotation disappears.
Minimisation can occur before storage as well as afterward. A transformation that never copies an unnecessary field avoids a future deletion problem in that destination.

48.1.5 TECHNICAL — the engineer’s version#

Treat classification as an application policy, not a property automatically inferred from a database type. TEXT can contain harmless labels or sensitive free-form notes; the engine does not determine their purpose.
OpenLineage’s object model distinguishes jobs, runs and datasets, providing a vocabulary for recording input/output relationships. It does not itself prove that every copy has been discovered or that a deletion was executed. [S193]
The chapter’s class cards and deletion inventory are original teaching designs. Their adequacy depends on the declared system boundary and the requirements supplied by its owners.

48.1.6 WORDS — remember these#

Record class: a group with a common responsibility — data categorised by purpose, access and lifecycle requirements rather than file extension alone. Purpose limitation: using information for a defined need — an explicit boundary on collection and downstream use. Data minimisation: keeping only what the task needs — reducing unnecessary fields, copies or lifetime under a stated requirement.

48.2 Retention policies#

48.2.1 PLAIN — in simple words#

A retention rule needs a start event, a duration or end condition, and an action. “Delete after thirty days” is ambiguous until we know thirty days after what.
Creation time, completion time and last legitimate activity may be different dates. Choosing the wrong clock can retire active work or keep abandoned information indefinitely.
Exceptions need an explicit basis, owner and review point. A permanent unexplained hold can turn a time-limited policy into “keep forever” without anyone making that decision openly.
A scheduled job is an implementation of a policy, not the policy itself. The organisation must still decide what should happen and verify that the job does it.

48.2.2 PLAIN — a picture in your head#

A library loan lasts fourteen days after checkout, not fourteen days after the book was printed. The event starting the clock changes everything.
A renewal changes the due date under a rule. Someone rubbing out the date on the card is not a valid renewal process.
Where the comparison breaks: a digital object can be referenced by several workflows with different completion conditions. One clock may not describe all legitimate uses of shared information.

48.2.3 PLAIN — a worked example#

For NOTE-7 only, invent a policy of expiry thirty elapsed days after its follow-up is closed. Let the synthetic closure instant be 1 September 2026 at 12:00 UTC. The resulting expiry is 1 October 2026 at 12:00 UTC.
This is elapsed-time arithmetic. A policy based on local calendar dates could produce a different boundary and must specify its time zone and treatment of daylight-saving transitions where relevant.
A hold on NOTE-7 suspends the deletion action under the chosen policy, but the hold needs a reason code, owner and review deadline. The automated system should not invent the reason.
Test one instant before expiry, exactly at expiry and after expiry. Also test an absent closure time and an active hold. In our example, unknown timing means review required, not an invented old date.

48.2.4 PLAIN — what is really happening inside#

A policy evaluator maps current record facts and policy version to an eligibility decision. The execution worker then attempts the corresponding action within a bounded batch.
Eligibility can change between selection and action. A newly recorded hold, for example, must not be ignored simply because the row appeared in yesterday’s export. Recheck conditions at the operation boundary where the design requires it.
Record the policy version used. When a policy changes, previously scheduled work may need reevaluation rather than blindly executing an obsolete list.

48.2.5 TECHNICAL — the engineer’s version#

Use explicit aware instants for elapsed-time examples and retain the original event-time semantics. A timestamp’s storage format does not decide whether the policy is elapsed-time or calendar-based.
Our companion evaluator rejects invalid types and negative durations, treats a missing closure instant as unresolved and makes the equality boundary explicit. Those checks test the arithmetic policy only, not a legal obligation.
Batch deletion should be restartable with bounded work and an auditable selection predicate. A large unqualified DELETE is not a lifecycle programme merely because it runs every night.

48.2.6 WORDS — remember these#

Retention trigger: the event starting a lifecycle rule — a defined transition such as closure, not an assumed timestamp. Hold: an explicit suspension of an action — a governed exception with reason, authority and review conditions. Policy version: which rule was applied — an identity connecting a lifecycle decision to the exact governing definition.

48.3 Deletion across copies#

48.3.1 PLAIN — in simple words#

Hiding a row from a screen, marking it inactive and deleting it from a table are different actions. None automatically reaches every copy made earlier.
A search index, cache, export, replica, analytics table or backup may retain information after the original screen stops showing it.
A lifecycle workflow therefore needs a store inventory and a clear action for each store. Unknown destinations remain unknown; they cannot become completed tasks by omission.
The strongest honest completion statement names its boundary: which stores, which record identities, which actions and which checks were completed.

48.3.2 PLAIN — a picture in your head#

Mira removes a note from the counter. Dev still has a photocopy, and a courier has a photograph. The empty counter says nothing about those copies.
A checklist helps only if it includes the places the note actually travelled. Ticking every item on an incomplete checklist does not prove that no other copy exists.
Where the comparison breaks: storage engines can retain old physical representations that ordinary queries cannot read. Logical visibility and recoverability from media are separate layers.

48.3.3 PLAIN — a worked example#

The synthetic inventory for NOTE-7 contains an active table, search projection, short-lived cache and controlled export. Each task has a scoped key and a status: pending, completed, failed, blocked or unknown.
Complete the active-table deletion and search removal. Suppose cache invalidation fails and the export owner has not confirmed its action. The overall status is not complete, despite two successful tasks.
Retrying a completed task should be safe under the chosen operation contract. However, “record not found” is meaningful only after the worker has confirmed the correct tenant, store and identifier.
The companion inventory model checks these status transitions and refuses an empty inventory as proof of completion. It does not contact a cache, delete a real export or inspect an operator’s computer.

48.3.4 PLAIN — what is really happening inside#

Database deletion changes logical visibility under the engine’s rules. Physical storage reclamation may occur later and can depend on old readers, logging, compaction and maintenance.
PostgreSQL describes how deleted or superseded row versions remain until vacuum can reclaim their space. Reclaiming space is not the same claim as sanitising all historical copies or media. [S190]
SQLite’s secure_delete setting concerns overwriting certain deleted content in database pages, with documented limits including some virtual-table arrangements. It is not a promise about every journal, backup, filesystem snapshot or external copy. [S191]

48.3.5 TECHNICAL — the engineer’s version#

Separate logical deletion, application inaccessibility, engine-level reclamation and media sanitisation in both requirements and evidence. A single Boolean deleted column cannot represent every layer honestly.
NIST SP 800-88 Revision 2 describes media sanitisation in terms of making target data access infeasible at a stated effort level. It is a media-management reference, not proof that an application row deletion has fulfilled every lifecycle requirement. [S192]
A deletion ledger can itself become sensitive. Keep only the identifiers and evidence necessary for its purpose, restrict access and give that ledger a lifecycle too.

48.3.6 WORDS — remember these#

Logical deletion: removal from ordinary active data — a change to the database’s visible record state. Sanitisation: rendering target media data infeasible to recover under a stated standard — a different layer from an empty application query. Store inventory: the declared destinations to address — an explicit set whose completeness must be assessed, not assumed.

48.4 Derived data and lineage#

48.4.1 PLAIN — in simple words#

Derived information is built from other information: a daily total, search document, recommendation feature or cleaned export. Understanding its origins helps us correct or retire it.
Some transformations can be updated by removing one contribution. Others need recomputation or a carefully defined approximation. The transformation determines the work.
A derived result can remain identifying or sensitive even after obvious names disappear. Its meaning, granularity and joinability matter.
Lineage is a map of relationships, not evidence that the mapped action has already happened.

48.4.2 PLAIN — a picture in your head#

A recipe card lists ingredients used in a cake. It tells Mira which batches might contain one recalled ingredient.
The card does not remove the ingredient from a baked cake. The appropriate action might be withdrawing or replacing the whole batch rather than trying to subtract it.
Where the comparison breaks: some digital aggregates can be adjusted exactly from sufficient retained contributions, while others cannot. A count, median and trained model have different update mechanics.

48.4.3 PLAIN — a worked example#

A synthetic report stores sum=300 and count=3 for contributions 50, 100 and 150. Removing the 100 contribution gives sum=200 and count=2, so the mean remains 100. Keeping only the original mean would not generally permit this update.
A median stored without its contributing values cannot generally be corrected by subtracting one number. The system needs enough retained state or a recomputation path under the applicable lifecycle policy.
A search projection that embeds NOTE-7 must be rebuilt or removed under its own index update rules. A later successful source query does not demonstrate that the searchable projection is gone.
A lineage record should identify source versions, transformation version and output identity. “Produced by yesterday’s job” is insufficient when the job was rerun with different inputs.

48.4.4 PLAIN — what is really happening inside#

A transformation may combine many records and create new identities. Lifecycle propagation requires a mapping from the affected source scope to the corresponding outputs or a method of rebuilding all affected outputs.
Shared outputs raise policy questions. Retiring one person’s optional note need not delete unrelated aggregate facts, but the retained result needs an independently justified meaning and risk assessment.
Data written outside instrumented pipelines may be absent from automated lineage. Manual exports and debug copies must not disappear from the inventory simply because a lineage tool did not observe them.

48.4.5 TECHNICAL — the engineer’s version#

OpenLineage run and dataset metadata can document transformation relationships. The record must still be populated correctly, and incomplete instrumentation yields incomplete observations. [S193]
Distinguish exact incremental maintenance from approximate correction. An exact sum/count representation supports the stated example; a sketch or model requires its own update and error contract.
Do not infer model unlearning, anonymisation or comprehensive deletion from a successful DELETE statement. Those claims need methods and evidence specific to the retained representation.

48.4.6 WORDS — remember these#

Lineage: where a result came from — recorded relationships among inputs, transformations, executions and outputs. Incremental maintenance: updating a result from a change — applying a justified delta instead of recomputing everything. Recomputation: rebuilding from an authorised source set — creating a new derived result under a recorded transformation version.

48.5 Backups and exceptions#

48.5.1 PLAIN — in simple words#

Backups preserve older states for recovery. That purpose can conflict with an expectation that an active-data deletion instantly reaches every historical snapshot.
State the policy for retained backups explicitly: access restrictions, expiry, permitted recovery use and what happens to previously deleted information after restoration.
Restoring a backup can reintroduce information removed since the backup was made. Recovery therefore needs a lifecycle reconciliation step, not only an integrity check.
An exception is not permission for arbitrary reuse. A restricted retained copy should not quietly become a normal source for analytics or customer support.

48.5.2 PLAIN — a picture in your head#

Mira seals an old notebook in a recovery box. It contains a note later removed from the working notebook.
Opening the box after a flood restores useful history, but also restores that old note. The recovery procedure must know which later lifecycle decisions to reapply.
Where the comparison breaks: encrypted backups, key escrow, incremental chains and storage snapshots complicate selective removal. A practical policy must match the actual storage architecture rather than assume every copy is independently editable.

48.5.3 PLAIN — a worked example#

A synthetic backup is taken at checkpoint B-20. NOTE-7 is retired afterward under lifecycle action D-21. Restoring B-20 without considering D-21 can bring the note back into active use.
In the exercise design, the recovery process restores into isolation, loads the authorised post-backup lifecycle decision set, reapplies applicable actions and checks the result before serving traffic.
The lifecycle decision record must contain enough information to prevent reintroduction while not preserving unnecessary original note content. Its access and retention need separate rules.
If the recovery team lacks the required decision set, it records that gap and keeps the affected restored data out of ordinary use until resolved. It does not mark the lifecycle work complete because the database starts successfully.

48.5.4 PLAIN — what is really happening inside#

Backup policies describe retained states and recovery paths; lifecycle policies describe permitted uses and actions. The two have to be designed together.
Deleting one encrypted key can make selected ciphertext unavailable only under strict assumptions about key copies, derivation, plaintext copies and scope. Destroying a shared key can also make unrelated necessary records unrecoverable.
Record exceptions with their scope, owner, reason and review conditions. The system should distinguish retained-but-restricted from active-and-permitted, rather than hiding both behind one deleted flag.

48.5.5 TECHNICAL — the engineer’s version#

Cryptographic erasure is a key-management and sanitisation claim with prerequisites, not a generic synonym for deleting a row. NIST’s sanitisation guidance provides the relevant media/key context; it does not validate our application example. [S192]
The companion’s restore test uses a fresh SQLite destination and synthetic data. It demonstrates logical state comparison and a subsequent lifecycle decision; it does not test cloud retention locks, key destruction or physical-media recovery.
Evidence should identify restored backup, recovery time boundary, applied lifecycle actions and unresolved retained copies. A broad “everything deleted” certificate would exceed those observations.

48.5.6 WORDS — remember these#

Reintroduction: retired information returning through recovery — restoration of an older state without later lifecycle decisions. Restricted retention: kept for a bounded exception — retained data whose access and use differ from ordinary active records. Cryptographic erasure: making protected ciphertext unusable by sanitising necessary key material — a claim dependent on key scope and remaining copies.

48.6 Evidence of completion and remaining limits#

48.6.1 PLAIN — in simple words#

Completion evidence should describe what was attempted, what succeeded and what remains unresolved. A reassuring message without those details is not a reliable record.
Each action needs a target identity, policy basis, execution outcome and suitable check. A failed worker and a completed worker are not interchangeable because both stopped running.
Keep the certificate narrower than the evidence. “Removed from these three active stores” can be supported while “no copy exists anywhere” cannot.
When new destinations are discovered, the inventory and outstanding work change. A prior completion result may remain historically true for its old scope without being sufficient for the expanded scope.

48.6.2 PLAIN — a picture in your head#

Mira signs a checklist saying she inspected rooms A, B and C. The statement should not say she inspected the entire building if she never entered room D.
If room D is discovered later, she adds a new inspection task rather than erasing the history of what she actually checked.
Where the comparison breaks: absence is often harder to observe in distributed stores than presence. Search results may be stale, access-limited or scoped incorrectly, so the verification method matters.

48.6.3 PLAIN — a worked example#

A synthetic completion record for NOTE-7 states tenant T-A, policy version RP-3, inventory version INV-2 and three completed active-store tasks. It separately lists one backup retained under a restricted exception until its configured expiry.
That record supports “active-store actions completed under INV-2; restricted backup remains.” It does not support “all copies permanently erased.”
A task marked unknown prevents the inventory model from reporting complete. A task marked blocked requires its exception to remain visible. Empty task sets are not treated as successful comprehensive action.
The companion tests those status rules and checks their deterministic outputs. It cannot verify an unobserved third-party store; its evidence is deliberately limited to the model and local exercise.

48.6.4 PLAIN — what is really happening inside#

Lifecycle work is a state machine with evidence attached to transitions. Repeated delivery and retries should preserve action identity without manufacturing additional authority.
An append-only audit trail can retain action outcomes while the original content disappears. The trail’s identifiers, actor information and reasons still need access and retention controls.
Operational review compares expected destinations with observed executions, reconciles gaps and assigns follow-up responsibilities. The useful result is a bounded, inspectable claim rather than an absolute adjective.

48.6.5 TECHNICAL — the engineer’s version#

A completion predicate can require a nonempty declared task set and successful verification for every required task, while reporting exceptions separately. The task set’s completeness remains an external premise.
Store the evidence method, not merely a Boolean: query scope, target version, execution result and timestamp, with sensitive values minimised. Confirm that the check itself ran under authority capable of seeing the intended target.
Publication of a deletion receipt does not create proof of independently controlled copies. Separate controllable scope, verified scope, restricted retention and unknown scope in the final statement.

48.6.6 WORDS — remember these#

Completion predicate: the exact rule for saying an action is done — a condition over declared tasks, evidence and exceptions. Verification scope: what a check could actually observe — the stores, identities, versions and authority included in the observation. Residual copy: information remaining after selected actions — a retained, failed, blocked or unknown destination requiring explicit treatment.

48.97 Practice and worked answers#

Question: Does hiding NOTE-7 from a screen remove its search projection? Answer: Not unless the projection’s own update or deletion path is completed and checked.
Question: Is the example’s thirty-day period a legal recommendation? Answer: No. It is invented elapsed-time arithmetic for teaching policy implementation.
Question: Why specify the retention trigger? Answer: Creation, closure and last activity can produce different expiry dates and different permitted actions.
Question: Can an unchanged aggregate mean prove that no contribution was removed? Answer: No. In the sum/count example, removing 100 from 50,100,150 leaves the mean at 100 while changing the population.
Question: Does an empty deletion-task list establish complete erasure? Answer: No. The inventory may be missing; the model rejects that vacuous conclusion.
Question: What risk follows restoring B-20 after action D-21? Answer: An older copy can reintroduce retired information unless the recovery workflow reapplies relevant later lifecycle decisions.
Question: Does vacuum establish media sanitisation? Answer: No. Space reclamation and physical recoverability are different claims.
Question: What should a completion record say when a restricted backup remains? Answer: Name the completed active scope and disclose the retained backup exception and its governing conditions.

48.98 Common wrong ideas#

Wrong: Retention is one period for the whole database. Right: Different record classes can have different purposes and rules.
Wrong: Missing timing means the record is old enough to delete. Right: Unknown timing requires a declared unresolved-data policy.
Wrong: Deleting a row reaches every copy. Right: Derived and historical stores require explicit treatment.
Wrong: A removed name makes every remaining record anonymous. Right: Joinability, granularity and context can preserve identifying meaning.
Wrong: A lineage map executes deletion. Right: It records relationships; execution and verification are separate.
Wrong: A backup restore is complete when the engine starts. Right: Later lifecycle and access decisions may need reconciliation.
Wrong: A deletion ledger can safely keep everything forever. Right: The ledger also has purpose, access and retention requirements.
Wrong: An absolute erasure claim follows from a few successful queries. Right: State the stores and observation boundary actually verified.

48.99 Chapter summary in 20 lines#

Give each record class a purpose and an owner.
Separate optional annotations from independently required facts.
Minimise unnecessary fields and downstream copies.
Define the event that starts a retention clock.
Distinguish elapsed durations from calendar rules.
Govern exceptions with reasons and review conditions.
Recheck changing eligibility at the action boundary.
Hiding, logical deletion and sanitisation are different actions.
Inventory caches, indexes, exports, analytics and backups.
Unknown destinations do not count as completed work.
Record the transformation history of derived outputs.
Preserve sufficient state for justified incremental corrections.
Recompute when a simple subtraction is not valid.
Do not infer anonymity or model unlearning from a removed field.
Make backup retention and permitted use explicit.
Prevent restored historical data from bypassing later lifecycle decisions.
Treat key destruction as a scoped, prerequisite-dependent claim.
Give lifecycle evidence its own access and retention policy.
Report completed, restricted and unresolved scope separately.
A completion statement must not exceed its actual evidence.

Return to contents