S3 & Object Storage for Data Engineers: Layout, Partitioning, Lifecycle & Cost

Every data lake is really a pile of files in a bucket, and almost every cost surprise and slow query traces back to how those files were laid out. object storage for data engineers is the skill of arranging that pile so a query engine reads the fewest bytes possible, so aging data quietly gets cheaper without anyone touching it, and so a hundred writers can land data at once without corrupting a table. Object storage looks trivial from the outside — you PUT a file, you GET it back — and that simplicity is exactly the trap: the system will happily let you store a billion tiny JSON files under one flat prefix, and then every query lists forever, scans everything, and bills you for it.

This guide walks the whole surface of a data lake architecture built on S3-style object storage, from the mental model up. It covers what a bucket, key, and prefix actually are (and why "folders" are a fiction), how immutability and strong read-after-write consistency shape ingestion, and why s3 layout is the single highest-leverage decision you make. Then it goes deep on the four levers that decide speed and cost: Hive-style k=v partitioning and partition pruning; columnar file formats, compression, and file sizing; storage classes and lifecycle rules for automatic tiering; and the performance, security, and table-format concerns — Iceberg, Delta, Hudi — that turn a bucket of objects into a lakehouse. Each domain pairs a teaching block with worked examples over real S3 paths and an interview-style scenario with a full elimination trace.

When you want hands-on reps alongside the reading, drill pipeline shaping on the ETL practice library →, tune scans and storage on the optimization practice library →, and model layouts on the data-transformation practice library →.

---

On this page

• The object-storage model — and why layout decides cost and speed
• Bucket & prefix layout + partitioning
• File formats & organization — Parquet/ORC vs JSON/CSV
• Lifecycle, storage classes & cost
• Performance, consistency, security & the lakehouse
• Cheat sheet — object-storage layout, partitioning & cost recipes
• Frequently asked questions
• Practice on PipeCode

---

1. The object-storage model — and why layout decides cost and speed

Object storage is a flat key-value store of immutable blobs, and the key layout is the only index you get

The one sentence that reframes everything: an object store is not a filesystem and not a database — it is a giant, flat, distributed key-value map from a string key to an immutable blob of bytes, and because there is no built-in index other than the key itself, the way you name and group your keys is your access plan. A relational database gives you B-tree indexes, statistics, and a planner; an object store gives you a bucket and the ordering of keys. Everything a query engine can do to read less — skip a partition, prune a file, seek to a column — it does by reasoning about your key layout and file contents, never about a hidden index the storage layer maintains for you.

The three primitives you actually work with.

• Bucket. A globally-named container and the unit of policy — region, encryption defaults, access policy, and versioning are set here. You typically have a small number of buckets (per environment or per data domain), not one per table.
• Key. The full string name of an object, e.g. curated/events/dt=2026-08-14/country=US/part-00007.parquet. The key is the whole address; there is no separate "path" and "filename" the way a POSIX filesystem has directories.
• Prefix. Any leading substring of a key up to a delimiter (usually /). A LIST with prefix=curated/events/dt=2026-08-14/ returns every key starting with that string. Prefixes are the closest thing to "folders," but nothing physically nests — the console just renders /-delimited prefixes as a tree.

Folders are a UI illusion. There is no directory object. s3://lake/curated/events/ does not exist as a thing; what exists are keys that happen to share that prefix. Creating an empty "folder" in a console just writes a zero-byte object whose key ends in /. This matters because "renaming a folder" means copying every object to new keys and deleting the old ones — there is no cheap mv. It is why table formats and compaction jobs think in terms of writing new files and swapping pointers, never editing in place.

Objects are immutable — replace, never append. You cannot append a row to an existing object or edit a byte in the middle. A PUT to an existing key overwrites the whole object atomically. This single property drives most data-lake design: ingestion writes new files into a partition rather than mutating old ones; "updates" are handled by writing new files plus a metadata layer (a table format) that decides which files are live; and compaction rewrites many small files into fewer large ones as a fresh set of objects.

Consistency is now strong read-after-write. Modern S3 provides strong read-after-write consistency for PUT, GET, and LIST: after a successful write, any subsequent read returns the new data, and a LIST immediately reflects the new key. This ended a long era where engineers had to design around eventual consistency — the classic trap where a freshly written file did not yet appear in a LIST, so a downstream job silently processed a partial partition. Strong consistency removes that class of race, but it does not give you transactions across multiple objects — writing 100 files is still 100 independent operations, which is exactly the gap that table formats close.

Why layout is the highest-leverage decision. A query engine over object storage answers a query in two phases: it lists the keys it might need, then reads (scans) the bytes inside the ones it cannot skip. Both phases are priced and both are latency. A good layout lets the engine skip most keys before it lists them (partition pruning) and skip most bytes inside the files it does read (columnar formats + statistics). A bad layout — one flat prefix, tiny files, a row format — forces a full LIST of everything and a full scan of every byte. The difference between the two, on the same data, is routinely 10–100× in both cost and time. That is why layout, not the storage service itself, is where a data engineer earns their keep.

The four cost axes to keep in your head. Object storage does not bill one number; it bills four, and different layouts move different axes.

• Storage — dollars per GB-month, cheaper for colder classes.
• Requests — per PUT, GET, and especially per LIST; a million tiny files means a million requests.
• Scan/compute — engines like Athena bill per terabyte scanned; this is the axis partitioning and columnar formats crush.
• Egress — data transferred out of the region/provider; keep processing close to the data.

Worked example — reading an S3 key layout and predicting the scan

Detailed explanation. The most useful first skill is to read a bucket layout and predict what a partition-aware engine will scan for a given query, because that prediction is the bill. Consider a curated events table laid out with Hive-style partitions and ask: for a query filtering one day and one country, how many partitions and roughly how many bytes does the engine touch? If you can answer that from the key layout alone, you can design layouts on purpose instead of by accident.

• Key layout encodes the index — the dt= and country= segments are the only "index" the engine has.
• Pruning happens at LIST time — the engine expands only the prefixes whose partition values match the WHERE clause.
• Scan happens after pruning — inside the surviving files, a columnar format reads only the projected columns.
• Bytes scanned = the bill — on per-TB engines, fewer partitions and fewer columns is directly fewer dollars.

Question. Given the layout below and the query, how many day-partitions does the engine list, and does it scan the whole table?

Input.

Fact	Value
Table root	s3://lake/curated/events/
Partitioning	dt=YYYY-MM-DD/country=XX/
Total size	3 TB across 400 daily partitions × ~20 countries
Query	WHERE dt = '2026-08-14' AND country = 'US', selecting 2 of 30 columns
Format	Parquet (columnar)

Code.

s3://lake/curated/events/
  dt=2026-08-13/country=US/part-0000.parquet ... part-0007.parquet
  dt=2026-08-13/country=UK/part-0000.parquet ...
  dt=2026-08-14/country=US/part-0000.parquet ... part-0011.parquet   <- matches
  dt=2026-08-14/country=UK/part-0000.parquet ...
  dt=2026-08-15/country=US/part-0000.parquet ...
  ... (400 days x ~20 countries)

Query:
  SELECT user_id, revenue
  FROM   curated.events
  WHERE  dt = '2026-08-14' AND country = 'US';

Step-by-step trace.

1. The planner reads the partition predicate dt = '2026-08-14' and expands only the prefix .../dt=2026-08-14/ — 399 other day-partitions are never listed.
2. The second predicate country = 'US' narrows the LIST to .../dt=2026-08-14/country=US/ — ~19 other country sub-partitions are skipped.
3. Only the ~12 Parquet files in that one leaf partition are candidates for scanning.
4. Because Parquet is columnar, the engine reads only the user_id and revenue column chunks inside those files — not the other 28 columns.
5. Net: instead of 3 TB, the engine scans on the order of a few hundred MB (one partition × two columns).

Output:

Layout / query	Partitions listed	Bytes scanned (approx)
Full-table scan (no pruning)	8000 leaves	~3 TB
Day-prune only	~20 leaves	~7.5 GB
Day + country prune	1 leaf	~375 MB
Day + country + 2 columns (Parquet)	1 leaf, 2 cols	~25 MB

Rule of thumb. The key layout is your only index — before you write a query engine over a bucket, be able to say from the key names alone how many partitions a filtered query will list and how many columns it will read.

Worked example — why immutability forces compaction and a metadata layer

Detailed explanation. Because objects cannot be appended or edited, a streaming or micro-batch ingestion that writes a file every few seconds produces thousands of tiny objects per partition per day — and each tiny file costs a LIST/GET and defeats columnar efficiency. The immutability property means you cannot "merge" them in place; you must rewrite them as new, larger objects and then point readers at the new set. That rewrite is compaction, and the "point readers at the new set" is exactly why you eventually want a metadata/table layer rather than trusting a raw directory listing.

• Immutability — no append/edit; every change is a new object.
• Small-files tax — thousands of tiny files means thousands of requests and poor scan efficiency.
• Compaction — read many small files, write a few big ones, atomically swap which files are "current."
• Metadata layer — a manifest of "which files are live" makes the swap atomic and gives you consistent reads mid-compaction.

Question. A partition accumulates 5,000 files of ~200 KB each from a streaming writer. What does reading and then fixing it cost, conceptually?

Input.

Fact	Value
Files in partition	5,000
Avg file size	~200 KB
Ideal target size	~256 MB per file
Reader	Athena/Spark over the raw prefix

Code.

# Before: 5,000 tiny objects -> 5,000 GET/LIST units, tiny row groups
s3://lake/curated/events/dt=2026-08-14/country=US/
  part-000001.parquet (200KB)
  part-000002.parquet (200KB)
  ... x 5000

# Compaction: read all, coalesce, write ~4 large files (~256MB each)
#   Spark: df = spark.read.parquet(path); df.coalesce(4).write.parquet(tmp)
#   then atomically replace the partition's file set

# After: ~4 objects -> ~4 GET units, healthy row groups for pushdown
  part-00000.parquet (256MB) ... part-00003.parquet (256MB)

Step-by-step trace.

1. Reading the partition as-is issues thousands of GETs and produces thousands of undersized row groups, so predicate/column pushdown barely helps — the request overhead dominates.
2. A compaction job reads all 5,000 files into memory/executors and rewrites them coalesced into ~4 objects of ~256 MB.
3. Writing new objects (never editing old ones) respects immutability; the old files remain until the swap.
4. A metadata layer (or a careful rename-swap) flips readers to the new file set atomically, so no reader sees a half-compacted partition.
5. Subsequent reads issue a handful of GETs over large, statistic-rich files — cheap and fast.

Output:

State	Objects	Request units to read	Scan efficiency
Before compaction	5,000	~5,000	poor (tiny row groups)
After compaction	~4	~4	good (pushdown works)

Rule of thumb. Immutability means you never fix files in place — you rewrite them larger and swap; if you are doing that swap by hand you have outgrown raw directories and want a table format.

---

2. Bucket & prefix layout + partitioning

A data lake's speed and bill are set by its prefix layout and partition columns, long before any query runs

The invariant to burn in: a good layout groups data into zones by trust level, names partitions as key=value segments so engines can prune them, and picks partition columns that are low-cardinality and aligned to the filters your queries actually use — while a bad layout dumps everything under one prefix or partitions on a high-cardinality column, which either scans everything or explodes into millions of tiny files. Partitioning is the biggest single lever in partitioning data on a lake, and it is a two-edged one: the right column makes queries skip 99% of the data; the wrong column makes every write slow and every listing enormous.

Zone layout — organise by trust, not by team. A durable convention is a small set of zones, each a top-level prefix (or bucket):

• raw/ (a.k.a. landing/bronze) — immutable, exactly as ingested, never edited. Your source of truth and replay.
• staging/ (silver) — cleaned, deduplicated, type-cast, conformed schemas.
• curated/ (gold) — modelled, partitioned, query-optimised tables that analysts and BI hit.

Keeping these as prefixes under a per-environment bucket (s3://acme-lake-prod/raw/..., .../curated/...) means one place to set encryption and access policy, clean lifecycle boundaries, and an obvious blast radius for permissions.

Hive-style k=v partitioning — the layout engines understand. The de-facto standard, understood by Athena, Spark, Trino, Presto, and Hive, encodes partition values directly in the key path as column=value segments:

s3://lake/curated/events/dt=2026-08-14/country=US/part-0000.parquet

When the engine sees WHERE dt = '2026-08-14', it maps that predicate to the dt=2026-08-14/ prefix and lists only there — partition pruning. The alternative, non-Hive layout (.../2026/08/14/... with no key=), requires you to register partitions manually or use partition projection; the k=v form is self-describing and auto-discoverable.

Choosing partition columns — the three tests. A column is a good partition key if it passes all three:

• Low cardinality — tens to a few thousand distinct values, not millions. dt (dates), country, event_type are good; user_id, timestamp-to-the-second, uuid are catastrophic.
• High query selectivity — your queries filter on it often. Partition by what appears in WHERE, not by what is convenient to write.
• Even, sizeable partitions — each partition should hold enough data to make files worth reading (ideally hundreds of MB), so you are not creating millions of tiny partitions.

Over-partitioning — the trap that eats the benefit. Partition too finely (e.g. by dt and hour and country and device) and you get the small-files problem: each leaf partition holds only a few KB, so you have millions of tiny objects. Now every LIST is enormous, every query pays request overhead, columnar row groups are too small to help, and metadata operations (adding partitions, MSCK REPAIR) crawl. The symptom is a query that is slow despite pruning — the engine spends its time listing and opening files, not scanning bytes.

Common trap layouts to pre-empt.

• One flat prefix for a huge table — no pruning is possible; every query is a full scan.
• Partitioning on a high-cardinality column (user_id, raw timestamp) — millions of partitions, unusable metadata, tiny files.
• Partitioning on a column queries never filter — you pay the write-time cost of partitioning and get zero read-time benefit.
• Non-Hive paths (/2026/08/14/) without projection/registration — the engine cannot auto-prune; you must manage partitions manually.

Hive-style partition layout for an events table — a worked teaching example

Detailed explanation. Consider a clickstream events table, tens of TB, queried almost always as "a date range, optionally one country, grouped by something." The design question is which columns become path partitions. Date is the universal filter, so dt is partition #1; country is a frequent, low-cardinality filter, so it is a reasonable partition #2; everything else (user, device, url) stays a column, not a partition, because those are high-cardinality and rarely the sole filter.

Question. Design the S3 key layout for events so the common "one day, one country" query prunes to a single leaf.

Input.

Fact	Value
Table	events(event_ts, dt, country, user_id, url, device, revenue)
Common query	date range + optional country, aggregate
Cardinalities	dt ~ 1/day, country ~ 20, user_id ~ 50M
Target file size	~256 MB per part file

Code.

# GOOD: date first (universal filter), then low-cardinality country
s3://lake/curated/events/dt=2026-08-14/country=US/part-0000.parquet
s3://lake/curated/events/dt=2026-08-14/country=US/part-0001.parquet
s3://lake/curated/events/dt=2026-08-14/country=UK/part-0000.parquet

# user_id, url, device stay as COLUMNS inside the Parquet files (not partitions)

# BAD: high-cardinality partition -> ~50M leaf partitions, tiny files
s3://lake/curated/events/user_id=8f3c.../part-0000.parquet   # do NOT do this

Step-by-step trace.

1. dt becomes partition #1 because every query filters a date range — pruning here removes the vast majority of data immediately.
2. country becomes partition #2: ~20 even, low-cardinality values, frequently filtered, each partition still large enough to hold healthy files.
3. user_id is kept as a column, not a partition — 50M distinct values would create 50M partitions and defeat everything.
4. Files inside each leaf are written at ~256 MB so row groups are large enough for efficient pushdown.
5. The "one day, one country" query maps to exactly one leaf prefix and lists only its files.

Output:

Design choice	Partitions created	Query effect
dt/ only	~400	prunes to day; country scans within
dt/country/	~8,000	prunes to single leaf for the common query
dt/country/user_id/	~50M+	small-files disaster; avoid

Rule of thumb. Partition by the columns your queries filter on, most-universal first, and only while each column stays low-cardinality — keep high-cardinality fields as columns inside the file.

Partition pruning — what WHERE dt= actually skips, a worked teaching example

Detailed explanation. Partition pruning is the mechanism that turns a layout into savings, and the exam-and-interview tell is knowing that pruning happens on the partition columns in the path, before any file is opened, whereas filtering on a non-partition column only helps inside files (via columnar statistics), not by skipping partitions. A predicate on dt skips whole prefixes; a predicate on revenue (a data column) skips nothing at the partition level — it can only skip row groups that a columnar format's min/max stats rule out.

• Partition-column predicate — prunes prefixes at LIST time; the biggest win.
• Data-column predicate — no partition skipping; relies on columnar row-group statistics.
• Function-wrapped partition column — WHERE CAST(dt AS ...)= can defeat pruning if the engine can't map it back to the path.
• Partition projection — lets Athena compute partition prefixes from a formula instead of a slow metadata LIST.

Question. Two queries hit the same partitioned table — one filters dt, one filters revenue. Which prunes partitions?

Input.

Query	Predicate	Partition column?
Q1	WHERE dt = '2026-08-14'	yes (dt)
Q2	WHERE revenue > 100	no (data column)
Q3	WHERE dt BETWEEN '2026-08-01' AND '2026-08-07'	yes (range)
Q4	WHERE SUBSTR(dt,1,7) = '2026-08'	wrapped — may not prune

Code.

-- Q1: prunes to ONE day-partition prefix (fast, tiny scan)
SELECT COUNT(*) FROM curated.events WHERE dt = '2026-08-14';

-- Q2: NO partition pruning (revenue is a column) -> scans all partitions,
--     only row-group min/max stats inside Parquet can skip some blocks
SELECT SUM(revenue) FROM curated.events WHERE revenue > 100;

-- Q3: range prune -> 7 day-partitions
SELECT country, SUM(revenue) FROM curated.events
WHERE dt BETWEEN '2026-08-01' AND '2026-08-07' GROUP BY country;

-- Q4: wrapping dt in SUBSTR() can block prefix mapping -> full scan; prefer:
SELECT COUNT(*) FROM curated.events
WHERE dt >= '2026-08-01' AND dt < '2026-09-01';

Step-by-step trace.

1. Q1's predicate is a partition column with an equality, so the planner lists only .../dt=2026-08-14/ and reads a single day.
2. Q2 filters revenue, a data column — no prefix can be skipped, so every partition is listed; only Parquet row-group statistics can drop some blocks inside.
3. Q3 is a range on the partition column, so the planner expands the seven matching dt= prefixes and lists only those.
4. Q4 wraps dt in SUBSTR, which many engines cannot fold back into a prefix filter, so it silently degrades to a full scan — rewriting it as a half-open range restores pruning.

Output:

Query	Partitions scanned	Relative cost
Q1 (dt =)	1	lowest
Q3 (dt BETWEEN)	7	low
Q2 (revenue >)	all (~400)	high
Q4 (SUBSTR(dt))	all (~400)	high (avoidable)

Rule of thumb. Only predicates on the partition columns in the path skip partitions — filter partition columns with plain comparisons, never wrapped in functions, and remember data-column filters lean on file statistics, not pruning.

Interview scenario on partition-scheme design

You are asked to lay out a 30 TB orders table on S3 for a lake queried by Athena. The dominant query is "revenue by product for a date range" (always a date filter, sometimes a region filter). The team also does occasional per-customer lookups. It must minimise bytes scanned and avoid a small-files explosion, with no manual partition maintenance.

Solution Using dt-then-region Hive partitioning with partition projection

Answer choices (as an interview would present them).

• A. Partition by customer_id so per-customer lookups are instant.
• B. One flat prefix; rely on Athena to scan and filter.
• C. Hive partitions dt=/region=, Parquet files, and Athena partition projection for dt.
• D. Partition by dt, region, product_id, and hour for maximum pruning.

Code.

Elimination:
A  partition by customer_id (millions) -> millions of tiny partitions   [reject: cardinality]
B  flat prefix -> no pruning, every query scans 30 TB                    [reject: cost]
D  dt/region/product/hour -> partition explosion + small files          [reject: over-partition]
C  dt/region Hive partitions + Parquet + projection                     [ACCEPT]

Step-by-step trace.

1. Constraints: dominant filter is date (+ sometimes region), minimise bytes scanned, avoid small files, no manual maintenance.
2. A partitions on customer_id — millions of distinct values — creating millions of leaf partitions and tiny files, and it does not help the dominant date query — eliminate on cardinality.
3. B flattens everything, so no pruning is possible and every query scans the full 30 TB — eliminate on cost.
4. D adds product_id and hour, blowing the partition count into the millions and shrinking each leaf to KB — the small-files trap — eliminate on over-partitioning.
5. C partitions by the two columns queries actually filter (dt, region), keeps product_id/customer_id as columns, stores Parquet for column+predicate pushdown, and uses partition projection so Athena computes dt= prefixes by formula — no slow metadata LIST, no manual MSCK REPAIR.

Output:

Requirement	Winner
Prune the date-range query	dt= partition
Optional region filter	region= sub-partition
Per-customer lookups	column + row-group stats (not a partition)
No manual maintenance	partition projection

Why this works — concept by concept:

• Partition on the query's filters — dt and region are exactly the columns the dominant query constrains, so pruning removes almost all data before any scan.
• Cardinality discipline — keeping customer_id/product_id as columns avoids the millions-of-partitions explosion while columnar statistics still accelerate their filters inside files.
• Partition projection — computing partitions from a date formula sidesteps the metadata store's slow LIST/MSCK, giving zero-maintenance pruning at query time.
• Cost — bytes scanned drop from ~30 TB to a few GB for the common query, and on a per-TB engine that is the entire bill; healthy file sizes keep request overhead low too.

ETL
Topic — data-transformation
Partitioning and layout transformation problems

Practice →

ETL Topic — etl Data-lake ingestion and pipeline problems

Practice →

---

3. File formats & organization — Parquet/ORC vs JSON/CSV

The format you write is a permanent tax or discount on every query that ever reads the data

The invariant: columnar formats (Parquet, ORC) let a query read only the columns and row groups it needs and store data compressed and encoded, so they scan a small fraction of the bytes that row/text formats (CSV, JSON) force — and on top of format, the size of your files decides whether request overhead or useful scanning dominates. The parquet vs csv decision is not a preference; on a per-TB-scanned engine it is a recurring line item on every query for the life of the table.

Columnar vs row/text — what actually differs. Understand the two mechanisms columnar formats give you that CSV/JSON cannot:

• Projection pushdown — a columnar file stores each column contiguously, so SELECT a, b reads only the a and b column chunks and skips the rest. CSV/JSON store whole rows, so reading two fields still reads every byte of every row.
• Predicate pushdown — Parquet/ORC keep per-row-group statistics (min, max, null count). A WHERE ts > X lets the reader skip entire row groups whose max is below X, without decoding them. Text formats have no statistics, so every row is parsed.
• Encoding + compression — columnar data compresses far better because a column is homogeneous (all timestamps, all one enum), enabling dictionary/run-length encoding plus a codec. CSV mixes types per row and compresses worse.

Compression codecs — the trade-off triangle. Format and codec are separate choices:

• Snappy — fast compress/decompress, moderate ratio, splittable; the default for Parquet in analytics.
• Zstd — better ratio than snappy at competitive speed; increasingly the default where supported.
• Gzip — high ratio but slow and not splittable as a raw text file (a single gzipped CSV cannot be read in parallel), which hurts throughput on large files.
• Splittability matters: a large non-splittable file is read by one worker; Parquet is internally splittable by row group regardless of codec, which is another reason to prefer it over gzipped CSV.

File sizing — the small-files problem, again. Even with Parquet, thousands of tiny files defeat you: each file has footer/metadata overhead, tiny row groups make statistics useless, and every file is a request. Target 128–512 MB per file (256 MB is a common sweet spot). Too big (multi-GB) hurts parallelism and memory; too small drowns you in requests and metadata. When streaming produces small files, run a periodic compaction to coalesce them.

Row groups, footers, and statistics — why Parquet is fast. A Parquet file is a set of row groups (horizontal chunks), each storing its columns separately with encoding, and a footer holding the schema plus per-row-group column statistics. A reader opens the footer once, uses statistics to choose row groups, then reads only the needed column chunks in those groups. This is the physical machinery behind projection and predicate pushdown — and it only works when row groups are large enough to be meaningful, which loops back to file sizing.

Common trap choices to pre-empt.

• CSV/JSON for a frequently-queried analytics table — every query pays a full-row, uncompressed scan; convert to Parquet.
• SELECT * on a wide columnar table — throws away projection pushdown by reading every column.
• Gzipped CSV for large files — not splittable, so one worker reads the whole file; use Parquet+snappy/zstd instead.
• Millions of tiny Parquet files — request overhead and useless statistics negate the format's benefit; compact.

CSV → Parquet conversion and the bytes-scanned delta — a worked teaching example

Detailed explanation. The single highest-ROI change on a young lake is converting hot tables from CSV/JSON to compressed Parquet, because it simultaneously shrinks storage and slashes bytes scanned on every future query. The mechanism is the two pushdowns plus compression: the same query that scanned the entire CSV now reads two compressed column chunks in a few pruned row groups. It is worth being able to estimate the delta so you can justify the conversion job.

• Storage shrink — columnar encoding + a codec typically compresses analytics data 3–10×.
• Projection — reading 2 of N columns cuts the scan by roughly the column fraction.
• Predicate pushdown — row-group statistics skip blocks the filter excludes.
• Combined effect — the three stack multiplicatively, often 10–50× fewer bytes scanned.

Question. A 1 TB CSV events table is queried as "sum revenue for a category," touching 2 of 25 columns. What does converting to Parquet do to storage and bytes scanned?

Input.

Fact	Value
Source	events.csv — 1 TB, 25 columns
Query	SELECT SUM(revenue) ... WHERE category = 'books' (2 columns)
Codec	snappy
Engine bills	per TB scanned

Code.

-- One-time CTAS converts CSV -> partitioned, compressed Parquet
CREATE TABLE curated.events_parquet
WITH (
  format = 'PARQUET',
  parquet_compression = 'SNAPPY',
  partitioned_by = ARRAY['dt'],
  external_location = 's3://lake/curated/events_parquet/'
) AS
SELECT event_ts, dt, category, revenue, /* ...21 more cols... */
FROM raw.events_csv;

-- The recurring query now reads 2 columns from pruned row groups:
SELECT SUM(revenue) FROM curated.events_parquet
WHERE dt = '2026-08-14' AND category = 'books';

Step-by-step trace.

1. The CTAS rewrites 1 TB of CSV into Parquet; snappy + columnar encoding compress it to roughly 200–300 GB on disk.
2. The recurring query projects only revenue and category, so the reader touches ~2/25 of the columns.
3. Parquet row-group statistics on category let the reader skip groups that contain no 'books' rows.
4. Partition pruning on dt (added during conversion) restricts the scan to one day before columns are even read.
5. Net bytes scanned fall from ~1 TB (CSV, full-row) to tens of MB (one day × two columns × compressed).

Output:

Metric	CSV	Parquet (snappy)
Storage	~1 TB	~250 GB
Bytes scanned (the query)	~1 TB	~tens of MB
Splittable / parallel	poor (gzip) / ok (plain)	yes (row groups)

Why this works — concept by concept:

• Columnar projection — storing columns contiguously lets the reader fetch only revenue and category, discarding 23 columns the query never names.
• Predicate pushdown via statistics — per-row-group min/max/null lets the engine skip blocks with no matching category, decoding a fraction of the file.
• Encoding + compression — homogeneous columns compress far better than mixed-type CSV rows, cutting both storage and the bytes that must be read.
• Cost — on a per-TB-scanned engine the query bill drops by orders of magnitude, and storage drops ~4×; the one-time CTAS pays for itself almost immediately.

Right-sizing and compacting files — a worked teaching example

Detailed explanation. After format, the second organisational lever is file size. A partition full of tiny files is slow even in Parquet, and a partition of a few giant files under-parallelises; the fix is a compaction step that targets ~256 MB objects with healthy row groups. The trade-off is concrete: too many files means request and metadata overhead dominates; too few means a single worker chews one huge file while others idle.

• Too-small files — request overhead, tiny row groups, useless statistics, slow LIST.
• Too-large files — poor parallelism, memory pressure, long tail on one task.
• Sweet spot — ~128–512 MB objects, row groups ~128 MB, so each worker gets a meaty, prunable chunk.
• Compaction cadence — run per-partition after streaming, or on a schedule for hot partitions.

Question. A day-partition holds 20,000 files averaging 500 KB (streaming output). How do you right-size it, and what changes?

Input.

Fact	Value
Files	20,000
Avg size	~500 KB (total ~10 GB)
Target	~256 MB per file
Engine	Spark/Athena over the partition

Code.

# Spark compaction: read the partition, coalesce to target size, rewrite
target_bytes = 256 * 1024 * 1024
df = spark.read.parquet("s3://lake/curated/events/dt=2026-08-14/")
approx_files = max(1, int(df.count() * avg_row_bytes / target_bytes))  # ~40

(df.repartition(approx_files)                 # ~40 balanced partitions
   .sortWithinPartitions("category")          # cluster for better row-group stats
   .write.mode("overwrite")
   .option("parquet.block.size", target_bytes)
   .parquet("s3://lake/curated/events/dt=2026-08-14/"))

Step-by-step trace.

1. 10 GB of data spread over 20,000 files means ~500 KB objects — far below the 256 MB target, so request overhead dominates every read.
2. The compaction reads the whole partition and computes a target file count (~40 files × 256 MB ≈ 10 GB).
3. repartition(40) balances the data; sortWithinPartitions("category") clusters similar values so row-group min/max statistics become tight and predicate pushdown skips more.
4. The rewrite emits ~40 healthy Parquet objects and atomically overwrites the partition's file set.
5. Reads now issue ~40 GETs instead of 20,000, with large row groups that make pushdown effective.

Output:

Metric	Before	After
Files in partition	20,000	~40
Avg object size	~500 KB	~256 MB
Read request units	~20,000	~40
Pushdown effectiveness	poor	good

Rule of thumb. Target ~256 MB Parquet files and sort within partitions on a common filter column; compact streaming output on a schedule so the small-files tax never accumulates.

Interview scenario on format and organization

A team stores 5 years of JSON logs (50 TB) in one prefix and runs Athena queries that select a handful of fields filtered by date and service. Queries are slow and expensive. Redesign storage to minimise scan cost with minimal ongoing ops.

Solution Using date/service Hive partitions + compacted Parquet (snappy)

Answer choices.

• A. Keep JSON but gzip each file to shrink storage.
• B. Convert to partitioned (dt/service) compacted Parquet with snappy; query that.
• C. Load everything into a relational database and query there.
• D. Keep JSON, add more Athena workers to go faster.

Code.

Elimination:
A  gzip JSON -> smaller storage but still full-row, not splittable       [reject: scan cost]
C  load 50 TB into an RDBMS -> wrong tool, expensive, defeats the lake   [reject]
D  "more workers" -> Athena is serverless; scan bytes/$ unchanged        [reject: no lever]
B  partitioned + compacted Parquet (snappy)                              [ACCEPT]

Step-by-step trace.

1. Constraints: many small selects filtered by date+service over 50 TB, minimise scan cost, minimal ops.
2. A gzips JSON — storage shrinks, but queries still read whole rows and gzip is not splittable, so scan cost and parallelism barely improve — eliminate.
3. C moves 50 TB into an RDBMS, the wrong engine for lake-scale analytics and hugely expensive — eliminate.
4. D throws workers at Athena, but Athena bills per byte scanned regardless of concurrency — the scan cost is unchanged — eliminate.
5. B converts to Parquet partitioned by dt/service, so queries prune to the right prefixes, read only projected columns, use row-group statistics, and read compacted ~256 MB files — every cost axis drops with a one-time conversion and a scheduled compaction.

Output:

Need	Mechanism
Prune date+service	dt=/service= Hive partitions
Read few fields	Parquet projection pushdown
Skip blocks	row-group statistics
Low request overhead	compacted ~256 MB files

Why this works — concept by concept:

• Partition + project — pruning removes irrelevant partitions and Parquet reads only the handful of selected fields, so a query touches a sliver of 50 TB.
• Columnar compression — snappy on homogeneous columns shrinks both storage and scanned bytes while staying splittable for parallelism.
• Compaction — coalescing tiny JSON-era files into ~256 MB Parquet kills the request overhead that made the old layout slow.
• Cost — on Athena the bill is bytes scanned, and this design cuts that from tens of TB per query to low GB, a 10–100× reduction, for a one-time job plus a cheap scheduled compaction.

SQL
Topic — optimization
Scan-cost and format optimization problems

Practice →

ETL Topic — data-transformation Format conversion and compaction problems

Practice →

---

4. Lifecycle, storage classes & cost

Data ages, its access pattern cools, and the bill should cool with it — automatically

The invariant: most data is read heavily when fresh and rarely once old, so the cost win is a lifecycle rule that automatically transitions objects to colder, cheaper storage classes by age (and deletes them at the end), while you keep an eye on all four cost axes — storage, requests, scan, and egress — because optimising one can quietly inflate another. This is storage cost optimization in one sentence, and the s3 lifecycle engine is what makes it hands-off.

Storage classes — the cost/retrieval trade. Each class trades cheaper storage for higher retrieval cost, latency, and a minimum storage duration:

• Standard — hot data, frequent access, no retrieval fee, no minimum duration. The default for raw/, staging/, and hot curated/.
• Standard-IA (Infrequent Access) — ~monthly access; cheaper storage, a per-GB retrieval fee, and a 30-day minimum. Good for data past its hot window but still occasionally queried.
• One Zone-IA — like IA but stored in a single AZ (less durable); for reproducible/replaceable data where you accept the durability trade for lower cost.
• Glacier Instant Retrieval — archive priced storage with millisecond retrieval; for rarely-accessed data you still need fast when you do.
• Glacier Flexible Retrieval — cheaper still; retrieval takes minutes to hours; a 90-day minimum.
• Glacier Deep Archive — the cheapest; retrieval in hours; a 180-day minimum. For long-term compliance you almost never read.

Lifecycle rules — automate the tiering. A lifecycle configuration is a set of rules scoped by prefix and/or tag that fire on object age:

• Transition actions move objects to a colder class after N days (e.g. → Standard-IA at 30, → Glacier at 90, → Deep Archive at 365).
• Expiration actions delete objects after N days (retention).
• Noncurrent-version transitions/expiration manage old versions in versioned buckets so history does not accumulate forever.
• Abort incomplete multipart uploads after N days — a commonly-forgotten rule that reclaims storage from failed large uploads.

The four cost axes — where the bill really comes from. Optimising storage alone can backfire; keep all four in view:

• Storage ($/GB-month) — colder classes are cheaper, but mind minimum durations and per-object metadata overhead for tiny files.
• Requests (PUT/GET/LIST, per thousand) — millions of tiny objects cost real money in requests, and retrieving from Glacier adds per-object retrieval requests.
• Scan/compute (per TB on Athena, or cluster time on Spark) — layout and format dominate this axis, not storage class.
• Egress (per GB out of region/provider) — keep compute in the same region as the bucket; cross-region and internet egress is often the surprise line item.

Intelligent-Tiering — when to let the platform decide. Intelligent-Tiering monitors access and moves objects between frequent and infrequent tiers automatically for a small per-object monitoring fee, with no retrieval charges for the automatic tiers. It beats hand-written rules when access patterns are unpredictable or varied; hand-written lifecycle rules win when the pattern is known and uniform (e.g. "logs are hot 30 days then archived") because you avoid the monitoring fee. It is a poor fit for huge numbers of tiny objects, where the per-object fee stacks up.

Common trap choices to pre-empt.

• Deleting or moving aging data by hand / by cron — the answer is a lifecycle rule; manual ops drift and get forgotten.
• Deep Archive for data you actually query occasionally — hours-long retrieval and a 180-day minimum make it wrong for anything but true cold compliance.
• Ignoring minimum durations — transitioning a 10-day-old object to IA/Glacier can cost more because you pay the minimum-duration storage anyway.
• Forgetting incomplete-multipart and old-version cleanup — invisible storage that grows forever.

A lifecycle policy that tiers raw → IA → Glacier → expire — a worked teaching example

Detailed explanation. The canonical cost question is "raw data is hot for a month, occasionally read for a year, kept 7 years for compliance, then deleted — do it with no manual ops," and the answer is always a lifecycle rule, never a migration job. You encode the access-pattern-over-time as transition and expiration actions on age, and the platform executes them forever. The subtlety is choosing transition ages that respect minimum durations so you do not pay a penalty.

• 0–30 days — Standard (heavy reads).
• 31–90 days — Standard-IA (occasional).
• 91 days–7 years — Glacier/Deep Archive (compliance, rare reads).
• > 7 years — expire (delete).

Question. Write a lifecycle policy for raw/events/ implementing that timeline with no manual intervention.

Input.

Age of object	Access pattern	Target class
0–30 days	heavy	Standard
31–90 days	occasional	Standard-IA
91–2555 days	compliance only	Glacier Deep Archive
> 2555 days (~7 yr)	none	delete

Code.

{
  "Rules": [
    {
      "ID": "raw-events-tiering",
      "Filter": { "Prefix": "raw/events/" },
      "Status": "Enabled",
      "Transitions": [
        { "Days": 30,  "StorageClass": "STANDARD_IA" },
        { "Days": 90,  "StorageClass": "DEEP_ARCHIVE" }
      ],
      "Expiration": { "Days": 2555 },
      "AbortIncompleteMultipartUpload": { "DaysAfterInitiation": 7 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 90 }
    }
  ]
}

Step-by-step trace.

1. New objects land in Standard and serve the heavy 30-day read window at low latency with no retrieval fee.
2. At age 30 the rule transitions them to Standard-IA — cheaper storage for the occasional reads that follow (past the 30-day IA minimum, so no penalty).
3. At age 90 they move to Deep Archive — the cheapest class for the multi-year compliance hold where reads are rare and hours-long retrieval is acceptable.
4. At age 2555 (~7 years) they expire and are deleted automatically — no human ever touches them.
5. The multipart-abort and noncurrent-version rules quietly reclaim storage from failed uploads and superseded versions.

Output:

Window	Class	Relative storage cost
0–30 d	Standard	highest
31–90 d	Standard-IA	lower
91 d–7 y	Deep Archive	lowest
> 7 y	deleted	zero

Rule of thumb. "Reduce cost for aging data" is always a lifecycle rule that tiers down by age and expires at retention — never a manual migration or a cron job, and always mind each class's minimum duration.

A cost model — where the bill actually comes from — a worked teaching example

Detailed explanation. Engineers reach for colder storage classes when the real problem is scan cost or request cost, so it pays to decompose a bill into the four axes and attack the biggest one. Storage is often not the dominant term for an analytics lake; bytes scanned by queries and requests from millions of tiny files frequently dwarf it. The drill: estimate each axis, then apply the lever that matches the dominant axis — lifecycle for storage, compaction for requests, partition+format for scan, and region co-location for egress.

• Storage lever — storage class / lifecycle.
• Request lever — file compaction (fewer, bigger objects).
• Scan lever — partitioning + columnar format.
• Egress lever — keep compute in-region; avoid cross-region reads.

Question. A lake stores 100 TB and runs 10,000 Athena queries/day. Which axis dominates, and what do you fix first?

Input.

Axis	Rough monthly estimate	Driver
Storage	100 TB Standard	$/GB-month
Scan	10k queries × ~2 TB avg	per-TB scanned
Requests	500M tiny-file GETs	per-1k requests
Egress	mostly in-region	small

Code.

# Order-of-magnitude decomposition (illustrative unit costs):
storage : 100 TB   * $23/TB-mo                 ~= $2,300 / mo
scan    : 10k/day * 2 TB * 30 * $5/TB          ~= $3,000,000 / mo   <- DOMINANT
requests: 500M GET * $0.0004/1k                ~= $200 / mo
egress  : in-region                            ~= negligible

# Fix order: attack SCAN first (partition + Parquet), not storage class.
#   partition + columnar -> ~2 TB scanned drops toward ~20 GB per query
#   => scan cost falls ~100x, the other axes barely matter

Step-by-step trace.

1. Decompose the bill: storage ~\$2.3k, requests ~\$200, egress negligible, scan ~\$3M — scan is 1000× the next axis.
2. The instinct to move data to Glacier would save a slice of the \$2.3k storage while doing nothing about the \$3M scan — the wrong lever.
3. The scan lever is layout+format: partition the hot tables and store Parquet so each query scans ~20 GB instead of ~2 TB.
4. That single change cuts the dominant axis ~100×, taking the query bill from ~\$3M toward ~\$30k, after which storage-class tuning is worth revisiting.

Output:

Axis	Before	After the right lever
Scan	~\$3.0M/mo	~\$30k/mo (partition + Parquet)
Storage	~\$2.3k/mo	~\$1.5k/mo (later, lifecycle)
Requests	~\$200/mo	lower (compaction)
Egress	~\$0	~\$0

Rule of thumb. Decompose the bill into storage, request, scan, and egress before optimising — for an analytics lake the scan axis usually dominates, so partitioning and columnar formats beat storage-class tuning by orders of magnitude.

Interview scenario on cost optimization

A company keeps 8 years of IoT telemetry (300 TB) in S3 Standard. The last 60 days are queried constantly; 60 days–2 years occasionally; older data only for rare audits. The storage bill is large and someone deletes old files by hand each quarter. Cut cost with no manual ops and no data loss.

Solution Using lifecycle transitions (IA → Deep Archive) + expiration, keeping hot data queryable

Answer choices.

• A. Move everything to Glacier Deep Archive to minimise storage cost.
• B. Lifecycle rules: Standard → Standard-IA at 60 d → Deep Archive at 2 y → expire at 8 y; keep hot data in Standard.
• C. Keep manually deleting quarterly but do it monthly instead.
• D. Compress all files with gzip and leave everything in Standard.

Code.

Elimination:
A  all -> Deep Archive -> hot 60-day queries now take hours to retrieve   [reject: latency]
C  manual deletes -> drift, human error, still all-Standard for hot+cold  [reject: ops]
D  gzip in Standard -> some storage saved, but cold data still hot-priced [reject: partial]
B  lifecycle tiering by age + expiration                                  [ACCEPT]

Step-by-step trace.

1. Constraints: hot 60 days must stay fast, cold data should get cheap, old data expires, no manual ops, no data loss.
2. A dumps everything into Deep Archive, so the constantly-queried last 60 days would need hours-long, per-object retrieval — it breaks the hot workload — eliminate on latency.
3. C keeps the manual deletion anti-pattern (just more often), which still drifts, risks human error, and leaves hot and cold data both in Standard — eliminate on ops.
4. D compresses but leaves cold data in the expensive Standard class and does nothing about retention — a partial fix — eliminate.
5. B keeps the hot 60 days in Standard for fast queries, transitions to Standard-IA at 60 days, to Deep Archive at 2 years for the audit-only tail, and expires at 8 years — all automatic, all durable.

Output:

Window	Class	Outcome
0–60 d	Standard	fast queries, no retrieval fee
60 d–2 y	Standard-IA	cheaper, occasional reads
2–8 y	Deep Archive	cheapest, audit-only
> 8 y	expired	retention enforced

Why this works — concept by concept:

• Age-based tiering — mapping access-cools-with-age onto transition actions moves each object to the cheapest class its access pattern allows, without touching the hot data.
• Keep hot data hot — leaving the last 60 days in Standard preserves low-latency, no-retrieval-fee queries, which Deep Archive would destroy.
• Automated expiration — an expiration action enforces the 8-year retention deterministically, replacing the error-prone quarterly manual delete.
• Cost — the bulk of 300 TB shifts to IA/Deep Archive pricing while the small hot slice stays in Standard, cutting storage cost dramatically with zero ongoing ops.

SQL
Topic — optimization
Storage-cost and lifecycle optimization problems

Practice →

Design Topic — design Data-lake storage and retention design problems

Practice →

---

5. Performance, consistency, security & the lakehouse

Once data is laid out well, the remaining wins are throughput, encryption, and a table format that adds ACID over plain objects

The invariant: object storage delivers massive throughput when you spread requests across many key prefixes and upload large objects in parallel parts, it is now strongly read-after-write consistent but still has no multi-object transactions, and the security and correctness gaps are closed by encryption + least-privilege IAM and by a table format (Iceberg/Delta/Hudi) that layers ACID, snapshots, and schema evolution over the raw files. This is where a bucket of objects becomes a lakehouse, blurring the old data lake vs data warehouse line.

Request throughput scales per prefix. S3 sustains very high request rates — thousands of GET/PUT per second — and that capacity scales per prefix. Historically you hand-distributed keys (adding a hash prefix) to spread load; S3 now auto-scales per prefix, but the layout principle still holds: many balanced prefixes parallelise better than one hot prefix. For a lake, Hive partitioning already spreads keys across many prefixes, so a well-partitioned table is also a well-parallelised one.

• Parallelise reads/writes across prefixes and objects; a single object is still one stream.
• Multipart upload splits a large object into parts uploaded in parallel, improving throughput and resilience (retry a failed part, not the whole file); it is required above 5 GB and wise above ~100 MB.
• Range GETs let a reader fetch a byte range (a Parquet row group) without downloading the whole object.

Consistency — strong, but not transactional. As covered earlier, reads are strongly consistent after writes. The gap that remains is multi-object atomicity: writing a "table" as 200 new Parquet files and deleting 200 old ones is 400 independent operations with no transaction around them. A reader listing mid-swap can see a mix. This is precisely the correctness hole table formats fill with an atomic metadata commit.

Security — the non-negotiable defaults. Object storage is where breaches happen, almost always via misconfiguration:

• Block Public Access — enable at the account and bucket level; the default posture for a data lake is nothing public.
• Encryption at rest — SSE-S3 (provider-managed keys) or SSE-KMS (your KMS keys, with audit trail and per-key access control); enable a bucket default so every object is encrypted.
• Least-privilege IAM + bucket policies — grant the narrowest action on the narrowest prefix to a per-workload role; a Spark job that reads curated/ and writes staging/ should not have s3:* on the whole bucket.
• Encryption in transit — enforce TLS via a bucket policy condition (aws:SecureTransport).

Table formats — the lakehouse layer. Iceberg, Delta Lake, and Hudi all solve the same core problem: they add a metadata/manifest layer over the raw files so a set of file changes commits atomically, giving you database-like guarantees on object storage:

• ACID commits — a write either fully appears or not at all; concurrent writers don't corrupt the table (optimistic concurrency).
• Snapshots + time travel — every commit is a snapshot; you can query the table "as of" a past version or timestamp.
• Schema + partition evolution — add/rename columns and even change partitioning without rewriting all data; Iceberg hidden partitioning lets queries prune without the user writing dt= in the path or predicate.
• Efficient upserts/deletes — MERGE/DELETE on immutable files via copy-on-write or merge-on-read, instead of hand-managed rewrites.

Data lake vs warehouse vs lakehouse. A data lake is cheap object storage holding raw/curated files in open formats — flexible, but historically without ACID or strong schema guarantees. A data warehouse is a managed system with ACID, indexing, and fast SQL, but pricier and more rigid. A lakehouse puts a table format (Iceberg/Delta/Hudi) on the lake to get warehouse-like ACID, schema, and performance on cheap open storage — one copy of data, open formats, and SQL engines and ML both reading it.

Common trap choices to pre-empt.

• A public bucket "just for sharing" — the classic breach; use pre-signed URLs or a policy, never public.
• s3:* on the whole bucket for a job — over-privileged; scope to prefix and action.
• Hand-managing upserts on raw files — race conditions and half-visible tables; use a table format.
• One giant object instead of multipart — slow, fragile uploads with no partial retry.

Prefix throughput + multipart upload — a worked teaching example

Detailed explanation. When an ingestion needs to write terabytes fast, two levers dominate: spread objects across many prefixes so request capacity parallelises, and upload each large object with multipart so a single file streams through many connections. The failure mode to avoid is a single hot prefix plus whole-object PUTs, which serialises throughput and makes any failure re-upload the entire file.

• Many prefixes — Hive partitions already fan keys across prefixes, parallelising request capacity.
• Multipart upload — parallel parts per object; retry a failed part, not the whole object.
• Part sizing — parts of ~64–256 MB balance parallelism and overhead (max 10,000 parts).
• Range reads — consumers fetch only the row groups they need.

Question. You must land 2 TB of hourly Parquet into a partitioned table quickly and resiliently. How do you lay out writes?

Input.

Fact	Value
Volume	2 TB, ~200 files of ~10 GB
Layout	dt=/hour= partitions (many prefixes)
Constraint	fast + resilient to transient failures
Object size	~10 GB each (>5 GB → multipart required)

Code.

# Writes fan across many partition prefixes (parallel request capacity):
s3://lake/curated/events/dt=2026-08-14/hour=00/part-0000.parquet
s3://lake/curated/events/dt=2026-08-14/hour=01/part-0000.parquet
...                                     hour=23/

# Each ~10 GB object uploaded multipart (parts in parallel; retry a part):
aws s3 cp part.parquet s3://.../hour=00/ \
  --expected-size 10737418240        # SDK/CLI splits into ~64-256MB parts

# Lifecycle safety net: abort incomplete multipart uploads after 7 days
#   (reclaims storage from parts of failed uploads)

Step-by-step trace.

1. Writes target 24 hourly prefixes under the day, so request load spreads across many prefixes instead of hammering one.
2. Each ~10 GB object exceeds the 5 GB single-PUT limit, so the CLI/SDK automatically uses multipart, uploading parts of ~64–256 MB in parallel.
3. If a part fails on a flaky network, only that part retries — not the whole 10 GB — so throughput and resilience both improve.
4. An abort-incomplete-multipart lifecycle rule cleans up parts from any upload that never completed, so failed uploads don't silently accrue storage.
5. Downstream readers use range GETs to pull only the row groups a query needs from each large object.

Output:

Approach	Parallelism	Failure cost
One prefix, whole-object PUT	serialised, single stream	re-upload whole file
Many prefixes + multipart	high, many streams	retry one part

Rule of thumb. Spread writes across partition prefixes and always multipart-upload large objects — then add an abort-incomplete-multipart lifecycle rule so failed uploads don't leak storage.

An Iceberg table over S3 — snapshots, time travel, hidden partitioning — a worked teaching example

Detailed explanation. The lakehouse upgrade is putting a table format over the raw files, and Iceberg is the clearest example: the data stays as Parquet in your bucket, but an Iceberg metadata layer tracks which files belong to which snapshot, so writes commit atomically, you can time-travel to any snapshot, and partitioning is hidden — the engine prunes by a partition transform (e.g. days(event_ts)) without you ever writing dt= in the query. This closes the two object-store gaps at once: no multi-object transaction, and no schema/partition evolution.

• Atomic commits — a write swaps the current snapshot pointer in one metadata operation; readers never see a half-written table.
• Time travel — query ... FOR VERSION AS OF / ... FOR TIMESTAMP AS OF a prior snapshot.
• Hidden partitioning — declare PARTITIONED BY days(event_ts); queries filtering event_ts prune automatically, no dt= predicate needed.
• Schema/partition evolution — add columns or change the partition spec without rewriting history.

Question. Model an events table on S3 with Iceberg so concurrent writers are safe and analysts can query a past state. Show partitioning, a merge, and time travel.

Input.

Requirement	Iceberg feature
Concurrent-writer safety	atomic snapshot commit
Prune by day without dt=	hidden partitioning days(event_ts)
Query yesterday's state	time travel (snapshot)
Upserts on immutable files	MERGE INTO (copy-on-write)

Code.

-- Table lives as Parquet under s3://lake/curated/events/, managed by Iceberg
CREATE TABLE curated.events (
  event_ts TIMESTAMP, user_id BIGINT, country STRING, revenue DECIMAL(10,2)
)
USING iceberg
PARTITIONED BY (days(event_ts))              -- hidden partitioning transform
LOCATION 's3://lake/curated/events/';

-- Concurrent-safe upsert (atomic snapshot commit under the hood):
MERGE INTO curated.events t
USING staging.events_delta s
ON t.user_id = s.user_id AND t.event_ts = s.event_ts
WHEN MATCHED THEN UPDATE SET revenue = s.revenue
WHEN NOT MATCHED THEN INSERT *;

-- Query prunes by day WITHOUT a dt= column, and time-travels to a snapshot:
SELECT country, SUM(revenue) FROM curated.events
WHERE event_ts >= TIMESTAMP '2026-08-14 00:00:00'
  AND event_ts <  TIMESTAMP '2026-08-15 00:00:00'
GROUP BY country;

SELECT COUNT(*) FROM curated.events
FOR TIMESTAMP AS OF TIMESTAMP '2026-08-13 23:59:59';   -- yesterday's snapshot

Step-by-step trace.

1. The table declares days(event_ts) as a hidden partition transform, so Iceberg physically clusters files by day but the analyst filters on event_ts directly — pruning happens without a dt= path predicate.
2. The MERGE computes changed files and commits a new snapshot by atomically swapping the metadata pointer; a concurrent writer either sees the old or new snapshot, never a half-applied mix (optimistic concurrency retries on conflict).
3. The analytical query filters event_ts for one day; Iceberg maps that to the day partitions and reads only those Parquet files.
4. The FOR TIMESTAMP AS OF query reads the snapshot that was current yesterday — the files it referenced are still tracked — giving reproducible time travel.

Output:

Capability	Plain files on S3	Iceberg over S3
Multi-file write atomicity	none (400 ops)	atomic snapshot commit
Prune without dt= predicate	no	hidden partitioning
Query a past state	no	time travel
Safe concurrent upserts	race-prone	optimistic concurrency

Rule of thumb. When a lake needs ACID, concurrent writers, upserts, or reproducible history, add a table format (Iceberg/Delta/Hudi) over the same Parquet files rather than hand-managing file swaps.

Interview scenario on table format selection

Several jobs write to the same S3 events table concurrently; analysts need consistent reads (never a half-written table), the schema evolves over time, and the team wants upserts and the ability to reproduce a past report. Choose a storage design.

Solution Using an Iceberg (or Delta/Hudi) table over Parquet on S3

Answer choices.

• A. Plain Parquet files in Hive partitions; writers coordinate by convention.
• B. An Iceberg table over Parquet on S3 (atomic commits, snapshots, schema evolution, MERGE).
• C. Move the table into a relational warehouse and drop the lake.
• D. One big CSV file that jobs append to.

Code.

Elimination:
A  plain Parquet + "coordinate by convention" -> races, half-visible tables [reject: no ACID]
C  move to a warehouse -> loses open storage/cost; overkill for the need     [reject]
D  append to one CSV -> objects are immutable; no append, no concurrency     [reject: impossible]
B  Iceberg/Delta/Hudi table format over Parquet on S3                        [ACCEPT]

Step-by-step trace.

1. Constraints: concurrent writers, consistent reads, schema evolution, upserts, reproducible history.
2. A leaves writers uncoordinated over raw files, so a reader can list a table mid-swap and multi-file writes are not atomic — it fails the consistency and concurrency requirements — eliminate.
3. C abandons the cheap open lake for a warehouse — it would work but throws away the storage-cost and open-format benefits and is more than the requirement needs — eliminate.
4. D relies on appending to an object, which object storage forbids (immutability), and a single file has no concurrency story at all — eliminate as impossible.
5. B keeps the data as Parquet on cheap S3 but adds a table format: atomic snapshot commits give consistent reads and safe concurrency, schema/partition evolution handles change, MERGE gives upserts, and snapshots give reproducible time travel.

Output:

Requirement	Mechanism
Consistent reads under concurrent writes	atomic snapshot commits
Schema changes over time	schema evolution
Upserts on immutable files	MERGE (copy-on-write / merge-on-read)
Reproduce a past report	snapshot time travel

Why this works — concept by concept:

• Atomic metadata commit — swapping a snapshot pointer in one operation is what turns 400 non-transactional file ops into a single all-or-nothing table change, giving readers a consistent view.
• Schema + partition evolution — the metadata layer records schema versions, so columns and partitioning can change without rewriting years of data.
• Upserts over immutable files — MERGE writes new files and re-points metadata, respecting object immutability while giving database-style updates.
• Cost — you keep one copy of open Parquet on cheap object storage and add only a small metadata layer, getting warehouse-like guarantees without warehouse-like storage bills.

Design
Topic — design
Lakehouse and table-format design problems

Practice →

Design
Course — ETL system design
ETL system design for data engineering interviews

Practice →

---

Cheat sheet — object-storage layout, partitioning & cost recipes

Layout keyword → decision lookup (memorise this table).

Scenario keyword	Decision
"organise by trust level"	zones: raw/ → staging/ → curated/
"engine should skip data"	Hive k=v partitions on filtered columns
"always filtered by date"	partition #1 = dt=
"high-cardinality id"	keep as a column, never a partition
"queries select few fields"	Parquet/ORC columnar
"shrink storage + scan"	columnar + snappy/zstd compression
"too many tiny files"	compact to ~256 MB objects
"aging data, cut cost"	lifecycle transition by age
"rarely read, keep for years"	Glacier Flexible / Deep Archive
"unpredictable access pattern"	Intelligent-Tiering
"must control encryption keys"	SSE-KMS
"never expose the bucket"	Block Public Access + bucket policy
"concurrent writers, ACID, upserts"	Iceberg / Delta / Hudi table format
"reproduce a past report"	table-format snapshot / time travel
"warehouse guarantees on cheap storage"	lakehouse = table format on the lake

Partitioning do/don't checklist.

• Partition on columns queries actually filter, most-universal (usually dt) first.
• Keep partition columns low-cardinality (tens–thousands of values, not millions).
• Ensure each leaf partition holds enough data for healthy (~256 MB) files.
• Don't partition on user_id/uuid/second-granularity timestamps.
• Don't wrap partition columns in functions in WHERE — it can defeat pruning.
• Don't stack so many partition columns that leaves shrink to KB (over-partitioning).

Format + compression picker. Analytics reads → Parquet/ORC + snappy (or zstd for better ratio). Interchange/ingest → JSON/CSV is fine transiently, but convert hot tables to Parquet. Avoid gzipped CSV for large files (not splittable). Target 128–512 MB files with row groups ~128 MB.

Storage-class / lifecycle recipe. Hot (raw/staging/hot curated) → Standard. Cooling (past hot window) → Standard-IA (mind 30-day min). Cold compliance → Glacier Flexible (90-day min) / Deep Archive (180-day min). Always add: transition by age, expiration at retention, abort incomplete multipart (7 d), noncurrent-version expiration. Unpredictable access → Intelligent-Tiering.

Cost-control checklist. Decompose the bill into storage / requests / scan / egress and attack the biggest. For analytics lakes the scan axis usually dominates → partition + Parquet first. Compact small files to cut requests. Keep compute in-region to cut egress. Tier aging data with lifecycle rules to cut storage.

---

Frequently asked questions

What is object storage and how is it different from a filesystem or a database?

Object storage is a flat, distributed key-value store that maps a string key to an immutable blob of bytes, exposed over an HTTP API (PUT/GET/LIST) rather than as a mounted filesystem. Unlike a filesystem there are no real directories — "folders" are just shared key prefixes — and unlike a database there is no built-in index, query planner, or transaction across objects. That is why object storage for data engineers is largely the discipline of key layout: the way you name and group keys is the only access plan the system gives you.

How should I partition data in S3 for a data lake?

Use Hive-style key=value path segments (e.g. dt=2026-08-14/country=US/) on the low-cardinality columns your queries filter on, most-universal first — date is almost always partition #1. Keep high-cardinality fields like user_id as columns inside the file, not partitions, or you create millions of tiny partitions. Good s3 partitioning lets the engine prune to a few prefixes and scan a sliver of the data; over-partitioning creates a small-files problem that erases the benefit.

Parquet vs CSV — which should a data engineer use, and why?

For any table that gets queried repeatedly, use Parquet (or ORC). Columnar storage lets a query read only the columns it selects and skip row groups via statistics, and homogeneous columns compress far better than CSV rows — so the same query scans a small fraction of the bytes. CSV/JSON are fine as transient interchange or ingest formats, but converting hot tables to compressed Parquet typically cuts both storage and per-query scan cost by 10× or more.

What are S3 storage classes and when do I use Glacier / Deep Archive?

Storage classes trade cheaper storage for higher retrieval cost, latency, and a minimum duration: Standard (hot), Standard-IA (~monthly access), Glacier Instant/Flexible (rare access, minutes-to-hours retrieval), and Deep Archive (cheapest, hours retrieval, 180-day minimum). Use Glacier/Deep Archive for compliance data you almost never read, and drive the transitions with s3 lifecycle rules by age rather than moving data by hand. Never park constantly-queried data in Deep Archive — the retrieval latency and cost will hurt.

Do I still need a table format like Iceberg or Delta on top of S3?

If you need ACID commits, safe concurrent writers, upserts/deletes, schema evolution, or reproducible time travel, yes — plain files on S3 give you none of those because object storage has no multi-object transaction. A table format (Iceberg, Delta Lake, or Hudi) adds a metadata layer over your existing Parquet files so a set of file changes commits atomically. If your lake is append-only, single-writer, and never needs history, plain partitioned Parquet may be enough.

Data lake vs data warehouse vs lakehouse — what's the difference?

A data lake is cheap object storage holding raw and curated files in open formats — flexible and inexpensive, but historically without ACID or strong schema guarantees. A data warehouse is a managed system with ACID, indexing, and fast SQL, but pricier and more closed. A lakehouse resolves the data lake vs data warehouse tension by putting a table format on the lake, giving warehouse-like ACID, schema, and performance on one copy of open data in cheap storage.

---

Practice on PipeCode

Turn object-storage theory into layout muscle memory

Guides explain buckets and partitions. PipeCode drills build the reflex the job actually rewards — reading a layout and predicting the scan, choosing partition columns that prune, and defending the cost-vs-latency trade-off on storage classes and formats. Pipecode.ai is Leetcode for Data Engineering — scenario-first practice on SQL, ETL, and pipeline design tuned to the trade-offs a data lake on object storage demands.

Practice ETL problems →
Practice optimization problems →