skip to content

How do you define and enforce retention periods across a warehouse and a lake so that expired data is actually deleted on time?

level: middleimportance: should knowfreq 45%

answer

  1. retention class per dataset
  2. clock starts from a named date
  3. partition by that date
  4. automated purge, then physical removal
  5. evidence and exceptions

basics

~20 s

Give every dataset a retention class tied to a named date column, partition by that date, run automated jobs that drop expired partitions and then physically purge old versions, and log evidence. Legal holds and exceptions are explicit, recorded overrides.

solid answer

~50 s

I would start with a **retention schedule**: each dataset carries a **retention class** in the catalog (for example 90 days, 2 years, 7 years), set by its owner with legal input, and the **date the clock runs from** — event date, account closure date. Enforcement is **automated**: tables are **partitioned by that date**, so a scheduled job can drop whole expired partitions cheaply instead of scanning rows. After the logical delete, the platform **expires old snapshots and removes unreferenced files**, or the data survives in time travel. Each run records **evidence** — what was removed, when, how many rows — and failures alert the owner. **Exceptions** such as a legal hold are explicit, time-bound overrides recorded against the dataset, never a quiet pause of the job. Finally, a report lists datasets **without a retention class**, because unclassified data is kept forever by default.

code

sql · 5 lines
sql
-- run by the platform's retention job for a dataset classed "400 days from event_date"
-- table partitioned by event_date, so this removes whole partitions
DELETE FROM analytics.web_events
WHERE event_date < CURRENT_DATE - INTERVAL '400' DAY;
-- afterwards the job expires old snapshots so the deleted files are physically removed

go deeper

for a junior

Know that each dataset should have a retention period and that expired data must be removed automatically.

for a middle

Explain retention classes, clock anchors, partition-aligned deletes and the need to purge old snapshots.

for a senior

Design the enforcement jobs, evidence, monitoring and explicit exceptions for legal holds across layers.

for a principal

Set the retention schedule's governance, including who approves classes and how derived data inherits them.

## Why retention needs engineering Most regimes and internal policies say data should be kept **no longer than necessary** — the GDPR calls this storage limitation — while some records must be kept **at least** a minimum period. A policy document does neither by itself: unless the platform deletes data automatically, everything is kept forever, because nobody runs a manual clean-up for thousands of tables. ## Defining retention 1. **Retention class per dataset**, recorded in the catalog as metadata: for example `90d`, `2y`, `7y`, `keep-aggregates-only`. 2. **Clock anchor**: the date column the period is measured from — the event timestamp, the account closure date, the contract end date. "Two years" is meaningless without it. 3. **Owner**: who set the class and why, with legal or compliance input for regulated data. 4. **Action at expiry**: delete, or reduce to an aggregate or pseudonymised form. ## Enforcing it | Step | Mechanism | |---|---| | Layout | partition tables by the clock-anchor date, so expiry drops whole partitions | | Logical delete | a scheduled platform job reads the retention class and removes expired partitions or rows | | Physical removal | expire old snapshots and remove unreferenced files; purge object versions | | Downstream | derived tables inherit or tighten the class; extracts carry it too | | Evidence | a log per run: dataset, cut-off date, partitions and rows removed | | Monitoring | alert on failed runs; report datasets with no class or overdue data | ## Exceptions A **legal hold** or regulatory investigation can require keeping data past its period. It must be an **explicit override** with a scope, an owner and an end date, recorded against the dataset, so the job skips only what is held and resumes automatically. Silently disabling the retention job is how data ends up kept for years with no reason on record. ## Common failure modes - Retention set on the curated table but not on the raw zone that feeds it. - Logical deletes run, but old snapshots keep the data readable for months. - A table partitioned by load date while retention is defined on event date, forcing expensive row-level deletes. - No class assigned, so the default is "forever". ## Why interviewers ask it Retention is where privacy policy meets platform engineering. A strong answer turns a policy into **metadata, layout and automated jobs**, remembers the **physical purge**, and handles **exceptions** explicitly.

  • Why partition by the retention clock's date rather than the load date?
    Retention is measured from a business date such as the event time. If the table is partitioned by that date, expired data is whole partitions that can be dropped cheaply; partitioned by load date, late-arriving events are scattered and every purge becomes a row-level rewrite.
  • How do derived tables inherit retention?
    A derived table should not keep personal data longer than its most restrictive source unless it is reduced to a non-identifying form. The platform can propagate the shortest source class through lineage and flag derived tables whose declared class is longer.

saying these in an interview costs you the question

  • Writing a retention policy without automated deletion behind it
  • Forgetting to expire snapshots after deleting expired rows
  • Pausing the retention job during a legal hold instead of recording a scoped exception
  • Leaving datasets with no retention class, which means keeping them forever