Deduplicating a Training Corpus with Hashes

The first real stage of a training pipeline is throwing things away

A training corpus starts as a crawl, and a crawl of the open web is mostly the same pages over and over: mirrors, syndication feeds, CDN caches, aggregators republishing an article verbatim, and the same forum thread reachable at four different URLs. Deduplication removes 20 to 30 percent of raw web data, before any other quality work happens. Falcon's RefinedWeb pipeline took roughly 1 billion crawled pages down to 2.8 TB of clean text, and dedup did a large share of that reduction.

The reason this matters is not disk space. A document that appears four times is seen four times during training, which quietly reweights the corpus toward whatever happens to be mirrored the most. And a duplicate is one of the ways evaluation text sneaks into training data, which is how a benchmark score stops meaning anything.

Exact dedup is a hash plus a keep rule

An exact duplicate is not "two documents that look alike". It is two documents whose bytes are identical, which the pipeline knows because the extract stage already stamped every document with a content hash. Two rows with the same content_sha256 hold the same content, full stop, no matter how different their URLs look.

So exact dedup in SQL is two decisions and nothing else:

1. Group by the hash. That is the duplicate set.
2. Pick one survivor per group. That is the keep rule, and it is a business decision, not a technical one.

The keep rule needs to be deterministic, which means it must break every tie it can hit. "Keep the earliest crawl" is not enough on its own, because two mirrors can be captured on the same day, and if two rows tie on the ordering column then which one survives depends on how the engine happened to scan the table. That is why the canonical form carries a second ordering column:

ROW_NUMBER() OVER (
  PARTITION BY content_sha256      -- the duplicate set
  ORDER BY crawl_dt, doc_id        -- the keep rule, plus the tiebreaker
) AS rn

Then WHERE rn = 1 keeps exactly one row per hash. Every row gets a number inside its own hash group, the lowest number goes to the earliest crawl, and doc_id settles the days where two copies arrived together.

You have written this query before. SQL Level 4 graded it on a customer staging table, keeping one latest row per email, and this course has graded the same keep-one twice since: Level 8's append-only bronze ranks redeliveries and keeps rn = 1 in the read view, and Level 9's read-side dedup does it again over an at-least-once stream. It is the single most-cited SQL task in the interview corpus behind this course: windowed dedup with ROW_NUMBER and a composite tiebreaker. Nothing about it changes because the rows are now web pages destined for a model. That is the whole point of this level: the AI-era stage is the pattern you already own, pointed at a new table.

Fuzzy and semantic dedup are the same idea, at higher cost

Exact hashing only catches byte-identical copies. Change one timestamp in a footer and the hash changes completely, so the two curation stages that follow relax the definition of "same":

Table

Three definitions of duplicate at increasing cost. Every pipeline runs exact dedup; fuzzy is common; semantic is a budget decision.

stage	what counts as a duplicate	how it is computed	cost
exact	identical bytes	GROUP BY a content hash, keep one	one pass, cheap enough to always run
fuzzy	mostly the same words	MinHash signatures, LSH buckets, then Jaccard overlap inside a bucket	many hashes per document plus a bucketed comparison
semantic	the same meaning in different words	embed every document, cluster the vectors, keep one per cluster	an embedding call per document plus a clustering pass

Fuzzy dedup (MinHash plus LSH plus a Jaccard threshold) is the workhorse of the three: MinHash turns each document into a small signature, LSH puts similar signatures in the same bucket so you never compare every pair, and Jaccard overlap makes the final call inside a bucket. None of that is expressible in a graded SQL exercise, and it does not need to be. What an interviewer wants to hear is the ladder: exact first because it is nearly free, fuzzy next because near-duplicates are the bulk of the remaining waste, semantic only if the corpus and the budget justify it.

Materialized dedup versus dedup at read

Level 9 already made you choose between absorbing duplicates at the write and absorbing them at the read, and Level 8 graded the read-side half of it directly, so you know both sides of this tradeoff. What curation adds is a reason the choice is not close: read amplification. A training job scans the corpus tens of times, so materializing the survivors into their own table means paying the window function once at build time instead of paying it on every scan. Dedup at read would charge that cost to every reader, forever, on a table whose contents stop changing the moment the crawl is processed.

So curation pipelines materialize. The practice below writes exactly that table, and it has to survive being run twice, which is the part that catches people: a dedup job that duplicates its own output on a rerun is a joke that writes itself.

Common mistake: deduplicating on the URL instead of on the content hash. The URL is the one thing every mirror changes. https://blog.example.com/parquet-guide and https://cdn.example.net/cache/parquet-guide are different strings holding the same bytes, and a URL-keyed dedup keeps both while a hash-keyed dedup keeps one.

Interview nuance: when you write the ROW_NUMBER, say the tiebreaker out loud. "I partition by the content hash and order by crawl date, then doc_id, so the result is deterministic even when two copies land on the same day" is the sentence that separates someone who has run this in production from someone who has seen it on a slide. Then add where the output goes, materialized table or view, and why.

On a real platform this differs. Here the corpus is 17 rows in SQLite and the hash is a column somebody already filled in. NVIDIA's NeMo Curator runs this stage over JSONL or Parquet shards on object storage with the hashing done in the extract stage, and at that scale the dedup is distributed: hash, shuffle rows that share a hash to the same worker, keep one per group. Spark's dropDuplicates makes the first decision, group by the hash, and skips the second: it keeps whichever copy the engine happened to hold, and you get no say. That is why production jobs write the row_number window over a Delta table instead, exactly as you did here. Of the two it is the only one that lets you name the keep rule.