Finding Near-Duplicate Rows in Your Data

You pull a customer report and notice something off. The total customer count is 4,200, but your billing system says 3,800. You start scrolling and there they are: "Sarah Chen" and "sarah chen" and " Sarah Chen". Same email on all three rows, same signup date, same lifetime value. One account, counted three times.

Exact duplicates are easy - run Remove Duplicates and you're done. Near-duplicates are trickier. The rows aren't identical. They just look the same to a human but not to a computer. A basic DISTINCT won't catch them.

The usual suspects

Near-duplicates show up in predictable ways:

• Case differences: "TechWave Solutions" vs "Techwave Solutions" vs "TECHWAVE SOLUTIONS"
• Extra whitespace: " John Smith " vs "John Smith" vs "John Smith"
• Trailing characters: "Acme Corp." vs "Acme Corp" vs "Acme Corp, Inc."
• Abbreviations: "St." vs "Street", "NY" vs "New York"

The first two categories - case and whitespace - account for probably 80% of near-duplicate issues. And they're the easiest to fix.

Start with the built-in near-duplicate finder

Before you build anything by hand, look at what the app already found. Click the green + in the Pipeline panel and select Bulk Replace from the Transform group. Pick the column, then switch from All Values to the Similar Suggestions tab.

That tab runs DuckDB's editdist3 (Levenshtein distance) over the distinct values in the column, lowercased, and groups any two values within one character edit of each other. "Techwave" and "TechWave" pair up because lowercasing makes them identical. "Nothwind" and "Northwind" pair up because they differ by one insertion. Each group comes with an + Accept button that drops it straight into the Groups list, and applying the step rewrites every member to the group's label.

Two limits worth knowing before you rely on it. It samples up to 500 distinct values, so on a high-cardinality column it sees a slice, not everything. And one edit is a tight threshold on purpose: "  Sarah  Chen  " is four edits away from "Sarah Chen", so padding and double spaces slip past it, and so does "Acme Corp." vs "Acme Corporation". Normalize whitespace first and the suggestions get noticeably better.

One more detail: the label on an accepted group is one of the group's own values, whichever the grouping picked, and there is no rename box. If you want a specific canonical spelling, delete the accepted group, go back to the All Values tab, tick the variants yourself, type the name you want and hit + Create.

The strategy: normalize, then deduplicate

You can't deduplicate on the original column because the values aren't equal. So create a normalized copy of the column, clean it up, and deduplicate on that instead.

Here's the approach in ExploreMyData:

Step 1: Copy the column

Use Copy Columns from the Columns group to duplicate the column you want to match on. If your column is customer_name, name the copy match_key. That copy is what we'll flatten. The original stays untouched so you keep the real name.

Step 2: Trim, then lowercase

Text Transform applies exactly one transform per step, so this is two steps, not one. Add a Text Transform on match_key with "trim" to strip leading and trailing whitespace, then add a second one on the same column with "lowercase".

Each step rewrites the column in place, so what you see in the pipeline is TRIM(match_key) followed by LOWER(match_key). Written as a single expression that composes to LOWER(TRIM(match_key)), with TRIM on the inside because it ran first. For these two the order happens not to matter, but get in the habit of reading the composition inside out, because for most pairs it does.

After both steps, all of these become the same value:

• "Sarah Chen" → "sarah chen"
• "SARAH CHEN" → "sarah chen"
• "  Sarah Chen  " → "sarah chen"

Step 3: Collapse the internal spaces

Trim only handles the ends. "Sarah  Chen" with two spaces in the middle is still a different string from "Sarah Chen" with one. Use Find & Replace on match_key. Find    (two spaces) and replace with   (one space). Leave "Entire cell" unticked, since you want a substring match here.

Under the hood: REPLACE(match_key, ' ', ' ')

If your data might have three or more consecutive spaces, apply this step twice. The first pass turns triple spaces into doubles, the second pass collapses those to singles.

Step 4: Deduplicate on the normalized column

Now use Remove Duplicates from the Filter & Sort group. Select match_key as the column to check for duplicates.

ExploreMyData generates: DISTINCT ON (match_key)

This keeps one row per unique normalized value and drops the rest. All four Sarah Chen spellings share a key, so one survives and the account stops being counted four times.

Step 5: Clean up

Once you're satisfied with the deduplication, drop the helper column. Use Delete Columns to remove match_key. Your data now has one row per customer with the original formatting intact.

Going further with abbreviations

Case and whitespace normalization handles the majority of near-duplicates. But what about "Acme Corp." vs "Acme Corporation"? These need domain-specific replacements.

Before deduplicating, add Find & Replace steps on match_key:

• Replace "corp." with "corporation"
• Replace "inc." with "incorporated"
• Replace "ltd." with "limited"
• Replace "st." with "street" (for address data)

Each replacement adds a pipeline step. The SQL stacks up: REPLACE(REPLACE(col, 'corp.', 'corporation'), 'inc.', 'incorporated')

Because the column is already lowercased, you don't have to fight the "Case sensitive" checkbox, which is on by default. Do watch the substring behaviour though: replacing "st." also rewrites the middle of "west." If you want whole values swapped rather than fragments, that's a job for Bulk Replace, which matches the entire cell.

It's not fancy fuzzy matching, but it catches the patterns that actually appear in your data. Look at the duplicates you have, identify the specific variation patterns, and add targeted replacements. Practical beats clever.

Check before you delete

This is the part people skip. Matching names are not evidence of a duplicate. Four rows reading "TechWave Solutions" with four different contact emails are four contacts at one company, and Remove Duplicates would throw three of them away. Four rows for Sarah Chen with the same email, the same signup date and the same lifetime value are one record entered four times. Only the second case is safe.

The cheap check: run Group & Aggregate on match_key with COUNT plus COUNT DISTINCT on whatever identifies the entity, an email or an account id. If a group has four rows and one distinct email, collapse it. Four rows and four distinct emails means keep them all, and what you actually wanted was a cleaner company label, not fewer rows. In that case run the normalization steps and stop before Remove Duplicates.

Try deduplicating your data →