A repeated order ID does not tell you which row to delete

By Dan Wang · AI-assisted technical note · Published September 22, 2026 · English revised September 24, 2026

This article describes a personal coding example built with synthetic data and AI assistance. It is not a client case study. The results come from local tests, not a production system.

An order export contains the same ID three times: twice with an amount of 29.00 and once with 290.00. Keeping only the first row removes the conflicting amount. Keeping only the last row has the same problem. The order of the rows does not tell us which amount is correct.

A cleanup tool should keep these conflicting records for review. It can remove identical rows when the agreed rules allow it, but a repeated ID alone does not show whether two rows represent the same order. Before deleting either row, someone needs to resolve the discrepancy.

Start with an example

The downloadable example contains twelve synthetic records:

All rows use USD. The refund row has status refund. All other rows have status paid, and the final row has extra spaces around that value. A script that keeps the first row for each ID reduces the twelve records to seven. It discards 290.00, removes one of the differently formatted amounts for ORD-1003, and merges the two rows with blank IDs. The CSV provides no basis for deciding that these changes are correct.

Compare all rows that share an ID

The example uses an existing Python script built with the standard library. In key_exact mode, it groups rows by the selected key columns: order_id in this case. For each group with a nonblank ID, it compares every field as text. If all rows are identical, it keeps the first and logs the removed copies. If any field differs, it keeps every row in the group and flags them all for review. Rows with missing IDs are kept and flagged separately.

For ORD-1002, this means keeping both copies of 29.00 as well as 290.00. Although two rows appear identical, this policy leaves the whole group intact until the conflict is resolved. That creates more work for the reviewer, but avoids deleting records from a group whose meaning is still uncertain.

The rule file specifies which changes are allowed:

{
  "confirmed": true,
  "trim_columns": ["status"],
  "required_columns": ["order_id", "currency", "amount"],
  "deduplicate": {"mode": "key_exact", "keys": ["order_id"]},
  "formula_export_mode": "preserve"
}

The only permitted change to a cell value is to remove leading or trailing whitespace from status. This happens before validation and row comparison. Adding other columns to trim_columns could therefore change which rows count as identical. The confirmed flag tells the script to apply these rules; setting it to true does not prove that a customer approved them or that they suit the data.

Run the example and check the output

Download the example and extract it into a new folder. It requires Python 3.10 or later; the original checks ran on Python 3.12.14. It needs no third-party packages, credentials, AI services or network connection. In that folder, run:

python3 reproduce.py
python3 -m unittest -v test_csv_delivery.py
python3 csv_delivery.py --input orders.csv --rules rules.json --output delivery

The last command creates the delivery folder and fails if that path already exists. Use a different output name when running it again. The first command, reproduce.py, uses a temporary folder and can be repeated without deleting earlier results.

The example produces eleven output records from twelve input records. It removes one duplicate, trims one cell and flags eight retained records for review. Those records have ten flags in total: five for conflicting rows, two for missing keys, two for empty required fields and one for a value that might be interpreted as a spreadsheet formula. Each row with a missing ID triggers both a missing-key flag and a required-field flag, which explains why there are more flags than affected records.

The audit log records that source record 2 was removed as a duplicate of record 1. It also maps each retained source record to its position in the output. These record numbers exclude the header. They may differ from line numbers in a text editor because a quoted CSV field can contain a newline.

The output folder includes source.csv, an exact byte-for-byte copy of the input, and a summary containing its SHA-256 hash. The generated cleaned.csv contains the permitted changes and may also use different quoting or line endings. It is therefore not a byte-for-byte copy. Keeping the original file and logging changes makes the two versions easier to compare.

What the flags do not prove

The script does not validate amounts as numbers. It flags 19.90 and 19.9 as a conflict because they differ as text. Treating them as equivalent would require an additional rule for parsing and formatting amounts. That rule would need to account for currency, precision and accepted number formats. Even then, parsing alone could not tell us whether 29.00 or 290.00 is correct.

The value not-a-number exposes another limitation: it is kept without a review flag. The field is nonempty, the order ID is unique, and none of the configured checks validates numeric content. An unflagged amount is therefore not necessarily valid. The example includes an assertion that checks this behavior.

The refund amount -5.00 does receive a warning because the formula detector flags values beginning with -, among other characters. This warning does not mean the refund is malicious. The script preserves these values; it does not make them safe for a spreadsheet to interpret. To inspect the output in a spreadsheet, use an import process that treats every field as text and disables formula interpretation, then review the warnings. Do not assume that opening the CSV by double-clicking it is safe.

What the tests establish

In the original verification, all seventeen tests in the existing suite passed, as did the assertions in reproduce.py. The suite covers retaining conflicts, missing keys, malformed input, preserving the original bytes, refusing existing output paths and handling a simulated write failure. It does not establish what happens during a process crash or power failure, or guarantee correct handling of every possible CSV file.

The script accepts UTF-8 input with no more than 1,000 data records and twenty columns. It checks that input records = output records + removed duplicates, and the audit log identifies each removed record. This accounts for the record count; it does not prove that the chosen rules are appropriate. In this example, keeping eleven records leaves the conflicting amounts and missing IDs available for review. Reducing the file to seven would hide those unresolved problems.