Appendix C: Python Data Tooling Reference

Four tools, one decision. Chapter 22 measured them; this is the reference for using whichever one the measurement chose.

The default recommendation from Chapter 22: DuckDB for anything you would write as SQL, Polars for anything you would write as a transformation, pandas for anything whose library you need.

C.1 Reading and Writing

import pandas as pd, polars as pl, duckdb, pyarrow.parquet as pq

# --- Parquet
df  = pd.read_parquet("orders.parquet", columns=["order_id", "net_cents"])
pdf = pl.read_parquet("orders.parquet", columns=["order_id", "net_cents"])
lf  = pl.scan_parquet("orders/*.parquet")            # lazy: nothing read yet
duckdb.sql("SELECT * FROM 'orders/*.parquet' LIMIT 5")

# --- CSV, with the types stated. Never infer types on data you care about.
df = pd.read_csv("suppliers.csv", dtype={"sku": "string", "qty": "Int64"})
pdf = pl.read_csv("suppliers.csv", schema_overrides={"sku": pl.Utf8})
duckdb.sql("SELECT * FROM read_csv('suppliers.csv', types={'sku':'VARCHAR'})")

# --- writing
df.to_parquet("out.parquet", index=False, compression="zstd")
pdf.write_parquet("out.parquet", compression="zstd")
duckdb.sql("COPY (SELECT ...) TO 'out.parquet' (FORMAT parquet, COMPRESSION zstd)")

The leading-zero trap (ch22 CS2). A sku column of "00471" becomes 471 in pandas and Polars if the type is inferred and every value is numeric. DuckDB keeps it as VARCHAR. Declare the type at read time, every time, on any column that is an identifier rather than a quantity.

C.2 The Operations You Use Constantly

# ---------------------------------------------------- filter
df[df.status == "shipped"]
pdf.filter(pl.col("status") == "shipped")
duckdb.sql("SELECT * FROM pdf WHERE status = 'shipped'")

# ---------------------------------------------------- select / rename
df[["order_id", "net_cents"]].rename(columns={"net_cents": "revenue_cents"})
pdf.select(pl.col("order_id"), pl.col("net_cents").alias("revenue_cents"))

# ---------------------------------------------------- derive
df["revenue"] = df.qty * df.unit_price_cents
pdf = pdf.with_columns((pl.col("qty") * pl.col("unit_price_cents")).alias("revenue"))

# ---------------------------------------------------- group and aggregate
df.groupby("customer_id", as_index=False).agg(
    orders=("order_id", "nunique"), revenue=("revenue", "sum"))
pdf.group_by("customer_id").agg(
    pl.col("order_id").n_unique().alias("orders"),
    pl.col("revenue").sum())

# ---------------------------------------------------- join
pd.merge(orders, customers, on="customer_id", how="left", validate="m:1")
orders.join(customers, on="customer_id", how="left")

# ---------------------------------------------------- window
df["seq"] = df.sort_values(["customer_id", "placed_at", "order_id"]) \
              .groupby("customer_id").cumcount() + 1
pdf.with_columns(
    pl.col("order_id").rank("ordinal")
      .over("customer_id").alias("seq"))

validate="m:1" in pd.merge is the most under-used argument in pandas. It raises if the join is not the cardinality you claimed, which is the fan-out (ch6) caught at the point it happens rather than three aggregations later. Use it on every join.

C.3 pandas: The Things That Bite

SettingWithCopyWarning. It means an assignment may not have affected the object you think. Almost always a real bug.

sub = df[df.qty > 0]
sub["flag"] = True              # warning, and may not persist

sub = df.loc[df.qty > 0].copy() # explicit
sub["flag"] = True

Integer columns become floats when a NaN appears.

df["qty"].dtype                 # int64
df.loc[3, "qty"] = None
df["qty"].dtype                 # float64 -- and 2^53 is now your integer limit

df["qty"] = df["qty"].astype("Int64")   # nullable integer; capital I

Money must never be a float. Integer cents, and Int64 if it can be null.

object dtype is a Python object per value. A string column defaults to it and costs roughly 10x the memory of string[pyarrow]:

df = df.astype({"sku": "string[pyarrow]", "status": "category"})

inplace=True is not faster and mostly makes chaining impossible. Ignore it.

Chained indexing (df[a][b] = x) does not reliably assign. Use .loc[a, b] = x.

C.4 Polars: The Model

Everything is an expression, and expressions are composable and parallel.

lf = (pl.scan_parquet("orders/*.parquet")          # lazy
        .filter(pl.col("status") != "cancelled")
        .with_columns((pl.col("qty") * pl.col("unit_price_cents")).alias("revenue"))
        .group_by("customer_id")
        .agg(pl.col("revenue").sum(),
             pl.col("order_id").n_unique().alias("orders"))
        .sort("revenue", descending=True))

print(lf.explain())                                 # the plan, before running it
out = lf.collect()                                  # now it runs
out = lf.collect(streaming=True)                    # and out-of-core

Three habits:

scan_* rather than read_*. Lazy lets the optimizer push the filter into the file scan, which is the whole point.

.explain() before .collect() on anything large. It shows projection and predicate pushdown.

Expressions over apply. A Python apply drops to one row at a time and forfeits every advantage.

C.5 DuckDB: The One That Usually Wins

import duckdb
con = duckdb.connect("kestrel.duckdb")              # or ":memory:"

# it reads files directly -- no load step
con.sql("""
  SELECT customer_id, sum(qty * unit_price_cents)::BIGINT AS revenue_cents
    FROM 'bronze/orders/*.parquet'
   WHERE status <> 'cancelled'
   GROUP BY 1 ORDER BY 2 DESC LIMIT 10
""").show()

# it queries DataFrames in place, with zero copy
con.sql("SELECT * FROM df WHERE qty > 2")           # pandas df, by variable name
con.sql("SELECT * FROM pdf").pl()                   # -> Polars
con.sql("SELECT * FROM df").arrow()                 # -> Arrow

The ::BIGINT is not decoration. SUM(BIGINT) returns HUGEINT, which has no Parquet primitive and lands as a DOUBLE. Money silently becomes a float, and above 2^53 cents it starts losing them (ch22 §22.9).

Useful settings:

con.sql("SET memory_limit='8GB'")
con.sql("SET threads=8")
con.sql("SET preserve_insertion_order=false")       # faster for large aggregations
con.sql("SET enable_progress_bar=true")

Reading from S3/MinIO:

con.sql("INSTALL httpfs; LOAD httpfs;")
con.sql("SET s3_endpoint='localhost:9000'; SET s3_use_ssl=false; SET s3_url_style='path';")
con.sql("SET s3_access_key_id=getenv('MINIO_ROOT_USER')")
con.sql("SET s3_secret_access_key=getenv('MINIO_ROOT_PASSWORD')")
con.sql("SELECT count(*) FROM 's3://bronze/orders/*.parquet'")

C.6 PyArrow: The Layer Underneath

You use it deliberately for three things.

Controlling the Parquet you write:

import pyarrow as pa, pyarrow.parquet as pq

pq.write_table(
    table, "orders.parquet",
    compression="zstd",
    row_group_size=1_000_000,          # ch9: the unit of skipping
    use_dictionary=["status", "sku"],
    write_statistics=True,             # required for predicate pushdown
)

Reading a schema without reading data:

md = pq.ParquetFile("orders.parquet").metadata
print(md.num_rows, md.num_row_groups, md.row_group(0).column(0).statistics)

Partitioned datasets:

import pyarrow.dataset as ds
dataset = ds.dataset("bronze/orders/", format="parquet", partitioning="hive")
tbl = dataset.to_table(filter=ds.field("event_date") == "2026-11-27",
                       columns=["order_id", "net_cents"])

C.7 Converting Between Them

All four share Arrow, so conversion is usually free:

pdf = pl.from_pandas(df)
df  = pdf.to_pandas()
arrow_tbl = pdf.to_arrow()
pdf = pl.from_arrow(arrow_tbl)
df  = con.sql("...").df()      # DuckDB -> pandas
pdf = con.sql("...").pl()      # DuckDB -> Polars
tbl = con.sql("...").arrow()   # DuckDB -> Arrow
con.register("t", pdf)         # Polars/pandas -> queryable in DuckDB

to_pandas() is the one that copies, because pandas' memory layout is not Arrow's for several types. Prefer staying in Arrow or Polars when the data is large.

C.8 Measuring, Honestly

Chapter 22's finding: you cannot measure four engines in one process. RSS does not fall when a DataFrame is freed, so the first engine measured looks cheapest.

# one subprocess per engine, measured from outside
import subprocess, resource, sys

def measure(script):
    p = subprocess.run([sys.executable, script], capture_output=True)
    usage = resource.getrusage(resource.RUSAGE_CHILDREN)
    return usage.ru_maxrss / 1024        # MB on Linux; bytes/1024**2 on macOS

And measure the import. import pandas takes ~0.4 s and import pyspark several seconds; on a job that runs for two seconds, that is the measurement.

C.9 Choosing

data fits comfortably in RAM, and you need scikit-learn / statsmodels
    -> pandas

a transformation pipeline, data up to a few times RAM
    -> Polars, lazy, streaming

anything you would naturally write as SQL: joins, aggregations, windows
    -> DuckDB

writing files another system will read, or controlling the layout
    -> PyArrow

data genuinely exceeds one machine
    -> Spark (Appendix F), and check that it genuinely does first

Chapter 22's measurement is worth repeating on your own data, because the crossover depends on your columns, your cardinalities, and your machine — and it is an afternoon.