SQL patterns every data engineer should know cold

A short reference of the SQL patterns that come up weekly in production data pipelines — across Postgres, BigQuery, Snowflake, and Databricks. Not the tutorial material; the stuff that actually causes bugs when you get it wrong.

Why this repo exists: I write pipeline SQL across four warehouses and kept hitting the same five classes of bug — window-function frame mistakes, MERGE dialect differences, NULL-handling surprises, partition-pruning footguns, and the materialization trade-offs nobody spells out. I organized the full set into a printable 22-page reference pack covering all 10 sections.

The patterns below are free — copy them. The full pack is the printable PDF version.

---

1. The last_value() frame bug (the #1 window-function mistake)

last_value() returns the last value in the current frame, not the partition. Without an explicit ROWS BETWEEN ... AND UNBOUNDED FOLLOWING, you get the current row — silently wrong:

-- ❌ BUG: latest_region will equal region on every row
select distinct
    customer_id,
    last_value(region) over (
        partition by customer_id
        order by valid_from
    ) as latest_region
from dim_customers;

-- ✅ CORRECT: explicit frame to end of partition
select distinct
    customer_id,
    last_value(region) over w as latest_region
from dim_customers
window w as (
    partition by customer_id
    order by valid_from
    rows between unbounded preceding and unbounded following
);

If you've ever had "mysterious wrong latest value" in a pipeline, this is why.

2. The NULL drop bug in <>

where x <> 'foo'        -- silently drops rows where x IS NULL
where x is distinct from 'foo'   -- correct — treats NULL as a value

When x is NULL, x <> 'foo' evaluates to NULL, which the WHERE clause treats as false. Rows vanish silently. This is the source of roughly half the "why are rows missing from my pipeline" tickets.

3. MERGE / UPSERT idioms side-by-side

The same idempotent upsert, four ways:

-- Postgres
insert into warehouse.fct_sales (sale_id, revenue_usd)
select sale_id, revenue_usd from staging.fct_sales
on conflict (sale_id) do update set
    revenue_usd = excluded.revenue_usd;

-- BigQuery / Snowflake
merge warehouse.fct_sales as target
using staging.fct_sales as source
on target.sale_id = source.sale_id
when matched then update set revenue_usd = source.revenue_usd
when not matched then insert (sale_id, revenue_usd) values (source.sale_id, source.revenue_usd);

-- Databricks / Spark SQL
merge into warehouse.fct_sales as target
using staging.fct_sales as source
on target.sale_id = source.sale_id
when matched then update set *
when not matched then insert *;

Gotcha: BigQuery MERGE is not atomic — concurrent MERGEs can race. Postgres is atomic. Scope merges to a partition on large targets.

4. Partition pruning footgun

-- ✅ Prunes: scans only relevant partitions
select * from fct_sales where order_date between '2026-01-01' and '2026-01-31';

-- ❌ Does NOT prune: full table scan
select * from fct_sales where extract(month from order_date) = 1;

Wrapping the partition column in any function disables pruning. Always filter on the raw column. This is the difference between a 5-second query and a 5-minute one.

5. The date_diff() argument-order trap

Same function, different argument order in every warehouse:

-- BigQuery:  (end, start, part)
date_diff(end_date, start_date, day)

-- Snowflake: (part, start, end)
datediff(day, start_date, end_date)

-- Postgres:  just subtract (returns integer days)
end_date - start_date

Always check before copying a query between warehouses. This is the source of "sign-flipped duration" bugs.

---

What's in the full reference

The free patterns above are ~5 pages of a 22-page printable reference. The full pack also covers:

• All window-function patterns — SCD-2 attribute-change detection, running totals, moving averages, gaps-in-sequence, dedup via row_number()
• CTE vs subquery vs temp table — when each wins (with a decision table)
• Reading query plans (EXPLAIN) — the 5 red flags that mean your query is broken
• Array/struct unnesting across BigQuery (UNNEST), Snowflake (FLATTEN), Postgres (jsonb_array_elements) — plus the LEFT JOIN trick that prevents silent row loss
• Date/time arithmetic comparison table — every operation across all 4 warehouses
• NULL handling in aggregates, joins, window functions — the full bug catalog
• Materialization-aware SQL — view vs table vs incremental, with a decision table
• Glossary of pipeline SQL vocabulary
• Pre-merge checklist for new analytics models

Coming soon to Gumroad + Etsy — $7.99, personal-use license, free 2-page preview.

(Listing in preparation. The patterns in this repo are free regardless.)

---

Who this is for

• Data engineers writing pipeline SQL daily
• Analytics engineers moving from dbt into raw SQL
• Backend engineers pulled into data work
• Interview prep — the patterns section covers what comes up

License

The patterns in this repo are free to use (CC0 — copy them, no attribution required, no warranty). The printable PDF pack is a separate commercial product sold under a personal-use license.

Contributing

Found a pattern that should be here? Open an issue. The goal is the shortlist of what actually comes up weekly in pipeline SQL, not an exhaustive reference.