Module 2: Clean and trust the data · Lesson 2.2 · about 35 min
Transforming with Polars and DuckDB
Write lazy Polars pipelines and DuckDB SQL straight on files, save partitioned Parquet, and learn why reading by column makes queries hundreds of times lighter.
Module 2 · about 35 minutes · read first: 2.1. You’ll be able to write a lazy Polars pipeline, query CSV and Parquet files directly with DuckDB, write Parquet split into folders, and choose between pandas, Polars and DuckDB for a job.
1. Why this matters
pandas taught a generation to analyse data, but for pipelines it is slow and hungry for memory. Polars (written in Rust, multi-threaded, lazy) and DuckDB (an analytical SQL database that runs inside your Python process, with no server) are now the standard open-source tools for one-machine ETL. With them a laptop handles hundreds of millions of rows, and many medium-sized companies never need a cluster at all. When yours does, Data at Scale picks up where this lesson stops; for pandas itself, see the pandas course.
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("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
df = daily.collect() # only now is anything read
An expression (pl.col("qty") * pl.col("price")) describes a calculation on whole columns. Lazy means scan_csv reads nothing yet: Polars collects the steps into a plan, improves it, and runs it on collect(). It reads only the columns you use (projection pushdown), applies filters while reading (predicate pushdown), and uses every core. Polars renames things between versions, so pin its version and check its docs if a name is refused.
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)")
all_varchar=true reads everything as text first, so a bad value cannot crash the load; try_strptime then turns bad time stamps into NULL instead of an error. partition_by writes one folder per restaurant (restaurant_id=Q204/…), so a query for one restaurant opens one folder.
DuckDB and Polars share data without copying through Apache Arrow, a standard in-memory column format: con.sql("…").pl() returns a Polars frame, and con.sql("select * from df") queries a Polars frame called df in place.
4. Why Parquet
Parquet is a file format that stores data by column, compressed, in chunks called row groups, each with the smallest and largest value of every column written in its header. A query for sum(price) where date = '2026-09-14' reads two columns and skips every row group whose date range cannot contain the 14th.
ts and price: 12 of 48 blocks.Maths and principle
Principle: column storage cuts the bytes read in proportion to the share of columns and row groups a query needs.
Equation:
where is the number of rows, the columns the query uses, the compressed width of column , and the share of row groups not skipped. A row store reads .
Analogy: reading one column of a printed spreadsheet instead of photocopying every page. Where it breaks: fetching a few whole rows by id (select * where id = 42) suits row stores, which is why the databases behind tills and booking systems store rows.
Try it: 20 equal columns; the query uses 3; the date filter keeps 1 row group in 30. What share of the bytes does it read, compared with a CSV? (3/20 × 1/30 = 0.5%, before compression helps further.)
5. Which tool when
| Job | Pick | Why |
|---|---|---|
| Reading messy files, odd row-by-row logic | Polars (pandas for unusual formats) | Expressions, with an escape hatch to plain Python |
| Joins, totals, window functions | DuckDB SQL | Readable, and it moves to the warehouse later |
| Exploring in a notebook with charts | pandas or Polars | The plotting ecosystem |
| Data bigger than memory on one machine | DuckDB, or Polars streaming | They can work through data in pieces |
| Many terabytes, many machines | Spark or Databricks, or the warehouse itself | Then the extra machinery pays for itself |
- Lazy evaluation
- Writing down the steps first and running them all at once, so the engine can plan the cheapest way.
- Pushdown
- Doing the filter or column choice while reading the file, so unneeded data is never loaded.
- Parquet
- A compressed file format that stores each column separately, in row groups with min/max statistics.
- Partitioning
- Splitting files into folders by a column (date, restaurant) so a query opens only the folders it needs.
- Physical meaning
- A useful rule: business logic in SQL, plumbing in Python. Joins and metrics written in SQL move to Azure SQL and dbt in Modules 4 and 5 nearly unchanged; the same logic in Polars would need rewriting. Keep Polars for parsing, typing and shaping raw files. And storing staging as Parquet often turns a nightly job from minutes into seconds on the same laptop, and later cuts the bill on a warehouse that charges per byte scanned.
“Big data needs Spark.” Most company datasets fit on one modern machine, and DuckDB or Polars process them faster than a small cluster, with far less setup. Reach for Spark when the data or the team truly outgrows one machine, not before.
6. Try it
- Load all the till files with pandas, lazy Polars and DuckDB. Time each with
time.perf_counter()and note peak memory (/usr/bin/time -von Linux, Task Manager on Windows). Put the table in the README. - Build
stg_qsr_posin Polars: parse time stamps (bad ones become null), addline_total,src_fileandloaded_at. - Write it to Parquet split by date folder (calendar date for now; Lesson 2.3 fixes the business day).
- In DuckDB, query the Parquet for revenue by brand and channel over one week. Run
explain analyzeand check that only the needed date folders are read. - Generate 120 days (
--days 120) and repeat the timings. Which tool grew best on your machine?
Commit: feat(clean): staging POS in Polars, Parquet partitions, benchmark table.
What to check: the row count of stg_qsr_pos plus the rows with bad time stamps equals the total rows in the CSVs. Nothing should vanish between the two.
7. Practice
- You add
.filter(pl.col("restaurant_id") == "Q204")afterscan_csv. Does Polars read the other restaurants’ rows into memory? - Why read every column as text first in DuckDB?
- Your Parquet folder is split by restaurant, but most queries filter by date. What would you change?
Answers
- It still has to scan the CSV text (CSV has no statistics), but it drops non-matching rows while reading instead of building them all in memory first. On Parquet split by restaurant, it would skip the other folders entirely.
- So one bad value (a date written
00/00/0000) cannot stop the whole load. You convert withtry_functions and catch the failures as NULLs to quarantine. - Split by business date instead (or by both), so date filters skip whole folders. Choose the split from how the data is queried.
New words: Polars, DuckDB, expression, lazy evaluation, projection and predicate pushdown, Apache Arrow, Parquet, row group, partitioning.
- Polars is lazy: it plans the whole job, reads only what it needs and runs in parallel on
collect(). - DuckDB runs SQL straight on CSV and Parquet files, and shares data with Polars through Arrow without copying.
- Parquet stores columns separately with min/max statistics, so queries read a tiny share of the bytes.
- Write business logic in SQL so it moves to the warehouse; keep Python for parsing and plumbing.
Check yourself
Sources
- Polars user guide: lazy API and query optimisation https://docs.pola.rs/
- DuckDB documentation: reading CSV and Parquet files, partitioned writes https://duckdb.org/docs/
- Apache Parquet documentation: file format and row groups https://parquet.apache.org/docs/
- Abadi, D., Madden, S. and Hachem, N. (2008), Column-Stores vs. Row-Stores: How Different Are They Really?, Proceedings of ACM SIGMOD 2008, 967-980 https://doi.org/10.1145/1376616.1376712
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.