Before this: Find the row grain before removing duplicates
The idea
Two identical rows can be two real purchases of the same item. Removing one understates sales.
Only deduplicate on a key that identifies one real event: an order line id, an event id, a transaction id.
If no such key exists, ask the source owner instead of guessing.
Worked example
Input: (order 7, item A, qty 1), (order 7, item A, qty 1) with line_id 1 and 2
- Visual
Add step → Remove duplicates → key order_id + line_id
- SQL (DuckDB)
... PARTITION BY order_id, line_id ...
- pandas
df.drop_duplicates(["order_id", "line_id"])
Result: Both rows stay: different line ids
Common mistakes
- DISTINCT on all columns.
- Dedup without a key.
- Guessing grain.
Check yourself
What key would make two identical-looking rows duplicates?
A shared event or line identifier.
Practice it
Deduplicate order lines at the right grain.