Week 2 · Clean and trust the data › Day 9

Day 9. Transforming with Polars and DuckDB

1. Why this matters

pandas taught a generation to analyse data, but for pipelines it's slow and memory-hungry. Polars (Rust, multi-threaded, lazy) and DuckDB (an in-process analytical SQL database) are now the standard open-source tools for single-machine ETL. With them, a laptop handles hundreds of millions of rows, and medium-sized companies often don't need Spark at all.

2. Polars: expressions and laziness

import polars as pl

lf = (
    pl.scan_csv("data/landing/shared_drive/qsr/*/pos_*.csv", include_file_paths="src_file",
                schema_overrides={"order_ts": pl.Utf8}, encoding="utf8-lossy")
      .with_columns(
          pl.col("order_ts").str.strptime(pl.Datetime, "%Y-%m-%d %H:%M:%S", strict=False),
          (pl.col("qty") * pl.col("price")).alias("line_total"),
      )
      .filter(pl.col("order_ts").is_not_null())
)
daily = (lf.group_by(pl.col("restaurant_id"), pl.col("order_ts").dt.date().alias("cal_date"))
           .agg(pl.len().alias("lines"), pl.col("line_total").sum().alias("revenue")))
print(daily.explain())        # the optimised plan: predicate and projection pushdown
df = daily.collect()

Lazy means Polars builds a plan and optimises it before running: it reads only the columns you use, pushes filters into the scan, and runs in parallel.

3. DuckDB: SQL on files

import duckdb
con = duckdb.connect("data/warehouse.duckdb")
con.sql("""
  create or replace table stg_pos as
  select * replace (try_strptime(order_ts, '%Y-%m-%d %H:%M:%S') as order_ts)
  from read_csv('data/landing/shared_drive/qsr/*/pos_*.csv', union_by_name=true, filename=true, all_varchar=true)
""")
con.sql("copy stg_pos to 'data/staging/qsr_pos' (format parquet, partition_by (restaurant_id), overwrite_or_ignore)")

DuckDB and Polars share data with zero copy through Apache Arrow: con.sql("…").pl() returns a Polars frame; con.sql("select * from df") queries a Polars frame in place.

4. Why Parquet

Parquet stores data by column, compressed, with min/max statistics per row group. A query for sum(revenue) where cal_date = '2026-09-14' reads one column and skips row groups whose date range doesn't include that day.

5. Which tool when