📖 Chapter 03 — Data Storage and Format
The file format is the foundation. CSV is usually wrong. Parquet is usually right.
Learning Objectives
- Choose the right file format for analytical data
- Use Parquet, Arrow, and partitioned datasets effectively
- Convert CSV (and other slow formats) to fast ones
- Apply partitioning for query performance
Introduction
Most people start with CSV. CSV is the lingua franca of data: every tool can read it, every human can open it, every export offers it. The problem: CSV is slow, large, and doesn't preserve types. For analytical work, there are much better choices.
This chapter covers the file formats that make analysis fast: Parquet for columnar storage, Arrow for in-memory, partitioning for query performance. By the end, you'll know when CSV is the right answer (rarely) and when to use something else (almost always).
The problem with CSV
CSV has three problems for analytical work:
- Size. A 10 GB CSV is often 1-2 GB as Parquet. The compression comes from the columnar format (similar values in the same column compress well) and from storing types explicitly (no need to store "1.0" when you can store 1.0 as a float).
- Speed. Reading a 10 GB CSV takes minutes. Reading the same data as Parquet takes seconds. The difference: Parquet is columnar (only the columns you need are read), compressed, and indexed.
- Types. CSV doesn't have types. "2024-01-15" is a string. "42" is a string. "true" is a string. When you read a CSV, you have to infer types, which is slow and error-prone. Parquet stores types explicitly.
For a dataset you'll read once, CSV is fine. For a dataset you'll read many times, the time savings from Parquet are massive.
Parquet: the columnar format
Parquet is an open-source columnar storage format. The design:
- Data is stored by column, not by row. To read one column, you only read that column, not the whole row.
- Each column has a type (int, float, string, timestamp, etc.). Types are preserved across reads.
- Compression is automatic. The compression uses the column's data type for efficiency (run-length encoding for low-cardinality columns, dictionary encoding for repeated values, etc.).
- Statistics are stored per column. Tools can skip data that doesn't match a query.
For most analytical datasets, Parquet is the right answer. The conversion from CSV to Parquet is one line of code:
import pandas as pd
df = pd.read_csv("data.csv")
df.to_parquet("data.parquet")Read the CSV once, save as Parquet, and from then on, use the Parquet file. The first read is slow; every subsequent read is fast.
Arrow: the in-memory format
Apache Arrow is an in-memory columnar format. It's the standard for data exchange between tools (Pandas, Polars, DuckDB, Spark, etc.) without copying or serializing.
For most analytical work, you don't interact with Arrow directly. The tools you use (Polars, DuckDB, PyArrow) handle the conversion. But it's good to know: when you load a Parquet file into Polars or DuckDB, it's in Arrow format in memory. When you read a Parquet file with Pandas, it's also in Arrow (since recent Pandas versions).
The benefit: zero-copy between tools. A Polars DataFrame and a PyArrow Table share the same memory; no conversion needed.
Partitioned datasets
For large datasets, partitioning is the right answer. A partitioned dataset is a directory of Parquet files, organized by one or more columns:
The partitioning by year and month means: a query for "all events in March 2024" only needs to read the files under year=2024/month=03/, not the whole dataset. This is the same idea as an index in a database.
For tools like DuckDB and Polars, partitioning is automatic: a query with a WHERE year = 2024 clause reads only the relevant partition.
Choosing partition columns
The right partition columns:
- Frequently filtered (a column you use in WHERE clauses)
- Low to medium cardinality (not too many distinct values)
- Monotonically increasing or decreasing (dates, IDs, status codes)
Common choices: year/month/day for time-series data, region or country for geographic data, category for categorical data. Avoid high-cardinality columns (user IDs, product IDs) — too many partitions make the directory listing slow.
The "small files" problem
When you write a partitioned dataset with many small files, you hit the small files problem. Each file has overhead (metadata, headers, etc.). 1000 files of 1 MB each is slower to read than 1 file of 1 GB.
The solution: target file sizes of 100-500 MB. If your writes produce 1 MB files, batch them. If your reads scan 1000 small files, rewrite the dataset with larger files.
For most home data science, the small files problem is a non-issue (datasets are small enough that the overhead doesn't matter). For larger datasets, it's the first thing to look at when queries are slow.
JSON, JSONL, and other formats
For data that's nested or hierarchical (API responses, logs, documents), JSON is the standard. The line-delimited variant (JSONL, one JSON object per line) is the right format for streaming and batch processing.
For analysis, JSON has the same problems as CSV: no types, slow, large. The right approach: load JSON into Arrow (with PyArrow or DuckDB), save as Parquet for analysis.
Database files: SQLite and DuckDB
For some workflows, a single-file database is the right format. SQLite is the standard for embedded relational data. DuckDB is the standard for embedded analytical data. Both are single files that you can put on the NAS.
SQLite is great for transactional data: a contact list, a journal, a collection of small records. Use it when the data is structured and you want to query it with SQL.
DuckDB is great for analytical data: a dataset that you'd load into Pandas, but want to query with SQL without loading everything into memory. Use it when the dataset is bigger than RAM but smaller than disk.
For both, the file is in data/processed/. The analysis tools connect to the file, query, return results.
The "I just want to load a CSV" answer
Sometimes CSV is fine. The cases:
- You're loading a one-off file from someone else. Convert to Parquet, save, use the Parquet from then on.
- You're exporting data for someone who doesn't have Parquet tools. CSV is the lingua franca; use it for exports.
- You're working with a small file (under 10 MB) and don't care about performance.
The pattern: load CSV once, save as Parquet, work with Parquet from then on. The first read is slow; every subsequent read is fast.
Tools: the right ones for the right job
- Pandas: the standard. Good for small-to-medium datasets, simple API, slow for large data.
- Polars: the modern alternative. Fast, lazy evaluation, multi-threaded, Arrow-native. The right default for new projects in 2026.
- DuckDB: the in-process SQL engine. Great for queries on data that doesn't fit in memory. Combines with Polars for fast analytical workflows.
- PyArrow: the Arrow library. For Parquet I/O, schema management, and the underlying data structures.
For most home data science, Polars + DuckDB is the right stack. Pandas is fine for compatibility with older code; PyArrow is the foundation; both work well with each other.
The "schema matters" point
For Parquet and Arrow, the schema is part of the data. A Parquet file knows: column names, column types, nullability. When you read it, the types are preserved. When you write a DataFrame to Parquet, the types are stored.
The benefit: you don't have to re-infer types on every read. The schema is checked when the data is written; it's trusted when the data is read.
The discipline: write Parquet files with explicit schemas. Don't rely on type inference. The schema is documentation; it tells future-you what the data is.
Engineering Note
The file format is the foundation. A slow format makes everything slow. A small format makes everything small. The 5 minutes of converting a CSV to Parquet saves hours of waiting for the data to load. Do it once, do it right, benefit forever.
Summary
Parquet for columnar storage, Arrow for in-memory, partitioning for query performance, DuckDB for SQL on data that doesn't fit in RAM. CSV is for one-off loads and exports; convert to Parquet for analysis. Polars is the modern default for new projects. Schemas matter: write explicit schemas, preserve types. The file format is the foundation of fast analysis.
Checklist
- ⬜ For each new dataset, convert CSV to Parquet (or write directly as Parquet)
- ⬜ For datasets over 10 GB, partition by year/month or another natural column
- ⬜ For datasets bigger than RAM, use DuckDB for SQL queries
- ⬜ Use Polars for new analysis code; Pandas is fine for compatibility
- ⬜ Document the schema for each dataset in a README or a docs/ file
Looking Ahead
Chapter 04 is pipelines and schedules. Cron, Airflow, Prefect. The chapter that turns "I run this analysis manually once a week" into "this analysis runs every Monday at 9 AM, the result is in my email, and I don't have to think about it."