Columnar Thinking with polars

P12.data-quality-sql.03 · Audience: guest, it-ml, language-pro · Prerequisites: SQL for Analytics I — Select, Filter, Group

SQL for Analytics II — Joins & Windows Data Validation & Quality Checks

SQL describes what rows you want; polars is a dataframe library that lets you describe the same transformations in Python — and it is built on a different physical idea than the row-by-row loops most people picture. Data lives in columns, work is described as expressions, and — the part worth travelling for — nothing runs until you ask for the answer. That last property, lazy evaluation, lets polars see your whole query before executing a line of it, and optimise the plan the way a SQL engine does. This module is about the shape of thinking columnar tools reward; it does not re-teach idiomatic pandas, which lives in Python Craft (P4) — the two are complementary lenses on the same tables.

Step 1 / 4 — Rows on paper, columns in memory

Picture our orders table. You read it as rows — one order per line, all its fields together — because that is how a receipt or a spreadsheet reads. But an analytical engine usually stores it as columns: every order_id contiguous in memory, then every quantity, then every product_id. Same table, transposed in how the bytes are laid out.

ⓘ Concept: Row-oriented vs column-oriented storage

row store: fast whole-record read/write · column store: fast few-columns-over-many-rows scan

Why it matters — The layout decides what is cheap. Row storage keeps a whole record together, so fetching or writing one entire row is fast — the right choice for a transactional system inserting orders one at a time. Column storage keeps each field's values together, so scanning or aggregating one column reads a tight, contiguous block and skips every column you did not ask for — the right choice for analytics, which typically touches a few columns across millions of rows. Most 'why is this so slow?' surprises trace back to using a layout against its grain.

This is why the same SUM(quantity) that an order-entry database labours over, a columnar tool answers in a blink: it reads one contiguous run of numbers and ignores everything else. Analytics is overwhelmingly "a few columns, many rows," and columnar storage is the layout built for exactly that shape.

✅ Key takeaways

Row storage keeps whole records together (fast single-record read/write, good for transactions); column storage keeps each field's values contiguous (fast few-columns-over-many-rows scans, good for analytics). Layout against its grain is where slowness hides.
polars expresses work as expressions — pl.col('quantity') * pl.col('price') is a recipe for a whole column, composed into a pipeline, applied vectorised. No loops, no index fiddling: describe the transformation, not the iteration. Revenue per city is Paris = 40, Lyon = 30, same as the SQL.
Lazy evaluation records a query plan and runs nothing until .collect(). Because polars sees the whole plan first, it optimises — pushing a filter down so other cities' rows are never materialised — the same bargain a SQL planner makes.
Columnar + lazy wins big on scan-heavy analytics (few columns, many rows) and barely matters on tiny tables or single-record fetches. Diagnose the shape of the workload before reaching for the 'fast library'.
Idiomatic pandas and vectorisation craft live in P4; this module owns the columnar-at-scale mental model. Same tables, two complementary lenses — reach for whichever fits the work.

Next: P12.data-quality-sql.04 — Data Validation & Quality Checks

Going deeper (technical) — reading the query plan

Interview-grade notes for the technical (it-ml) track.

The plan is a real object you can inspect. A LazyFrame carries a logical plan; plan.explain() prints it, and plan.show_graph() renders it. Read it bottom-up (the scan is at the leaves, the sink at the root). Two plans matter: the logical plan (what you wrote) and the optimised physical plan (what will actually run). The gap between them is the optimiser's work, and reading both side by side is the fastest way to learn what polars rewrote.

Predicate pushdown is the headline optimisation. A .filter(pl.col("city") == "Paris") written after a scan is pushed into the scan, so rows failing the predicate are dropped as the file is read — never decoded, never allocated. On a Parquet file this compounds with the format's own row-group statistics: entire row groups whose min/max range excludes "Paris" are skipped without being read at all. In the explain() output you will see the predicate migrate down to the SCAN node — that migration is the pushdown.

Projection pushdown is its sibling: only the columns the plan actually references are read from disk. Select two columns out of fifty and the other forty-eight are never touched — the single biggest reason columnar formats fly on wide tables. Again, explain() shows the projected column set attached to the scan node.

Lazy vs eager, precisely. Eager (pl.DataFrame, .read_*) executes each call immediately and returns data; lazy (pl.LazyFrame, .scan_*) returns a plan and defers until .collect(). Prefer scan_parquet/scan_csv over read_* for large inputs precisely so pushdown can prevent the read; use eager for small interactive work where the optimiser has nothing to gain and you want the value now. The classic anti-pattern is .collect() in the middle of a pipeline — it materialises early and severs the plan, forfeiting every cross-step optimisation after that point. Collect once, at the end.

Streaming. collect(streaming=True) runs the plan in batches rather than loading the whole frame, letting a query process data larger than memory — the lazy plan is what makes this possible, because the engine already knows the full pipeline and can push batches through it end to end. Not every operation has a streaming implementation, so check the plan for a full-materialisation node if a larger-than-memory job unexpectedly blows up.

Ask the mentor about this module

Ask a question about this content. The mentor explains and grounds its answer in what you are studying; asking is recorded as a learning signal, not a grade.

Ctrl/Cmd + Enter to send

Try it yourself

A scratch console for this page's ideas — ungraded, nothing you run here is recorded.

Scratch console

A scratch console with the scientific stack (pandas, numpy, scikit-learn). Runs on the server — no network, resource-limited and measured.

Output appears here.

Where next?

Continue

Data Validation & Quality Checks →

Data Quality & SQL · P12

Later in Data Quality & SQL

Go up a level