Data formats: CSV, JSON and Parquet

Before writing code, it helps to understand the formats data travels in, because the choice affects speed, cost and how many bugs you will chase.

The three you meet most

CSV is plain text with one row per line. It is universal and human readable, but it has no types. Is 01/02/2026 the first of February or the second of January? Is 007 a number or a code? Every reader guesses, and different tools guess differently. Commas inside values and line breaks inside quoted fields cause endless trouble.

JSON carries structure: nested objects, lists, strings, numbers, booleans and null. APIs mostly speak JSON. It is great for exchanging records and bad for analysis at scale, because every query has to parse text and the same keys are repeated in every record.

Parquet is a binary, columnar format designed for analytics. Values for each column are stored together, compressed and typed, with statistics such as minimum and maximum per chunk.

• Reading two columns out of fifty only touches those two columns.
• Similar values next to each other compress very well, often several times smaller than CSV.
• Types are stored with the data, so a date stays a date.
• Engines can skip chunks entirely when statistics show they cannot match a filter.

A quick comparison in code

import duckdb

duckdb.sql("""
    COPY (SELECT * FROM read_csv_auto('observations.csv'))
    TO 'observations.parquet' (FORMAT parquet)
""")
print(duckdb.sql("SELECT count(*), avg(temp_c) FROM 'observations.parquet'"))

DuckDB reads the CSV, infers types, writes Parquet and then queries the Parquet file directly. On a file of a few million rows you will notice the Parquet query is much faster and the file much smaller.

Our rule for the course

Keep raw API responses as JSON exactly as received, because that is your evidence of what the source said. Convert to Parquet as soon as you clean the data. Use CSV only when a person or a legacy system needs it.