Our monthly 'top 3 sellers per region' bonus report left out a sales rep who tied for third, and a rerun picked the other one. ROW_NUMBER breaks ties arbitrarily. We show how to pick RANK, DENSE_RANK or WITH TIES on purpose.
A pipeline you are afraid to re-run is a pipeline that will eventually corrupt data. This guide covers partition overwrite, MERGE upserts, dedup keys, watermarks and why exactly-once is a property you build, not a setting you enable.
Page 1 of our activity feed loaded in 9 ms, page 4,000 in 3.2 seconds, and a partner's sync job was walking every page. OFFSET makes Postgres read and discard every earlier row. We switched to keyset pagination with a tie-breaker and a matching index.
A new operations dashboard refreshed every 15 minutes and our BigQuery bill jumped by 9,000 USD in a month. Each tile ran SELECT star over a 40 TB events table without a partition filter. Column pruning, required partition filters and a bytes billed cap fixed it.
A churn model reached 0.97 AUC in validation and 0.68 in production. The training set joined customer features from the current snapshot, so it included support tickets and plan downgrades that happened after the prediction date. Point-in-time joins brought validation to an honest 0.81.
EXPLAIN ANALYZE prints a wall of nodes, costs and loops that most of us skim for the word Seq Scan. This guide walks a real slow query node by node: estimates vs actuals, loops, buffers, and the fixes each pattern points to.
Most of our worst backend incidents did not start with a big design flaw. They started with a swallowed exception, a missing index or an endpoint that returned everything. Here are ten habits we now block in code review, and what we do instead.
Most reporting queries that end up as nested subqueries or app-side loops are one window function away from simple. A practical tour of LAG for gaps, running totals, moving averages and the frame clause defaults that quietly give wrong numbers.
Our invoice list took 3 seconds even though a composite index covered both filter columns. The range column was first, and two other queries wrapped the column in a function or a cast. Reordering and rewriting fixed all three.