Python · medium
You need to process a 50GB CSV on a machine with 16GB of RAM. How?
Asked in Data Engineer interviews, in the Python round.
Short answer
Stream it instead of loading it: read_csv with chunksize, or hand it to an engine that spills to disk and reads only the columns you touch (DuckDB, Polars lazy, Spark). If it is a recurring job, convert once to Parquet, columnar plus compression usually cuts both IO and memory by an order of magnitude. The general move is to make the working set fit in memory, not the file.
How to answer it
Definition
Do not load it. The file is larger than memory, so the answer is to stream it, or to hand it to an engine that reads only what it needs and spills to disk.
The options
- Chunked reads in pandas. Process a piece at a time and accumulate a small result.
- An out-of-core engine: DuckDB, Polars in lazy mode, or Dask. They handle spilling and parallelism for you.
- Push the work upstream. If the file came from a warehouse, aggregating there and exporting the result is usually the real answer.
- Convert to Parquet once. Columnar and compressed, so later passes read only the columns they need.
Example
import pandas as pd
totals = {}
for chunk in pd.read_csv(
"50gb.csv",
chunksize=1_000_000,
usecols=["category", "amount"], # two columns, not eighty
dtype={"category": "category", "amount": "float32"},
):
for k, v in chunk.groupby("category")["amount"].sum().items():
totals[k] = totals.get(k, 0) + vOr let an engine do it:
import duckdb
duckdb.sql(
"SELECT category, SUM(amount) FROM read_csv_auto('50gb.csv') GROUP BY category"
).df()Why the dtypes matter
usecols and dtype are not micro-optimizations here. Reading two columns instead of eighty is a 40x cut before anything else happens, and float32 against the default float64 halves what is left. A 50GB file often becomes a few GB of working data once you stop reading what you do not need.
Key points
- Streaming works when the operation is incremental: sums, counts, filters, groupbys with bounded cardinality.
- It breaks down for a global sort or an exact median, which need the whole dataset. That is where an out-of-core engine earns its place.
- Convert to Parquet once if the file will be read repeatedly. The conversion pays for itself on the second pass.
- A groupby with millions of distinct keys can exhaust memory even chunked, because the accumulator grows.
- Ask where the file came from. Aggregating in the warehouse beats any local trick.
FAQ
- What chunk size? Large enough to amortise overhead, small enough to fit comfortably. A million rows is a reasonable start, then measure.
- Why DuckDB rather than Dask? For a single machine and SQL-shaped work, DuckDB is simpler and usually faster. Dask suits a cluster or existing pandas code.
- Can I sort it? Not in a streaming pass. Use an engine with an external sort, or sort within partitions if that is enough.
The 30-second answer
I would not load it. The first question is what the operation actually is: for a sum, a count or a groupby I can stream the file in chunks with read_csv and a chunksize, keeping only a small accumulator in memory.
Before that I would cut what is read at all, with usecols and explicit dtypes, because reading two of eighty columns is a far bigger win than any tuning, and float32 instead of float64 halves the rest.
In practice I would reach for DuckDB or Polars in lazy mode and let the engine handle spilling and parallelism, and convert the file to Parquet once if it is going to be read more than a couple of times. I would also ask where the file came from, because if it was exported from a warehouse the right fix is to aggregate it there instead.
In the interview
- What they are checking: whether you know that column pruning and dtypes are usually enough, before reaching for a cluster.
- Common mistake: "use Spark", offered before asking what the job actually needs to compute.