Lesson 07 SQL for Data Engineers
Theme:

SQL for Data Engineers

Lesson 07 · Data Engineering with Python · ~15 min read

1. Why every data engineer needs more SQL than they think

The most common path into data engineering starts with Python. A developer learns pandas, picks up a pipeline task, and writes the whole thing as a chain of DataFrame operations. It works beautifully on a 1 GB sample. Then production hits 100 GB — and the laptop is on fire.

The fix is almost always the same: push the work to the warehouse. Write SQL that runs inside DuckDB, Snowflake, or BigQuery; return only the small result. SQL is the lingua franca of the analytical world: every warehouse speaks it, every analyst reads it, every BI tool consumes it. The pandas-only engineer is locked out of half the stack — and worse, often re-implements what SQL does natively, badly and slowly.

Most pandas practitioners already know the basics: SELECT ... FROM ... WHERE ... GROUP BY ... JOIN .... What they systematically miss are the features that turn SQL from a query language into an analytical powerhouse: CTEs, window functions, QUALIFY, GROUPING SETS, set operators, and the critical distinction between JOIN, IN, and EXISTS. This lesson closes that gap.

2. CTEs — name your steps

A Common Table Expression (CTE) is a named subquery declared with WITH name AS (...) at the top of a query. You can chain multiple CTEs: 

WITH a AS (...),
     b AS (...),
     c AS (... FROM a JOIN b ...)
SELECT * FROM c;

The result reads top-down like a Python function call stack. Three reasons to prefer CTEs over deeply nested subqueries:

  1. Readability. A 4-level nested subquery is unreadable. The same logic written as four named CTEs is obvious to any reviewer.
  2. Reusability within the query. A single CTE can be referenced multiple times in subsequent CTEs or the final SELECT — unlike a subquery inlined in one place.
  3. Debuggability. Replace the final SELECT with SELECT * FROM step_3 LIMIT 100 to inspect any intermediate step without rewriting the whole query.

One caveat: CTEs do not improve performance over equivalent inline subqueries. Modern optimisers treat them identically (the old "CTE is an optimisation fence" rule has been gradually relaxed across engines — check EXPLAIN if you care). But the readability win alone justifies the keyword.

3. Window functions — the headline feature

A window function adds columns of context to each row, computed across a window of related rows — without collapsing the row like GROUP BY does. The syntax is: 

<function>() OVER (
  PARTITION BY <cols>
  ORDER BY <cols>
  [ROWS|RANGE BETWEEN <frame_start> AND <frame_end>]
)

Three families cover almost every use case:

The mental model: GROUP BY collapses rows into one summary row per group; PARTITION BY keeps every row but attaches the summary alongside. They solve different problems. In pandas terms, GROUP BY + agg() is like groupby().agg(); PARTITION BY + window is like groupby().transform().

The window frame clause is the most underused feature. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives a 7-row rolling window. Without an explicit frame, the default for ordered aggregate windows is "all rows from the start of the partition to the current row" (running total) — not a rolling window. Always specify the frame explicitly when you want rolling semantics.

4. QUALIFY — the post-window filter

Window functions are evaluated after WHERE and GROUP BY, so you cannot filter on a window result in WHERE. The traditional workaround is to wrap the windowed query in a CTE and filter in the outer SELECT: 

WITH numbered AS (
  SELECT actor, type, created_at,
         ROW_NUMBER() OVER (PARTITION BY actor ORDER BY created_at) AS rn
  FROM events
)
SELECT * FROM numbered WHERE rn = 1;

QUALIFY is the post-window equivalent of WHERE — it filters after windows are evaluated, collapsing the two-step pattern to one: 

SELECT actor, type, created_at,
       ROW_NUMBER() OVER (PARTITION BY actor ORDER BY created_at) AS rn
FROM events
QUALIFY rn = 1;

QUALIFY is supported by DuckDB, Snowflake, BigQuery, and Databricks. It is not supported by Postgres or MySQL — use the CTE pattern on those engines. The course uses DuckDB, so QUALIFY is available in every lab.

5. GROUPING SETS, ROLLUP, CUBE — multi-level aggregation in one pass

Dashboards often need totals at multiple levels simultaneously: per-day, per-type, per-day-per-type, and a grand total. The naive approach writes four queries and UNION ALLs them. GROUPING SETS does the same in one declaration: 

SELECT
  date_trunc('day', created_at) AS day,
  type,
  COUNT(*) AS n
FROM events
GROUP BY GROUPING SETS (
  (day, type),   -- detail rows
  (day),         -- per-day subtotals
  (type),        -- per-type subtotals
  ()             -- grand total
);

ROLLUP(a, b, c) is the hierarchical shorthand: GROUPING SETS((a, b, c), (a, b), (a), ()). CUBE(a, b, c) generates the full power set: every subset of the grouping columns. Performance is roughly equivalent to running the equivalent UNIONs, but the planner can share a single table scan.

Pair with GROUPING(col), which returns 1 when col was rolled up and 0 otherwise — the correct way to tag rows for display logic instead of checking IS NULL (which fails when the real data contains NULLs).

6. JOIN, IN, EXISTS — the row-multiplication trap

A JOIN multiplies rows. If actor alice made 100 commits, actors JOIN commits produces 100 rows for alice. If you only want to filter — "actors who have made at least one commit" — the join is the wrong tool: 

-- WRONG: produces 100 rows for alice
SELECT a.* FROM actors a
JOIN commits c ON c.actor_id = a.id;

-- RIGHT: one row for alice, filtering by existence
SELECT a.* FROM actors a
WHERE EXISTS (SELECT 1 FROM commits c WHERE c.actor_id = a.id);

-- ALSO CORRECT: equivalent with IN
SELECT a.* FROM actors a
WHERE a.id IN (SELECT actor_id FROM commits);

EXISTS and IN differ in one critical edge case: NULL handling. NOT IN (SELECT col FROM ...) returns zero rows if any value in the subquery is NULL, because x NOT IN (1, 2, NULL) evaluates to UNKNOWN, not TRUE. NOT EXISTS has no such trap. Always prefer NOT EXISTS over NOT IN when the subquery might return NULLs.

Common pitfalls

7. EXPLAIN — read the plan before optimising

Every SQL engine ships an EXPLAIN (or EXPLAIN ANALYZE for runtime statistics). It returns the query plan — the tree of physical operators the planner selected: which tables to scan, in what order to join them, where to aggregate, whether to use a hash or a sort.

Three things to look for:

  1. Sanity-check join order. If a small table is on the outer side of a hash join, the planner thinks it's big — usually a statistics problem. Run ANALYZE to refresh table statistics.
  2. Confirm partition pruning. On a Parquet-partitioned table, EXPLAIN should show the planner reading only the matching partitions. If it reads all of them, your WHERE clause isn't predicate-pushdown-friendly.
  3. Spot accidental cross joins. A missing ON clause produces a cartesian product — EXPLAIN shows it as a nested-loop join with no condition. A billion-row red flag.

The first instinct for a slow query should not be "add an index" — it should be "read EXPLAIN." Indexes help OLTP point lookups; analytical queries scale by partition pruning, columnar pruning, and join order. EXPLAIN tells you which of those is failing.

References