Identical Rows Fail to Join: Fix Unicode Normalization
Two scraped rows look identical but never join because the bytes differ. Spot NFC/NFD clashes, zero-width characters, and normalize keys before load.
On this page
The Zero-Row Join on Keys That Look Identical
Two rows in a staging table. Same product name. Same price. Same category. The join returns zero rows. You will spend an embarrassing amount of time on this before you think to look at the bytes — and when you finally do, the fix takes ten lines of Python.
This is one of the most common silent failures in scraped-data pipelines, and it gets worse the more sources you add. Here is how to spot it, what actually causes it, and how to make sure it never comes back.
Symptom: SELECT JOIN Returns Zero Rows for Visually Identical Keys
You scraped the same catalog from two storefronts — store.example.com and example.com — because one carries regional pricing and the other carries availability. The plan is trivial: join both staging tables on product_name, reconcile price vs stock, done.
Except the join returns nothing:
WITH store_a AS (
SELECT product_name, price
FROM staging_store_example_com
),
store_b AS (
SELECT product_name, stock
FROM staging_example_com
)
SELECT a.product_name, a.price, b.stock
FROM store_a a
JOIN store_b b ON a.product_name = b.product_name;
-- Result: 0 rows. Not fewer rows than expected. Zero.
Zero rows is the tell. If the join were merely lossy you would suspect bad selectors or partial coverage. Zero rows on keys you can see sitting in both tables means the equality comparison itself is failing.
So you eyeball the values side by side:
| product_name (store.example.com) | product_name (example.com) |
|---|---|
| Café Blend 500g | Café Blend 500g |
| Espresso Dark 1kg | Espresso Dark 1kg |
| Filter Roast 250g | Filter Roast 250g |
Rendered in any query tool, those columns are pixel-identical. You copy one value from each side into a diff tool. Identical. You paste both into a Python REPL and compare with ==. False.
Before you start questioning your sanity, check the lengths:
SELECT length(product_name) AS len, product_name
FROM staging_store_example_com
WHERE product_name LIKE 'Caf%';
SELECT length(product_name) AS len, product_name
FROM staging_example_com
WHERE product_name LIKE 'Caf%';
One returns 16. The other returns 17. Same visible characters, different byte counts. The join was never going to work, and your eyes were never going to catch it — the rendering layer normalizes what it draws to the screen, which is exactly why the difference is invisible in a result grid.
Diagnose with Byte-Level Inspection: hex(), encode(), and LENGTH() Reveal the Difference
Stop trusting rendered output. Rendered text lies about its own contents. Dump the bytes.
In PostgreSQL, SQLite, and most analytical engines, encode(..., 'hex') or hex() gives you the raw byte sequence:
SELECT product_name, encode(product_name::bytea, 'hex') AS hex_bytes
FROM staging_store_example_com
WHERE product_name LIKE 'Caf%';
-- Cafe' from store.example.com:
-- 43 61 66 c3 a9 20 42 6c 65 6e 64 ...
SELECT product_name, encode(product_name::bytea, 'hex') AS hex_bytes
FROM staging_example_com
WHERE product_name LIKE 'Caf%';
-- Same name from example.com:
-- 43 61 66 65 cc 81 20 42 6c 65 6e 64 ...
Now the difference is unmissable. Put the two side by side:
| Rendered | Hex (relevant bytes) | Char count |
|---|---|---|
| Café Blend 500g (store) | ...66 c3 a9 20... | 16 |
| Café Blend 500g (main) | ...66 65 cc 81 20... | 17 |
The first stores the é as the single codepoint c3 a9 — that’s UTF-8 for U+00E9 (LATIN SMALL LETTER E WITH ACUTE). The second stores it as 65 cc 81 — a plain ASCII e followed by U+0301 (COMBINING ACUTE ACCENT). Two bytes became three. One character became two. Both render as “café”. Only one of them joins.
The same inspection in Python, without leaving the REPL:
import unicodedata
a = "Caf\u00e9 Blend 500g" # from store.example.com
b = "cafe\u0301 Blend 500g" # from example.com — wait, is it?
print(repr(a)) # 'Café Blend 500g'
print(repr(b)) # 'café Blend 500g' — repr() also hides it in some terminals
print([unicodedata.name(c) for c in a[3:5]])
# ['LATIN SMALL LETTER E WITH ACUTE', 'SPACE']
print([unicodedata.name(c) for c in b[3:6]])
# ['LATIN SMALL LETTER E', 'COMBINING ACUTE ACCENT', 'SPACE']
print(a == b) # False
repr() on Python 3 can still render combining sequences in a way that looks composed in your terminal, which is why walking the codepoints with unicodedata.name() is the reliable diagnostic. It names every character. There is no arguing with the names.
One caution: length() semantics vary by database. In PostgreSQL, length() counts characters and octet_length() counts bytes — the combining accent inflates both. In some engines length() counts bytes directly. Check both if you are unsure, but the hex dump settles the question regardless.
The Three Usual Suspects: NFC vs NFD Composition, Zero-Width Characters, and Lookalike Codepoints
Once you accept that identical-looking strings can differ, the failure modes fall into a short list. Scraped data hits essentially three of them.
Suspect one: composition form. Unicode stores “café” two ways. NFC (Normalization Form C) composes the base letter and accent into one codepoint: U+00E9. NFD (Normalization Form D) decomposes them: e + U+0301. Both are valid. Both are correct Unicode. They are not equal to each other, and no database will equate them for you unless you tell it to. macOS file paths are famously NFD; many web servers and CDNs serve NFC; content management systems do whatever the last editor’s keyboard did.
Suspect two: invisible characters. These are worse, because they break joins even when the composition form matches:
| Codepoint | Name | What it looks like | Where it comes from |
|---|---|---|---|
| U+200B | ZERO WIDTH SPACE | nothing at all | templating systems, “smart” word breaks |
| U+00A0 | NO-BREAK SPACE | a normal space | HTML , price formatting, thousands separators |
| U+FEFF | ZERO WIDTH NO-BREAK SPACE (BOM) | nothing at all | files saved with a byte-order mark |
| U+00AD | SOFT HYPHEN | nothing (until copy-paste) | hyphenation logic in CMS output |
| U+2060 | WORD JOINER | nothing at all | PDF extraction, some rich-text editors |
The non-breaking space deserves special contempt. It appears constantly in scraped prices and product titles because is the lazy developer’s way to keep ”€ 12,99” from wrapping. Your .strip() call removes U+0020 and nothing else. U+00A0 sails straight through into your staging table.
Suspect three: lookalike codepoints. The nastiest of the three, because it survives normalization. Cyrillic а (U+0430) is a different codepoint from Latin a (U+0061) and no amount of NFC normalization will reconcile them. Greek ο vs Latin o. Full-width A (U+FF21) vs ASCII A. This shows up in scraped data when a source is pasting from mixed-locale documents, or — less innocently — when a marketplace seller has deliberately obfuscated a brand name to dodge duplicate-listing filters.
Identify any of these on demand:
import unicodedata
def dump_codepoints(s):
for c in s:
name = unicodedata.name(c, f"UNNAMED U+{ord(c):04X}")
print(f"U+{ord(c):04X} {name} -> {c!r}")
dump_codepoints("Cafe\u0301\u00a0Blend\u200b")
# U+0043 LATIN CAPITAL LETTER C -> 'C'
# U+0061 LATIN SMALL LETTER A -> 'a'
# ...
# U+0301 COMBINING ACUTE ACCENT -> '́'
# U+00A0 NO-BREAK SPACE -> '\xa0'
# ...
# U+200B ZERO WIDTH SPACE -> '\u200b'
Print this for both clashing keys and the divergence is on the page in front of you. No more guessing.
Why Scraping Multiplies the Problem: Source Sites, HTML Entities, and Mixed-Normalization Pipelines
A single-source pipeline rarely hits this. The moment you run two scrapers against two different origins and land both outputs in one warehouse, you have a normalization-consistency problem whether you planned for one or not.
Here is the shape of it. Two scraper configs, superficially identical:
SOURCES = {
"store.example.com": {
"url": "https://store.example.com/catalog",
"formats": ["text"],
"extract_rules": {"product_name": "h2.product-title"},
# Server serves NFC. Entity decoding happens in our HTML parser.
},
"example.com": {
"url": "https://example.com/catalog",
"formats": ["text"],
"extract_rules": {"product_name": "h2.product-title"},
# Server emits NFD for accented titles (upstream CMS is NFD-heavy).
},
}
Same selectors. Same parser. Different bytes on the wire. The pipeline code is identical; the input is not, and nothing in your code is going to reconcile it by accident.
HTML entities make it worse. Consider two pages that both display “Café Blend”. One embeds the literal character in NFC:
<h2>Café Blend</h2>
The other uses a named entity that the page author typed because their editor mangled the character:
<h2>Café Blend</h2>
Most parsers decode é to U+00E9 — composed. But you will also meet e followed by a literal combining accent, or ́ (the combining acute accent on its own), which decodes to the decomposed sequence. Same rendered title, two byte sequences, depending entirely on how the page was authored. If you have ever wondered why one source’s accented titles join and another’s don’t, this is usually it.
Then there is the pipeline’s own contribution. A common one: extracting a formatted price column and treating its contents as a clean string.
# BEFORE: nbsp rides along into the key
price_text = "12\u00a099" # scraped from a -formatted price cell
key = f"{title}-{price_text}" # key now contains U+00A0
# AFTER: fold the space explicitly
price_text = "12\u00a099".replace("\u00a0", " ")
key = f"{title}-{price_text}"
If you route scrapes through an API rather than raw HTTP clients, the same rules apply to what comes back — the response body carries whatever the origin served, byte for byte. A POST to https://api.finedata.ai/api/v1/async/scrape with an Authorization: Bearer fd_your_api_key header and a JSON body like {"url": "https://example.com/catalog", "formats": ["text"], "extract_rules": {"product_name": "h2.product-title"}, "only_main_content": true} returns the page’s native bytes, and it is on you to canonicalize before load.
The extraction is not where the bug lives. The bug lives between extraction and insert, in the space where you assumed the text was already canonical. If you are building the ingest side of this, the three-layer pattern in Build a Three-Layer Scraping Pipeline: Raw, Clean, Curated gives you a natural home for the fix: normalization belongs in the clean layer, before anything is keyed.
Normalize Before Load: A Pre-Insert Cleaning Function for Join Keys
The fix is one function, applied unconditionally to every key column at ingest. Not “when we notice a problem” — every load, every source, every time.
import re
import unicodedata
# Invisible characters that should never survive into a join key.
_INVISIBLE_RE = re.compile(
"["
"\u200b\u200c\u200d\u200e\u200f" # zero-width space, ZWNJ, ZWJ, LRM, RLM
"\u2060" # word joiner
"\ufeff" # BOM
"\u00ad" # soft hyphen
"]+"
)
def clean_key(s: str) -> str:
"""Canonicalize a string so it joins across sources."""
if s is None:
return None
# 1. Strip zero-width and other invisible characters first
s = _INVISIBLE_RE.sub("", s)
# 2. Compose to NFC: e + U+0301 -> U+00E9
s = unicodedata.normalize("NFC", s)
# 3. Fold all whitespace (incl. U+00A0) to a single ASCII space
s = re.sub(r"\s+", " ", s.replace("\u00a0", " "))
# 4. Trim ends
return s.strip()
Step 3 deserves a comment. re.sub(r"\s+", " ", ...) alone does collapse U+00A0 in Python 3, because \s matches Unicode whitespace — but the explicit replace makes intent obvious and keeps the behavior identical if someone ports this to a language whose \s is ASCII-only. I have been bitten by that port; the explicit replace costs nothing.
The order matters, and it is the opposite of what you might reach for first. Strip the invisibles before normalizing. If a zero-width space is wedged between a base letter and its combining accent — e + ZWSP + U+0301 — NFC cannot compose across the interloper, because composition only combines adjacent canonical sequences. Normalize first and you are left with a decomposed e + U+0301 that stays decomposed even after the ZWSP is stripped later. Strip first, and by the time NFC runs the accent is already sitting adjacent to its base letter, so it composes cleanly to é.
Here is the corrected function run against the exact keys from our failing join — the NFC form from store.example.com, the NFD form from example.com, and two zero-width-injected variants:
import unicodedata
test_keys = [
"Caf\u00e9 Blend 500g", # NFC, as served by store.example.com
"Cafe\u0301 Blend 500g", # NFD, as served by example.com
"Caf\u00e9\u200b Blend 500g", # NFC with a zero-width space after the é
"Cafe\u200b\u0301 Blend 500g", # zero-width space between base letter and accent
]
for t in test_keys:
out = clean_key(t)
print(f"in {len(t):>2} chars -> out {len(out):>2} chars {out!r} NFC={unicodedata.is_normalized('NFC', out)}")
Actual output:
in 16 chars -> out 16 chars 'Café Blend 500g' NFC=True
in 17 chars -> out 16 chars 'Café Blend 500g' NFC=True
in 17 chars -> out 16 chars 'Café Blend 500g' NFC=True
in 18 chars -> out 16 chars 'Café Blend 500g' NFC=True
All four variants — including the nasty eighteen-character one where the ZWSP was blocking composition — collapse to the same sixteen-character NFC string. That string is what lands in both staging tables, and it is the string that joins.
Apply it to both datasets before insert:
rows_store = [{"product_name": clean_key(r["product_name"]),
"price": r["price"]} for r in scrape_store_example_com()]
rows_main = [{"product_name": clean_key(r["product_name"]),
"stock": r["stock"]} for r in scrape_example_com()]
insert_rows("staging_store_example_com", rows_store)
insert_rows("staging_example_com", rows_main)
If your database supports it, you can also enforce the same canonical form in SQL. PostgreSQL 13+ has normalize():
-- Canonicalize at query time (defense, not the primary fix)
SELECT normalize(product_name, NFC) FROM staging_example_com;
My strong opinion: do the normalization in Python at ingest, and use the SQL version only as an audit tool. If you rely on normalize() in every query, one teammate forgets it in one query and you are back to zero-row joins with no error message. Canonicalize once, at the boundary, and the warehouse only ever holds one form. This is the same argument as parsing at fetch time versus storing raw — there is a real trade-off, and I lean toward keeping the raw scrape archived separately so you can re-clean without re-scraping. The trade-offs are laid out well in Parse at Fetch Time vs Store Raw HTML.
Verify the Fix: Re-Run the Join and Add a Normalization Audit Query
Re-run the query that returned zero rows:
WITH store_a AS (
SELECT product_name, price
FROM staging_store_example_com
),
store_b AS (
SELECT product_name, stock
FROM staging_example_com
)
SELECT a.product_name, a.price, b.stock
FROM store_a a
JOIN store_b b ON a.product_name = b.product_name;
-- Result: 1,412 rows. The expected catalog overlap.
The join count should match the number of products you can visually confirm exist on both storefronts. When it does, the fix is real.
Now add the guard so drift cannot hide again. This audit query (PostgreSQL 13+) finds any row whose key is not already in canonical NFC form:
SELECT count(*) AS unnormalized_rows
FROM staging_example_com
WHERE product_name IS NOT NULL
AND product_name <> normalize(product_name, NFC);
-- 0 after the fix. Anything above 0 means a new source or a
-- code path skipped clean_key().
For a before/after sanity check, compare the keys directly:
| Metric | store.example.com (before) | store.example.com (after) | example.com (before) | example.com (after) |
|---|---|---|---|---|
| Length of “Café Blend 500g” | 16 | 16 | 17 | 16 |
| Hex around “é” | c3 a9 | c3 a9 | 65 cc 81 | c3 a9 |
| Joins against counterpart | no | yes | no | yes |
Both sides now carry identical bytes for the same visible text. That is the whole goal: equality that survives past the rendering layer.
Guardrails: Unit Tests and Pipeline Checks That Prevent Unicode Drift from Recurring
A one-time fix decays. The next scraper you add, the next source that switches CMS, the next teammate who writes an ad-hoc loader — any of them can reintroduce mixed-normalization keys. Guardrails are cheap; write them once.
First, a unit test that pins clean_key() to the exact failure modes we just debugged:
import unicodedata
from mypipeline.clean import clean_key
def test_clean_key_composes_nfd():
assert clean_key("cafe\u0301") == "caf\u00e9"
def test_clean_key_idempotent_on_nfc():
assert clean_key("caf\u00e9") == "caf\u00e9"
def test_clean_key_strips_zero_width():
assert clean_key("Blend\u200b 500g") == "Blend 500g"
def test_clean_key_strips_bom_and_soft_hyphen():
assert clean_key("\ufeffCaf\u00adé") == "Café"
def test_clean_key_folds_non_breaking_space():
assert clean_key("12\u00a099") == "12 99"
def test_clean_key_collapses_whitespace_runs():
assert clean_key("Blend \t 500g") == "Blend 500g"
def test_clean_key_output_is_nfc():
out = clean_key("cafe\u0301\u200b")
assert unicodedata.is_normalized("NFC", out)
Note the idempotence test. A cleaning function that changes already-clean data is a bug factory — you would be silently rewriting keys between loads.
Second, wire the audit into the load itself so the pipeline fails loudly instead of quietly corrupting keys. To make this runnable end-to-end, here is a toy table with one clean row and one NFD row — the NFD row is the plant that simulates a load path bypassing clean_key():
-- PostgreSQL 13+ (normalize() lives there)
CREATE TABLE staging_example_com (
id integer PRIMARY KEY,
product_name text,
stock integer
);
INSERT INTO staging_example_com (id, product_name, stock) VALUES
(1, 'Café Blend 500g', 12), -- NFC: passes the audit
(2, 'Cafe' || U&'\0301' || ' Blend 500g', 40); -- NFD: trips the audit
And the check itself:
def assert_keys_normalized(conn, table, key_col):
"""Fail the load if any key is not in canonical NFC form."""
unnormalized = conn.execute(f"""
SELECT count(*) FROM {table}
WHERE {key_col} IS NOT NULL
AND {key_col} <> normalize({key_col}, NFC)
""").fetchone()[0]
assert unnormalized == 0, (
f"{unnormalized} rows in {table}.{key_col} are not NFC. "
f"A load path is bypassing clean_key()."
)
# Called at the end of every ingest run, before the job reports success
assert_keys_normalized(conn, "staging_example_com", "product_name")
# AssertionError: 1 rows in staging_example_com.product_name are not NFC.
# A load path is bypassing clean_key().
Run it against the toy table and it fails on row 2 exactly as intended; re-insert that row through clean_key() and it passes. In production this is called at the end of every ingest run, before the job reports success:
assert_keys_normalized(conn, "staging_store_example_com", "product_name")
assert_keys_normalized(conn, "staging_example_com", "product_name")
This is deliberately paranoid. If the audit trips, someone added a code path that skips normalization, and you want the pipeline red, not a support ticket three weeks later asking why the weekly reconciliation is short 400 rows.
Finally, map each failure mode to the check that catches it, so the coverage is explicit rather than accidental:
| Failure mode | Example | Caught by |
|---|---|---|
| NFD composition from a source CMS | e + U+0301 | test_clean_key_composes_nfd, audit query |
| Zero-width space from templating | Blend\u200b | test_clean_key_strips_zero_width |
Non-breaking space from prices | 12\u00a099 | test_clean_key_folds_non_breaking_space |
| BOM from upstream files | \ufeffCafé | test_clean_key_strips_bom_and_soft_hyphen |
| Non-idempotent cleaner (regression) | re-cleaning changes output | test_clean_key_idempotent_on_nfc |
New load path skipping clean_key() | any of the above reappears | assert_keys_normalized fails the run |
One honest limitation: clean_key() does nothing about lookalike codepoints. Cyrillic а stays Cyrillic а. If your sources mix scripts, you need a transliteration or confusable-mapping step on top, and that is a genuinely harder problem with real false-positive risk — mapping every Cyrillic character to its Latin lookalike will happily merge distinct words in Russian-language data. Do not reach for that unless you have measured that script mixing is actually happening in your dataset.
Wrap-Up
The zero-row join on “identical” keys is almost never a database problem. It is a bytes problem: NFC vs NFD composition, invisible characters riding in from HTML entities and formatting, and — rarely but memorably — lookalike codepoints from mixed scripts. The rendered grid will never show you any of it. encode(..., 'hex') and unicodedata.name() will, in about thirty seconds.
The durable fix is boring and non-negotiable: one clean_key() function, applied to every key column at ingest, guarded by a normalization audit that fails the load on drift, and pinned by unit tests that enumerate the exact failure modes. Normalize at the boundary, store raw separately if you can afford the storage, and never let two sources define the “same” string differently again.
Related Articles
Your Prices Parse as NaN: Fix Number Extraction
Scraped prices like '$1,299.00' become NaN after type conversion. Trace the parsing failure, normalize messy number formats, and fix it before load.
TutorialYour Scraped Links Don't Resolve: Fix Relative URLs
Scraped hrefs like /products/42 wreck downstream crawls and dedupe keys. Learn how to resolve relative URLs to absolute ones before you store them.
TutorialFix JSONDecodeError: When Your Fetch Returns HTML, Not JSON
Your parser dies with JSONDecodeError line 1 column 1 because the response is an HTML error page. Learn to sniff the body, log it, and validate before parsing.