Ciaren

Blog

Deduplicate Revisions with a Clear Winner

Keep one revision per supplier and SKU with a multi-column sort, an explicit tie-breaker, and checks that catch conflicting revisions before deduplication.

About this post

Published
Reading time
3 min
Author
Ciaren team
deduplicationdata-qualitytutorial

A supplier sends two updates for the same SKU at 09:00:00. One says the item is available and the other says it is discontinued. If you sort only by update time and keep the first row per SKU, the timestamp gives you no basis for choosing between them.

Suppose the feed supplies a numeric revision and guarantees that the higher revision wins when timestamps tie. That rule makes it possible to choose the same record each time the same data arrives, even when the file is shuffled.

Decide which rows compete

This file combines feeds from several suppliers, so the record key is the pair supplier_id and sku. Timestamps in the file are UTC. The rows are shown in file order.

supplier_idskuupdated_at (UTC)revisionstatus
S04LAMP-12026-11-10 09:00:009available
S17LAMP-12026-11-10 08:30:003backordered
S04LAMP-12026-11-10 09:00:0010discontinued
S04CORD-22026-11-10 08:00:002available
S04LAMP-12026-11-09 15:00:008backordered
S04CORD-22026-11-09 08:00:001backordered

S04's revision 8 appears after revision 10 in the file, even though its source timestamp is from the day before. S17's LAMP-1 has its own history and should survive alongside S04's.

Sort the candidates, then keep the first

  1. After File Input, make sure updated_at is a datetime and revision is an integer. If both arrive as text, use Parse dates with %Y-%m-%d %H:%M:%S and Change types for the revision. Text sorting can put 9 ahead of 10 in descending order. Parse dates · Change types
  2. Use Assert not null in error mode for both key columns, the timestamp, and the revision. If the supplier can replay a row, remove exact duplicates across all columns first. Then use Assert unique in error mode on supplier_id, sku, updated_at, and revision. Two differing rows with that same combination have equal rank. Stop there so neither status wins by chance. Assert not null · Assert unique
  3. In Sort rows, select updated_at first and revision second. Set the order to descending. This puts the most recent update first and the highest numeric revision first when timestamps tie. Rows from different suppliers can interleave. The next step uses the record key to find the first row for each supplier and SKU. Sort rows
  4. In Remove duplicates, set the subset to supplier_id and sku and set keep to first. Put Assert unique on the same pair after this step to check the output. To display the survivors in the order below, sort by supplier and SKU, both ascending. Remove duplicates · Assert unique
supplier_idskurevisionstatus
S04CORD-22available
S04LAMP-110discontinued
S17LAMP-13backordered

Test the survivor as well as the count

Connect the final node to a CSV File Output named latest-revisions and run the flow twice, reversing the input rows for the second run. The output should still have these three supplier and SKU pairs, with revision 10 and the status discontinued for S04's LAMP-1. A check on the number of output rows alone would also pass if the workflow kept revision 9.

If your supplier also promises that a revision number appears only once per supplier and SKU, check those three columns before sorting. That stricter rule catches a reused revision number even when the timestamps differ.