SCD

SCD — медленно меняющиеся измерения¶

В звёздной схеме измерения содержат описательные атрибуты: имя клиента, город, сегмент. Но что делать, когда клиент переехал из Москвы в Казань? Перезаписать? Сохранить обе версии? Хранить оба значения в одной строке?

Для ответа на этот вопрос Ральф Кимбалл ввёл классификацию SCD (Slowly Changing Dimensions) — типы от 0 до 3 (и выше). Каждый тип — свой способ реагирования на изменение атрибута.

Почему это важно¶

Представь отчёт «Выручка по городам». Если клиент переехал и мы просто обновили город, то вся его прошлая выручка «переедет» в новый город. Исторический отчёт станет некорректным.

Кулинарная аналогия

Ты записываешь в журнал, какие продукты от какого поставщика получил. Поставщик сменил название. Перезаписать? Тогда непонятно, от кого пришла прошлая партия. Сохранить обе записи? Тогда нужно уметь различать старое и новое название.

SCD решает именно эту задачу — как хранить историю изменений в измерениях.

SCD Type 0 — не меняем¶

Значение фиксируется при первой загрузке и никогда не обновляется.

Применяется для атрибутов, которые не должны меняться: дата рождения, исходный источник регистрации.

SQL

-- Type 0: дата регистрации фиксирована навсегда
CREATE TABLE dim_customer_type0 (
    customer_key   SERIAL PRIMARY KEY,
    customer_id    INT NOT NULL,
    name           VARCHAR(100),
    registered_at  DATE NOT NULL  -- никогда не обновляется
);

SCD Type 1 — перезаписываем¶

Старое значение затирается новым. Истории нет.

SQL

-- Type 1: просто UPDATE
UPDATE dim_customer
SET city = 'Казань',
    segment = 'VIP'
WHERE customer_id = 1;

Когда применять:

Исправление ошибок (опечатка в имени)
Атрибуты, по которым не строят исторические отчёты
Когда история не нужна

Ограничение

После Type 1 UPDATE невозможно узнать, какое значение было раньше. Все исторические отчёты покажут новое значение.

SCD Type 2 — полная история¶

Самый распространённый тип. При изменении атрибута создаётся новая строка в измерении, старая помечается как неактивная.

Структура таблицы¶

SQL

CREATE TABLE dim_customer_type2 (
    customer_key    SERIAL PRIMARY KEY,      -- суррогатный ключ (уникален для каждой версии)
    customer_id     INT NOT NULL,            -- натуральный ключ (одинаков для всех версий)
    name            VARCHAR(100),
    email           VARCHAR(200),
    city            VARCHAR(100),
    segment         VARCHAR(50),
    -- SCD Type 2 метаполя
    effective_from  DATE NOT NULL,           -- начало действия версии
    effective_to    DATE,                    -- конец действия (NULL = актуальная)
    is_current      BOOLEAN NOT NULL DEFAULT TRUE
);

Начальная загрузка¶

SQL

INSERT INTO dim_customer_type2
    (customer_id, name, email, city, segment, effective_from, is_current)
VALUES
    (1, 'Анна', 'anna@example.com', 'Москва', 'Regular', '2026-01-10', TRUE),
    (2, 'Борис', 'boris@example.com', 'Новосибирск', 'New', '2026-01-11', TRUE);

Обработка изменения¶

Анна переехала из Москвы в Казань 1 марта 2026. Нужно два шага:

SQL

-- Шаг 1: закрыть текущую версию
UPDATE dim_customer_type2
SET effective_to = '2026-02-28',
    is_current   = FALSE
WHERE customer_id = 1
  AND is_current = TRUE;

-- Шаг 2: вставить новую версию
INSERT INTO dim_customer_type2
    (customer_id, name, email, city, segment, effective_from, is_current)
VALUES
    (1, 'Анна', 'anna@example.com', 'Казань', 'Regular', '2026-03-01', TRUE);

Результат¶

customer_key	customer_id	name	city	effective_from	effective_to	is_current
1	1	Анна	Москва	2026-01-10	2026-02-28	FALSE
3	1	Анна	Казань	2026-03-01	NULL	TRUE
2	2	Борис	Новосибирск	2026-01-11	NULL	TRUE

Теперь customer_key = 1 и customer_key = 3 — два разных суррогатных ключа для одного клиента. Факты, привязанные к customer_key = 1, покажут «Москва», а факты после 1 марта — «Казань».

Запросы к SCD Type 2¶

SQL

-- Актуальные данные
SELECT * FROM dim_customer_type2 WHERE is_current = TRUE;

-- Данные на конкретную дату
SELECT *
FROM dim_customer_type2
WHERE customer_id = 1
  AND effective_from <= '2026-02-15'
  AND (effective_to IS NULL OR effective_to >= '2026-02-15');

-- Все версии одного клиента
SELECT *
FROM dim_customer_type2
WHERE customer_id = 1
ORDER BY effective_from;

Суррогатный ключ — обязательно

В SCD Type 2 суррогатный ключ критически важен: он связывает конкретную версию измерения с фактом. Натуральный ключ (customer_id) один для всех версий, поэтому его нельзя использовать в FK таблицы фактов.

SCD Type 3 — предыдущее значение¶

Хранит только одну предыдущую версию атрибута в дополнительной колонке.

SQL

CREATE TABLE dim_customer_type3 (
    customer_key    SERIAL PRIMARY KEY,
    customer_id     INT NOT NULL,
    name            VARCHAR(100),
    city            VARCHAR(100),         -- текущий город
    previous_city   VARCHAR(100),         -- предыдущий город
    city_changed_at DATE,                 -- когда сменился
    segment         VARCHAR(50)
);

Обработка изменения¶

SQL

UPDATE dim_customer_type3
SET previous_city   = city,
    city            = 'Казань',
    city_changed_at = '2026-03-01'
WHERE customer_id = 1;

customer_key	name	city	previous_city	city_changed_at
1	Анна	Казань	Москва	2026-03-01

Ограничение

Type 3 хранит только одну предыдущую версию. Если Анна переедет ещё раз, информация о Москве потеряется. Подходит только для атрибутов с редкими изменениями, где нужен доступ к «было/стало».

Сравнение типов SCD¶

Тип	Что происходит	История	Сложность	Когда применять
Type 0	Ничего	Нет	Минимальная	Неизменяемые атрибуты
Type 1	Перезапись	Нет	Низкая	Исправление ошибок, неважные атрибуты
Type 2	Новая строка	Полная	Высокая	Ключевые бизнес-атрибуты
Type 3	Доп. колонка	Одна версия	Средняя	«Было/стало» для одного атрибута

Комбинирование типов¶

В реальных проектах разные атрибуты одного измерения обрабатываются разными типами SCD:

SQL

CREATE TABLE dim_customer_hybrid (
    customer_key    SERIAL PRIMARY KEY,
    customer_id     INT NOT NULL,           -- Type 0: не меняется
    name            VARCHAR(100),           -- Type 1: перезаписывается
    city            VARCHAR(100),           -- Type 2: полная история
    previous_city   VARCHAR(100),           -- Type 3: предыдущее значение
    segment         VARCHAR(50),            -- Type 2: полная история
    registered_at   DATE,                   -- Type 0: не меняется
    effective_from  DATE NOT NULL,
    effective_to    DATE,
    is_current      BOOLEAN DEFAULT TRUE
);

На практике

Большинство проектов используют Type 1 для некритичных атрибутов и Type 2 для ключевых. Type 0 и Type 3 встречаются реже. Выбор типа — бизнес-решение: какие атрибуты нужно отслеживать исторически, а какие нет.

SCD Type 2 с помощью MERGE (PostgreSQL 15+)¶

Начиная с PostgreSQL 15 можно использовать MERGE для атомарной обработки SCD Type 2:

SQL

-- Подготовим staging-таблицу с новыми данными
CREATE TEMP TABLE stg_customers AS
SELECT 1 AS customer_id, 'Анна' AS name, 'anna@example.com' AS email,
       'Казань' AS city, 'VIP' AS segment;

-- MERGE для SCD Type 2
-- Шаг 1: закрыть устаревшие версии
UPDATE dim_customer_type2 d
SET effective_to = CURRENT_DATE - 1,
    is_current   = FALSE
FROM stg_customers s
WHERE d.customer_id = s.customer_id
  AND d.is_current = TRUE
  AND (d.city != s.city OR d.segment != s.segment);

-- Шаг 2: вставить новые версии
INSERT INTO dim_customer_type2
    (customer_id, name, email, city, segment, effective_from, is_current)
SELECT
    s.customer_id, s.name, s.email, s.city, s.segment,
    CURRENT_DATE, TRUE
FROM stg_customers s
WHERE NOT EXISTS (
    SELECT 1 FROM dim_customer_type2 d
    WHERE d.customer_id = s.customer_id
      AND d.is_current = TRUE
      AND d.city = s.city
      AND d.segment = s.segment
);

Что запомнить¶

SCD — способ обработки изменений в таблицах измерений
Type 0 — не меняем, Type 1 — перезаписываем, Type 2 — новая строка, Type 3 — доп. колонка
Type 2 — самый мощный: полная история, но нужен суррогатный ключ и метаполя (effective_from/to, is_current)
В одном измерении разные атрибуты могут использовать разные типы SCD
Выбор типа SCD — бизнес-решение, а не техническое
В Data Vault историзация встроена через сателлиты — это аналог SCD Type 2 «из коробки»