Why I started
I started building a CSV comparison tool for my own use and to explore a business idea. I wanted to see where I could improve on standard comparison tools.
One thing I found frustrating was ambiguous matches. Before comparing values, you need to know which rows belong together.
What makes a match ambiguous?
Here's a made-up example to illustrate the problem.
Suppose these are two exports, and I'm using customer_id to match rows.
First export
customer_id |
item | amount |
|---|---|---|
| 101 | Hosting | 20 |
| 101 | Support | 50 |
Second export
customer_id |
item | amount |
|---|---|---|
| 101 | Support | 55 |
| 101 | Hosting | 20 |
Both files have two rows with customer_id 101. That ID identifies the customer, but it doesn't identify an individual row.
Matching by row order would compare Hosting with Support and report misleading changes. Looking at the item names makes the likely matches clearer, but customer_id alone doesn't provide enough information.
How I approached it
I settled on matching rows by unique keys, then showing additions, removals, and changes.
For the example above, a subscription ID could identify each record:
subscription_id |
customer_id |
item | amount |
|---|---|---|---|
| sub-001 | 101 | Hosting | 20 |
| sub-002 | 101 | Support | 50 |
If those subscription IDs stay the same in the next export, the rows can be matched even when their order changes. The comparison can then show that Support changed from 50 to 55.
The key needs to be unique within each file and identify the same record across both files. A row number might be unique, but it won't reliably identify the same record after rows are reordered.
My approach is to reject duplicate keys rather than guess. The tradeoff is that the person comparing the files needs to find a suitable key or prepare the data first.
What I'm still trying to understand
Tracking rows by unique keys to show additions, removals, and changes is the part I find most useful so far.
- What kinds of datasets do you compare repeatedly?
- How often do you compare them?
- What do you use to identify the same record across exports?
- What do you do with the differences afterward?
I'd be interested in concrete examples, especially where matching gets messy or your current tools fall short.
Written from my own notes, with AI assistance for organization, wording, and the illustrative example.
Top comments (2)
Nice write-up. One thing that helped me in a similar spot: sort the ambiguous rows by a content hash before matching, so the pairing is deterministic when keys collide. Then surface the collision to the user instead of silently picking one. Letting a human break the tie beats a wrong guess every time. Good luck with Prism.
Thanks for this. Repeatable pairing can still be the wrong pairing. Prism currently rejects duplicate keys and asks the user to choose a unique one. I'd rather stop and make the ambiguity visible than return a comparison that looks right but isn't.