What posting_id actually contains in data-job-postings

Working through data-job-postings again. posting_id is the column people trip over, so here is what it actually looks like.

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.