Library

Pattern

A histogram in SQL

Count values per bin of a fixed width, keeping the empty bins.

Pattern

Allocation with a remainder

Spread an amount over rows by weight, rounded to the cent, with the remainder on one chosen row so the parts add up exactly.

Pattern

Anti-join

The rows of one table that have no match in another: customers who never ordered.

Pattern

Apply a change feed

Reduce a batch of changes to the last one per key, deletes included, then apply it with one MERGE.

Pattern

Counting business days

Build the working days once (no weekend, no holiday), then count them between two dates.

Pattern

Effective-dated lookup

Find the rate, price or version valid on the date of each fact, from a table that stores changes.

Pattern

Gaps and islands (streaks)

Group consecutive days (or numbers) into runs: streaks, sessions, periods.

Pattern

Invariants as tests

A rule that must hold on every row, or between a model and its source, written as "the rows that break it".

Pattern

Latest row per key (deduplication)

Keep one row per key, the most recent one.

Pattern

Merging overlapping periods

Merge periods that overlap or touch into continuous ones, with a running maximum of the end dates.

Pattern

Peak concurrency

How many things were active at the same time, at most: +1 at each start, −1 at each end, then a running total.

Pattern

Quarantine table

Bad rows go to a separate table with the rule they break, instead of being filtered out and forgotten.

Pattern

Running total

Add up a measure from the first row to the current one.

Pattern

Semi-join

The rows that have at least one match, each kept once.

Pattern

Walking a graph without looping

A recursive query over links that can loop must carry its path and refuse to revisit a node.

Pitfall

Convert, then truncate

Cutting a UTC time into days, hours or months gives UTC days: convert to the reader's time zone first.

Pitfall

COUNT(*) vs COUNT(column)

COUNT(column) skips NULLs; COUNT(*) counts rows.

Pitfall

Dirty data

A messy file raises no error: every query runs, and the answers are wrong. Check a new table before trusting it.

Pitfall

Integer division

7 / 93 is 0 when both sides are integers.

Pitfall

Join fan-out

Joining two one-to-many tables multiplies rows, and sums grow with them.

Pitfall

NOT IN with a NULL

One NULL in the list and NOT IN returns no rows at all.

Pitfall

Not observable yet

A result that has not had time to happen is unknown, not zero: young cohorts and the current period look like a collapse.

Pitfall

Simpson's paradox

A group can be better in every segment and worse overall, when the segments weigh differently.

Pitfall

Soft delete and NULL

WHERE NOT is_deleted also drops the rows where the flag is NULL.

Keyword

LEFT JOIN

Keep every row of the left table, even when nothing matches on the right.

Keyword

MAX_BY and MIN_BY

The value of one column on the row where another column is largest (or smallest), in a plain GROUP BY.

Metric

Average basket

Revenue divided by the number of orders, over a period.

Metric

Cancellation rate

Cancelled orders divided by all orders, as a percentage.

Metric

Explaining a change

Split a metric's change into parts that add up to it: volume against value, mix against rate, then by segment.

Metric

Revenue

One definition for the whole coffee shop: quantity * unit_price of the order lines, cancelled orders excluded.

Metric

Sample ratio mismatch

Before reading an experiment, check that its groups have the sizes they were meant to have.

Modeling

Data Vault: hubs, links, satellites

Hubs hold business keys, links hold relationships, satellites hold attributes and their history.

Modeling

Normalize or denormalize?

Store each fact once where data is written; copy it where data is mostly read.

Modeling

Staging, intermediate, marts

Each definition lives in one layer: names and types in staging, shared logic in intermediate models, business entities in marts.

Modeling

The grain of a fact table

What one row of a fact table represents. Decide it first.