Deduplicating records that arrive from two sources

Why two sources make deduplication harder than one

Dedup inside a single pipeline is mostly a solved problem. You control the schema, the timing, and the write path, so a unique constraint on a natural key usually does the job. The moment a second source enters the picture, that assumption breaks. We run this constantly: two scraping jobs hitting the same target through different proxy pools for redundancy, or a primary scraper backed by a secondary API feed that covers the same listings when the scraper gets rate-limited. Both sources are telling the truth about the same entity, but they disagree on formatting, timing, and sometimes on the value itself.

The core problem is that “same record” is a judgment call, not a database fact. Source A might return a phone number as +65 9123 4567 and source B as 912345 67. Source A timestamps a listing update at the moment its scraper ran; source B timestamps it at the moment the underlying page changed. Neither is wrong. A dedup layer that only checks for exact string equality will let both records through and quietly double your row count.

The three shapes duplicates take

Exact duplicates are the easy case: identical payload, identical key, different arrival time. A unique index catches these for free.

Near duplicates are the common case with two sources: same real-world entity, different representation. Different whitespace, different casing, a trailing country code, a price stored as 19.99 versus 19.990000. These pass any exact-match check and still represent one entity.

Conflicting duplicates are the dangerous case: same entity, genuinely different values, because the two sources scraped the target at different moments and the underlying data changed in between. That’s a freshness problem, and it needs a different fix than the first two. Normalizing won’t touch it.

Treating all three the same way is the most common mistake we’ve seen in pipelines that bolt dedup on as an afterthought. A single SELECT DISTINCT or a single hash comparison only solves the first shape.

Building a stable identity before you can dedupe

You can’t deduplicate what you can’t identify. Before any matching logic runs, every incoming record needs a canonical key that doesn’t depend on which source produced it. For scraped data this is usually a composite of a few normalized fields: a lowercased, trimmed identifier plus a domain, or a product ID plus a marketplace region, rather than anything the source itself assigns (row IDs, internal database IDs, or timestamps are useless here because the two sources will never agree on them).

Normalization has to happen before matching, not after. Strip non-numeric characters from phone numbers, lowercase and trim strings, round currency to a fixed number of decimal places, resolve relative URLs to absolute ones. This step is boring and it’s where most dedup pipelines actually live or die. If source A stores a URL with a trailing slash and source B doesn’t, and you hash the raw string, you’ll insert the same page twice forever.

Once you have a canonical key, that key is what gets hashed or indexed, never the raw payload.

Hashing the record vs hashing the key

There are two different hashes worth keeping, and conflating them causes bugs.

A key hash (or the canonical key itself) tells you “is this the same entity.” This is what your unique constraint or your dedup index is built on. It should be stable even if every other field on the record changes.

A content hash tells you “has anything about this entity changed since I last saw it.” This is a hash of the normalized payload, excluding volatile fields like scrape timestamp or proxy IP used. When a new record comes in with a key hash that already exists but a different content hash, that’s your signal to update the row instead of inserting a new one, and it’s also your signal to log that the two sources disagreed, which is worth keeping visible rather than silently overwriting.

Skipping the content hash and just doing an upsert on the key hash works, but it means you lose the ability to tell “nothing changed” from “source B just overwrote source A’s fresher value with stale data.” That distinction matters when the two sources aren’t equally reliable.

Where to dedupe: at ingestion, in the pipeline, or at rest

Dedup at ingestion, before a record is written anywhere, is the cheapest place to catch it, but it requires the two sources to write through the same choke point, which isn’t always true when they’re separate scraping jobs running on separate schedules or separate infrastructure.

Dedup in the pipeline, using a message queue with a dedup window (Kafka’s idempotent producer and dedup-by-key patterns, or a Redis set of recently seen key hashes with a TTL) catches duplicates that arrive close together in time. This is the right layer when the two sources are near-simultaneous, for example two scrapers running the same crawl on a schedule offset by a few minutes.

Dedup at rest, via a unique constraint plus an upsert, is the backstop. It’s also the only layer that reliably catches duplicates that arrive hours or days apart, which is exactly what happens when one source is a live scraper and the other is a periodic bulk export. We keep this layer even when the earlier layers are in place, because pipeline-level dedup windows are finite by design and anything that outlives the window slips through.

Relying on only one of these layers is the second most common failure we’ve seen. Ingestion-only dedup misses anything that comes from a genuinely separate process. Database-only dedup means you’re paying storage and processing cost for records you’re going to throw away anyway.

Merge logic when the two sources disagree

Once you can detect a conflict (same key hash, different content hash), you still have to decide which value wins. There’s no universal answer, but a few patterns cover most cases.

Last-write-wins by source timestamp works when both sources timestamp accurately and clocks are trustworthy. It fails when one source’s timestamp reflects scrape time rather than the actual change time on the target, which is common with scraped data. In that case, last-write-wins will happily let a stale scrape overwrite a fresher one just because it happened to run later.

Field-level merge, where you pick the more trusted source per field rather than per record, works well when one source is authoritative for some fields and the other fills gaps. A common real setup: a scraper is fast but occasionally misses fields when a page layout shifts, while a slower official API is complete but rate-limited to a fraction of the volume. Letting the API source win on any field it actually returns, and falling back to the scraper otherwise, gets more accuracy than picking one source wholesale.

Keeping both values with provenance, rather than collapsing to one, is the right call when the conflict itself is the interesting signal, for example price tracking where a discrepancy between two sources might mean one of them is stale rather than wrong.

Whatever rule you pick, log the conflicts you overwrote. A merge rule that silently discards disagreements makes it impossible to audit later why a downstream number looks off.

Timing problems: clock skew, late arrivals, and out of order writes

Two independent sources rarely agree on clocks, and “arrived later” doesn’t always mean “is more current.” A scraper behind a slow proxy can take minutes longer to return a page than a fast one hitting the same target, so the record that lands in your queue second might describe an earlier state of the page. If your merge logic trusts arrival order instead of a timestamp taken from the actual content (a “last updated” field on the page itself, not a scrape timestamp), you’ll get intermittent flapping where a field alternates between an old and new value depending on which proxy pool happened to be faster that hour.

The fix is to always prefer a timestamp intrinsic to the data over one extrinsic to your pipeline, when the source provides one. When it doesn’t, treat arrival order as a weak signal, not a strong one, and lean on field-level trust rules instead.

What we run in production

For our own pipelines that pull from a primary scraper plus a secondary fallback source, the shape is: normalize both feeds through the same canonicalization function before either touches storage, compute a key hash and a separate content hash, dedup against a short in-pipeline window (minutes) to absorb near-simultaneous writes, then rely on a unique constraint at the database layer with an upsert-on-conflict as the permanent backstop. Conflicts get written to a small log table rather than silently discarded, because more than once that log has been the first sign that one of the two sources drifted out of sync with the actual target site.

This is mostly discipline: normalizing before hashing, separating identity from freshness, and never trusting a single layer of dedup to catch everything. Nothing exotic about it.

When dedup fails quietly

The failure mode worth watching for is a slow doubling of row counts that nobody notices until a report looks off. Nothing actually crashes. If you’re merging two sources and haven’t checked your duplicate rate in a while, it’s worth running a one-off query grouping by your canonical key and counting rows per group. A number consistently above one tells you which layer is leaking, and it’s almost always a normalization gap rather than a logic bug.

If you’re building or debugging a pipeline like this, we cover the orchestration and infrastructure side in more depth over at Data Research Tools.

Get new guides and videos first — join the Telegram channel.