Counting rows is not testing grain

Every table has a sentence describing what one row is. On flight-delays it is "one row per scheduled flight". Here is the test that the data agrees.

flight_no on its own has 4,715 distinct values across 8,000 rows, so it repeats — an average of 1.7 rows per value. Adding origin gives 7,690 distinct combinations, still short of the 8,000 rows.

select flight_no, origin, count(*) as n
from flight_delays
group by 1, 2
having count(*) > 1
order by n desc
limit 10;

That query returns rows, so the pair is not the grain — there is at least one more column in the key. Joining on it as if it were unique multiplies rows on the other side, and the failure shows up as a total that is too high rather than as an error.

The having count(*) > 1 formulation matters more than it looks. A bare count(distinct ...) tells you whether the key holds; this tells you which rows break it, which is the difference between knowing you have a problem and being able to fix it. Sort by n desc and the worst offender is the first row — usually enough to identify the cause without another query.

Two things this test does not tell you. It does not say whether the duplicates are wrong: some tables legitimately carry a history, and the fix there is to add the version column to the key rather than to delete rows. And it says nothing about the future — a key that holds across 8,000 rows today can break on the next load, which is the argument for running it on a schedule rather than once during exploration.

Until the real key is known, treat every aggregate over this table as provisional. The safe move is to aggregate to a grain you have verified before joining anything to it — that converts an unknown multiplier into an explicit one.

A grain claim you have not tested is a comment, not a constraint. It takes one query to convert it, and the test costs nothing to run nightly. The pattern has the version we ship, and Rows, grain, and the shape of a dataset is the long form.