Chapter 18: SQL Transformations

"The difference between a four-second query and a forty-minute one is almost never the database. It is whether you asked for a set or for a loop."

Overview

SQL is the highest-return skill in this book. It is the only thing here that will still be the same in twenty years, it transfers across every engine in Part II, and it is what you will write most days.

This chapter assumes you can already write SELECT, JOIN, GROUP BY, and a subquery — the Prerequisites page's self-check — and covers what a data engineer needs beyond that. Which is a specific list: window functions, because they replace an entire category of self-joins and correlated subqueries; CTEs, for readability, with a correction to a widely-believed myth about them; recursion, for hierarchies; deduplication, which you will write more often than anything else in this chapter; and gaps and islands, which is the shape behind sessionization and which almost nobody recognizes until they have seen it named.

The chapter's organizing idea is set-based thinking: the discipline of describing what you want rather than how to compute it, and of doing it in one statement rather than a loop. That is what makes the four-second-versus-forty-minute difference, and it is a habit rather than a feature.

One correction to make early, because it wastes people's time. A CTE is not an optimization barrier in a modern engine. That was true in PostgreSQL before version 12 and it is stated as fact in a great deal of writing that has not been updated. §18.4 covers what is actually true now.

In this chapter, you will learn to:

• Explain set-based thinking and rewrite a loop as a statement.
• Use window functions for ranking, offsetting, and running aggregates.
• Write a frame clause deliberately, and know what the default one does.
• Use CTEs for readability, without the myth.
• Write a recursive CTE with a termination guard.
• Deduplicate on a declared grain, choosing among four patterns.
• Solve gaps and islands, including sessionization.
• Read a transformation's query plan.

Who needs this chapter: everyone. It is on the Quick Start path and it is the single most transferable chapter in the book.

18.1 Set-Based Thinking

The habit that matters more than any syntax.

Procedural thinking: for each order, look up the customer, compute the total, write a row. Set-based thinking: describe the relationship between the order set and the result set, once.

# Procedural. Correct, and it is 780,000 round trips.
for order in fetch_orders(date):
    customer = fetch_customer(order.customer_id)     # 1 query per order
    lines = fetch_lines(order.order_id)              # 1 more
    total = sum(l.qty * l.price for l in lines)
    insert_fact(order.id, customer.region, total)

-- Set-based. One statement. The engine chooses the join strategy,
-- parallelizes, and streams.
INSERT INTO gold.fct_order (order_id, region, net_revenue_cents)
SELECT o.order_id,
       c.region,
       SUM(oi.quantity * oi.unit_price_cents - oi.discount_cents)
  FROM silver.orders o
  JOIN silver.customers c USING (customer_id)
  JOIN silver.order_items oi USING (order_id)
 WHERE o.order_date = :dt
 GROUP BY 1, 2;

The difference is not stylistic. The loop makes 1.5 million network round trips at, say, 0.4 ms each — ten minutes of pure latency before any work. The statement makes one.

The three shapes that give it away

You are thinking procedurally if you write any of these:

A cursor or a Python loop over query results, issuing more queries. Almost always replaceable by a join.

A correlated subquery in the SELECT list. Frequently replaceable by a window function (§18.2), and the rewrite is usually an order of magnitude faster:

-- Correlated: the subquery is conceptually evaluated per row.
SELECT o.order_id,
       (SELECT COUNT(*) FROM orders o2
         WHERE o2.customer_id = o.customer_id) AS customer_order_count
  FROM orders o;

-- Windowed: one pass.
SELECT order_id,
       COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count
  FROM orders;

A self-join to compare a row with its neighbor. LAG and LEAD (§18.2).

📐 Design Decision — When procedural is right

Set-based is the default and it is not universal. Three cases where a loop is correct:

An operation that must be chunked, to bound transaction size or source load — Chapter 7 §7.7's extract. The loop is over chunks, and each chunk is set-based inside.

Genuinely sequential logic where each step depends on the previous result in a way SQL cannot express — some financial allocations, some path-dependent calculations. Rare, and worth being suspicious of: most "inherently sequential" problems turn out to be window functions or recursive CTEs.

Calling an external service per row. An enrichment API, an ML inference endpoint. You are bounded by the service, not the database, and the answer is batching rather than SQL.

What choosing procedural costs, when it is wrong: the round trips, and — less obviously — you have moved the logic out of the database and out of SQL, so it is no longer readable by analysts, no longer testable with dbt, and no longer optimizable by the engine.

18.2 Window Functions

The single largest addition to your SQL vocabulary, and the one that replaces the most bad code.

A window function computes across a set of rows related to the current row, without collapsing them. That last clause is the whole difference from GROUP BY.

SELECT customer_id, order_date, net_revenue_cents,
       -- aggregate WITHOUT collapsing
       SUM(net_revenue_cents) OVER (PARTITION BY customer_id) AS customer_total,
       -- rank within the partition
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS nth_order,
       -- reach into another row
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
       -- running total
       SUM(net_revenue_cents) OVER (PARTITION BY customer_id
                                    ORDER BY order_date) AS running_total
  FROM gold.fct_order;

The functions you will actually use

ROW_NUMBER versus RANK matters more than it looks. For deduplication (§18.7) you must use ROW_NUMBER, because RANK gives tied rows the same number and WHERE rank = 1 then keeps all of them — which is the exact bug that produces duplicates in a deduplication step.

QUALIFY

Filtering on a window function requires a subquery, because WHERE runs before windows are computed:

-- The portable form.
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC) rn
    FROM bronze.orders_cdc
) WHERE rn = 1;

QUALIFY does it directly, and it is one of the genuinely nice additions of the last decade:

SELECT * FROM bronze.orders_cdc
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC) = 1;

🧭 Availability: Snowflake, BigQuery, DuckDB, Databricks, and Teradata support it. PostgreSQL does not (as of 17), so portable code uses the subquery form. This book uses QUALIFY where the engine allows and says so.

18.3 The Frame Clause

The part people skip, and the source of the most surprising window-function bug.

A frame defines which rows within the partition the function sees. And there is a default, which is not the one people expect.

SUM(x) OVER (PARTITION BY k ORDER BY d)
-- means:
SUM(x) OVER (PARTITION BY k ORDER BY d
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

With ORDER BY, the default frame is a running aggregate, not a partition-wide one. Without ORDER BY, the default is the whole partition. So adding an ORDER BY to a window silently changes SUM from a total into a running total.

ROWS versus RANGE — the bug

ROWS  BETWEEN 2 PRECEDING AND CURRENT ROW    -- 3 physical rows
RANGE BETWEEN 2 PRECEDING AND CURRENT ROW    -- all rows whose ORDER BY value
                                             -- is within 2 of the current one

RANGE includes all peers — rows with the same ORDER BY value. So with the default RANGE ... CURRENT ROW:

-- Two orders on the same date for one customer.
SELECT order_date, net_revenue_cents,
       SUM(net_revenue_cents) OVER (PARTITION BY customer_id ORDER BY order_date)
  FROM fct_order;

 order_date  | net_revenue | running_total
-------------+-------------+---------------
 2025-11-01  |        4995 |          4995
 2025-11-03  |        2995 |          7990
 2025-11-03  |        1295 |          9285   ← BOTH rows show 9285
 2025-11-07  |        3495 |         12780

Both rows on 2025-11-03 show the same running total, because RANGE treats them as peers and includes both in each one's frame. If you wanted a per-row running total you needed ROWS:

SUM(net_revenue_cents) OVER (PARTITION BY customer_id ORDER BY order_date
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

⚠️ Failure Mode — LAST_VALUE returns the current row

The most reported window-function surprise, and it is the default frame again.

sql SELECT order_id, LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY changed_at) FROM order_status_history;

This returns the current row's status, not the last one. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so "last value in the frame" is the current row.

Precisely: under RANGE, CURRENT ROW means the last peer of the current row — the same peer rule that produced the running-total surprise above. So LAST_VALUE returns the last row of the current peer group, which is the current row itself whenever the ordering is unique. Ordering by changed_at on a status history, it is unique, and you get the current row. Order by something with ties and you get a third answer: the last of the tied group, which is neither the current row nor the partition's last.

Verify it rather than trusting either of us: code/window_lab.py --demo frame prints all four columns side by side against a fixture with a deliberate tie.

Three fixes:

```sql -- 1. State the frame explicitly. LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY changed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- 2. Use FIRST_VALUE with a reversed sort. Usually clearer. FIRST_VALUE(status) OVER (PARTITION BY order_id ORDER BY changed_at DESC)

-- 3. ROW_NUMBER and filter. Clearest of all, and it is the deduplication -- pattern anyway. QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY changed_at DESC) = 1 ```

The general rule: if a window function's answer surprises you, write the frame out explicitly. Roughly half of window-function bugs are the default frame doing exactly what it is documented to do.

18.4 CTEs, and the Myth

A common table expression names a subquery. Its value is readability, and readability in a transformation is worth more than it sounds — a 200-line model with named steps is reviewable and the same logic as nested subqueries is not.

WITH paid_orders AS (
    SELECT * FROM silver.orders WHERE status NOT IN ('pending', 'cancelled')
),
order_totals AS (
    SELECT order_id,
           SUM(quantity * unit_price_cents - discount_cents) AS net_cents
      FROM silver.order_items
     GROUP BY 1
),
with_customer AS (
    SELECT o.order_id, o.order_date, c.region, t.net_cents
      FROM paid_orders o
      JOIN order_totals t USING (order_id)
      JOIN silver.customers c USING (customer_id)
)
SELECT region, order_date, SUM(net_cents) AS revenue_cents
  FROM with_customer
 GROUP BY 1, 2;

The myth

"CTEs are optimization barriers." This was true in PostgreSQL before version 12, where a CTE was always materialized and predicates could not be pushed into it. It is widely repeated as a general fact about SQL and it is not one.

What is true today:

So write CTEs for readability, and reach for the materialization hint only when you have measured a problem. The one case where forcing materialization genuinely helps is a CTE referenced several times whose computation is expensive — otherwise the engine may compute it repeatedly.

18.5 Recursive CTEs

For hierarchies and graphs: a category tree, a management chain, a bill of materials, a dependency graph.

-- Kestrel's category hierarchy: Outdoor > Jackets, Workwear > Boots, ...
WITH RECURSIVE category_tree AS (
    -- ANCHOR: the roots
    SELECT category_id, parent_category_id, name,
           name AS path,
           0 AS depth
      FROM silver.categories
     WHERE parent_category_id IS NULL

    UNION ALL

    -- RECURSIVE: children of what we already have
    SELECT c.category_id, c.parent_category_id, c.name,
           t.path || ' > ' || c.name,
           t.depth + 1
      FROM silver.categories c
      JOIN category_tree t ON c.parent_category_id = t.category_id
     WHERE t.depth < 10        -- ← the termination guard. Not optional.
)
SELECT * FROM category_tree ORDER BY path;

Three things are load-bearing.

UNION ALL, not UNION. UNION deduplicates on every iteration, which is slow and which masks a cycle rather than surfacing it.

The depth guard. A cycle in the data — category A's parent is B, B's parent is A — makes this run forever. Real hierarchies contain cycles more often than you expect, usually from a data-entry error or a botched migration. A depth limit turns an infinite loop into a wrong answer you can detect, which is strictly better.

A cycle detector, if you can afford it. Carry the path and stop when a node repeats:

      JOIN category_tree t ON c.parent_category_id = t.category_id
     WHERE t.depth < 10
       AND POSITION(c.name IN t.path) = 0    -- crude; a path array is better

Where recursion is the wrong tool: if your hierarchy has a bounded, known depth — three levels of category, always — a flattened dimension with level_1, level_2, level_3 columns is simpler, faster, and easier for analysts. Recursion earns its complexity when depth is genuinely variable.

18.6 Pivoting and Unpivoting

Pivot: rows to columns. Unpivot: columns to rows.

Pivot, portably

SELECT category_name,
       SUM(CASE WHEN channel = 'web'     THEN net_cents ELSE 0 END) AS web,
       SUM(CASE WHEN channel = 'ios'     THEN net_cents ELSE 0 END) AS ios,
       SUM(CASE WHEN channel = 'android' THEN net_cents ELSE 0 END) AS android,
       SUM(CASE WHEN channel = 'phone'   THEN net_cents ELSE 0 END) AS phone
  FROM gold.fct_order_item f
  JOIN gold.dim_product p USING (product_key)
 GROUP BY 1;

Several engines have PIVOT syntax, which is shorter and less portable. The CASE form works everywhere and is what dbt macros generate.

The constraint that matters: you must know the columns in advance. SQL returns a fixed set of columns, so a dynamic pivot requires generating the SQL — which dbt's dbt_utils.pivot does by querying the distinct values first.

And that is a schema change on every run. A new channel appears and the table gains a column, silently. Prefer the long format in gold and pivot in the BI layer unless a consumer genuinely requires wide.

Unpivot

-- Wide to long, portably: a UNION ALL per column.
SELECT category_name, 'web' AS channel, web AS net_cents FROM wide
UNION ALL SELECT category_name, 'ios', ios FROM wide
UNION ALL SELECT category_name, 'android', android FROM wide;

Verbose, and it is what you do when a source arrives wide — a spreadsheet export with a column per month, which happens constantly.

18.7 Deduplication

You will write this more often than anything else in this chapter, and there are four patterns.

1. ROW_NUMBER — the general answer

-- Keep the latest version of each order line.
SELECT * FROM bronze.order_items
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_item_id
                           ORDER BY _ingested_at DESC, _offset DESC) = 1;

The tiebreaker is not optional. ORDER BY _ingested_at DESC alone is non-deterministic when two rows share a timestamp — and a non-deterministic deduplication produces different results on re-runs, which breaks reproducibility (Chapter 13 §13.11) in a way that is extremely hard to diagnose. Always order by something unique, last.

2. DISTINCT ON — PostgreSQL and DuckDB

SELECT DISTINCT ON (order_item_id) *
  FROM bronze.order_items
 ORDER BY order_item_id, _ingested_at DESC, _offset DESC;

Concise and non-portable. Note the ORDER BY must begin with the DISTINCT ON columns.

3. GROUP BY with aggregates

SELECT order_item_id,
       MAX(_ingested_at)                  AS ingested_at,
       MAX(quantity)                      AS quantity   -- ⚠️
  FROM bronze.order_items
 GROUP BY 1;

⚠️ This is the pattern that silently produces Frankenstein rows. MAX(quantity) is the maximum across versions, not the quantity from the latest version. If the columns are not independent — and they are not — this composes a row that never existed.

Use it only when you genuinely want per-column aggregates.

4. DISTINCT on the whole row

SELECT DISTINCT * FROM staging.events;

Only correct when duplicates are byte-identical. A CDC stream's duplicates differ in _offset and _ingested_at, so DISTINCT * keeps all of them and looks like it worked.

🔎 Read the Plan — Test that your deduplication deduplicated

A deduplication step that does not deduplicate is invisible: the query succeeds and the row count is higher than it should be, which nothing checks unless you check it.

The assertion is two lines and belongs after every dedup:

sql SELECT COUNT(*) AS rows, COUNT(DISTINCT order_item_id) AS keys FROM silver.order_items; -- assert rows = keys

This is Chapter 1's duplicate-rows incident's ninety-second diagnosis, promoted from an investigation to a test. In dbt it is a unique test and it is nine lines of YAML.

And the related check, for the opposite failure:

sql -- Did deduplication drop rows it should not have? SELECT COUNT(DISTINCT order_item_id) FROM bronze.order_items; -- before SELECT COUNT(*) FROM silver.order_items; -- after -- assert equal

Both, always. A dedup can fail by keeping too many or by keeping too few, and the two checks catch different failures.

18.8 Gaps and Islands

The shape behind sessionization, streaks, contiguous ranges, and downtime windows — and almost nobody recognizes it until it has been named.

The problem: given rows with an ordering, find groups of consecutive rows that satisfy a condition. Islands are the groups; gaps are the spaces between.

The technique

Create a group identifier that is constant within an island and changes between them, then GROUP BY it.

-- Islands of consecutive days on which a customer ordered.
WITH ordered AS (
    SELECT customer_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
      FROM (SELECT DISTINCT customer_id, order_date FROM gold.fct_order)
),
grouped AS (
    -- The trick: for consecutive dates, (date - rn) is CONSTANT.
    --   date  2025-11-01 02 03   05 06
    --   rn             1  2  3    4  5
    --   date-rn       Oct31 31 31  Nov1 1    ← changes exactly at the gap
    SELECT *, order_date - rn * INTERVAL '1 day' AS island_key
      FROM ordered
)
SELECT customer_id,
       MIN(order_date) AS streak_start,
       MAX(order_date) AS streak_end,
       COUNT(*)        AS streak_length
  FROM grouped
 GROUP BY customer_id, island_key
HAVING COUNT(*) >= 3;

The date - row_number trick is the classic form and it is worth understanding rather than copying: for consecutive values, both increase by one per row, so the difference is constant. The difference changes exactly where the sequence breaks.

The general form

When the island condition is not "consecutive integers," use a boolean flag plus a running sum:

WITH flagged AS (
    SELECT *,
           CASE WHEN event_ts - LAG(event_ts) OVER (PARTITION BY session_id
                                                    ORDER BY event_ts)
                     > INTERVAL '30 minutes'
                THEN 1 ELSE 0 END AS is_new_island
      FROM silver.events
),
grouped AS (
    -- A running sum of a 0/1 flag is a group id that increments at each break.
    SELECT *, SUM(is_new_island) OVER (PARTITION BY session_id
                                       ORDER BY event_ts
                                       ROWS UNBOUNDED PRECEDING) AS island_id
      FROM flagged
)
SELECT * FROM grouped;

"Flag the breaks, then take a running sum of the flag" is the pattern to memorize. It handles every gaps-and-islands problem, and the date - rn trick is a special case of it.

18.9 Sessionization

The most common gaps-and-islands problem in data engineering, and Kestrel's Chapter 6 §6.8 definition: thirty minutes of inactivity, or midnight UTC, whichever comes first.

WITH events AS (
    SELECT anonymous_id, customer_id, event_ts, event_type, path
      FROM silver.events
     WHERE event_date BETWEEN :lo AND :hi
),
with_gap AS (
    SELECT *,
           LAG(event_ts) OVER (PARTITION BY anonymous_id
                               ORDER BY event_ts) AS prev_ts
      FROM events
),
flagged AS (
    SELECT *,
           CASE
             WHEN prev_ts IS NULL                                    THEN 1
             WHEN event_ts - prev_ts > INTERVAL '30 minutes'         THEN 1
             -- the midnight rule: a new UTC day starts a new session
             WHEN event_ts::DATE <> prev_ts::DATE                    THEN 1
             ELSE 0
           END AS is_session_start
      FROM with_gap
),
sessionized AS (
    SELECT *,
           SUM(is_session_start) OVER (PARTITION BY anonymous_id
                                       ORDER BY event_ts
                                       ROWS UNBOUNDED PRECEDING) AS session_seq
      FROM flagged
)
SELECT anonymous_id,
       MD5(anonymous_id || ':' || session_seq::TEXT) AS session_id,
       MIN(event_ts)                                  AS started_at,
       MAX(event_ts)                                  AS ended_at,
       EXTRACT(EPOCH FROM MAX(event_ts) - MIN(event_ts))::INT AS duration_seconds,
       COUNT(*)                                       AS event_count,
       -- the customer_id from the LAST event: identity is resolved at login,
       -- so the end of a session knows more than the start. Ch. 2 §2.5's
       -- conforming problem, in one line.
       MAX(customer_id)                               AS customer_id,
       BOOL_OR(event_type = 'purchase')               AS did_purchase
  FROM sessionized
 GROUP BY anonymous_id, session_seq;

⚠️ Failure Mode — Sessions that straddle the batch boundary

This query sessionizes a date range. A session that began at 23:50 and continued to 00:10 is split by the midnight rule — deliberately, per the definition. Fine.

But a session that began at 23:50 on the last day of the batch and continues into data you have not loaded yet is split by the batch boundary, which is not a definition, it is an artifact. Tomorrow's run sees the tail as a new session with no beginning.

The symptom is a persistent excess of very short sessions clustered at the start of each batch window — and it looks like real user behavior, which is why it survives.

Two fixes:

1. Overlap the input and re-emit. Read :lo - 1 day through :hi, sessionize, and write only sessions that started within [lo, hi). A session straddling the lower boundary is recomputed correctly and discarded; one straddling the upper is emitted tomorrow. This is Chapter 13 §13.4's overlap window, applied to a transformation — and it needs the same idempotent write.
2. Hold open sessions. Keep a state table of sessions with no terminating event and continue them next run. Correct, stateful, and much harder.

Kestrel uses the first. The general lesson: any transformation with a window function that looks backwards needs input overlap, or it produces artifacts at every batch boundary.

💸 Cost Check — this query is the one from Chapter 1

Kestrel's nightly sessionization is the query above, at scale, and it is the job whose cost opened this book.

```text 14,000,000 events/night, partitioned by event_date

WHERE CAST(event_ts AS DATE) = :day no pruning 160 nodes x 10.0 h = $3,840.00 WHERE event_date = :day pruned 24 nodes x 1.3 h = $74.88 ───────── 51.3x, $1,374,269 a year ```

Nothing about the window functions changed. The LAG, the running SUM, the GROUP BY — all identical. The entire difference is a predicate that the engine could not use, because wrapping a partition column in a function makes it unavailable for pruning (§18.10, and Chapter 7 §7.6).

Three things in this query cost money, in order:

The partition predicate. Worth 51×, and it is one line.

The number of distinct PARTITION BY clauses. Every window here partitions by anonymous_id and orders by event_ts — one sort, reused by all three window functions. Add a fourth that partitions by customer_id and the engine sorts the dataset twice. That is a doubling nobody sees, because the query still returns the right answer.

The overlap window. The ⚠️ above reads :lo - 1 day, which is 14 million extra events a night for correctness — about $5.76 at the fixed job's rate. Worth stating out loud, because "the correct version costs 8% more" is a much easier conversation to have before someone finds it on a bill.

🧪 Try It — run the four bugs and watch each one happen

bash cd part-04-transformation/chapter-18-sql-transformations/code python window_lab.py --self-check python window_lab.py --demo dedup # §18.7 python window_lab.py --demo frame # §18.3 python window_lab.py --demo islands # §18.8 python window_lab.py --demo boundary # §18.9

This file does not assert arithmetic. It builds a small SQLite database and runs the queries, because every claim this chapter makes about window semantics is a claim about what an engine actually does — and the only honest way to check it is to ask one.

Do the frame demo first, and predict the output before you run it. LAST_VALUE over the default frame returns the current row, not the last one, and almost everybody gets this wrong the first time — including people who have used window functions for years, because the query looks obviously correct and returns plausible numbers.

Then do dedup twice and diff the results. With the tiebreaker they are identical; remove it and they are not. That is the whole of Chapter 38's Case Study 2, reproduced in about four seconds, and it is worth doing once so that the phrase "non-deterministic dedup" stops being abstract.

18.10 Reading a Transformation's Plan

Chapter 7 §7.6 covered plans for extraction. For transformations, three things matter.

1. The join strategy. Hash join (build a hash table on the smaller side — usually right), merge join (both sides sorted — good for large sorted inputs), nested loop (fine for a tiny inner side, catastrophic otherwise). A nested loop over two large tables is the single most common cause of a transformation that never finishes.

2. Row-count estimates against actuals, at every node. Chapter 7 §7.6's rule: off by 10× means you are debugging statistics, not a query. In a transformation with six joins, one bad estimate at the bottom propagates through every join above it, so check the lowest divergent node first.

3. Spilling. Chapter 8 §8.8. A window function partitioning over something with enormous groups, or a sort that does not fit memory. Remote spill is the strongest signal a transformation needs attention.

And the thing to check before any of them: does the plan show a sort you did not ask for? Window functions require sorted input, and a chain of window functions with different PARTITION BY clauses forces a re-sort per clause. Consolidating window functions onto the same partitioning is frequently a large, free win — and it is invisible unless you look at the plan.

🎓 Interview Angle — the SQL question is not about SQL

A data engineering interview will ask you to write a window function, usually one of four: deduplicate, sessionize, running total, or top-N-per-group. You should be able to write all four without thinking, and that is table stakes rather than a differentiator.

What separates candidates is what they say around the query. Three habits, in the order they land:

State the grain before you write anything. "One row per order line after dedup" — and then the tiebreaker question answers itself, because you have just committed to a uniqueness claim you now have to make true.

Name the tiebreaker, unprompted. ORDER BY updated_at DESC alone is the single most common answer to a dedup question and it is non-deterministic. A candidate who adds , id DESC and says why has debugged this; one who does not has read about it.

Ask what happens at the boundary. For anything time-windowed: "does this run over the whole table or over a day? Because if it's a day, LAG reaches back into data the batch doesn't have." This is §18.9's failure mode, and it is the question that most reliably surprises an interviewer, because it is the bug their production pipeline actually has.

On the whiteboard-versus-laptop question: if you are given an engine, use it. Run the query against three rows you made up, including a tie and a null, and say what you are checking. An interviewer watching you build a two-row test case is watching the thing they are actually hiring for.

And if you are asked to optimize it, the answer order is: partition predicate first (§18.10), then consolidate the PARTITION BY clauses, then look at the plan. Nobody expects you to know their statistics. Everybody expects you to know that a CAST on a partition column is a full scan.

🏭 From the Pipeline — the LEFT JOIN that became an INNER JOIN

A gold model joined fct_order_line to dim_promotion with a LEFT JOIN, correctly: most lines have no promotion, and a left join keeps them.

Someone added a filter for a new dashboard:

sql FROM fct_order_line f LEFT JOIN dim_promotion p ON p.promotion_id = f.promotion_id WHERE p.promotion_type <> 'internal' -- <- the change

A predicate on the right-hand table in the WHERE clause turns a LEFT JOIN into an INNER JOIN, because a null promotion_type fails <> 'internal'. Every line without a promotion disappeared.

Revenue fell 71% overnight and was noticed within a day — which is the good outcome, and it is only the good outcome because the drop was enormous.

The version of this that is not noticed is the same mistake on a dimension where most rows do match:

sql LEFT JOIN dim_customer c ON c.customer_sk = f.customer_sk WHERE c.region <> 'internal'

Guest checkouts have no customer — 4.2% of orders at Kestrel — so this silently drops 4.2% of revenue, which is well inside the range anybody would attribute to normal variation.

The fix is one line and the rule is worth memorising:

```sql -- put the predicate in the JOIN, not the WHERE LEFT JOIN dim_customer c ON c.customer_sk = f.customer_sk AND c.region <> 'internal'

-- or, if you mean the WHERE, say what happens to nulls explicitly WHERE c.region <> 'internal' OR c.region IS NULL ```

Two things made this survive review. The WHERE clause reads as a filter on the result, which is what a WHERE normally is, and nothing about the syntax signals that it has changed the join's semantics. And the reviewer was looking at the diff — one added line — rather than at the query.

The assertion that would have caught the quiet version is Chapter 23's grain-and-volume pair: a row count against the unfiltered fact table, asserted to be within a band. A 4.2% drop fails a 3% band and passes a 10% one, which is why the band's width is a decision rather than a default.

🔐 Privacy & Governance — a window function can re-identify

Aggregation is usually the privacy-safe operation. Window functions are not aggregation, and the difference matters more than it looks.

```sql -- an AGGREGATE: one row per group. Individuals are gone. SELECT region, count(*), sum(net_cents) FROM fct_order_line GROUP BY 1;

-- a WINDOW: one row per INPUT row, with a group-level value attached. SELECT customer_id, net_cents, sum(net_cents) OVER (PARTITION BY region) AS region_total, rank() OVER (PARTITION BY region ORDER BY net_cents DESC) AS rk FROM fct_order_line; ```

The second query's output has one row per customer. It has not aggregated anything away; it has added context to individual records — and rk = 1 names the largest spender in each region.

Three specific hazards, in order of how often they appear in a real model:

A rank exposes an individual by construction. "Top 10 customers by revenue" is a list of people, and it is a list of people whether or not the output contains a name — the rank plus the region plus the amount is frequently enough to identify someone to anyone who knows the business.

A window over a small partition leaks the partition. avg(salary) OVER (PARTITION BY team) with a team of two lets each member compute the other's salary exactly. This is the k-anonymity problem (Chapter 31) arriving as a perfectly ordinary analytical query.

And LAG/LEAD reconstructs a sequence. Chapter 18's sessionisation is built on this and it is the point — a session is a behavioural trace of one person, and it is more identifying than any single event in it (Chapter 31's re-identification section).

Two practical rules:

Assert a minimum partition size wherever a window is applied to something person-shaped. A HAVING count(*) >= 5 on the equivalent aggregate, checked as a test, catches the small-partition case before it is published.

And classify the output of a windowed model, not just its inputs. A model whose inputs are classified internal can produce an output that is confidential — the window is where the classification changes, and no automatic tagging tool will notice.

What gets slow, and the four rewrites that fix most of it

Every technique in this chapter is fast at a million rows and some of them are not at a hundred million. Four patterns account for most of the difference, and each has a mechanical rewrite.

1. A self-join to compare neighbouring rows

-- O(n log n) at best, and a full shuffle of both sides
SELECT a.*, b.placed_at AS prev_placed_at
  FROM orders a LEFT JOIN orders b
    ON b.customer_id = a.customer_id AND b.seq = a.seq - 1;

-- one pass, one sort
SELECT *, LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY seq) AS prev_placed_at
  FROM orders;

The window version reads the table once. The self-join reads it twice and shuffles both copies, and the gap widens with the table.

2. Several window functions with different PARTITION BY clauses

-- THREE sorts: one per distinct window specification
SUM(x) OVER (PARTITION BY customer_id ORDER BY ts)
SUM(y) OVER (PARTITION BY region      ORDER BY ts)
SUM(z) OVER (PARTITION BY customer_id ORDER BY ts)   -- same as the first!

Windows sharing a specification share a sort. Consolidating the first and third costs nothing and removes a full sort of the dataset; the second genuinely needs its own, and knowing which is which requires reading the plan (§18.10).

The tell in a Spark plan is the number of Sort nodes; in a warehouse it is the profile's sort operators. Either way, the count should equal the number of distinct window specifications — and when it does not, something is re-sorting.

3. DISTINCT where a GROUP BY or a window would do

-- a full sort or hash of every column
SELECT DISTINCT customer_id, region FROM fct_order_line;

-- the same answer, and the optimiser has more to work with
SELECT customer_id, region FROM fct_order_line GROUP BY 1, 2;

-- and if you wanted "one row per customer", this is the honest version
SELECT * FROM fct_order_line
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC,
                                                             order_line_id DESC) = 1;

SELECT DISTINCT * over a wide table is the most expensive statement in this book and it is almost always somebody papering over a fan-out they have not diagnosed. The fix is to find the join that duplicated the rows (§6.9), not to deduplicate afterwards.

4. A CROSS JOIN hiding in a date spine

-- a spine of dates x customers: 365 x 1,900,000 = 693,500,000 rows
-- ...built to produce a report with 12,000 rows in it
SELECT d.date_key, c.customer_id, coalesce(f.net_cents, 0)
  FROM dim_date d CROSS JOIN dim_customer c
  LEFT JOIN fct_order_line f ON ...

Date spines are correct and they are a multiplication. Build them over the dimension values that actually appear, not over the full dimension — and build them at the grain of the output rather than of the input.

The measurement that ranks these for your own project

-- warehouse-agnostic shape: what does each model cost, per run?
SELECT model, avg(rows_scanned), avg(bytes_scanned), avg(elapsed_s),
       avg(bytes_spilled_to_remote)
  FROM your_run_history
 WHERE run_date > current_date - 30
 GROUP BY 1 ORDER BY 3 DESC LIMIT 10;

Sort by elapsed and look at the spill column (Chapter 8 §8.8). Remote spill on a model that should aggregate to a few hundred rows is a fan-out, not a memory problem — and it is the single highest-yield thing to look for, because it is a correctness bug that presents as a performance one.

🧭 Version Note — SQL got better, and the good parts are unevenly available

Several things in this chapter were awkward five years ago and are now one keyword — on some engines.

text feature what it replaces where it works ───────────────────────────────────────────────────────────────────────── QUALIFY a subquery around every Snowflake, BigQuery, window filter DuckDB, Databricks, Teradata. NOT Postgres. PIVOT / UNPIVOT a wall of CASE expressions Snowflake, DuckDB, Databricks, SQL Server GROUPS frame ROWS or RANGE, neither of Postgres 11+, DuckDB, which meant "peer groups" Snowflake IGNORE NULLS a nested window trick that most, and the syntax nobody remembers differs lateral / cross join unnest a correlated subquery everywhere, spelled four ways

QUALIFY is the one worth knowing the boundary of, because this book uses it throughout and PostgreSQL does not have it. The portable form is the subquery (§18.2), and a project that may move engines should decide once and write it down (Appendix B §B.9).

GROUPS deserves a mention because it fixes a real gap. ROWS counts rows and RANGE counts values; GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW counts peer groups, which is what people usually mean when they say "the previous distinct value."

What has not changed: the frame clause's default, which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in the standard and in every engine here. §18.3's LAST_VALUE surprise is thirty years old and will still be surprising people in another thirty, because it is in the standard rather than in an implementation.

🔁 Idempotency Check — a SELECT is idempotent; a transformation is not automatically

A query returns the same answer twice only if nothing it reads has changed and nothing it computes depends on the moment. Three things in ordinary SQL break that.

```sql -- 1. the clock WHERE placed_at >= current_date - 7 -- a different 7 days each run

-- 2. an incomplete ORDER BY in a window QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC) = 1

-- 3. a random or a hash used as a tiebreak ORDER BY random() -- and yes, this is written ```

Row 1 is the one that hides in plain sight. It is not wrong — it is not reproducible, which is a different property. A model with current_date in it cannot be backfilled, cannot be tested against a fixture, and produces a different table depending on when it runs, and none of that fails.

The replacement is an interval passed in from the orchestrator (Chapter 24 §24.3), which is the same fix as everywhere else in this book: make the time an input rather than an ambient fact.

sql WHERE placed_at >= {{ var('window_start') }} AND placed_at < {{ var('window_end') }}

Row 2 is §18.7's tiebreaker and Chapter 38's Case Study 2.

And row 3 has a legitimate use that people generalise from wrongly: ORDER BY random() for a sample is fine when the sample is exploratory and catastrophic when the sample feeds anything reproducible. Use a deterministic hash of a key instead — WHERE abs(hash(order_id)) % 100 < 1 is a stable 1% sample that gives the same rows every run.

The check: run the model twice, against the same fixture, and diff. Chapter 27's purity check greps for the clock; the diff catches the other two, which no grep can find.

18.11 The Kestrel Silver Models

Everything in this chapter, applied.

-- silver/orders.sql
WITH deduped AS (
    -- §18.7 pattern 1, with a unique tiebreaker so re-runs are deterministic
    SELECT * FROM bronze.orders_cdc
     WHERE ingest_date >= :since
    QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id
                               ORDER BY lsn DESC, _kafka_offset DESC) = 1
),
typed AS (
    SELECT order_id,
           customer_id,
           status,
           placed_at::TIMESTAMPTZ                     AS placed_at,
           (placed_at AT TIME ZONE 'UTC')::DATE       AS order_date,
           channel,
           op,
           lsn                                        AS source_lsn
      FROM deduped
     WHERE op <> 'd'                                  -- deletes handled by the merge
),
enriched AS (
    SELECT *,
           -- §18.2: the customer's nth order, without a self-join
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY placed_at)     AS customer_order_seq,
           -- §18.2: days since their previous order
           EXTRACT(DAY FROM placed_at
                   - LAG(placed_at) OVER (PARTITION BY customer_id
                                          ORDER BY placed_at))::INT
                                                      AS days_since_prev_order
      FROM typed
)
SELECT * FROM enriched;

Three details in that model are worth pointing at, because each one is a decision the query does not explain by itself.

_kafka_offset is the tiebreaker, carried through from bronze precisely so that this dedup has something total to order by (§18.7). It is not data anyone wants; it exists so that two runs over the same input produce the same table. A bronze layer that discards its transport metadata takes this option away from every model downstream, which is one of the quieter reasons Chapter 34 insists bronze keeps what it was given.

WHERE op <> 'd' comes after the dedup, not before it. Filtering deletes out first would let an older non-delete row win for a key whose latest event is a deletion — the row would come back to life, which is the specific bug Chapter 14 §14.8's merge exists to prevent.

And days_since_prev_order is null for a first order, deliberately, rather than zero. Zero is a measurement and null is an absence, and a downstream AVG treats them very differently (Chapter 23 §23.4).

🧱 Kestrel Platform — Increment 18: the silver models

(a) Write silver.orders, silver.order_items, and silver.events as SQL, with the deduplication pattern from §18.7 — including the unique tiebreaker.

(b) Write silver.sessions using §18.9's sessionization, with the midnight rule.

(c) Prove the deduplication. Both assertions from §18.7's 🔎 callout: rows equal distinct keys, and no keys were dropped. Two checks, because a dedup fails in two directions.

(d) Reproduce the boundary artifact. Run sessionization on two adjacent date ranges without overlap, and count sessions of under 30 seconds starting within five minutes of the window start. Then add the overlap and re-count. Record both numbers — the difference is the artifact.

(e) Read the plan for silver.sessions. Count the sorts. Then consolidate the window functions onto a common PARTITION BY ... ORDER BY where possible and count again.

18.12 Summary

Set-based thinking is the habit that matters more than any syntax. Describe the relationship between sets once, rather than looping. The three tells that you are thinking procedurally: a loop issuing queries, a correlated subquery in the SELECT list (usually a window function), and a self-join to compare a row with its neighbor (usually LAG/LEAD). Procedural is right for chunking, for genuinely sequential logic — which is rarer than it looks; most "inherently sequential" problems are window functions or recursive CTEs — and for per-row external calls.

Window functions compute across related rows without collapsing them, which is the whole difference from GROUP BY. Use ROW_NUMBER, not RANK, for deduplication — RANK gives ties the same number and WHERE rank = 1 keeps all of them. QUALIFY filters on a window directly and is unavailable in PostgreSQL.

The frame clause is where the surprises are. With ORDER BY, the default frame is a running aggregate; without it, the whole partition — so adding an ORDER BY silently turns a total into a running total. RANGE includes peers — rows sharing the ORDER BY value — so two rows on the same date show the same running total; ROWS counts physical rows. And 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 the most reported window-function surprise. If a window function's answer surprises you, write the frame out explicitly.

CTEs are for readability, and they are not optimization barriers. That was true in PostgreSQL before 12 and is repeated as a general fact. Modern engines inline them and decide; force materialization only when you have measured a problem with a CTE referenced several times.

Recursive CTEs need UNION ALL (not UNION, which deduplicates every iteration and masks cycles) and a depth guard, which is not optional — a cycle turns it into an infinite loop, and a depth limit converts an infinite loop into a wrong answer you can detect. Prefer a flattened dimension when depth is bounded and known.

Pivot with CASE for portability, and note the constraint: you must know the columns in advance, so a dynamic pivot generates SQL — and a new value silently adds a column, which is a schema change every run. Prefer long format in gold and pivot in the BI layer.

Four deduplication patterns, and you will write this more than anything else here. ROW_NUMBER is the general answer, and the unique tiebreaker is not optional — without it, dedup is non-deterministic and re-runs produce different results. DISTINCT ON is concise and non-portable. GROUP BY with MAX() per column silently composes rows that never existed. DISTINCT * only works on byte-identical duplicates, which CDC duplicates are not.

And assert both directions after every dedup: rows equal distinct keys, and no keys were dropped. A dedup fails by keeping too many or too few, and the checks are different.

Gaps and islands is the shape behind sessionization, streaks, and contiguous ranges. The technique: create a group identifier constant within an island, then group by it. The general form to memorize is "flag the breaks, then take a running sum of the flag"; date - row_number is a special case.

⚠️ Any transformation with a backward-looking window function needs input overlap, or it produces artifacts at every batch boundary — a persistent excess of short sessions at each window start, which looks like real user behavior and therefore survives. That is Chapter 13's overlap window applied to a transformation, and it needs the same idempotent write.

In a transformation's plan, check three things — join strategy (a nested loop over two large tables is the commonest cause of a transformation that never finishes), estimate-versus-actual at the lowest divergent node, and spilling. And before all three: look for sorts you did not ask for. Window functions with different PARTITION BY clauses force a re-sort each; consolidating them is frequently a large free win, and it is invisible unless you read the plan.

What's next

Chapter 19 is dbt, which takes the SQL in this chapter and makes it into software: files in version control, with dependencies, tests, documentation, and CI. Its contribution was social rather than technical — it dragged transformation back inside software engineering twenty years after GUI ETL tools dragged it out — and the chapter is honest about what it is bad at.