SQL for data engineering — the parts that matter.

Before anything advanced, you need to be unshakeable on the basics. INNER, LEFT, FULL OUTER, CROSS, and anti-joins (LEFT JOIN ... WHERE b.id IS NULL). GROUP BY with HAVING vs WHERE vs QUALIFY. UNION vs UNION ALL — and why the wrong choice can 10x your query cost.

If you can't explain when a LEFT JOIN silently drops rows because of a WHERE clause on the right side, stop here and fix that first. It's the #1 SQL bug in production pipelines.

Window functions are non-negotiable. Every serious data engineering interview tests them. The pattern:

SELECT
  user_id,
  event_time,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn,
  LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY event_time
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7
FROM events

Master ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, and framed aggregates (ROWS vs RANGE). Know when QUALIFY exists (Snowflake, BigQuery, Databricks) and when it doesn't.

A 200-line query as one giant subquery pyramid is a red flag. Break it into CTEs, each named for what it produces. Recursive CTEs for tree/graph traversal (org charts, category trees).

Trade-off worth knowing: on some engines (Postgres pre-12), CTEs are optimization fences. On modern warehouses (Snowflake, BigQuery, Databricks), they're inlined. Explain plans tell you.

Three patterns that show up in almost every DE interview:

• Gaps and islands. Group consecutive rows that share a property. Solve with ROW_NUMBER difference or LAG + running sum.
• Sessionization. Bucket events into sessions with an inactivity gap (typically 30 min). LAG for prev event, flag new sessions, running sum for session ID.
• Dedupe latest per key. QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1. On engines without QUALIFY, wrap it in a subquery.

• Read the explain plan. EXPLAIN in Postgres, EXPLAIN or the query profile in Snowflake, execution graph in BigQuery.
• Partition pruning. If your table is partitioned by event_date, filter on event_date — not on a derived column. WHERE DATE(event_ts) = ... often kills pruning.
• Clustering / sort keys. Snowflake clustering keys, BigQuery clustering, Redshift sort keys — all reduce scanned data. Cluster on your most-filtered column.
• Broadcast vs shuffle joins. Small dim tables should be broadcast. The optimizer usually gets this right; when it doesn't, hint it (/*+ BROADCAST(dim) */).
• Avoid SELECT * on wide tables. Columnar warehouses charge you per column scanned.

Real pipelines rarely full-refresh. Learn the three incremental patterns cold:

• Append-only. Immutable event streams. Cheapest.
• Merge / upsert. MERGE INTO target USING source ON key WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT. The workhorse.
• Delete + insert. Delete the affected partition, insert fresh. Simpler than merge when you can identify the affected slice.

Bonus: SCD Type 2 with MERGE and a validity window (valid_from, valid_to, is_current). Comes up in every senior interview.

SQL is code. Test it.

• dbt tests for not_null, unique, relationships, accepted_values.
• Custom singular tests for business invariants ("total revenue in marts equals total in staging").
• Row-count assertions in the pipeline. If the daily fact table grows by 10x overnight, alert — don't just publish.

The DataForge SQL track drills windows, CTEs, gaps-and-islands, and warehouse-specific optimizations with real datasets — not toy Northwind queries. Pair it with Bug Hunt to practice reading broken SQL under interview conditions, and with the interview questions page to prep the exact patterns hiring managers ask.