The idea
The grain is what a single row represents: an order, an order line, a line version. Deduplicate on a key coarser than the grain and you delete real data. Order O1 with lines 1 and 2 is two valid rows, not a duplicate.
Once the grain is right, the survivor must be chosen by a rule, never by "whichever row came first". Write the ordering: latest version, then a tie-break that makes the order total, such as the smallest ingest id.
Rows you remove are evidence. Send them to exceptions with a reason like superseded or duplicate.
Worked example
Input: (O1, line 1, v1) (O1, line 1, v2) (O1, line 2, v1)
- Visual
Remove duplicates → keys order_id + line_id → order version descending
- SQL (DuckDB)
QUALIFY row_number() OVER (PARTITION BY order_id, line_id ORDER BY version DESC) = 1
- pandas
df.sort_values("version", ascending=False).drop_duplicates(["order_id", "line_id"])
Result: (O1, 1, v2) and (O1, 2, v1) survive; (O1, 1, v1) is superseded
Common mistakes
- Keys coarser than the grain.
- Survivors chosen by file order.
- Sorting versions as text (10 before 9).
Check yourself
Why not deduplicate on order_id?
An order has several lines; you would delete valid lines.
What if two rows tie on every order column?
Add a tie-break that makes the order total, such as the smallest ingest id.
Practice it
Keep the latest version of every order line.