data agent misreading null vs empty string in dedupe: explicit null semantics

TL;DR

The agent's dedupe treats NULL and empty string inconsistently, so it merges rows it should keep or keeps rows it should merge. Define explicit null semantics once: pick whether empty string means NULL or means a real value, normalize every record to that rule, and build the dedupe key from normalized values.

Symptom

A data agent deduplicating records mishandles NULL vs empty string: sometimes they compare equal and distinct rows get merged, sometimes they compare unequal and true duplicates survive. The behavior shifts between runs because different code paths apply different implicit rules.

When to use

• Dedupe keeps duplicates it should merge, or merges rows it should keep
• NULL vs empty string handling differs across code paths
• Dedupe results change between runs on the same data

When not to use

• Dedupe is fuzzy or similarity-based (thats a matching threshold problem)
• Duplicates come from re-ingestion without stable ids (fix the ids first)
• The data has no nulls or empty strings at all (the rule is vacuous)

Steps

1. Audit how the two values actually appear in your data URIs

SELECT count(*) FILTER (WHERE email IS NULL) AS nulls,
       count(*) FILTER (WHERE email = '') AS empties
FROM contacts;

Expected output: the real split. Most teams are surprised by how many of each they have; the rule must cover both.

2. Pick one rule and write it down: either empty string normalizes to NULL, or they stay distinct. There is no universally right answer, only a consistent one:

Rule: empty string means unknown, same as NULL. Normalize '' to NULL everywhere before dedupe.

Expected output: a documented decision. The bugs come from the rule being implicit and different in each author's head.

3. Normalize at the dedupe boundary with NULLIF so the key is built from clean values:

SELECT DISTINCT ON (NULLIF(email, ''), NULLIF(phone, '')) * FROM contacts;

Expected output: dedupe keys treat '' and NULL identically per the rule. Every path that builds a key uses the same normalization.

4. Enforce the rule at ingestion too, so raw data arrives already normalized:

record["email"] = record["email"] or None

Expected output: new records cannot reintroduce the ambiguity. A boundary-only fix rots as soon as a new writer skips it.

Variant phrasings

dedupe treating null and empty as different

Apply NULLIF in the key. If the business rule says they are the same, the key must reflect it.

dedupe merging rows with missing emails

Missing emails all normalize to NULL and then match each other. Exclude null-keyed records from auto-merge, or require a second matching field.

Why it happens

SQL says NULL never equals anything, including NULL, while empty string is a perfectly ordinary value. Application code in Python or JS often treats both as falsy and therefore equal. An agent writing dedupe logic mixes these two worlds without noticing, and each function it writes picks whichever convention was nearest at hand.

Edge cases

• Normalizing '' to NULL loses information if some source genuinely means empty-but-known. Check with the data owner before collapsing the distinction.
• Composite keys with multiple nullable fields need the rule applied per field, not just on the concatenated string.
• Some warehouses treat empty strings as NULL on load (Oracle-style). Know your warehouse's behavior or the ingestion layer will fight the dedupe layer.

Provenance

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