Deduplication

Overview

Deduplication is the process of finding and removing repeated records from a dataset. The repeats can be exact copies, where every value matches, or near duplicates, where the records are almost the same but differ in small ways such as spacing, case, a typo or a changed timestamp. For machine learning, deduplication protects a model from memorizing repeated examples, saves training compute, and keeps test sets honest by making sure the same record does not appear in both training and evaluation data.

The core recipe is simple. Turn each record into a comparable form, group records that look alike, decide which member of each group to keep, and remove or merge the rest. The hard part is scale and judgment: comparing every record with every other record is too slow for large data, so practical systems use hashing and indexing tricks, and deciding what counts as "the same" depends on the task. For models that learn from raw tables, such as those described on /model/architecture, deduplication happens at two levels: duplicate rows inside a table, and duplicate or overlapping tables across the whole pre-training collection and the benchmarks used to judge it.

What it is

Deduplication, often shortened to dedup, means detecting records that represent the same thing and keeping only one of them. It is used in databases, data warehouses, backup systems, search engines and machine learning pipelines. The word covers several related jobs, so it helps to separate them.

Types of duplicates

Levels of deduplication

• Row level: remove repeated rows inside one table.
• Entity level: merge rows that describe the same real-world entity, such as a person or a company. This is also known as entity resolution or record linkage.
• Table level: detect that two tables are copies or versions of each other.
• Corpus level: find overlap between a large training collection and any evaluation set.

Why people care

Duplicates are easy to create. Retried loads, repeated exports, joins that fan out, scraped pages that are mirrored across sites, and the same public dataset uploaded under several names all produce them. Left alone, they distort counts, bias statistics, inflate accuracy and waste money.

An everyday analogy

Think of a photo library after years of phone backups. Some photos are exact copies. Some are the same shot saved at two sizes. Some are burst shots that differ by a blink. Cleaning the library means deciding which of these you consider "the same photo", then keeping the best one from each group. Dedup in data works the same way, and the key decision is how similar is similar enough.

Where it came from

Duplicate detection has a long history in several fields, and each contributed ideas that are still in use.

Record linkage and entity resolution

In the middle of the twentieth century, statisticians and census workers faced the problem of matching records of the same person across different lists. A well known probabilistic framework for this, published in 1969, described how to weigh agreement and disagreement on each field to decide whether two records match. This line of work became the field of record linkage, later called entity resolution in computer science.

Databases and storage

Database systems added unique constraints, primary keys and distinct operators to prevent or remove exact duplicates. Storage and backup systems added block level deduplication, where data is split into chunks, each chunk is hashed, and identical chunks are stored once. Content defined chunking made this robust to small shifts in the data.

Web search and text

Search engines needed to avoid showing the same page many times. Work from the late 1990s introduced shingling, where a document is turned into a set of overlapping word sequences, and MinHash, a way to estimate the overlap between two sets from small signatures. Together with locality sensitive hashing, these methods made near duplicate detection feasible on billions of pages. A related idea, SimHash, compresses a document into a short fingerprint so that similar documents have similar fingerprints.

Machine learning

As large models were trained on web scale text, research showed that repeated passages in the training data lead to memorization and to wasted capacity. Studies published in the early 2020s reported that removing duplicates can make models regurgitate training text less, improve efficiency, and give cleaner evaluation. Dedup became a standard step in data pipelines for language models. The same concern now applies to tabular collections, where public datasets are mirrored, versioned and re-uploaded many times.

How it works

A dedup pipeline usually has the following steps. Each step can be simple or sophisticated depending on scale and need.

Step 1: define what "the same" means

Before writing code, decide the rule. Questions to answer:

• Should two rows that differ only in an update timestamp count as duplicates?
• Should case, spacing and punctuation be ignored?
• Should numeric values within a tiny tolerance count as equal?
• Which columns identify a record, and which are just attributes?
• Is the goal to clean a table, or to protect an evaluation set from leakage?

Different goals call for different rules. A rule that is right for a customer list can be wrong for sensor readings, where identical consecutive readings are real and meaningful.

Step 2: normalize

Normalization removes differences that do not matter, so that equal things look equal. Typical operations:

• trim whitespace and collapse repeated spaces,
• lowercase text,
• strip accents and unify Unicode forms,
• standardize date and number formats,
• round floats to a chosen precision,
• sort columns or fields into a fixed order,
• replace missing markers such as empty strings, "N/A" and "null" with one value.

Good normalization catches many duplicates cheaply, before any fancy matching.

Step 3: build a fingerprint

For exact dedup, compute a hash of the normalized record, for example by hashing the tuple of its values. Two records with the same hash are treated as equal, usually after a final equality check to rule out hash collisions.

Step 4: group exact duplicates

Sort or group by the hash. Every group with more than one member is a set of duplicates. This is linear in the size of the data and easy to parallelize. Most of the savings in real pipelines come from this step.

Step 5: find near duplicates with blocking or hashing

Comparing every pair of records takes time that grows with the square of the number of records. For a million records, that is half a trillion pairs. Two main strategies avoid this.

Blocking. Group records by a cheap key, such as the first letters of a name or a postal code, and compare pairs only inside each block. This works well for structured data when a good key exists. The risk is that a typo in the key sends two true matches to different blocks.

Locality sensitive hashing with MinHash. Convert each record into a set of features, such as character n-grams or token shingles. Compute a short MinHash signature for the set. Split the signature into bands, and hash each band. Records that share a band hash become candidate pairs. Similar records are likely to collide in at least one band, while dissimilar ones rarely do. You then verify candidates with an exact similarity measure.

An analogy: imagine sorting a huge pile of photographs into drawers by a few rough features, such as dominant color and orientation. Photos of the same scene tend to land in the same drawer, so you only need to compare photos within a drawer.

Step 6: score candidate pairs

For each candidate pair, compute a similarity score. Common measures:

For structured records, scores are computed per field and then combined, either with hand set weights, with a probabilistic model in the classic record linkage tradition, or with a trained classifier.

Step 7: decide matches

Pick a threshold or train a classifier. Pairs above the threshold are matches. Pairs well below are non matches. Pairs in between may go to human review. Because the pairs form a graph, apply transitive closure: if A matches B and B matches C, they all belong to one cluster. Be careful, since chaining can merge clusters that should stay separate.

Step 8: choose the survivor

For each cluster, choose what to keep. Options:

• keep the first or the most recent record,
• keep the most complete record, with the fewest missing values,
• merge field by field, taking the best non-missing value,
• keep all records but add a cluster id, so the choice can be made later.

Keeping a cluster id is often wise, since it preserves information and allows audits.

Step 9: record what you did

Write down the rule, the thresholds, the counts removed and the sample of removed records. Dedup is a lossy step, and you want to be able to explain and reverse it.

Embedding based dedup

For semantic duplicates, a model can map each record to a vector, and a nearest neighbor index finds vectors that are close. Approximate nearest neighbor search keeps this fast at scale. This catches paraphrases and reformatting that string methods miss, at the cost of more compute and a need to tune the distance threshold.

Key concepts and terms

A worked example

A small numeric walk-through

Consider the following customer table.

Normalization (trim, collapse spaces, lowercase) turns rows 1, 2 and 3 into the same tuple, so exact dedup merges them into one record. Row 4 differs by one character in the name. Using edit distance, the names "maria lopez" and "mario lopez" are one edit apart out of eleven characters, a similarity of about 0.91. All other fields match. With a threshold of 0.9, row 4 joins the cluster, so the final table has two customers, not five. Whether that is right depends on context: Maria and Mario might be two real people. This is why thresholds need to be checked on a sample and why keeping a cluster id is useful.

Exact dedup with pandas

import pandas as pd

df = pd.read_csv("customers.csv")

def norm_text(s):
    return (s.astype("string")
             .str.strip()
             .str.replace(r"\s+", " ", regex=True)
             .str.lower())

key_cols = ["name", "city", "signup"]
clean = df.copy()
for c in ["name", "city"]:
    clean[c] = norm_text(clean[c])

dups = clean.duplicated(subset=key_cols, keep=False)
print("rows in duplicate groups:", int(dups.sum()))

deduped = clean.drop_duplicates(subset=key_cols, keep="first")
print("before:", len(df), "after:", len(deduped))

Near duplicate detection with MinHash

from datasketch import MinHash, MinHashLSH

def shingles(text, k=3):
    t = " ".join(text.lower().split())
    return {t[i:i + k] for i in range(max(1, len(t) - k + 1))}

def signature(text, num_perm=128):
    m = MinHash(num_perm=num_perm)
    for s in shingles(text):
        m.update(s.encode("utf8"))
    return m

records = df["name"].astype(str) + " | " + df["city"].astype(str)
lsh = MinHashLSH(threshold=0.8, num_perm=128)
sigs = {}
for i, text in records.items():
    sigs[i] = signature(text)
    lsh.insert(str(i), sigs[i])

pairs = set()
for i, sig in sigs.items():
    for j in lsh.query(sig):
        if int(j) != i:
            pairs.add(tuple(sorted((i, int(j)))))
print(len(pairs), "candidate near duplicate pairs")

The index returns candidate pairs quickly. You then verify them with an exact measure and look at a sample by eye.

Cleaning a split to avoid leakage

import hashlib

def row_hash(row, cols):
    payload = "|".join(str(row[c]).strip().lower() for c in cols)
    return hashlib.sha1(payload.encode("utf8")).hexdigest()

cols = [c for c in train.columns if c != "target"]
train_hashes = set(train.apply(row_hash, axis=1, cols=cols))
mask = test.apply(row_hash, axis=1, cols=cols).isin(train_hashes)
print("test rows also in train:", int(mask.sum()), "of", len(test))
test_clean = test[~mask]

Note that the target column is left out of the hash. The goal here is to find test rows whose features appear in training, since a model could simply look them up.

The same idea in SQL

SELECT *
FROM (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY lower(trim(name)), lower(trim(city)), signup
           ORDER BY updated_at DESC
         ) AS rn
  FROM customers t
) x
WHERE rn = 1;

Window functions are the standard SQL tool for keeping one survivor per group.

Strengths and limitations

Strengths

• Cleaner statistics: counts, means and class balance reflect reality instead of loading accidents.
• Less memorization: models see each example fewer times, which reduces verbatim recall of training data.
• Lower cost: fewer rows means faster training and cheaper storage.
• Honest evaluation: removing overlap between training and test data makes scores trustworthy.
• Better data quality signals: duplicate rates often point to upstream bugs such as double loading.
• Scales well: hashing based methods run in near linear time and parallelize across machines.

Limitations

• Definition problem: there is no universal rule for what counts as a duplicate. Wrong rules delete real data.
• False positives: aggressive near duplicate rules merge distinct entities, such as twins or two products with similar names.
• False negatives: conservative rules leave duplicates that cause leakage.
• Information loss: repeated rows can carry real information, such as frequency. Removing them changes the distribution.
• Cost of semantic matching: embedding based methods need models and indexes, and thresholds need tuning.
• Bias risk: if duplicates concentrate in certain groups, removing them changes group representation.
• Chaining: transitive closure can produce giant clusters from a few loose links.

When duplicates should be kept

In these cases, include a time or event identifier in the key, or skip row dedup altogether.

How it compares with alternatives

Deduplication is one of several data quality and data preparation steps. The table shows how it differs from nearby ideas.

Exact versus near duplicate methods

A common plan is to run all three in order of cost. Exact hashing removes the bulk. MinHash catches formatting variants. Embeddings handle the rest on a smaller remaining set.

Prevention versus cleanup

It is cheaper to prevent duplicates than to remove them. Idempotent loads, unique constraints, deterministic record ids and careful merge logic in pipelines all reduce duplicates at the source. Cleanup is still needed for data that comes from outside, but prevention keeps the problem small.

Role in tabular foundation models

Tabular foundation models are pre-trained on large collections of tables so that they can predict on a new table through in-context learning, without task specific training. Their inputs have row and column structure, mixed types, missing values and often unreliable or missing column names. Deduplication touches almost every stage of building and judging such models. See /model/architecture for the model family and /model/benchmarks for evaluation.

Duplicate rows inside a table

When a model reads a table as context, each row is a labeled example. Repeated rows have several effects:

• They waste context. A context window of a fixed number of rows holds fewer distinct examples.
• They bias in-context learners. A model that behaves like a nearest neighbor or a kernel method will give extra weight to repeated points.
• They create false confidence. If a query row also appears in the context with its label, the model can copy the label, and the evaluation looks better than the real skill.

Within-table dedup before building the context is therefore a sensible default, unless repeats are meaningful, for example as weights.

Duplicate tables across the pre-training collection

Public tabular datasets are mirrored, re-uploaded, versioned and split in many ways. The same underlying data can appear as a full table, as a train split, as a test split, and as a cleaned variant. If these are all in the pre-training set, the model sees the same content many times, which wastes capacity and can cause memorization of specific tables. Table level dedup uses fingerprints of column sets, value distributions and row hashes to find tables that are copies or subsets of each other.

A practical table fingerprint combines:

1. the number of rows and columns,
2. the column types in order or as a sorted multiset,
3. sketches of each column, such as MinHash of values or quantile summaries for numbers,
4. a hash of a sample of normalized rows.

Two tables with matching sketches are strong candidates for being the same data, even if column order or names differ. This matters because columns may carry no names, or names may have been anonymized.

Synthetic tables

Pre-training often relies on large numbers of synthetic tables generated from random programs or causal graphs (see synthetic data generation). Generators with narrow randomness can produce many near identical tables. Dedup at the level of table structure and statistics helps keep the synthetic distribution diverse. It also supports a curriculum, because you can measure diversity and difficulty before scheduling tables from simple to complex.

Benchmark contamination

This is the highest stakes use. Models are judged on held out benchmark tables. If a benchmark dataset, or a close variant, was part of pre-training, scores are inflated. Because tabular collections come from shared public sources, overlap is easy to create by accident. Good practice:

• fingerprint every benchmark table before building the pre-training set,
• remove from pre-training any table whose fingerprint matches a benchmark table at the table level or at a high row overlap level,
• check row level overlap between benchmark test splits and any training data,
• report the check, so readers can trust the results.

For how results are presented, see /model/benchmarks, and for the research context see /model/research.

In-context learning and leakage

In-context models take labeled rows as input at prediction time. If the test rows are duplicates of context rows, the model can read the answer. When evaluating, make sure that the context rows and query rows come from clean, deduplicated splits. For repeated experiments with random splits, check that dedup happened before splitting, not after. Otherwise duplicates fall on both sides.

Continual learning

When a model keeps learning from new tables over time, dedup against everything seen so far prevents the model from over-weighting familiar content. Persistent fingerprint stores make this efficient, since you only compare new tables with the stored sketches rather than the full old data.

Imputation and anomaly detection

• Imputation: models trained with masked cell objectives, in the spirit of masked cell modeling, learn to fill hidden cells from other cells. Duplicate rows make this too easy, since a masked value can be copied from its twin. Dedup keeps the task honest. After dedup, merged duplicates can also supply values for missing cells, which is a form of imputation by record fusion.
• Anomaly detection: duplicates distort what looks normal. A pattern repeated a thousand times looks perfectly normal because of repetition, not because it is typical of the process. Conversely, an exact repeat of a rare event can be a sign of a pipeline fault. See anomaly detection and Isolation Forest.

Sector and semantic classification

Models that classify a table into a domain or sector learn from many tables with labels. If near identical tables with the same label dominate, the classifier learns to recognize those specific tables. Dedup at the table level, and splitting by source rather than by random rows, gives a fairer estimate of how it will do on unseen tables.

Models named in this space

For examples of tabular foundation models that read tables as context, see TabPFN, TabICL and TabSTAR. All of them depend on the quality of their training and evaluation data, and the points above apply to each.

Practical guidance and best practices

Plan the pipeline

1. Profile first. Count exact duplicates and look at samples.
2. Define the key and the similarity rule in writing.
3. Normalize, then hash, then group.
4. Add near duplicate detection only if needed, and measure its precision on a labeled sample.
5. Decide the survivor rule and keep a cluster id.
6. Log counts and examples of what was removed.
7. Re-run the same pipeline on new data with the same rules.

Dedup before splitting

Always remove duplicates before creating train, validation and test splits. If you split first, copies of the same record land on both sides and the test score is optimistic. For grouped data, split by group, for example by customer or by source table, so that related records stay together.

Choose keys with care

• Include columns that define identity, not columns that change with each load, such as ingestion time.
• For time based data, include the time so that legitimate repeats are kept.
• Exclude the target when checking leakage between features, and include it when checking label conflicts.

Look for label conflicts

Duplicates with different labels are a data quality problem. Find groups where the features match but the target differs. Decide whether to drop, relabel, or keep them as legitimate noise.

conf = (df.groupby(feature_cols)["target"].nunique()
          .reset_index(name="n_labels"))
print((conf["n_labels"] > 1).sum(), "feature groups with conflicting labels")

Tune thresholds with samples

Draw a random sample of candidate pairs at several similarity levels, review them by hand, and pick the threshold where precision is acceptable. Do not choose a threshold from theory alone.

Keep an audit trail

Store removed rows or at least their cluster ids and the rule version. If someone later asks why a record vanished, you can answer.

Use the right tools

Test on known cases

Insert a few synthetic duplicates with known edits into a copy of your data and check that the pipeline finds them. This gives you a recall estimate you can trust.

Common mistakes

• Deduplicating after splitting. Duplicates then straddle train and test, and scores are inflated.
• Only checking exact matches. Whitespace, case and rounding differences hide many duplicates. Normalize first.
• Including volatile columns in the key. A load timestamp makes every row unique, so nothing is removed.
• Using a loose threshold with transitive closure. A few weak links can merge thousands of distinct records.
• Removing meaningful repeats. Sensor and transaction data often need repeats kept.
• Ignoring label conflicts. Duplicate features with different labels hurt training and evaluation.
• Hashing the target into the leakage check. This hides overlap in features.
• Forgetting benchmark overlap. Public datasets turn up in many collections. Check before training.
• Dropping the cluster id. Without it you cannot audit or revisit choices.
• Relying on a single method. Combine exact, hashed and, where needed, semantic methods.
• Not measuring. Without a labeled sample, you cannot say whether the pipeline removes too much or too little.
• Treating dedup as one time. New loads bring new duplicates, so build it into the pipeline.
• Comparing floats for equality. Round or use tolerances before hashing numeric columns.

Frequently asked questions

What is deduplication in machine learning?

It is the step of finding and removing repeated or nearly repeated records from the data used for training and evaluation. The aim is to stop models from memorizing repeats, to avoid wasting compute, and to keep test data separate from training data. It can apply to rows, to whole tables, or to entire collections.

What is the difference between exact and near duplicate detection?

Exact detection finds records that are identical after cleaning, usually by hashing. Near duplicate detection finds records that are very similar but not identical, using measures such as edit distance, set overlap or embedding distance. Exact methods are fast and simple, while near methods catch typos and formatting variants but need threshold tuning.

How does MinHash work in simple terms?

MinHash turns a set of features, such as pieces of text, into a short signature in a way that two similar sets get similar signatures. By comparing signatures, you can estimate how much two sets overlap without comparing every element. Combined with locality sensitive hashing, it lets you find similar records in large data without checking every pair.

Should I remove duplicates before or after splitting into train and test?

Before. If you split first, copies of the same record can end up on both sides, which makes the test score look better than the real performance. For data with natural groups, such as customers or source tables, split by group so that related records stay together.

Can deduplication hurt my model?

Yes, if it removes meaningful repeats or merges distinct entities. Repeated values in logs and sensor data can be real, and a loose similarity threshold can merge different people or products. Check a sample of removed records, keep a cluster id, and make sure the rule matches the task.

How does deduplication relate to data leakage?

Leakage happens when information from the test set reaches the training process. Duplicates are a common cause, since the same record in both sets lets a model recall the answer. Removing overlap between training and test data is one of the most direct ways to prevent this kind of leakage.

Why does it matter for tabular foundation models?

These models are pre-trained on many tables and judged on benchmark tables. Mirrored and re-uploaded datasets mean the same content can appear in both. Table level and row level dedup keep pre-training diverse, protect benchmarks from contamination, and keep in-context evaluation honest.

How do I find duplicate tables when column names are missing?

Use fingerprints that do not depend on names. Combine the table shape, the order or multiset of column types, and sketches of each column such as MinHash of values or quantile summaries. Tables with matching sketches are likely the same data even when columns are renamed or reordered.

How much data does deduplication usually remove?

It varies widely. A clean, curated table may have almost none, while scraped or merged collections can have a large share. Profile your own data first, since the duplicate rate is itself a useful quality signal that can point to upstream problems.

What tools can I use?

For small data, pandas or SQL window functions are enough. For large data, use distributed engines and hashing based methods, and libraries that implement MinHash and locality sensitive hashing. For entity resolution across sources, use record linkage libraries or build a pipeline of blocking, scoring and clustering.

Glossary

• Blocking: limiting comparisons to records that share a cheap key.
• Cluster id: an identifier shared by all records judged to be duplicates of one another.
• Contamination: overlap between training data and evaluation data.
• Edit distance: the number of single character edits needed to turn one string into another.
• Entity resolution: deciding which records refer to the same real-world thing.
• Exact duplicate: a record whose normalized values match another record exactly.
• Fingerprint: a compact summary used to compare records or tables quickly.
• Hash: a fixed size value computed from data.
• Jaccard similarity: overlap of two sets divided by their union.
• Leakage: information from evaluation data influencing training.
• Locality sensitive hashing: hashing that places similar items in the same bucket with high probability.
• MinHash: a signature method for estimating set similarity.
• Near duplicate: a record very similar to another but not identical.
• Normalization: cleaning that removes irrelevant differences.
• Precision: the share of flagged pairs that are true duplicates.
• Recall: the share of true duplicate pairs that were found.
• Shingle: a short overlapping unit used to turn text into a set.
• SimHash: a fingerprint method where similar inputs give similar bit patterns.
• Survivor: the record kept from a duplicate cluster.
• Transitive closure: merging chains of matches into single clusters.

Summary

Deduplication removes exact and near duplicate records so that data is cleaner, models memorize less and evaluations are honest. The usual path is to define what "the same" means, normalize, hash and group for exact matches, use blocking or MinHash with locality sensitive hashing for near matches, score candidate pairs, choose a survivor and keep an audit trail. Always dedup before splitting data, keep meaningful repeats, and check label conflicts.

For tabular foundation models, dedup works at three levels: rows inside a table, tables inside the pre-training collection, and overlap between the collection and the benchmarks. Done well, it keeps in-context learning fair, preserves diversity in synthetic and real pre-training data, and supports trustworthy reporting. Related topics include anomaly detection, curriculum learning and synthetic data generation. For the wider picture, see /model/architecture, /model/benchmarks and /model/research.

Related

It belongs to Foundation models from scratch in my stack, under Foundation models and applied ML. Others in the same group: