Type 3 (add column) и Type 4 (mini-dimension / history table)

Type 2 хранит полную историю — но дорого: каждое изменение это новая строка. Иногда столько не нужно, а иногда Type 2 порождает катастрофически много строк. Каталог SCD предлагает ещё два решения для таких случаев.

Type 3 хранит не всю историю, а ровно одно прошлое значение — в отдельном столбце рядом с текущим. Type 4 решает другую беду: когда часть атрибутов dimension меняется так часто, что Type 2 раздул бы dimension до неприличия, эти атрибуты выносят отдельно.

Type 3 — Add new attribute (column)

SCD Type 3 добавляет рядом с атрибутом «текущее значение» отдельный столбец «предыдущее значение». При изменении атрибута текущее значение переезжает в столбец «предыдущее», а в «текущее» записывается новое. Хранится по-прежнему одна строка на сущность.

Ключевое ограничение Type 3: в столбце «предыдущее» помещается ровно одно значение. Когда атрибут изменится во второй раз, самое старое значение будет потеряно — его вытеснит более свежее «предыдущее».

CREATE TABLE dim_territory (
    territory_key      INT PRIMARY KEY,
    territory_name     VARCHAR(80),
    current_region     VARCHAR(40),       -- актуальное значение
    previous_region    VARCHAR(40),       -- Type 3: ровно одно прошлое значение
    region_changed_date DATE              -- когда произошла смена
);

При смене региона:

UPDATE dim_territory
SET previous_region = current_region,        -- текущее уезжает в "предыдущее"
    current_region  = 'Северо-Запад',        -- новое становится текущим
    region_changed_date = DATE '2026-03-01'
WHERE territory_key = 42;

До UPDATE:
 territory_key | current_region | previous_region
---------------+----------------+----------------
 42            | Запад          | (NULL)

После UPDATE:
 territory_key | current_region | previous_region
---------------+----------------+----------------
 42            | Северо-Запад   | Запад

Когда Type 3 уместен? Для редких, заранее известных, «разовых» изменений, где интересно сравнить «до» и «после», а промежуточные шаги не важны. Классический пример — ребрендинг: компания один раз переименовала торговые территории, и аналитикам полезно видеть рядом старое и новое название, чтобы сопоставлять отчёты до и после реформы. Type 3 даёт это одним столбцом — без размножения строк, как в Type 2.

Сильная сторона Type 3 — именно одновременная видимость двух значений в одной строке. В Type 2, чтобы сравнить «было» и «стало», нужно достать две строки-версии и сопоставить их. В Type 3 оба значения лежат рядом, в одной строке, и сравнение — это просто два столбца в одном SELECT. Для сценария «показать каждую территорию со старым и новым названием» это удобнее Type 2. Но удобство оплачено жёстким ограничением в одно прошлое значение — поэтому Type 3 и применяют только там, где изменение заведомо одно. Type 3 — узкоспециальный инструмент: он не «слабее Type 2», он решает другую, более узкую задачу.

Иногда столбец «предыдущее» используют иначе — хранят в нём не «предыдущее», а зафиксированное «оригинальное» значение (original_region). Тогда можно всегда сравнить текущее состояние с самым первым. Это та же механика Type 3 — отдельный столбец под одно дополнительное значение, просто конвенция другая.

Разница между двумя конвенциями содержательна. Вариант «предыдущее значение» отвечает на вопрос «что было прямо перед текущим состоянием» — он скользит вместе с изменениями. Вариант «оригинальное значение» отвечает на вопрос «с чего всё начиналось» — он зафиксирован навсегда в момент создания строки. Какой из них выбрать, диктует бизнес-задача: для анализа «насколько изменилась территория по сравнению с прошлым названием» нужен «предыдущее»; для анализа «насколько мы отошли от первоначальной классификации» — «оригинальное». Иногда заводят оба столбца сразу. Главное — это всё равно Type 3: фиксированное, заранее заданное число дополнительных столбцов, а не растущая история.

Type 4 — проблема быстро меняющихся атрибутов

Перейдём к Type 4. Он решает иную задачу. Представьте dim_customer с двумя группами атрибутов:

• Стабильные: имя, дата рождения, город. Меняются редко.
• Быстро меняющиеся: возрастная группа, диапазон дохода, скоринговый сегмент, статус лояльности. Могут пересчитываться ежемесячно.

Если сделать dim_customer целиком Type 2, каждое ежемесячное изменение скорингового сегмента породит новую строку всей dimension — со всеми стабильными атрибутами в придачу. Миллион клиентов, ежемесячный пересчёт сегмента — это 12 миллионов новых строк в год на ровном месте, причём имя и дата рождения в них дублируются без всякой пользы. Type 2 здесь технически работает, но взрывает dimension.

Type 4 разрывает этот клубок: быстро меняющиеся атрибуты выносят из основной dimension в отдельную маленькую mini-dimension.

Здесь стоит увидеть аналогию с приёмами из модуля про dimension-таблицы. Mini-dimension Type 4 устроена так же, как junk dimension: это таблица, где строка — не сущность, а комбинация значений группы атрибутов. Junk dimension собирала разрозненные флаги, чтобы убрать россыпь FK из fact-таблицы; mini-dimension собирает быстро меняющиеся атрибуты, чтобы убрать их из-под историзации основной dimension. Механизм один — таблица комбинаций с собственным surrogate key, — но решаемые задачи разные. Понимание этой общности помогает: научившись проектировать junk dimension, вы уже почти умеете проектировать mini-dimension.

Type 4 через mini-dimension

Mini-dimension — это отдельная dimension, содержащая только быстро меняющиеся атрибуты, причём не по одной строке на клиента, а по одной строке на каждую встречающуюся комбинацию значений этих атрибутов.

-- Основная dimension: только стабильные атрибуты
CREATE TABLE dim_customer (
    customer_key  INT PRIMARY KEY,
    customer_id   INT,
    customer_name VARCHAR(120),
    date_of_birth DATE,
    city          VARCHAR(80)
);

-- Mini-dimension: только быстро меняющиеся атрибуты, строка на КОМБИНАЦИЮ
CREATE TABLE dim_customer_profile (
    profile_key   INT PRIMARY KEY,
    age_band      VARCHAR(12),   -- '25-34', '35-44', ...
    income_band   VARCHAR(16),   -- 'до 50т', '50-100т', ...
    loyalty_tier  VARCHAR(10)    -- 'Bronze', 'Silver', 'Gold'
);

Идея: возрастных групп — единицы, диапазонов дохода — единицы, уровней лояльности — три. Всех их комбинаций — десятки или сотни, не больше. dim_customer_profile — крошечная таблица фиксированного размера, не зависящая от числа клиентов.

Где же связь клиента с его профилем? Она хранится в fact-таблице. Fact-таблица получает два foreign key: customer_key (на стабильную dimension) и profile_key (на mini-dimension). Каждая fact-строка фиксирует, какой профиль был у клиента в момент события.

CREATE TABLE fct_sales (
    sale_key     BIGINT PRIMARY KEY,
    customer_key INT REFERENCES dim_customer(customer_key),
    profile_key  INT REFERENCES dim_customer_profile(profile_key),  -- профиль на момент продажи
    date_key     INT,
    sale_amount  NUMERIC(12,2)
);

Когда у клиента меняется скоринговый сегмент, основная dim_customer не трогается вообще. Просто следующая fct_sales-строка этого клиента получит profile_key, указывающий на другую (уже существующую в mini-dimension) комбинацию. История изменений профиля при этом естественно «записана» в самой fact-таблице: по последовательности profile_key в продажах клиента видно, как менялся его профиль.

Стоит оценить, насколько это экономно по сравнению с наивным Type 2. В Type 2 ежемесячное изменение профиля у миллиона клиентов добавляло бы миллион новых строк в dim_customer каждый месяц — со всеми стабильными атрибутами в каждой. В Type 4 при том же изменении в dim_customer не появляется ни одной новой строки, а dim_customer_profile остаётся таблицей фиксированного размера в десятки-сотни строк, потому что новых КОМБИНАЦИЙ значений почти не возникает — клиенты лишь переходят между уже существующими профилями. Рост, который в Type 2 был бы линейным по числу клиентов и месяцев, в Type 4 сведён к нулю на стороне dimensions. Дополнительная нагрузка ложится только на fact-таблицу — но она и так растёт с каждым событием, так что лишний столбец profile_key в ней погоды не делает.

Важно понять и то, как Type 4 отвечает на исторические вопросы. «Какой профиль был у клиента в момент январской покупки» — это просто profile_key январской fct_sales-строки. «Как менялся профиль клиента за год» — это последовательность различных profile_key в его продажах, упорядоченная по дате. История не хранится отдельной структурой — она восстанавливается из fact-таблицы, потому что каждая fact-строка зафиксировала профиль на момент своего события. Это элегантно, но имеет границу: если клиент в каком-то месяце ничего не покупал, его профиль за этот месяц в fact-таблице не отражён. Когда нужен профиль на любую дату независимо от активности клиента, переходят к Type 5 — он добавляет к mini-dimension прямую ссылку на текущий профиль, и это тема следующего урока.

Type 4 через отдельную history-таблицу

У Type 4 есть и вторая разновидность. Основную dimension ведут как Type 1 (текущие значения, перезапись), а каждое изменение дополнительно протоколируют в отдельную history-таблицу.

-- Основная dimension: текущее состояние, обновляется как Type 1
-- dim_customer (customer_key, customer_id, name, region, ...)

-- Отдельная history-таблица: журнал всех изменений
CREATE TABLE dim_customer_history (
    customer_id    INT,
    attribute_name VARCHAR(40),    -- какой атрибут изменился
    old_value      VARCHAR(200),
    new_value      VARCHAR(200),
    changed_at     TIMESTAMP
);

Текущие запросы идут к основной dimension и быстры — она компактна. Аудит и редкие исторические запросы идут к history-таблице. Разделение «горячих» текущих данных и «холодного» журнала истории — суть этого варианта Type 4.

Логика этого разделения — оптимизация под реальную нагрузку. Подавляющее большинство запросов к dimension спрашивают текущее состояние: какой сейчас регион, какой сейчас сегмент. Если основная dimension ведётся как Type 1 и хранит только текущее, она остаётся маленькой и быстрой — а это именно то, что нужно частым запросам. История изменений нужна редко — для аудита, расследований, разовых исторических отчётов — и для неё допустимо отдельное, более медленное хранилище. Type 4 через history-таблицу сознательно разводит два класса запросов по двум структурам, оптимизируя каждую под свой профиль использования. Это та же идея, что и mini-dimension, но применённая к разделению «текущее против истории», а не «стабильное против быстро меняющегося».

Попробуй сам

Часть 1 — Type 3. Дана dim_product с атрибутом category, который компания один раз масштабно пересмотрела при реструктуризации каталога.

1. Добавьте к dimension столбцы для Type 3: current_category, previous_category, category_changed_date.
2. Напишите UPDATE для смены категории. Что произойдёт, если категорию сменят второй раз?
3. Объясните, почему Type 3 подходит для разовой реструктуризации, но не подойдёт, если категория меняется ежегодно.

Часть 2 — Type 4. В dim_customer атрибуты loyalty_tier и income_band пересчитываются каждый месяц.

4. Спроектируйте mini-dimension dim_customer_profile. Прикиньте: сколько в ней строк, если loyalty_tier имеет 3 значения, income_band — 5, age_band — 6.
5. Покажите, какие два foreign key появятся в fct_sales и почему связь клиент-профиль хранится именно в fact-таблице.