A lot of pipelines lean on a hash as a key. You take a few business fields — a customer name, a country, an account number — mash them into one string, hash it, and use that hash to match a row to its record or to detect whether anything changed since last time. It’s a good trick, and its whole value is that it’s deterministic: the same input always gives the same hash.

Which is why it’s so disorienting when the same input gives two different hashes.

I was moving a pipeline from SQL Server into Microsoft Fabric. The old one computed its keys in SQL Server; the new one recomputed the identical keys in Python (PySpark) — same fields, same order, same separator, same algorithm, SHA-256 on both sides. I checked the logic twice. Then I joined the new keys against the old warehouse to confirm they lined up.

Nothing matched. Not “most things matched” — nothing. Every key was different, every join came back empty, and because an empty join isn’t an error, the pipeline reported a clean, successful run while producing a table with no history attached to a single row.

The algorithm wasn’t the problem. SHA-256 is SHA-256. The problem was one layer underneath, the one nobody looks at: a hash doesn’t hash a string, it hashes bytes — and the two sides were turning the same string into different bytes.

When SQL Server hashes an NVARCHAR, it hashes the string’s UTF-16 representation — two bytes per character. Python’s hashlib, handed a string, hashes whatever .encode() produces, and .encode() defaults to UTF-8 — one byte for the characters I had. So the SQL side hashed A\x00C\x00M\x00E\x00 and the Python side hashed ACME, and SHA-256 did its job flawlessly on two genuinely different inputs, producing two genuinely different, equally valid-looking 64-character hashes that would never, ever be equal.

The fix is one line — encode the string as UTF-16 little-endian in Python so the bytes match what SQL Server hashed — plus the tedious part I hadn’t accounted for. The old SQL didn’t hash the raw fields; it trimmed trailing spaces, upper-cased, and collapsed double spaces first, and every one of those steps had to be replicated in exactly the same order before hashing, or the bytes drift again. The hash was the easy part. The real key was the canonicalization, and the hash just the fingerprint stamped on top of it.

This bug has a cousin I’d met a few months earlier, and once you’ve seen one you start seeing the whole family. A stored-procedure load turned every Japanese column to blanks — not an error, just empty cells where the Kanji had been. Same root cause: somewhere in the path a value passed through a single-byte VARCHAR instead of a Unicode NVARCHAR, and the characters that don’t fit in one byte didn’t raise anything, they simply evaporated. The bytes you never look at are the ones that delete your data.

The through-line is uncomfortable precisely because it’s so far below where I like to think I work. Encoding is plumbing. It’s the least glamorous layer in the stack, the one you assume just works — and it’s exactly where data goes wrong silently: no exception, no failed row, a green pipeline and a valid-looking hash and a table disconnected from its own past. So my migration checklist now carries a line most of my old ones didn’t: reproduce a known key by hand, byte for byte, and prove the new system computes the same one before trusting a single join. Not because it’s likely to be wrong — because when it is wrong, nothing else will tell you.

Same string, same algorithm, different answer. The computer wasn’t confused for an instant. It was doing precisely what I’d told it to, two levels down from where I was looking. That’s usually where these things live.

Try it

Type a string and watch UTF-8 and UTF-16LE hash to different values — this post’s bug, live in your own browser. It’ll also hash a dropped file’s contents — the content fingerprint an ingestion pipeline should key on instead of the filename. Nothing is uploaded. (Open it full-page.)