> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Deduplicate near-duplicate records

> Deduplicate text with MinHash or records with similar embeddings using LanceDB Enterprise SQL.

Enterprise only

LanceDB Enterprise provides `dedupe` and `duplicate_pairs` for finding
near duplicates in text and vector columns. Use `duplicate_pairs` to inspect
matching pairs and `dedupe` to return retained source rows. Neither function
modifies the source table.

## Choose a comparison

| Duplicate criterion | Approach |
| - | - |
| A shared key or an exact value | [Ranking](https://docs.lancedb.com/enterprise/v1-14-0/curation/windowing.md#dedup), such as keeping the latest record for each key. |
| Text with small edits or overlapping passages | [MinHash text deduplication](#text), which compares token shingles. |
| Similar meaning, images, or other embedded content | [Vector deduplication](#example-data), which compares embeddings using a vector index. |

The selected column determines the algorithm: string and MinHash signature
columns use text matching; vector columns use the vector-index workflow.

## Deduplicate text with MinHash

MinHash finds lexical near duplicates, such as copies of a scraped document
with small edits, without generating embeddings.

> [!NOTE]
>
> Text deduplication requires an Enterprise server with native MinHash SQL
> support. Check support on your deployed server; updating the client alone
> does not enable these experimental functions.

For a native `documents` table without WAL and a top-level `body` string
column, inspect candidate pairs first:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT row_id_a, row_id_b, distance, dataset_version
FROM duplicate_pairs(
    'documents', 'body', distance_threshold => 0.2
);
```

Then return the retained rows:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT *
FROM dedupe(
    'documents', 'body', distance_threshold => 0.2
);
```

Here, `distance_threshold` is estimated Jaccard distance between token-shingle
sets, from zero through one. Lower values require closer matches. The defaults
are 128 hashes, 3 words per shingle, and 16 LSH bands. No MinHash or vector
index is required for these text-column calls. LSH candidate generation can
miss pairs that meet the distance threshold.

By default, `dedupe` keeps the smallest snapshot row ID in each connected
component. Pass `resolution => 'direct_representative'` to remove only rows
that directly match a retained representative. Both policies retain rows
with NULL or no-token text.

To reuse signatures across queries, materialize them in a new table:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
CREATE TABLE documents_with_signatures AS
SELECT *, minhash(body) AS signature FROM documents;

SELECT *
FROM dedupe(
    'documents_with_signatures', 'signature', distance_threshold => 0.2
);
```

Preserve the signature's configuration metadata and recompute it when its
source text changes. The scalar functions `minhash_distance` and
`minhash_lsh_keys` also support explicit signature scoring and candidate-key
generation.

See the [MinHash SQL guide](https://docs.lancedb.com/enterprise/v1-14-0/enterprise/sql-minhash.md) for signature compatibility,
configuration, resolution policies, resource limits, and batch materialization.

## Vector example data

Connect with an [Enterprise connection](https://docs.lancedb.com/enterprise/v1-14-0/basics/catalog.md#enterprise). A small
fixture keeps the distances easy to check by hand.

> [accordion] Create the example table
>
> `curation_embeddings` has eight records. Rows 10 and 20 are near-identical;
> rows 30, 40 and 50 form one looser cluster; the rest are distinct.
>
> ```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
> import pyarrow as pa
> from lancedb.index import IvfFlat
>
> schema = pa.schema([
>     pa.field("id", pa.int64(), nullable=False),
>     pa.field("caption", pa.utf8()),
>     pa.field("embedding", pa.list_(pa.float32(), 2)),
> ])
> table = db.create_table("curation_embeddings", [
>     {"id": 10, "caption": "a cat on a chair",     "embedding": [0.0, 0.0]},
>     {"id": 20, "caption": "a cat on the chair",    "embedding": [0.0, 0.03]},
>     {"id": 30, "caption": "a dog in a field",      "embedding": [5.0, 5.0]},
>     {"id": 40, "caption": "a dog in the field",    "embedding": [5.0, 5.05]},
>     {"id": 50, "caption": "a dog out in a field",  "embedding": [5.0, 5.10]},
>     {"id": 60, "caption": "a bird in a tree",      "embedding": [10.0, 10.0]},
>     {"id": 70, "caption": "a boat on the water",   "embedding": [20.0, 20.0]},
>     {"id": 80, "caption": "a car on the road",     "embedding": [30.0, 30.0]},
> ], schema=schema)
>
> table.create_index("embedding", config=IvfFlat(num_partitions=1, distance_type="l2"))
> table.wait_for_index(["embedding_idx"])
> ```
>
> `dedupe` and `duplicate_pairs` read the vector index, so the column needs one
> before either function runs; a query against unindexed fragments is rejected
> rather than silently returning stale or wrong results.
>
> For an `l2` index, `distance_threshold` and the `distance` column below are
> both **squared** L2, not the plain L2 a plain SQL `distance()` call returns.

## Collapse vector near-duplicates

`dedupe` returns one representative row per cluster of records within
`distance_threshold` of each other -- rows 10/20 collapse to one, 30/40/50
collapse to one, and the four distinct rows pass through unchanged, leaving
ids 10, 30, 60, 70 and 80:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT id, caption
FROM dedupe(
    'curation_embeddings',
    'embedding',
    distance_threshold => 0.003
)
ORDER BY id
```

Every other column survives unchanged, since `dedupe` returns full rows, not
just which ones matched. Clustering is transitive: 30 and 40 are within
`distance_threshold` of each other, and so are 40 and 50, so all three
collapse into one representative even though 30 and 50 alone sit outside the
threshold. Pass `resolution => 'direct_representative'`
for a simpler, non-transitive selection instead, if a chained cluster like that
should not merge into one representative.

Which row survives as the representative is not guaranteed to stay the same
across appends: a later row that bridges two previously separate clusters can
change which id represents the merged cluster. Do not treat a representative's
id as a stable identifier for its cluster.

## Inspect vector pairs instead

`duplicate_pairs` returns the matching edges themselves, without collapsing
anything -- useful for review, or for a threshold that is still being tuned:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT id_a, id_b, distance
FROM duplicate_pairs(
    'curation_embeddings',
    'embedding',
    distance_threshold => 0.003,
    id_column => 'id'
)
ORDER BY id_a
```

This returns the pairs `(10, 20)`, `(30, 40)` and `(40, 50)` -- note 30 and 50
are not a direct pair here (their squared L2 distance is `0.01`, outside the threshold),
even though `dedupe` merges all three transitively above.

## Keep it current with a materialized view

Wrap either call in a [materialized view](https://docs.lancedb.com/enterprise/v1-14-0/management/materialized-view.md) so
the deduplicated set stays current as new records arrive, instead of
recomputing it from scratch on every curation pass:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
CREATE MATERIALIZED VIEW curation_embeddings_deduped AS
SELECT * FROM dedupe(
    'curation_embeddings',
    'embedding',
    distance_threshold => 0.003
)
```

See [Refresh a materialized view](https://docs.lancedb.com/enterprise/v1-14-0/feature-engineering/refresh-materialized-view.md)
for manual and automatic refresh options to keep it current as new records land.

## Vector index, snapshot and recall limits

Vector deduplication requires one compatible current vector index covering
the scanned snapshot. Wait for indexing to finish after ingestion and inspect
index coverage before running a full scan. Unindexed fragments are rejected;
deleted rows are excluded at the pinned dataset version.

The scan compares candidates **within each physical IVF partition and index
segment**. It does not compare across partitions or segments. Scanning every
partition therefore does not establish global all-pairs recall. Quantized
indexes compare their encoded vector representations; `IVF_FLAT` compares the
stored vectors. Thresholds must match the index's metric: L2 is squared L2,
cosine is `1 - cosine`, dot distance is `1 - dot`, and Hamming counts differing
bits. Inspect close examples and known duplicates before choosing a threshold.

`duplicate_pairs` reports `dataset_version`, `segment_id` and `partition_id`.
Retain those with the index configuration and source version when comparing
runs. Pass `id_column` for application IDs, rather than treating physical row
addresses as durable identifiers. A new append, index change or bridge between
components can change groups and representatives.

## Efficient vector deduplication and scale qualification

Start with [`duplicate_pairs` partition sampling](https://docs.lancedb.com/enterprise/v1-14-0/sql/dql/duplicate-pairs#sampling),
using a recorded seed and a bounded partition count. Inspect pair density and
threshold quality before running `dedupe`, which always scans every partition.
Sampling partitions is not uniform row sampling and can miss rare groups.
`resolution='connected_components'` merges transitive chains;
`direct_representative` uses direct representative relationships instead.

Pair work grows quadratically within dense partitions, and a loose threshold
can emit a quadratic number of edges. An outer `LIMIT` is not a resource
budget for upstream pair finding or group resolution. Avoid repeatedly
materializing all pairs in a Python list; select needed columns, aggregate on
the server, and persist a reviewed result when repeated reads justify it.
Measure peak memory, spill storage, elapsed time and pair count on
representative partitions before increasing scope.

The [LeRobotDataset integration](https://docs.lancedb.com/enterprise/v1-14-0/integrations/lerobotdataset.md) illustrates a
27.6-million-row data workflow. Its timings are not a versioned qualification
of this deduplication path on arbitrary indexes or hardware. A 10-billion-row
run is not qualified by that example. Qualify source/index versions, partition
sizes, metric, threshold, worker resources, spill limits and correctness on your
deployment before extrapolating. There is no universal row-count or latency
guarantee in this guide.

Agents can follow the repository's
[vector deduplication skill](https://github.com/lancedb/lancedb/blob/main/.agents/skills/lancedb-vector-dedup/SKILL.md)
to apply these checks without inventing Function options or scale guarantees.

## Continue with the curated data

[Profile the result](https://docs.lancedb.com/enterprise/v1-14-0/curation/aggregate.md) to check how much a threshold
removes before committing to it, or go back to
[choosing a curation task](https://docs.lancedb.com/enterprise/v1-14-0/curation.md#questions).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.