Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 36 ยท Storage Formats and Lakehouses

File Formats: Row-Based vs Columnar

Learn how CSV, JSON Lines, Avro, Parquet and ORC store data, why columnar files win for analytics, and how to pick a format for each stage of a pipeline.

Every pipeline eventually writes bytes to disk or object storage, and the format of those bytes decides how fast later queries run, how much storage costs, and how painful schema changes become. The same table of one million rows can be 90 MB as CSV and 12 MB as Parquet, and a query that reads two columns can touch 5% of the file instead of all of it.

This chapter is about storage: formats, partitioning, and the table formats that turn a pile of files into a reliable lakehouse. In this lesson you learn the two basic layouts, row-based and columnar, and when each one is the right tool. Every example runs in your browser, so you can measure the differences yourself.

The examples use Python with pandas and pyarrow, both of which run in the in-browser Python runner. Real data lakes hold billions of rows; the principles are the same at a small scale.

Row-based formats

A row-based format writes one record after another: all fields of row 1, then all fields of row 2, and so on. CSV, JSON Lines (one JSON object per line), Avro and the database "heap" pages behind most OLTP systems work this way.

Row layouts are good when you:

  • write or read whole records (an event arrives, you append it, a service reads one order);
  • stream data, because a record is complete as soon as it is written;
  • need human-readable files for debugging (CSV and JSON).

They are poor for analytics. To compute the average of one column out of 50, the reader still has to walk past all the other 49 columns of every row.

Format Schema Typed Splittable Typical use
CSV none (header row at best) no, everything is text yes (if no quoted newlines) exports, exchange with humans
JSON Lines implicit, per record partly yes logs, API dumps, events
Avro embedded in file, supports evolution yes yes Kafka messages, raw landing zone

Columnar formats

A columnar format stores all values of column A together, then all values of column B. Parquet and ORC are the two common ones. Because values in a column share a type and often repeat, they compress very well: a column of country codes with 200 distinct values becomes a small dictionary plus tiny integer references.

Row layout

Record by record id, name, price, then the next row
Fast to append One write per record
Slow to scan one column Must skip all others
1 / 2

Columnar files also store statistics (minimum and maximum of each column chunk). A query with WHERE price > 900 can skip whole chunks whose maximum is below 900 without reading them. This is called predicate pushdown or data skipping, and it is the reason formats like Parquet make warehouses and lakehouses fast.

The cost: writing is heavier (you buffer rows, then transpose and compress them), and fetching a single full record means touching every column. Columnar files are written in batches, never one row at a time.

Measure it yourself

Python Size of the same data in four formats

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

You should see Parquet at a fraction of the CSV size, even though the data is random. Real-world data, with more repetition and sorted keys, compresses even better.

Reading only the columns you need

Python Column pruning with Parquet

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Asking for two of 21 columns means the reader decompresses only those two column chunks. With CSV you would parse every byte of every line, then drop 19 columns.

Choosing a format

A practical rule of thumb for each pipeline zone:

flowchart LR
  A["Source systems"] --> B["Landing zone<br/>raw JSON / CSV / Avro"]
  B --> C["Clean zone<br/>Parquet, typed"]
  C --> D["Serving zone<br/>Parquet or warehouse tables"]
  • Landing (raw): keep data exactly as received. JSON Lines, CSV or Avro are fine; Avro is better when a schema registry exists because it carries types.
  • Clean/curated: convert to Parquet with explicit types, so later jobs are fast and cheap.
  • Serving: Parquet in a lakehouse table, or loaded into a warehouse.
  • Interchange with humans: CSV, but always with a documented delimiter, encoding (UTF-8) and date format.

Common mistakes: keeping analytics data as many small CSVs, letting a type guess change between loads (a column of zip codes read as integers loses leading zeros), and writing one row at a time into Parquet, which produces thousands of tiny files. The next two lessons fix the last problem with good partitioning and compaction.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a columnar format speed up a query that reads 2 of 50 columns?

    Each column is stored contiguously, so unrequested columns are never read from storage.
  2. 2. Which format is the best fit for appending single events one by one from a message queue?

    Row formats make a record complete as soon as it is written. Columnar files are meant for batches.
  3. 3. What is predicate pushdown (data skipping)?

    Statistics stored with each chunk let the engine ignore chunks whose value range cannot satisfy the WHERE clause.
  4. 4. Why can CSV cause silent bugs between loads?

    CSV is text without a schema; type inference can change from one file to the next.

Summary

  • Row formats (CSV, JSON Lines, Avro) are good for writing and streaming whole records.
  • Columnar formats (Parquet, ORC) are good for analytics: compression, column pruning, data skipping.
  • Keep raw data untouched in the landing zone, and convert to typed Parquet in the clean zone.
  • Write columnar files in batches; many tiny files hurt performance.

Next: Parquet in Practice