Chapter 22 — Key Takeaways (Python Transformations)

The page to keep open when deciding whether a job needs a cluster.

What each one is

Most complaints about pandas in a pipeline are complaints about using an interactive tool non-interactively.

pandas' four structural problems

1. Everything in memory, at a large multiple — 111 MB Parquet → 3,138 MB RSS
2. No query optimizer — a filter after a join stays after the join
3. Single-threaded for most operations — one core of thirty-two
4. The index, and the copy-versus-view ambiguity — SettingWithCopyWarning

🧭 pandas 3.0 fixed the fourth (Copy-on-Write: chained assignment now consistently does nothing) and made strings Arrow-backed. Both change behaviour a decade of tutorials describe.

The measurement

20,000,000 rows · 111.2 MB Parquet · 32 cores · pandas 3.0.2 / polars 1.41.2 / duckdb 1.4.4

The memory column matters more. Everything finished in under eleven seconds; the memory spread is 9.8×, and memory decides whether the job runs on the box you have.

77.5% of pandas' time was one operation — groupby() over a million groups. "pandas' group-by over a million groups is slow" tells you which jobs are affected; "pandas is 25× slower" does not.

Laziness is worth 1.5× time and 2.5× memory in the same library, from one keyword.

The ranking flipped between 2M and 20M rows. Any "X is faster than Y" that does not name a data size is not a claim.

⚠️ This benchmark measured nothing until each engine ran in its own process. RSS does not fall when a DataFrame is freed, so every engine inherited the fixture's high-water mark — and pandas appeared cheapest on memory, the opposite of the truth. It was caught because the result was implausible. When a benchmark surprises you, suspect the benchmark first.

Memory

$$\text{peak RSS} \approx \text{multiplier} \times \text{Parquet size}$$

📏 One machine handles roughly RAM ÷ 10 with a streaming engine, RAM ÷ 30 with a materializing one. 64 GB laptop → ~6 GB (DuckDB), ~2 GB (pandas).

A multiplier is a property of the QUERY as much as the engine. Polars-lazy used more than pandas-with-projection in Case Study 1 and far less in §22.6.

Ask how much ONE RUN touches, not how big the dataset is. Kestrel's clickstream is 341 GB a year and 934 MB a day — and that gap is where most reflexive reaching for Spark happens.

Out-of-core: DuckDB spills to disk automatically (SET memory_limit); Polars streams a growing subset (collect(engine="streaming") — check your query is covered); pandas does neither.

Types

⚠️ DuckDB's SUM(BIGINT) returns HUGEINT, and Parquet has no mapping for it — it lands as DOUBLE. Money becomes a float unconditionally, and loses cents above 2^53 ($90 trillion).

SUM(net_revenue_cents)::BIGINT AS revenue_cents   -- the whole fix

Assert the output SCHEMA, not only the output values. A value test passes on a float column holding exactly the right numbers.

Counts and ranks differ: Polars uint32 · pandas int64 · DuckDB BIGINT. All correct; a naive assert df1.equals(df2) across engines fails on dtype before comparing a value.

Reading files

⚠️ Type inference makes your schema a property of whatever arrived this morning.

• A null makes an integer column a float → the join silently drops rows
• A leading zero makes it a string → every comparison fails
• An empty file makes everything a string → the partition's schema conflicts with its neighbours

None of these fail at the read. They fail at a join, three models later, as a row count that is quietly wrong.

Measured, on a pure-digit column with leading zeros and no type declared:

A single letter anywhere in the column protects it, in all three. Feeds that survive on that are not safe — they are lucky, and luck expires when a supplier renumbers.

📐 A type declaration is only a control at the boundary where the information still exists:

pl.read_csv(path, schema_overrides={"sku": pl.String})   # ✅  "0041229"
pl.read_csv(path).with_columns(pl.col("sku").cast(str))  # ❌  "41229"

Both "declare the schema." One works. And casting is not validating — assert a format, not a count.

From notebook to pipeline — four lines

df = pl.read_parquet(path)                          # 1. declare the schema
assert df.height == df.select(KEY).n_unique()       # 2. assert the grain
assert df.height > MIN_ROWS                         # 3. assert a volume floor
write_partition(out, df, overwrite=True)            # 4. never append

Those cover an inferred type, a fan-out, an empty input, and a double run. ⭐ The one people leave out is the third, and Chapter 19 §19.8 is a whole section on why nothing else catches it.

Not on the list: rewriting it in another engine. If it is fast enough, promoting it is four lines and rewriting it is a project.

Choosing

1. Does something downstream need a pandas DataFrame? → pandas
2. Is it naturally SQL? → DuckDB (optimizer, streaming, lowest multiplier, transfers to your warehouse)
3. Is it naturally imperative? → Polars
4. Does one run exceed ~RAM ÷ 10? → Chapter 21, or a warehouse
5. Is it already written and fast enough? → leave it alone

📐 Kestrel chose DuckDB despite Polars winning the benchmark: nine people read SQL and three write Polars; a DuckDB model moves to Snowflake unchanged; the 2.9× multiplier keeps jobs on existing workers; and dbt-duckdb plugs into Chapter 19's project. Pick a default and name the exceptions with reasons — not a per-job choice nobody can predict.

The two case studies, compressed

Exit code 137. 128 + 9 — SIGKILL from the OOM killer, which cannot be caught, so there is no traceback. Three days went into the platform; Reason: OOMKilled would have taken a minute. The job read 34 columns to use 8, and columns= alone was a 3.5× reduction. The data grew 3% and the job stopped working.

🏭 Publish peak memory next to duration and alert at 60% of the limit — the gap between 60% and 90% is where a busy Monday lives. A job whose memory grows 3% a quarter has a computable failure date.

The zeroes. A supplier stopped quoting a column; read_csv inferred an integer; 346 SKUs stopped matching. The Chapter 19 control fired on the first morning and diagnosis still took three days, because an alert names where a defect is visible, not where it is. The idempotent upsert meant three broken nights produced one night's damage — a discipline containing a defect it was not written for.