Reading subject_id before trusting it in clinical-trial

Profiling subject_id on clinical-trial before anyone builds anything on top of it.

900 rows, no nulls, 900 distinct values. Subject identifier.

900 distinct values across 900 rows, so it is unique and a candidate key.

Uniqueness is a property of the data you have, not a guarantee about the data you will get. Nothing in the file enforces it; a re-export with an extra row, a merged upstream table, or a backfill that runs twice all break it, and none of them raise an error.

select
  count(*)                     as rows,
  count(subject_id)            as present,
  count(*) - count(subject_id) as nulls,
  count(distinct subject_id)   as distinct_values
from clinical_trial;

Unique today is not the same as declared unique. If nothing tests it, the day it stops being unique is the day a join starts fanning out quietly.

Where this bites: the failure is silent in both directions. A duplicated key produces too many rows and a total that is too high; a missing one produces too few and a total that is too low. Neither errors, and both look like a real change to whoever reads the number.

The grain is one row per enrolled subject, which is the context every one of those numbers depends on. None of them survive a change of grain, which is why "profile the column" and "profile the table" are the same job. Full schema, and the CSV, on the dataset page.