Appendix B: SQL for Data Engineering

Not a SQL tutorial. This is the subset a data engineer uses constantly, with the traps that produce wrong numbers rather than errors.

Every example runs against the Kestrel schema (Appendix A). Dialect differences are noted where they change behaviour rather than syntax.

B.1 The Ones That Produce Wrong Numbers Silently

These are first because they are the ones that ship.

-- 1. NOT IN with a NULL returns NOTHING. Silently.
SELECT * FROM products
 WHERE sku NOT IN (SELECT sku FROM discontinued);   -- 0 rows if any sku is NULL

SELECT * FROM products p                            -- correct
 WHERE NOT EXISTS (SELECT 1 FROM discontinued d WHERE d.sku = p.sku);

-- 2. COUNT(col) skips NULLs; COUNT(*) does not.
SELECT count(*), count(shipped_at) FROM orders;     -- different numbers

-- 3. An INNER JOIN silently drops the rows you were counting.
SELECT c.customer_id, count(o.order_id)             -- customers with 0 orders vanish
  FROM customers c JOIN orders o USING (customer_id) GROUP BY 1;

SELECT c.customer_id, count(o.order_id)             -- correct
  FROM customers c LEFT JOIN orders o USING (customer_id) GROUP BY 1;

-- 4. A WHERE clause on the right side of a LEFT JOIN makes it an INNER JOIN.
SELECT ... FROM customers c
  LEFT JOIN orders o USING (customer_id)
 WHERE o.status = 'shipped';                        -- inner join now

SELECT ... FROM customers c                          -- correct: move it to ON
  LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'shipped';

-- 5. Integer division truncates before you multiply.
SELECT 5500 / 21900 * 10000 AS bps;                 -- 0
SELECT 10000 * 5500 / 21900 AS bps;                 -- 2511

-- 6. Aggregating a joined fact multiplies it.
SELECT o.order_id, sum(ol.cents), sum(s.shipping_cents)   -- both inflated
  FROM orders o JOIN order_lines ol USING (order_id)
                JOIN shipments s USING (order_id);

Number 6 is the fan trap (ch6), and it is the single most expensive item on this list because the result looks plausible. Aggregate each fact separately and join the aggregates.

B.2 Window Functions

The most valuable feature in this list.

SELECT
  order_id, customer_id, placed_at, net_cents,

  row_number() OVER (PARTITION BY customer_id ORDER BY placed_at, order_id)  AS seq,
  rank()       OVER (ORDER BY net_cents DESC)                                AS rnk,
  dense_rank() OVER (ORDER BY net_cents DESC)                                AS drnk,

  lag(placed_at)  OVER (PARTITION BY customer_id ORDER BY placed_at)         AS prev_order,
  lead(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at)         AS next_order,

  first_value(net_cents) OVER w                                              AS first_order_value,
  sum(net_cents)         OVER (PARTITION BY customer_id ORDER BY placed_at
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW)                     AS running_total
FROM gold.fct_order
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Three things that catch people:

ROW_NUMBER needs a total order. ORDER BY placed_at alone, with ties, returns a different row on every run — and a uniqueness test still passes. Always add a tie-breaker (ch20, ch38 CS2).

RANK leaves gaps and DENSE_RANK does not. 1, 1, 3 versus 1, 1, 2.

LAST_VALUE does not do what you expect. With the default frame it returns the last row of the current peer group, which is usually the current row. Use an explicit frame:

last_value(x) OVER (PARTITION BY k ORDER BY t
                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

B.3 The Frame Clause

The default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and it is not what most people mean.

ROWS   counts physical rows
RANGE  includes every PEER -- every row with the same ORDER BY value
GROUPS counts distinct peer groups

When the ordering column has duplicates they differ:

-- two orders on 2026-03-05
sum(x) OVER (ORDER BY order_date ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- includes rows up to this one

sum(x) OVER (ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- includes BOTH rows of 2026-03-05, for both of them

A running total over a date column with multiple rows per date is the standard place this bites.

B.4 Deduplication

-- keep the latest version of each order. Note the TIE-BREAK.
WITH ranked AS (
  SELECT *, row_number() OVER (PARTITION BY order_id
                               ORDER BY updated_at DESC, cdc_lsn DESC) AS rn
    FROM bronze.orders_raw
)
SELECT * FROM ranked WHERE rn = 1;

QUALIFY removes the subquery, in Snowflake, BigQuery, DuckDB, and Databricks:

SELECT * FROM bronze.orders_raw
QUALIFY row_number() OVER (PARTITION BY order_id
                           ORDER BY updated_at DESC, cdc_lsn DESC) = 1;

Postgres has no QUALIFY. Use DISTINCT ON, which is a Postgres extension and is often faster:

SELECT DISTINCT ON (order_id) *
  FROM bronze.orders_raw
 ORDER BY order_id, updated_at DESC, cdc_lsn DESC;

B.5 As-Of Joins

Point-in-time correctness (ch32). Portable form:

SELECT l.customer_id, l.event_ts, f.orders_to_date
  FROM labels l
  LEFT JOIN LATERAL (
      SELECT orders_to_date
        FROM features
       WHERE customer_id = l.customer_id
         AND valid_from <= l.event_ts        -- <= not <
       ORDER BY valid_from DESC
       LIMIT 1
  ) f ON true;

Native support exists in DuckDB, Snowflake, ClickHouse, and Databricks:

SELECT * FROM labels ASOF LEFT JOIN features
  ON labels.customer_id = features.customer_id
 AND labels.event_ts >= features.valid_from;

Read your engine's boundary semantics. >= versus > is a row of data per entity, and the defaults differ.

B.6 Gaps and Islands, and Sessionization

Finding runs of consecutive values, and the standard sessionization pattern:

WITH marked AS (
  SELECT customer_id, event_ts,
         CASE WHEN event_ts - lag(event_ts) OVER (PARTITION BY customer_id
                                                  ORDER BY event_ts)
                   > INTERVAL '30 minutes'
              OR lag(event_ts) OVER (PARTITION BY customer_id ORDER BY event_ts) IS NULL
              THEN 1 ELSE 0 END AS is_new_session
    FROM silver.stg_events
),
sessioned AS (
  SELECT *, sum(is_new_session) OVER (PARTITION BY customer_id
                                      ORDER BY event_ts
                                      ROWS UNBOUNDED PRECEDING) AS session_seq
    FROM marked
)
SELECT customer_id, session_seq,
       min(event_ts) AS started_at, max(event_ts) AS ended_at,
       count(*) AS events
  FROM sessioned GROUP BY 1, 2;

The ROWS UNBOUNDED PRECEDING is load-bearing — with RANGE, simultaneous events land in the same peer group and the running sum jumps.

B.7 Set Operations and Anti-Joins

-- rows in A not in B, on multiple columns
SELECT a.* FROM a
 WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.k1 = a.k1 AND b.k2 = a.k2);

-- symmetric difference: what a reconciliation needs
SELECT COALESCE(o.k, n.k) AS k, o.amount AS old_amount, n.amount AS new_amount
  FROM old_output o
  FULL OUTER JOIN new_output n USING (k)
 WHERE o.k IS NULL OR n.k IS NULL OR o.amount <> n.amount;

-- EXCEPT / INTERSECT: whole-row, and they deduplicate
SELECT k FROM a EXCEPT SELECT k FROM b;          -- EXCEPT ALL keeps duplicates

The FULL OUTER JOIN is the reconciliation form (ch37 §37.6). An inner join cannot see a row that one side produced and the other did not, which is the most common difference.

B.8 Pivot and Unpivot

-- pivot, portable
SELECT customer_id,
       sum(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
  FROM orders GROUP BY 1;

-- unpivot, portable
SELECT customer_id, 'shipped' AS status, shipped AS n FROM t
UNION ALL
SELECT customer_id, 'cancelled', cancelled FROM t;

DuckDB, Snowflake, and Spark have PIVOT/UNPIVOT. The dynamic case — pivoting on values you do not know in advance — requires generated SQL in every engine, and that is the honest answer to the interview question.

B.9 Dialect Differences That Matter

Two that cause real bugs:

Snowflake folds unquoted identifiers to upper case and Postgres folds to lower. A quoted identifier in one is not the same object in the other.

BigQuery arrays are zero-based and everything else is one-based.

B.10 Reading a Plan

EXPLAIN (ANALYZE, BUFFERS)  SELECT ...;   -- Postgres: actual rows + IO
EXPLAIN ANALYZE             SELECT ...;   -- DuckDB
EXPLAIN                     SELECT ...;   -- Spark; add FORMATTED for detail

Four things to look for, in order:

Which relation is scanned, and how much of it. Seq Scan on order_lines (rows=6,480,000) when you expected a day's worth means a predicate did not prune.

Whether the estimate matches reality. Postgres prints rows=1000 and actual rows=2,400,000 when statistics are stale; the plan was chosen on the estimate.

Where the joins are and in what order. A nested loop over a large outer relation is usually the problem.

Whether a sort or a hash spills. Sort Method: external merge Disk: 412000kB means memory was insufficient.

In Spark, read the scan node:

FileScan parquet [order_id,net_cents]
  PartitionFilters: [isnotnull(event_date), (event_date = 2026-03-14)]   <- pruning worked
  PushedFilters: [IsNotNull(customer_id)]
  ReadSchema: struct<order_id:bigint,net_cents:bigint>                    <- projection worked

An empty PartitionFilters on a partitioned table is Chapter 1's $3,840 bug.

B.11 Money, Time, and Types

-- money: integer cents, always
net_revenue_cents  BIGINT NOT NULL     -- never FLOAT, never DOUBLE
-- and cast explicitly when summing in DuckDB, or SUM returns HUGEINT
SELECT sum(net_revenue_cents)::BIGINT FROM ...;

-- time: store UTC, be explicit at boundaries
placed_at   TIMESTAMPTZ NOT NULL       -- an instant
event_date  DATE NOT NULL              -- a partition key, derived explicitly

The timezone trap (ch38 CS1): WHERE placed_at >= '2026-11-01' against a timestamptz is evaluated in the session timezone. Two sides of a reconciliation with different session settings compare different months. State the timezone in the query:

WHERE placed_at >= TIMESTAMPTZ '2026-11-01 00:00:00+00'
  AND placed_at <  TIMESTAMPTZ '2026-12-01 00:00:00+00'

And never use BETWEEN on timestamps — it is inclusive at both ends, so a row at exactly midnight lands in two months.

B.12 Performance Habits

Filter before you join, not after. The optimizer usually does this and cannot when the predicate involves a function of a joined column.

Do not apply functions to a column you are filtering on. WHERE CAST(event_ts AS DATE) = '...' defeats partition pruning; WHERE event_ts >= ... AND event_ts < ... does not.

SELECT * on a columnar table reads every column. Name them.

Aggregate before joining when the join fans out.

EXISTS beats IN on a large subquery, and beats COUNT(*) > 0 always, because it can stop at the first match.

Check statistics before optimizing. ANALYZE in Postgres; a stale plan is a much more common cause of a slow query than a missing index.