Deduplication rules and winner row selection

What it does

Deduplication collapses multiple rows for the same customer within a single table, before any cross-table matching happens. Each table is deduplicated on its own rules, and one row per duplicate group is picked as the winner that represents that customer going forward.

Key facts

Rows sharing an identical primary key value are deduplicated automatically, with the first row taken as the winner. Your rules handle everything else.
Rules run per table. There is no cross-table deduplication here — that's the match step.
A rule is one or more conditions on chosen columns, combined with AND. Email alone, or Email plus FirstName, are the typical shapes.
Normalisation options affect matching only, never the stored data: Numerals, Symbols, Text to lower case, Type – Phone, Type – Name, Type – Address, Type – Organization, Unicode to ASCII, Whitespace, plus Alias mapping and Custom bypass, both in preview.
Type – Name covers 500+ name variations and titles (debby to deborah, prof to Prof.). Type – Organization strips around 50 company noise words such as co, corp, ltd.
Precision is Low (30%), Medium (60%), High (80%) or Exact (100%), with a Custom percentage available if none of those fit.
Exceptions let you block a match that otherwise satisfies the rule — the standard defence against twins, family members at one address, or shared household emails.
Merge preferences decide the winner row: Most filled (default, the row with the most populated columns), Most recent, or Least recent. The last two need a date or numeric field to sort on.
Ties are broken by MAX of the primary key — the largest primary key value wins.
Advanced merge preferences work per column, so you can take the most recent email from one row and the most complete address from another.

When to use / skip

Every table with any chance of duplicate customer rows wants a rule here, which in practice means every CRM contact table and every e-commerce customer table you'll ever see. The cost of skipping it is duplicated profiles that then get matched badly downstream, and match rules that appear to misbehave when the real problem is dirty input.

You can legitimately skip deduplication on a table you know is unique by construction — a mastered reference table, or an extract that's already been deduplicated upstream by the client's MDM. Even then, one Exact-precision rule on the natural key costs nothing and catches the day the upstream job breaks.

Where people over-invest is precision tuning. Low precision on a name column in a large consumer table generates a mountain of false positives that you then spend weeks unpicking with exceptions. Start tight, loosen deliberately.

Configuration decisions

Which columns identify a duplicate in each table. Identity columns (email, mobile, national ID) beat demographic columns every time.
Precision per condition. Exact for identifiers, Medium or High for names, and a documented reason for anything looser.
Which normalisations to apply per condition, and whether the preview Alias mapping and Custom bypass options are acceptable given their preview status. Custom bypass is the right home for junk values like [email protected] and 555-1212.
Winner row strategy per table: Most filled by default, or Most recent where the client cares about currency more than completeness — and which date column drives it.
Whether any columns need advanced per-column merge preferences, rather than taking everything from the winner row.
Which exceptions are needed, and whether they're genuinely rules or just one-off data problems better fixed upstream.

Gotchas

Losing rows aren't discarded. They persist as alternate rows and are still considered during matching, though records match to winner rows. People assume dedupe deletes things; it doesn't.
Most filled rewards completeness, not correctness. A record stuffed with stale values beats a sparse but freshly updated one. On sources where recency matters, change the strategy.
Normalisation changes matching only. Nobody's data gets cleaned, so the ugly value can still surface in the merged profile depending on merge order.
Duplicates left in the primary table after deduplication are not reduced by the match step unless they match against a secondary table. Fix them here or live with them.
The MAX primary key tiebreaker is arbitrary from a business point of view. If the client cares which record wins, give the rule a real tiebreaker column rather than relying on it.
Bypass and alias values live in the rule, not in the data. Nobody outside the project knows they exist unless you write them down.

Consultant notes

Preview rule results before running anything. The preview is the cheapest feedback loop in the entire product and most teams skip straight past it.
Give the client a duplicate count per source before and after. It's the number the sponsor remembers, and it justifies the effort spent on rules.
Push back on "just set precision to Low and let it find everything". Ask what happens when two different customers merge into one profile and the wrong person gets the email; that usually ends the argument.
Keep a written record of every bypass value, alias pair and exception, with the reason. In eighteen months someone will ask why a specific record never deduplicates.
Before go-live, check that the winner row strategy matches what the business actually wants for contact details, not just what was quickest to configure.

Worth revisiting when Alias mapping and Custom bypass come out of preview, or if source data quality changes materially.