Chapter 22: Python Transformations: pandas, Polars, DuckDB, and When Each Is the Right Tool

"The interesting question is not which is fastest. It is which one lets you keep the job on one machine."

Overview

Chapter 21 ended by saying that a modern single machine handles far more than most people assume, and that the tools which do it got much better while their reputations did not move. This chapter is that claim, measured.

Three tools, and they are not three versions of the same thing:

• pandas is the incumbent. Everyone knows it, everything integrates with it, and it has four structural problems that a decade of work has mitigated rather than removed.
• Polars is a columnar DataFrame engine with an expression API and a query optimizer. It looks like pandas from a distance and is a fundamentally different design underneath.
• DuckDB is an embedded analytical database. You write SQL, it reads your Parquet files directly, and it streams rather than materializing.

Everything in this chapter that is a number came from code/engine_benchmark.py, run on the machine this book was written on, and the harness ships so you can disagree with the numbers by producing better ones. That is deliberate: Chapter 11 §11.6 is about what happens when a benchmark is quoted rather than reproduced, and a chapter comparing three engines is exactly where that failure would land.

22.1 What Each One Actually Is

pandas (2008) was built for interactive financial analysis: a person at a REPL, a dataset that fits in memory, and a premium on expressiveness. Every design decision follows from that, and most of the complaints about pandas in a pipeline are complaints about using an interactive tool non-interactively.

Its data model is a collection of NumPy arrays plus an index — a labelled axis that aligns operations automatically. The index is the single most distinctive thing about pandas and the source of both its power and a large share of its confusion.

Polars (2020) is written in Rust over Apache Arrow. Two design choices matter:

An expression API. You describe what to compute (pl.col("x").sum().over("region")) rather than mutating a frame. Expressions are values — composable, reusable, and analysable by an optimizer.

A lazy mode with a real query planner. pl.scan_parquet(...) builds a plan; .collect() runs it. Between those two the optimizer pushes filters and column projections into the file read.

No index. Polars deliberately has none. Row order is data, joins are explicit, and the class of bug where an operation silently aligned on labels you had forgotten about does not exist.

DuckDB (2019) is "SQLite for analytics": an embedded, in-process OLAP database with a vectorized execution engine. It is a full SQL engine — window functions, CTEs, everything in Chapter 18 — that reads Parquet, CSV, JSON, Arrow tables, and pandas DataFrames without importing them first.

-- No load step. DuckDB reads the files where they are.
SELECT customer_id, SUM(net_revenue_cents)
  FROM read_parquet('s3://lake/silver/order_items/*.parquet')
 WHERE status IN ('paid','shipped','delivered')
 GROUP BY 1;

🎓 Interview Angle — "why would you use DuckDB instead of pandas?"

The weak answer is "it's faster." The strong answer names the two structural differences:

"DuckDB streams and pandas materializes." pandas loads the whole frame into memory before doing anything; DuckDB processes in batches and only keeps what the query needs. On the measurement in §22.6, that is 320 MB against 3,138 MB for the same query on the same data.

"DuckDB has a query optimizer and pandas executes what you wrote, in the order you wrote it." A filter written after a join stays after the join in pandas. DuckDB moves it.

And the honest third clause, which is what makes it a good answer rather than a rehearsed one: "pandas is still the right tool when the output has to go somewhere that expects a DataFrame, and for anything under a few hundred thousand rows the difference does not matter."

22.2 pandas' Four Structural Problems

Not complaints about the API. Four properties of the design, each of which produces a specific class of production incident.

One: everything is in memory, at a large multiple. A 111 MB Parquet file became 3,138 MB of resident memory in §22.6's measurement — 28× the file size. Parquet is compressed and dictionary-encoded; pandas is decompressed, and a string column that Parquet stores once per distinct value becomes a Python object per row in the object dtype.

Two: no query optimizer. pandas executes each statement as written, in order. Filter after a join and it joins first. Select two columns from a fifty-column read and it reads fifty.

Three: single-threaded for most operations. The machine this chapter was measured on has 32 cores. pandas used one of them; Polars and DuckDB used all of them.

Four: the index, and the copy-versus-view ambiguity it enables.

df[df.status == "paid"]["revenue_cents"] = 0   # may or may not do anything

This is the SettingWithCopyWarning, and the reason it is a warning rather than an error is that pandas genuinely cannot always tell. Copy-on-Write, which became the default in pandas 3.0, resolves this — chained assignment now consistently does nothing rather than sometimes working — which is an improvement and also means a decade of code and tutorials describe different behaviour.

🧭 Version Note — measured on pandas 3.0.2, Polars 1.41.2, DuckDB 1.4.4

requirements.txt pins pandas 2.2.3, Polars 1.17.1, and DuckDB 1.1.3 for reproducibility of the book's environment. The measurements in §22.6 were taken on newer versions, and they are reported with their versions attached because that is the only honest way to quote a benchmark.

What changed in pandas 3.0 and matters here:

• Copy-on-Write is the default. Chained assignment silently does nothing instead of sometimes working. Code that relied on the sometimes-working case is now silently wrong in a different way, which is a genuine migration hazard.
• Strings are Arrow-backed by default rather than object. This is a large memory improvement and it means older "pandas uses 10× your data size" advice understates modern pandas' improvement on string-heavy frames — though §22.6 still measured 28×.
• inplace=True and a long list of deprecated APIs are gone.

Polars is still pre-2.0 and its API moves. Between 0.19 and 1.x, groupby became group_by, pl.count() became pl.len(), and several apply variants were renamed. Pin it, and expect a migration when you bump it.

22.3 Polars: Expressions and Laziness

The expression API is the part worth learning deliberately, because it is what makes the optimizer possible.

import polars as pl

(df.filter(pl.col("status").is_in(KEEP))
   .group_by("customer_id")
   .agg(pl.col("net_revenue_cents").sum().alias("revenue_cents"),
        pl.len().alias("n_lines"))
   .with_columns(pl.col("revenue_cents").rank("ordinal", descending=True)
                   .over("region").alias("region_rank")))

pl.col("x").sum() is a value. You can assign it to a variable, put it in a list, build it in a loop, and pass it around — which is what makes complex logic composable in a way pandas' method chaining is not.

.over("region") is a window function, exactly Chapter 18 §18.2's, with the same semantics.

Lazy mode is where the design pays off, and the payoff is memory as much as time:

q = (pl.scan_parquet("order_items.parquet")     # nothing has been read
       .filter(pl.col("status").is_in(KEEP))
       .group_by("customer_id")
       .agg(pl.col("net_revenue_cents").sum()))
print(q.explain())                               # the plan
df = q.collect()                                 # NOW it runs

scan_parquet plus a lazy chain lets Polars push the filter and the column projection into the file read. Columns the query never mentions are never decompressed. In §22.6's measurement that is 1,211 MB against 3,061 MB for the identical query written eagerly — the same answer, in 40% of the memory and 66% of the time.

🧪 Try It — explain() before collect()

Take any lazy Polars query and print the plan before running it:

python print(q.explain())

Look for two things, and they are the Polars equivalents of Chapter 21 §21.3's plan-reading:

• PROJECT n/m COLUMNS — how many of the file's columns are actually read. If it says PROJECT */12 COLUMNS you are reading everything, and usually the cause is a select("*") or a collect() earlier in the chain than you meant.
• SELECTION: on the scan — the predicate that was pushed down. Empty means the filter runs after the read.

The most common cause of both is an accidental .collect() in the middle of a chain, which ends the lazy plan and starts a new one. Everything after it is eager, and the optimizer cannot see across the boundary.

22.4 DuckDB: SQL Over Files

DuckDB's proposition is that you already know SQL and your data is already in files.

import duckdb
con = duckdb.connect()               # in-memory, or a path for persistence

con.sql("""
  SELECT region, SUM(revenue_cents)::BIGINT AS revenue
    FROM read_parquet('silver/order_items/*.parquet') oi
    JOIN read_parquet('silver/dim_customer.parquet')  d USING (customer_id)
   WHERE oi.status IN ('paid','shipped','delivered')
   GROUP BY 1 ORDER BY 2 DESC
""").df()                            # -> a pandas DataFrame, zero-copy via Arrow

Four things this buys, and the third is the one people do not expect:

No load step. Glob patterns, Hive partitioning, S3 paths, and a full-fidelity Parquet reader with predicate and projection pushdown.

It queries your Python objects. A pandas or Polars DataFrame in scope is queryable by name:

orders = pl.read_parquet("orders.parquet")
duckdb.sql("SELECT status, COUNT(*) FROM orders GROUP BY 1")   # just works

It streams. DuckDB processes in vectors of ~2,048 rows and does not materialize intermediate results it does not need. This is why §22.6 measured its peak memory at 2.9× the file size against Polars-lazy's 10.9× and pandas' 28.2×, and it is the property that decides whether a job fits.

It is the same SQL as your warehouse, near enough. Chapter 18's window functions, CTEs, QUALIFY, and DISTINCT ON all work, which means a transformation prototyped locally is a transformation you can move.

22.5 Arrow: Why They Interoperate

Apache Arrow is a memory format, not a file format, and it is the reason moving data between these three is nearly free.

        Parquet on disk  ──read──▶  ARROW in memory
                                       │  │  │
                    ┌──────────────────┘  │  └──────────────────┐
                    ▼                     ▼                     ▼
                 Polars                DuckDB                 pandas
              (Arrow native)        (Arrow native)       (Arrow-backed in 3.0)

When both sides speak Arrow, handing data over is passing a pointer, not serializing and parsing.

tbl = duckdb.sql("SELECT ...").arrow()      # zero-copy
pl.from_arrow(tbl)                          # zero-copy

The exception is worth knowing: converting to legacy pandas dtypes — object strings, NumPy datetimes — is a real copy and a real conversion. .df() may be cheap or may not, depending on the column types. .arrow() is always cheap, so when you are chaining engines, stay in Arrow and convert once at the end.

22.6 A Measured Comparison

Everything below came from code/engine_benchmark.py. The workload is the shape of a real gold model rather than a microbenchmark: read Parquet → filter on status → aggregate to customer grain → join a dimension → rank within region → write Parquet.

Each engine runs in its own process, which is not fussiness — see the ⚠️ callout below.

20,000,000 order lines, 111.2 MB Parquet, 1,000,000 customers. 32 cores. python 3.13.12 · pandas 3.0.2 · polars 1.41.2 · duckdb 1.4.4 · pyarrow 24.0.0

And at a tenth of the size — 2,000,000 rows, 11.2 MB:

Five things in that data, in order of how much they should change your behaviour.

One: the memory column matters more than the time column. Every engine here finishes in under eleven seconds. The spread in memory is 9.8×, and memory is what decides whether the job runs on the box you have. A 111 MB file becoming 3.1 GB is how a "small" job needs a large instance.

Two: pandas' penalty grows with size — 10× at 2M rows, 25× at 20M — because the single-threaded gap widens as the parallel engines get more to do.

Three: it is one operation. The per-step breakdown:

pandas, 20M rows          seconds    share
  import                    0.234     2.3%
  read                      0.747     7.2%
  filter                    1.135    10.9%
  groupby                   8.042    77.5%   ←
  join                      0.089     0.9%
  rank+sort                 0.057     0.6%
  write                     0.067     0.6%

77.5% is groupby().agg() over a million groups. "pandas is 25× slower" is a much less useful sentence than "pandas' group-by over a million groups is slow," because the second one tells you which jobs are affected and which are not.

Four: laziness is worth 1.5× in time and 2.5× in memory — polars-lazy against polars-eager, same answer, same library, one keyword. That is the cheapest available improvement in this chapter.

Five: the ranking changes with scale. Polars-lazy wins at 2M; at 20M it and DuckDB are within 21%, and DuckDB uses a quarter of the memory. Any statement of the form "X is faster than Y" that does not name a data size is not a claim.

⚠️ Failure Mode — this benchmark measured nothing until it ran each engine in its own process

The first version of engine_benchmark.py measured all four engines in one Python process, and the memory column was meaningless. Worth stating plainly, because the mistake is easy and the output looks fine.

RSS is a property of the process and it does not go down when a DataFrame is freed. Allocators keep the memory. So:

• The fixture generation allocated several hundred megabytes before any engine ran.
• Every engine's "peak" therefore included that floor.
• Whichever engine ran first looked worst, and the ordering was partly an artifact of the loop.

In the contaminated run, pandas appeared to use the least memory of the four — the exact opposite of the truth — because it ran last, on a process whose high-water mark had already been set by everything before it.

The fix is one subprocess per engine, and the general lesson is Chapter 18 Case Study 2's: a measurement with only one implementation has no error bar. The reason this was caught is that the result was implausible — pandas cheapest on memory — and implausibility is the cheapest error detector there is. Use it. When a benchmark tells you something surprising, suspect the benchmark first.

22.7 Memory Is the Number That Decides

On one machine, the constraint that matters is peak resident memory, and the useful way to think about it is a multiplier on your file size.

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

Those are for this workload, and the multiplier depends on what your query does — a query that only sums one column will be far kinder to every engine than one that aggregates to a million groups. Measure your own. The harness takes a --rows argument for exactly this.

Four things that inflate the multiplier, in rough order of impact:

Decompression. Parquet with zstd is often 5–12× smaller on disk than in memory (Chapter 11 §11.6 measured 12.3× for Kestrel's clickstream). Most of the multiplier is this, and it is unavoidable.

High-cardinality grouping. A million groups means a million-entry hash table plus its outputs.

Joins. The build side is materialized.

Legacy string dtypes. object strings are a Python object per row. Arrow-backed strings — the pandas 3.0 default — are dramatically better, which is why the modern multiplier is 28× and older advice says worse.

📏 Scale Note — the rule of thumb worth carrying

A single machine comfortably handles a Parquet dataset of roughly RAM ÷ 10 with an engine that streams, and RAM ÷ 30 with one that materializes.

On a 64 GB laptop: about 6 GB of Parquet with DuckDB, 2 GB with pandas. On a 512 GB instance ($3/hour, Chapter 21 §21.1): about 50 GB and 17 GB.

Kestrel's entire year of clickstream is 341 GB of Parquet (Chapter 1 §1.6), so it is genuinely a Spark job. One day is 934 MB, which fits on a laptop with room to spare — and that gap is where most of the reflexive reaching for Spark happens: a job is sized for the archive when it only ever processes a day.

The question to ask is not "how big is the dataset?" but "how much of it does one run touch?"

22.8 The Out-of-Core Question

What if it does not fit?

DuckDB spills to disk automatically. Set a memory limit and it will use temporary files rather than failing:

con.execute("SET memory_limit='8GB'")
con.execute("SET temp_directory='/fast/scratch'")

This is the single biggest practical difference between DuckDB and the others, and it changes the failure mode from "the process dies" to "the query is slower." Put the temp directory on fast local storage; on a cloud instance that means the NVMe, not the network volume.

Polars has a streaming engine for a growing subset of operations, invoked with .collect(engine="streaming"). It handles scans, filters, projections, and many aggregations without materializing. Check whether your specific query is supported rather than assuming — coverage has been expanding release to release, and an unsupported operation falls back to in-memory silently.

pandas has nothing. Chunk it yourself with chunksize, and accept that any operation needing a global view — a sort, a group-by across chunks, a join — has to be implemented by you.

And the honest fourth option: when the working set genuinely exceeds one machine, that is Chapter 21. The engines here are not competing with Spark at 4 TB; they are competing with the reflex of reaching for Spark at 4 GB.

22.9 Types and Nulls: Where the Three Disagree

Three engines agreeing on a row count tells you very little. They routinely disagree on the types, and one of those disagreements silently turns money into a float.

⚠️ Failure Mode — DuckDB's SUM over integers writes Parquet as a FLOAT

This was found by engine_benchmark.py's self-check, which asserts that money stays an integer type end to end. It failed on DuckDB, and the mechanism is worth knowing.

SUM(BIGINT) returns HUGEINT — a 128-bit integer. That is a good decision: it means a sum of 64-bit values cannot overflow.

Parquet has no 128-bit integer primitive that the writer maps HUGEINT to, so it is written as DOUBLE. Your integer-cents column is now a float, unconditionally, whatever its magnitude.

```python

Measured, not asserted:

SUM over BIGINT -> parquet column type: double

true value 9,007,199,254,740,993

round-tripped 9,007,199,254,740,992.0 ← one cent short

```

$$2^{53}\ \text{cents} = \$90{,}071{,}992{,}547{,}409.93$$

So the precision loss needs a $90 trillion total and will not bite Kestrel. The type change is immediate and unconditional, and that is the real defect: every downstream consumer now has a float money column, all further arithmetic is float arithmetic, and validate.py's money-as-float rule is violated by the output of a pipeline whose source was clean.

The fix is one cast:

sql SUM(net_revenue_cents)::BIGINT AS revenue_cents

The general lesson is bigger than DuckDB. Every engine has a widening rule for aggregates, and the widened type may not survive your output format. Assert the output schema, not only the output values — a test that checks the numbers are right will pass on a float column containing exactly the right numbers.

Two more disagreements, both measured in the same benchmark:

Counts and ranks have different integer types. pl.len() and Polars' rank() return uint32; pandas' size and rank return int64; DuckDB's COUNT(*) and ROW_NUMBER() return BIGINT. All three are correct. A downstream schema check comparing them will fail, and a naive assert df1.equals(df2) across engines fails on dtype before it ever compares a value.

Nulls. Arrow-native engines have one null concept. pandas historically had several — NaN for floats, None for objects, NaT for datetimes — and NaN != NaN, so a group-by on a column with nulls behaves differently depending on dtype and on the dropna argument. pandas 3.0's Arrow backing narrows this considerably, and it does not eliminate it in code that still uses NumPy dtypes.

22.10 Choosing

A decision procedure, in the order to apply it.

1. Does the output need to be a pandas DataFrame for something downstream? scikit-learn, a plotting library, a colleague's notebook. If yes, and the data is small, use pandas. The conversion cost and the cognitive cost of two engines usually exceed the speedup.

2. Is the transformation naturally SQL? Filters, joins, aggregations, window functions — Chapter 18's material. If yes, use DuckDB. You get the optimizer, the streaming, the lowest memory multiplier here, and SQL that transfers to your warehouse.

3. Is it naturally imperative? Row-wise logic that resists SQL, a parser, a state machine, a call into a library. Use Polars, whose expression API handles the awkward cases without dropping to a Python loop.

4. Does one run's working set exceed roughly RAM ÷ 10? Then it is Chapter 21, or a warehouse.

5. Is it already written in pandas and fast enough? Leave it alone. A rewrite has a cost and a risk, and "we could make this 25× faster" is not a reason to touch a job that takes four seconds.

📐 Design Decision — Kestrel standardized on DuckDB, and it was not because it won the benchmark

Polars-lazy was the fastest engine at both scales measured. Kestrel chose DuckDB for single-machine transformations anyway, and the reasoning generalizes past this chapter.

One: SQL is the team's shared language. Four engineers, three analysts, two scientists (Chapter 1 §1.5). All nine read SQL. Three write Polars. A transformation only one third of the team can review is a transformation with one reviewer.

Two: it moves. A DuckDB model is a Snowflake model with a different read_parquet. A Polars pipeline is a rewrite.

Three: memory, not speed. The 2.9× multiplier is what keeps jobs on the orchestrator's existing workers instead of on dedicated instances, and that saved more than the 21% speed difference cost.

Four: dbt-duckdb. It plugs into Chapter 19's project. Polars would be a second execution model in the DAG — exactly Chapter 21 §21.1's counterweight, in the other direction.

Polars is used for two jobs where the logic is genuinely imperative, and both have a comment saying why. That is the shape to aim for: a default, and named exceptions with reasons — not a per-job choice that no one can predict.

22.11 Reading Files Is Where It Actually Breaks

Every measurement in §22.6 read Parquet, which is the easy case: Parquet carries its own schema, so there is nothing to infer and nothing to get wrong. Most real pipelines do not get that, and the read step is where more incidents originate than every transformation in this chapter combined.

CSV has no types. Whatever reads it must guess, and the three engines guess differently.

pd.read_csv("orders.csv")            # infers from the first ~1M rows
pl.read_csv("orders.csv")            # infers from the first 100 rows by default
duckdb.sql("FROM read_csv('orders.csv')")   # samples, and will tell you

Four traps, in order of how often they cause an incident:

A ZIP-code column becomes an integer, and 02134 becomes 2134. Any identifier with leading zeros — postcodes, SKUs, account numbers, some order IDs — is a string that looks like a number.

A mostly-integer column with one null becomes a float, in NumPy-backed pandas, because NumPy integers cannot hold nulls. order_id arrives as 1.0, 2.0, 3.0, joins fail silently against an integer key, and the row count drops. pandas' nullable and Arrow-backed dtypes solve this and are not the default for read_csv output.

A date is parsed with the wrong convention. 03/04/2026 is March 4th or April 3rd depending on who wrote the file, and every engine will pick one without asking.

Inference is based on a sample. Polars reads 100 rows by default; if row 40,000 is the first non-numeric value in a column, the read fails there — or worse, succeeds with the rest coerced.

The fix is the same in all three: state the schema.

pl.read_csv("orders.csv", schema_overrides={"zip": pl.String,
                                            "order_id": pl.Int64})
pd.read_csv("orders.csv", dtype={"zip": "string", "order_id": "Int64"})
duckdb.sql("FROM read_csv('orders.csv', columns={'zip':'VARCHAR', ...})")

⚠️ Failure Mode — inference makes the schema a property of the data

This is the sentence worth carrying out of the section. When you let a reader infer types, your pipeline's schema is determined by whatever arrived this morning.

The consequences are all of the same shape and all silent:

• Tuesday's file has a null in quantity; the column is a float on Tuesday and an integer on Wednesday. The downstream join works on one day and not the other.
• A new SKU with a leading zero appears; the column that was an integer for two years becomes a string, and every comparison against it now fails.
• A file that happens to be empty infers every column as object/String, and the write produces a Parquet file whose schema conflicts with every other file in the partition.

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

Chapter 17's contract is the real fix and this is the local half of it: a declared schema at the read is the enforcement point, and it belongs in code rather than in the data. If you cannot declare it because you genuinely do not know, that is a finding about the source rather than a reason to infer.

JSON has types and they are the wrong ones. Everything numeric is a double, so integer cents survive JSON as floats unless you cast. Kestrel's clickstream is ~820 bytes of JSON per event (Chapter 1 §1.5), and the conversion to Parquet is where the types get fixed — which is precisely why that conversion is a modelled step and not a copy.

Writing has one decision that matters: compression and row-group size, which is Chapter 11's material. The default is usually fine; the thing that is not fine is the file count, which is Chapter 21 Case Study 2.

22.12 From Notebook to Pipeline

Most pandas in production started as a notebook, and the transition is where a working analysis becomes an unreliable job. Five things change, and none of them is about performance.

One: hidden state becomes real. A notebook's correctness can depend on cells having been run in an order that is not the order they appear in. A script runs top to bottom, once. Every notebook has at least one cell that only works because of a variable defined in a cell someone later deleted.

Two: head() stops protecting you. Interactive work looks at the first few rows constantly, which catches gross errors immediately. A scheduled job looks at nothing. The assertions in §22.13 are the replacement for looking, and they are not optional in the way they feel while the code still runs next to a person.

Three: the data changes and the schema does not follow. §22.11. In a notebook you re-run and see the error; in a job the inferred dtype shifts and the failure surfaces downstream.

Four: memory stops being elastic. A laptop with 64 GB tolerates a 28× multiplier on a 1 GB file. A container with a 4 GB limit does not, and the failure is an OOM kill with no traceback — the process is simply gone, which is the least informative failure in this book.

Five: it has to be idempotent. Chapter 20 §20.3. A notebook that appends to a file is fine because a person runs it once; the same code on a schedule is Chapter 1's duplicate-rows incident.

🏭 From the Pipeline — the four-line diff that makes a notebook a job

Not a rewrite. Kestrel's checklist for promoting an analysis, in the order that catches the most per line changed:

```python

1. Declare the schema at every read. §22.11.

df = pl.read_parquet(path) # or read_csv with schema_overrides

2. Assert the grain immediately after the read.

assert df.height == df.select(KEY).n_unique(), "grain violated at read"

3. Assert a volume floor. Ch. 19 CS1 -- everything else passes on empty.

assert df.height > MIN_ROWS, "only %d rows" % df.height

4. Make the write idempotent. Ch. 20 §20.3.

write_partition(out, df, overwrite=True) # never append ```

Four lines, and they cover the four failures that actually happen: an inferred type, a fan-out, an empty input, and a double run.

The one people leave out is the third, because it feels redundant next to the others. It is the one that catches an upstream that stopped — and Chapter 19 §19.8 is a whole section on why nothing else will.

What is deliberately not on the list: converting to Polars or DuckDB. That is §22.10 step 5 — if the pandas version is fast enough, promoting it is four lines, and rewriting it is a project.

22.13 Testing a Python Transformation

The best thing about all three of these is that they need no infrastructure to test. No warehouse, no cluster, no fixtures loaded into a database.

def test_revenue_excludes_cancelled():
    lines = pl.DataFrame({
        "order_id": [1, 2, 3],
        "status": ["paid", "cancelled", "shipped"],
        "net_revenue_cents": [1000, 9999, 2500],
    })
    got = transform(lines)                          # the function under test
    assert got["revenue_cents"].sum() == 3500

Four assertions worth making beyond the values, and the first is the one §22.9 exists for:

The output schema, including types. Not only the columns — the dtypes. This is what catches the DuckDB float.

The grain. assert got.height == got.select("customer_id").n_unique(). Chapter 18 §18.7, and it fails in two directions.

Determinism. Run the transformation twice on the same input and compare full rows. A non-deterministic tiebreaker (Chapter 18 §18.7) shows up here and essentially nowhere else.

Empty and null inputs. An empty frame is the input your transformation will actually receive one morning, and a group-by over an empty frame returns different things in different engines.

💸 Cost Check — the single machine against the warehouse, priced

The choice between DuckDB on one box and a warehouse is usually argued on elegance. Here it is on the frozen rate card.

The workload: the daily gold build — read 934 MB of Parquet, filter, aggregate to customer grain, join a dimension, rank within region, write. Measured at 0.5 s on 32 cores for 20 M rows; call the real build 4 minutes at Kestrel's volume.

```text A. A WAREHOUSE (Medium, 4 credits/h at $2.00) 4 min x 30 days = 2 h/month x 4 credits x $2.00 $16.00 / month plus the 60-second minimum on each resume negligible here

B. A DEDICATED MACHINE, always on (r6i.8xlarge, $2.400/h) 720 h x $2.400 $1,728.00 / month

C. THE SAME MACHINE, started for the job and stopped (4 min + ~2 min startup) x 30 = 3 h x $2.400 $7.20 / month

D. AN EXISTING ORCHESTRATOR WORKER, already paid for marginal cost $0.00 / month ```

Row B is why "just use a big machine" gets rejected in cost reviews, and it is the version people price. Rows C and D are the ones that are actually on offer, and both beat the warehouse.

Three things the table does not say, and they usually decide it:

The warehouse is already there. Its marginal cost for one more model is $16 a month and its marginal operational cost is zero. Row D's $0.00 assumes a worker with 32 cores and enough memory, which is itself a decision with a cost.

Concurrency is not on this page. One model on one machine is cheap; forty models needing to run in three hours is a scheduling problem that a warehouse solves by adding compute and a single machine solves by queueing.

And the memory ceiling is a cliff, not a curve (§22.7). A warehouse degrades by spilling; a machine at its limit fails, and the failure arrives at an input size nobody predicted.

Kestrel's answer, and the reasoning generalises: DuckDB for anything that fits comfortably, the warehouse for anything that must run concurrently with other things or that any consumer queries directly. The cost difference between $16 and $7.20 is not what decides it — the operational surface is (Chapter 5 §5.1), and that is the argument to make rather than the arithmetic.

🔁 Idempotency Check — a notebook is not idempotent and a pipeline must be

The transition from a notebook to a scheduled job is where most Python transformation bugs are born, and the reason is that a notebook's execution model is fundamentally not repeatable.

text notebook a pipeline needs ───────────────────────────────────────────────────────────────────────── cells run in whatever order you one entry point, top to bottom clicked them state persists between runs a fresh process every time a variable defined three cells ago explicit inputs and outputs and since deleted from the source `df = df.append(...)` run twice an idempotent write the current date, implicitly an interval, passed in (ch 24) a file path on someone's laptop a path derived from configuration

Row four is the classic and it survives the port. A cell that appends and is executed twice produces a DataFrame with duplicates; the notebook's author restarts the kernel and does not think about it again. The same code in a retried Airflow task produces the same duplicates and nobody restarts anything.

The three-step port that works:

text 1. RESTART AND RUN ALL, and confirm the notebook still produces the same output. Most do not, and finding that out in the notebook is much cheaper than finding it out in production. 2. Extract to a module with ONE function taking the interval as an argument. No module-level state, no date.today(), no absolute paths. 3. Run it twice against the same interval and diff the target in BOTH directions.

Step 1 is the one people skip and it fails startlingly often — a variable from a deleted cell, an import that was run once, a file read that has since been commented out. "Restart and run all" is a five-second test that catches the majority of what step 3 would catch, before any porting work.

And the pandas-specific hazard worth naming: chained assignment. df[df.a > 1]['b'] = 0 may modify a copy, may modify the original, and the behaviour has changed between versions. In pandas 3 it raises, which is a genuine improvement, and code ported from an older notebook will meet it.

🔐 Privacy & Governance — the laptop is the least-governed place your data will ever be

Everything in this chapter runs on one machine, and that machine is usually somebody's laptop — which has no access log, no retention policy, no encryption guarantee you can point at, and a backup to a personal cloud account.

text where the data ends up during ordinary local work ───────────────────────────────────────────────────────────────────── ~/Downloads/orders_export.csv the classic ~/.duckdb/ a local database file /tmp/spill/ an out-of-core engine's spill ~/.ipynb_checkpoints/ a notebook with output CELLS, containing rows, in git a screenshot in a chat message outside every system you own

The fourth row is the one that reaches version control. A Jupyter notebook stores its output in the file, so a df.head() showing twenty customers with names and emails is committed, pushed, and retained in the git history forever — where it survives deleting the file.

Four controls, in order of effort:

Strip notebook outputs before committing. nbstripout as a pre-commit hook is one line of configuration and it removes the whole class.

Work against masked or sampled data locally. Chapter 27 §27.12's pathological fixture is designed for this; a local dataset should be the rows that break things, not the rows that identify people.

Set the spill directory deliberately, and on encrypted storage. DuckDB and Polars both spill to /tmp by default; on a shared machine that is readable, and on any machine it survives the process.

And do not download. A query against the warehouse is logged, access-controlled, and revocable. A CSV on a laptop is none of those, and the moment it is downloaded it is outside every mechanism Chapter 30 and Chapter 31 build.

The uncomfortable observation worth stating plainly: the strongest privacy control available here is friction, and the whole point of this chapter is to remove friction. DuckDB reading Parquet straight from object storage is faster and better governed than the same data downloaded — which makes it the rare case where the convenient path is also the correct one, and it is worth saying so to a team rather than relying on policy.

🔎 Read the Plan — all three engines will tell you, and only one is easy to read

```python

DuckDB: a real query plan, in the same vocabulary as a warehouse

duckdb.sql("EXPLAIN ANALYZE SELECT ... FROM 'orders/*.parquet' WHERE ...").show()

┌─────────────────────────────┐

│ PARQUET_SCAN │

│ Filters: order_date=... │ <- pushed into the file

│ Projections: 2 of 28 │ <- projection pushdown

│ Rows: 17,753 / 6,480,000 │ <- what it actually read

└─────────────────────────────┘

Polars: the optimised LOGICAL plan, and it is genuinely readable

lf.explain(optimized=True)

PARQUET SCAN

PROJECT 2/28 COLUMNS

SELECTION: [(col("order_date")) == (...)] <- pushed down

pandas: there is no plan. There is no optimiser. What you wrote is

what runs, in the order you wrote it.

```

The pandas row is not a gap in the tooling; it is the difference §22.1 draws. pandas is a library that executes your statements; the other two are engines that decide how to execute your intent. You cannot read a plan for a program that has none.

What to look for in the two that have one:

Projection and selection pushed into the scan. 2 of 28 columns and a filter shown at the scan node mean the engine is reading a fraction of the file. If the filter appears above the scan, it is being applied after reading everything — usually because the predicate is on a computed column, which is Chapter 1's mistake in a new engine.

The rows read against the rows in the file. 17,753 / 6,480,000 is the pruning working; the two numbers being equal is the single most useful signal on the page.

And, in Polars, whether the plan is optimized=True. lf.explain() defaults to the unoptimised plan on some versions, which shows your pipeline as written and tells you nothing about what will run.

The habit: EXPLAIN before you optimise, in every engine that has one, and for pandas — measure, because there is nothing else to read.

🧭 Version Note — pandas 3, and why old advice about it is now wrong

pandas changed more between 2.0 and 3.0 than in the decade before, and a great deal of widely repeated advice is now obsolete in a way that is easy to miss because the API looks the same.

text then now ───────────────────────────────────────────────────────────────────────── object dtype for strings Arrow-backed strings by DEFAULT -> the memory multiplier fell from ~60x to ~28x int64 cannot hold null nullable Int64 exists, and a left join no longer silently promotes your integers to float chained assignment sometimes works copy-on-write; it RAISES .append() removed. Use pd.concat. inplace=True is faster it never was, and it is deprecated "use pandas for everything" Arrow interop makes handing off to Polars or DuckDB nearly free

Two of those change advice in this chapter's own subject.

The string change moved the memory multiplier by a factor of two, which means published benchmarks from before it — including a lot of the "pandas uses 10× your data size" folklore — are measuring a different library. §22.7's 28× is measured, on this version, and it will move again.

And copy-on-write turned a class of silent bug into an exception. df[df.a > 1]['b'] = 0 used to modify a copy sometimes and the original sometimes, depending on the memory layout. It now raises, which will break code ported from an older notebook and is unambiguously an improvement.

What has not changed: the four structural problems in §22.2, the fact that pandas has no optimiser, and the decision procedure in §22.10. pandas is still a library that executes your statements in the order you wrote them, and no amount of backend improvement changes that.

The practical instruction: pin the version in any benchmark, and re-run it after a major upgrade. This chapter's own numbers carry their versions for exactly this reason.

22.14 The Kestrel Platform

🧱 Kestrel Platform — Increment 22: the local transformation path

Every model from Chapter 19 already runs on DuckDB through dbt-duckdb. This increment adds the things that are not dbt models, and the benchmark that justifies the choice.

text platform/transform/ local/ benchmark.py ← engine_benchmark.py, pointed at real Kestrel data ua_parse.py ← Polars: user-agent parsing, genuinely imperative reconcile.py ← DuckDB: compare two Parquet trees, both directions tests/ test_ua_parse.py test_schema_contracts.py ← §22.9's schema assertions

Five things this increment must get right:

1. Run benchmark.py on one real day of Kestrel clickstream — 14,000,000 events, 934 MB — and record the numbers in an ADR. Not the book's numbers. Yours.
2. Every DuckDB aggregate over money carries ::BIGINT. Add a lint for SUM(*_cents) without a cast; §22.9 is a one-line defect that no value-based test catches.
3. test_schema_contracts.py asserts output dtypes, not just values, for every transformation that crosses an engine boundary.
4. ua_parse.py carries a comment saying why it is Polars and not DuckDB. The rule from §22.10's callout: a default, and named exceptions with reasons.
5. reconcile.py is the EXCEPT-both-directions comparison from Chapter 18 Case Study 1, running over Parquet trees with no warehouse involved. It is the tool every migration in Chapter 37 needs.

The exercise that matters is 22.23(a): run the benchmark on your own hardware and find a place where this chapter's ordering does not hold. There will be one — the ranking already flipped between 2M and 20M rows on one machine — and finding it is the point.

22.15 Summary

Three tools, three designs. pandas is an interactive tool used non-interactively; Polars is a columnar engine with an expression API and an optimizer; DuckDB is an embedded SQL database that reads your files where they are.

pandas' four structural problems: everything in memory at a large multiple · no query optimizer · single-threaded · the index and its copy-versus-view ambiguity. Copy-on-Write in pandas 3.0 fixes the fourth and changes behaviour a decade of tutorials describe.

Measured, 20M rows, 111 MB Parquet, 32 cores: polars-lazy 0.412 s · duckdb 0.496 s · polars-eager 0.620 s · pandas 10.435 s (25.35×). And 77.5% of pandas' time is one operation — a group-by over a million groups.

The memory column matters more than the time column. 320 MB (DuckDB) to 3,138 MB (pandas) for the same query: 9.8×, and memory is what decides whether the job runs on the box you have.

Laziness is worth 1.5× time and 2.5× memory in the same library, from one keyword. scan_parquet + .collect() pushes filters and projections into the read.

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

⚠️ 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, which is the opposite of the truth. When a benchmark surprises you, suspect the benchmark first.

Rule of thumb: one machine handles roughly RAM ÷ 10 of Parquet with a streaming engine and RAM ÷ 30 with a materializing one. And 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.

DuckDB spills to disk automatically; Polars streams a growing subset of operations; pandas does neither.

⚠️ DuckDB's SUM(BIGINT) returns HUGEINT, which Parquet stores as DOUBLE. Money becomes a float unconditionally, and loses cents above 2^53. ::BIGINT fixes it. Assert the output schema, not only the output values — a value test passes on a float column holding exactly the right numbers.

Choose in this order: does something downstream need pandas · is it naturally SQL (DuckDB) · is it naturally imperative (Polars) · does one run exceed RAM ÷ 10 (Chapter 21) · is it already written and fast enough (leave it alone).

⚠️ Type inference makes your schema a property of whatever arrived this morning. A null makes an integer column a float; a leading zero makes it a string; an empty file makes everything a string. None of these fail at the read — they fail at a join, three models later, as a row count that is quietly wrong. Declare the schema in code.

Promoting a notebook to a job is four lines, not a rewrite: declare the schema at the read, assert the grain, assert a volume floor, make the write idempotent. Those cover an inferred type, a fan-out, an empty input, and a double run — and the volume floor is the one people leave out.

Pick a default and name the exceptions with reasons. Kestrel chose DuckDB despite Polars winning the benchmark, because nine people read SQL and three write Polars, and because a DuckDB model moves to the warehouse unchanged.

Chapter 23 turns from making data to trusting it: Great Expectations, dbt tests, and why bad data is worse than no data.

Key terms: pandas · Polars · DuckDB · Apache Arrow · type inference · schema_overrides · zero-copy · eager evaluation · lazy evaluation · predicate pushdown · projection pushdown · out-of-core · streaming execution · Copy-on-Write · object dtype · memory multiplier · HUGEINT · integer cents