Data Deduplication: Hash, Fuzzy Matching und Record Linkage im Praxiseinsatz

import pandas as pd
import hashlib

def hash_record(row: pd.Series, key_columns: list[str]) -> str:
    composite_key = "|".join(
        str(row[col]).strip().lower() if pd.notna(row[col]) else ""
        for col in key_columns
    )
    return hashlib.sha256(composite_key.encode()).hexdigest()

df = pd.read_parquet("s3://raw/orders/2026/04/03/batch_*.parquet")
KEY_COLS = ["order_id", "customer_id", "placed_at", "total_cents"]
df["dedup_hash"] = df.apply(hash_record, axis=1, key_columns=KEY_COLS)
deduped = df.drop_duplicates(subset=["dedup_hash"], keep="first")
print(f"Entfernt: {len(df) - len(deduped):,} Duplikate ({(len(df) - len(deduped)) / len(df):.1%})")

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id, customer_id, DATE_TRUNC('second', placed_at)
            ORDER BY ingested_at ASC
        ) AS row_num
    FROM raw.orders
)
SELECT * EXCLUDE (row_num) FROM ranked WHERE row_num = 1;

import pandas as pd
from thefuzz import fuzz

customers = pd.DataFrame({
    "customer_id": ["C001", "C002", "C003", "C004", "C005"],
    "name": ["John Smith", "Jon Smith", "Jonathan Smith", "Jane Doe", "J. Doe"],
    "email": ["john@acme.com", "jon@acme.com", "jonathan@acme.com", "jane@acme.com", "j.doe@acme.com"],
})

def find_fuzzy_duplicates(df: pd.DataFrame, name_col: str, threshold: int = 85) -> list[tuple]:
    duplicates = []
    names = df[name_col].tolist()
    for i in range(len(names)):
        for j in range(i + 1, len(names)):
            score = fuzz.token_sort_ratio(names[i], names[j])
            if score >= threshold:
                duplicates.append((df.index[i], df.index[j], score))
    return duplicates

pairs = find_fuzzy_duplicates(customers, "name", threshold=85)

import jellyfish
customers["soundex_block"] = customers["name"].apply(
    lambda x: jellyfish.soundex(x.split()[0])
)
for block, group in customers.groupby("soundex_block"):
    if len(group) > 1:
        pairs = find_fuzzy_duplicates(group, "name", threshold=85)

import pandas as pd
import recordlinkage

crm = pd.DataFrame({
    "name": ["Alice Johnson", "Bob Martinez", "Carol White"],
    "email": ["alice@example.com", "bob@example.com", "carol@example.com"],
    "postcode": ["10115", "10117", "10119"],
})
erp = pd.DataFrame({
    "company_contact": ["A. Johnson", "Robert Martinez", "Carol White-Smith"],
    "contact_email": ["alice@example.com", "b.martinez@example.com", "carol@example.com"],
    "zip": ["10115", "10117", "10119"],
})

indexer = recordlinkage.Index()
indexer.block("postcode", "zip")
candidate_pairs = indexer.index(crm, erp)

comparer = recordlinkage.Compare()
comparer.string("name", "company_contact", method="jarowinkler", label="name_sim")
comparer.exact("email", "contact_email", label="email_exact")
comparer.exact("postcode", "zip", label="postcode_exact")

features = comparer.compute(candidate_pairs, crm, erp)
matches = features[features["email_exact"] == 1]

Duplikate aus Doppel-Ingestion / Retries?
  └─ JA → Hash-basiert

Duplikate aus Tippfehlern / Abkürzungen?
  └─ JA → Fuzzy Matching (mit Blocking)

Zwei Datasets ohne gemeinsamen Key?
  └─ JA → Record Linkage

Mix aus allem?
  └─ JA → Pipeline: erst Hash, dann Fuzzy, dann Linkage

Raw Data
   ↓
[1] Hash-basiert (exakte Duplikate)
   ↓
[2] Standardisierung (lowercase, normalize)
   ↓
[3] Fuzzy Matching in Blocks
   ↓
[4] Human Review Queue (80–90 % Similarity)
   ↓
[5] Golden Record (Field-by-Field merge)
   ↓
Deduplicated Output