pandas: catch duplicate AI enrichment before a CRM import

One duplicate company can change your import

Imagine four leads waiting for company-level enrichment: two belong to company A, one to B and one to C. Your enrichment table contains two conflicting entries for A. A plain join can turn those four leads into six output rows. The import file now contains more records than the original batch, even though the enrichment step appeared to complete.

This AI-assisted editorial guide shows a proposed check between an AI enrichment worker and a CRM import. It is a documentation-based walkthrough using synthetic data. The Python example passed a syntax check, but local pandas execution was unavailable; the assertions below describe expected results for you to verify in your environment. No CRM import or AI model call was performed.

1. Agree on a stable company identifier

Keep the company_id supplied by your source system alongside each lead. Ask the enrichment worker to return that identifier unchanged. A similar-looking company name is insufficient for this exact-match join: an AI-generated label could refer to a subsidiary, a trading name or a different business.

The example removes surrounding whitespace and rejects null or blank company IDs on either input. pandas documents that null keys can match each other during a merge, so missing identifiers need an explicit rule. Here that rule is to stop the batch and repair the input. Do not replace every missing company with the same placeholder. [1]

2. Install the documented version separately

On October 15, 2026, PyPI lists pandas 3.0.6 with Python 3.11 or newer required. These PowerShell commands use that pinned version in a separate environment. Save both Python blocks below in order as enrichment_check.py, then run the last command. Installation requires internet access; the example data is entirely local. [2]

In a production CSV reader, preserve identifiers as strings so leading zeros survive. The small in-memory fixture below already uses string IDs. Also validate required columns and unique lead IDs before this step; the helper focuses on company matching rather than a complete import schema.

python -m venv .venv
.\.venv\Scripts\python.exe -m pip install "pandas==3.0.6"
# Save both Python blocks below as enrichment_check.py first.
.\.venv\Scripts\python.exe enrichment_check.py

3. Require one enrichment row per company

The left input contains leads, so repeated company IDs are allowed. The right input contains company-level enrichment and must have unique keys. validate="many_to_one" expresses that relationship; indicator=True adds a _merge column distinguishing matches from left-only records. Specify on="company_id" explicitly. [1]

The helper returns two tables: matched leads and pending leads. The variable ready means a key matched, not that an AI-generated segment is verified or approved. Keep a separate review policy for the enrichment’s evidence, freshness and suitability.

import pandas as pd

def prepare_leads(leads, enrichment):
    left, right = leads.copy(), enrichment.copy()
    for frame in (left, right):
        frame["company_id"] = frame["company_id"].astype("string").str.strip()
        keys = frame["company_id"]
        if keys.isna().any() or keys.eq("").any():
            raise ValueError("Missing company_id: repair the input before merging")
    joined = left.merge(
        right[["company_id", "segment"]],
        on="company_id", how="left",
        validate="many_to_one", indicator=True,
    )
    ready = joined.loc[joined["_merge"].eq("both")].copy()
    pending = joined.loc[joined["_merge"].eq("left_only")].copy()
    return ready, pending

4. Run the synthetic checks before using a real export

Append this fixture block to the same file. Its first case expects three matched leads and one pending lead, preserving four rows overall. Five rejection cases cover a duplicate right-hand key plus null and blank keys on each side. A final unchecked join illustrates the six-row outcome the validation is designed to catch.

These are expected assertions, not reported runtime observations. If a check fails, compare the installed version, input column names and source identifiers before changing the validation. Keep the failing fixture so a later pipeline change cannot silently reintroduce the problem.

# TESTS: synthetic records only; no CRM or AI service is connected.
leads = pd.DataFrame({
    "lead_id": ["L1", "L2", "L3", "L4"],
    "company_id": ["A", "A", "B", "C"],
})
enrichment = pd.DataFrame({
    "company_id": ["A", "B"],
    "segment": ["Demo agency", "Demo manufacturer"],
})
ready, pending = prepare_leads(leads, enrichment)
assert ready["lead_id"].tolist() == ["L1", "L2", "L3"]
assert pending["lead_id"].tolist() == ["L4"]
assert len(ready) + len(pending) == len(leads)
print("Valid input: 3 matched, 1 pending; 4 rows preserved")

def expect_rejection(name, left, right, error):
    try:
        prepare_leads(left, right)
    except error:
        print(name + ": rejected")
    else:
        raise AssertionError(name + ": should have failed")

duplicate = pd.concat([enrichment, enrichment.iloc[[0]]], ignore_index=True)
expect_rejection("Duplicate enrichment key", leads, duplicate, pd.errors.MergeError)
for side in ("left", "right"):
    for label, value in (("null", None), ("blank", "   ")):
        a, b = leads.copy(), enrichment.copy()
        (a if side == "left" else b).loc[0, "company_id"] = value
        expect_rejection(f"{side} {label} key", a, b, ValueError)

# Negative control: the unchecked join multiplies the two A leads.
unchecked = leads.merge(duplicate, on="company_id", how="left")
assert len(unchecked) == 6
print("Unchecked control: 4 input leads became 6 rows")

5. Resolve conflicts and keep unmatched leads visible

The pandas merging guide explains why repeated keys can generate combinations of matching rows, and how validation checks uniqueness before joining. A duplicate company in the enrichment table should therefore trigger investigation before the batch reaches an importer. [3]

Do not immediately drop duplicates and keep whichever row happens to appear first. For a real enrichment workflow, define how competing records are resolved: for example, an approved source and a review timestamp. Preserve the rejected versions and the decision reference in your own audit process. The toy segment values here are illustrative labels, not actual company classifications.

Route pending leads back to the enrichment queue or a reviewer, retaining their original lead IDs. Reconcile matched plus pending counts with the original batch. Before enabling a CRM write, map accepted fields deliberately and test the import in a sandbox. This join check prevents one kind of row multiplication; existing duplicate leads, incorrect classifications and repeated import submissions need their own controls.

Sources & further reading

1. pandas.merge API: validation, indicator and null-key behavior
2. PyPI: pandas 3.0.6 and Python requirements
3. pandas user guide: merge key uniqueness

Put checks between AI enrichment and your CRM

Sociologix can help design lead enrichment, validation, review queues and CRM handoffs around your actual data. Bring a sample schema and the decisions your team needs to approve.