Type 2 (add row): полная история изменений

Type 0 и Type 1 не хранят историю изменений, и для многих атрибутов это правильно. Но для атрибутов вроде региона клиента, должности сотрудника или категории товара история критична: без неё исторические отчёты врут, а воспроизвести прошлый срез невозможно. Нужен SCD-тип, который запоминает каждую версию атрибута и навсегда связывает каждую fact-строку с тем значением, которое действовало в момент события.

Это SCD Type 2 — самый важный и самый широко применяемый SCD-тип на практике. Его идея: на каждое изменение атрибута в dimension вставляется новая строка, а старая не трогается. Одна сущность представлена несколькими строками — по одной на каждый период своей «жизни» с определённым набором атрибутов. Type 2 — это рабочая лошадь историзации: когда говорят «нам нужна история изменений этого атрибута», по умолчанию подразумевают именно Type 2.

Surrogate key против durable key

Чтобы понять Type 2, надо чётко развести два ключа.

Durable key (он же natural/business key) — стабильный идентификатор самой сущности. У клиента Анны это, скажем, customer_id = 7012. Он один на Анну навсегда, сколько бы раз она ни переезжала.

Surrogate key — идентификатор конкретной версии строки. В Type 2 он уникален не на сущность, а на временной срез. У Анны до переезда surrogate key один, после переезда — другой, хотя durable key всё тот же.

Это и есть сердце Type 2: одна сущность (один durable key) — несколько версий (несколько surrogate keys). Fact-таблица ссылается на dimension через surrogate key — то есть на конкретную версию. Поэтому январская покупка Анны навсегда привязана к той версии Анны, что действовала в январе.

Почему вообще нужны два отдельных ключа, почему нельзя обойтись одним? Потому что у них разные задачи. Surrogate key должен указывать на одну конкретную строку — а строк-версий у Анны несколько, и каждая нуждается в собственном уникальном идентификаторе, иначе fact-таблица не сможет сослаться именно на нужную версию. Durable key должен отвечать на вопрос «это всё одна и та же Анна?» — он связывает все версии одной сущности воедино. Один ключ обе роли совместить не может: если бы ключ был один и уникальный на строку, потерялась бы связь между версиями (нельзя было бы понять, что три строки — это одна Анна); если бы он был один и общий для всех версий, fact-таблица не смогла бы указать на конкретную версию. Type 2 принципиально требует двух ключей с разной зоной уникальности.

CREATE TABLE dim_customer (
    customer_key   INT PRIMARY KEY,   -- surrogate key: уникален на ВЕРСИЮ
    customer_id    INT NOT NULL,      -- durable key: уникален на СУЩНОСТЬ
    customer_name  VARCHAR(120),
    city           VARCHAR(80),
    region         VARCHAR(40),       -- Type 2-атрибут: изменения создают новую строку
    effective_date DATE NOT NULL,     -- с какой даты версия действует
    end_date       DATE NOT NULL,     -- по какую дату; у текущей = 9999-12-31
    is_current     BOOLEAN NOT NULL,  -- true ровно у одной версии сущности
    version        INT NOT NULL       -- порядковый номер версии: 1, 2, 3...
);

Четыре служебных столбца Type 2

Кроме surrogate и durable ключей, Type 2-строка несёт четыре служебных столбца. Каждый отвечает на свой вопрос.

• effective_date — с какой даты эта версия начала действовать.
• end_date — по какую дату версия действовала. У текущей (ещё не закрытой) версии в end_date ставят «бесконечность» — практически дату 9999-12-31 (или NULL). Дата 9999-12-31 удобнее NULL: с ней работают обычные сравнения дат без специальной обработки.
• is_current — булев флаг: true ровно у одной версии каждой сущности — у самой свежей. Нужен для быстрого запроса «дай текущее состояние» без сравнения дат.
• version — порядковый номер: 1 у первой версии, 2 у второй и так далее. Удобен для человека и для запросов «предыдущая версия».

Главный инвариант: для любой даты D и любой сущности существует ровно одна версия, у которой effective_date <= D <= end_date. Периоды версий одной сущности не пересекаются и не имеют разрывов. effective_date новой версии равен дню, следующему за end_date предыдущей (или тому же дню — конвенция выбирается командой, но должна быть единой).

После переезда Анны в dimension две строки:

 customer_key | customer_id | region | effective_date | end_date   | is_current | version
--------------+-------------+--------+----------------+------------+------------+--------
 88001        | 7012        | Центр  | 2020-01-01     | 2026-02-28 | false      | 1
 88002        | 7012        | Сибирь | 2026-03-01     | 9999-12-31 | true       | 2

Один customer_id = 7012, два customer_key (88001 и 88002) — две версии. Январская fct_sales-строка имеет customer_key = 88001, апрельская — customer_key = 88002. История зафиксирована навсегда.

Обратите внимание, как именно происходит привязка fact-строки к версии. В момент загрузки январской продажи ETL-процесс находит версию Анны, действовавшую на дату продажи, и берёт её surrogate key — 88001. Этот ключ записывается в fct_sales и больше никогда не меняется. Когда в марте Анна переедет и появится версия 88002, январская строка fct_sales продолжит ссылаться на 88001 — её ничто не трогает. В этом и состоит «навсегда»: связь fact-строки с версией dimension фиксируется в момент загрузки факта и далее неизменна. Именно поэтому исторический отчёт воспроизводится стабильно — fact-строки помнят, к какой версии каждой сущности они относятся.

Загрузка SCD2 через MERGE

Когда приходит изменение атрибута, ETL должен сделать две операции атомарно: закрыть текущую версию (проставить ей end_date и is_current = false) и вставить новую версию. Стандартный инструмент для этого — оператор MERGE.

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

Шаг 1 — закрыть версии, у которых атрибут изменился:

-- Закрываем текущую версию, если в источнике регион стал другим
UPDATE dim_customer d
SET end_date   = :load_date - INTERVAL '1 day',
    is_current = false
FROM staging_customer s
WHERE d.customer_id = s.customer_id   -- та же сущность
  AND d.is_current  = true            -- закрываем только текущую версию
  AND d.region <> s.region;           -- и только если Type 2-атрибут изменился

Шаг 2 — вставить новые версии для тех же изменившихся сущностей:

-- Вставляем новую версию: новый surrogate key, version+1, is_current=true
INSERT INTO dim_customer
    (customer_key, customer_id, customer_name, city, region,
     effective_date, end_date, is_current, version)
SELECT
    nextval('dim_customer_seq'),       -- новый surrogate key
    s.customer_id,                     -- durable key тот же
    s.customer_name, s.city, s.region,
    :load_date,                        -- новая версия действует с даты загрузки
    DATE '9999-12-31',                 -- end_date = "бесконечность"
    true,                              -- это теперь текущая версия
    d_prev.version + 1                 -- номер версии на единицу больше
FROM staging_customer s
JOIN dim_customer d_prev
      ON d_prev.customer_id = s.customer_id
WHERE d_prev.is_current = false                 -- только что закрытая нами версия
  AND d_prev.end_date = :load_date - INTERVAL '1 day'
  AND d_prev.region <> s.region;

Логика MERGE такова: если у сущности Type 2-атрибут совпадает с источником — ничего не делаем. Если изменился — закрываем старую версию и открываем новую. Если сущность совсем новая — просто вставляем версию 1. Многие СУБД позволяют записать это одним оператором MERGE ... WHEN MATCHED ... WHEN NOT MATCHED, но логика всегда та же: закрыть-и-вставить.

Почему «закрыть» и «вставить» — это две операции, а не одна? Потому что переход между версиями обязан сохранить инвариант непрерывности: на любую дату — ровно одна действующая версия. Если бы мы только вставили новую строку, не закрыв старую, у Анны на даты после переезда оказались бы ДВЕ действующие версии (старая всё ещё с end_date 9999-12-31), и запрос «версия на дату» вернул бы две строки вместо одной — а JOIN с fact-таблицей задвоил бы measures. Если бы только закрыли старую, не вставив новую, на даты после переезда не осталось бы НИ ОДНОЙ действующей версии. Корректен лишь обе операции вместе и атомарно: старая закрывается ровно тем днём, новая открывается ровно следующим, без зазора и без нахлёста. Поэтому загрузку SCD2 выполняют в транзакции — чтобы между «закрыть» и «вставить» никто не увидел dimension в промежуточном, нарушенном состоянии.

Результат после загрузки изменения "Анна переехала в Сибирь":
 customer_key | customer_id | region | effective_date | end_date   | is_current
--------------+-------------+--------+----------------+------------+-----------
 88001        | 7012        | Центр  | 2020-01-01     | 2026-02-28 | false
 88002        | 7012        | Сибирь | 2026-03-01     | 9999-12-31 | true

Запросы к SCD2

Type 2-dimension отвечает на оба вопроса из первого урока модуля.

Вопрос as-is («текущее состояние»): фильтруем по is_current = true.

-- Текущий регион каждого клиента
SELECT customer_id, region
FROM dim_customer
WHERE is_current = true;

Вопрос as-was («состояние на дату D»): фильтруем по диапазону дат.

-- Какой регион был у клиентов на 2026-01-15
SELECT customer_id, region
FROM dim_customer
WHERE DATE '2026-01-15' BETWEEN effective_date AND end_date;

 customer_id | region
-------------+--------
 7012        | Центр       <- на 15 января Анна была в "Центре" — корректно

Обратите внимание, что эти два запроса используют разные механизмы Type 2. As-is опирается на флаг is_current — это быстрый путь: один булев столбец, по которому можно построить индекс, и запрос «текущее состояние» не сравнивает даты вообще. As-was опирается на диапазон effective_date .. end_date — это путь для произвольной исторической даты. Type 2 хранит оба механизма одновременно именно потому, что оба вопроса нужны бизнесу: is_current обслуживает частый запрос «как сейчас», диапазон дат — запрос «как было на конкретный день». Можно было бы обойтись только датами (текущая версия — та, у которой end_date = 9999-12-31), но отдельный флаг is_current делает самый частый запрос проще и быстрее, поэтому его и держат.

А исторический отчёт «продажи по регионам за январь» теперь верен автоматически — потому что fct_sales-строки января ссылаются на customer_key = 88001 (версию «Центр»), и обычный JOIN даёт правильный регион:

SELECT c.region, SUM(s.sale_amount) AS revenue
FROM fct_sales s
JOIN dim_customer c ON c.customer_key = s.customer_key  -- JOIN по surrogate key!
WHERE s.date_key BETWEEN 20260101 AND 20260131
GROUP BY c.region;

 region | revenue
--------+--------
 Центр  | 50000.0    <- верно: в январе Анна была в "Центре"

Попробуй сам

Дана dim_employee с Type 2-атрибутом department и служебными столбцами effective_date, end_date, is_current, version.

1. Сотрудник employee_id = 500 пришёл 2024-01-01 в отдел «Продажи». 2025-06-01 перешёл в «Маркетинг». Выпишите две строки dimension со всеми служебными столбцами и разными surrogate key.
2. Напишите UPDATE, закрывающий первую версию, и INSERT, создающий вторую (как в примере с MERGE).
3. Напишите запрос «в каком отделе был сотрудник 500 на 2025-03-10» и «в каком он сейчас».
4. Сотрудник сменил рабочий email (это Type 1-атрибут). Нужно ли создавать новую Type 2-версию? Объясните, почему нет.