Five minutes with posting_id in data-job-postings

Someone asked what is in posting_id on data-job-postings, and the honest answer took one query.

3,500 rows, no nulls, 3,500 distinct values. Posting identifier.

3,500 distinct values across 3,500 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(posting_id)            as present,
  count(*) - count(posting_id) as nulls,
  count(distinct posting_id)   as distinct_values
from data_job_postings;

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 posting, 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.