When Pandas Breaks Down — and What to Reach For

A senior interview question: “you have 10 GB of data, can Pandas handle it?” Knowing where Pandas fails and what to swap in.

Pandas’ constraints

Pandas is in-memory and single-threaded:

• Whole DataFrame in RAM. 100 GB dataset on a 32 GB machine → OOM.
• One core. Multi-core machines waste 7+ cores on a pure-Pandas pipeline.
• Eager evaluation. Every operation produces an intermediate; chains rack up memory.
• Object-dtype strings are slow (Python-level per-cell).

These tradeoffs are fine for interactive analysis on a few GB. They break for production data pipelines.

The decision tree

How big is the data?

≤ 1 GB in memory
  → Pandas is fine.

1-10 GB, single machine
  → Polars (faster, lazy, streaming, similar ergonomics).
  → DuckDB (SQL on Parquet, in-process).
  → Pandas + chunking + Parquet for ingest.

10-100 GB, single machine
  → Polars streaming engine (out-of-core).
  → DuckDB (handles disk spill).
  → Dask (parallel Pandas-like, multi-process).

> 100 GB, distributed
  → Spark / PySpark.
  → Snowflake / BigQuery / Redshift if it's analytic SQL.
  → Apache Arrow + custom pipelines for streaming.

Polars — the modern Pandas alternative

import polars as pl

df = pl.read_parquet("orders.parquet")
result = (
    df.lazy()
    .filter(pl.col("status") == "completed")
    .group_by("user_id")
    .agg(pl.col("amount").sum().alias("total"))
    .sort("total", descending=True)
    .head(100)
    .collect()
)

Why faster:

• Rust + Arrow backing. No Python per-cell overhead. Columnar memory.
• Multi-threaded. Uses all CPUs by default.
• Lazy evaluation with query optimization (predicate pushdown, projection pushdown).
• Streaming engine — process data larger than RAM.

For most workloads, Polars is 5-30× faster than Pandas on the same machine.

Migration cost: API is similar-but-different. Many Pandas idioms translate cleanly; some (apply, iterrows) don’t have direct equivalents (and are usually anti-patterns anyway).

See ../03_polars/ for details.

DuckDB — SQL in-process

import duckdb

con = duckdb.connect()
con.execute("SELECT user_id, SUM(amount) FROM read_parquet('orders.parquet') GROUP BY user_id").df()

DuckDB is an embedded analytics DB. No server. Reads Parquet/CSV/JSON directly. Pandas-like return values. SQL.

Strengths:

• Read Parquet directly — no Python iteration.
• Excellent on out-of-core data (auto-spills to disk).
• Same engine as MotherDuck (managed DuckDB) — local dev → cloud.
• Integrates with Pandas — result.df() returns a DataFrame.

When you can express it as SQL and the data lives in files / object storage, DuckDB beats Pandas by orders of magnitude.

Dask — parallel Pandas

import dask.dataframe as dd

ddf = dd.read_parquet("orders/*.parquet")
result = ddf.groupby("user_id").amount.sum().compute()

Dask DataFrames are partitioned Pandas DataFrames. API mirrors Pandas; under the hood, partitions run in parallel across cores or a cluster.

Use case: you have a Pandas codebase and need to scale to more memory or multi-machine without a full rewrite. Dask is the lower-friction path.

Downsides:

• Some Pandas ops don’t translate cleanly (joins on non-partitioned keys are slow).
• Performance ceiling is below Polars / DuckDB for single-machine work.
• Operationally heavier if you go distributed.

PySpark — distributed at scale

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()

df = spark.read.parquet("s3://bucket/orders/")
result = (
    df.filter(df.status == "completed")
      .groupBy("user_id")
      .sum("amount")
      .orderBy("sum(amount)", ascending=False)
      .limit(100)
      .toPandas()
)

For terabyte-scale data across many machines, Spark is the industry standard. Steep operational complexity (cluster, driver/executor model, shuffle); justified only when single-machine tools can’t keep up.

See ../01_spark_databricks/ for details.

Comparison table

When Pandas wins

Despite the above, Pandas is still right for:

• Notebook exploration on small datasets where the ecosystem (matplotlib, seaborn, statsmodels) matters.
• Interop with other Python libraries that consume DataFrames (most ML libraries).
• One-off scripts where rewrite cost exceeds runtime gain.
• Teams without Polars / DuckDB experience where productivity > performance.

Don’t migrate working Pandas code that’s fast enough. Migrate code that’s slow / OOMing / hitting wall-clock budgets.

Chunking — the Pandas escape hatch

When you must use Pandas on big data:

chunks = pd.read_csv("big.csv", chunksize=100_000)

results = []
for chunk in chunks:
    filtered = chunk[chunk["status"] == "completed"]
    grouped = filtered.groupby("user_id")["amount"].sum()
    results.append(grouped)

total = pd.concat(results).groupby(level=0).sum()

Works for: filter+aggregate, transform+write. Doesn’t work easily for: joins across chunks, sorts.

For Parquet, pyarrow.dataset does the same idea but more efficiently:

import pyarrow.dataset as ds
dataset = ds.dataset("orders/", format="parquet")
for batch in dataset.to_batches(columns=["user_id", "amount", "status"], filter=ds.field("status") == "completed"):
    process(batch.to_pandas())

Predicate + projection pushdown at scan time = much less data to process.

Memory-optimization checklist (before reaching for Polars)

1. df.memory_usage(deep=True) — find the heaviest columns.
2. Convert object-strings to category or nullable string.
3. Downcast ints: int64 → int32 → int16 where the range allows.
4. Drop unnecessary columns at read time: pd.read_csv(..., usecols=[...]).
5. Read Parquet instead of CSV.
6. Filter at read time: pd.read_parquet(..., filters=[("status", "=", "completed")]).
7. Use chunking + aggregate-in-stream for huge files.

After all of this, if you’re still hitting limits, it’s time to switch engines.

What about Modin / cuDF / RAPIDS?

• Modin — drop-in Pandas replacement with Ray/Dask backend. Promising; ecosystem less mature than Polars.
• cuDF (RAPIDS) — GPU-accelerated DataFrame. Massive speedup if you have NVIDIA hardware and the workload is GPU-friendly.
• Pandas API on Spark — Spark’s Pandas-compatible API; cheats by running on Spark.

These exist; they’re niche. Polars + DuckDB cover most cases without exotic hardware.

Interview angle

• “You have 50 GB of Parquet data and need to compute per-user totals. Pandas?” — No; OOM. Options: Polars streaming, DuckDB SQL on the Parquet files, or PySpark if it’s distributed. DuckDB is often the lightest-weight win for SQL-shaped analytics.
• “Pandas vs Polars — when each?” — Pandas for small interactive work where ecosystem matters. Polars for any production pipeline where speed or memory matters, or where you’d otherwise need Dask. Polars is the modern default for new pipelines.
• “What’s the streaming engine in Polars?” — Polars can process data larger than RAM by streaming chunks through the query plan, with operators that support it (filter, group-by-streaming, aggregations). Not all operators are streaming-friendly (yet).
• “DuckDB vs Polars?” — DuckDB: SQL, in-process, excellent at out-of-core Parquet analytics. Polars: DataFrame API, in-process, excellent at lazy + streaming. Overlap heavily; pick based on whether SQL or DataFrame API fits the codebase.
• “When would you reach for Spark vs DuckDB or Polars?” — when data exceeds what one machine can do. For a few hundred GB on a beefy machine, Polars / DuckDB usually beat Spark on wall clock and operational complexity. For genuine terabytes across many machines, Spark.
• “How would you migrate Pandas code to Polars?” — column expressions translate cleanly; replace chained method calls with the expression API; drop iterrows / apply (use vectorized expressions). For lazy: wrap pipelines in df.lazy()...collect() for query optimization.
• “Modin vs Polars?” — Modin is “Pandas API, distributed backend”; lower migration cost. Polars is a new API with significantly better performance per machine. Polars usually wins on raw speed; Modin wins when you have many Pandas-trained team members and want a less disruptive switch.