Стратегии дедупликации данных

Введение

В ДатаТех 15% записей клиентов — дубликаты. Один и тот же человек представлен как “Иван Петров” (CRM), “И. Петров” (Metabase) и “[email protected]” (PostgreSQL). Дубликаты искажают аналитику, увеличивают затраты на маркетинг и нарушают compliance. В этом уроке мы разберём стратегии обнаружения и устранения дубликатов.

Exact vs Fuzzy Deduplication

Exact Duplicates

Записи, идентичные по всем полям. Самый простой случай:

-- Найти exact duplicates (все поля совпадают)
SELECT name, email, phone, COUNT(*) AS cnt
FROM customers
GROUP BY name, email, phone
HAVING COUNT(*) > 1;

Fuzzy Duplicates

Записи, описывающие одну и ту же сущность, но с различиями:

Fuzzy-дубликаты — основная проблема. Их нельзя найти простым GROUP BY.

Детерминистическое сопоставление

Детерминистическое сопоставление — matching по точным совпадениям отдельных полей:

-- Стратегия 1: Same email, different name
SELECT a.customer_id AS id_1, b.customer_id AS id_2,
       a.email, a.name AS name_1, b.name AS name_2
FROM customers a
JOIN customers b
  ON a.email = b.email AND a.customer_id < b.customer_id
WHERE a.name != b.name;

-- Стратегия 2: Same phone (normalized), different email
SELECT a.customer_id AS id_1, b.customer_id AS id_2,
       a.phone, a.email AS email_1, b.email AS email_2
FROM customers a
JOIN customers b
  ON REPLACE(REPLACE(a.phone, '+7', '8'), '-', '')
   = REPLACE(REPLACE(b.phone, '+7', '8'), '-', '')
  AND a.customer_id < b.customer_id
WHERE a.email != b.email;

Уровни уверенности

Результаты сопоставления классифицируются по уровню уверенности:

Golden Record

После обнаружения дубликатов создаётся Golden Record — единая мастер-запись:

def create_golden_record(duplicates):
    """Создать Golden Record из группы дубликатов.

    Правила:
    - name: самое длинное (полная форма)
    - email: первое non-null
    - phone: нормализованный формат +7
    - created_at: минимальный (самая ранняя регистрация)
    - updated_at: максимальный (последнее обновление)
    """
    golden = {
        "name": max(
            (d["name"] for d in duplicates if d.get("name")),
            key=len, default=None
        ),
        "email": next(
            (d["email"] for d in duplicates if d.get("email")),
            None
        ),
        "phone": normalize_phone(
            next((d["phone"] for d in duplicates if d.get("phone")), None)
        ),
        "created_at": min(d["created_at"] for d in duplicates),
        "updated_at": max(d["updated_at"] for d in duplicates),
    }
    return golden

Стратегии merge

Dedup в SQL (оконные функции)

-- Дедупликация через ROW_NUMBER
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY updated_at DESC NULLS LAST
    ) AS rn
  FROM customers
)
-- Оставляем только первую (самую свежую) запись для каждого email
SELECT * FROM ranked WHERE rn = 1;

Сценарий: ДатаТех

Сценарий: DataTech Solutions (ДатаТех Солюшенз)

После миграции CRM в ДатаТех обнаружили 5% дубликатов в таблице customers (2,500 из 50,000 записей). Декомпозиция:

• Exact duplicates: 400 записей (200 пар) — одна и та же запись загружена дважды при миграции
• Same email, different name: 1,200 записей (600 пар) — сокращения, опечатки, транслитерация
• Same phone, different email: 900 записей (450 пар) — один человек с несколькими email

Алексей реализовал трёхэтапный процесс:

1. Exact duplicates — автоматический merge (newest wins)
2. Same email — автоматический merge с review исключений (когда name слишком различается)
3. Same phone — ручной review Data Steward (низкая confidence)

Итоги

• Exact duplicates — полное совпадение всех полей; обнаруживаются GROUP BY HAVING COUNT > 1
• Fuzzy duplicates — одна сущность с различиями в написании; требуют сопоставления по ключевым полям
• Уровни уверенности (High, Medium, Low) определяют: автоматический merge, review или manual resolution
• Golden Record — мастер-запись, созданная по правилам merge (newest wins, most complete, field-level best)
• Оконная функция ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) — основной SQL-инструмент дедупликации

В следующем уроке мы перейдём к dbt — инструменту, который превращает ad-hoc SQL-проверки в автоматические тесты качества, встроенные в data pipeline.