Skip to content

SCD.md

Latest commit

 

History

History
426 lines (307 loc) · 25.2 KB

SCD.md

File metadata and controls

426 lines (307 loc) · 25.2 KB

Медленно меняющиеся измерения (SCD): как хранить историю в аналитических базах данных

Для кого эта статья?
Для тех, кто уже умеет писать базовые SQL-запросы в PostgreSQL, знаком с понятиями таблиц, строк и колонок, и теперь делает первые шаги в аналитике и проектировании хранилищ данных.

1. Введение: зачем вообще нужны измерения и почему они «медленно меняются»?

Представьте, что вы строите отчёт по продажам. У вас есть таблица с фактами — например, «продано 10 единиц товара X клиенту Y 15 марта». Но чтобы понять, кто такой клиент Y или что за товар X, вам нужны справочники — таблицы с описанием клиентов, товаров, регионов и т.п.

В мире аналитики такие справочники называют измерениями (dimensions), а таблицу с продажами — фактами (facts).

А теперь представьте: клиент сменил адрес или перешёл в другую категорию (например, из «обычного» в «VIP»). Если вы просто обновите строку в таблице клиентов, то потеряете информацию о том, какой статус у клиента был на момент продажи. А это критично: отчёт «продажи VIP-клиентам в марте» окажется неверным!

Такие атрибуты — которые меняются со временем, но не каждый день — и называются медленно меняющимися измерениями (Slowly Changing Dimensions, SCD).

2. Что такое Slowly Changing Dimensions (SCD)?

SCD — это подход к хранению изменений в измерениях с учётом времени. Он позволяет отвечать на вопросы вроде:

  • Какой адрес у клиента был на дату заказа?
  • Сколько продаж пришлось на товары категории «Электроника» до того, как её переименовали в «Гаджеты»?

Без SCD вы видите только текущее состояние, а с ним — всю историю.

3. Типы SCD — простыми словами

Существует несколько стандартных стратегий обработки изменений. Рассмотрим самые важные.

Type 0 — Никогда не меняется

Атрибут фиксирован навсегда. Например, дата рождения клиента.
Такие поля не требуют специальной обработки — они просто не обновляются.

Type 1 — Просто перезаписать

Вы просто делаете UPDATE, и старое значение исчезает.

✅ Просто.
❌ История теряется.

Подходит, если изменение — это исправление ошибки (например, опечатка в имени).

Type 2 — Новая строка для новой версии

Каждое изменение порождает новую строку в таблице. Старая строка остаётся, но помечается как «устаревшая».

✅ Полная история.
✅ Можно восстановить состояние на любую дату.
❌ Больше данных, сложнее запросы.

Это самый распространённый подход в аналитике.

Type 3 — Добавить колонку «предыдущее значение»

В таблице появляются поля вроде previous_category, category_change_date.

✅ Простая история «до/после».
❌ Хранит только одно предыдущее значение. Не масштабируется.

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

Type 4, 5, 6 — Продвинутые гибриды

Эти типы существуют, но встречаются редко и почти не используются новичками:

  • Type 4: история выносится в отдельную таблицу («мини-хранилище» для одного измерения).
  • Type 5: комбинация Type 4 и Type 1 — текущее значение в основной таблице, а история — отдельно.
  • Type 6: объединяет Type 1, 2 и 3 в одной таблице — очень гибко, но сложно.

Вам не нужно запоминать эти типы сейчас. Достаточно знать, что они бывают — на случай, если встретите их в документации.

4. Практический пример на PostgreSQL: Type 1 vs Type 2

Допустим, у нас есть таблица клиентов:

-- Исходная таблица (до изменений)
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL  -- например: 'Regular', 'VIP'
);

Type 1: просто обновляем

Клиент №1 стал VIP:

UPDATE customers
SET category = 'VIP'
WHERE customer_id = 1;

Теперь в таблице только новое значение. Если продажа была сделана до этого UPDATE, вы не узнаете, что клиент тогда был «Regular».

Type 2: сохраняем историю — подробнее

Чтобы хранить историю, мы меняем структуру таблицы. Вот ключевые поля:

  • customer_key — искусственный (surrogate) первичный ключ. Он уникален для каждой версии клиента.
  • customer_id — бизнес-идентификатор (например, из CRM). Он не меняется и связывает все версии одного клиента.
  • valid_from — дата, с которой эта версия стала актуальной.
  • valid_to — дата, по которую эта версия была актуальной. Если NULL — значит, версия актуальна сейчас.

Пример структуры:

CREATE TABLE customers_scd2 (
    customer_key SERIAL PRIMARY KEY,     -- уникальный ID каждой версии
    customer_id INT NOT NULL,            -- неизменный бизнес-ID клиента
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    valid_from DATE NOT NULL,            -- с какой даты действует
    valid_to DATE                        -- по какую дату действовала (NULL = сейчас)
);

Шаг 1. Добавляем начальную запись (клиент зарегистрировался 1 января 2024):

INSERT INTO customers_scd2 (customer_id, name, category, valid_from, valid_to)
VALUES (1, 'Иван Петров', 'Regular', DATE '2024-01-01', NULL);

Шаг 2. 15 апреля 2025 клиент становится VIP. Мы делаем два действия:

  1. Закрываем старую запись: указываем дату начала новой версии (интервал [valid_from, valid_to)).
  2. Добавляем новую запись: она начинает действовать с 15 апреля и пока актуальна.
-- 1. Завершаем предыдущую версию
UPDATE customers_scd2
SET valid_to = DATE '2025-04-15'
WHERE customer_id = 1 AND valid_to IS NULL;

-- 2. Вставляем новую версию
INSERT INTO customers_scd2 (customer_id, name, category, valid_from, valid_to)
VALUES (1, 'Иван Петров', 'VIP', DATE '2025-04-15', NULL);

Теперь в таблице две строки для одного клиента. И мы можем спросить:

Какой была категория клиента на 10 апреля 2025?

SELECT category
FROM customers_scd2
WHERE customer_id = 1
  AND DATE '2025-04-10' >= valid_from
  AND (valid_to IS NULL OR DATE '2025-04-10' < valid_to);

Результат: 'Regular' — правильно!

💡 Почему не BETWEEN valid_from AND valid_to?
Потому что у актуальной записи valid_to IS NULL. В SQL сравнения с NULL не дают TRUE, поэтому для “текущей” версии обычно пишут valid_to IS NULL OR ....

Type 2 через логику, похожую на UPSERT

В реальных ETL-процессах часто используют идемпотентные операции: запуск скрипта дважды не должен ломать данные. Для этого удобно применять подход, похожий на upsert (update + insert), но адаптированный под логику SCD Type 2.

В PostgreSQL классический ON CONFLICT не подходит напрямую, потому что мы не обновляем существующую строку, а добавляем новую при изменении.

Поэтому логика выглядит так:

  1. Сравнить входящие данные с последней версией в таблице.
  2. Если атрибуты изменились — закрыть старую запись и вставить новую.
  3. Если не изменились — ничего не делать.

Пример (часто реализуется в Python, dbt, Airflow и т.п.):

-- Предположим, новая версия: customer_id=1, category='VIP', effective_date='2025-04-15'

-- Шаг 1: вставляем новую версию, только если есть изменения
WITH last_version AS (
  SELECT * FROM customers_scd2
  WHERE customer_id = 1 AND valid_to IS NULL
)
INSERT INTO customers_scd2 (customer_id, name, category, valid_from, valid_to)
SELECT 1, 'Иван Петров', 'VIP', DATE '2025-04-15', NULL
WHERE EXISTS (
  SELECT 1 FROM last_version WHERE category != 'VIP'
);

-- Шаг 2: если вставка произошла — закрываем старую запись
UPDATE customers_scd2
SET valid_to = DATE '2025-04-15'
WHERE customer_id = 1 AND valid_to IS NULL
  AND EXISTS (
    SELECT 1 FROM customers_scd2
    WHERE customer_id = 1 AND category = 'VIP' AND valid_from = DATE '2025-04-15'
  );

На практике такие логики чаще выносят в ETL-инструменты (например, dbt с пакетом dbt-scd), потому что чистый SQL быстро становится громоздким.

💡 Главное: SCD Type 2 — это не одна операция, а процесс: сравнить → закрыть старое → добавить новое.

В учебном проекте из папки dwh-modeling/sql/ эти идеи можно увидеть «вживую»:

  • в 02_dml_stg-dds.sql собирается полная история клиентов (SCD2) из всех событий в stg.customers_raw — это пример первичной загрузки / full backfill;
  • в 03_demo_increment.sql реализован инкрементальный SCD2: в одной транзакции добавляются новые версии клиентов из снимка ods.customers и закрываются предыдущие актуальные строки в dds.dim_customer.

А что, если СУБД не позволяет UPDATE? (Trino, Hive, ClickHouse в режиме append-only)

Некоторые аналитические системы (например, Hive в формате ORC/Parquet, Trino, ClickHouse в режиме только вставки) не поддерживают UPDATE старых строк. Как тогда реализовать SCD Type 2?

Ответ: никаких UPDATE не нужно — ведь в Type 2 мы и так не меняем старые данные, а только добавляем новые!

Алгоритм загрузки (ETL):

  1. Сравнить входящие данные с последней версией в таблице.
  2. Если есть изменения — пишем новую строку с новыми valid_from.
  3. Старые строки остаются нетронутыми.

Пример в Trino/Hive-стиле (только INSERT):

Давайте разберём этот запрос по шагам, чтобы понять его логику:

-- new_customers — staging-таблица с обновлёнными данными
-- dim_customers_scd2 — основная таблица (append-only)

INSERT INTO dim_customers_scd2
WITH current_customers AS (
  SELECT *
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY valid_from DESC) AS rn
    FROM dim_customers_scd2
  )
  WHERE rn = 1  -- отбираем первую, самую свежую, запись
)
SELECT
  uuid() AS customer_key,  -- уникальный ID каждой версии
  n.customer_id,           -- неизменный бизнес-ID клиента
  n.name,
  n.category,
  COALESCE(n.effective_date, current_date) AS valid_from  -- дата начала действия новой версии
FROM new_customers n
LEFT JOIN current_customers c ON n.customer_id = c.customer_id
WHERE c.customer_id IS NULL OR c.category != n.category;  -- условие верно, если какие-то из полей справочника поменялись
🎯 Как работает этот запрос: пошаговое объяснение
Шаг 1: Подготовка данных (CTE current_customers)

CTE current_customers находит последнюю версию каждого клиента из таблицы dim_customers_scd2:

SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY valid_from DESC) AS rn
FROM dim_customers_scd2
  • PARTITION BY customer_id — группируем по клиентам
  • ORDER BY valid_from DESC — сортируем версии от самой новой к самой старой
  • WHERE rn = 1 — выбираем только самую свежую версию
Шаг 2: Сравнение данных (LEFT JOIN + WHERE)

Теперь сравниваем новые данные с текущими:

FROM new_customers n
LEFT JOIN current_customers c ON n.customer_id = c.customer_id

Возможные сценарии после JOIN:

Сценарий n.customer_id c.customer_id Условие WHERE Результат
Новый клиент 2 NULL ✅ c.customer_id IS NULL Вставляется
Категория изменилась 1 1 ✅ c.category != n.category Вставляется
Без изменений 3 3 ❌ оба условия ложны Пропускается
Шаг 3: Вставка новых версий

Для подходящих записей создаём новую версию:

  • uuid() — генерируем уникальный ключ для новой версии
  • current_date - функция, возвращающая текущую даты
  • COALESCE(n.effective_date, current_date) — устанавливаем дату начала действия новой версии

💡 Правильный подход к датам: В реальных ETL-процессах важно использовать дату из исходных данных, когда она доступна. Мы используем COALESCE(n.effective_date, current_date), что означает:

  • Если в new_customers есть поле effective_date — используем его
  • Если нет — используем текущую дату (current_date)

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

  • effective_date отражает реальную дату изменения (например, когда клиент стал VIP)
  • current_date — это дата загрузки данных, которая может не совпадать с датой изменения
  • Использование правильной даты критично для точного исторического анализа

Пример правильной структуры исходных данных:

new_customers:
customer_id | name        | category | effective_date
1           | Иван Петров | VIP      | 2025-04-15     ← дата реального изменения
Практический пример

До выполнения запроса:

dim_customers_scd2:
customer_id | name        | category | valid_from
1           | Иван Петров | Regular  | 2024-01-01

Новые данные:

new_customers:
customer_id | name        | category
1           | Иван Петров | VIP      ← изменилась категория
2           | Мария Иванова| Regular ← новый клиент

После выполнения запроса:

dim_customers_scd2:
customer_id | name        | category | valid_from
1           | Иван Петров | Regular  | 2024-01-01  ← старая версия
1           | Иван Петров | VIP      | 2025-11-04  ← новая версия
2           | Мария Иванова| Regular | 2025-11-04  ← новый клиент

💡 Ключевой момент: В append-only системах мы не обновляем старые записи, а только добавляем новые. История сохраняется автоматически!

Главный вопрос после загрузки: как же читать эти данные?

Поскольку мы не можем обновлять valid_to у предыдущей версии, стандартный подход с BETWEEN не сработает. Вместо этого, для поиска нужной версии мы полагаемся на оконные функции или на логику «ближайшей даты, но не позже».

Паттерн 1: Найти последнюю (актуальную) версию на сегодня

Это самый частый запрос. Мы хотим видеть самую свежую информацию о клиенте.

-- Вариант с оконной функцией (универсальный и надежный)
WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY valid_from DESC) AS rn
  FROM dim_customers_scd2
)
SELECT
  customer_id, name, category, valid_from
FROM ranked
WHERE rn = 1;

Этот запрос берёт строку с самой поздней датой начала действия для каждого клиента.

Паттерн 2: Найти версию, которая была актуальна на конкретную дату

Это основная цель SCD2. Например, «какой статус клиента был на дату заказа 2025-03-15?».

В append-only мире у нас нет valid_to, поэтому мы ищем последнюю версию, у которой valid_from ≤ целевой даты.

-- Запрос для получения состояния на '2025-03-15'
WITH as_of_date AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY valid_from DESC) AS rn
  FROM dim_customers_scd2
  WHERE valid_from <= DATE '2025-03-15'
)
SELECT
  customer_id, name, category, valid_from
FROM as_of_date
WHERE rn = 1;

Как это работает:

  1. WHERE valid_from <= DATE '2025-03-15' отфильтровывает все версии, которые появились после нашей целевой даты.
  2. ORDER BY valid_from DESC сортирует оставшиеся версии от самой свежей к самой старой.
  3. ROW_NUMBER() ... WHERE rn = 1 выбирает самую свежую из актуальных на ту дату версий.

Это и есть «путешествие во времени» (time travel) в системах без встроенной поддержки этой функции.

Почему не использовать флаг «текущая версия»?
В append-only системах любой флаг актуальности становится устаревшим сразу после новой вставки. Без UPDATE его сложно поддерживать, поэтому в таких архитектурах чаще полагаются на даты и оконные функции — так модель остаётся идемпотентной.

Таким образом, SCD Type 2 не только совместим с append-only системами, но и является для них естественным выбором, так как его логика основана исключительно на добавлении данных, а не на их изменении.

5. Когда что использовать?

Сценарий Рекомендуемый тип
Исправление опечатки Type 1
Юридически значимые изменения (статус, тариф, регион) Type 2
Очень простая аналитика без требований к истории Type 1
Нужна только «последняя смена» и ничего больше Type 3 (осторожно!)

Совет новичку: если сомневаетесь — выбирайте Type 2. Лучше иметь историю и не использовать её, чем не иметь и не суметь ответить на важный вопрос.

6. Подводные камни и советы

  • Не используйте customer_id как первичный ключ в Type 2. Он повторяется! Вместо этого — customer_key (surrogate key).
  • Всегда задавайте valid_to как NULL для актуальной записи, если это допустимо в вашей СУБД — это упрощает модель.
  • Для условий “актуально на дату” используйте паттерн: d >= valid_from AND (valid_to IS NULL OR d < valid_to).
  • Type 2 увеличивает объём данных — но для аналитики это нормально.
  • В связке с фактами: в таблице фактов храните customer_key, а не customer_id — иначе не получится соединить с нужной версией.

7. Заключение

Медленно меняющиеся измерения — это не «магия», а практический инструмент для честной и точной аналитики во времени.

Начните с понимания разницы между Type 1 и Type 2. Попробуйте реализовать оба подхода в своей БД. Задайте себе вопрос:

«Если бы я построил отчёт по данным на прошлый месяц — дал бы он правильный ответ после сегодняшнего изменения?»

Если нет — вам нужен SCD Type 2.

И помните: даже в системах без UPDATE вы можете хранить полную историю — достаточно понимать, как правильно читать данные с помощью оконных функций и временных границ.