Data Retention, Archival & Tiered Lifecycle: Hot/Warm/Cold, Legal Hold & Cost

data retention is the policy that quietly decides whether your storage bill grows linearly forever, whether an auditor's "produce every record from Q3 2021" request takes ten minutes or ten weeks, and whether a right-to-erasure demand collides head-on with a seven-year legal hold — and it is the design decision senior data engineers most often defer until the S3 invoice or the compliance email forces the conversation. Every byte your pipelines land has a lifecycle: it is born hot (queried constantly), cools to warm (queried occasionally), freezes to cold (queried almost never but still legally required), and eventually either expires or gets locked under a hold. Get the lifecycle policy wrong and you pay hot-storage prices for data nobody has read in three years, or worse, you purge a record the day before a subpoena lands.

This guide is the senior-data-engineering walkthrough for designing a retention program end to end, framed the way interviewers actually probe it: the hot warm cold tiering model that maps access frequency to storage class, the declarative lifecycle policy and TTL automation that ages and purges data without a fragile cron job, the archival and restore economics of cold storage where a Glacier retrieval is a job with a latency and a bill, and the legal hold plus compliance-deletion rules where a hold always overrides a TTL and GDPR erasure sometimes means shredding an encryption key rather than the rows. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the system-design practice library →, rehearse the cost trade-offs on the optimization practice library →, and sharpen the pipeline mechanics on the data-processing practice library →.

---

On this page

• Why the retention policy determines everything downstream
• Hot / warm / cold tiering
• Lifecycle policies and TTL automation
• Archival, restore, and cold-storage economics
• Legal hold, compliance retention, and deletion
• Cheat sheet — data retention & lifecycle recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the retention policy determines everything downstream

Four levers, four cost-and-liability curves — the policy you write in year one binds the invoice and the audit in year three

The one-sentence invariant: data retention is a picking exercise across four levers — which tier data lives in (hot warm cold), how it ages and purges (lifecycle policy + TTL), how it is archived and restored (cold storage), and what legal or compliance constraints lock or force its deletion (legal hold) — and each lever trades storage cost against retrieval latency, compliance exposure, and deletion guarantees in a way that is expensive and slow to undo once petabytes have accumulated. The retention rule you write when a table is small becomes the rule you fight to change when it is 40 TB, because the transition costs, the restore bills, and the accumulated legal holds all scale with the data you kept.

The four axes interviewers actually probe.

• Compliance / legal obligation. Every dataset has a minimum retention (tax records 7 years, SOX 7 years, HIPAA 6 years, some telecom logs 90 days) and, increasingly, a maximum retention (GDPR "keep no longer than necessary", CCPA). The two can conflict, and the senior answer names both floor and ceiling before touching a storage class. Interviewers open here because a retention policy set without the legal floor is a data-destruction incident waiting to happen.
• Access frequency & latency SLA. How often is the data read, and how fast must a read return? Hot data (dashboards, feature serving) needs millisecond access; warm data (ad-hoc analytics) tolerates seconds; cold data (audit, legal) tolerates minutes-to-hours. This axis drives the tier, and getting it wrong either overpays for storage or breaks an SLA.
• Storage cost curve. Cost per GB drops roughly 4–5× from hot to infrequent-access and another 4–5× to deep archive — but retrieval cost moves the opposite way. The naive "just put everything in Glacier" answer ignores that a single full-table restore of archived data can cost more than a year of hot storage. Model both storage and retrieval.
• Deletion / purge guarantee. When retention expires, can you prove the data is gone? TTL deletes are eventually consistent; a legal hold overrides expiry; GDPR erasure of data that is also under a compliance floor requires crypto-shredding, not row deletion. The guarantee — not the delete statement — is what auditors ask for.

The 2026 reality — storage is cheap, unbounded retention is not.

• Tiering is the default lever. S3 (Standard → Standard-IA → Glacier Instant/Flexible → Deep Archive), GCS (Standard → Nearline → Coldline → Archive), and Azure (Hot → Cool → Cold → Archive) all expose the same three-to-four-tier ladder. Intelligent-Tiering can automate the hot/warm decision, but cold and archive still need an explicit policy.
• Lifecycle automation replaced cron. Declarative age-based transition and expiration rules (S3 lifecycle, GCS lifecycle, table-level TTL in BigQuery / DynamoDB / Cassandra) age and purge data on the platform's schedule — no fragile "delete rows older than N days" job to babysit.
• Archival is cheap to keep and priced to read. Deep-archive storage is ~$1/TB-month, but a restore is an asynchronous job with a retrieval tier (expedited / standard / bulk), a latency (minutes to 12 hours), and a per-GB retrieval charge. Archived is not the same as queryable.
• Legal hold + compliance deletion is the lever that cannot be automated away. Object Lock / WORM freezes objects against deletion; holds override TTLs; and reconciling a GDPR erasure request against a compliance retention floor is the single hardest retention problem senior interviewers pose.

What interviewers listen for.

• Do you name both a minimum and a maximum retention for a dataset? — senior signal.
• Do you say "a legal hold overrides the TTL" without being prompted? — required answer.
• Do you separate storage cost from retrieval cost when discussing cold tiers? — senior signal.
• Do you describe deletion as a provable guarantee, not just a DELETE statement? — required answer.
• Do you push back on "just archive everything to Glacier" with the restore-cost question? — senior signal.

Worked example — the four-lever retention map

Detailed explanation. The single most useful artifact for a retention interview is a per-dataset map that names the tier, the lifecycle rule, the archive/restore plan, and the legal constraint for each dataset. Every senior retention discussion converges on this map; having it in your head keeps you from setting one blanket rule for wildly different data. Walk through building the map for a hypothetical clickstream lake plus a financial-ledger table.

• The estate. A partitioned S3 events/ lake (~2 PB, grows 3 TB/day) and a Postgres-then-warehoused gl_ledger (~800 GB, append-only).
• The reads. Clickstream: last 30 days hot for dashboards, last 13 months warm for analytics, older cold for ML backfill and audit. Ledger: current quarter hot, everything else cold but legally required for 7 years.
• The constraints. Clickstream carries PII (GDPR max-retention pressure); ledger carries a SOX 7-year floor.

Question. Build the four-lever map for both datasets and pick the tier, lifecycle rule, and legal constraint for each age band.

Input.

Lever	Question it answers	Clickstream	Ledger
Tier	how fast must reads be?	age-banded hot/warm/cold	hot current Q, cold rest
Lifecycle	when does it move / expire?	30d→IA, 13mo→Glacier, 25mo→delete	90d→Glacier, never auto-delete
Archive/restore	how is cold data read back?	bulk restore for ML backfill	expedited restore for audit
Legal	floor and ceiling?	GDPR max ~24mo (PII)	SOX min 7 years

Code.

Retention map — one row per dataset × age band
==============================================

events/ (clickstream, PII)
  0–30d    HOT    S3 Standard        queried daily (dashboards)
  30d–13mo WARM   S3 Standard-IA     queried weekly (analytics)
  13–24mo  COLD   S3 Glacier Flexible rare (ML backfill, bulk restore)
  > 24mo   EXPIRE lifecycle delete   GDPR: not needed → purge

gl_ledger (financial, SOX)
  current Q HOT   warehouse / Standard queried daily (close)
  > 90d     COLD  S3 Glacier Deep Arch legally required, rarely read
  7 years   HOLD-CHECK  no auto-delete  SOX floor; delete only after 7y AND no hold

Step-by-step explanation.

1. The clickstream and the ledger get different maps even though both are "old data" — the clickstream's ceiling (GDPR "keep no longer than necessary") forces expiry at ~24 months, while the ledger's floor (SOX 7 years) forbids expiry before 7 years. One blanket rule would either destroy the ledger too early or hoard PII too long.
2. The tier follows the read pattern, not the age alone. Clickstream at 13 months is cold because ML backfills tolerate a bulk restore; the ledger's cold tier is Deep Archive because audit reads are rare and can wait hours.
3. The lifecycle rule is the mechanism that moves data between tiers automatically. Clickstream uses transitions (30d→IA, 13mo→Glacier) plus an expiration (24mo delete). The ledger transitions to Deep Archive at 90d but has no automatic expiration — the SOX floor means deletion is a governed manual action.
4. The archive/restore plan differs by urgency: clickstream ML backfill uses cheap bulk retrieval (up to 12h is fine); ledger audit uses expedited retrieval (minutes) because an auditor request is time-boxed.
5. The legal lever sits on top of everything: even after the ledger passes 7 years, deletion is blocked if a legal hold is active on any object. Hold-check-then-delete is the invariant, never delete-then-discover-the-hold.

Output.

Dataset	Hot	Warm	Cold	Expiry rule
Clickstream	0–30d Standard	30d–13mo IA	13–24mo Glacier	delete at 24mo (GDPR ceiling)
Ledger	current Q	—	> 90d Deep Archive	none auto; 7y floor + hold check

Rule of thumb. Never write one blanket retention rule for the whole lake. Draw a per-dataset map with four columns — tier, lifecycle, archive/restore, legal floor/ceiling — and let the read pattern pick the tier while the law picks the expiry.

Worked example — what interviewers actually probe

Detailed explanation. The senior retention interview has a predictable arc: an ambiguous opener ("our S3 bill is growing 20% a quarter — what do you do?"), then progressive narrowing to test whether you know the four axes and, crucially, whether you treat deletion as a provable guarantee and legal hold as an override. Candidates who name the compliance floor and the restore-cost trap score highest; candidates who say "move it all to Glacier" score lowest. Walk through the grading rubric.

• Ambiguous opener. "Our storage bill is exploding — how do you cut it?" — invites a tiering + lifecycle answer.
• Follow-up 1. "Can we just delete data older than a year?" — probes the compliance-floor axis.
• Follow-up 2. "What does it cost to read the archived data back?" — probes the retrieval-cost axis.
• Follow-up 3. "Legal puts a hold on 2019 records — what happens to your TTL?" — probes the hold-override axis.
• Follow-up 4. "A user requests erasure of data that's also under a 7-year hold — now what?" — probes the erasure-vs-retention conflict.

Question. Draft a five-minute senior retention answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Bill reduction	"move everything to Glacier"	"tier by access frequency; lifecycle rules automate transitions"
Deletion	"delete data over a year old"	"delete only above the compliance floor and below the GDPR ceiling"
Retrieval	"it's cheaper in Glacier"	"storage is cheaper; a full restore can cost more than a year hot"
Legal hold	"we'd pause deletes"	"Object Lock; a hold overrides the TTL automatically"
Erasure conflict	"we delete the rows"	"crypto-shred the key; held rows stay, become unreadable"

Code.

Senior retention answer template (5 minutes)
=============================================

Minute 1 — name the four levers up front
  "Tier by access frequency, automate aging with lifecycle rules,
   archive cold data with a restore plan, and gate everything on the
   legal floor/ceiling. Deletion is a provable guarantee, not a DELETE."

Minute 2 — tiering + lifecycle
  "Hot in Standard for the last 30 days, warm in IA to ~13 months, cold
   in Glacier beyond. S3 lifecycle rules do the transitions automatically
   on age; no cron job."

Minute 3 — cost, both sides
  "Storage drops ~5x per tier down to ~$1/TB-month in Deep Archive, but
   retrieval moves the other way — a full bulk restore of a petabyte is a
   real bill. I model restore frequency before archiving."

Minute 4 — legal hold + floor/ceiling
  "Every dataset has a minimum retention (SOX 7y, HIPAA 6y) and often a
   maximum (GDPR). A legal hold via Object Lock/WORM overrides the TTL —
   held objects cannot expire until the hold is released."

Minute 5 — erasure conflict + proof
  "For a GDPR erasure of data under a hold, I crypto-shred: destroy the
   per-subject encryption key so the rows are permanently unreadable while
   the encrypted bytes stay to satisfy the hold. Every purge writes an
   auditable ledger entry — that's the deletion proof."

Step-by-step explanation.

1. Minute 1 frames the answer around four levers with deletion as a guarantee. Weak candidates jump straight to "Glacier" (a single storage class) instead of naming the program shape; naming the levers signals you have run a real retention policy, not just clicked a lifecycle wizard.
2. Minute 2 states the access-frequency-drives-tier principle and names the automation. Saying "lifecycle rules, no cron" is the tell that you know the platform ages data declaratively.
3. Minute 3 is the cost pushback. "Storage cheaper, retrieval dearer" and "a full restore can exceed a year of hot storage" is the senior counter to the naive archive-everything answer.
4. Minute 4 names the floor and ceiling and the hold override. Stating that Object Lock makes a hold beat the TTL automatically — rather than "we'd manually pause deletes" — shows you enforce it in the platform, not in a runbook.
5. Minute 5 resolves the hardest case: erasure-vs-retention. Crypto-shredding plus an auditable purge ledger is the senior answer that turns "we delete the rows" (which the hold forbids) into "we make the data unreadable and prove it."

Output.

Grading criterion	Weak score	Senior score
Names four levers in minute 1	rare	mandatory
Names compliance floor and ceiling	rare	required
Separates storage vs retrieval cost	occasional	senior signal
Names hold-overrides-TTL	rare	senior signal
Resolves erasure-vs-hold with crypto-shred	very rare	strong senior signal

Rule of thumb. The senior retention answer is a five-minute monologue: four levers, access-frequency picks the tier, lifecycle automates aging, model both storage and retrieval cost, and gate deletion on the legal floor/ceiling with holds overriding TTLs. Rehearse it once; deploy it every interview.

Worked example — the "which tier and when to delete" decision tree

Detailed explanation. Given a new dataset, the senior architect runs a short decision tree to place it in a tier and set its expiry. Codifying the tree makes the policy defensible: any stakeholder can hand you a dataset and you can place it. Walk the tree with three canonical datasets — a real-time feature store, a compliance audit log, and a raw ingestion staging bucket.

• Q1. Is it read on a hot path (dashboard, serving, current-period close)? → yes = HOT (Standard); no = go to Q2.
• Q2. Is it read at least monthly for analytics / backfill? → yes = WARM (IA / Nearline); no = go to Q3.
• Q3. Is it legally required or plausibly needed later? → yes = COLD (Glacier / Archive); no = candidate for expiry.
• Q4 (expiry gate). Is there a compliance floor or an active legal hold? → yes = cannot delete until floor passes AND no hold; no = set a lifecycle expiration at the GDPR ceiling (or the "no longer useful" age).

Question. Walk the decision tree for the three datasets and record the tier and expiry each ends up with.

Input.

Dataset	Q1 (hot path?)	Q2 (monthly?)	Q3 (legal/later?)	Q4 (floor/hold?)
Feature store	yes	—	—	no
Compliance audit log	no	no	yes	7-year floor
Raw staging bucket	no	no	no	no

Code.

# Decision-tree helper (illustrative)
def place_dataset(hot_path: bool,
                  monthly_read: bool,
                  legally_required: bool,
                  compliance_floor_years: int | None,
                  legal_hold: bool) -> dict:
    """Return the tier and expiry policy for a dataset."""
    if hot_path:
        tier = "HOT — Standard"
    elif monthly_read:
        tier = "WARM — Standard-IA"
    elif legally_required:
        tier = "COLD — Glacier / Deep Archive"
    else:
        tier = "COLD (short) — candidate for expiry"

    if legal_hold:
        expiry = "BLOCKED — legal hold active; no deletion"
    elif compliance_floor_years:
        expiry = f"no auto-delete; manual review after {compliance_floor_years}y + hold check"
    else:
        expiry = "lifecycle expiration at 'no longer useful' age / GDPR ceiling"

    return {"tier": tier, "expiry": expiry}


print(place_dataset(True,  False, False, None, False))
# → {'tier': 'HOT — Standard', 'expiry': "lifecycle expiration at ..."}

print(place_dataset(False, False, True,  7,    False))
# → {'tier': 'COLD — Glacier / Deep Archive', 'expiry': 'no auto-delete; manual review after 7y + hold check'}

print(place_dataset(False, False, False, None, False))
# → {'tier': 'COLD (short) — candidate for expiry', 'expiry': "lifecycle expiration at ..."}

Step-by-step explanation.

1. The feature store short-circuits at Q1 → HOT. Serving and dashboards cannot tolerate a retrieval delay, so it stays in Standard regardless of age; its expiry is a simple "no longer useful" lifecycle rule because it carries no compliance floor.
2. The compliance audit log fails Q1 and Q2 (rarely read) but passes Q3 (legally required) → COLD in Glacier/Deep Archive. Its expiry is not automatic: the 7-year floor plus a hold check means deletion is a governed manual action, never a lifecycle rule.
3. The raw staging bucket fails all of Q1–Q3 — it is transient reprocessing scratch. It goes to a short-lived cold or even Standard-IA with an aggressive lifecycle expiration (e.g. 30 days), because keeping raw staging forever is pure waste.
4. Q4 is the safety gate that runs on every dataset before any deletion. A legal hold blocks deletion outright; a compliance floor defers it; only when neither applies does a lifecycle expiration fire. This ordering — hold check first, floor second, expiry last — is the invariant that prevents premature destruction.
5. The tree is deliberately shallow (four questions) so it is whiteboard-able. An interviewer can hand you any dataset and you place it and set its expiry in under a minute — exactly the fluency the retention question tests.

Output.

Dataset	Tier	Expiry policy
Feature store	HOT — Standard	lifecycle delete when no longer useful
Compliance audit log	COLD — Deep Archive	no auto-delete; 7y floor + hold check
Raw staging bucket	COLD/IA (short)	aggressive lifecycle expiration (~30d)

Rule of thumb. Place a dataset by walking hot-path → monthly-read → legally-required → expiry-gate. The read pattern picks the tier; the compliance floor and any legal hold gate the delete. Never let a lifecycle expiration fire before the hold-and-floor check.

Senior interview question on retention policy design

A senior interviewer often opens with: "You inherit a 2 PB S3 data lake with a single Standard storage class and no lifecycle rules — the bill is growing 20% a quarter, and legal just asked whether you can produce 2019 records and whether you're honoring GDPR erasure. Walk me through the retention program you'd design, the tiering and lifecycle rules, the cost model, and how you'd reconcile the compliance floor with the erasure requests."

Solution Using a tiered lifecycle policy with a compliance-gated deletion pipeline

Retention program — 2 PB lake, mixed PII + compliance data
==========================================================

1. CLASSIFY   tag every prefix: {dataset, pii, floor_years, ceiling_days}
2. TIER       access-frequency → storage class (Standard/IA/Glacier/DeepArch)
3. LIFECYCLE  declarative age-based transitions + expiration per tag
4. ARCHIVE    Parquet + manifest for cold; restore plan per dataset
5. GOVERN     hold-check → floor-check → expire; crypto-shred for PII erasure
6. PROVE      every transition + purge writes to a retention ledger

// S3 lifecycle configuration — tag-scoped, per-dataset rules
{
  "Rules": [
    {
      "ID": "clickstream-tiering-and-expiry",
      "Filter": { "Tag": { "Key": "dataset", "Value": "clickstream" } },
      "Status": "Enabled",
      "Transitions": [
        { "Days": 30,  "StorageClass": "STANDARD_IA" },
        { "Days": 395, "StorageClass": "GLACIER" }
      ],
      "Expiration": { "Days": 730 }
    },
    {
      "ID": "ledger-archive-no-expiry",
      "Filter": { "Tag": { "Key": "dataset", "Value": "gl_ledger" } },
      "Status": "Enabled",
      "Transitions": [
        { "Days": 90, "StorageClass": "DEEP_ARCHIVE" }
      ]
      // no Expiration: SOX floor — deletion is a governed manual action
    }
  ]
}

# Compliance-gated deletion — runs before ANY purge (hold → floor → expire)
from datetime import date, timedelta

def may_delete(obj) -> tuple[bool, str]:
    """Return (allowed, reason). Order is strict: hold, then floor, then age."""
    if obj.legal_hold:                                  # 1. hold wins, always
        return (False, "blocked: active legal hold")
    floor_end = obj.created + timedelta(days=obj.floor_years * 365)
    if obj.floor_years and date.today() < floor_end:    # 2. compliance floor
        return (False, f"blocked: under {obj.floor_years}y floor until {floor_end}")
    if obj.age_days < obj.ceiling_days:                 # 3. not yet at ceiling
        return (False, "retain: below deletion age")
    return (True, "eligible: no hold, floor passed, ceiling reached")


def erase_subject(obj):
    """GDPR erasure for data that may also be under a floor/hold."""
    if obj.legal_hold or under_floor(obj):
        crypto_shred(obj.subject_key)     # destroy per-subject key → rows unreadable
        ledger_append("crypto_shred", obj.subject_id)
    else:
        hard_delete(obj)                  # safe to physically remove
        ledger_append("hard_delete", obj.subject_id)

Step-by-step trace.

Step	Before (single Standard class)	After (tiered lifecycle + governance)
Storage cost	2 PB × Standard ≈ $47k/mo	~70% in IA/Glacier ≈ $18k/mo
Aging mechanism	none (manual, never done)	declarative lifecycle transitions
2019 record retrieval	full-lake scan, days	manifest lookup + targeted restore, hours
GDPR erasure	delete rows (breaks holds)	crypto-shred key; held bytes stay
Deletion safety	ad-hoc, no floor check	hold → floor → ceiling gate, always
Auditability	none	retention ledger per transition/purge

After the program, ~70% of the lake ages into IA and Glacier automatically, cutting the storage bill by roughly 60%. A 2019-record request is a manifest lookup plus a targeted restore rather than a full-lake scan. GDPR erasure requests for data under a hold are satisfied by crypto-shredding the per-subject key while the encrypted bytes remain to honor the hold, and every transition and purge lands in an auditable retention ledger.

Output:

Metric	Before	After
Monthly storage bill	~$47k	~$18k
Data aged off hot storage	0%	~70%
2019 retrieval time	days (scan)	hours (manifest + restore)
Erasure honors holds	no	yes (crypto-shred)
Deletion audit trail	none	full ledger

Why this works — concept by concept:

• Tag-driven classification — tagging every prefix with {dataset, pii, floor_years, ceiling_days} lets one lifecycle configuration express many per-dataset policies. The tag is the join key between the storage layer and the compliance policy; without it, every rule would be a brittle prefix match.
• Declarative lifecycle transitions — the platform evaluates age-based rules daily and moves objects between classes without a cron job. Transitions to IA and Glacier are where the ~60% bill reduction comes from; the expiration handles the GDPR ceiling for non-floored data.
• Strict hold → floor → ceiling ordering — the may_delete gate checks the legal hold first, the compliance floor second, and the deletion age last. This ordering makes premature destruction impossible: a hold or an unexpired floor blocks the delete regardless of age.
• Crypto-shredding for erasure-under-hold — when a GDPR erasure collides with a retention obligation, destroying the per-subject encryption key renders the rows permanently unreadable (satisfying erasure) while the encrypted bytes remain (satisfying the hold). This is the only correct resolution of the conflict.
• Cost — the program adds a tagging step, a lifecycle configuration, and a governance/ledger pipeline (a few engineer-weeks) and buys a ~60% storage reduction plus provable, audit-ready deletion. The eliminated cost is unbounded Standard-class growth (O(bytes) forever) and the legal exposure of both over-retention (GDPR) and premature deletion (SOX). Net: O(1) governed lifecycle versus O(N) manual firefighting.

Design
Topic — design
Design problems on data retention and storage tiering

Practice →

ETL Topic — etl ETL problems on lifecycle and purge pipelines

Practice →

---

2. Hot / warm / cold tiering

hot warm cold maps access frequency to storage class — cheaper to store as it cools, slower and costlier to read

The mental model in one line: tiered storage is the practice of matching each byte's access frequency to a storage class whose cost-per-GB drops as retrieval latency and retrieval cost rise — hot data that is queried constantly lives in Standard (millisecond reads, highest $/GB), warm data queried occasionally lives in Infrequent-Access (seconds, ~40% cheaper storage but a per-GB read fee), and cold data queried almost never lives in Glacier / Archive (minutes-to-hours, ~$1/TB-month storage but a real retrieval bill) — and the entire skill is choosing the tier from the read pattern, not from the data's age alone. Every senior data engineer has under-tiered (overpaid for storage) or over-tiered (broken an SLA or blown a retrieval budget) at least once.

The three tiers and their trade curves.

• Hot (S3 Standard / GCS Standard / Azure Hot). Millisecond first-byte latency, highest storage cost (~$23/TB-month on S3), no per-GB retrieval fee. Use for anything on a serving or dashboard path, and for data written in the last N days that is still being iterated on.
• Warm (S3 Standard-IA / GCS Nearline / Azure Cool). Same millisecond latency, ~40–45% cheaper storage (~$12.5/TB-month), but a per-GB retrieval charge and a minimum storage duration (30 days) plus a minimum billable object size (128 KB). Use for data read a few times a month.
• Cold (S3 Glacier Instant/Flexible/Deep Archive / GCS Coldline/Archive). Storage from ~$4/TB-month (Glacier Instant) down to ~$1/TB-month (Deep Archive), but reads are either higher-latency-instant or an asynchronous restore job (minutes to 12 hours) with a retrieval fee and a longer minimum storage duration (90–180 days). Use for audit, legal, and rarely-touched backfill data.

The three costs every tier decision must weigh.

• Storage cost. $/GB-month. Drops ~2–5× per tier down. This is the number the naive "move it to Glacier" answer optimizes — and the only one it optimizes.
• Retrieval cost. $/GB read back. Rises per tier down: Standard is free to read, IA charges per GB, Glacier charges per GB plus a per-request fee, and expedited Glacier retrieval is the most expensive of all. A single full-table restore can dwarf a year of storage savings.
• Minimums. Minimum storage duration (IA 30d, Glacier 90d, Deep Archive 180d) and minimum billable object size (IA/Glacier bill a minimum of 128 KB per object). Early deletion or tiny objects get charged as if larger/longer — the "small-object tax."

The three failure modes senior engineers pre-empt.

• Retrieval-cost surprise. A team tiers 500 TB to Glacier to save storage, then an ML retraining job restores all of it — the one-time retrieval bill exceeds the annual storage savings. Mitigation: estimate restore frequency and volume before archiving; keep frequently-backfilled data in IA or Glacier Instant, not Deep Archive.
• Small-object tax. A lake of millions of 5 KB JSON files tiered to IA gets billed at 128 KB each — a 25× overcharge on storage and a per-request retrieval fee per tiny object. Mitigation: compact small objects into large Parquet files before tiering (the single biggest cold-storage cost lever).
• Cold-read stampede. A dashboard accidentally points at a cold prefix; every page load triggers a Glacier restore. Mitigation: never put anything on a read path in a restore-required tier; use Glacier Instant (millisecond reads) if a cold-priced-but-occasionally-read tier is needed.

Common interview probes on tiering.

• "How do you decide hot vs warm vs cold?" — access frequency, not age alone.
• "What's the catch with Glacier?" — retrieval latency + retrieval cost + minimum-duration + small-object tax.
• "When is IA the wrong choice?" — for data read many times a month (retrieval fees add up) or for tiny objects (128 KB minimum).
• "How do you avoid a retrieval-cost blowup?" — estimate restore volume × frequency before archiving; compact small objects.

Worked example — classifying a partitioned event lake into tiers

Detailed explanation. The canonical tiering exercise: take a date-partitioned S3 event lake, measure access frequency per age band from access logs, and assign each band to a tier. The output is the input to the lifecycle policy in section 3. Walk through the classification for a lake partitioned by dt=YYYY-MM-DD.

• Layout. s3://lake/events/dt=YYYY-MM-DD/part-*.parquet, ~3 TB/day, 2 years of history.
• Access signal. S3 server access logs (or CloudTrail data events / Storage Lens) counting GET requests per partition age band.
• Rule. Hot if read daily, warm if read a few times a month, cold if read less than monthly.

Question. Assign each age band to a tier from its measured GET frequency, and estimate the storage-cost impact.

Input.

Age band	GET reqs / partition / month	Read pattern	Tier
0–30 days	~900	dashboards, daily jobs	HOT
30–395 days	~20	weekly/monthly analytics	WARM
395–730 days	~1	rare ML backfill, audit	COLD

Code.

# Classify partitions into tiers from access-log GET counts
def tier_for(gets_per_month: int, is_read_path: bool) -> str:
    """Access frequency picks the tier; read-path data never goes restore-only."""
    if is_read_path:
        return "HOT (Standard) — on a serving/dashboard path"
    if gets_per_month >= 100:
        return "HOT (Standard)"
    if gets_per_month >= 5:
        return "WARM (Standard-IA)"
    if gets_per_month >= 1:
        return "COLD (Glacier Instant)"   # occasionally read → instant-retrieval cold
    return "COLD (Glacier Flexible/Deep Archive)"  # essentially never read


bands = [
    ("0-30d",    900, True),
    ("30-395d",  20,  False),
    ("395-730d", 1,   False),
]
for name, gets, read_path in bands:
    print(f"{name:10s} {gets:4d} gets/mo -> {tier_for(gets, read_path)}")
# 0-30d       900 gets/mo -> HOT (Standard) — on a serving/dashboard path
# 30-395d      20 gets/mo -> WARM (Standard-IA)
# 395-730d      1 gets/mo -> COLD (Glacier Instant)

Step-by-step explanation.

1. The classification is driven by measured GET frequency from access logs, not by a guess. The 0–30 day band serves dashboards and daily jobs (~900 GETs/partition/month) → clearly hot. Skipping the measurement is how teams mis-tier and either overpay or break an SLA.
2. The 30–395 day band drops to ~20 GETs/month — analytics that run weekly or monthly. That is warm: IA keeps millisecond latency (so analytics don't slow down) while cutting storage ~45%. The per-GB retrieval fee is acceptable at 20 reads/month.
3. The 395–730 day band is read ~once a month for ML backfill or audit. It is cold, but because it is occasionally read, Glacier Instant (millisecond reads, cold-priced storage) beats Flexible/Deep Archive — the latter would force an asynchronous restore on every backfill.
4. The is_read_path guard is the safety rail: anything on a serving or dashboard path stays hot regardless of frequency, because a restore-required tier on a read path causes the cold-read stampede failure mode.
5. The output feeds directly into the lifecycle transition rules — 30d→IA, 395d→Glacier — so the classification is not a one-off spreadsheet but the specification for the automation.

Output.

Age band	Tier	Storage $/TB-mo	Read latency	Retrieval fee
0–30d	Standard	~$23	ms	none
30–395d	Standard-IA	~$12.5	ms	per-GB
395–730d	Glacier Instant	~$4	ms	per-GB (higher)

Rule of thumb. Tier from measured access frequency, not from age intuition. Keep occasionally-read cold data in an instant-retrieval class (Glacier Instant / GCS Coldline) and reserve restore-required tiers (Flexible / Deep Archive) for data that is genuinely almost never read.

Worked example — the retrieval-cost blowup and how to model it

Detailed explanation. A team archives 500 TB of clickstream to Glacier Deep Archive to save storage, celebrating a 95% storage-cost cut. Two months later, a model retraining restores the entire 500 TB via standard retrieval, and the one-time retrieval bill is larger than a full year of the storage savings. Walk through the cost model that would have caught this before archiving.

• Storage saved. 500 TB × ($23 − $1)/TB-month ≈ $11,000/month saved by moving Standard → Deep Archive.
• Retrieval cost. Deep Archive standard retrieval ≈ $0.02/GB → 500 TB × 1024 GB × $0.02 ≈ $10,240 per full restore, plus per-request fees.
• Break-even. If you restore the full set more than ~once a month, archiving loses money.

Question. Build the cost model that decides whether archiving a dataset to Deep Archive is net-positive, given its restore frequency.

Input.

Parameter	Value
Dataset size	500 TB
Standard storage	$23/TB-month
Deep Archive storage	$1/TB-month
Standard retrieval	~$0.02/GB (~$20/TB)
Assumed full restores	1 per quarter (planned)

Code.

# Net-benefit model for archiving a dataset to cold storage
def archive_net_monthly(size_tb: float,
                        hot_per_tb: float,
                        cold_per_tb: float,
                        retrieval_per_tb: float,
                        full_restores_per_month: float) -> dict:
    storage_saved = size_tb * (hot_per_tb - cold_per_tb)          # $/month saved
    retrieval_cost = size_tb * retrieval_per_tb * full_restores_per_month
    net = storage_saved - retrieval_cost
    return {
        "storage_saved_per_month": round(storage_saved),
        "retrieval_cost_per_month": round(retrieval_cost),
        "net_per_month": round(net),
        "verdict": "ARCHIVE" if net > 0 else "KEEP WARMER (retrieval dominates)",
    }


# Planned: one full restore per quarter → 1/3 per month
print(archive_net_monthly(500, 23, 1, 20, 1/3))
# → {'storage_saved_per_month': 11000, 'retrieval_cost_per_month': 3413,
#     'net_per_month': 7587, 'verdict': 'ARCHIVE'}

# Reality: monthly retraining restores the whole set
print(archive_net_monthly(500, 23, 1, 20, 1.0))
# → {'storage_saved_per_month': 11000, 'retrieval_cost_per_month': 10240,
#     'net_per_month': 760, 'verdict': 'ARCHIVE'}

# Weekly retraining
print(archive_net_monthly(500, 23, 1, 20, 4.0))
# → {'storage_saved_per_month': 11000, 'retrieval_cost_per_month': 40960,
#     'net_per_month': -29960, 'verdict': 'KEEP WARMER (retrieval dominates)'}

Step-by-step explanation.

1. The model subtracts the monthly retrieval cost from the monthly storage saving. The storage saving is fixed (size × the per-TB delta between tiers); the retrieval cost scales with how often you read the whole set back.
2. At the planned restore frequency (one full restore per quarter), archiving is clearly net-positive: $11k saved vs ~$3.4k retrieval → +$7.6k/month. This is the number that justified the archive on paper.
3. At monthly retraining, the retrieval cost nearly cancels the storage saving (+$0.76k/month) — the archive is barely worth it and any per-request or minimum-duration fee could tip it negative.
4. At weekly retraining, the retrieval cost is ~4× the storage saving → −$30k/month. Archiving is now actively losing money; the correct tier is warm (IA) or Glacier Instant, where storage is still cheaper than Standard but repeated reads don't incur restore charges.
5. The lesson is that the tier decision is a function of restore frequency, not a one-time storage comparison. Requiring this model before any Deep Archive transition is the senior discipline that prevents the retrieval-cost blowup.

Output.

Restore frequency	Storage saved/mo	Retrieval cost/mo	Verdict
1 per quarter	$11,000	$3,413	ARCHIVE
1 per month	$11,000	$10,240	ARCHIVE (marginal)
1 per week	$11,000	$40,960	KEEP WARMER

Rule of thumb. Never archive to a restore-required cold tier without modeling storage_saving − restore_frequency × retrieval_cost. If you read the whole set back more than ~monthly, the retrieval fees eat the storage savings — keep it in IA or Glacier Instant instead.

Worked example — the small-object tax and compaction fix

Detailed explanation. A streaming pipeline lands millions of tiny 5 KB JSON files per day and tiers them to IA to save money. The storage bill rises instead of falling, because IA bills a minimum of 128 KB per object and charges a per-object transition and retrieval fee. The fix is compaction: roll the tiny files into large Parquet objects before tiering. Walk through the diagnosis and the fix.

• Symptom. IA storage bill for 5 KB objects is ~25× the raw byte size; transition and retrieval per-request fees dominate.
• Root cause. IA/Glacier minimum billable object size is 128 KB; a 5 KB object is billed as 128 KB. Plus per-object monitoring/transition/request fees.
• Fix. Compact many small objects into few large Parquet files (target 128 MB–1 GB) before the lifecycle transition to IA/Glacier.

Question. Quantify the small-object tax and design the compaction job that removes it.

Input.

Metric	Before (tiny JSON)	After (compacted Parquet)
Object size	5 KB	~256 MB
Billed size in IA	128 KB (min)	actual
Objects for 1 TB/day	~210 million	~4,100
Per-request fees	dominant	negligible

Code.

# Illustrative Spark compaction — small files -> large Parquet, then tier
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("compact-before-tiering").getOrCreate()

# 1. Read a day's worth of tiny JSON objects
df = spark.read.json("s3://lake/raw/events/dt=2026-08-16/")

# 2. Coalesce to target ~256 MB Parquet files (128MB–1GB is the sweet spot)
target_files = max(1, int(df.rdd.map(lambda r: len(str(r))).sum() / (256 * 1024 * 1024)))
(df.repartition(target_files)
   .write
   .mode("overwrite")
   .option("compression", "zstd")
   .parquet("s3://lake/compacted/events/dt=2026-08-16/"))

# 3. Only the compacted prefix carries the lifecycle tag that transitions to IA/Glacier
#    (the raw tiny-object prefix has an aggressive expiration instead)

-- Storage-class analysis: is a prefix suffering the small-object tax?
-- (run against S3 Storage Lens / inventory export loaded into Athena)
SELECT storage_class,
       COUNT(*)                                   AS objects,
       ROUND(SUM(size) / 1024.0 / 1024 / 1024, 1) AS actual_gb,
       ROUND(SUM(GREATEST(size, 131072)) / 1024.0 / 1024 / 1024, 1) AS billed_gb_min128k,
       ROUND(100.0 * SUM(GREATEST(size, 131072)) / NULLIF(SUM(size), 0) - 100, 0) AS tax_pct
FROM   s3_inventory
WHERE  prefix LIKE 'raw/events/%'
GROUP  BY storage_class;

Step-by-step explanation.

1. The tax comes from the 128 KB minimum billable size: 5 KB objects are billed as 128 KB, a 25× overcharge on storage alone. For 210 million objects per TB, the per-request transition and retrieval fees compound the damage.
2. The compaction job reads a day's tiny JSON and rewrites it as a few large (~256 MB) Parquet files with zstd compression. This collapses ~210 million objects into ~4,100 — eliminating both the minimum-size tax and the per-request fees.
3. Parquet is the right target format because it is columnar (cheaper scans on restore), splittable (parallel reads), and compresses well — a triple win over row-oriented JSON for archived analytical data.
4. Only the compacted prefix carries the lifecycle tag that transitions to IA/Glacier; the raw tiny-object prefix gets an aggressive expiration (e.g. 7 days) so it is never tiered and never taxed.
5. The Storage Lens / Athena query is the detection tool: comparing SUM(size) against SUM(GREATEST(size, 131072)) surfaces exactly which prefixes are paying the tax, so you compact the worst offenders first.

Output.

Metric	Before	After
Objects per TB/day	~210 million	~4,100
Billed vs actual storage	~25×	~1×
Per-request fees	dominant	negligible
Restore scan cost	high (row JSON)	low (columnar Parquet)

Rule of thumb. Compact small objects into large Parquet files (128 MB–1 GB) before tiering to IA or Glacier. The 128 KB minimum billable size makes tiny objects more expensive in a "cheaper" tier than they were in Standard — compaction is the single biggest cold-storage cost lever.

Senior interview question on hot/warm/cold tiering

A senior interviewer might ask: "You have a 1 PB event lake of small JSON files, all in S3 Standard, that a nightly analytics job reads for the last 90 days and an ML team backfills from once a quarter. Design the tiering: which classes, which age bands, how you'd handle the small files, and how you'd guarantee the backfill doesn't produce a surprise retrieval bill."

Solution Using compaction-then-tiering with an instant-retrieval cold class and a modeled backfill budget

# 1. Compact raw small files into large Parquet before any tiering
def compact_daily(dt):
    df = spark.read.json(f"s3://lake/raw/events/dt={dt}/")
    n = max(1, estimate_bytes(df) // (256 * 1024 * 1024))
    (df.repartition(n)
       .write.mode("overwrite")
       .option("compression", "zstd")
       .parquet(f"s3://lake/events/dt={dt}/"))
    # raw prefix expires in 7 days (never tiered, never taxed)

// 2. Lifecycle on the COMPACTED prefix — Standard -> IA -> Glacier Instant
{
  "Rules": [
    {
      "ID": "events-compacted-tiering",
      "Filter": { "Prefix": "events/" },
      "Status": "Enabled",
      "Transitions": [
        { "Days": 90,  "StorageClass": "STANDARD_IA" },
        { "Days": 180, "StorageClass": "GLACIER_IR" }
      ]
    },
    {
      "ID": "raw-tiny-objects-expire",
      "Filter": { "Prefix": "raw/events/" },
      "Status": "Enabled",
      "Expiration": { "Days": 7 }
    }
  ]
}

# 3. Backfill budget guard — refuse a restore that busts the quarterly budget
QUARTERLY_RETRIEVAL_BUDGET = 5000  # dollars

def guard_backfill(size_tb, retrieval_per_tb=8.0):   # Glacier IR read ~ $8/TB
    cost = size_tb * retrieval_per_tb
    if cost > QUARTERLY_RETRIEVAL_BUDGET:
        raise RuntimeError(f"backfill ${cost:.0f} > budget ${QUARTERLY_RETRIEVAL_BUDGET}; "
                           f"narrow the date range or request approval")
    return cost

Step-by-step trace.

Concern	Decision	Reasoning
Small files	compact to ~256 MB Parquet first	kills the 128 KB minimum-size tax
Hot band (0–90d)	S3 Standard	nightly analytics needs ms reads, no retrieval fee
Warm band (90–180d)	Standard-IA	~45% cheaper storage, still ms latency
Cold band (>180d)	Glacier Instant Retrieval	cheap storage, ms reads for quarterly backfill
Raw tiny objects	7-day expiration	never tiered, never taxed
Backfill cost	budget guard before restore	prevents surprise retrieval bill

After deployment, the raw tiny objects are compacted daily and expire in 7 days, so the taxed prefix never reaches a cold tier. The compacted lake ages Standard → IA → Glacier Instant, cutting storage ~70%. Because the cold class is Instant Retrieval, the quarterly ML backfill reads at millisecond latency with a predictable per-GB fee, and the budget guard refuses any restore that would exceed the quarterly retrieval budget.

Output:

Metric	Before	After
Object count	~210M/TB	~4,100/TB
Storage bill	1 PB Standard	~70% in IA/Glacier IR
Backfill latency	n/a	ms (Glacier IR)
Backfill cost control	none	hard budget guard
Small-object tax	~25×	eliminated

Why this works — concept by concept:

• Compact-before-tier — rolling tiny JSON into large Parquet before any transition removes both the 128 KB minimum-size tax and per-request fees, and makes cold-tier scans cheaper because Parquet is columnar and compressed. This is the prerequisite for every other saving.
• Glacier Instant Retrieval for occasionally-read cold — because the ML team backfills quarterly (not never), an instant-retrieval cold class gives Deep-Archive-adjacent storage prices with millisecond reads, avoiding the asynchronous-restore latency and the cold-read stampede risk.
• Raw-prefix aggressive expiration — the raw tiny-object prefix is transient reprocessing scratch; a 7-day expiration means it never gets tiered and never gets taxed, so only the well-shaped compacted data ever cools.
• Backfill budget guard — modeling size × retrieval_per_TB and refusing anything over the quarterly budget turns the retrieval-cost blowup from a surprise invoice into a controlled, approved action.
• Cost — the compaction job costs a daily Spark run and the lifecycle config is free; together they buy a ~70% storage reduction plus a bounded, predictable backfill cost. The eliminated cost is the 25× small-object tax and the unbounded retrieval exposure of a naive Deep-Archive move.

Optimization
Topic — optimization
Optimization problems on storage cost and tiering

Practice →

Design Topic — design Design problems on tiered data lakes

Practice →

---

3. Lifecycle policies and TTL automation

A lifecycle policy and TTL age and purge data declaratively — you write the rule once and the platform enforces it, no cron to babysit

The mental model in one line: a lifecycle policy is a declarative, age-based rule set that the storage platform evaluates on its own schedule to transition objects between tiers and expire them when they pass a deletion age, and a TTL is the row- or partition-level equivalent inside a database (BigQuery partition expiration, DynamoDB TTL, Cassandra default_time_to_live) — both replace the fragile imperative "delete rows older than N days" cron job with a rule the engine owns, so the mechanism cannot silently stop running the way a forgotten scheduled task does. The senior skill is expressing intent declaratively, scoping rules with tags or prefixes so they hit exactly the right data, and knowing the eventual-consistency gotchas of each engine's expiry.

Object-store lifecycle rules — the two actions.

• Transition. Move an object to a cheaper class at a given age (Days: 30 → STANDARD_IA). Transitions are one-way down the ladder; you cannot lifecycle back to a hotter class (a hot read triggers a restore instead).
• Expiration. Delete an object (or a noncurrent version) at a given age (Expiration: { Days: 730 }). This is the automated purge. For versioned buckets, NoncurrentVersionExpiration cleans up old versions separately.
• Scoping. A Filter by prefix, by object tag, or by size bound decides which objects a rule applies to. Tag-based scoping (Tag: {Key: dataset, Value: clickstream}) is the flexible choice — one bucket can carry many per-dataset policies.
• Evaluation cadence. Rules are evaluated roughly daily and act asynchronously; an object at "Days: 30" may be transitioned hours-to-a-day after it crosses the threshold. Lifecycle is not real-time — design for eventual action.

Table-level TTL — the database equivalents.

• BigQuery partition expiration. ALTER TABLE ... SET OPTIONS (partition_expiration_days = 400) drops whole partitions once they age out — an O(1) metadata drop, not a row scan. Table-level expiration_timestamp expires the entire table.
• DynamoDB TTL. A designated numeric attribute holds a Unix epoch; DynamoDB deletes the item within ~48 hours of that time. TTL deletes are not immediate and consume no write capacity but are eventually consistent — queries may still see expired items briefly.
• Cassandra TTL. INSERT ... USING TTL 604800 or table default_time_to_live tombstones rows after N seconds; compaction later reclaims the space. Beware tombstone build-up on high-churn TTL tables.

Declarative vs imperative — why the rule beats the cron.

• Imperative purge job. A scheduled DELETE FROM t WHERE created_at < now() - interval '90 days' works until the scheduler is paused, the query times out on a huge table, or someone changes the column name. It is O(rows) and it fails silently.
• Declarative lifecycle/TTL. The engine owns the rule; it cannot be "forgotten," it acts at metadata granularity (partition/object drop, O(1)), and it survives deploys and on-call rotations. Prefer declarative for anything that must reliably run forever.

Common interview probes on lifecycle/TTL.

• "How do you delete old data reliably?" — declarative lifecycle/TTL, not a cron DELETE.
• "Prefix or tag scoping?" — tags for flexible per-dataset policy in a shared bucket.
• "Is a TTL delete immediate?" — no; it's eventually consistent (DynamoDB ~48h; lifecycle ~daily).
• "How do you expire a partitioned table cheaply?" — partition expiration drops whole partitions (O(1)), never a row scan.

Worked example — S3 lifecycle configuration with tag-scoped transitions

Detailed explanation. The canonical object-store lifecycle: a single bucket carrying two datasets with different policies, expressed as tag-scoped rules that transition and expire on age. Walk through the full configuration and how tagging at write time wires it up.

• Bucket. One shared s3://lake bucket.
• Datasets. clickstream (tier down then delete at 24mo) and gl_ledger (archive at 90d, never auto-delete).
• Mechanism. Objects are tagged dataset=... at write time; lifecycle rules filter on the tag.

Question. Write the S3 lifecycle configuration and the write-time tagging so each dataset follows its own policy in one bucket.

Input.

Dataset	Transition rules	Expiration	Tag
clickstream	30d→IA, 395d→Glacier	730d delete	dataset=clickstream
gl_ledger	90d→Deep Archive	none	dataset=gl_ledger

Code.

# Write-time tagging — the tag is the join key to the lifecycle rule
import boto3
s3 = boto3.client("s3")

def put_tagged(bucket, key, body, dataset):
    s3.put_object(
        Bucket=bucket, Key=key, Body=body,
        Tagging=f"dataset={dataset}",     # lifecycle rules filter on this tag
    )

put_tagged("lake", "events/dt=2026-08-16/part-0.parquet", data, "clickstream")
put_tagged("lake", "ledger/2026/Q3/gl.parquet",            data, "gl_ledger")

// S3 lifecycle configuration — two datasets, one bucket, tag-scoped
{
  "Rules": [
    {
      "ID": "clickstream",
      "Filter": { "Tag": { "Key": "dataset", "Value": "clickstream" } },
      "Status": "Enabled",
      "Transitions": [
        { "Days": 30,  "StorageClass": "STANDARD_IA" },
        { "Days": 395, "StorageClass": "GLACIER" }
      ],
      "Expiration": { "Days": 730 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 30 }
    },
    {
      "ID": "gl_ledger",
      "Filter": { "Tag": { "Key": "dataset", "Value": "gl_ledger" } },
      "Status": "Enabled",
      "Transitions": [
        { "Days": 90, "StorageClass": "DEEP_ARCHIVE" }
      ]
    }
  ]
}

# Apply the configuration
aws s3api put-bucket-lifecycle-configuration \
  --bucket lake \
  --lifecycle-configuration file://lifecycle.json

Step-by-step explanation.

1. The object is tagged dataset=clickstream or dataset=gl_ledger at write time. The tag is the join key: it lets one bucket carry two completely different retention policies without splitting into separate buckets.
2. The clickstream rule transitions at 30 days to IA and at 395 days to Glacier, then expires at 730 days. The expiration is the automated GDPR-ceiling purge — no cron needed. NoncurrentVersionExpiration cleans up superseded versions after 30 days so versioning doesn't quietly hoard old copies.
3. The ledger rule transitions to Deep Archive at 90 days and has no expiration — the SOX floor means the ledger is never auto-deleted; its deletion is a separate governed action (section 5).
4. Transitions are strictly one-way down the ladder. You cannot write a lifecycle rule that moves data back to Standard; if a Glacier object is read, that triggers a restore (section 4), not a lifecycle transition.
5. Applying the configuration is a single API call; from then on the platform evaluates the rules daily and acts. There is no scheduled job to monitor — if the account exists, the rules run.

Output.

Object age	clickstream class	gl_ledger class
0–29d	Standard	Standard
30–89d	Standard-IA	Standard
90–394d	Standard-IA	Deep Archive
395–729d	Glacier	Deep Archive
≥ 730d	deleted (expired)	Deep Archive (never auto)

Rule of thumb. Scope lifecycle rules with object tags, not brittle prefixes, so one bucket serves many per-dataset policies. Always pair a transition ladder with an explicit expiration for data that has a ceiling — and deliberately omit the expiration for data under a compliance floor.

Worked example — table TTL in BigQuery and DynamoDB

Detailed explanation. The database equivalent of a lifecycle rule is a table/partition TTL. BigQuery drops whole partitions once they age past partition_expiration_days; DynamoDB deletes items whose TTL attribute has passed. Both are declarative and cheap, but both are eventually consistent. Walk through configuring each.

• BigQuery. Partitioned-by-day table; expire partitions after 400 days.
• DynamoDB. Session table; each item carries an expires_at epoch; TTL deletes within ~48h.
• Gotcha. Neither is instantaneous; downstream queries must tolerate briefly-visible expired data.

Question. Configure a 400-day partition expiration in BigQuery and a TTL in DynamoDB, and handle the eventual-consistency window.

Input.

Store	TTL mechanism	Granularity	Consistency
BigQuery	partition_expiration_days	whole partition (O(1) drop)	dropped after expiry, ~daily
DynamoDB	TTL attribute (epoch)	per item	deleted within ~48h

Code.

-- BigQuery — expire day-partitions older than 400 days (O(1) partition drop)
ALTER TABLE analytics.events
SET OPTIONS (
  partition_expiration_days = 400
);

-- Filter out any not-yet-dropped expired partitions in read queries
SELECT *
FROM   analytics.events
WHERE  _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 400 DAY);

# DynamoDB — enable TTL on an attribute, then write items with an expiry
import boto3, time
ddb = boto3.client("dynamodb")

# 1. One-time: designate the TTL attribute
ddb.update_time_to_live(
    TableName="sessions",
    TimeToLiveSpecification={"Enabled": True, "AttributeName": "expires_at"},
)

# 2. Write an item that should live 30 days
ttl_epoch = int(time.time()) + 30 * 24 * 3600
ddb.put_item(
    TableName="sessions",
    Item={
        "session_id": {"S": "abc123"},
        "expires_at": {"N": str(ttl_epoch)},   # DynamoDB deletes ~within 48h of this
    },
)

# 3. Reads must filter — TTL deletion is not immediate
#    (a query may still return an item after expires_at, before the sweep)
resp = ddb.get_item(TableName="sessions", Key={"session_id": {"S": "abc123"}})
item = resp.get("Item")
if item and int(item["expires_at"]["N"]) < time.time():
    item = None   # treat as expired even though the sweep hasn't run yet

Step-by-step explanation.

1. BigQuery's partition_expiration_days = 400 drops whole day-partitions once they age past 400 days. Because it operates on partition metadata, dropping a partition is O(1) — no row scan, no slot cost, unlike an imperative DELETE.
2. The read query still filters _PARTITIONDATE >= 400 days ago because expiration is evaluated roughly daily; a partition can be one day past its expiry but not yet dropped. The filter guarantees correct results during that window and also prunes partitions for cost.
3. DynamoDB's TTL is a designated numeric attribute holding a Unix epoch. Enabling it is a one-time table setting; thereafter, DynamoDB deletes items within ~48 hours of the epoch passing — at no write-capacity cost.
4. The critical gotcha: TTL deletion is not immediate. Between expires_at and the background sweep (up to 48h), a GetItem or Query can still return the "expired" item. Correct code filters on expires_at < now() on read and treats stale items as absent.
5. Both mechanisms are declarative and self-running, but both are eventually consistent. Any correctness that depends on "the data is gone the instant it expires" must add a read-time filter — the TTL bounds storage, not visibility.

Output.

Store	Action at expiry	Latency	Read-time guard
BigQuery	partition dropped	~daily	_PARTITIONDATE >= filter
DynamoDB	item deleted	within ~48h	expires_at < now() filter

Rule of thumb. Use partition expiration for warehouse tables (O(1) partition drops) and a TTL attribute for key-value stores, but always add a read-time filter on the expiry column — declarative TTLs bound storage, not visibility, and both delete eventually rather than instantly.

Worked example — the eventual-consistency and tombstone gotchas

Detailed explanation. Two subtle failure modes bite teams that trust TTL as an instantaneous, free delete: (a) queries see expired-but-not-yet-swept data and produce wrong counts, and (b) high-churn Cassandra TTL tables accumulate tombstones that make reads slow and eventually error. Walk through both and their mitigations.

• Eventual visibility. A billing job counts "active sessions" and includes items whose TTL passed hours ago but haven't been swept → over-counts.
• Tombstone build-up. A Cassandra table with default_time_to_live and heavy writes generates tombstones faster than compaction reclaims them → TombstoneOverwhelmingException on range reads.

Question. Diagnose both gotchas and design the mitigations.

Input.

Gotcha	Symptom	Mitigation
Eventual visibility	over-count of expired rows	read-time filter on expiry column
Tombstone build-up	slow reads / read errors	TWCS compaction + partition-aligned TTL

Code.

-- Cassandra — table TTL with time-window compaction to bound tombstones
CREATE TABLE telemetry.readings (
    device_id   text,
    bucket_day  date,
    ts          timestamp,
    value       double,
    PRIMARY KEY ((device_id, bucket_day), ts)
) WITH default_time_to_live = 2592000          -- 30 days
  AND compaction = {
        'class': 'TimeWindowCompactionStrategy',
        'compaction_window_unit': 'DAYS',
        'compaction_window_size': 1
      };
-- TWCS drops whole expired SSTables instead of scanning tombstones row-by-row

-- BigQuery / Snowflake — never trust "it's expired" without a read filter
-- WRONG: assumes TTL already purged
SELECT COUNT(*) AS active FROM sessions;

-- RIGHT: filter to non-expired at read time
SELECT COUNT(*) AS active
FROM   sessions
WHERE  expires_at > CURRENT_TIMESTAMP();

Step-by-step explanation.

1. The over-count comes from trusting the TTL as instantaneous. Between expiry and the sweep, expired rows are still physically present; COUNT(*) includes them. The fix is a read-time predicate (expires_at > now()) so correctness never depends on sweep timing.
2. In Cassandra, every TTL expiry writes a tombstone marking the row deleted. Reads must scan past tombstones until compaction reclaims them; on a high-churn table with the default compaction strategy, tombstones pile up and range reads slow down or throw TombstoneOverwhelmingException.
3. The mitigation is Time-Window Compaction Strategy (TWCS) plus partitioning aligned to the TTL window (bucket_day). With TWCS, an entire time-window SSTable ages out and is dropped whole once all its rows expire — the engine discards the file instead of reading millions of tombstones.
4. Aligning the partition key to the TTL window ((device_id, bucket_day)) ensures a whole partition's data expires together, so TWCS can drop it cleanly. Mixing long- and short-lived data in one partition defeats this and re-creates the tombstone problem.
5. The general principle across engines: a TTL is a storage bound, not a visibility or performance guarantee. Add read-time filters for correctness and choose a compaction/partition strategy that lets whole units expire together for performance.

Output.

Concern	Naive TTL	Hardened TTL
Count correctness	over-counts expired	read filter → exact
Cassandra read latency	grows with tombstones	flat (TWCS drops SSTables)
Delete cost	per-row tombstone	per-window file drop
Partition design	mixed lifetimes	TTL-aligned buckets

Rule of thumb. Treat every TTL as eventually consistent: filter on the expiry column at read time for correctness, and for TTL-heavy Cassandra tables use TWCS with TTL-aligned partitions so whole SSTables expire and drop instead of accumulating tombstones.

Senior interview question on lifecycle and TTL automation

A senior interviewer might ask: "You need to purge a 5-billion-row event table and a 40 TB S3 prefix on a 90-day retention, reliably, forever, without a cron job that someone can forget to re-enable. Walk me through the declarative lifecycle and TTL design, how you'd scope it, and how you'd handle the fact that neither delete is instantaneous."

Solution Using declarative partition expiration, tag-scoped lifecycle, and read-time expiry filters

-- 1. Warehouse table — partition expiration drops whole partitions (O(1))
ALTER TABLE analytics.events
SET OPTIONS (partition_expiration_days = 90);

-- 2. All reads filter to the retention window — correctness during the sweep lag
CREATE OR REPLACE VIEW analytics.events_live AS
SELECT *
FROM   analytics.events
WHERE  _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

// 3. S3 prefix — declarative expiration, tag-scoped, no cron
{
  "Rules": [
    {
      "ID": "events-90d-purge",
      "Filter": { "Tag": { "Key": "retention", "Value": "90d" } },
      "Status": "Enabled",
      "Expiration": { "Days": 90 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 7 },
      "AbortIncompleteMultipartUpload": { "DaysAfterInitiation": 3 }
    }
  ]
}

# 4. Monitoring — assert the declarative rules are actually acting
def assert_retention_healthy():
    # BigQuery: no partition older than 90d + slack should exist
    oldest = bq_query("SELECT MIN(_PARTITIONDATE) AS d FROM analytics.events")[0]["d"]
    assert oldest >= date.today() - timedelta(days=95), f"stale partition {oldest}"
    # S3: no object older than 90d + slack under the 90d tag
    stale = s3_count_older_than(bucket="lake", tag=("retention", "90d"), days=95)
    assert stale == 0, f"{stale} objects past retention not yet expired"

Step-by-step trace.

Layer	Mechanism	Why it beats a cron
Warehouse table	partition_expiration_days = 90	O(1) partition drop; engine-owned
Read path	events_live view filters to 90d	correct during sweep lag
S3 prefix	tag-scoped Expiration: 90d	declarative; survives deploys
Versions	NoncurrentVersionExpiration: 7d	versioning doesn't hoard old copies
Multipart	abort incomplete after 3d	no orphaned upload parts billed
Monitoring	assert oldest ≤ 90d + slack	detects a stopped/mis-scoped rule

After deployment, the warehouse table drops day-partitions older than 90 days automatically (a metadata operation, not a 5-billion-row scan), and the S3 prefix expires objects past 90 days on the platform's daily cadence. The events_live view guarantees queries never see the up-to-one-day window of expired-but-not-yet-dropped partitions. A monitoring check asserts the oldest surviving data is within 90 days plus slack, so a mis-scoped or disabled rule is caught instead of silently over-retaining.

Output:

Metric	Cron DELETE	Declarative lifecycle/TTL
Delete cost	O(5B rows) scan	O(partitions) metadata drop
Reliability	fails if scheduler paused	engine-owned, always runs
Read correctness during lag	wrong counts	view filter → exact
Orphaned versions/uploads	accumulate	auto-cleaned
Failure detection	none	monitoring assertion

Why this works — concept by concept:

• Partition expiration over row DELETE — dropping a whole day-partition is an O(1) metadata operation; an imperative DELETE ... WHERE created_at < ... on 5 billion rows is an O(rows) scan that costs slots and can time out. Partition-granular expiry is both cheaper and reliable.
• Engine-owned declarative rules — the storage platform and warehouse own the lifecycle/TTL rules, so they cannot be "forgotten" the way a paused cron can. The rule survives deploys, on-call rotations, and codebase refactors.
• Read-time expiry filter — because both lifecycle expiration and TTL are eventually consistent (up to a day for lifecycle, ~48h for DynamoDB), the events_live view filters to the retention window so query correctness never depends on sweep timing.
• Version and multipart cleanup — NoncurrentVersionExpiration and AbortIncompleteMultipartUpload close the two silent-cost leaks (hoarded old versions, orphaned upload parts) that a naive object-expiration rule misses.
• Cost — the design is a few ALTER TABLE/lifecycle-config lines plus a monitoring assertion, and it buys O(1) purges that run forever without supervision. The eliminated cost is the O(rows) scan of a cron DELETE and the outage risk of a scheduler that silently stops.

Data Processing
Topic — data-processing
Data-processing problems on partitioning and expiration

Practice →

SQL Topic — sql SQL problems on partition pruning and TTL filters

Practice →

---

4. Archival, restore, and cold-storage economics

archival is cheap to keep and priced to read — a cold storage restore is an asynchronous job with a latency and a bill

The mental model in one line: archival to cold storage (Glacier Flexible / Deep Archive, GCS Archive, Azure Archive) buys the cheapest storage on the ladder — roughly $1/TB-month — by making reads an asynchronous restore job rather than a direct GET, and that job has three dimensions you must plan for: a retrieval tier (expedited / standard / bulk) that trades latency for cost, a per-GB retrieval fee, and a temporary restored copy that itself incurs storage until it expires — so "archived" is a storage state, not a queryable state, and the restore contract must be designed before you archive. Every senior data engineer who has archived carelessly has heard "we need the 2019 data by end of day" and discovered the restore is a 12-hour bulk job.

The retrieval tiers — latency vs cost.

• Expedited. Minutes (1–5 min for Glacier Flexible). Highest per-GB cost. Use for time-boxed audit/legal requests where a human is waiting. Not available for Deep Archive.
• Standard. Hours (3–5h Flexible; up to 12h Deep Archive). Moderate cost. The default for planned backfills.
• Bulk. Up to 5–12 hours (Flexible up to 12h; Deep Archive up to 48h). Cheapest per-GB, often an order of magnitude below expedited. Use for large, non-urgent restores like a full ML re-training corpus.

The archive format contract — archived ≠ queryable.

• Format. Archive in a columnar, splittable, compressed format (Parquet + zstd) so that when restored, the data is immediately scannable by Athena/Spark/BigQuery without a re-parse. Archiving raw JSON means a slow, expensive re-ingest on restore.
• Manifest. Keep a manifest — a small always-hot index of what is archived, where, at what partition granularity, and under which archive job — so you can restore just the partitions you need instead of the whole dataset. A restore without a manifest is a full-dataset restore.
• Restored-copy lifecycle. A Glacier restore produces a temporary copy in a readable class for N days; that copy incurs storage cost until it expires. Set the restore's Days to the minimum you need.

The three cost components of a restore.

• Retrieval fee. $/GB read from archive; varies by tier (expedited ≫ standard ≫ bulk).
• Request fee. Per-object (or per-1000-objects) request charge — another reason to compact into few large objects before archiving.
• Temporary storage. The restored copy's storage for the days it lives in the readable class.

Common interview probes on archival.

• "How do you read archived data?" — a restore job with a retrieval tier; not a direct GET.
• "What format do you archive in?" — Parquet + zstd + a hot manifest for partition-scoped restores.
• "How do you keep restore cost down?" — bulk tier for non-urgent, manifest for partial restores, compact objects.
• "What's the catch with Deep Archive?" — no expedited tier; standard is up to 12h, bulk up to 48h.

Worked example — a Glacier restore job with a manifest-scoped partial restore

Detailed explanation. The canonical restore: an auditor needs Q3-2019 ledger data. Instead of restoring the whole archived ledger, you consult a hot manifest to find exactly which archived objects cover Q3-2019, issue a restore for only those objects at the standard tier, then query the temporary restored copy. Walk through the whole flow.

• Manifest. A hot Parquet/Delta table archive_manifest(dataset, partition, s3_key, storage_class, archived_at).
• Restore. restore_object for only the matching keys, standard tier, 7-day copy.
• Query. Athena over the restored prefix once the job completes.

Question. Restore only the Q3-2019 ledger partitions using the manifest, and show the cost of a scoped vs full restore.

Input.

Parameter	Value
Full ledger archived	8 TB in Deep Archive
Q3-2019 partitions	~120 GB
Retrieval tier	standard
Restore copy lifetime	7 days

Code.

import boto3
s3 = boto3.client("s3")

# 1. Consult the HOT manifest to find only the objects we need
def keys_for(dataset, quarter):
    rows = athena_query(f"""
        SELECT s3_key
        FROM   ops.archive_manifest
        WHERE  dataset = '{dataset}'
          AND  partition BETWEEN '2019-07-01' AND '2019-09-30'
    """)
    return [r["s3_key"] for r in rows]

# 2. Issue a scoped restore — only Q3-2019, standard tier, 7-day copy
def restore_scoped(bucket, keys, days=7, tier="Standard"):
    for key in keys:
        s3.restore_object(
            Bucket=bucket, Key=key,
            RestoreRequest={
                "Days": days,
                "GlacierJobParameters": {"Tier": tier},  # Standard ≈ 12h for Deep Archive
            },
        )

# 3. Poll until the restore completes, then query the restored copy
def is_restored(bucket, key):
    head = s3.head_object(Bucket=bucket, Key=key)
    return 'ongoing-request="false"' in head.get("Restore", "")

keys = keys_for("gl_ledger", "2019Q3")
restore_scoped("lake", keys)
# ... poll is_restored(...) ... then Athena SELECT over the restored partitions

# Cost comparison — scoped (manifest) vs full restore
def restore_cost(gb, retrieval_per_gb=0.02, requests=1, req_per_1000=0.05):
    return round(gb * retrieval_per_gb + (requests / 1000) * req_per_1000, 2)

print("scoped (120 GB):", restore_cost(120,  requests=4100))    # ~$2.60
print("full   (8 TB):",  restore_cost(8192, requests=4100_00))  # ~$184.66

Step-by-step explanation.

1. The manifest is the hero: it is a small, always-hot table mapping partitions to archived object keys. Querying it to find only the Q3-2019 keys means we restore 120 GB instead of the full 8 TB — a ~68× reduction in retrieval volume.
2. restore_object with Days: 7 creates a temporary readable copy of each matching object that lives for 7 days. The Tier: Standard choice reflects that an auditor request tolerates a several-hour wait; expedited would cost far more and standard on Deep Archive completes within ~12h.
3. Restore is asynchronous — the objects are not readable the instant you call restore_object. You poll head_object for ongoing-request="false" before querying. Code that assumes immediate availability fails.
4. Once restored, Athena queries the temporary copy exactly like normal Parquet — because we archived in Parquet with a manifest, there is no re-parse or re-ingest step; the restored partitions are immediately scannable.
5. The cost comparison makes the manifest's value concrete: the scoped restore is ~$2.60 versus ~$185 for a full-dataset restore, before counting the temporary storage of 8 TB vs 120 GB for 7 days. Partition-scoped restore is the difference between a trivial and a painful audit response.

Output.

Restore	Volume	Retrieval cost	Temp storage (7d)
Full (no manifest)	8 TB	~$185	8 TB × 7d
Scoped (manifest)	120 GB	~$2.60	120 GB × 7d

Rule of thumb. Always archive with a hot manifest that maps partitions to object keys, so a restore reads only the partitions you need. A partition-scoped restore is often 50–100× cheaper and faster than a full-dataset restore — the manifest is the cheapest insurance in the whole retention program.

Worked example — modeling restore latency for a time-boxed audit

Detailed explanation. A regulator gives you 72 hours to produce records. The data is in Deep Archive. You must choose a retrieval tier that meets the deadline at the lowest cost, and you must account for restore-then-transfer-then-query time, not just the restore latency. Walk through the latency budget.

• Deadline. 72 hours from request to delivered evidence.
• Data. 2 TB archived in Deep Archive.
• Tiers. Deep Archive: standard ≤12h, bulk ≤48h (no expedited).

Question. Choose the retrieval tier and lay out the end-to-end latency budget that meets the 72-hour deadline.

Input.

Phase	Standard tier	Bulk tier
Restore job	≤ 12h	≤ 48h
Query + package	~4h	~4h
Review + deliver	~8h	~8h
Total	~24h	~60h

Code.

# Pick the cheapest retrieval tier that fits the deadline, with a safety margin
def choose_tier(deadline_h, size_tb, review_h=8, query_h=4, margin=1.5):
    tiers = {                      # (max_restore_hours, retrieval_$/GB) for Deep Archive
        "standard": (12, 0.02),
        "bulk":     (48, 0.0025),
    }
    best = None
    for name, (restore_h, per_gb) in sorted(tiers.items(), key=lambda kv: kv[1][1]):
        total = (restore_h + query_h + review_h) * margin
        if total <= deadline_h:
            cost = size_tb * 1024 * per_gb
            best = (name, round(total, 1), round(cost, 2))
            break                  # cheapest that fits, because we sorted by price
    return best

print(choose_tier(72, 2))
# → ('bulk', 90.0, 5.24)   -> bulk exceeds 72h with margin; fall through...

# Corrected: evaluate all fitting tiers, pick cheapest that actually fits
def choose_tier_v2(deadline_h, size_tb, review_h=8, query_h=4, margin=1.5):
    tiers = {"standard": (12, 0.02), "bulk": (48, 0.0025)}
    fitting = []
    for name, (restore_h, per_gb) in tiers.items():
        total = (restore_h + query_h + review_h) * margin
        if total <= deadline_h:
            fitting.append((name, round(total, 1), round(size_tb * 1024 * per_gb, 2)))
    return min(fitting, key=lambda t: t[2]) if fitting else ("MISS DEADLINE", None, None)

print(choose_tier_v2(72, 2))
# → ('standard', 36.0, 40.96)   bulk's 90h > 72h with margin, so standard it is

Step-by-step explanation.

1. The latency budget is end to end, not just the restore. Restore + query/package + human review, multiplied by a safety margin, must fit inside the 72-hour deadline. Counting only the 12h restore and forgetting the review time is how teams miss regulator deadlines.
2. Bulk is the cheapest tier (~$0.0025/GB) but its ≤48h restore, plus query and review times a 1.5× margin, comes to ~90h — over the 72h deadline. So despite being cheaper, bulk does not fit.
3. Standard (≤12h) totals ~36h with margin — comfortably inside 72h. It costs ~$41 for 2 TB versus ~$5 for bulk, but meeting a regulator deadline is not negotiable, so standard is the correct choice.
4. The first code version has a bug: it picks the cheapest tier and stops, returning bulk even though bulk misses the deadline. The corrected version collects all fitting tiers and picks the cheapest among those that fit — the right decision rule.
5. The general principle: pick the cheapest retrieval tier that meets the deadline with margin, computed over the full end-to-end budget. Deep Archive has no expedited tier, so if a deadline is under ~24h end-to-end, the data should not have been in Deep Archive at all — it belongs in Glacier Flexible or Instant.

Output.

Tier	End-to-end (×1.5 margin)	Cost (2 TB)	Fits 72h?
bulk	~90h	~$5	no
standard	~36h	~$41	yes ✓

Rule of thumb. Budget restore latency end to end — restore + query + human review × a safety margin — and pick the cheapest retrieval tier that still fits the deadline. If a plausible deadline is under ~24h, Deep Archive is the wrong tier; use Glacier Flexible/Instant so expedited or instant reads are available.

Worked example — the "archived but unqueryable" failure and the manifest fix

Detailed explanation. A team archives 5 years of raw, uncompacted, un-manifested JSON to Deep Archive to hit a storage target. Two years later, compliance asks for specific records. Nobody knows which of the millions of archived objects contain them, so the only option is to restore everything — a 300 TB, 48-hour, five-figure bulk restore — and then re-parse raw JSON to find the records. Walk through why this happened and how a manifest and format discipline prevent it.

• Root cause. No manifest (can't locate specific records) + raw JSON (must re-parse on restore) + tiny objects (per-request fees explode).
• Fix. Archive Parquet + zstd, maintain a hot partition→key manifest, compact before archiving.

Question. Contrast the un-manifested raw-JSON archive with a manifested Parquet archive for a targeted-record request.

Input.

Dimension	Raw JSON, no manifest	Parquet + manifest
Locate records	impossible → restore all	manifest lookup → scoped
Restore volume	300 TB	~50 GB
On-restore work	re-parse JSON	direct Parquet scan
Cost	five figures	tens of dollars

Code.

# Archive-time discipline: compact, convert to Parquet, and register in the manifest
def archive_partition(dataset, partition, src_prefix, archive_bucket):
    # 1. Compact + convert to columnar Parquet (zstd) before archiving
    df = spark.read.json(src_prefix)
    key = f"{dataset}/{partition}/data.parquet"
    (df.coalesce(target_files(df))
       .write.mode("overwrite").option("compression", "zstd")
       .parquet(f"s3://{archive_bucket}/{key}"))

    # 2. Register in the HOT manifest so future restores are partition-scoped
    manifest_insert({
        "dataset": dataset,
        "partition": partition,
        "s3_key": key,
        "storage_class": "DEEP_ARCHIVE",
        "row_count": df.count(),
        "archived_at": now(),
    })
    # 3. Tag so the lifecycle rule transitions THIS object to Deep Archive
    tag_object(archive_bucket, key, {"archive": "deep"})

Step-by-step explanation.

1. The disaster is the compounding of three omissions: no manifest means you cannot locate specific records, so you must restore everything; raw JSON means even after restoring you must re-parse to find records; and tiny objects mean per-request fees dominate the bill.
2. The fix starts at archive time, not restore time. Compacting and converting to Parquet with zstd makes the restored data immediately scannable and shrinks both storage and request counts.
3. The manifest insert is the crucial step: recording (dataset, partition, s3_key, row_count, archived_at) in a hot table means any future request becomes a manifest query that returns exactly the keys to restore — turning a 300 TB restore into a ~50 GB one.
4. Tagging the object drives the lifecycle transition to Deep Archive declaratively, so the archive tier is applied by policy rather than a manual copy — keeping the archive process consistent and auditable.
5. The lesson is that archival is a write-time design problem. You cannot bolt a manifest or a good format onto data already sitting as raw JSON in Deep Archive — you would have to restore all of it first. Get the format and manifest right before the data cools.

Output.

Request outcome	Raw JSON, no manifest	Parquet + manifest
Restore volume	300 TB (everything)	~50 GB (scoped)
Restore time	~48h bulk	~12h standard
Post-restore work	re-parse all JSON	direct Parquet scan
Approx cost	five figures	tens of dollars

Rule of thumb. Archival is a write-time discipline: compact into large Parquet, keep a hot partition→key manifest, and tag for declarative transition — all before the data reaches cold storage. You cannot retrofit a manifest onto un-indexed archived data without restoring all of it first.

Senior interview question on archival and restore

A senior interviewer might ask: "You're archiving 5 years of a 200 TB event lake to Deep Archive for a 7-year compliance requirement, but roughly once a quarter someone needs specific date-ranges back for analysis, and once a year a regulator wants specific records inside 72 hours. Design the archive format, the manifest, the restore workflow, and the cost/latency model that satisfies both the routine and the deadline-bound restores."

Solution Using Parquet archives, a hot manifest, and tier-selected partition-scoped restores

# 1. Archive-time: compact -> Parquet -> manifest -> tag for Deep Archive transition
def archive(dataset, partition, src):
    df = spark.read.json(src)
    key = f"{dataset}/{partition}/data.parquet"
    (df.coalesce(target_files(df)).write.mode("overwrite")
       .option("compression", "zstd").parquet(f"s3://archive/{key}"))
    manifest_insert(dataset, partition, key, df.count())   # HOT index table
    tag_object("archive", key, {"archive": "deep"})        # lifecycle -> Deep Archive

# 2. Restore workflow: manifest lookup -> scoped restore -> tier by deadline
def restore(dataset, date_lo, date_hi, deadline_h):
    keys = manifest_lookup(dataset, date_lo, date_hi)      # only what we need
    size_gb = manifest_size_gb(keys)
    tier = "Standard" if deadline_h <= 24 else "Bulk"      # deadline picks the tier
    for k in keys:
        s3.restore_object(Bucket="archive", Key=k,
                          RestoreRequest={"Days": 7,
                                          "GlacierJobParameters": {"Tier": tier}})
    return {"objects": len(keys), "gb": size_gb, "tier": tier}

-- 3. The manifest — always hot, tiny, the key to partition-scoped restores
CREATE TABLE ops.archive_manifest (
    dataset        STRING  NOT NULL,
    partition      DATE    NOT NULL,
    s3_key         STRING  NOT NULL,
    storage_class  STRING  NOT NULL,
    row_count      BIGINT  NOT NULL,
    archived_at    TIMESTAMP NOT NULL,
    PRIMARY KEY (dataset, partition)
);

Step-by-step trace.

Concern	Decision	Reasoning
Format	Parquet + zstd	scannable on restore; small; compresses
Locatability	hot manifest table	partition-scoped restore, not full-dataset
Routine restore (quarterly)	Bulk tier	non-urgent; ~10× cheaper than standard
Deadline restore (72h)	Standard tier	fits end-to-end budget with margin
Restore copy lifetime	7 days	minimum needed; bounds temp storage
Compaction	coalesce to ~256 MB	avoids per-request fee explosion

After deployment, archiving is a write-time pipeline that lands compacted Parquet in Deep Archive and registers every partition in a hot manifest. A routine quarterly analysis restores only the requested date range via cheap bulk retrieval; a deadline-bound regulator request restores the same way but selects the standard tier to fit the 72-hour budget. Because everything is Parquet with a manifest, restored data is immediately queryable — no re-parse, no full-dataset restore.

Output:

Scenario	Volume restored	Tier	Approx cost	Time
Quarterly analysis (1 mo range)	~120 GB	Bulk	~$0.30	≤ 48h
Regulator request (72h SLA)	~50 GB	Standard	~$1	≤ 12h
(avoided) full-dataset restore	200 TB	Bulk	~$500+	≤ 48h

Why this works — concept by concept:

• Parquet + zstd archive format — columnar, splittable, compressed data is immediately scannable when restored, eliminating the re-parse step that raw JSON forces and shrinking both storage and per-request fees.
• Hot manifest as the restore index — a tiny always-available table mapping partitions to archived keys turns every restore into a partition-scoped read of tens of GB instead of a full-dataset restore of hundreds of TB. It is the single highest-leverage archival artifact.
• Deadline-driven tier selection — the restore workflow picks bulk for non-urgent routine restores (cheapest) and standard for deadline-bound requests (fits the SLA). The tier is a function of the deadline, computed end-to-end.
• Bounded restore-copy lifetime — a 7-day Days on the restore keeps the temporary readable copy's storage cost minimal; leaving restored copies around indefinitely quietly re-inflates the bill the archive was meant to cut.
• Cost — the archive pipeline (compaction + manifest) costs engineering time and a hot manifest table; it buys ~$1/TB-month storage plus restores that cost tens of dollars instead of hundreds-to-thousands, and it meets a 72h regulator SLA. The eliminated cost is the "restore everything and re-parse" catastrophe of an un-manifested raw archive.

Optimization
Topic — optimization
Optimization problems on restore cost and retrieval tiers

Practice →

Data Processing Topic — data-processing Data-processing problems on archival and manifests

Practice →

---

5. Legal hold, compliance retention, and deletion

A legal hold always overrides the TTL, a compliance floor beats a delete, and erasure of held data means crypto-shredding — not row deletion

The mental model in one line: the legal hold and compliance layer sits above every lifecycle rule as a set of overrides — an Object Lock / WORM legal hold freezes objects against deletion until it is released (beating any TTL), a compliance retention floor forbids deletion before a minimum age (SOX 7y, HIPAA 6y), and when a GDPR/CCPA right-to-erasure request targets data that is also under a floor or hold, the resolution is crypto-shredding (destroying the per-subject encryption key so the bytes are permanently unreadable while remaining physically present) rather than deleting rows the law requires you to keep — and every purge, hold, and shred must land in an auditable ledger so deletion is a provable guarantee. This is the single hardest and most-probed retention topic in senior interviews.

The override precedence — the invariant every senior engineer states.

• Legal hold (highest). An active hold blocks all deletion of the held objects, overriding any TTL or lifecycle expiration, until the hold is explicitly released. Holds are indefinite (no retention period) and are set/removed by legal, not by the retention pipeline.
• Compliance retention floor. A minimum age before deletion is permitted (Object Lock in COMPLIANCE mode enforces this even against the root account for the retention period). Deletion before the floor is impossible, not just discouraged.
• Lifecycle expiration / TTL (lowest). The ordinary age-based purge — only fires when no hold is active and the compliance floor has passed. The precedence is strict: hold → floor → expire.

Object Lock modes — WORM enforcement.

• Governance mode. Retention is enforced but a user with a special permission (s3:BypassGovernanceRetention) can override — useful for "protect by default, allow deliberate exceptions."
• Compliance mode. Retention is absolute for the retention period; no one, including the root account, can delete or shorten it. Use for hard regulatory floors (SEC 17a-4 WORM, financial records).
• Legal hold flag. Independent of a retention period; an on/off flag that blocks deletion until removed. A single object can have both a compliance retention period and a legal hold.

The erasure-vs-retention conflict — crypto-shredding.

• The conflict. GDPR Article 17 grants a right to erasure; SOX/HIPAA/tax law mandate retention. A subject under both must be "erased" without deleting records the law requires you to keep.
• The resolution. Encrypt each subject's data with a per-subject key (envelope encryption). To "erase," destroy that key: the encrypted bytes remain (satisfying the retention floor/hold) but are permanently unreadable (satisfying erasure). This is crypto-shredding.
• The proof. Record the key destruction in an auditable ledger with timestamp, subject id, and the legal basis — the deletion proof a regulator or DPA will ask for.

Governance — deletion as a provable guarantee.

• Retention ledger. Every transition, hold, release, purge, and shred writes an immutable ledger entry. "We deleted it" is not an answer; "here is the ledger entry proving key K for subject S was destroyed at time T under basis B" is.
• Hold-check-then-delete. The purge pipeline queries the hold registry and the floor before any deletion; it never deletes first and reconciles later.

Common interview probes on legal hold and deletion.

• "A legal hold lands on data your TTL is about to delete — what happens?" — the hold overrides; the object cannot expire until released.
• "How do you honor GDPR erasure for data under a 7-year hold?" — crypto-shred the per-subject key; keep the encrypted bytes.
• "What's the difference between governance and compliance mode Object Lock?" — governance is bypassable with permission; compliance is absolute, even for root.
• "How do you prove data was deleted?" — an immutable retention/purge ledger, not the absence of the row.

Worked example — S3 Object Lock legal hold overriding a lifecycle expiration

Detailed explanation. The canonical hold scenario: a bucket has a lifecycle rule that expires objects at 730 days, but legal places a hold on a set of objects related to litigation. The hold must block the expiration until released, and the system must make this automatic, not a manual "remember to pause the lifecycle" step. Walk through enabling Object Lock, setting a legal hold, and verifying the hold beats the TTL.

• Bucket. Object Lock enabled at creation (required; cannot be added later).
• Lifecycle. 730-day expiration.
• Hold. Legal hold flag ON for litigation-relevant objects.

Question. Set a legal hold on specific objects and demonstrate that it overrides the 730-day lifecycle expiration.

Input.

Component	Value
Object Lock	enabled at bucket creation
Lifecycle expiration	730 days
Legal hold	ON for litigation objects
Precedence	hold overrides expiration

Code.

import boto3
s3 = boto3.client("s3")

# 1. Bucket must be created with Object Lock enabled (cannot retrofit)
s3.create_bucket(Bucket="legal-lake",
                 ObjectLockEnabledForBucket=True)

# 2. Place a LEGAL HOLD on litigation-relevant objects (indefinite; no period)
def place_hold(bucket, key):
    s3.put_object_legal_hold(
        Bucket=bucket, Key=key,
        LegalHold={"Status": "ON"},
    )

# 3. (Optional) also set a COMPLIANCE-mode retention floor
def set_floor(bucket, key, until):
    s3.put_object_retention(
        Bucket=bucket, Key=key,
        Retention={"Mode": "COMPLIANCE", "RetainUntilDate": until},
    )

# 4. Verify: even though lifecycle says "expire at 730d", the held object survives
def deletion_blocked(bucket, key) -> bool:
    lh = s3.get_object_legal_hold(Bucket=bucket, Key=key)
    return lh["LegalHold"]["Status"] == "ON"   # ON => lifecycle expiration cannot delete it

# Attempting to delete a held object fails — the hold wins over any rule
$ aws s3api delete-object --bucket legal-lake --key case-2019/evidence.parquet
An error occurred (AccessDenied): Object is under a Legal Hold and cannot be deleted.

Step-by-step explanation.

1. Object Lock must be enabled at bucket creation — it cannot be added to an existing bucket. This is a design-time decision: buckets that may ever hold litigation or WORM data must be created lock-enabled from day one.
2. put_object_legal_hold with Status: ON sets an indefinite hold — no retention period, just an on/off flag. The hold stays until legal explicitly turns it OFF, regardless of the object's age.
3. The lifecycle rule still exists and still evaluates the object at 730 days — but the platform refuses to expire an object under a legal hold. The precedence is enforced by the storage layer itself, not by a runbook step; there is no window where the lifecycle could win.
4. A compliance-mode retention floor can be layered on top (put_object_retention), giving a hard minimum age and an indefinite hold on the same object. Compliance mode means even the root account cannot delete before RetainUntilDate.
5. Attempting to delete a held object returns AccessDenied — the hold beats the delete API, the lifecycle expiration, and even a manual operator. This is what makes "the hold overrides the TTL" a platform guarantee rather than a hopeful policy.

Output.

Object state	Lifecycle says	Actual result
No hold, age < 730d	retain	retained
No hold, age ≥ 730d	expire	deleted
Legal hold ON, age ≥ 730d	expire	retained (hold wins)
Compliance floor active	—	delete blocked even for root

Rule of thumb. Create any bucket that might hold litigation or WORM data with Object Lock enabled from day one, and enforce the hold at the storage layer so it overrides the lifecycle automatically. A legal hold is a platform-enforced override, never a "remember to pause the purge" runbook step.

Worked example — GDPR erasure of data under a compliance floor via crypto-shredding

Detailed explanation. The hardest retention conflict: a customer exercises GDPR right-to-erasure, but their transaction records are under a SOX 7-year floor and cannot be deleted. The resolution is crypto-shredding — each subject's PII is encrypted with a per-subject key; erasing the subject means destroying the key, rendering the data permanently unreadable while the encrypted bytes stay to satisfy the retention floor. Walk through the envelope-encryption setup and the shred.

• Setup. Per-subject data key (DEK), wrapped by a KMS key; PII encrypted with the DEK.
• Erasure. Destroy/disable the DEK → ciphertext becomes permanently undecryptable.
• Proof. Ledger entry: subject id, key id, timestamp, legal basis.

Question. Design the crypto-shred that satisfies both GDPR erasure and the SOX floor, and record the deletion proof.

Input.

Constraint	Requirement	Mechanism
GDPR erasure	data unreadable	destroy per-subject key
SOX floor	bytes retained 7y	keep encrypted ciphertext
Proof	auditable	immutable ledger entry

Code.

# Envelope encryption: one data key per subject, wrapped by KMS
import boto3, os
kms = boto3.client("kms")

def encrypt_pii(subject_id: str, plaintext: bytes) -> dict:
    # 1. Generate a per-subject data key (DEK), wrapped by the KMS master key
    dk = kms.generate_data_key(KeyId="alias/pii-master", KeySpec="AES_256")
    dek_plain, dek_wrapped = dk["Plaintext"], dk["CiphertextBlob"]
    ciphertext = aes_gcm_encrypt(dek_plain, plaintext)      # encrypt PII with the DEK
    del dek_plain                                           # never persist the plaintext DEK
    return {"subject_id": subject_id, "ciphertext": ciphertext, "wrapped_dek": dek_wrapped}


def crypto_shred(subject_id: str, wrapped_dek: bytes, legal_basis: str) -> None:
    """GDPR erasure for data under a retention floor/hold: destroy the key, keep the bytes."""
    # 2. Schedule deletion of the KMS key material that can unwrap this subject's DEK
    #    (or, if per-subject KMS keys, schedule_key_deletion directly)
    key_id = kms_key_for(subject_id)
    kms.schedule_key_deletion(KeyId=key_id, PendingWindowInDays=7)

    # 3. The wrapped DEK is now permanently un-unwrappable -> ciphertext is unreadable.
    #    The encrypted bytes remain on disk, satisfying the SOX 7-year floor.

    # 4. Write the DELETION PROOF to the immutable retention ledger
    ledger_append({
        "action": "crypto_shred",
        "subject_id": subject_id,
        "key_id": key_id,
        "legal_basis": legal_basis,          # "GDPR Art.17 erasure; SOX floor active"
        "at": now_iso(),
    })

Step-by-step explanation.

1. Envelope encryption gives each subject their own data key (DEK), itself encrypted ("wrapped") by a KMS master key. The PII on disk is ciphertext produced with the DEK; the plaintext DEK is never persisted.
2. To erase a subject whose data is under a SOX floor, you cannot delete the rows — the law requires keeping them. Instead you destroy the key path: scheduling deletion of the KMS material that can unwrap the subject's DEK makes the wrapped DEK permanently un-unwrappable.
3. Once the key is gone, the ciphertext is mathematically undecryptable — the data is erased in every sense that matters to GDPR (no one can ever read it) — while the encrypted bytes physically remain to satisfy the retention floor and any legal hold.
4. The PendingWindowInDays=7 reflects KMS's mandatory deletion delay; the shred is effective once the window elapses. For per-subject KMS keys you schedule_key_deletion directly; for a shared key with per-subject DEKs you delete/rotate so the specific wrapped DEK can no longer be unwrapped.
5. The ledger entry is the deletion proof: subject id, key id, legal basis, and timestamp, written to an immutable (WORM / append-only) store. When a DPA asks "prove you erased subject S," you produce this entry — the absence of a readable row is not itself proof.

Output.

Requirement	Row delete (wrong)	Crypto-shred (right)
GDPR erasure honored	yes	yes (unreadable)
SOX floor honored	no (bytes gone)	yes (bytes retained)
Legal hold honored	no	yes
Deletion proof	none	ledger entry

Rule of thumb. When a right-to-erasure request targets data under a retention floor or legal hold, crypto-shred — encrypt per subject and destroy the key — instead of deleting rows. The ciphertext stays to satisfy the floor while becoming permanently unreadable to satisfy erasure, and the key-destruction ledger entry is your provable deletion.

Worked example — the compliance-gated purge pipeline with an audit ledger

Detailed explanation. The purge pipeline must never delete data that is under a hold or below its floor, and must record every action for audit. The senior design is a gate that checks hold → floor → age in strict order before any delete, and appends every decision to an immutable ledger. Walk through the pipeline.

• Registries. A hold registry (which objects/subjects are held) and a policy table (floor/ceiling per dataset).
• Gate. For each candidate, check hold, then floor, then age; delete only if all pass.
• Ledger. Append every retain/delete/shred decision immutably.

Question. Implement the compliance-gated purge that checks hold → floor → age and writes an audit ledger entry for every decision.

Input.

Check	Blocks deletion if	Source
Legal hold	any active hold	hold registry
Compliance floor	age < floor	policy table
Deletion age	age < ceiling	policy table

Code.

from datetime import date, timedelta

def purge_candidate(obj, policy, holds, ledger) -> str:
    """Strict order: hold -> floor -> ceiling. Every path writes an audit entry."""
    # 1. HOLD — highest precedence; overrides everything
    if obj.id in holds or obj.subject_id in holds:
        ledger.append(action="retain", reason="legal_hold", obj=obj.id)
        return "retain: legal hold"

    # 2. COMPLIANCE FLOOR — cannot delete before minimum age
    floor_end = obj.created + timedelta(days=policy.floor_years * 365)
    if policy.floor_years and date.today() < floor_end:
        ledger.append(action="retain", reason="under_floor", obj=obj.id,
                      detail=f"until {floor_end}")
        return f"retain: under {policy.floor_years}y floor"

    # 3. CEILING / deletion age — only now may it be purged
    if obj.age_days < policy.ceiling_days:
        ledger.append(action="retain", reason="below_ceiling", obj=obj.id)
        return "retain: not yet at deletion age"

    # 4. All gates passed — delete (or crypto-shred if PII) and PROVE it
    if obj.is_pii:
        crypto_shred(obj.subject_id, obj.wrapped_dek, legal_basis="ceiling reached, no hold")
        ledger.append(action="crypto_shred", reason="ceiling_reached", obj=obj.id)
        return "erased: crypto-shred"
    hard_delete(obj)
    ledger.append(action="hard_delete", reason="ceiling_reached", obj=obj.id)
    return "deleted"

-- Immutable retention ledger (append-only; ideally WORM / Object-Lock backed)
CREATE TABLE ops.retention_ledger (
    entry_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    action       TEXT NOT NULL,          -- retain | hard_delete | crypto_shred | hold_set | hold_release
    reason       TEXT NOT NULL,
    object_id    TEXT,
    subject_id   TEXT,
    legal_basis  TEXT,
    decided_at   TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    decided_by   TEXT NOT NULL DEFAULT current_user
);
-- No UPDATE/DELETE grants; the ledger is write-once for audit integrity.

Step-by-step explanation.

1. The gate enforces the precedence in order: a legal hold is checked first and short-circuits everything — no floor or age check can override it. This is the "hold wins" invariant expressed as code.
2. The compliance floor is checked second: even with no hold, an object younger than its floor (e.g. 7 years for SOX) is retained. Only data past its floor is eligible to proceed.
3. The ceiling/deletion-age check is third: an object past its floor but not yet at its deletion age is still retained. Deletion fires only when hold is absent, floor has passed, and the ceiling is reached — the strict conjunction.
4. When all gates pass, PII is crypto-shredded (in case a residual floor/hold applies to related copies) and non-PII is hard-deleted; both paths write a ledger entry. Every retain decision is also logged, so the ledger explains why data still exists, not just why it was removed.
5. The ledger table has no UPDATE/DELETE grants — it is append-only, ideally backed by Object Lock so the audit trail itself is WORM. This makes the ledger the authoritative deletion proof that survives even an operator with delete rights on the data.

Output.

Object	Hold	Age vs floor	Age vs ceiling	Decision
A (litigation)	ON	—	—	retain: legal hold
B (ledger, 3y)	off	< 7y floor	—	retain: under floor
C (event, 20mo)	off	no floor	< 24mo	retain: below ceiling
D (event PII, 25mo)	off	no floor	≥ 24mo	erased: crypto-shred

Rule of thumb. Make the purge pipeline a strict hold → floor → ceiling gate that logs every decision — retain and delete alike — to an append-only, WORM-backed ledger. Deletion is only defensible when you can produce the ledger entry that proves the gates were checked in order.

Senior interview question on legal hold and compliance deletion

A senior interviewer might ask: "Your platform holds customer PII under a GDPR regime and financial records under a SOX 7-year floor. Legal issues a litigation hold on a subset. A customer then requests erasure of data that spans both the litigation hold and the SOX floor. Design the storage-layer controls, the deletion precedence, the erasure mechanism, and the audit trail that satisfies GDPR, SOX, and the litigation hold simultaneously."

Solution Using Object Lock holds, per-subject crypto-shredding, and a strict-precedence purge with a WORM ledger

# 1. Storage-layer controls: Object Lock + per-subject envelope encryption
def onboard_bucket():
    s3.create_bucket(Bucket="regulated", ObjectLockEnabledForBucket=True)  # WORM-capable

def write_pii(subject_id, plaintext, floor_years):
    rec = encrypt_pii(subject_id, plaintext)          # per-subject DEK, KMS-wrapped
    key = f"{subject_id}/data.parquet"
    s3.put_object(Bucket="regulated", Key=key, Body=rec["ciphertext"])
    if floor_years:                                    # compliance-mode retention floor
        s3.put_object_retention(Bucket="regulated", Key=key,
            Retention={"Mode": "COMPLIANCE",
                       "RetainUntilDate": years_from_now(floor_years)})

# 2. Litigation hold — overrides floor AND ceiling for the affected subjects
def apply_litigation_hold(subject_ids):
    for sid in subject_ids:
        for key in keys_for_subject(sid):
            s3.put_object_legal_hold(Bucket="regulated", Key=key,
                                     LegalHold={"Status": "ON"})
        holds_registry.add(sid)
        ledger.append(action="hold_set", subject_id=sid, legal_basis="litigation")

# 3. Erasure request spanning hold + floor -> strict precedence resolution
def handle_erasure(subject_id):
    if subject_id in holds_registry:
        # Hold wins: cannot delete, cannot even crypto-shred litigation-relevant data
        ledger.append(action="erasure_deferred", subject_id=subject_id,
                      legal_basis="litigation hold supersedes GDPR erasure until released")
        return "deferred: under litigation hold"
    if under_floor(subject_id):
        # Floor active, no hold: crypto-shred (keep bytes, destroy key)
        crypto_shred(subject_id, wrapped_dek_for(subject_id),
                     legal_basis="GDPR Art.17; SOX floor active")
        return "erased: crypto-shred (bytes retained for SOX)"
    hard_delete_subject(subject_id)                    # no hold, no floor -> physical delete
    ledger.append(action="hard_delete", subject_id=subject_id, legal_basis="GDPR Art.17")
    return "erased: hard delete"

Step-by-step trace.

Situation	Hold	Floor	Erasure resolution
Under litigation hold	ON	any	deferred — hold supersedes erasure until released
No hold, under SOX floor	off	active	crypto-shred (bytes stay, key destroyed)
No hold, floor passed	off	passed	hard delete
Hold released later	off→	active	re-evaluate → crypto-shred

After the design is in place, litigation-held subjects have their erasure requests deferred (with a logged legal basis) because a court-ordered hold supersedes the GDPR erasure right until released — a nuance senior interviewers specifically look for. Subjects not under a hold but under the SOX floor are crypto-shredded so their data is unreadable yet retained. Only subjects with neither a hold nor an active floor are physically deleted. Every decision — including the deferral — is written to a WORM-backed ledger.

Output:

Subject class	GDPR	SOX	Litigation hold	Outcome
Held + floored	erasure deferred	records kept	honored	deferred, logged
Floored only	erasure met (unreadable)	records kept	n/a	crypto-shred
Neither	erasure met (deleted)	n/a	n/a	hard delete
Hold released	re-evaluated	kept if floored	released	crypto-shred

Why this works — concept by concept:

• Object Lock + compliance-mode retention — the storage layer enforces the SOX floor absolutely (even root cannot delete before RetainUntilDate) and the legal hold as an indefinite override, so the precedence is a platform guarantee, not a policy hope.
• Per-subject envelope encryption — encrypting each subject with their own KMS-wrapped DEK is the prerequisite for crypto-shredding; without per-subject keys you cannot erase one subject while retaining others in the same file.
• Strict hold → floor → ceiling precedence — the erasure handler checks the litigation hold first (deferring erasure while it is active), then the floor (crypto-shred), then falls through to hard delete. Encoding the precedence prevents the classic mistake of honoring GDPR by deleting records a court ordered preserved.
• Erasure deferral under litigation hold — recognizing that a court-ordered hold supersedes the GDPR erasure right (with a logged legal basis and re-evaluation on release) is the senior nuance; naive designs illegally delete held data to satisfy the erasure SLA.
• WORM-backed audit ledger — every decision, including deferrals and retentions, is written to an append-only, Object-Lock-protected ledger. This is the provable trail that satisfies a GDPR DPA, a SOX auditor, and a litigation e-discovery request simultaneously.
• Cost — the design adds per-subject key management, Object Lock, and a governance/ledger pipeline (a meaningful but bounded engineering investment) and buys simultaneous, provable compliance with three conflicting regimes. The eliminated cost is a catastrophic one: illegally deleting held records (spoliation sanctions) or over-retaining PII (GDPR fines up to 4% of global revenue).

Data Validation
Topic — data-validation
Data-validation problems on retention and deletion proofs

Practice →

Design
Topic — design
Design problems on compliance, legal hold, and WORM storage

Practice →

---

Cheat sheet — data retention & lifecycle recipes

• Which tier when. Access frequency picks the tier: HOT (S3 Standard / GCS Standard) for serving/dashboard/current-period data read daily; WARM (Standard-IA / Nearline) for analytics read a few times a month; COLD-instant (Glacier Instant Retrieval / Coldline) for occasionally-read audit/backfill data; COLD-archive (Glacier Flexible / Deep Archive / Archive) for data read less than yearly and legally required. Never put anything on a read path in a restore-required tier — that is the cold-read stampede.
• Tier cost curve (memorise the direction). Storage $/GB drops ~2–5× per tier down (Standard ~$23/TB-mo → IA ~$12.5 → Glacier IR ~$4 → Deep Archive ~$1). Retrieval $/GB moves the opposite way (Standard free → IA per-GB → Glacier per-GB+request → expedited most expensive). Decide with storage_saving − restore_frequency × retrieval_cost, not storage price alone. Minimum storage durations: IA 30d, Glacier 90d, Deep Archive 180d; minimum billable object size 128 KB (the small-object tax).
• Compact before you tier. Roll tiny objects into large Parquet+zstd files (128 MB–1 GB) before any transition to IA/Glacier — the 128 KB minimum billable size makes millions of tiny objects more expensive in a "cheaper" tier. Compaction is the single biggest cold-storage cost lever and it makes cold-tier scans cheaper too (columnar + compressed).
• S3 lifecycle rule template. Tag-scope, don't prefix-match: Filter.Tag {dataset: X}; ladder Transitions [{Days:30 → STANDARD_IA},{Days:395 → GLACIER}]; automated purge Expiration {Days:730}; hygiene NoncurrentVersionExpiration {NoncurrentDays:30} and AbortIncompleteMultipartUpload {DaysAfterInitiation:3}. Omit Expiration deliberately for data under a compliance floor. Transitions are one-way down; a hot read of cold data triggers a restore, never an up-transition.
• Table TTL snippets. BigQuery ALTER TABLE t SET OPTIONS (partition_expiration_days = 90) drops whole partitions (O(1), no row scan). DynamoDB update_time_to_live(AttributeName='expires_at') deletes items within ~48h of the epoch. Cassandra default_time_to_live + TWCS so whole time-window SSTables drop instead of accumulating tombstones. Always add a read-time filter (WHERE expires_at > now() / _PARTITIONDATE >=) — TTLs bound storage, not visibility.
• Glacier restore recipe. Keep a hot manifest (dataset, partition, s3_key, storage_class, row_count) so restores are partition-scoped (often 50–100× cheaper than full-dataset). restore_object(RestoreRequest={Days:7, GlacierJobParameters:{Tier}}); poll head_object for ongoing-request="false" before querying (restore is asynchronous). Pick the cheapest tier that fits the deadline end-to-end (restore + query + review × margin): expedited (min, no Deep Archive), standard (≤12h), bulk (≤48h, cheapest).
• Restore cost model. retrieval = size_GB × per_GB(tier) + requests × per_request + temp_copy_storage × days. Bulk is ~10× cheaper per-GB than standard; standard ~10× cheaper than expedited. If a plausible deadline is under ~24h end-to-end, the data should not be in Deep Archive — use Glacier Flexible/Instant so faster reads are available.
• Deletion precedence (the invariant). Strict order: legal hold → compliance floor → ceiling/TTL. A hold overrides every TTL until released; a floor (SOX 7y, HIPAA 6y, tax 7y) forbids deletion before minimum age; only then does the lifecycle expiration fire. Encode it as a gate that checks hold, then floor, then age, and never deletes-then-reconciles.
• Object Lock modes. Governance mode = enforced but bypassable with s3:BypassGovernanceRetention (protect-by-default with deliberate exceptions). Compliance mode = absolute for the retention period, even root cannot delete (SEC 17a-4 WORM, financial records). Legal hold = indefinite on/off flag independent of any retention period; a single object can carry both a compliance period and a hold. Object Lock must be enabled at bucket creation — it cannot be retrofitted.
• Crypto-shred recipe (erasure vs retention). Encrypt each subject with a per-subject data key (KMS-wrapped DEK). To honor a GDPR/CCPA erasure of data under a floor/hold, schedule_key_deletion on the key path so the ciphertext is permanently undecryptable while the encrypted bytes remain — satisfying erasure and retention. A litigation hold supersedes erasure: defer (and log) until the hold releases, then re-evaluate. Never delete rows the law requires you to keep.
• Deletion as a provable guarantee. Every transition, hold set/release, purge, and shred writes to an append-only, WORM-backed retention ledger with (action, reason, object/subject id, legal_basis, timestamp). "We deleted it" is not an answer; the ledger entry proving key K for subject S was destroyed at time T under basis B is. Log retain decisions too, so the ledger explains why surviving data still exists.
• Governance checklist. Every dataset has a named minimum retention (floor) and maximum retention (ceiling); tags carry {dataset, pii, floor_years, ceiling_days}; lifecycle rules are declarative (never a cron DELETE); cold data is Parquet+manifest; buckets that may hold litigation/WORM are Object-Lock-enabled from creation; a monitoring assertion proves the oldest surviving data is within retention + slack (catches a stopped or mis-scoped rule).

Frequently asked questions

What is data retention in one sentence?

data retention is the policy that governs how long each dataset is kept, in which storage tier, and under what legal constraints — defining a minimum retention (a compliance floor like SOX 7 years or HIPAA 6 years, below which deletion is forbidden), a maximum retention (a ceiling like GDPR's "keep no longer than necessary", beyond which data should be purged), and the lifecycle policy that automatically ages data from hot to warm to cold storage and eventually expires or archives it. The four levers — hot warm cold tiering, TTL/lifecycle automation, archival with a restore plan, and legal hold/compliance deletion — each trade storage cost against retrieval latency, compliance exposure, and deletion guarantees, and the policy binds every downstream cost and audit for years. Senior data-engineering interviews probe retention because it is where cost optimization, compliance law, and system design intersect.

Hot vs warm vs cold storage — when do I use each?

Pick the tier from access frequency, not age alone. Use hot (S3 Standard, GCS Standard, Azure Hot) for anything on a serving or dashboard path and data read daily — millisecond latency, highest storage cost, no retrieval fee. Use warm (S3 Standard-IA, GCS Nearline, Azure Cool) for data read a few times a month — same millisecond latency, ~45% cheaper storage, but a per-GB retrieval fee and a 30-day minimum duration. Use cold for data read rarely: Glacier Instant Retrieval / Coldline for occasionally-read audit and backfill data (cheap storage, still millisecond reads), and Glacier Flexible / Deep Archive / Azure Archive for data read less than yearly (cheapest storage ~$1/TB-month, but reads are an asynchronous restore job taking minutes to 48 hours). The trap is that cost optimization cuts both ways — storage gets ~5× cheaper per tier down while retrieval gets more expensive, so a single full restore of archived data can exceed a year of hot-storage savings.

What is a lifecycle policy / TTL and how is it automated?

A lifecycle policy is a declarative, age-based rule set that the storage platform evaluates on its own schedule to transition objects to cheaper tiers and expire them at a deletion age — for example, S3 rules that move objects to Standard-IA at 30 days, to Glacier at 395 days, and delete them at 730 days, scoped by object tag so one bucket can carry many per-dataset policies. A TTL is the database equivalent: BigQuery partition_expiration_days drops whole partitions (an O(1) metadata operation, not a row scan), DynamoDB deletes items within ~48h of a TTL-attribute epoch, and Cassandra default_time_to_live tombstones rows after N seconds. Both replace the fragile imperative "delete rows older than N days" cron job with a rule the engine owns, so it cannot be silently forgotten. The key gotcha is that both are eventually consistent — a lifecycle expiration acts within about a day and a DynamoDB TTL within ~48 hours — so any correctness that depends on data being gone must add a read-time filter on the expiry column.

How does a legal hold interact with a TTL delete?

A legal hold always overrides the TTL. The deletion precedence is strict: legal hold → compliance floor → lifecycle expiration/TTL. When an object is under an S3 Object Lock legal hold (an indefinite on/off flag set by legal), the platform refuses to expire or delete it — even if a lifecycle rule says it should expire at 730 days and even if an operator issues a delete-object call, which returns AccessDenied. The hold is enforced at the storage layer, not by a "remember to pause the purge" runbook step, so there is no window where the TTL could win. The hold persists until legal explicitly releases it, at which point the object re-enters the normal precedence (floor, then ceiling). This is why buckets that may ever hold litigation-relevant data must be created with Object Lock enabled from day one — it cannot be retrofitted — and why the purge pipeline must always check the hold registry before any deletion rather than deleting first and reconciling later.

How do I reconcile GDPR erasure with a compliance retention floor?

When a GDPR/CCPA right-to-erasure request targets data that is also under a compliance retention floor (SOX 7 years) or a legal hold, you cannot delete the rows — the law requires keeping them — so you crypto-shred instead. Encrypt each subject's PII with a per-subject data key (a KMS-wrapped DEK via envelope encryption); to "erase" the subject, destroy that key path (schedule_key_deletion), which renders the ciphertext permanently undecryptable while the encrypted bytes physically remain to satisfy the floor. The data is erased in every sense that matters to GDPR (no one can ever read it) yet retained in the sense SOX requires. The one exception is a litigation hold, which supersedes the erasure right: you defer the erasure (logging the legal basis) until the hold is released, then re-evaluate. Every shred and deferral writes to an immutable, WORM-backed retention ledger — that ledger entry, not the absence of a row, is the deletion proof a data-protection authority or auditor will demand.

How much does cold storage restore actually cost?

Cold storage is cheap to keep (~$1/TB-month in Deep Archive) but priced to read, and a restore has three cost components: the retrieval fee ($/GB, which varies ~10× by tier — expedited ≫ standard ≫ bulk), a per-request fee (per object, which is why you compact into few large objects before archiving), and the temporary readable copy's storage for the days it lives after restore. A full-dataset restore can be brutal — restoring 500 TB from Deep Archive via standard retrieval at ~$0.02/GB is ~$10,000 in retrieval alone, potentially more than the annual storage savings that motivated archiving. The fix is a hot manifest that maps partitions to archived object keys, so you restore only the ~50–120 GB you actually need (often 50–100× cheaper), and a cost model that compares storage_saving against restore_frequency × retrieval_cost before archiving. If you read a dataset back more than ~monthly, keep it in Glacier Instant or Standard-IA rather than a restore-required archive tier.

Practice on PipeCode

• Drill the system-design practice library → for the retention-policy, tiering, WORM, and legal-hold system-design problems senior interviewers love.
• Rehearse on the optimization practice library → for the storage-vs-retrieval cost models, compaction, and restore-budget trade-offs.
• Sharpen the pipeline mechanics on the data-processing practice library → for lifecycle transitions, partition expiration, and archival + manifest patterns.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-lever retention map against real graded inputs.

Lock in retention and lifecycle muscle memory

Docs explain storage classes. PipeCode drills explain the decision — when warm beats cold, when a full restore costs more than a year of hot storage, when a legal hold silently overrides your TTL, and when GDPR erasure means shredding a key instead of deleting a row. Pipecode.ai is Leetcode for Data Engineering — decision-first practice tuned for the cost and compliance trade-offs senior data engineers actually face.

Practice design problems →
Practice optimization problems →