Слияние данных из разных источников

Слияние данных из разных источников

Слияние данных — это процесс объединения разнородных потоков (БД продуктов, CRM, платежные провайдеры, логи событий, сторонние реестры) в целостные сущности и консистентные витрины. Цель — получить «золотую запись» (Golden Record) и согласованные разрезы для аналитики, ML и операционных кейсов.

1) Типовые сценарии и цели

360° по сущности: клиент/игрок, устройство, платежный инструмент, мерчант.
Консолидация транзакций: несколько PSP/касс → единый журнал с обязательной идемпотентностью.
Нормализация событий: веб/мобайл/бэкенд-логи → единый словарь событий.
Обогащение: внешние справочники (гео, FX, AML/санкции, маркетинговые источники).
Единые метрики: согласование валют/таймзон, схем и кодировок.

2) Контракты источников и схемы

• Схема: поля, типы, нуллабельность, ключ(и), домены значений.
• Семантика: что означает каждое поле (словари).
• SLA: свежесть/частота, максимальная задержка и out-of-order.
• Эволюция: политика изменения схем (backward/forward), deprecation.
• Качество: уникальность ключей, допустимые диапазоны, референциальная целостность.

3) Идентификация: ключи и сопоставление (record linkage)

3.1. Жесткие идентификаторы

Естественные ключи: `user_id`, `transaction_id`, `device_id`, `iban`.
Прокси-ключи: e-mail/телефон (с нормализацией: регистр, пробелы, коды стран).
Суррогаты: `surrogate_id` в хаб-таблицах при отсутствии универсального ключа.

3.2. Мягкие правила сопоставления

Детерминированные: точное совпадение нормализованного e-mail + ДР; «дом»/«моб» телефон → E.164.
Вероятностные (фаззи): Jaro-Winkler/Levenshtein для имени/адреса, TF-IDF/эмбеддинги для строк, «блокировка» (blocking) по грубым хэшам/префиксам для ускорения.
Графовые подходы: сущности как узлы, совпадения как ребра; кластеризация компонент связности.
Стратегия «step-up»: от строгих к мягким правилам с ручной ревью «на границе».

3.3. Правила консолидации (survivorship)

Приоритет источника: «KYC-реестр > CRM > логи», когда есть конфликт значений.
Свежесть: более новая метка времени побеждает (с поправкой на достоверность).
Заполненность: prefer non-NULL; слияние адресов/тегов объединением множеств.
Аудит: сохраняйте «след решения» — что перезаписали и почему.

4) Дедупликация и MDM

MDM-слой (Master Data Management): таблицы «мастер-сущностей» + связи «источник→мастер».
Golden Record: агрегированная запись с полем `confidence`/источником правды.
История: SCD-тип 2 для атрибутов, зависящих от времени (адрес, статус KYC).
Идентичности: таблицы мерджей (merge map) с датами «слияния»/«расплитования».

5) Потоки изменений: CDC, опоздавшие и дубликаты

CDC (Change Data Capture): события `insert/update/delete` + `source_lsn`/offset.
Опоздавшие события: водяные метки (watermarks) и окна ожидания (grace period), хранение поздних апдейтов для корректировок.
Out-of-order: сортировка по ключу и времени, компенсирующие апдейты.
Дубликаты: идемпотентные ключи (`event_id`, `idempotency_key`), дедуп в окне.
Exactly-once: транзакционные сингс/стор, `MERGE` с детерминированной логикой.

6) Таймзоны, валюты и календарь

Время: хранить в UTC + локализованные срезы; явно хранить `ingested_at` и `event_time`.
Валюты: хранить «сырую валюту» и нормализованную `base_ccy` с курсом на дату операции.
Календари: таблицы праздников/рабочих дней по регионам для честных сравнений.

7) Псевдо-SQL для слияния (upsert/merge)

7.1. Транзакции (идемпотентный журнал)

sql
MERGE INTO fact_transactions t
USING staging_transactions s
ON t. txn_id = s. txn_id
WHEN MATCHED AND s. updated_at > t. updated_at THEN
UPDATE SET amount = s. amount,
currency = s. currency,
status = s. status,
updated_at = s. updated_at
WHEN NOT MATCHED THEN
INSERT (txn_id, user_ext_id, amount, currency, status, event_time, updated_at)
VALUES (s. txn_id, s. user_ext_id, s. amount, s. currency, s. status, s. event_time, s. updated_at);

7.2. «Золотая запись» пользователя (приоритет источника + свежесть)

sql
WITH ranked AS (
SELECT s. ext_user_id,
s. norm_email,
s. phone_e164,
s. addr_struct,
s. source,
s. updated_at,
ROW_NUMBER() OVER (
PARTITION BY s. ext_user_id
ORDER BY
CASE s. source
WHEN 'KYC' THEN 1 WHEN 'CRM' THEN 2 ELSE 3 END,
s. updated_at DESC
) AS rn
FROM staging_users s
)
MERGE INTO dim_user_golden g
USING ranked r
ON g. ext_user_id = r. ext_user_id
WHEN MATCHED AND r. rn = 1 THEN
UPDATE SET email = COALESCE(r. norm_email, g. email),
phone = COALESCE(r. phone_e164, g. phone),
address = COALESCE(r. addr_struct, g. address),
source_of_truth = r. source,
updated_at = r. updated_at
WHEN NOT MATCHED AND r. rn = 1 THEN
INSERT (ext_user_id, email, phone, address, source_of_truth, updated_at)
VALUES (r. ext_user_id, r. norm_email, r. phone_e164, r. addr_struct, r. source, r. updated_at);

8) Качество и тестирование

Схема-тесты: обязательные поля, типы, домены.
Логика-тесты: уникальность ключа, отсутствие дубликатов, нет «назад во времени».
Сверки (reconciliation): суммы по источнику vs итоговая витрина; расхождения → тикеты.
Профилирование: распределения, доля NULL, «длинные хвосты».
Метрики слияния: precision/recall сопоставления, доля «CONFLICT», % записей с confidence ≥ порога.

9) Наблюдаемость и SLO

SLO свежести: лаг витрин ≤ N минут/часов; мониторинг задержек и backlog.
Алерты: рост дубликатов, всплеск конфликтов, падение coverage ключей.
Логи lineage: из какого источника взяли поле, когда и кем перезаписано.
Рунибуки: сценарии инцидентов (опоздавшие партии, шторма CDC, неверный FX).

10) Безопасность, приватность, комплаенс

PII: псевдонимизация, хэширование идентификаторов, маскирование в BI.
RLS/CLS: доступ по ролям и строкам; экспорт — с токенами и сроком годности.
Срок жизни данных: графики хранения; право на удаление (DSAR) и «legal hold».
Анти-соединение (re-identification): правила минимизации джойнов чувствительных таблиц.

11) Организация моделей и данных

Слои: `raw` (как есть) → `staging` (очистка/нормализация) → `core` (мастер-сущности, факт/измерения) → `marts` (витрины под аналитику/ML).
SCD: тип 2 для атрибутов, тип 1 — для исправления ошибок; явные `valid_from/valid_to`.
Feature Store: функции преобразований идентичны онлайн/офлайн; point-in-time корректность.

12) Паттерны реализации

ELT с семантическим слоем: логика слияния описана декларативно (правила, приоритеты, ключи).
Стрим + микробатч: для near-real-time витрин — микробатчи 1–15 мин с watermarks.
Graph-linkage: отдельный граф-хаб для сложной идентификации (девайсы, карты, адреса).
Step-up валидация: новые правила linkage включать в shadow-режиме, собирать метрики точности.

13) Чек-лист перед релизом контура слияния

• Контракты источников подписаны; схемы и словари полей согласованы
• Определены ключи/правила linkage; есть стратегия дедупликации
• Заданы правила survivorship и приоритеты источников; аудит-лог включен
• CDC/идемпотентность/обработка поздних данных реализованы
• Валюты/таймзоны/календарь нормализованы
• Тесты качества и сверки настроены; дашборды наблюдаемости есть
• SLO свежести и доступности зафиксированы; алерты и рунибуки готовы
• PII/доступы/хранение соответствуют требованиям комплаенса
• Документация: паспорт сущности, схема lineage, примеры запросов

14) Паспорт «золотой записи» (шаблон)

Сущность: `USER_GOLDEN`

Ключ: `user_master_id` (surrogate), мэппинги `source_user_id[]`

• `email`: нормализация + приоритет `KYC > CRM > LOGS`
• `phone`: нормализация E.164, сплит по верификации
• `name`: Jaro-Winkler ≥ 0.92, fallback — источник «KYC»
• `address`: составной объект; объединение + приоритет свежести
• История: SCD2 (`valid_from/valid_to`)
• Lineage: ссылочный список полей-доноров
• Качество: coverage≥98%, дубликаты≤0.3%
• SLO: свежесть ≤ 1 ч, доступность ≥ 99.9%
• Владельцы: Data Platform, KYC/AML
• Риски: коллизии имен, «семейные» телефоны, shared-devices

15) Итоги и рекомендации

Слияние — это не только «JOIN по ключу», а контур: контракты источников → идентификация и дедуп → приоритеты и «золотая запись» → CDC и опоздавшие → качество и наблюдаемость → безопасность и история изменений.
Стройте правила прозрачно, храните аудит каждого решения, поддерживайте SCD и exactly-once. Так данные из десятков источников превращаются в надежные витрины и устойчивые метрики для продукта, аналитики и ML.