SQL, data modeling, data quality, EDA, visualization · insight-1066
Sample by a hash of the entity key so a sample stays consistent across tables and reruns
- Evidence type
- practitioner heuristic
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
You need a 5% development sample of bookings and their payments, and sampling each table with its own random draw leaves most bookings without their payments.
Action
Sample entities, not rows: keep rows where a stable hash of the entity key falls under the threshold, apply the same predicate in every table, and record the hash function and salt. Compare the sample's key marginals with the full table.
Reason
A deterministic function of the key puts every row of an entity on the same side of the cut in every table and every run.
Boundary
Development sandboxes, feature prototyping, notebooks on warehouse data and repeatable experiments on subsets.
Symptom or risk
Independent row sampling keeps each row with probability p, so a pair survives with probability about p squared, joins collapse and per-customer features are computed on partial histories.
Exceptions
Row-level questions that do not need entity histories, where Bernoulli sampling is fine.
Trade-offs
Hash sampling inherits any clustering of the key and gives sample sizes that vary by entity size.
Pitfalls
Hashing a key that correlates with the target, for example a sequential id that follows signup date.
Lessons and concepts
Claims and their sources
PostgreSQL's TABLESAMPLE draws a new random sample per query unless REPEATABLE with a seed is given, and reproduces a sample only while the table is unchanged.