Skip to content
Tutorial 17 min read

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.

FE
FineData Engineering · Editorial Policy
|
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 500gCafé Blend 500g
Espresso Dark 1kgEspresso Dark 1kg
Filter Roast 250gFilter 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:

RenderedHex (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:

CodepointNameWhat it looks likeWhere it comes from
U+200BZERO WIDTH SPACEnothing at alltemplating systems, “smart” word breaks
U+00A0NO-BREAK SPACEa normal spaceHTML  , price formatting, thousands separators
U+FEFFZERO WIDTH NO-BREAK SPACE (BOM)nothing at allfiles saved with a byte-order mark
U+00ADSOFT HYPHENnothing (until copy-paste)hyphenation logic in CMS output
U+2060WORD JOINERnothing at allPDF 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&eacute; Blend</h2>

Most parsers decode &eacute; to U+00E9 — composed. But you will also meet &#x65; followed by a literal combining accent, or &#769; (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 &nbsp;-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:

Metricstore.example.com (before)store.example.com (after)example.com (before)example.com (after)
Length of “Café Blend 500g”16161716
Hex around “é”c3 a9c3 a965 cc 81c3 a9
Joins against counterpartnoyesnoyes

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 modeExampleCaught by
NFD composition from a source CMSe + U+0301test_clean_key_composes_nfd, audit query
Zero-width space from templatingBlend\u200btest_clean_key_strips_zero_width
Non-breaking space from &nbsp; prices12\u00a099test_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 outputtest_clean_key_idempotent_on_nfc
New load path skipping clean_key()any of the above reappearsassert_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 &nbsp; 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.

#unicode #data-cleaning #deduplication #python #text-normalization #slot:problems-first

Related Articles