Exercises: Python Transformations

Most of these are measurements. code/engine_benchmark.py generates its own data and runs the whole comparison, so nothing here needs a warehouse, a cluster, or a download.

The book's numbers are from one machine. Several of these exercises ask you to disagree with them, and the right answer is whatever your hardware says.

Difficulty: ⭐ warm-up · ⭐⭐ standard · ⭐⭐⭐ deeper. Solutions: daggered (†) and odd-numbered problems are in appendices/answers-to-selected.md.

Part A — Warm-ups ⭐

22.1 † Say in one sentence each what pandas, Polars, and DuckDB are — not what they do well, what they are.

22.2 List pandas' four structural problems. Which one did pandas 3.0 resolve, and what did resolving it break?

22.3 † What does scan_parquet + collect() buy over read_parquet? Give both effects with the measured magnitudes.

22.4 Why can DuckDB query a pandas DataFrame in scope without importing it? What is the one conversion that is not free?

22.5 † Which column of §22.6's table matters more, and why?

22.6 Give the memory multipliers for the four engines measured. What is the multiplier a property of — the engine, or the query?

22.7 † State the RAM ÷ n rule of thumb for streaming and materializing engines. Apply it to a 64 GB laptop.

22.8 What does DuckDB's SUM(BIGINT) return, and what happens when you write it to Parquet? Give the fix.

22.9 † Give §22.10's decision procedure in order. Which step is the one people skip?

22.10 State the four lines that promote a notebook to a job, and which of the four is most often left out.

Part B — Standard ⭐⭐

22.11 Run engine_benchmark.py at 2M and 20M rows on your machine. Report your table. How does the ordering compare to the book's, and what about your hardware explains any difference?

22.12 † Run it at a size large enough that one engine fails or spills. Report which fails first, at what size, and with what error. The error message is part of the answer.

22.13 Take the pandas implementation in the benchmark and make it faster without changing engines. Try: columns=, categorical dtypes for the string columns, and observed=True on the group-by. Report the ratio you achieve against the original, and how much of the 25× gap you closed.

22.14 † Reproduce §22.9's DuckDB finding. Write a SUM over integer cents to Parquet without the cast, read the schema, and then find the smallest value at which the round trip is lossy. Report the value and show your work.

22.15 Take a Polars lazy query and insert a .collect() in the middle. Print explain() for both versions. Report the PROJECT n/m COLUMNS line for each, and the memory difference.

22.16 † Reproduce Case Study 2. Write two CSVs — one column all digits with leading zeros, one mixed — and read both with pandas, Polars, and DuckDB with no type declared. Report the six results. Then fix all three.

22.17 Measure the peak memory of a job you own. Use the PeakRSS class from the benchmark, or read /sys/fs/cgroup/memory.peak. Report it as a fraction of its container limit, and say whether Case Study 1's 60% threshold would have fired.

22.18 † Write the same non-trivial transformation three times — pandas, Polars, DuckDB — and have a test assert that all three produce identical output including dtypes. Report what you had to reconcile.

Part C — Deeper ⭐⭐⭐

22.19 §22.7's multipliers came from one workload. Construct a workload where the ordering is reversed — where pandas uses less memory than DuckDB — and explain the property of the query that causes it.

22.20 † Case Study 1's team rejected an 8 GB limit because the job did not need it. Formalize that test: given a job and a limit request, what evidence would you require to grant it? Apply your test to a real request you have seen.

22.21 §22.6's benchmark measured nothing until each engine ran in its own process. Audit a benchmark you did not write — a blog post, a vendor page, a README — for the same class of flaw. What could you not determine from what they published?

22.22 † Design the lint rule that catches SUM(*_cents) without a ::BIGINT cast in DuckDB SQL, including inside dbt models and Jinja. State its false-positive rate and what it cannot see.

Part D — The Kestrel Platform ⭐⭐⭐

22.23 — Increment 22: the local transformation path.

(a) Run engine_benchmark.py against one real day of Kestrel clickstream — 14,000,000 events, 934 MB — and record the results in an ADR with your hardware and library versions. Find a place where this chapter's ordering does not hold. There will be one.

(b) Write platform/transform/local/ua_parse.py in Polars, with a comment saying why it is not DuckDB.

(c) Write platform/transform/local/reconcile.py: a DuckDB EXCEPT-both-directions comparison of two Parquet trees, with no warehouse involved.

(d) Write tests/test_schema_contracts.py asserting output dtypes, not just values, for every transformation that crosses an engine boundary.

(e) Add the SUM(*_cents) lint from 22.22 to CI.

22.24 † Add a memory budget to every scheduled Python job: measure peak RSS per run, publish it next to duration, and fail the build for any job above 60% of its container limit. Report how many of your jobs fail on the first run.

Expect the answer to be more than zero. Case Study 1's audit found three of eleven, one at 91%, none of which had failed yet — and the one at 91% had a computable failure date about five weeks out.

Reflection

A. §22.6's benchmark produced a wrong answer — pandas cheapest on memory — and it was caught because the result was implausible. How much of your confidence in any measurement rests on the result being what you expected, and what does that imply about measurements that surprise you in a direction you like?

B. Kestrel chose DuckDB despite Polars winning the benchmark, on the grounds that nine people read SQL and three write Polars. Is that a technical decision? Where else in this book has the same shape of argument appeared, and did it come out the same way?