Before this: Find the row grain before removing duplicates
The idea
When several rows describe the same thing, a survivor rule picks one. The rule is an ordering inside each key: newest instant first, then highest priority, then smallest id.
The last sort key must be unique. Without it, two rows tied on every other field can swap places between runs, and reports change for no reason.
Compare typed values. Priority 10 must beat 9, so convert before ordering, and convert timestamps to instants. Rows that lose go to the exception report as duplicates.
Worked example
Input: account 00042: (updated 2026-01-01T10:00Z, priority 9, id r2), (2026-01-01T12:00+02:00, priority 10, id r1)
- Visual
Add step → Remove duplicates → key account → order updated desc, priority desc, id asc
- SQL (DuckDB)
QUALIFY ROW_NUMBER() OVER (PARTITION BY account ORDER BY CAST(updated AS TIMESTAMPTZ) DESC, CAST(priority AS BIGINT) DESC, id) = 1
- pandas
# after pd.to_datetime(updated, utc=True) and priority.astype("int64") df.sort_values(["updated", "priority", "id"], ascending=[False, False, True]).drop_duplicates("account")
Result: Both are 10:00Z (a tie on time), so priority decides: r1 (priority 10) survives and r2 is reported as a duplicate
Common mistakes
- Ordering by text.
- No unique tie-break.
- Deleting losers instead of reporting them.
Check yourself
Why add id as the last sort key?
It is unique, so ties are impossible and the survivor never changes between runs.
Practice it
Pick one surviving record per account.