SCD типы для Data Engineer: гайд для собеса

Что такое SCD и зачем

SCD (Slowly Changing Dimensions) — стратегии хранения изменений в таблицах измерений Kimball-модели. Когда у клиента сменился адрес, у товара — категория, у сотрудника — отдел, есть выбор: затереть старое значение, сохранить историю, или сохранить только последнее предыдущее.

Главная боль без SCD — отчёт «выручка по регионам за прошлый год» считается по текущей привязке клиентов к региону. Клиент переехал из Москвы в Питер в декабре — его январская покупка в Москве вдруг стала питерской. Менеджер по Москве недосчитался выручки, бухгалтерия спрашивает, что произошло. Это классический баг отчётности, и решается он через SCD Type 2.

На собесе DE спросят: «Какие типы SCD знаете?» — нужно назвать минимум 0, 1, 2 и понять кейсы для каждого.

SCD Type 0: фиксация

Значение никогда не меняется. Если в исходнике обновилось — игнорируем.

Когда использовать: дата создания клиента, оригинальный канал привлечения, первая покупка. Эти атрибуты по бизнес-смыслу неизменны.

-- dim_user (Type 0 для signup_channel)
| user_id | signup_channel | created_at          |
|---------|----------------|---------------------|
| 1       | organic        | 2024-03-15 10:00:00 |

Если в проде в users поменяли signup_channel с organic на paid — DWH остаётся с organic. Это намеренно.

Реализация: на уровне ETL просто не апдейтим колонку. В dbt — игнорируем поле в snapshot/merge.

SCD Type 1: перезапись

Хранится только текущее значение. История теряется.

Когда использовать: опечатки, телефоны, email — то, где исторические значения не нужны для аналитики.

-- dim_user (Type 1 для email)
| user_id | email                |
|---------|----------------------|
| 1       | new@example.com      |  -- было old@example.com, обновили

Реализация — UPSERT по user_id:

MERGE INTO dim_user AS dst
USING staging_users AS src
ON dst.user_id = src.user_id
WHEN MATCHED THEN UPDATE SET email = src.email, phone = src.phone
WHEN NOT MATCHED THEN INSERT (user_id, email, phone) VALUES (src.user_id, src.email, src.phone);

В dbt — incremental-модель со стратегией merge и unique_key='user_id'.

Type 1 — самый дешёвый, но необратимо стирает историю. Прежде чем выбирать его — спросить аналитика: «Нам когда-нибудь понадобится знать, что email был раньше другим?»

SCD Type 2: история версий

На каждое изменение — новая строка. У строк есть valid_from / valid_to и/или is_current флаг.

Когда использовать: адрес клиента, отдел сотрудника, категория товара, тариф подписки. Всё, что влияет на отчётность по периодам.

-- dim_user (Type 2 для region)
| user_id | region | valid_from          | valid_to            | is_current |
|---------|--------|---------------------|---------------------|------------|
| 1       | MSK    | 2024-01-01 00:00:00 | 2024-12-15 14:30:00 | FALSE      |
| 1       | SPB    | 2024-12-15 14:30:00 | 9999-12-31 00:00:00 | TRUE       |

Запрос «выручка по региону на день покупки»:

SELECT
    d.region,
    SUM(o.amount) AS revenue
FROM fct_orders o
JOIN dim_user d
    ON o.user_id = d.user_id
   AND o.created_at >= d.valid_from
   AND o.created_at <  d.valid_to
GROUP BY d.region;

Реализация в чистом SQL — три шага: закрыть текущую версию (UPDATE valid_to=NOW(), is_current=false), вставить новую версию, обработать новых пользователей.

В dbt — snapshot:

{% snapshot dim_user_snapshot %}
{{ config(
    unique_key='user_id',
    strategy='check',
    check_cols=['region', 'tariff']
) }}
SELECT user_id, region, tariff, email FROM {{ source('raw', 'users') }}
{% endsnapshot %}

Surrogate key — отдельная колонка user_sk (sha256 от user_id + valid_from), нужна для джоинов в фактах, чтобы зацепить именно эту версию строки.

SCD Type 3: одно прошлое значение

Хранится текущее и одно предыдущее значение в отдельных колонках.

Когда использовать: редко. Кейс — нужно знать «нынешний и предыдущий», но не всю историю. Например, текущий и прошлый менеджер клиента.

| user_id | manager_current | manager_previous | manager_changed_at  |
|---------|-----------------|------------------|---------------------|
| 1       | Anna            | Boris            | 2025-04-01 10:00:00 |

Реализация:

UPDATE dim_user
SET manager_previous = manager_current,
    manager_current = 'Catherine',
    manager_changed_at = NOW()
WHERE user_id = 1;

Type 3 редко встречается на практике — обычно либо Type 1 (не нужна история), либо Type 2 (нужна полная). Если на собесе спросили «когда Type 3?» — честный ответ «редко, обычно для одной критичной смены при ограничении на размер таблицы».

SCD Type 6: гибрид 1+2+3

Одна таблица содержит признаки всех трёх типов: история версий (Type 2), плюс колонка с актуальным значением во всех строках (Type 1), плюс колонка с предыдущим значением (Type 3).

| user_sk | user_id | region | region_current | valid_from | valid_to | is_current |
|---------|---------|--------|----------------|------------|----------|------------|
| 1       | 1       | MSK    | SPB            | 2024-01-01 | 2024-12-15 | FALSE |
| 2       | 1       | SPB    | SPB            | 2024-12-15 | 9999-12-31 | TRUE  |

Зачем region_current во всех строках: чтобы аналитик мог одним JOIN получить историю покупок клиента с пометкой «где он сейчас». Без этого нужен self-join.

Type 6 — мощно, но сложно поддерживать: при каждом обновлении нужно update region_current во ВСЕХ исторических строках клиента. На больших dimension-ах — дорогая операция.

На собесе Type 6 спрашивают редко. Знать — плюс к экспертизе, реализовывать — только если требование явно.

Частые ошибки

Использовать Type 1, потом плакать. Год назад «история не нужна», сейчас CFO просит cohort-анализ — а данных нет. Если есть сомнения — Type 2.

Type 2 без surrogate key. Джоины фактов с dimension по user_id без учёта valid_from/valid_to дают M:N. Surrogate key + правильный JOIN решают.

Открытый valid_to как NULL. Удобнее 9999-12-31: BETWEEN-фильтры работают без OR valid_to IS NULL.

Игнорировать late-arriving facts. Факт пришёл вчера, но датирован прошлым годом — нужен правильный SCD-срез на ту дату. Если справочник уже несколько раз менялся — джоин даст не ту версию. Решение: всегда джоинить через valid_from <= fact_date < valid_to.

Triggers вместо batch update. Ручные триггеры на изменения в OLTP, чтобы пушить в DWH — фрагильно. Лучше — CDC или регулярный snapshot.

Type 2 на каждое поле. Если хранить историю по каждой колонке — таблица раздуется в десятки раз. Type 2 — только на бизнес-критичные атрибуты, остальные Type 1.

Связанные темы

• dbt на собеседовании DE
• Подготовка к собесу Data Engineer
• DWH ClickHouse на собесе DE
• Data Warehouse vs Database
• SQL для Data Engineer: собеседование

FAQ

Какой тип SCD по умолчанию выбирать?

Type 1 — для технических полей (email, телефон), Type 2 — для бизнес-атрибутов, влияющих на отчётность по периодам (регион, отдел, тариф). Если не уверен — Type 2: дешевле сначала хранить историю, чем потом восстанавливать.

Как реализовать SCD Type 2 в ClickHouse?

ReplacingMergeTree с версионной колонкой и регулярным OPTIMIZE FINAL даёт что-то близкое к Type 2, но не идентично — старые версии хранятся до мержа. Для строгого SCD2 — отдельная таблица с явными valid_from/valid_to и INSERT при изменениях, либо dbt snapshot поверх MergeTree.

Что такое late-arriving dimension?

Факт пришёл, а строки в dimension под нужный момент времени ещё нет (или она ещё не имеет нужной версии). Решения: «inferred member» (заглушка с placeholder, потом обновляется), либо отложить факт до прихода dimension.

Surrogate key — это всегда автоинкремент?

Нет. Чаще — hash от business key + valid_from (детерминированно, можно пересчитать). Auto-increment плох тем, что после full-refresh ID меняются и фактовые таблицы ломаются.

Slowly vs Rapidly Changing Dimension — есть разница?

Slowly — изменения редкие (раз в месяц/год). Rapidly — частые (несколько раз в день). RCD требуют отдельных стратегий — обычно выносят быстро меняющийся атрибут в junk-dimension или mini-dimension, чтобы не раздувать основную.

Это официальная информация?

Нет. Статья основана на работах Kimball, документации dbt и опыте практики. Конкретные требования к моделированию зависят от компании и команды.

Тренируйте моделирование данных — откройте тренажёр с 1500+ вопросами для собесов.