Patterns

One-to-many join silently duplicates the "one" side's measures.

A join to a table with more than one row per key multiplies every measure on the other side. Nothing errors, and the number is plausible.

row_number() over the key, ordered by recency, filtered to 1.

Keep the best row per key with a window function instead of a self-join or a GROUP BY that loses columns.

value − row_number() is constant within a consecutive run.

Group consecutive runs — of dates, of statuses, of anything — by subtracting a row number from the value.

Cumulative sum of a 0/1 "new group starts here" flag becomes a group id.

Turn a stream of timestamped events into sessions by flagging the gaps and cumulative-summing the flag.

Left join from a complete date series to the facts, not the other way round.

Join to a generated calendar so days with no activity appear as zero instead of vanishing.

sum(case when key = 'x' then value end) — one column per value you want.

Reshape long to wide with CASE inside an aggregate. Portable, readable, and it works everywhere.

NOT EXISTS, never NOT IN against a nullable column.

Find rows with no match on the other side — and avoid the NOT IN trap that silently returns nothing.

Filters on the right table belong in ON, not WHERE.

A predicate on the right table in WHERE silently discards the unmatched rows you wrote a left join to keep.

DELETE the window and INSERT it, in one transaction, parameterised by date.

Every write replaces a named slice rather than adding to a pile, so rerunning a job is safe and backfilling is routine.

MERGE … WHEN MATCHED AND source.updated_at > target.updated_at.

Upsert on a natural key, and refuse to let an out-of-order replay overwrite good data with stale data.

Replace a rolling window, not just yesterday. Publish the restatement period.

Reprocess the last N days every night so late-arriving events are picked up instead of landing in a closed window.

hash(natural key columns) — stable across reruns, machines and backfills.

Derive the key from the content instead of an auto-increment, so reprocessing the same row produces the same key.

One check at ingestion protects every downstream model.

Validate the shape of incoming data where it enters your system — fail loudly on structure, warn on distribution.

The grain sentence, written as an assertion that runs.

One test per model, on the key that defines its grain. The cheapest quality control available and the one that catches the worst bugs.

Close the old row, insert a new one, and let facts point at the version that was current.

Keep every version of a row so "what was true in February" stays answerable after March changes it.

Never publish a mean of money, duration or counts without the median next to it.

Report both. The gap between them is a free skew diagnostic, and it tells the reader which number to trust.

Always publish the count, the denominator, and the interval.

A percentage without a denominator is not a finding. The Wilson interval behaves sensibly at small n, where the normal approximation falls apart.

Resample with replacement, recompute, take the percentiles of the results.

When you cannot remember the standard error formula — or there is not one — resample. It works for medians, percentiles and ratios alike.

Cohort month × period number, with censoring made explicit.

Group by when they started, measure how many remain N periods later — and remember that the bottom-right corner is young, not good.

(treated after − treated before) − (control after − control before).

When a change hits one group and not another, compare the change over time rather than the levels. Any time-invariant difference cancels out.

Holm–Bonferroni over the family of tests you actually looked at.

Twenty segments at α = 0.05 gives you a one-in-two chance of a false finding. Correct for it, or at minimum say how many you ran.

For every feature, state when its value becomes known. Anything after the cutoff is leakage.

Every feature must be computable from information available at prediction time. Write down each one's timestamp, and the leakage disappears.

Time-ordered folds with a gap, never a random shuffle.

Validate the way you will deploy — train on the past, predict the future, and leave the gap your real prediction lag imposes.

A rule, a last value, or the majority class — measured before anything is trained.

Build the dumbest thing that could work and measure it properly. Your model must beat it on the same split, or it has done nothing.