Rows vs Columns on Disk, and Column Projection

The same table, two ways on disk

A CSV or a JSON file is row-oriented: it writes all of row 1, then all of row 2, and so on. That is perfect when you want a whole row at a time, which is what an application database does. Analytics is the opposite. You scan millions of rows but touch only a few columns ("total revenue by country last month" reads 2 columns out of 40), so the row-oriented order forces you to read the whole table to answer a two-column question.

Parquet is column-oriented: it writes all of column A's values together, then all of column B's, and so on. Apache Parquet is the default file format of the data lake, and its columnar layout is the entire reason.

Table

Columnar stores each column together, so an analytic query reads only the columns it needs and compresses each one hard.

aspect	row store (CSV/JSON)	column store (Parquet)
on-disk order	all of row 1, then all of row 2	all of column A, then all of column B
a 2-of-8-column SELECT reads	every column of every row	only those 2 column chunks
compression	mixed types per row, compresses poorly	like values together, compresses far better
good for	writing and fetching whole rows	scanning a few columns over many rows

What columnar buys you (part 1: column projection)

The first and biggest win is column projection (also called column pruning). Because each column is stored as its own contiguous chunk, a query for two columns reads only those two chunks and physically skips the rest of the bytes. On a wide table this is enormous: selecting 2 columns out of 8 might read a few percent of the file, not all of it. How small depends on how wide the table is and how large the columns you skip are, so the win is biggest on very wide tables or when the unselected columns dominate the bytes (as the fat url column does in the exercise).

This is why SELECT * is a code smell on a lake. On a row store it costs the same as naming your columns; on a columnar store every extra column you select is more bytes read, and more money on an engine that charges by bytes scanned. Name the columns you need.

Parquet also stores the schema and types inside the file (in a footer), so a reader never has to guess whether a column is an integer or a string the way it must with CSV. You get typed, self-describing files for free.

Measuring it with SQL

A Parquet file records, per column, how many bytes that column occupies. Reading those column statistics is how you predict what a query will scan and prove that projection pays off. The exercises here query a parquet_column_stats table (one row per column with its compressed size) and compute exactly how many bytes a two-column query reads versus the whole row.

Common mistake: reaching for SELECT star on a lake. On a columnar store every extra column you select is more bytes read and more money, so name only the columns you actually need.

Interview nuance: "why is Parquet faster than CSV for analytics" has two halves, and column projection is the first: a columnar file lets a query read only the columns it selects instead of every byte of every row. (The second half, compression, is the next lesson.) Saying both, and adding "and predicate pushdown skips row groups too," is a complete junior answer.

On a real platform this differs. A real Parquet reader gets these per-column sizes from the file footer, and an engine like Athena reports "data scanned" per query. The parquet_column_stats table here is a small stand-in with one row per column, so the projection math you do is the math the engine does.