Data loading (CSV, JSON, parquet): Visual walkthrough

Before we start, one word we will use constantly:

We will build a tiny table and follow it through every format. Our table:

name	age	score
Alice	25	92.5
Bob	30	87.3

Step 1 — What a table really is before it becomes a file

WHAT. A table lives in our head as a 2‑D grid: rows going down, columns going across. But a file is 1‑D — one long tape. So before anything, we must choose an order in which to unroll the grid onto the tape.

WHY. There are exactly two natural ways to flatten a grid into a line, and this single choice is the entire story of the page. Everything else — speed, size, "columnar win" — is a consequence of this choice.

PICTURE. The grid on the left; the two ways to unroll it (row-by-row vs column-by-column) as two different tapes on the right.

Figure — Data loading (CSV, JSON, parquet)

WHAT. We lay our table out as CSV. Each row becomes one line of text; each value is written as characters (the number 25 becomes the two letters '2''5'), separated by commas.

WHY text and row-major? Because CSV's job is human interchange — you want to open it anywhere, in any editor, and read it. Row-major matches how you'd read a spreadsheet aloud ("Alice, 25, 92 point 5..."). The cost of that friendliness appears in the next step.

PICTURE. The CSV tape with each character shown as a box, ages highlighted in pink, and the disk head forced to walk through the names to reach each age.

Let us count the reading cost of "give me all the ages". Call:

C_{csv} = number of rows N \times bytes in one full row R

$N$ — how many rows exist (a million people).
$R$ — the size of one entire row in bytes (name plus age plus score plus commas).

The head must pass over every byte of every row to find each comma-separated age, because nothing on the tape says "ages start here". The age is 2 bytes but you paid to walk the whole row of, say, 20 bytes to reach it.

Step 3 — JSON on the tape: same walk, now carrying its own dictionary

WHAT. JSON is also row-major, but each row re-writes the column names ("age":) next to every single value.

WHY. JSON's superpower is being self-describing and nestable — a value can be {"address": {"city": "NYC"}}. To allow that, each value must be labelled. The price: the labels repeat on every row.

PICTURE. The JSON tape drawn under the CSV tape at the same scale, so you see the extra "age":, "name":, braces and quotes swelling the tape for the same data.

The size of one JSON row is:

R_{json} = R_{values} + repeated every row R_{keys} + R_{punctuation}

$R_{values}$ — the actual data (25, Alice …), the only part you care about.
$R_{keys}$ — the words name, age, score, paid again on every row.
$R_{punctuation}$ — { } " " : scaffolding.

Because $R_{keys}$ and $R_{punctuation}$ are pure overhead repeated $N$ times, JSON is the fattest tape — the parent's rule-of-thumb of $2$ – $3 \times$ CSV. Reading a single column is still the full walk $C \approx N \times R_{json}$ , now over a longer tape. Worst of both worlds for analytics; best for flexible, labelled API data.

Step 4 — The pivot: rewrite the tape column-by-column

WHAT. Take the exact same three values-per-row, but reorder the tape so all names sit together, then all ages, then all scores. This is Parquet's core move — column-major from Step 1.

WHY. If the ages are physically contiguous on the tape, and a small note at the front records where the age block starts and how long it is, the head can jump straight to it and read only those bytes. We are trading away human-readability to buy seekability.

PICTURE. The tape sliced into three coloured blocks (names / ages / scores) with a "footer" box of metadata at the end holding each block's start position and length; an arrow shows the head jumping directly to the age block.

Now the true cost of "give me all the ages" has two parts, because you can only jump after reading the footer:

C_{parquet} = footer bytes S_{foot} + cost to jump t_{seek} + age block bytes S_{age}

$S_{foot}$ — the footer/metadata, read first so we learn where the age block lives. Small and fixed, but not zero.
$t_{seek}$ — the cost of moving the head to the age block's start position (one disk seek).
$S_{age}$ — the bytes of just the age block. The names and scores are never touched.

For a large table $S_{foot} + t_{seek}$ is tiny beside $S_{age}$ , so $C_{parquet} \approx S_{age}$ — but for a tiny file the footer can dominate, which is exactly the edge case in Step 8.

Step 5 — Quantify the win: the ratio that gives ~40×

WHAT. Put the row cost from Step 2 next to the column cost from Step 4 and take their ratio — this is the parent note's speedup number.

WHY. A speedup claim is meaningless without the formula behind it. Here is where "columnar" turns into an actual multiplier you can predict before running anything.

PICTURE. Two horizontal bars — a long row-format bar (you read all of it) vs a short columnar bar (you read one slice) — with the savings region shaded pale yellow.

First, the one symbol we have not yet named:

The fraction of work you save by reading one column, ignoring the tiny footer for now:

saving = \frac{N \cdot R - S _{age}}{N \cdot R} = 1 - \frac{S _{age}}{N \cdot R}

$N \cdot R$ — total bytes a row format forces you to read (Step 2).
$S_{age}$ — bytes columnar actually reads (Step 4).
The ratio $\frac{S _{age}}{N \cdot R}$ shrinks as the table gets wider (bigger $K$ ), because one column is a smaller slice of the whole.

If every column is roughly the same size, one column is $\frac{1}{K}$ of the data, so $S_{age} \approx \frac{N \cdot R}{K}$ , giving:

speedup = \frac{N \cdot R}{S _{age}} \approx K

A 100‑column table ( $K = 100$ ) → about 100× fewer bytes read for one column. Add real-world decompression (Step 6) and seek/footer overhead (Step 4) and this settles near the parent's observed ~40×. The width of your table is your speedup.

Step 6 — Why the columnar blocks also compress harder (and what it costs)

WHAT. Within one column, every value has the same type and often repeats (25, 25, 30, 25, 25…). A block of same-kind data squeezes far smaller than a row mixing text, ints and floats.

WHY. Compression works by spotting repetition and predictability. A row Alice,25,92.5 mixes three unpredictable things. A column 25,25,30,25,25 is a predictable stream — perfect for the tricks below. But shrinking on disk is not free: the CPU must un-shrink it on the way back in, so we buy fewer disk-bytes with extra CPU-cycles.

PICTURE. One age column collapsing under Parquet's hybrid encoding, each stage labelled, with a red "CPU cost to decompress" arrow reminding you the squeeze is undone at read time.

Step 7 — Predicate pushdown: skip blocks you don't even want

WHAT. Ask for age > 25. Parquet groups rows into chunks ("row groups") and the footer records the min and max age of each chunk. If a chunk cannot possibly contain a matching row, the whole chunk is skipped unread.

WHY. Filtering in CSV means: read everything, throw most away. Parquet can decide from the footer alone that a block is irrelevant — it never reads those bytes at all. This is stronger than the column skip of Step 4: now we skip rows too.

PICTURE. Three stacked row-group blocks, each tagged with (min, max) age; the query age > 25 crosses out the block whose max is 24 (no value can exceed 25) before any data is read.

To be precise about the comparison and avoid an off-by-one, we keep the filter as written and turn it into a keep-rule on the group's max:

bytes read = groups g \sum {S_{g} 0 if the filter (age > v) can still hold, i.e. max_{g} > v if max_{g} \leq v (no row in g can exceed v)

$v$ — the literal filter value written in your query, here $v = 25$ from age > 25. It is not shifted to 26.
$max_{g}$ — the largest age in group $g$ , straight from the footer.
The rule is $max_{g} > v$ (strictly, matching the strict > in the query). For age >= 25 the rule would instead be $max_{g} \geq v$ . Match the comparison operator exactly — that is where off-by-one bugs hide.

A group with $max_{g} = 24$ and $v = 25$ : since $24 \leq 25$ , no row can satisfy age > 25, so it contributes $0$ — skipped unread.

Step 8 — Edge & degenerate cases (never hit a surprise)

WHAT. Four corners where "columnar always wins" bends, plus the classic type trap. The figure lays all five side by side.

WHY. A tool you only know in its best case will burn you in its worst. Each panel below has its own reason it fails, so you can recognise the situation before you pay for it.

PICTURE. Left column: the four "columnar loses" cases stacked, each with its failing quantity called out. Right: the zip-code trap shown as "01234" losing its leading zero the moment CSV guesses it is a number.

Walking the figure top to bottom:

Common mistake When columnar does

not win — each with its own WHY

You need every column of every row (e.g. exporting the full table). Why it fails: $S_{age}$ in Step 4 grows into the whole file, so the ratio in Step 5 becomes $1$ — no speedup. Row formats are just as good here.
One tiny row ( $N = 1$ , one column). Why it fails: the footer $S_{foot}$ and compression headers from Steps 4 and 6 can be bigger than the data itself, so overhead dominates. CSV wins on small files.
Constant, append-only writes. Why it fails: Parquet must build blocks, compute min/max stats and compress before it can write (Steps 4, 6, 7) — all slow. This is the Slow write cell in the parent's table; use CSV or JSONL for streaming logs.
Deeply nested data (user.address.city). Why it fails: the flat column blocks of Step 4 assume a rectangular table; JSON stores nesting natively while Parquet only partially supports it and often needs flattening first — see 1.4.08-Data-cleaning-and-preprocessing.

The right-hand panel of the figure shows the type trap — format-independent but it bites hardest in CSV:

The one-picture summary

The figure below compresses the whole walkthrough onto a single board: the top tape is row-major (CSV/JSON) — the head's arrow runs across every box, cost $N \cdot R$ . The bottom tape is column-major (Parquet) — three contiguous colour blocks plus a yellow footer; the pink arrow dives straight into the AGES block, and the caption reminds you that footer + seek is the small price for that jump. The one-line takeaway under it — width of table ≈ speedup — is the payoff of Steps 4–7.

Recall Feynman retelling — say it back in plain words

Imagine a library where every book is one person's file: to collect everyone's height, you'd pull every book off every shelf, flip to the height page, and re-shelve — that's CSV, row by row. Now imagine a second library organised so all the heights sit on one shelf, all the names on another, with a directory card at the door saying "heights: aisle 3, and by the way the tallest here is 190". You want heights over 200? The card already says the tallest is 190 — you don't even enter. That directory card is Parquet's footer; the by-column shelving is the columnar layout; and because every book on the height shelf is just numbers, you can shrink-wrap them tight (but you spend a moment un-wrapping each on the way out). The only times the first library wins: when you genuinely want whole books (all columns), when there's only one book (tiny file, the directory card weighs more than the book), or when new books arrive every second (append-heavy writes).

Where does this walkthrough sit in the vault? Files load into arrays and frames from 1.4.05-NumPy-arrays-and-operations and 1.4.06-Pandas-DataFrames-and-operations; you then clean them (1.4.08-Data-cleaning-and-preprocessing, 1.5.01-Handling-missing-data), engineer features (2.3.05-Feature-engineeringtechniques), and feed them to models (3.1.02-TensorFlow-and-Keras-basics). Parquet is the format of choice for that whole ML pipeline precisely because of Steps 4–7.

Recall Quick self-test

Why can't CSV jump straight to the age column? ::: It is row-major text with no index/footer — nothing on the tape records where ages start, so the parser must walk every byte to find each comma. In Step 5, what makes the speedup bigger? ::: A larger $K$ (more columns) — one column is a smaller slice of the whole, so $S_{age} / (N \cdot R)$ shrinks and speedup $\approx K$ . In age > 25, what value $v$ does Parquet compare each group's max against? ::: The literal filter value $v = 25$ (not 26); it keeps a group only if $max_{g} > 25$ , matching the strict >. Why isn't a Parquet read truly "free" apart from the age block? ::: You must first read the footer ( $S_{foot}$ ) and pay a seek ( $t_{seek}$ ), and every compressed block costs CPU to decompress. Name one case where columnar loses. ::: Reading all columns, tiny files, or append-heavy writes (Parquet writes are slow).

Visual walkthrough — Data loading (CSV, JSON, parquet)