Data tiering for the DE interview

Why tiering shows up in DE interviews

Storage costs scale with data volume, but not all data is queried equally. The last 24 hours of clickstream gets hit by every active dashboard. The clickstream from two years ago might be touched once a quarter by a compliance audit. Treating both the same way — sitting on hot SSD at premium per-GB cost — burns a budget that should be funding new pipelines instead.

That is why data tiering is one of those "everyone-says-they-know-it" topics that actually separates engineers in interviews. The candidate who can sketch a tier diagram, name the storage classes, do the cost math, and explain when promotion-back from cold tier becomes a debugging nightmare is the candidate who lands the senior offer. It is one of the cheapest wins on the FinOps side of data engineering.

The mental model is simple. You categorise data by access frequency and latency tolerance, then match each bucket to a storage medium whose price-per-GB reflects how often you actually touch it. Three tiers — hot, warm, cold — are the canonical answer in any system design loop at Stripe, Snowflake, Databricks, or any company running multi-petabyte lakes. What follows is the version that gets you past the bar at a senior screen.

The hot tier

The hot tier is where queries live. SSD, NVMe, in-memory caches — anything that returns in single-digit milliseconds. This is the working set: data your dashboards, customer-facing features, and active ML pipelines hit constantly.

Typical hot-tier choices:

• OLTP: Postgres, MySQL, CockroachDB on local NVMe or provisioned IOPS volumes.
• OLAP: ClickHouse hot tables on SSD, Snowflake or BigQuery active partitions, Druid real-time segments.
• Cache: Redis, Memcached, Materialize for sub-second aggregates.
• Object storage hot class: S3 Standard, GCS Standard, Azure Blob Hot.

Hot data is usually the last 30 days for clickstream, the last 24 hours for real-time alerting, the active rolling window for fraud detection (often 7-14 days). For B2B SaaS dashboards that surface "this month vs last month", hot covers ~60 days so month-over-month always works without warming a partition.

The cost story matters: S3 Standard sits around $0.023 per GB-month in us-east-1 at typical scale, and provisioned NVMe on EBS gp3 lands at roughly $0.08 per GB-month before snapshots. A petabyte on hot block storage is real money — the kind of line item that ends up on a CFO slide if nobody is paying attention.

The warm tier

The warm tier holds data that gets queried, but not on every dashboard refresh. Monthly cohort reports, quarterly KPI rollups, ad-hoc investigations, ML feature recomputation — these are warm workloads. They tolerate seconds-to-minutes latency and they reward the engineer who moves them off premium storage.

Typical warm-tier choices:

• S3 Standard-IA (Infrequent Access) or Glacier Instant Retrieval — same millisecond access pattern as hot, but cheaper if you read it less than once a month.
• HDD-backed Snowflake or BigQuery older partitions (the platforms transition automatically based on age and edit recency).
• Iceberg or Delta tables on S3 with compacted Parquet files and partition pruning doing the work.
• ClickHouse warm volumes on cheaper EBS, often st1 or sc1 for sequential scan patterns.

Warm tier is where partitioning becomes the difference between a 3-second query and a 3-minute query. A well-partitioned warm tier can outperform a poorly-partitioned hot tier on aggregate scans — never let partition design lapse just because the data feels "old".

A typical age cut for warm is 30 to 365 days for clickstream, 3 to 24 months for transactional data, the prior fiscal year for finance tables that still feed annual board decks. Match the age window to the actual business cadence, not to a default a junior eng copy-pasted from an AWS blog post.

The cold tier

The cold tier is archive. You are paying 80-95% less per GB than hot in exchange for minutes-to-hours retrieval latency and per-GB egress fees that make casual access genuinely expensive. The whole design assumes that if you touch cold data this quarter, that is unusual.

Typical cold-tier choices:

• S3 Glacier Flexible Retrieval — minutes to hours, much cheaper than IA.
• S3 Glacier Deep Archive — 12-hour retrieval, the cheapest object storage on AWS, around $0.00099 per GB-month.
• GCS Coldline / Archive — analogous tiers on Google.
• Tape libraries in self-hosted shops with very long retention requirements.

Cold tier is where compliance retention lives: financial trade records (often 7 years under SOX-equivalent rules), HIPAA audit logs, GDPR or CCPA history that you must keep but should rarely read. Disaster recovery snapshots also go here — they exist precisely so you do not touch them under normal operation.

Lifecycle policies in practice

The promise of tiering only works if transitions are automated. Hand-moving data between tiers is exactly the kind of toil that gets skipped during a sprint crunch, leaving terabytes stuck on the expensive class. Three patterns to know cold for the interview.

S3 lifecycle rules — JSON policy applied to a bucket or prefix. Standard → Standard-IA → Glacier → Deep Archive on a schedule.

{
  "Rules": [{
    "ID": "events-tiering",
    "Status": "Enabled",
    "Filter": { "Prefix": "events/" },
    "Transitions": [
      { "Days": 30,  "StorageClass": "STANDARD_IA" },
      { "Days": 90,  "StorageClass": "GLACIER" },
      { "Days": 365, "StorageClass": "DEEP_ARCHIVE" }
    ],
    "Expiration": { "Days": 2555 }
  }]
}

ClickHouse TTL with volumes — the engine moves parts between disks based on column-level TTL expressions. The storage_policy defines which volumes are hot, warm, cold.

ALTER TABLE events MODIFY TTL
  event_date + INTERVAL 30 DAY  TO VOLUME 'warm',
  event_date + INTERVAL 90 DAY  TO VOLUME 'cold',
  event_date + INTERVAL 365 DAY DELETE;

Iceberg or Delta Lake — partition-level compaction and archival run through table-maintenance jobs. Iceberg has expire_snapshots, rewrite_data_files, and remove_orphan_files procedures. Delta has OPTIMIZE, VACUUM, and Z-ordering. The lifecycle policy on the underlying S3 bucket then sweeps old snapshot files to colder classes.

CALL system.rewrite_data_files('events', strategy => 'sort', sort_order => 'event_date ASC');
CALL system.expire_snapshots('events', older_than => TIMESTAMP '2025-11-01');

The trio — object-store lifecycle, engine-level TTL, and table-format compaction — is what a senior DE answer sounds like. A junior answer stops at "we set an S3 rule."

Cost math interviewers want to hear

System-design interviews love a back-of-envelope. A useful table to keep in your head:

A worked example interviewers respect: imagine you log 2 TB per day of clickstream, retain 7 years, and run dashboards on the last 60 days. Hot footprint: 60 × 2 TB = 120 TB. Warm (60-365 days): 610 TB. Cold (1-7 years): 4,380 TB. On all-hot S3 Standard you would pay roughly $117k/year. With the tiered policy above you drop to roughly $10-12k/year for the same data plus a modest retrieval budget. That ~10x reduction is the order of magnitude that gets a tiering project funded.

The "what does it cost?" follow-up is where mid-level candidates freeze. Have a model in your head before walking in.

Common pitfalls

The first pitfall is forgetting promotion-back paths. When an incident happens and you suddenly need 18 months of logs hot for a forensics query, the team that designed only one-way transitions is now staring at hours of Glacier restores plus per-GB retrieval fees on terabytes. The fix is to design an explicit warm-up procedure: scripts that pull a defined cold partition back into a temporary hot path, with cost estimates printed before the restore runs.

Another trap is uniform TTL across heterogeneous datasets. Putting a single "older than 30 days goes warm" rule across all event types means high-value payments partitions get cooled at the same rate as low-value feature_flag_eval partitions. Senior data engineers tier by value-density, not chronological age alone: payments stay hot longer because they drive revenue dashboards, feature-flag evals cool fast because nobody queries them after the launch.

A third pitfall is ignoring small-file accumulation in warm and cold tiers. Iceberg, Delta, and Hudi all degrade hard when a partition has tens of thousands of tiny files. The lifecycle rule moved bytes to Standard-IA, fine — but the underlying file count was never compacted. Now your warm-tier scans run slower than the original hot-tier scans. Compaction must be part of the tiering plan, not an afterthought.

A fourth is double-charging via cross-region replication. If your DR bucket sits in us-west-2 while your primary is us-east-1 and both run the same lifecycle rules independently, you pay storage and transition fees in both regions, plus the replication traffic. The fix is to centralise lifecycle policy and only replicate the hot+warm working set, leaving cold tier in a single region with a documented RPO/RTO.

Finally, GDPR right-to-delete becomes painful at the cold tier. Glacier objects under retention locks cannot be deleted on demand, and many lifecycle setups inadvertently set Object Lock policies that conflict with deletion SLAs. Always check whether retention compliance and erasure-on-request can co-exist before pushing personal data into Deep Archive.

Related reading

• Apache Iceberg deep dive for the data engineering interview
• ClickHouse MergeTree for the data engineering interview
• Iceberg time travel for the data engineering interview
• Data lineage for the data engineering interview
• SQL for the data engineer interview

If you want to drill DE system-design questions like this one every day, NAILDD is launching with a full track on storage architecture, lifecycle policy, and tiering trade-offs.

FAQ

Is "tiering" the same thing as "archiving"?

They overlap, but tiering is the broader concept. Archiving usually implies a single move to cold storage with little expectation of read-back. Tiering implies a multi-step policy with hot, warm, and cold stages, plus rules for promotion when access patterns change. In a strong interview answer, archiving is a subset of tiering — the final step in the lifecycle, not the whole story.

How do I decide whether a dataset is hot, warm, or cold?

Look at the read-to-write ratio over a 30-day window and the business latency tolerance. If queries hit the dataset multiple times per day and the user-facing SLA is sub-second, it is hot. If reads happen weekly and dashboards can wait a minute, warm. If reads happen quarterly or for compliance only, cold. Always confirm with actual access logs — S3 has access analytics, Snowflake exposes QUERY_HISTORY, ClickHouse has system.query_log. Don't guess.

What is the difference between S3 Standard-IA and S3 Glacier Instant Retrieval?

Both serve objects with millisecond latency so they look identical from the read side, but pricing differs. Standard-IA charges higher storage and lower retrieval fees, suitable for data you read maybe a couple of times a month. Glacier Instant Retrieval is cheaper to store but charges more per GB retrieved, suitable for data you almost never touch but still need to fetch quickly when you do. The break-even tends to fall around once per quarter access — below that, Glacier Instant wins.

Does Snowflake / BigQuery handle tiering automatically?

Mostly yes. Snowflake separates storage from compute and applies internal tiering based on edit recency, while BigQuery has long-term storage pricing that kicks in after 90 days of partition immutability and roughly halves the cost. You still need good partitioning and clustering — these systems reward good schema design and silently overcharge bad design.

How does data tiering interact with GDPR right-to-erasure?

Carefully. Object Lock and compliance retention modes on cold storage can prevent deletion until a defined date, which conflicts directly with a 30-day erasure SLA. Best practice is to avoid Object Lock on personal data entirely, and instead rely on auditable lifecycle policies plus a clearly documented deletion workflow that can reach into all tiers. Some teams tokenise PII before it ever lands in cold tier, leaving only opaque identifiers in archive and keeping the lookup table hot with a fast delete-on-request path.

What is a realistic interview answer if I have not run tiering at scale?

Describe the mental model — hot/warm/cold matched to access pattern and latency — then walk through a concrete dataset like clickstream. Quote approximate per-GB prices, name the storage classes, mention lifecycle policy, and call out at least two pitfalls. Honesty about scale beats fabricated numbers — interviewers can tell.