Type 5, 6, 7 и bi-temporal моделирование

Type 0-4 — базовые техники. Но у каждой есть ограничение. Type 2 хранит историю, но «текущее значение версии-профиля» не получить без JOIN через fact-таблицу. Type 1 даёт текущее значение, но теряет историю. Бизнесу часто нужно и то, и другое сразу — поэтому Kimball Group определила гибридные типы 5, 6 и 7. Каждый — это конкретная комбинация уже известных вам базовых типов. В конце урока разберём bi-temporal моделирование — взгляд на время с двух осей сразу.

Type 5 — Type 4 mini-dimension плюс Type 1 outrigger

SCD Type 5 по определению Kimball Group — это Type 4 + Type 1, отсюда и номер 5.

Вспомните Type 4 с mini-dimension: быстро меняющиеся атрибуты вынесены в dim_customer_profile, а связь клиента с профилем хранится в fact-таблице. У этого решения есть неудобство: чтобы узнать ТЕКУЩИЙ профиль клиента, нужно идти через fact-таблицу — найти его самую свежую продажу и взять оттуда profile_key. Если клиент давно ничего не покупал или нужен просто список клиентов с их текущими профилями без обращения к продажам — это громоздко.

Type 5 устраняет это. К базовой dimension добавляют столбец-ссылку на текущий профиль mini-dimension, и этот столбец ведут как Type 1 — то есть при смене профиля его перезаписывают.

-- Базовая dimension с Type 1-ссылкой на текущий профиль (outrigger)
CREATE TABLE dim_customer (
    customer_key        INT PRIMARY KEY,
    customer_id         INT,
    customer_name       VARCHAR(120),
    city                VARCHAR(80),
    current_profile_key INT REFERENCES dim_customer_profile(profile_key)
        -- Type 1: при смене профиля просто перезаписывается
);

current_profile_key — это Type 1 outrigger: dimension ссылается на mini-dimension, и ссылка обновляется перезаписью. Теперь текущий профиль клиента читается одним JOIN базовой dimension с mini-dimension, без всякой fact-таблицы:

-- Текущий профиль каждого клиента — без обращения к fct_sales
SELECT c.customer_name, p.loyalty_tier, p.income_band
FROM dim_customer c
JOIN dim_customer_profile p ON p.profile_key = c.current_profile_key;

При этом исторические профили никуда не делись — fact-таблица по-прежнему хранит profile_key, действовавший в момент каждой продажи. Type 5 даёт обе возможности: текущий профиль через outrigger (быстро, без fact), исторический — через fact-таблицу.

Type 6 — комбинация 1 + 2 + 3

SCD Type 6 по определению Kimball Group — это Type 1 + Type 2 + Type 3 в одной строке, и 6 = 1 + 2 + 3.

Берётся основа Type 2: на каждое изменение — новая строка с surrogate key, effective/end date, current flag. Но в каждую строку добавляют ещё два особых столбца:

• столбец «current value» (механика Type 1) — содержит АКТУАЛЬНОЕ значение атрибута и перезаписывается во ВСЕХ строках сущности при каждом изменении;
• столбец «historical/previous value» (механика Type 3) — содержит значение, действовавшее в период именно этой строки.

Разберём на Анне. Регион меняется с «Центр» (IL в оригинальной нотации Kimball) на «Сибирь» (NY). В каждой строке два столбца региона: region_historical (значение этого периода) и region_current (текущее значение клиента).

Шаг 1 — пока Анна в «Центре», одна строка:

 customer_key | customer_id | region_historical | region_current | effective | end_date   | is_current
--------------+-------------+-------------------+----------------+-----------+------------+-----------
 88001        | 7012        | Центр             | Центр          | 2020-01-01| 9999-12-31 | true

Шаг 2 — Анна переехала в «Сибирь». Type 2 закрывает старую строку и добавляет новую. Type 1-механика обновляет region_current в ОБЕИХ строках на «Сибирь». region_historical в каждой строке остаётся своим:

 customer_key | customer_id | region_historical | region_current | effective | end_date   | is_current
--------------+-------------+-------------------+----------------+-----------+------------+-----------
 88001        | 7012        | Центр             | Сибирь         | 2020-01-01| 2026-02-28 | false
 88002        | 7012        | Сибирь            | Сибирь         | 2026-03-01| 9999-12-31 | true

Посмотрите на строку 88001: region_historical = Центр (так было в её период), region_current = Сибирь (так у Анны сейчас). Одна строка несёт оба ответа.

Type 6 отвечает на три вопроса одним обращением к dimension:

• as-was — JOIN fct_sales по surrogate key, берём region_historical: какой регион был в момент события.
• as-is — берём region_current из любой строки клиента: какой регион сейчас.
• as-of point-in-time — фильтр по диапазону effective/end date: какой регион был на произвольную дату.

-- as-was: продажи по региону, действовавшему в момент покупки
SELECT c.region_historical, SUM(s.sale_amount)
FROM fct_sales s
JOIN dim_customer c ON c.customer_key = s.customer_key
GROUP BY c.region_historical;

-- as-is: те же продажи, но сгруппированные по ТЕКУЩЕМУ региону клиента
SELECT c.region_current, SUM(s.sale_amount)
FROM fct_sales s
JOIN dim_customer c ON c.customer_key = s.customer_key
GROUP BY c.region_current;

Type 7 — двойной ключ в fact-таблице

SCD Type 7 по определению Kimball Group: dimension ведётся как обычный Type 2, а fact-таблица хранит два ключа — surrogate key версии И durable (natural) key сущности.

В Type 2 fact-таблица хранила только surrogate key. Type 7 добавляет рядом durable key:

CREATE TABLE fct_sales (
    sale_key      BIGINT PRIMARY KEY,
    customer_key  INT,   -- surrogate key версии: для запросов as-was
    customer_id   INT,   -- durable key сущности: для запросов as-is
    date_key      INT,
    sale_amount   NUMERIC(12,2)
);

Два ключа дают два режима анализа без изменения dimension:

• через surrogate key (customer_key) — JOIN с конкретной версией: ответ as-was (значение на момент события);
• через durable key (customer_id) — JOIN с текущей версией (где is_current = true): ответ as-is (текущее значение).

-- as-is через durable key: продажи по ТЕКУЩЕМУ региону клиентов
SELECT c.region, SUM(s.sale_amount)
FROM fct_sales s
JOIN dim_customer c
      ON c.customer_id = s.customer_id   -- JOIN по durable key
     AND c.is_current  = true            -- к текущей версии
GROUP BY c.region;

У Type 7 есть осознаваемая цена. JOIN по durable key обязан включать условие is_current = true — иначе fact-строка соединится со ВСЕМИ версиями клиента сразу, и measures задвоятся. Декларативный foreign-key constraint по durable key объявить нельзя: durable key не уникален в dimension (версий несколько), а FK должен ссылаться на уникальный ключ. Поэтому корректность JOIN по durable key держится на дисциплине разработчика, а не на ограничении СУБД. Это плата за гибкость двойного ключа.

Сводка гибридных типов:

Bi-temporal моделирование: две оси времени

Все SCD-типы выше отвечают на вопрос «когда атрибут был таким в реальном мире». Но у времени в данных есть вторая ось, и серьёзные системы (банки, страхование, регуляторная отчётность) учитывают обе. Это bi-temporal modeling — моделирование по двум временным осям одновременно.

Две оси:

• Valid time (он же effective time, application time) — когда факт был истинным в реальном мире. Анна реально жила в Сибири с 1 марта — это valid time.
• Transaction time (он же system time, knowledge time) — когда база данных узнала этот факт, когда он был в неё записан. Анна переехала 1 марта, но в систему её новый адрес внесли только 10 марта (бумаги дошли с задержкой) — 10 марта это transaction time.

Эти оси различаются, потому что реальность и её отражение в БД не синхронны. Между событием в мире и его записью в систему всегда есть лаг — данные приходят поздно, с ошибками, задним числом.

Зачем нужны обе оси

Однооосная модель (обычный SCD2 с effective/end date) знает только valid time — она ответит «какой регион был у Анны 5 марта». Но она не ответит на вопрос другого рода: «что наша система ЗНАЛА о регионе Анны 5 марта». А 5 марта система ещё считала, что Анна в «Центре» — новый адрес внесли только 10-го.

Разница критична для аудита и регуляторной отчётности. Если в марте на основе данных системы был сформирован отчёт, он использовал «Центр» — потому что на тот момент система знала именно это. Чтобы воспроизвести этот отчёт точь-в-точь (например, по запросу регулятора), нужно уметь спросить базу: «покажи состояние данных, каким оно было известно на 5 марта». Это запрос по transaction time.

Bi-temporal таблица хранит два периода в каждой строке — пару valid-дат и пару transaction-дат:

CREATE TABLE dim_customer_bitemporal (
    customer_id    INT,
    region         VARCHAR(40),
    valid_from     DATE,       -- valid time: с какого момента факт истинен в мире
    valid_to       DATE,       -- valid time: по какой момент
    tx_from        TIMESTAMP,  -- transaction time: когда строка внесена в БД
    tx_to          TIMESTAMP   -- transaction time: когда строка перестала быть актуальной записью
);

Это позволяет задавать вопросы по двум осям независимо:

• «Какой регион был у Анны 5 марта?» — фильтр по valid time. Ответ: Сибирь (она уже переехала).
• «Что система знала о регионе Анны по состоянию на 5 марта?» — фильтр по transaction time. Ответ: Центр (адрес ещё не внесли).
• «Что система знает СЕЙЧАС о том, где Анна была 5 марта?» — фильтр по обеим осям. Ответ: Сибирь.

Попробуй сам

Часть 1 — гибридные типы. Для dim_customer с меняющимся атрибутом segment:

1. Опишите словами, что добавит Type 6: какие два столбца сегмента появятся и какой из них перезаписывается во всех строках при изменении.
2. Для Type 7 выпишите, какие два ключа будут в fct_sales, и какой из них даёт as-was, а какой as-is.
3. Напишите as-is запрос для Type 7 и объясните, почему в JOIN обязателен is_current = true.

Часть 2 — bi-temporal. Событие: клиент сменил тариф 2026-04-01, но в систему это внесли 2026-04-12.

4. Выпишите valid_from и tx_from для новой строки.
5. Сформулируйте два разных вопроса: один по valid time, другой по transaction time — и дайте на них разные ответы для даты 2026-04-05.