Series · roadmap stage 2/13
The Python & SQL Series
Ten lessons in order, from the mental model to a capstone checklist. Every lesson is structured by us and grounded in a real, cited source from the registry — no invented commands.
10 of 10 lessons published
- Lesson 1 The relational mental model — sets, grain, and NULL
Before the first SELECT, build the model every later lesson uses: a query returns a set with a stated grain, a join changes that grain by its cardinality, and NULL never compares as you expect. You learn the first question every SQL review
- Lesson 2 First hands-on — a correct SELECT with a justified join
Stand up a local DuckDB through the DuckDB MCP server, inspect before you query with list_tables and describe_table, and write one SELECT with a join whose cardinality you can justify. You read CSV and Parquet straight into the engine, stat
- Lesson 3 Review SQL for fan-out joins
Follow the SQL Reviewer agent's four-step pass over a real query: read the diff and its context, establish the grain of every result set, apply the SQL Review skill's five rules while stopping at the first fan-out, and emit findings with fi
- Lesson 4 Window functions — keep the grain, rank the rows
A window function computes over a set of rows and hands the answer back to every row, so the result keeps its grain — the property that makes a keep-latest dedupe safe and a bare GROUP BY grain-changing. You find the duplicates first with o
- Lesson 5 Fix SQL lint with the SQLFluff core rule pack
Install the SQLFluff core rule pack, point it at your warehouse dialect, and lint a fixture that fails on purpose: indentation, keyword capitalisation, SELECT star, and unused CTEs. You fix each finding while the passing fixture stays green
- Lesson 6 Read an EXPLAIN plan and pick the one fix
Take the EXPLAIN output of a slow query and read it bottom-up, naming the expensive node before you touch anything. You compare estimated rows to actual rows to catch stale statistics, separate scan cost from compute time with the warehouse
- Lesson 7 Run the full SQL review pass on a PR
Run the SQL Reviewer agent's full pass over a pull request: read the diff in context, state the grain of every result set, then check the correctness bugs the SQL Review skill targets — fan-out joins, NULL-unsafe NOT IN and = predicates — a
- Lesson 8 Production gotchas — NULL traps, implicit casts, silent drift
The bugs that survive testing compare wrong instead of failing loud: NOT IN over a nullable subquery, a comparison across types left to an implicit cast, and a schema change that silently turns a column to NULLs. You walk the review rules t
- Lesson 9 Incident — the numbers doubled and no alarm fired
Row counts are inflated and metrics double-counted after a retry, a fan-out join or a backfill — the incident signature of a silent fan-out. You work the duplicate-records runbook end to end: count duplicates per key with one query, correla
- Lesson 10 Capstone — the SQL correctness checklist
Close the series by turning nine lessons into one standing checklist: a grain statement for every SELECT, a cardinality justification for every join, NULL-safe anti-joins, explicit casts, lint in CI, and one decision rule for slow queries.