Медленно меняющиеся измерения (SCD)

Dimension-таблицы — справочники, которые обогащают факты: клиенты, продукты, локации, тарифные планы. В OLTP-системах dimension обновляется на месте (UPDATE). В ClickHouse UPDATE — мутация: тяжёлая операция, переписывающая parts. Для dimension, которые меняются редко, но предсказуемо, существует паттерн Slowly Changing Dimensions (SCD).

В Уроке 2 (Модуль 03) мы разобрали ReplacingMergeTree: дедупликация при merge, а не при INSERT. Здесь мы применяем ReplacingMergeTree как инструмент для реализации SCD в ClickHouse.

SCD Type 1: перезапись текущего значения

SCD Type 1 — самый простой подход: храним только актуальную версию dimension-записи. Историю не сохраняем.

В ClickHouse SCD Type 1 реализуется через ReplacingMergeTree с версионным столбцом:

CREATE TABLE dim_customers (
    customer_id UInt64,
    name String,
    email String,
    plan LowCardinality(String),
    region LowCardinality(String),
    updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY customer_id;

Каждое изменение dimension — новый INSERT с увеличенным updated_at:

-- Начальная версия
INSERT INTO dim_customers VALUES
    (42, 'Alice', '[email protected]', 'free', 'EU', '2024-01-01 00:00:00');

-- Клиент сменил email и план
INSERT INTO dim_customers VALUES
    (42, 'Alice', '[email protected]', 'pro', 'EU', '2024-03-15 10:30:00');

Чтение актуальной версии — SELECT с FINAL:

SELECT customer_id, name, email, plan
FROM dim_customers FINAL
WHERE customer_id = 42;
-- Результат: (42, 'Alice', '[email protected]', 'pro')

Почему версионный столбец обязателен

Без версионного столбца (ENGINE = ReplacingMergeTree()) при merge остаётся последняя по порядку вставки строка. Этот порядок недетерминирован при параллельных INSERT. С версионным столбцом дедупликация всегда предсказуема: побеждает строка с максимальным значением updated_at.

is_deleted: soft-delete в SCD

Начиная с ClickHouse 24.x, ReplacingMergeTree поддерживает параметр is_deleted — столбец UInt8, который сигнализирует об удалении записи:

CREATE TABLE dim_products (
    product_id UInt64,
    name String,
    category LowCardinality(String),
    price Decimal64(2),
    updated_at DateTime,
    is_deleted UInt8
) ENGINE = ReplacingMergeTree(updated_at, is_deleted)
ORDER BY product_id;

Для “удаления” записи вставляем строку с is_deleted=1 и максимальным updated_at:

-- Создание продукта
INSERT INTO dim_products VALUES
    (100, 'Widget', 'hardware', 29.99, '2024-01-01 00:00:00', 0);

-- Снятие продукта с продажи (soft delete)
INSERT INTO dim_products VALUES
    (100, 'Widget', 'hardware', 29.99, '2024-06-01 00:00:00', 1);

При merge строка с is_deleted=1 и максимальным updated_at удаляет все версии этого ORDER BY ключа. После merge записи с product_id=100 физически не существует.

SCD Type 2: полная история изменений

SCD Type 2 сохраняет все версии dimension-записи с диапазонами действия (valid_from / valid_to). Это нужно, когда аналитика требует ответа на вопрос “каким было значение атрибута на дату X?”.

В ClickHouse SCD Type 2 реализуется отдельной таблицей (не ReplacingMergeTree, а обычным MergeTree):

CREATE TABLE dim_customers_history (
    customer_id UInt64,
    name String,
    email String,
    plan LowCardinality(String),
    valid_from DateTime,
    valid_to DateTime DEFAULT toDateTime('9999-12-31 23:59:59')
) ENGINE = MergeTree()
ORDER BY (customer_id, valid_from);

Каждое изменение — два действия:

1. Вставить новую версию с valid_from = now(), valid_to = 9999-12-31
2. Закрыть предыдущую версию (через INSERT с тем же ключом и обновлённым valid_to)

-- Начальная версия
INSERT INTO dim_customers_history VALUES
    (42, 'Alice', '[email protected]', 'free', '2024-01-01 00:00:00', '9999-12-31 23:59:59');

-- Смена плана: закрыть старую версию, создать новую
INSERT INTO dim_customers_history VALUES
    (42, 'Alice', '[email protected]', 'free', '2024-01-01 00:00:00', '2024-03-14 23:59:59');
INSERT INTO dim_customers_history VALUES
    (42, 'Alice', '[email protected]', 'pro', '2024-03-15 00:00:00', '9999-12-31 23:59:59');

Запрос “какой план был у клиента на 15 февраля”:

SELECT customer_id, plan
FROM dim_customers_history
WHERE customer_id = 42
  AND valid_from <= '2024-02-15 00:00:00'
  AND valid_to >= '2024-02-15 00:00:00';
-- Результат: plan='free'

SCD Type 1 vs Type 2: выбор

Паттерн production-использования: dim_customers

-- SCD Type 1: только актуальная версия
CREATE TABLE dim_customers (
    customer_id UInt64,
    name String,
    email String,
    plan LowCardinality(String),
    updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY customer_id;

-- ETL pipeline: CDC (Debezium/Maxwell) -> INSERT INTO dim_customers
-- Каждое изменение из PostgreSQL/MySQL -> новый INSERT с updated_at = now()

-- Аналитический запрос: JOIN c fact-таблицей
SELECT
    d.plan,
    count() AS orders,
    sum(f.amount) AS revenue
FROM orders AS f
INNER JOIN dim_customers FINAL AS d ON f.customer_id = d.customer_id
WHERE f.order_date >= '2024-01-01'
GROUP BY d.plan
ORDER BY revenue DESC;

Ключевые выводы

1. SCD Type 1 реализуется через ReplacingMergeTree(updated_at) + SELECT FINAL. Хранит только актуальную версию.
2. is_deleted параметр (24.x+) добавляет soft-delete: строки с is_deleted=1 удаляются при merge.
3. SCD Type 2 использует обычный MergeTree с valid_from/valid_to для полной истории изменений.
4. Версионный столбец обязателен для предсказуемой дедупликации — без него порядок недетерминирован.
5. FINAL гарантирует корректное чтение актуальной версии до merge. WHERE is_deleted = 0 фильтрует soft-deleted записи.