GovernanceGENERALFORMAT-SPECIFICBROKER-SPECIFICORG-SPECIFIC

Data Retention

How long should this dataset exist? A retention horizon is simultaneously a recovery window and a liability window, and the two want opposite numbers.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

How long should this dataset exist, and what stops working on the day it is deleted?

Who needs this

Everyone who will need the data during the window and nobody after it, which is the problem — the people who benefit from long retention are visible and vocal, and the people who benefit from short retention are a future incident responder and a regulator, neither of whom is in the room when the number is chosen.

What one row is

Retention attaches to a partition or object, not to a table, because that is the unit deletion can actually operate on. A table-level retention expressed against a date-partitioned dataset is implemented as "drop partitions older than N", and a dataset that is not partitioned by time usually cannot be aged at all without a rewrite (Partitioning).

The obvious build

Set a retention policy per system: the warehouse keeps everything, the lake keeps everything, logs keep ninety days because that was the default, and backups keep whatever the backup tool was configured with. Nobody has ever been fired for keeping data, and deleting it feels like the risky operation.

Why it breaks

The retention that matters is the shortest one in the chain, and nobody computed it. If raw expires at thirty days, your maximum backfill range is thirty days regardless of how long the warehouse keeps its output (Planning a Backfill).

How it breaks with real data

The retention that matters is the shortest one in the chain, and nobody computed it. If raw expires at thirty days, your maximum backfill range is thirty days regardless of how long the warehouse keeps its output (Planning a Backfill).
A lifecycle rule was attached to a bucket prefix. The ingestion job's output path changed when a new source version shipped, and objects have been accumulating under the new prefix, ungoverned, ever since (Storage Lifecycle).
Deleting a partition from a table format is a metadata operation that hides the data; the files remain until snapshots expire and compaction rewrites them. The bytes are still there and still readable by anything with file access (Open Table Formats).
Backups have their own retention, usually longer than production, and a "deleted" dataset is fully present in a restore point for months afterwards (Backup Strategy).
A downstream mart depends on eighteen months of a fact table that is retained for twelve. The dependency is invisible until the first month where the mart is quietly built from less data than it needs (Impact Analysis).
Retention was set on the modelled table and not on the extracts, the scratch tables and the BI caches derived from it, so the data expires in the governed copy and persists in six ungoverned ones (PII in Pipelines).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

A retention horizon answers two different questions with one number. As a recovery window it says how far back you can reprocess, restate a metric, investigate an incident or answer a historical question. As a liability window it says how long you must protect, classify, mask and be able to delete this data, and how much of it appears in the scope of a breach.
These pull in opposite directions and neither has a natural stopping point, so a horizon chosen without naming both is a horizon chosen by whichever consideration the author happened to be thinking about.
Retention is enforced by four different mechanisms that fail independently: object-store lifecycle rules on a prefix, table or partition TTLs in a warehouse, snapshot expiry plus compaction in a table format, and broker retention on a log. A dataset that flows through all four has four separately configured clocks (Retention and Replay).
There is a difference between logical deletion and physical deletion that matters enormously here. Dropping a partition, expiring a snapshot and deleting an object are three different events, sometimes separated by weeks, and only the last one removes bytes (Compaction: The Merge That Pays for Cheap Writes).
Retention interacts with immutability in the layers designed to be immutable. Raw is immutable by design, log segments are immutable, Parquet files are immutable — and the only way to remove one record from an immutable file is to rewrite the file (Deletion Requests).
Legal hold inverts everything: an obligation to preserve specific data overrides the schedule that would delete it, and a platform with no way to suspend expiry for a subset has a real problem the first time it is asked.

One number answering two questions

ORG-SPECIFICThe relative horizons shown are a shape rather than a recommendation: a regulated financial platform and a consumer product with a short analytical memory will place these in very different places, and only the ordering argument — raw shorter than modelled — transfers.

Ask why a dataset is retained for its current horizon and the answer is almost always historical: it was the default, or someone picked it, or it is what the previous platform did. Ask what the number should be and the conversation immediately splits into two arguments that never quite meet, because retention is doing two jobs.

As a recovery window, the horizon is the maximum range over which you can reprocess. Every backfill, every metric restatement, every "what really happened in March" investigation and every schema-change repair reads upstream data, and if that data has expired the repair is impossible rather than expensive (Backfills).

As a liability window, the horizon is how long you carry the obligation. Everything in scope must be classified, access-controlled, masked, reachable by deletion tooling and included in the scope of any incident that touches its storage. That cost is continuous and it ends exactly when the data does.

The table below is the decomposition per layer. Note that the two columns want opposite numbers on every row, and that the layer where they conflict most sharply is raw — which is the layer most platforms retain longest without deciding to.

Layer	What longer retention buys	What longer retention costs	Typical shape
Source system	The last resort when your own copies are gone — and the only copy with authoritative current state.	Not yours to decide. The source team optimises for operational needs and will shorten it without telling you.	Shortest of all, and outside your control. Never plan a recovery around it (Source of Truth).
Broker / event log	Replay from the log: rebuild any downstream state without touching the source.	Storage on the broker, and full unmodified payloads sitting in a system with its own access model.	Bounded by design. On a compacted topic, time-based retention does not apply to the latest record per key (Retention and Replay).
Raw landing zone	The maximum backfill range. Everything reprocessable is reprocessable from here.	The least filtered, least classified, most complete copy of everything — the highest-liability store on the platform.	Where the conflict is sharpest. Size it to the realistic backfill range, not to "forever" (The Raw Landing Zone).
Modelled tables	Historical analysis, year-over-year comparison, trend and cohort work.	Moderate: these are minimized, classified and masked, so the obligation per row is lower.	Long. This is the layer that should hold your history, precisely because it is the governed one.
Marts and extracts	Fast answers to specific questions over a specific range.	Copies with their own access models, frequently outside the deletion tooling.	Short, and derivable. A mart should be rebuildable rather than retained (Data Marts).
Logs and audit trails	The ability to answer "who read this, when" long after the fact.	High volume, and access logs themselves contain query text and identifiers.	Longer than instinct suggests — the audit question always arrives after the horizon you first picked (Audit Logs for Privileged Actions).
Backups and snapshots	Disaster recovery, which is a different requirement from history.	A full copy of everything, including everything you deleted, under a separate access model.	Independent of everything above, and the most common reason a deletion is incomplete (Backup Strategy).

The chain minimum nobody computed

A platform's reprocessable range is not the retention of any one store. It is the minimum along the path a rebuild would have to read, and because each store's horizon is configured by a different person in a different system, almost nobody knows what their own minimum is.

The practical version: to rebuild fct_orders for March you need raw for March, which needs the broker to have had it when raw was written, and if your fix requires re-deriving from the source you need the source to still hold it. The range you can actually repair is the smallest of those, and it is usually much shorter than the warehouse's own retention suggests.

Publish the number. "This platform can reprocess N days" is a single derived value, it belongs in the platform's documentation next to its freshness SLOs, and computing it usually surprises the team that owns it (Pipeline SLOs).

What breaks on the day retention fires

Trigger	Symptom	Cause	Response
Raw expires at its horizon while a bug in a transformation is still undiscovered.	A metric is wrong for a period that can no longer be recomputed from source data.	The raw horizon was sized for storage cost rather than for the realistic time-to-discovery of a logic bug.	Size raw to time-to-discovery plus repair time, not to a round number; keep a narrow, long-retained reconciliation extract that makes restatement possible even when full reprocessing is not.
A monthly mart is rebuilt from an eighteen-month window of a twelve-month table.	The mart builds successfully with less input than it needs; the trend line bends at the boundary.	Retention was set per table with no visibility of downstream range dependencies.	Derive range dependencies from lineage and treat a downstream window longer than an upstream horizon as a build-breaking error (Impact Analysis).
A lifecycle rule is attached to a prefix that the writer no longer uses.	Storage grows steadily; the retention job reports success every run.	Write path and expiry rule are configured in different systems and changed independently.	Define both in the same configuration; alert on oldest-object age per path rather than on job exit status.
Partitions are dropped from a table-format dataset with no snapshot expiry scheduled.	Queries return nothing for the period; storage is unchanged; the files are still readable directly.	Logical deletion at the metadata layer was mistaken for physical deletion of bytes.	Schedule snapshot expiry and compaction as first-class jobs, monitor them, and verify deletion by measuring bytes rather than by querying the table (File Compaction).
A horizon is shortened to reduce exposure.	Immediate, irreversible deletion of everything older than the new value, including a range something depended on.	Shortening retention is a deletion and was reviewed as a configuration change.	Treat any downward change as a deletion: named approver, lineage check for dependent ranges, and a staged reduction rather than a single step.
A legal hold arrives for a subset of subjects.	No mechanism to suspend expiry selectively; the choice is all-or-nothing.	Hold was never a requirement when retention was implemented, and expiry operates on partitions rather than on subjects.	Build selective suspension before it is needed: a hold list consulted by every expiry job, auditable and reversible.

Making expiry observable

WAREHOUSE-SPECIFICFunction names differ — datediff, date_diff, min_by, argMin and the metadata tables that expose partitions and storage inventory are all engine-specific — but every warehouse exposes partition metadata and every object store exposes an inventory, so the three checks port even though the SQL does not.

Retention is the only pipeline in a data platform whose failure produces *more* data rather than less, which means every detector built for the rest of the platform is blind to it. No freshness check fires, no volume anomaly triggers, no consumer complains. The dataset simply keeps growing, correctly, forever (Volume Anomalies).

The signal that works is age, not job status. For every governed path, the age of its oldest object should sit just under the horizon and stay there. A rising oldest-age is the unambiguous symptom of expiry not running, and it is the only check that cannot be satisfied by a successful run against the wrong target.

The second signal is the mirror: the age of the *newest* object under a path that should be dormant. If a writer moved and the lifecycle rule stayed behind, the old path stops growing and the new one accumulates ungoverned — and only a check that looks for writes where none are expected will see it.

Retention posture, expressed as data tests over metadata

1-- 1. Is expiry running? Oldest partition age per governed dataset,
2--    compared against the horizon declared in the catalog.
3select
4    d.dataset,
5    d.retention_days,
6    datediff(day, min(p.partition_date), current_date) as oldest_partition_age_days,
7    datediff(day, min(p.partition_date), current_date) - d.retention_days as days_over
8from catalog_datasets d
9join dataset_partitions p on p.dataset = d.dataset
10group by d.dataset, d.retention_days
11having days_over > 0;   -- any row here is expiry not doing its job
12 
13-- 2. Is the rule pointed at the right place? A path that should be
14--    dormant but is still receiving writes means the writer moved.
15select path, max(written_at) as newest_write
16from storage_inventory
17where path in (select path from retention_rules where expected_dormant)
18group by path
19having newest_write > dateadd(day, -7, current_date);
20 
21-- 3. What is the chain minimum? The reprocessable range of a serving
22--    dataset is the smallest horizon anywhere on its lineage path.
23select
24    l.serving_dataset,
25    min(d.retention_days) as reprocessable_days,
26    min_by(d.dataset, d.retention_days) as binding_constraint
27from lineage_paths l
28join catalog_datasets d on d.dataset = l.upstream_dataset
29group by l.serving_dataset;

The third query is the one worth running first. Its output is a number most platforms have never computed, and it is almost always smaller than the team expects — the binding constraint is usually the broker or the raw layer, not the warehouse everyone thinks of.

How to build it

Most important first.

Set retention per layer, and set raw shortest of the layers that can bear it. Size the raw horizon to the realistic maximum backfill range plus a margin, and say what that range is (Keeping Raw History: The Recovery Position and the Liability).
Compute the chain minimum explicitly and publish it. "This platform can reprocess N days" is a single number derived from the shortest retention on the path, and almost no platform knows its own.
Attach lifecycle rules to the same configuration that defines where a job writes, so that changing the write path and changing the expiry are one change and one review (Infrastructure as Code).
Distinguish and monitor logical from physical deletion. Expiring snapshots and running compaction are the steps that actually remove bytes in a table format, and both are scheduled jobs that can fail quietly (File Compaction).
Give every non-modelled store a horizon too: scratch schemas, quarantine tables, dead-letter queues, BI extract caches, notebook output prefixes and log stores. These are the ones with no owner and therefore no default (PII in Pipelines).
Build legal hold before you need it: a mechanism to suspend expiry for an identified subset, auditable and reversible. Retrofitting it during a live matter is not a good time to design it.
Alert on the retention job, and alert on its result rather than its exit code — oldest object age per governed path is the signal, because a successful run against the wrong prefix looks identical to a correct one (Pipeline Observability).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A lifecycle rule guarantees eventual deletion of objects matching its prefix and age condition, on the provider's schedule rather than instantly. It guarantees nothing about objects outside the prefix, including ones written after a path change.
A partition drop guarantees the partition is no longer visible to queries through that table. Whether the underlying files are gone depends on the table format and on whether snapshot expiry has run (Open Table Formats).
Broker retention guarantees segments older than the horizon become unavailable to consumers. On a compacted topic the guarantee is different in kind — the latest value per key is retained regardless of age, which means a compacted topic is not aged by time at all (Retention and Replay).
No retention mechanism covers backups, snapshots or replicas unless it was configured to. The default is that they outlive the data they copy (Backups Are Sensitive Data Copies).
Nothing guarantees a consumer can survive the deletion. Expiry has no notion of downstream dependency, and the first indication that a mart needed eighteen months is a metric that changed shape.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The horizon check: for every governed path, query the age of the oldest object or partition and compare it against the stated retention. It catches expiry that is not running, rules attached to the wrong prefix, and paths that were never covered.
It misses everything in a copy the check does not know about — backups, replicas, BI caches and any extract — and it misses logical-versus-physical deletion entirely, because a partition that is invisible to queries has no age the query can see.
A second check is worth having: for every dataset, the age of the *newest* object under paths that should have stopped being written to. A non-zero result means a writer moved and the lifecycle rule did not follow it.

Freshness

Retention is the opposite end of the axis from freshness: it sets how far back a consumer can ask rather than how recently data arrived. Both are properties of the same dataset and both belong in its contract (Data Contracts).
The deletion itself has latency. Lifecycle rules act on a provider schedule, snapshot expiry runs when the job runs, and compaction runs when it runs — so "deleted" is a state a dataset reaches over a period rather than at an instant.
A shortened horizon takes effect immediately and retroactively, which is what makes it the most dangerous configuration change in this area. Extending is safe; shortening deletes.

When the schema or meaning changes

Changing a horizon downward is a deletion, and it should be reviewed as one: named approver, stated reason, and a check for downstream dependencies on the range being removed (Impact Analysis).
A schema change does not usually affect retention, but a change to the partition column does — retention implemented as "drop partitions older than N" silently stops working if the partitioning scheme changes (Partitioning).
A new regulatory requirement can change a horizon retroactively in either direction: a maximum retention forces deletion of what you hold, a minimum retention forbids the expiry you already configured. Both arrive as a surprise, which is the argument for horizons being configuration rather than code (Data Governance).

How to re-run this safely

Deletion is the operation with no undo. Before shortening a horizon or running a one-off cleanup, confirm the range is not the input to any model — the check is a lineage query, and it takes minutes (Data Lineage).
If data expires that was needed, the only recovery paths are a backup that has not itself expired, or the source system, which usually has a shorter horizon than you do. Check both before assuming recovery is possible (Restore Testing).
A retention job that has been failing silently is recoverable in the sense that nothing was lost — you have too much data rather than too little. Treat it as an incident anyway, because the over-retained period is a liability with a known start date.

What can go wrong

Failure modes

The retention job fails for months and nobody notices, because expiry is the one pipeline whose failure produces no missing data and no downstream error.
The lifecycle rule points at a prefix nothing writes to any more. The rule succeeds, the metrics look fine, and nothing has been deleted since the path changed.
Deletion is logical only: partitions are dropped, files are retained until a snapshot expiry that was never scheduled, and the bytes remain fully readable (Open Table Formats).
A horizon is shortened to reduce exposure, and it deletes the range a monthly restatement depends on. The deletion succeeds; the restatement fails a month later.
Backups retain everything for longer than production, so every deletion is undone in the restore path and nobody accounted for it (Backup Strategy).
Legal hold arrives, there is no mechanism to suspend expiry for a subset, and the choice is between suspending all deletion platform-wide or missing the hold.
A compacted topic is assumed to be aged by its time-based retention and is not, so the latest record per key persists indefinitely (Retention and Replay).

Misreads

"We keep everything, so we can always reprocess." Only if everything is still readable. Retention on the broker, on the raw prefix and in the source all bound this independently, and the smallest one wins (Replay from the Log).
"Deleted means gone." Dropping a partition, expiring a snapshot and deleting an object are three separate events. Until the last one, the bytes are readable by anything with file access (Open Table Formats).
"Retention is a storage cost decision." It is a liability decision that also affects storage cost. Framing it as cost optimisation is how horizons get set by whoever is looking at the storage line that quarter.
"Backups are not in scope." Backups are a full copy with an independent horizon, and they are the most common reason a deletion is incomplete (Backups Are Sensitive Data Copies).
"The warehouse retains for seven years, so we have seven years of history." You have seven years of whatever the warehouse holds, which was built from raw that expired long ago and cannot be rebuilt (Keeping Raw History: The Recovery Position and the Liability).

Privacy, retention and access

Retention is where over-collection turns into over-exposure. A field kept forever appears in every future incident, every deletion request and every access review, and horizon is the only lever that bounds all three at once (Data Minimization).
A retention decision must name both windows explicitly — the recovery range it preserves and the liability period it accepts — with an owner for each. A horizon with only one of the two written down is a horizon that will be argued about annually and never changed.
Legal hold, deletion requests and retention are one mechanism, not three. All of them are "make specific data stop existing, or specifically keep existing, on a schedule", and platforms that build them separately end up with three partly-working systems (Deletion Requests).

Operating it

How you see it in production

Oldest partition or object age per governed path, against its stated horizon. One query per path, and the only real proof that expiry works.
Bytes deleted per retention run, per path. A run that deletes zero bytes for several consecutive periods is either correct or completely broken, and only this metric plus the age metric together can tell you which.
Chain minimum retention across each lineage path — the maximum reprocessable range for each serving dataset, computed rather than assumed (Lineage Debugging).
Count of datasets with no retention rule at all, which is the coverage number underneath everything else here (The Data Catalog).

What changes at 10x and 100x

At 10x volume, retention becomes the main lever on storage cost, and the layers where it is shortest matter most because raw is usually the largest layer.
At 100x, physical deletion becomes a scheduling problem: rewriting files to remove records competes with the compaction and ingestion the platform already runs (File Compaction).
More datasets scales the configuration surface, and configuration is where this fails — one rule per path, several paths per dataset, and no single place to see them all (Drift: When the File and Reality Disagree).
More jurisdictions turns one horizon into several for the same logical dataset, which usually forces retention to become a row-level property and therefore a partitioning decision (Row and Column Security).

What drives cost here

Retained bytes multiplied by horizon is the direct driver, and it is the one that responds most directly to tiering — moving cold partitions to colder storage classes changes the slope without changing the horizon (Storage Lifecycle).
Long retention also drives query cost indirectly: a table with more history is a table where an unpartitioned scan reads more, and consumers habitually write queries without date predicates (Scan Cost).
Physical deletion has its own compute cost. Removing records from immutable files means rewriting them, and a deletion-heavy workload against a large table is a compaction workload (File Compaction).
Over-retention costs are asymmetric and lumpy: they are small and continuous until an incident or an audit, at which point they are the incident's scope.

What this approach costs

Short retention reduces exposure and bounds your recovery. The bound is exact and unforgiving: a bug discovered outside the raw horizon cannot be fixed from raw, and the only remaining option is a modelled-layer reconstruction that may not be possible (What Backfills Break).
Long retention preserves optionality and grows the incident scope, the deletion-request cost and the storage bill, all of which compound quietly.
Per-layer tiering is the right answer and costs you a more complicated mental model: consumers must understand that the same logical data has different horizons at different layers, and that the answer to "how far back can we go" depends on which layer they mean.
Legal hold machinery is pure overhead until the day it is not, and building it later is measurably harder than building it early.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe double framing — a horizon is simultaneously a recovery window and a liability window — and the chain-minimum property hold everywhere, because they follow from the fact that reprocessing reads upstream data that has its own clock.
FORMAT-SPECIFICWhether deleting a partition removes bytes depends entirely on the table format: Iceberg and Delta hide data at the metadata layer and require snapshot expiry plus rewrite to physically remove it, whereas a plain directory of Parquet files under a lifecycle rule deletes objects directly.
BROKER-SPECIFICTime-based retention and compaction are different mechanisms: a compacted topic retains the latest record per key indefinitely regardless of the time horizon, so a platform that assumes a topic ages out by time will retain identifying keys forever without noticing.
ORG-SPECIFICMaximum and minimum retention periods, what triggers a legal hold and which datasets are subject to which regime are legal determinations that vary by jurisdiction and contract; the engineering is identical and the numbers are not transferable between organisations.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasecompaction partitioning-and-sharding

Securitybackup-security audit-logs least-privilege risk-and-residual-risk

Cloudstorage-lifecycle backup-strategy rpo-and-rto restore-testing disaster-recovery

Domains that do not exist yet

— DevOps / Production Engineering owns lifecycle rules and backup configuration as infrastructure code. Retention that is not expressed in the same repository as the pipeline that writes the data will drift apart from it, and that drift is the most common cause of ungoverned storage.
— Distributed Systems owns why replicas and snapshots in other regions have independent lifetimes, and why a deletion that is durable in one region may not yet be durable in another.