Which source creates duplicates and how to prove it with a simple query

Three teams in a room and no evidence. Marketing thinks the CRM sync creates the duplicates. Operations thinks it is the registration form. The one data person thinks it is the lead capture upload from last October and cannot prove any of it, so the meeting ends with an agreement to look into it.

Which source creates duplicates is answerable in an afternoon, and it needs one column you probably are not storing. Tag every record with the system that created it, cluster the duplicates as you already do, then group those clusters by the set of sources they span.

Tagging the record at the door

The tag has to be applied at ingest and never inferred afterwards.

Three fields, written by the loader: the source system, the identifier that system used for the record, and the timestamp the record arrived. Inferring the source later from a pattern in the data will work for about a year and then quietly stop, because a vendor changes an identifier format and every historic attribution moves with it.

Lineage is the fifth of the pillars of data observability Barr Moses set out for Monte Carlo in December 2020, after freshness, distribution, volume and schema, and she describes it as the most holistic of them, the one that tells you which upstream sources and which downstream systems an issue touches. Duplicate attribution is a small, cheap instance of exactly that, and it needs no graph and no vendor.

The standards world has a longer name for the same idea. The W3C's PROV Data Model, a Recommendation issued on 30 April 2013, defines provenance as a record describing the people, institutions, entities and activities involved in producing, influencing or delivering a piece of data. Your source tag is the smallest useful version of that record: which agent produced this row.

Which source is creating your duplicates?

Take a portfolio load of 128,000 person records across four shows, run the clustering, and suppose it produces 5,100 duplicate clusters.

Now group the clusters by their source signature, which is the sorted set of distinct sources the records in that cluster came from.

Three of those four rows point at completely different problems with completely different owners, and the room full of people blaming each other now has an order to work in. Nearly six in ten clusters never left the registration platform, which is a form problem. A quarter span registration and the CRM, which is an integration problem. One in eight sit inside the CRM, which is a sales hygiene problem and belongs to a different team entirely.

The query behind the table is a group by over a string built from the distinct sources in each cluster. If your clustering already writes a cluster id onto each record, it is about eight lines.

What does a cluster spanning two systems tell you?

That the two systems have no key in common, and the matcher had to fall back on names and email addresses.

When a registration record and a CRM contact describe the same buyer, one of two things is true. Either the integration carried an identifier across, in which case the join is exact and the pair was never a duplicate in the first place. Or it did not, in which case every difference in spelling, employer or email address produces a second person. The 1,240 clusters in that band are the visible cost of a missing identifier, and the fix is upstream in the integration. No amount of matcher tuning recovers a key that was never carried across.

That distinction changes who does the work. Tuning the matching thresholds to catch more of these pairs is cluster J's territory and will help a little. Writing the registration platform's person id into a custom field on the CRM contact at the moment of sync removes the whole band, permanently, and takes an afternoon of somebody's integration time.

Single system clusters carry the opposite message. The system had every opportunity to notice, and did not.

Splitting the single system clusters by time

The 3,020 registration only clusters are not one problem. Split them by the gap between the two creation timestamps and they come apart cleanly.

Suppose 2,180 of the 3,020 have both records created within ten minutes of each other, and 840 have a gap of more than thirty days. The first group is a form accepting a resubmission: a browser refresh on a slow payment step, a double click on submit, a person who was not sure the first attempt worked. The second group is a person genuinely registering again, weeks later, having forgotten, which is a different failure and needs a lookup on the form rather than a constraint in the database.

The ten minute rule is arbitrary and worth checking on your own file by plotting the gaps. On most registration platforms the distribution is obvious, with a spike under two minutes and then almost nothing until the following week.

Fixing in order of volume

Now the numbers do the prioritisation for you.

Stopping the double submit removes 2,180 of 5,100 clusters, which is 42.7 per cent of everything, and the work is a submission token on the form plus a unique constraint on the email within an event edition. Carrying the identifier across the CRM integration removes another 1,240, which is 24.3 per cent, so those two changes between them address roughly two thirds of the duplicate population.

Compare that with the instinct, which is to improve the matching. Better matching finds duplicates faster and joins them more accurately. It creates none of the fixes above, and next edition the same 2,180 double submits arrive again. Measuring where records are born is what lets you spend the quarter on the form instead of the model.

Publish the signature table next to the headline duplicate rate from K24, because a rate on its own invites the question this table answers, and somebody will ask it in the meeting where the rate is first shown.

Where source attribution goes wrong

Some sources are not sources. An aggregator feed that collects registrations from three media partners arrives tagged as one system, and every duplicate inside it looks like a form problem when the real cause is that two partners sent the same person. If a feed is itself a collection, you need a second field carrying the original channel, and you need to ask the aggregator for it before you build anything on top.

Backfills are the other trap. A historic import of five years of registrations done during a platform migration will carry the source tag of the migration job, so every duplicate created before the migration attributes to a system that did not exist when the records were made. Tag the migration explicitly, exclude it from the signature analysis, and say so in the caption.

There is also a limit on what this can prove. The signature says where the records were created, and creation is not always the same as cause. A CRM only cluster can be the product of a sales team keying a contact twice, or of an enrichment vendor writing back a second record under a slightly different company name, and the tag looks identical in both cases. Separating those needs whoever owns the CRM to look at twenty of them, which takes half an hour and is worth doing before anyone is blamed.

The counting also says nothing about the merges that were wrong. Grouping clusters by source assumes the clusters are correct, and some of them will have joined two different people, which is measured on a labelled sample in K25 and should be run against the same population before you make a case to a board.

Start by adding the source system column to your ingest this week, even before anything reads it. Then run the clustering on your most recent portfolio load and produce the signature table for whatever history you can already attribute. If more than half your clusters sit inside one system, your next quarter of unified data work is a form change, and you will have a number that says so.