Counting rows is not testing grain

Every table has a sentence describing what one row is. On movie-ratings it is "one row per user-film rating". Here is the test that the data agrees.

user_id on its own has 1,100 distinct values across 9,000 rows, so it repeats — an average of 8.2 rows per value. Adding rated_at gives 9,000 distinct combinations, which matches the row count exactly.

select user_id, rated_at, count(*) as n
from movie_ratings
group by 1, 2
having count(*) > 1
order by n desc
limit 10;

That query returns nothing, so the pair is the grain. Worth writing down as a test rather than remembering: the day it starts returning rows is the day every join on this table starts fanning out, and nothing will 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 9,000 rows today can break on the next load, which is the argument for running it on a schedule rather than once during exploration.

Where a grain test earns its place is at the boundary. Run it on arrival, before anything downstream builds on the assumption, and the failure lands on the person who can fix it instead of on an analyst three models later wondering why revenue doubled.

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.