Chapter 18 — Key Takeaways (SQL Transformations)

The page to keep open while writing a model. Everything here is engine-portable.

The argument

SQL is a declarative language and most people write procedural code in it. A loop, a correlated subquery, and an offset self-join are the three dialects of that mistake, and each has a set-based replacement that is shorter, faster, and easier to read.

The three tells, and their replacements:

loops= in the plan is what names the problem. actual time is per loop — multiply them. 1.9M loops × 0.31 ms is ten minutes hiding behind a node that reads as trivial.

Window functions

GROUP BY collapses. A window computes across related rows and keeps every one. That clause is the entire difference, and it is why a window replaces a correlated subquery.

SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC, cdc_lsn DESC) AS rn

ROW_NUMBER, never RANK, for deduplication. RANK gives tied rows the same number, so WHERE rank = 1 keeps all of them — the dedup that produces duplicates.

WINDOW w AS (...) names a specification once and reuses it. Standard, widely supported, almost never used. Its main value is showing a reviewer that the partitionings are shared.

The frame clause — the half of window bugs

ORDER BY present, no frame  →  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
no ORDER BY                 →  the whole partition

⚠️ Adding an ORDER BY silently converts SUM from a total into a running total.

⚠️ RANGE includes peers. Two orders on the same date show the same running total — both get the value that includes both. ROWS is what you almost always meant.

⚠️ LAST_VALUE returns the last row of the current peer group under the default frame — the current row itself when the ordering is unique, which is how the surprise is usually reported. Not a bug: the frame ends at the current row, and under RANGE that means the last peer. Fix with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use FIRST_VALUE with the ordering reversed.

CTEs and recursion

"CTEs are optimization barriers" was true in PostgreSQL ≤ 11 and is repeated everywhere as a general fact. Modern engines inline and decide. Use MATERIALIZED / NOT MATERIALIZED when you need to force it.

Recursive CTEs: UNION ALL, not UNION — UNION deduplicates every iteration, which is slow and masks a cycle.

A depth guard converts an infinite loop into a wrong answer you can detect. Strictly better than running forever, and the assertion on MAX(depth) is what makes it useful.

Prefer a flattened dimension when depth is bounded and known. Recursion earns its complexity only when depth is genuinely variable.

Deduplication — four patterns, one correct

⚠️ MAX(quantity) is the maximum across versions, not the quantity from the latest version. The columns are not independent, so you get a Frankenstein row assembled from several source rows.

The tiebreaker is not optional. ORDER BY updated_at DESC alone is non-deterministic when two rows share a timestamp — and re-running produces different output, which is the hardest class of bug to diagnose because it does not reproduce.

🔎 Assert in both directions. A dedup fails two ways:

-- 1. rows == distinct keys   (nothing duplicated)
-- 2. distinct keys in == distinct keys out   (nothing DROPPED)

Checking only the first passes a query that silently lost half your keys.

Gaps and islands

Flag the breaks, then take a running sum of the flag. The running sum is constant within an island, so it is the group identifier.

SUM(is_break) OVER (PARTITION BY customer_id ORDER BY d) AS island_id

date - ROW_NUMBER() is a special case of this, and only works for dense integer/date sequences. Learn the general form; it covers streaks, sessions, contiguous ranges, and state runs.

Sessionization

CASE WHEN LAG(ts) OVER w IS NULL
       OR ts - LAG(ts) OVER w > INTERVAL '30 minutes' THEN 1 ELSE 0 END

⚠️ LAG returns NULL at the first row of a partition, and the SQL cannot tell "no previous row exists" from "the previous row is outside my filter." At a batch boundary that manufactures a new session out of the tail of a real one.

The artifact: an excess of short, single-page, zero-duration sessions at every window start — which looks exactly like bot traffic, and gets explained rather than investigated.

The fix — four lines:

-- read back
WHERE event_ts >= :start - INTERVAL '2 hours' AND event_ts < :end
-- ...sessionize...
-- emit only sessions that START in the window; without this the overlap
-- replaces phantoms with DUPLICATES
WHERE session_start >= :start AND session_start < :end

Size the overlap at a multiple of the gap and assert the relationship in code — the gap is configuration, and a comment is not a control.

Do not copy an overlap width across cadences. One day is 3% of a monthly batch and 100% of a daily one.

Overlap repairs bounded context. When the required context is unbounded — a customer's first order ever — no overlap works, and the window has to go.

Reading a transformation's plan

Look for sorts you did not ask for, before anything else.

Each distinct window specification is a sort. Four partitionings over 1.9M rows is four sorts, and here, 852 MB of spill. Consolidating them is frequently a large, free win — and it is invisible unless you read the plan.

Then: loops= in the millions · Sort Method: external merge Disk: · estimated vs. actual rows.

Verification

Compare with a full-row EXCEPT in both directions, never a row count — a row count passes when two rows have swapped values.

Expect it to find real discrepancies. Rewriting Kestrel's cohort model surfaced 41 customers where the old code returned 0 and the new returned NULL; the old behaviour had been accidentally wrong for eighteen months and something downstream depended on it.

A measurement with only one implementation has no error bar. Compute the important aggregates twice by different paths and alert on the divergence, not on either value. Kestrel's shadow session model costs $62.40 a year and would have saved fourteen months of a deleted peak hour.

The trap this chapter is really about

Neither case study involved bad SQL. One model accumulated six reasonable additions until it was 41 minutes; the other had a defect that was small, regular, and had a plausible explanation already waiting for it.

Audit for the shape, not the sighting. The cohort audit found seven more models to rewrite; the window audit found two more boundary bugs — and one of them needed a different fix.