File and table formats

The whole difference is layout on disk. Analytics rarely wants whole rows - it wants "sum this one column across a billion records". Layout decides how much work that takes:

• Row format (CSV/JSON): values are interleaved record by record. To sum one column, the engine must read every byte of every row and skip the rest. All that I/O, mostly wasted.
• Columnar (Parquet): each column is stored together. To sum one column, the engine reads only that column's block and ignores the rest entirely. Less I/O, and similar values sit together so they compress hard.
• Types are baked in: Parquet records that a column is a DATE or an INTEGER. CSV is all text, so every read re-parses and re-guesses types - slow and error-prone.

Parquet is not always right. CSV and JSON keep earning their place at the edges of a pipeline. CSV is the universal interchange format - every tool, every partner, every spreadsheet reads it, so it is often what data arrives as and what a non-technical stakeholder wants exported. JSON is the right shape for nested, semi-structured, still-changing data from APIs. The rule: readable formats for the human-facing and fluid edges, Parquet for the machine-facing analytical core.

Write orders to CSV, JSON, and Parquet, then read one column back from each and check its stored type. Press ▶ Run (first run caches the ~8 MB engine).

-- land the same data three ways
COPY (SELECT * FROM orders) TO 'f/orders.csv'     (FORMAT CSV, HEADER);
COPY (SELECT * FROM orders) TO 'f/orders.json'    (FORMAT JSON);
COPY (SELECT * FROM orders) TO 'f/orders.parquet' (FORMAT PARQUET);

-- read one value back from each and see what type it is
(SELECT 'parquet' AS fmt, typeof(order_date) AS date_type
 FROM 'f/orders.parquet' LIMIT 1)
UNION ALL
(SELECT 'csv', typeof(order_date)
 FROM read_csv_auto('f/orders.csv') LIMIT 1);

Because Parquet stores columns separately, reading two of them touches only those two columns' data - the engine never loads the rest. Land the full table, then pull just two columns back.

COPY (SELECT * FROM orders) TO 'f/orders.parquet' (FORMAT PARQUET);

-- only these two columns' data is read from the file
SELECT order_id, order_date
FROM 'f/orders.parquet'
LIMIT 8;

A table format keeps a manifest of which Parquet files make up the table right now, plus a log of changes. That metadata unlocks three things plain files cannot do:

• Transactions (ACID): a write either fully lands or not at all. No more readers catching a half-written table mid-update.
• Schema evolution: add, rename, or drop a column without rewriting every file. The metadata tracks the change.
• Time travel: query the table as it was at an earlier version or timestamp - for audits, debugging, and reproducibility.

Write order_items to Parquet, read it back, and verify the row count matches the source - a clean round trip loses nothing.

COPY (SELECT * FROM order_items) TO 'rt/items.parquet' (FORMAT PARQUET);

SELECT (SELECT count(*) FROM 'rt/items.parquet') AS file_rows,
       (SELECT count(*) FROM order_items)        AS source_rows;

COPY only completed orders to Parquet, read them back, and aggregate - count orders per channel straight from the landed file.

COPY (SELECT * FROM orders WHERE status = 'completed')
TO 'rt/completed.parquet' (FORMAT PARQUET);

SELECT channel, count(*) AS completed_orders
FROM 'rt/completed.parquet'
GROUP BY channel
ORDER BY completed_orders DESC;

A thought exercise, no SQL. Parquet wins for the analytical core - so when would you deliberately keep data as JSON?

• Nested, semi-structured source data: an API returns orders with a variable list of line items and optional metadata. JSON stores that nesting natively; forcing it into flat columns too early loses shape.
• Schema still fluid: when the source keeps adding and dropping fields, JSON absorbs the change without a rewrite. You land raw JSON, then flatten to Parquet once the schema settles.
• The pattern: JSON at the raw landing edge for messy, changing sources; Parquet for the cleaned, stable, analytics-facing tables. Right tool, right stage.