Data Lifecycle: Hot, Warm, Cold, Retention and Deletion

In one line

Data's value and access frequency drop with age. Keep hot data on fast storage and in the transactional database, move warm data to cheaper tiers or partitions, archive cold data to object storage, and delete on schedule according to retention policy and law. Lifecycle is designed into the schema (time partitioning, TTLs), not bolted on.

0/5 · 0%

Think of it like this

An office's papers. This month's files are on the desk, this year's in a cabinet, older years in off-site storage boxes, and anything past the legal retention period is shredded on a schedule.

Key ideas

01
Tiers: hot (recent, frequently accessed: primary database, SSD, cache), warm (occasionally accessed: older partitions, compressed or on cheaper volumes, replicas), cold (rarely accessed: Parquet in S3 Glacier or Deep Archive, queried via Athena/Trino when needed).
02
Retention policies per dataset: legal minimums (financial records often 7–10 years), maximums (privacy laws require deleting personal data when no longer needed), and business needs. Record them in a data catalogue.
03
Mechanisms: time partitioning and dropping old partitions, TTL (Cassandra, DynamoDB, MongoDB TTL indexes, Redis EXPIRE), archival jobs that copy then delete with verification, object storage lifecycle rules.
04
Deletion is harder than it looks: backups, replicas, search indexes, caches, warehouses and logs also hold copies. Crypto-shredding (encrypt per user or tenant and destroy the key) makes data unreadable everywhere at once.
05
Legal hold: the ability to suspend deletion for specific records under litigation overrides normal retention.

Code & diagrams

archive-partition.shbash

# archive an old monthly partition to Parquet in S3, then drop it
psql -c "ALTER TABLE events DETACH PARTITION events_2025_03 CONCURRENTLY"
psql -c "\copy events_2025_03 TO STDOUT WITH (FORMAT csv, HEADER)" \
  | duckdb -c "COPY (SELECT * FROM read_csv_auto('/dev/stdin')) TO 'events_2025_03.parquet' (FORMAT parquet)"
aws s3 cp events_2025_03.parquet s3://acme-archive/events/2025/03/ --storage-class DEEP_ARCHIVE
# verify row count in the Parquet file matches, then:
psql -c "DROP TABLE events_2025_03"

Interview problem

The problem

Lifecycle for a messaging app

Messages: users read the last 30 days constantly, occasionally search older ones, and legal requires keeping records for 2 years; deleted accounts must be erased within 30 days. 5 TB/month. Design the lifecycle.

When it breaks

No retention policy

What you see

The primary database grows to 40 TB of mostly unread data; backups take days, vacuum and index maintenance struggle, and personal data is kept illegally.

Fix & prevent

Define retention per dataset, partition by time, archive and drop on schedule, and review policies with legal.

Explain it without notes

What is crypto-shredding?

Practice

Write a DynamoDB or MongoDB mechanism that deletes sessions 30 days after last activity.

Trade-offs

↔
Tiering cuts cost and keeps hot data fast, at the cost of pipelines, slower access to old data, and deletion complexity.

Done when you can

I can design hot/warm/cold tiers, retention and verifiable deletion for a dataset.

Command Palette