Как построить суррогатный ключ для разных типов кредитных договоров с пересекающимися номерами

Что проверяют

Понимаете ли вы разницу между бизнес-ключом и суррогатным и умеете ли сделать ключ стабильным, когда номера договоров из разных систем совпадают. Следом спрашивают, что будет с ключом при перезагрузке данных и при изменении атрибутов договора.

Как отвечать

Начните с бизнес-ключа. Номер сам по себе не уникален, уникальна тройка: система-источник, тип договора (кредит наличными, ипотека, карта) и номер. Все части нормализуются: trim, единый регистр, одинаковый формат номера.

Дальше два рабочих способа получить суррогат:

• последовательность плюс таблица соответствий: в хранилище заводится BIGINT из IDENTITY или SEQUENCE, а key map хранит пару «бизнес-ключ → суррогат». Ключ компактный и быстрый в джойнах, но при загрузке нужен поиск по таблице, а параллельные загрузки надо синхронизировать;
• хэш от бизнес-ключа: MD5 или SHA-256 от склеенных нормализованных полей, как хэш-ключ хаба в Data Vault 2.0. Он детерминирован и считается в любой системе без обращения к хранилищу. В dbt для этого есть dbt_utils.generate_surrogate_key.

Правила склейки: явный разделитель, которого нет в данных, и явная замена NULL на метку. В Spark concat_ws молча пропускает NULL, поэтому без coalesce разные записи могут получить одинаковую строку.

Если в измерении ведётся история по SCD2, у каждой версии свой суррогат, а для связи версий одного договора держат отдельный постоянный ключ.

Где спотыкаются

Добавляют в ключ дату загрузки или случайный uuid4. Тогда при каждой перезагрузке тот же договор получает новый ключ, и связи с фактами рвутся.

Склеивают поля без разделителя: номер 1 с типом 23 и номер 12 с типом 3 дают одну и ту же строку 123.

Включают в ключ изменяемые атрибуты вроде клиента или филиала. При переуступке или реструктуризации ключ меняется, и история договора разваливается на два объекта.