🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
Как хранить историю изменений в DWH
Существует такая проблема, когда при обновлении строк в таблицах исторические данные пропадают. Но очень часто требуется сохранить всю хронологию изменений: хранить не только новые, но и старые данные. Это нужно как и для регуляторных требований, так и для бизнесовых (например, когда старые метрики пересчитываются задним числом с новыми данными).
Говоря в контексте схем БД, прежде всего речь идет об обновлении таблиц измерений: изменившиеся данные куда-то нужно девать, и записать хронологию в этом случае сложнее, чем в таблице фактов. Как это всё осуществить архитектурно?
🟠На помощь приходит подход Slowly Changing Dimensions (SCD). Идея в том, чтобы трансформировать таблицу измерений в более информативную. Например, в таблице "Клиенты" помимо содержащихся там полей:
▶️id
▶️ФИО
▶️Сегмент и т.п.
добавить еще поля:
▶️Дата начала
▶️Дата окончания
▶️Флаг текущей версии
▶️id представить как 2 ключа: бизнес ключ (старый id, например номер договора) и суррогатный ключ
Отдельно прокомментирую разбивку на ключи. Это нужно для того, чтобы у строки был уникальный идентификатор. Потому что бизнес ключ остается одним и тем же - он определяет клиента Иванова И.И. как отдельного человека (например по уникальному номеру договора). Но суррогатный ключ, если можно так сказать - "технический" в рамках таблицы измерений, и постоянно меняется с каждой новой версией.
Допустим, у Иванова И.И. обновился сегмент. Как это будет выглядеть для ключей:
БК = 1; СК = 1; ФИО = Иванов И.И; Сегм = РБ
-------------------------------
БК = 1; СК = 2; ФИО = Иванов И.И; Сегм = МСБ
🟠На фоне этого возникает вопрос: как джойнить эту таблицу с фактами? Ведь до SCD таблицы соединялись по БК, который всегда был уникальным, а теперь он стал дублироваться. Решение простое - нужно теперь джойнить по СК, а для этого его необходимо добавить еще и в таблицу фактов как дополнительный атрибут.
Сделать это можно постфактум. Например, в таблице фактов отражено, что 1-ого числа была совершена продажа Иванову И. И. В клиентской таблице смотрим, какой СК был актуален у Иванова И. И. на 1-ое число, и просто заносим его в факты, предварительно добавив там новое поле.
И так нужно сделать для каждого измерения, которое связывается с фактами. Если таких измерений, например, 5, то в фактах у нас будет 5 столбцов с СК для каждого из них.