"Bytes on Disk" — File Formats, Parquet and Object Storage

Skim Card

One thing to remember: in analytics the file format is the query optimiser’s best friend: the right layout lets the engine skip most of the bytes, and the cheapest byte is the one never fetched.

• Same rows: JSON Lines 1,650 MiB, CSV 628 MiB, Snappy Parquet 63 MiB. A 4-column query fetched 707 MiB of CSV, 9.1 MiB of Parquet.
• gzip shrinks text 9–20 times, but every query still decompresses and parses all of it, from the first byte, in order.
• Footer min/max lets Parquet skip row groups; sort for your filter. By date, one day read 1 of 50 row groups, the basket lookup 49.
• Requests cost latency and money: engines fetch extra bytes to save requests, and 600 small files took 2,155 GETs where one took 147.
• Object storage has no rename or multi-file transaction, so a globbed folder isn’t a table; table formats commit with conditional writes.
• Arrow is Parquet’s in-memory twin: 6 million rows reached Python in 0.02 s as Arrow, 1.78 s as tuples.

Use …	When …
Parquet (ZSTD or Snappy)	analytical data at rest; data files under Iceberg and Delta
Avro	messages and row-at-a-time landing, with a schema registry (Part 27)
JSON Lines	raw semi-structured landing you convert soon; small feeds people debug by eye
CSV	exchanging with outsiders and spreadsheets; convert at the boundary
Arrow	moving data between engines and languages in memory

Verified against: DuckDB 2.0.0 (parquet and avro extensions), PyArrow 26.0.0, Polars 2.1.0, SeaweedFS 4.51 · docs: parquet-format 2.14.0, Avro 1.12, Iceberg 1.12, Trino 484, parquet-java, Spark 4.2, Amazon S3 · 28 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill

Story Opening

Ingrid’s profile from Part 2 still sat in Rohan’s notes: a third of every pipeline run went on decompressing gzipped JSON and parsing fields nobody used. Rohan’s fix was ready by Monday: “gzip is already compressed. A faster JSON parser, more cores, done. It’s a serialisation problem.”

“Between services, the reader wants the whole message,” Ingrid said. “Farah’s report wants four columns out of thirteen, and one day out of a hundred and twenty-six. No parser makes reading bytes you don’t need free. Write the same table four ways, put them in the bucket, and count what comes back.”

The Quick Map

Concepts, with use cases

Row formats and columnar formats

A row format writes one record after another, all fields together: CSV, JSON Lines, Avro. It suits producing and moving records: append one when it exists, read it whole. A columnar format writes all values of one column together, in blocks of rows: Parquet and ORC. It suits reading a few columns of many records.

What breaks if you keep row formats for analytics. Every query pays for every field of every row, so the bill grows with the width of the table, not the width of the question (step 2 measures it).

Larkspur. The tills upload JSON Lines because that’s what a till can produce (Part 2’s raw zone), and raw stays that way forever. Everything finance queries is written as Parquet.

Where else you’ll see this. A food-delivery app’s order service publishes Avro events to Kafka, one complete record per message, with the schema in a registry; an hourly job lands them as Parquet, where analysts look at four of the forty fields across a year of orders.

Five formats, compared

Splittable means several readers can start in the middle of one file: plain text at line boundaries, Avro at the sync markers between blocks, which exist “to permit efficient splitting of files”. A gzip stream can’t be split (Tips & Gotchas).

Schema evolution differs as much. CSV and JSON have nothing to evolve: a renamed column is just a different string. Avro resolves the writer’s schema (stored in the file) against the reader’s schema, using defaults for added fields, which is why it’s the usual choice for Kafka topics with a registry (Part 27): a card network’s authorisation events gain a field every few months, and consumers on last year’s schema keep reading. Parquet stores a schema per file; making many files behave as one evolving table is the table format’s job (Part 7). ORC (2.3.1) is capable, with a smaller ecosystem today; this part uses Parquet.

Inside a Parquet file

A Parquet file is a sequence of row groups (blocks of rows), each holding one column chunk per column, each chunk made of pages: an optional dictionary page, then data pages. Values are encoded first (dictionary, run-length, delta and byte-stream-split encodings), then compressed with a codec. At the end sits the footer: the schema plus, for every column chunk, its byte offset, sizes, encodings and statistics (min, max, null count). Nested data (lists, structs) is stored with the repetition and definition levels from Google’s Dremel paper, so each leaf field is its own column. That matters for Larkspur: a till event’s lines is a list of structs and clickstream events carry nested properties, and stored nested in Parquet, a query reading only lines.sku reads only that leaf.

Two optimisations follow from that layout, and every engine uses both:

• Projection pruning: read only the column chunks the query names.
• Predicate pushdown: compare the filter with each row group’s min/max and skip row groups that can’t match. Optional bloom filters answer “is this exact value possibly here?” for a column chunk, and an optional page index stores min/max per page.

What breaks without it. Without statistics a one-day query reads every row group; without projection, every column. You pay on every query.

Larkspur. Most finance questions start with a date range, so sales lines get sorted by business_date before writing (step 4).

Where else you’ll see this. An adtech platform writes impression logs sorted by advertiser_id and hour, so the “my campaign, last week” query each advertiser runs touches a few row groups of a petabyte-scale table.

Apache Arrow: the in-memory side

Arrow “defines a language-independent columnar memory format for flat and nested data”. Parquet is optimised for storage (encoded, compressed, read from disk or network); Arrow is optimised for computation (contiguous, uncompressed buffers that CPUs process in tight loops). When DuckDB hands a result to Polars, or a client reads BigQuery’s Storage Read API in Arrow format, data travels as Arrow buffers instead of one object per row.

What breaks without it. Every engine-to-language hop converts row by row (step 5 measures the cost).

Larkspur. Data scientists pull sales lines from DuckDB into Polars for forecasting (Part 11) without a copy step in between. Elsewhere: a quant team reads market data from a warehouse through an Arrow-based API straight into dataframes, because converting billions of rows to objects took longer than the analysis.

Object storage is not a filesystem

Object storage (Amazon S3, Google Cloud Storage, Azure Blob Storage, SeaweedFS in the lab) stores whole, immutable objects under keys in a flat namespace; folders are a naming convention. Compared with a filesystem:

• No rename, no append. A “move” is a copy plus a delete, and both objects exist in between (step 6). S3’s atomic RenameObject (June 2025) works only in S3 Express One Zone directory buckets; Azure’s Data Lake Storage Gen2 hierarchical namespace has atomic directory operations.
• LIST is a request, and it’s paged. ListObjectsV2 returns at most 1,000 keys per call. Listing a big table’s files costs one request per thousand, priced like a write.
• Consistency is strong now. S3 has had strong read-after-write consistency, listings included, since 1 December 2020; Cloud Storage documents the same. “Eventually consistent S3” advice is out of date.
• Conditional writes. If-None-Match: * creates an object only if the key doesn’t exist (S3 since August 2024); If-Match: <ETag> replaces it only if nobody changed it since you read it (S3 since November 2024). Cloud Storage has ifGenerationMatch (with 0 meaning “must not exist”) and Azure Blob supports both headers. That’s compare-and-swap for files.
• Requests have a price and a latency. S3 Standard in us-east-1 charges USD 0.005 per 1,000 PUT, COPY, POST or LIST requests, USD 0.0004 per 1,000 GETs and USD 0.023 per GB-month. AWS quotes first-byte latencies of “roughly 100–200 milliseconds” and at least 3,500 writes or 5,500 reads per second per partitioned prefix.

Why a folder of Parquet isn’t a table. Part 0 and Part 2 read the raw zone with a glob such as pos/events/*/*.jsonl.gz. A glob is a listing: the “table” is whatever keys exist at that moment. A writer replacing ten files is visible halfway; a retried job that leaves a second file doubles the data; and there’s no way to say “these 40 new files and these 12 deletions are one change”. A table format fixes this by writing data files first and then making one small conditional write, such as swapping a catalog pointer, the single moment the change becomes visible (Part 7). Delta Lake on S3 still documents a DynamoDB log store for multi-cluster writes, for the same reason.

Larkspur. Raw files are written once and never renamed: each till upload lands under its final key. Analytical tables go through Iceberg and the Polaris catalog. Of the five promises from Part 2, typed files serve correctness (no reader guesses a type), and treating folders as tables only through a table format serves uniqueness and completeness: no stray file doubles data, no half-written folder loses it.

Where else you’ll see this. A media company’s ingest jobs, ported from HDFS, “renamed” a _temporary/ prefix on success: atomic on HDFS, a slow copy plus deletes on object storage, visible halfway through.

The formats keep moving

parquet-format keeps adding types engines used to fake. VARIANT (2.11–2.12) gives semi-structured data a real type: Larkspur’s clickstream payloads, JSON strings today, are the case Part 7 tries. GEOMETRY and GEOGRAPHY (2.11) do the same for spatial data, such as a utility’s map of grid assets. The ALP float encoding is (preview) in 2.14: the spec is final, but use it only when every reader supports it. Vortex, a Linux Foundation (LF AI & Data) project, declares its file format stable while its APIs still change; DuckDB 2.0 has a vortex extension. Lance is a file and table format for multimodal AI data, in active development, with LanceDB built on it (Part 32). Neither replaces Parquet for a lakehouse today.

Under the Hood

A reader starts at the end

A reader fetches the last 8 bytes (footer length plus magic), then the footer, then decides from the footer alone which byte ranges to fetch. Step 3 does it by hand. The footer is 0.11% of the file, and it’s the only part read for row groups the filter rules out.

Statistics, and what they can’t do

Min/max statistics only help when a row group’s values sit close together, and that’s the sort order’s doing. Sorted by basket_id (Part 1’s layout), every row group covers all 126 days, so Part 1’s date filter skipped nothing; sorted by business_date, a row group covers at most four.

The reverse case: a date-sorted row group holds every store’s baskets for a few days, so its basket_id range runs from GB-0001-… to SG-0100-… and contains almost any ID. A bloom filter would answer the lookup, but DuckDB wrote them only for dictionary-encoded columns, and basket_id, with tens of thousands of distinct values per row group, isn’t one. The file has no page index either (DuckDB 2.0 wrote none; step 3), so pruning happens per row group, not per page.

A sort order is a decision about which questions are cheap. Iceberg’s sort orders and clustering (Part 10) make that decision part of the table instead of each writer’s habit.

Bytes against requests

On a laptop, SeaweedFS answers in about a millisecond; S3 quotes 100–200 ms to first byte and bills every request. At that latency, step 4’s run without prefetching makes 12–25 minutes of request waiting that the engine must hide with parallelism; the default run, 11–21 seconds. Small files hit the same wall from the other side (step 2). The defaults below encode the usual answer, row groups around 128 MB in files of hundreds of MB; Part 10 covers sizing and compaction.

Writers choose differently

Part 1 saw DuckDB’s Iceberg writer choose ZSTD and far bigger row groups than its own COPY. Writers’ defaults differ, even in units:

Row counts give very different byte sizes for narrow and wide tables. Set size and codec in the table’s properties, and check the footer of what a writer produced.

Hands-On

The code is in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-3). It needs the core profile only; DuckDB, PyArrow and Polars run on your machine. Peak container memory was 1.5 GiB (SeaweedFS 0.9 GiB of it, during the upload); the write step peaked at 6–7 GB resident on the host.

make up-core
make data upload   # once, if you skipped Part 0

Step 1: one table, thirteen ways

The table is Part 1’s sales_line: one row per POS basket line, 13 columns, built from the raw till events by sql/part01/sales_line.sql, sorted by basket_id. sql/part03/write_formats.sql writes it in every format and layout; an excerpt:

COPY sales_line TO '{out}/sales_line.csv' (FORMAT csv, HEADER);
COPY sales_line TO '{out}/sales_line.deflate.avro' (FORMAT avro, CODEC 'deflate');

-- Parquet as Part 1 wrote it: DuckDB's defaults (Snappy, row groups of 122,880 rows), then two other codecs.
COPY sales_line TO '{out}/sales_line.parquet' (FORMAT parquet);
COPY sales_line TO '{out}/sales_line.zstd.parquet' (FORMAT parquet, COMPRESSION zstd);

-- Sorted for the questions finance asks (by business date, then store), with three row-group sizes.
COPY (FROM sales_line ORDER BY business_date, store_id, basket_id, line_no)
TO '{out}/by_date.rg10k.parquet' (FORMAT parquet, ROW_GROUP_SIZE 10000);

-- One folder per store, one small file in each.
COPY sales_line TO '{out}/per_store' (FORMAT parquet, PARTITION_BY (store_id));

Writing Avro with COPY … (FORMAT avro) is documented in DuckDB’s avro extension repository, not yet on the 2.0 docs page. scripts/part03_formats.py write fills in {out} (data/lab/part03), runs every statement and uploads the results to s3://larkspur-lake/part03/:

uv run python scripts/part03_formats.py write

sales_line from data/raw: 6,055,922 rows, 13 columns (6.2s)

file                                    size   write
sales_line.csv                      658.5 MB    0.4s
sales_line.csv.gz                    71.6 MB   11.2s
sales_line.jsonl                   1730.4 MB    1.0s
sales_line.jsonl.gz                  88.5 MB   15.2s
sales_line.avro                     522.2 MB    3.0s
sales_line.deflate.avro              70.9 MB   21.3s
sales_line.parquet                   66.0 MB    0.2s
sales_line.zstd.parquet              41.0 MB    0.3s
sales_line.uncompressed.parquet     238.9 MB    0.2s
by_date.parquet                      69.2 MB    0.3s
by_date.rg10k.parquet                76.0 MB    0.5s
by_date.rg1m.parquet                 68.0 MB    0.6s
per_store/ (600 files)               68.3 MB    0.4s

uploaded to s3://larkspur-lake/part03/: 612 objects

JSON Lines is the biggest because every row repeats all 13 key names. Even uncompressed Parquet is about a third the size of CSV, thanks to encodings alone. The gzip and deflate writes took 11–21 seconds; every Parquet variant took under one.

Step 2: the same question from object storage

compare runs Part 1’s Q3 revenue query (four columns) over each format in the bucket, with bytes and GETs from DuckDB’s EXPLAIN ANALYZE, then over the local files. It turns off DuckDB’s external file cache so every run really fetches, and fails if any answer differs.

uv run python scripts/part03_formats.py compare

Q3 revenue by country and month (sql/part01/revenue_by_month.sql)
                                       ---- from s3://larkspur-lake/part03 ----  local disk
format                           stored     fetched  GETs   median      median
CSV                           628.0 MiB   706.6 MiB   370    0.92s       0.24s
CSV, gzip                      68.2 MiB    84.2 MiB    45    0.99s       0.89s
JSON Lines                   1650.2 MiB     1.6 GiB   842    1.52s       0.38s
JSON Lines, gzip               84.4 MiB   100.3 MiB    54    1.38s       1.28s
Avro                          498.0 MiB   498.0 MiB   250    1.59s       1.23s
Avro, deflate                  67.6 MiB    67.6 MiB    34    1.24s       1.13s
Parquet, uncompressed         227.8 MiB    10.2 MiB   147    0.21s       0.01s
Parquet, Snappy                62.9 MiB     9.1 MiB   147    0.03s       0.01s
Parquet, ZSTD                  39.1 MiB     7.9 MiB   148    0.03s       0.02s
Parquet, Snappy, 600 files     65.2 MiB    21.3 MiB  2155    0.32s       0.06s

all 10 return the same 9 rows
column names and types read back: identical in all

What the numbers say:

1. Row formats fetch the whole file; columnar formats fetch the question. CSV, JSON and Avro fetched at least their full size. Parquet fetched 8–10 MiB whatever its codec, because the query names four columns.
2. gzip trades CPU for storage. On local disk, plain CSV scanned in 0.24 s, read in parallel ranges; gzipped CSV took 0.89 s, decompressed from the first byte in order. Avro’s deflate is per block, so it stays splittable, but every row is still decoded.
3. Types survived everywhere, this time, because DuckDB’s sniffer guessed every CSV and JSON type right; other readers guess differently (Tips & Gotchas). Parquet and Avro carry their schema, so there’s nothing to sample: the per-run inference cost Part 2 tuned away with maximum_sample_files doesn’t exist for them.

Two results we can’t explain: CSV fetched 78.6 MiB more than its size (gzipped text about 16 MiB more), far beyond the sniffer’s 20,480-row sample; and uncompressed Parquet ran seven times slower than Snappy against SeaweedFS with the same requests. Treat lab timings as relative; bytes and requests carry over to S3.

Step 3: open a footer

scripts/part03_footer.py reads the date-sorted file from the end, as a reader does, then decodes the footer with PyArrow:

uv run python scripts/part03_footer.py

data/lab/part03/by_date.parquet: 69,214,626 bytes
first 4 bytes         b'PAR1' (magic)
last 8 bytes          b5 24 01 00 50 41 52 31 -> footer length 74,933, then b'PAR1'
footer                bytes 69,139,685 to 69,214,617 (0.11% of the file)
written by            DuckDB version v2.0.0 (build d54cbbe3df), format version 1.0
rows, row groups      6,055,922 in 50 row groups of 13 columns

row group 20: 122,880 rows, 4,975,019 bytes of column chunks
  business_date
    at byte         28,642,125: dictionary page + data pages, ('PLAIN_DICTIONARY',)
    size            64 encoded -> 68 stored
    statistics      min 2026-07-22, max 2026-07-25, nulls 0, distinct 4
    bloom filter    47 bytes; page index: none
  basket_id
    at byte         27,827,160: data pages only, ('PLAIN',)
    size            3,194,904 encoded -> 348,382 stored
    statistics      min GB-0001-20260723-00001, max SG-0100-20260724-00013, nulls 0, distinct None
    bloom filter    none; page index: none

read_row_group(20, 2 columns) -> Arrow table, 122,880 rows, 1,474,560 bytes in memory
  pyarrow.Field<business_date: date32[day] not null>, pyarrow.Field<amount_minor: int64 not null>

Read it from the top. 122,880 business_date values take 64 bytes: four distinct dates in a dictionary page, then small integer indexes, run-length encoded, which sorted data turns into a few long runs. The file records the v1 name PLAIN_DICTIONARY (DuckDB writes format v1); v2 writers use RLE_DICTIONARY. That’s why uncompressed and Snappy Parquet fetched almost the same bytes in step 2: the query’s four columns are dictionary-encoded, and little is left for a codec. Codecs earn their keep on high-cardinality columns: Snappy shrinks basket_id’s 3.2 MB of PLAIN values to 348 KB, because consecutive IDs share long prefixes. At the bottom, 68 bytes of dates on disk become 491,520 bytes of Arrow, 4 per date, ready for vectorised arithmetic.

The footer is queryable in SQL too: sql/part03/footer.sql runs DuckDB’s parquet_schema(), parquet_file_metadata() and parquet_metadata(). Its last query asks the footers of both sort orders which row groups could hold 15 August, without reading any data (the first three results are left out here):

uv run python scripts/duck.py --local sql/part03/footer.sql

┌──────────────────┬────────────┬─────────────────┬───────────────────┐
│      sorted      │ row_groups │ may_hold_15_aug │ widest_range_days │
│     varchar      │   int64    │      int64      │       int64       │
├──────────────────┼────────────┼─────────────────┼───────────────────┤
│ by basket_id     │         50 │              50 │               126 │
│ by business_date │         50 │               1 │                 4 │
└──────────────────┴────────────┴─────────────────┴───────────────────┘

Step 4: sort for the filter

skip runs one business day’s sales by country (sql/part03/one_day.sql) and Part 1’s basket lookup over four layouts; “row groups read” comes from EXPLAIN ANALYZE. The last one_day row repeats the first with SET disable_parquet_prefetching = true: every byte range its own request.

uv run python scripts/part03_formats.py skip

== one_day
layout                                         row groups read     fetched  GETs   median
by basket_id, 122,880-row groups                       50 / 50    43.0 MiB   107     29ms
by business_date, 122,880-row groups                    1 / 50   321.0 KiB     5      5ms
by business_date, 10,000-row groups                    6 / 592     1.0 MiB    22     24ms
by business_date, 1,000,000-row groups                   1 / 7     1.5 MiB     6      8ms
by basket_id, prefetching off                          50 / 50     8.1 MiB  7478    655ms

== basket_lookup
layout                                         row groups read     fetched  GETs   median
by basket_id, 122,880-row groups                        1 / 50   826.0 KiB     4      7ms
by business_date, 122,880-row groups                   49 / 50    35.8 MiB   146     35ms
by business_date, 10,000-row groups                  176 / 592     8.5 MiB   236     55ms
by business_date, 1,000,000-row groups                   7 / 7    36.0 MiB    28     39ms

• Sorting decided everything: one day cost 43 MiB sorted by basket, 321 KiB sorted by date.
• Row-group size is a dial. Small groups skip finer but cost more requests and a bigger file (the 10,000-row file was the largest Snappy file in step 1); 1,000,000-row groups make fewer requests but read more around each match. DuckDB’s default suited this table best.
• The engine chose requests over bytes. With prefetching on, DuckDB coalesces nearby byte ranges into larger GETs, using a cost model to choose how big a gap to read through (parquet_prefetch_column_gap, NULL by default so the model adapts). Off, it fetched a fifth of the bytes in 70 times the requests and ran 22 times slower, at millisecond latency.

Step 5: hand the rows to Python

uv run python scripts/part03_arrow.py

6,055,922 rows x 4 columns, median of 3
.fetchall() -> Python tuples           1.78s
.to_arrow_table() -> Arrow table       0.02s  211 MiB of Arrow buffers
.pl() -> Polars DataFrame              0.02s  via pl.from_arrow, no row objects

DuckDB’s .pl() builds an Arrow table and wraps it with polars.from_arrow.

Step 6: what the bucket can and can’t do

scripts/part03_object_store.py uses the S3 API through botocore: two writers race to create one commit file, then to move one pointer; then a “rename” and two listings.

uv run python scripts/part03_object_store.py

1a. Two writers create the same commit file, If-None-Match: *
    writer A: "29a08ce0a42700f289a556b20dae9554"
    writer B: PreconditionFailed (HTTP 412)
    the object holds {"writer": "A"}
1b. Two writers move the same pointer from version 1, If-Match: <ETag they read>
    both read version 1, ETag "fce8ed52ad0bc010cd9a8dd97f615e98"
    writer A -> version 2: "03b9e3de02de58348c2b34f8d30c2866"
    writer B -> version 2: PreconditionFailed (HTTP 412)
    the pointer holds {"version": 2, "writer": "A"}; B must re-read, rebase and retry
2. 'Rename' staging/part-0.parquet to data/part-0.parquet
    after CopyObject:   ['data/part-0.parquet', 'staging/part-0.parquet']
    after DeleteObject: ['data/part-0.parquet']
3. LIST requests to enumerate a prefix (ListObjectsV2, 1,000 keys per page)
    s3://larkspur-raw/raw/pos/events/    128 objects, 1 LIST request
    s3://larkspur-raw/raw/             3,458 objects, 4 LIST requests

1b is optimistic concurrency, your services’ UPDATE … WHERE version = ? applied to a file. Every table format’s commit needs a guarantee of this shape from somewhere: the catalog, the storage, or a lock service (Part 7).

Step 7: the tests

tests/test_part03.py re-checks every claim above on the lab data (its fixture rewrites missing files).

uv run pytest -q tests/test_part03.py

......                                                                   [100%]
6 passed in 15.15s

The Landscape

Product names as of 28 October 2026. Cells that couldn’t be verified say so.

Tips & Gotchas

Gotcha — gzip isn’t splittable. A .csv.gz or .jsonl.gz must be decompressed from its first byte, in order, so an engine can’t hand pieces of one file to different workers. Larkspur’s till uploads get away with it because each day is its own moderate file; land many files, or use a block-compressed format.

Gotcha — small files cost requests. One file per store fetched more than twice the bytes (600 footers and dictionaries) in 15 times the GETs, and ran ten times slower than one file even at millisecond latency. Partition by what queries filter on, not by every key you have (Part 10).

Gotcha — CSV types are guesses. DuckDB samples 20,480 rows to guess them; Spark’s CSV reader defaults inferSchema to false and reads strings. SKUs like 00123 can come back as numbers in one engine and strings in another. Convert CSV to a typed format at the boundary, once.

Gotcha — new Parquet types need new readers. A reader that doesn’t know VARIANT, GEOMETRY or the ALP encoding (preview) may fail or return raw bytes. Check every engine that reads the table before a writer turns them on.

Tip — sort before you write, then check the footer. Sort by the column most queries filter on (often date, then a common second key), and confirm with parquet_metadata() that row-group min/max ranges are narrow. It’s the cheapest performance work in analytics.

Troubleshooting

Design Drill

The question: “We run a fleet of 400,000 smart energy meters and grid sensors. They send readings every few seconds, and we store 2 TB a day of gzipped JSON in object storage, partitioned by upload hour. Analysts and the grid-operations team say queries are slow and the query bill keeps growing. What do you change first?”

Clarify first

• What do the expensive queries filter on (time, site, device, metric), and how many of a reading’s fields do they read?
• How late can readings arrive (meters buffer during outages), and do people query by event time or upload time?
• Which engine runs the queries, and how is it billed: per byte scanned, per node, per slot?
• How long must raw readings be kept, and must they be replayable for billing disputes or regulators?

The shape of a strong answer

• Measure first: write one hour as Parquet (ZSTD) and compare size and the top queries’ bytes scanned. In this part’s retail test it was under half the size of gzipped JSON, and a 4-column query fetched 7.9 MiB instead of 100 MiB.
• First change: convert to Parquet on landing, hourly or in micro-batches; keep the gzipped JSON as immutable raw, in a colder storage class after the replay window.
• Partition by event date, not upload hour, so time filters prune; handle late readings by rewriting the affected days (Part 2’s pattern).
• Sort within each day by site, device, time, so common filters skip row groups; files of hundreds of MB with ~128 MB row groups, compacted.
• Type the columns: timestamps as timestamps, readings as numbers with units; BYTE_STREAM_SPLIT helps floats compress. Leave ALP (preview) until every reader supports it.
• Put the files under a table format (Iceberg) so conversion and compaction commit atomically (Part 7).
• Pre-aggregate dashboard rollups (15-minute averages per site) instead of rescanning raw readings on every refresh.

Trade-offs to say out loud

• Conversion costs compute and up to an hour of latency; operations may need a small real-time path for the last hour.
• Sorting by site helps site queries, not “every device with voltage above X”; sort for the most expensive frequent queries.
• Keeping raw JSON costs storage; it buys the ability to rebuild everything.

Common weak answers

• “Switch gzip to zstd”: still row-oriented JSON, still parsed in full on every query.
• “Partition by device_id”: hundreds of thousands of small files a day and request-bound queries.
• “More nodes”: faster scanning of bytes that never needed reading, at a higher bill.

Further Reading

1. Apache Parquet, “File Format” documentation: layout, encodings, page index, bloom filters. https://parquet.apache.org/docs/file-format/
2. Apache Avro specification, “Object Container Files” and “Schema Resolution”. https://avro.apache.org/docs/1.12.0/specification/
3. Apache Arrow, “Arrow Columnar Format”. https://arrow.apache.org/docs/format/Columnar.html
4. Amazon S3 User Guide, “Best practices design patterns: optimizing Amazon S3 performance”. https://docs.aws.amazon.com/AmazonS3/latest/userguide/optimizing-performance.html
5. Melnik et al., “Dremel: Interactive Analysis of Web-Scale Datasets”, VLDB 2010: where Parquet’s nested encoding comes from. https://research.google/pubs/pub36632/

Story Closing

Rohan’s change request for the Part 2 pipeline fitted in two lines: raw till uploads stay gzipped JSON, untouched; everything downstream is Parquet, sorted by business date. Ingrid approved it with one comment: “Check the footer of the first file it writes.”

Then Ingrid pushed over last quarter’s discount report, where a correct-looking join counted each basket’s discount once per line. “Fast files don’t help if one row means two things. Before we build anything else, decide what a row is.”

In Part 4, Rohan learns dimensional modelling, grain first, and finds the discount bug and the stock number that summed what can’t be summed.

This is Part 3 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 1: Foundations & the Lakehouse.