duplicate check misses duplicates that differ only by trailing whitespace error

TL;DR

Normalise before you compare: the duplicate check misses rows that differ only by trailing whitespace because the warehouse comparison treats padded and unpadded strings as different keys, so trim key columns in the check and at ingestion. Trailing spaces arrive from fixed-width extracts and padded source fields. Exact-match duplicate detection cannot see two keys as the same when one carries padding the other lacks.

duplicate check misses duplicates that differ only by trailing whitespace error

Use this when

• The check or run fails with this symptom: a duplicate check misses duplicates that differ only by trailing whitespace.
• You are working on a uniqueness or duplicate check over string keys fed by padded source extracts and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• Duplicates that differ by case rather than whitespace, which need a case rule decision instead of trimming.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Select the suspect keys with their lengths to expose padding. Verify: rows whose trimmed values match but raw values differ are listed
2. Find which source or extract introduces the padding, such as a fixed-width file or a char type column. Verify: the padding origin is identified
3. Trim the key columns at ingestion, or change char columns to varchar where the platform pads. Verify: newly loaded keys carry no trailing spaces
4. Update the duplicate check to compare trimmed keys so historic padded rows are still caught. Verify: the check flags the known padded pair
5. Clean the existing padded rows in place. Verify: a trimmed duplicate scan returns zero groups

Variant phrasings

duplicate check misses trailing whitespace duplicates

The shorter phrasing focused on the named failure.

duplicates differ only by trailing spaces error

The symptom phrasing a searcher tries next.

string key padding duplicate detection fix

The variant naming the failing stage or tool directly.

Compatibility: Warehouse string comparison semantics in Snowflake, BigQuery, Postgres, and DuckDB, plus fixed-width source extracts

Why it happens

Fixed-width formats and char types pad values to a fixed length. When one feed pads and another does not, the same business key exists twice in forms that exact comparisons treat as distinct, so duplicate checks pass over real duplicates.

Edge cases / pitfalls

• Trailing whitespace is invisible in most query tools; always check with length or a marker character when duplicates are suspected but not found.
• Trimming at check time only, without fixing ingestion, means every new batch reintroduces invisible duplicates.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_TTw6gsBcXMRvj4fhIiUFPA

Resolved from the public thread: https://vectle.com/posts/pst_TTw6gsBcXMRvj4fhIiUFPA

Thread query: duplicate check misses duplicates that differ only by trailing whitespace error

TL;DR: Normalise before you compare: the duplicate check misses rows that differ only by trailing whitespace because the warehouse comparison treats padded and unpadded strings as different keys, so trim key columns in the check and at ingestion. Trailing spaces arrive from fixed-width extracts and padded source fields. Exact-match duplicate detection cannot see two keys as the same when one carries padding the other lacks.