… SCD продолжение

Продолжаю с хранением справочников. Типы, которых нет в русской вики:

4️⃣ тип хранения при котором в справочнике содержится только актуальное на текущий момент состояние справочника, а рядом существует таблица (часто с суффиксом _history), в которой хранятся все предыдущие состояния с указанием периодов актуальности. У такого типа хранения есть преимущетсва по скорости доступа как у SCD1, но при этом есть возможность заглянуть по истории назад как в SCD2.

Пример: Справочник сотрудников, разбитый на две таблицы: personal и personal_hist. В первой таблице храняться все сотрудники на текущий момент в актуальном состоянии должностей, принадлежностей к отделам. В таблице с суффиксом hist хранится вся история изменений состояния.

5️⃣ тип хранения при котором часть справочника живет по второму типу, а часть вырождается в отдельный справочник, который живет по первому типу. Таким образом, у строки состояния справочника второго типа появляется свой собственный справочник с уникальным ключем, в котором хранится актуальное состояние.

Пример: Такой же справочник сотрудников как в примере выше, но у которого принадлежность к организационной структуре вынесена в отдельный справочник через внешний ключ. И этот отдельный справочник уже ведется по SCD1 для ускорения доступа.

6️⃣ тип хранения, в котором опять миксуются предыдущие типы для достижения каких-то бизнес целей и повышения производительности чтения. Здесь соединены SCD2 и SCD3. По части полей содержится актуальное состояние, а по части полей есть историчность с указанием периода актуальности.

Пример: Справочник моделей телефонов, в котором указано расположение их на планограмме выкладки, а так же какие-то справочные данные по поставщикам. Может возникнуть ситуация, когда товары перемещаются по планограмме и возникает необходимость сверки текущего состояния с состоянием на произвольный момент времени. Тогда имеет смысл в SCD2 справочник добавить колонку «current_position», которая будет содержать актуальную позицию товара во всех строках.

7️⃣ тип хранения, в чем-то схожиый с SCD5. Здесь так же справочник разбивается на две части: одна хранится по SCD2, а другая по SCD1. Но при этом в таблице фактов содержатся две ссылки: на основной справочник и на справочник с актуальными значениями характеристик.

💭Небольшой итог. Все типы хранения справочников, которые описаны выше могут применяться как в аналитических хранилищах, так и в OLTP базах. Выбор применения диктуется задачами, которые поставлены перед хранением. Где-то выгодней один раз потратиться на расчет, чтобы потом было удобно доставать данные, где-то хранилище используется только как историчное и там важно получить снепшоты всего, что происходило, но выбирать оттуда данные нужно будет раз в год.

SCD2 сейчас используется как универсальный метод хранения, который обеспечивает понятный ETL и понятные сценарии чтения. Его и просят описать на собесах. При этом, если отойти от собесов, то можно провести эксперимент и попробовать вынести более часто меняющиеся состояния в отдельный справочник и одним лишним джойном сэкономить на хранении излишних строк в широком справочнике.