Why 'ABC' Didn't Equal 'ABC': An Invisible Character Broke My Join
The views I express here are mine alone and do not necessarily reflect the opinions or official position of my employer.
I stared at two values side by side for a good five minutes convinced I was losing my mind. Both said ABC123. Same font, same casing, same everything I could see. And a join between them wasn't matching. Counts were quietly off, rows I expected to line up simply didn't, and nothing in the output looked broken enough to explain why.
The culprit was a character I couldn't see at all: a carriage return, \r, hex 0D, sitting silently at the end of one of the values. What looked like ABC123 (6 characters) was actually ABC123\r (7 characters). "Identical" on screen and genuinely different to an exact-match join.
What a carriage return even is, and why you can't see it
\r is a leftover from old Windows and DOS line-ending conventions, where a line break is written as \r\n rather than just \n. It's invisible in Snowsight and Excel I checked, all of them render it as nothing, or at most a line continuation, never as a visible character. It typically sneaks in through a copy-paste or a CSV import into a grid-style reference table, the kind of place where someone pastes a list of codes in from another spreadsheet and nobody double-checks the encoding.
Visual inspection genuinely cannot catch this. The two things that can:
LENGTH(product_code)on the two "identical" values comes back different, one is 6, the other is 7.HEX_ENCODE(product_code)shows the raw bytes, and there it is:...313233 0Dinstead of just...313233.
Once I ran those two checks, the mystery was over in under a minute. The five minutes of confusion staring at the screen was entirely because I was checking the wrong kind of evidence: my eyes instead of the bytes.
The fix, and why the two-argument TRIM matters
SELECT TRIM(product_code, chr(13) || chr(10) || ' ') AS product_code_clean
FROM lookup_codes;
Snowflake's two-argument TRIM(str, chars) strips any leading or trailing characters that appear in the chars set you give it, unlike plain TRIM(str), which only strips spaces. Here, chr(13) is the carriage return (\r, hex 0D), chr(10) is a line feed (\n, hex 0A), and ' ' is a literal space. Concatenated together with ||, that builds the character set "\r\n ", so the expression strips any combination of trailing or leading \r, \n, or spaces from product_code, specifically targeting the invisible contamination that was breaking the join.
TRIM in dbt, fix the source, or both
Once I had the fix working, the real question was where it should live.
| Approach | Pros | Cons |
|---|---|---|
| TRIM in dbt | Fast, no access to the source tool needed, defends automatically against future recurrence | Masks the symptom every run, doesn't fix the source data, and gives no visibility that contamination exists or keeps recurring |
| Fix the source rows directly | Clean source of truth | One-time fix, doesn't prevent recurrence if someone pastes dirty data in again later |
| Both | Clean now, defended going forward | Slightly more work, but the pattern I default to now |
I went with both. The TRIM is cheap insurance with no real downside, so it stays in the model regardless. But I also went back and cleaned the handful of dirty rows in the source table, because TRIM alone masks the contamination forever without fixing it, and the source tool isn't fully in my control, so new dirty rows can absolutely show up again later. Only one of our downstream tests happened to catch this at all. If it had not existed, this would have kept under-joining with no signal anywhere.
Why I trust the bytes over my eyes now
The lesson was about what "looks the same" is worth as evidence, not about \r specifically. A viewer renders bytes into pixels, and that rendering step throws information away by design, whitespace, control characters, encoding quirks all get flattened into nothing. Any time a join, a filter, or a WHERE x = y refuses to behave the way two "identical" values suggest it should, LENGTH() and HEX_ENCODE() are the first two things I reach for now, before I spend five more minutes squinting at a screen that was never going to show me the answer.