All stacks · Languages & local engines
Query patterns, window functions, and modeling.
SQLDeduplication, running totals, sessionization, period-over-period comparisons, and top-N per group — plus frames, pitfalls, and live playground queries.
SQLDISTINCT, GROUP BY, ROW_NUMBER, QUALIFY, MERGE, and stream-side dedup compared on cost and semantics, plus how to pick the survivor row deterministically.
SQLHow WITH RECURSIVE actually executes, patterns for hierarchy traversal and BOM explosion, and the cycle guards that keep a bad edge from running forever.
SQLConditional aggregation, native PIVOT syntax, unpivoting wide tables, and how to handle dynamic categories without generating SQL at runtime.
SQLWhat actually happens to a CTE in Postgres, Snowflake, and BigQuery, when it blocks predicate pushdown, and when referencing it twice costs you two scans.
SQLHow window frames actually evaluate, why RANGE and ROWS give different answers on ties, and six window patterns that delete self-joins from your models.
SQLThe join fan-out failure class: grain checks before and after every join, EXISTS for semi-joins, anti-joins done right, and the postmortem pattern.
SQLFive SQL habits that scan more than they should: SELECT *, functions on filter columns, DISTINCT as a bug fix, OR-joins, and per-row UDF calls.
dbtHow incremental dbt models actually write: unique_key as merge grain, is_incremental filters, lookback windows, and the late-arrival cases that silently drop rows.
SnowflakeHow Dynamic Tables actually refresh: TARGET_LAG, the warehouse that pays for it, streams vs DT, and why I still keep dbt for gold.
SnowflakeWhat a micropartition is, how clustering and sargable filters prune, why wrapping columns and SELECT * blow the scan, and how to read partitions_scanned.
SnowflakeCREATE … CLONE shares micropartitions until someone writes. How storage grows, how to clone at a timestamp, how I use clones in CI, and why I never mutate gold through a clone.
ProductivityHow to actually use AI coding assistants for pipeline work — SQL generation, dbt refactoring, debugging, and documentation — plus the failure modes to watch for.
TrinoTrino stores nothing. A federated SELECT * on prod Postgres is a database incident. Use Iceberg for the lake, and keep interactive SQL off OLTP.
SQL is the one skill in data engineering that has never stopped compounding. Every warehouse, lakehouse, and streaming engine converges on it, and the gap between adequate SQL and fluent SQL shows up directly in pipeline cost, correctness, and how long code review takes.
The articles here go past syntax: window functions used the way production models use them, join semantics that stop duplicate-row bugs, query anti-patterns that quietly multiply warehouse spend, and the modeling patterns — deduplication, sessionization, SCDs — that repeat in every stack.
SQL plus one orchestration tool and one cloud platform is a credible junior profile — and interviews weight SQL heavily because it is hard to fake. Strong window-function fluency alone puts you ahead of a surprising share of candidates.
Deduplication with row_number, top-N per group, running totals and moving averages, gaps-and-islands, and join questions designed to expose fan-out misunderstandings. Our interview section drills each with worked solutions.
The right side was not unique on the join key, so each left row matched multiple right rows — the classic fan-out. Check grain before joining: if the key is not unique, aggregate or dedupe first. This one bug pays for all the time you spend learning grain discipline.
In modern warehouses, generally no — most optimizers inline CTEs, so structure queries for readability. The exception is recursive CTEs and engines that materialize CTEs referenced multiple times; measure before contorting your SQL.
Use the free SQL playground on this site — SQLite in your browser with realistic orders, customers, and events tables — or DuckDB locally over public Parquet datasets. Both cover 95% of interview-relevant SQL.
One email a week with our latest tutorials. No spam.
Newsletter signup is not live yet. Use the contact form if you want to be notified.