SQL, data modeling, data quality, EDA, visualization · insight-1064
Break ties explicitly when choosing the latest row per key
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A customer table receives two updates in the same second, and the "latest" row chosen with DISTINCT ON or ROW_NUMBER ORDER BY updated_at differs between runs.
Action
Add a deterministic tie-breaker to the ORDER BY: a monotonically increasing source sequence, ingestion offset or version number, then the key itself as a last resort. Test by shuffling input order and comparing outputs.
Reason
The first row of a group is unpredictable unless the ORDER BY fully determines the order.
Boundary
Latest-record dedup, current-state tables built from CDC and snapshots with second-level timestamps.
Symptom or risk
With equal sort keys the engine picks an arbitrary row, so the chosen version, and every number downstream of it, can flip between reruns without any code change.
Exceptions
Sources with strictly unique, high-resolution timestamps.
Trade-offs
Requires a trustworthy sequence column from the source or the loader.
Pitfalls
Using the processing timestamp as the tie-breaker, which encodes arrival order and not event order.
Lessons and concepts
Claims and their sources
In PostgreSQL DISTINCT ON keeps the first row of each group, and the first row is unpredictable unless ORDER BY determines it.