Is subject_id + arm the grain of clinical-trial?

Quick check on clinical-trial: 900 rows, and the question is what makes one row unique.

subject_id on its own has 900 distinct values across 900 rows, so it is unique by itself. Adding arm gives 900 distinct combinations, which matches the row count exactly.

select subject_id, arm, count(*) as n
from clinical_trial
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 900 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.