How would you set snapshot retention for lakehouse tables across audit, rollback and cost?

## Frame it as an objective, not a number The weak answer is "seven days, it's the default." The strong answer starts by naming what retention buys — the ability to return to, reproduce, or audit a past table state — and what it costs — storage for every file that only history still references, plus metadata for every retained version. Then it derives the number from requirements on both sides. ## The floor: detection time How long does a bad write take to notice? Three honest patterns: - A pipeline with data-quality gates that fail the run: minutes to hours. - A table watched only through a dashboard someone opens on weekday mornings: up to a long weekend. - A table reconciled at month end: weeks. Retention must exceed the realistic detection time with margin, because recovery *after* the horizon means reconstruction from sources, not rollback. This is the single most useful sentence in the whole answer: **the retention window is the rollback window**. ## The four ceilings **Storage cost.** Files stay alive while any unexpired snapshot references them. Update-heavy and compacted tables are the expensive cases: a nightly compaction rewrites live data every night, and every rewritten predecessor is pinned until the snapshot that references it expires. Thirty-day retention on such a table can hold many multiples of its live size. Estimate it per table rather than assuming history is nearly free — it is nearly free only for append-only tables that are never rewritten. **Version and metadata volume.** A streaming job committing every minute produces on the order of a thousand-plus versions a day. Retention expressed only in days ignores this; healthy policies bound both **age** and **count**, because metadata growth degrades planning long before storage becomes alarming. **Longest-running reader.** Expiry that deletes files under a scan pinned to an expiring snapshot kills the query. Retention comfortably longer than your slowest query, plus scheduling maintenance away from heavy read windows, prevents an entirely self-inflicted incident class. **Deletion deadlines.** Deleting a row does not remove it from files that unexpired snapshots still reference. If you have committed to erasing personal data within a fixed period, retention on the affected tables must be shorter than that period, or the policy must include a forced expiry step for those tables. This is the constraint people most often discover late, and it cuts directly against the audit argument for long history. ## Tier, don't standardise One number across a platform is always wrong somewhere. A workable set of tiers: - **Raw / streaming ingest**: short — a day or a few days. Replayable from the source, high commit rate, expensive to keep. Bound by version count as well as age. - **Curated / derived tables**: medium — long enough to cover the detection window for the pipelines feeding them, typically one to two weeks. - **Serving tables with published, reproducible outputs**: longer, and paired with pinned versions rather than blanket retention. - **Regulated tables**: driven by the audit requirement, with the storage cost accepted explicitly and the deletion deadline reconciled against it. ## Exceptions instead of blanket extensions When one version must stay reproducible — a model's training set, a filed report — protect *that version* where the format supports pinning or naming snapshots so expiry skips it, or copy the state into a separate table. Extending table-wide retention to preserve one point also pins every intermediate version's files, which is the expensive way to buy one thing. ## Operationalising it Retention is one of three separate knobs, and conflating them causes incidents: **retention** (how far back you can travel), **maintenance frequency** (how often expiry and compaction run), and the **orphan-file age threshold** (safety margin, which must exceed the longest in-flight write). Set them independently, apply them as table properties owned by the platform rather than by whoever wrote the pipeline, and monitor the outcome: total storage versus live data, file counts, retained version counts, and oldest available version per table. Alert when the oldest available version drifts inside a stated recovery objective — that is the signal that a schedule change has silently shortened someone's rollback window. ## Rehearse the recovery A retention policy justified by rollback should be tested by an actual rollback drill on a real table. Teams routinely discover that the version they would return to predates a schema change, or that the downstream consumers need re-running too. Retention only buys the *option* to recover; the runbook is what turns it into a recovery.