Капстоун: SCD2 и date dimension

В прошлом уроке мы построили star schema проката: fact-таблица fct_rental и четыре dimensions. Но мы оставили два пробела. Первый: dimensions «застыли» — мы не подумали, что атрибуты клиента или станции меняются во времени. Второй: dim_date мы лишь упомянули. Этот урок закрывает оба — реализуем SCD Type 2 и детально построим date dimension.

Оба пробела — про время. SCD2 отвечает на вопрос «как сохранить историю изменений измерения». Date dimension — на вопрос «как правильно моделировать само время как измерение». Без них star schema из прошлого урока работает, но даёт неверные ответы на исторические вопросы.

Проблема: измерения меняются

Конкретный сценарий. Клиент Анна зарегистрировалась в Москве, в марте совершила 5 аренд. В апреле она переехала в Казань и обновила город в профиле. В мае — ещё 3 аренды.

Аналитик спрашивает: «сколько выручки принесли клиенты из Москвы». Если в dim_customer город просто перезаписан на «Казань» (это SCD Type 1 из модуля про SCD), то все аренды Анны — и мартовские московские, и майские казанские — посчитаются как «Казань». Мартовская выручка задним числом «переехала» в Казань. Отчёт стал неверным.

Корень проблемы: fact-строка должна быть связана с тем состоянием dimension, которое было актуально в момент события. Мартовская аренда — с «московской» Анной, майская — с «казанской». SCD Type 1 это состояние не хранит. Нужен SCD Type 2.

Решение: SCD Type 2

Из модуля про Slowly Changing Dimensions: SCD Type 2 при изменении атрибута вставляет новую строку с новым surrogate key, не трогая старую. Каждая версия сущности — отдельная строка. Для этого dimension получает служебные столбцы: effective date, end date, current flag.

Перепроектируем dim_customer под SCD2:

CREATE TABLE dim_customer (
    customer_key     INTEGER PRIMARY KEY,   -- surrogate key: УНИКАЛЕН НА ВЕРСИЮ
    customer_id      INTEGER,               -- durable key: один на сущность
    full_name        VARCHAR(150),
    city             VARCHAR(100),          -- меняющийся атрибут
    -- служебные столбцы SCD2:
    effective_date   DATE    NOT NULL,      -- с какого дня версия актуальна
    end_date         DATE    NOT NULL,      -- по какой день (9999-12-31 у текущей)
    is_current       BOOLEAN NOT NULL       -- флаг текущей версии
);

Ключевое различие двух идентификаторов (из модуля про ключи и SCD):

• customer_key — surrogate key, уникален на каждую версию. У московской Анны один key, у казанской — другой.
• customer_id — durable key, один на сущность на всю её жизнь. У обеих версий Анны он одинаков. Durable key позволяет собрать все версии одного клиента.

Посмотрим, что лежит в dim_customer после переезда Анны:

customer_key | customer_id | full_name | city   | effective_date | end_date   | is_current
501          | 1001        | Анна      | Москва | 2026-01-10     | 2026-04-15 | false
502          | 1001        | Анна      | Казань | 2026-04-15     | 9999-12-31 | true

Две строки на одного клиента. У старой версии end_date проставлен датой переезда и is_current = false. У новой версии effective_date = дата переезда, end_date = заглушка 9999-12-31, is_current = true. История сохранена полностью.

Как fact-таблица связывается с версией

Теперь главное — как fct_rental ссылается на правильную версию. Здесь и работает SCD2.

При загрузке аренды в fact-таблицу ETL ищет в dim_customer версию клиента, актуальную на дату аренды. Правило поиска: найти строку, где customer_id совпадает И дата аренды попадает в интервал [effective_date, end_date).

-- При загрузке fct_rental подбираем версию dimension на дату аренды
INSERT INTO fct_rental (customer_key, date_key, rental_amount, ...)
SELECT
    dc.customer_key,        -- key ИМЕННО ТОЙ версии, что действовала
    ...
FROM staging_rentals r
JOIN dim_customer dc
  ON dc.customer_id = r.customer_id
 AND r.rental_date >= dc.effective_date    -- аренда попадает
 AND r.rental_date <  dc.end_date          -- в интервал версии
...

Результат: мартовская аренда Анны получит customer_key = 501 (Москва), майская — customer_key = 502 (Казань). Каждая fact-строка навсегда «приклеена» к тому состоянию клиента, которое было в момент события. Теперь запрос «выручка клиентов из Москвы» через JOIN dim_customer WHERE city = 'Москва' посчитает только мартовские аренды — верно.

Date dimension: время как полноценное измерение

Второй пробел — dim_date. Возникает вопрос: зачем вообще отдельная таблица дат, если в fact-таблице уже есть started_at? Почему не фильтровать прямо по нему?

Потому что у даты для аналитики есть десятки атрибутов, которых нет в самом timestamp: день недели, номер месяца, название месяца, квартал, год, признак выходного, признак праздника, номер недели, фискальный период. Date dimension (из модуля про dimension-таблицы) — это таблица со строкой на каждый календарный день и всеми этими атрибутами заранее вычисленными.

CREATE TABLE dim_date (
    date_key        INTEGER PRIMARY KEY,   -- "умный" ключ YYYYMMDD: 20260520
    full_date       DATE    NOT NULL,
    day_of_week     VARCHAR(15),           -- 'среда'
    day_number      INTEGER,               -- 20
    month_number    INTEGER,               -- 5
    month_name      VARCHAR(15),           -- 'май'
    quarter         INTEGER,               -- 2
    year            INTEGER,               -- 2026
    is_weekend      BOOLEAN,               -- false
    is_holiday      BOOLEAN                -- false
);

-- Строка для 20 мая 2026:
-- 20260520 | 2026-05-20 | среда | 20 | 5 | май | 2 | 2026 | false | false

Две детали заслуживают внимания.

«Умный» ключ. date_key — это YYYYMMDD целым числом: 20 мая 2026 -> 20260520. Из модуля про ключи вы помните правило «surrogate key без смысла». Date dimension — известное исключение из этого правила: ключ-дата осмысленный, потому что даты не меняются и не появляются заново, и читаемый ключ удобен (по date_key сразу видно дату, можно сортировать и фильтровать диапазоном без JOIN).

Заполнение заранее. dim_date не наполняется по мере поступления данных — её генерируют наперёд, сразу на годы вперёд (например, 2020-2035). Это разовая операция: дни известны заранее, ждать их «прихода» незачем.

Зачем всё это? Date dimension превращает сложные календарные запросы в простые. «Выручка по будням против выходных» без dim_date — это вычисление дня недели из timestamp в каждом запросе. С dim_date — это JOIN dim_date ... GROUP BY is_weekend. «Выручка по кварталам», «сравнение год к году» — всё становится тривиальным GROUP BY по готовому атрибуту.

С SCD2 и date dimension star schema проката завершена и корректна: она хранит историю изменений измерений и даёт удобный аналитический доступ ко времени. В финальном уроке мы пройдём по всей модели с критическим взглядом — поищем анти-паттерны, обсудим документацию и code review, и научимся защищать проектное решение.

Попробуй сам

Продолжите кейс онлайн-библиотеки.

1. У читателя есть «категория» (студент, преподаватель, обычный), которая может меняться. Перепроектируйте dim_reader под SCD2: добавьте effective_date, end_date, is_current, разделите surrogate key и durable key.
2. Смоделируйте: читатель был «студент», стал «преподаватель». Покажите две строки в dim_reader после изменения.
3. Напишите условие JOIN, по которому fact-таблица выдач подберёт версию читателя, актуальную на дату выдачи.
4. Спроектируйте dim_date для библиотеки. Какой «умный» ключ возьмёте? Какие атрибуты будут полезны для отчётов «выдачи по месяцам» и «просрочки по дням недели»?
5. Решите, какие атрибуты читателя вести по SCD2, а какие — по Type 1. Обоснуйте.