DWH, SCD и таблицы фактов

OLTP и OLAP

OLTP (Online Transaction Processing) и OLAP (Online Analytical Processing) — два разных подхода к работе с данными.

OLTP-системы обрабатывают операционные транзакции: создание заказа, добавление товара, проведение оплаты и изменение статуса. Такие операции обычно быстрые, затрагивают небольшое количество строк и должны выполняться согласованно.

Для OLTP особенно важны:

транзакции;
ограничения целостности;
блокировки и изоляция;
нормализация данных;
операции INSERT, UPDATE и DELETE.

Типичная структура OLTP-базы интернет-магазина:

text

customer
   |
   +--- order
          |
          +--- order_item
                   |
                   +--- product

OLAP-системы предназначены для аналитических запросов: расчета выручки по месяцам, сравнения продаж по регионам, поиска наиболее продаваемых товаров и анализа динамики показателей. Один запрос может читать миллионы строк и использовать GROUP BY и несколько JOIN.

Характеристика	OLTP	OLAP
Основная задача	Обработка операций	Анализ данных
Тип запросов	Короткие	Сложные
Изменение данных	Частое	Обычно пакетная загрузка
Структура	Нормализованная	Часто денормализованная
Основная нагрузка	`INSERT`, `UPDATE`, `DELETE`	`SELECT`, `GROUP BY`, `JOIN`
История	Не всегда хранится	Обычно важна

DWH

DWH (Data Warehouse), или хранилище данных, — система для накопления и аналитической обработки данных из различных источников.

Источниками DWH могут быть:

OLTP-базы;
CRM и ERP;
файлы;
внешние сервисы;
другие информационные системы.

Обобщенный поток данных выглядит так:

text

OLTP / CRM / ERP / Files
          |
          v
        ETL/ELT
          |
          v
         DWH
          |
          v
        BI / OLAP

В отличие от операционной базы, DWH хранит данные прежде всего для анализа. Операционная база может содержать таблицы customer, order, order_item, product, а в DWH те же данные могут быть преобразованы в:

text

dim_customer
dim_product
dim_date
dim_store
fact_sales

Такая структура отделяет операционную работу приложения от аналитической нагрузки.

Загрузка данных в DWH

Данные из OLTP-системы не обязательно копируются в DWH один к одному. Во время загрузки могут выполняться:

очистка данных;
преобразование типов;
объединение источников;
устранение дубликатов;
расчет дополнительных показателей;
сохранение истории изменений.

Таким образом, DWH — не просто копия операционной базы, а подготовленная для анализа модель данных.

SCD

SCD (Slowly Changing Dimension), или медленно изменяющееся измерение, — механизм отслеживания изменений атрибутов измерения во времени. Он применяется, когда данные меняются не очень часто и важно сохранить их историческое состояние.

Например, клиент может изменить город. В операционной системе достаточно хранить актуальное значение, а в DWH может потребоваться ответить, в каком городе находился клиент в момент совершения покупки.

Постановка проблемы

Есть данные о платежах:

payment_id	customer_id	staff_id	store_id	amount
1	7	1	1	500
2	10	1	2	700

И данные по сотрудникам:

staff_id	fio	store_id
1	Иванов Петр	1
2	Максим Сергеевич	2

По текущему значению store_id нельзя надежно определить, в каком магазине работал сотрудник во время каждой продажи. SCD решает эту задачу разными способами.

Тип 0 (SCD0)

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

customer_id	fio	born_date	born_city	passport
1	Иванов Петр	01.05.1974	Самара	4477 050607

Дату и место рождения можно считать неизменяемыми атрибутами, тогда как ФИО и паспорт потенциально могут измениться.

Тип 1 (SCD1)

Используется простое перезаписывание данных. История изменений полностью теряется.

customer_id	fio	born_date	born_city	passport
1	Семенов Петр	01.05.1974	Самара	5588 323344

После изменения ФИО и паспорта исходные значения уже недоступны.

Тип 2 (SCD2)

При изменении добавляется новая строка и служебные столбцы для версионности.

customer_id	fio	born_date	born_city	passport	active	created_date
1	Иванов Петр	01.05.1974	Самара	4477 050607	false	01.01.2020
1	Семенов Петр	01.05.1974	Самара	5588 323344	true	17.06.2021

История сохраняется, но появляется избыточность. Для управления версиями также часто используют valid_from, valid_to и is_current.

Тип 3 (SCD3)

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

Исходное состояние:

customer_id	fio	passport	old_fio	old_passport	change_date
1	Иванов Петр	4477 050607	null	null	null

После изменения:

customer_id	fio	passport	old_fio	old_passport	change_date
1	Семенов Петр	5588 323344	Иванов Петр	4477 050607	17.06.2021

Метод сохраняет ограниченную историю: количество изменений определяется числом заранее созданных столбцов.

Тип 4 (SCD4)

Текущие данные хранятся в основной таблице, а изменения — в отдельной исторической таблице.

Основная таблица:

customer_id	fio	born_date	born_city	passport	actual_date
1	Семенов Петр	01.05.1974	Самара	5588 323344	17.06.2021

Таблица изменений:

customer_id	fio	passport	actual_date
1	Иванов Петр	4477 050607	01.01.2020

Основная таблица остается компактной и содержит актуальную информацию, а историю можно получить через отдельную таблицу.

Тип 5 (SCD5)

Type 5 сочетает Type 4 и Type 1. История хранится в отдельной таблице, а в основном измерении добавляется ссылка на текущее мини-измерение.

Мини-измерение:

profile_id	age_group	income_group
1	25-34	Средний
2	35-44	Высокий

В таблице фактов может храниться историческая ссылка на мини-измерение, а current_profile_id в основном измерении обновляется по правилам Type 1. Поэтому старые факты сохраняют прежний профиль, но актуальный профиль также доступен.

Тип 6 (SCD6)

Type 6 объединяет Type 1, Type 2 и Type 3. История хранится в отдельных строках измерения, а в каждой строке дополнительно сохраняется текущее значение атрибута.

customer_key	customer_id	city	current_city	valid_from	valid_to	is_current
1	10	Москва	Санкт-Петербург	2025-01-01	2026-03-01	false
2	10	Санкт-Петербург	Санкт-Петербург	2026-03-01	NULL	true

city показывает значение в соответствующий период, а current_city — актуальное значение. Поэтому можно выполнять анализ как «на момент события», так и по текущему состоянию.

Тип 7 (SCD7)

Type 7 поддерживает исторический и текущий взгляд через две ссылки в таблице фактов:

ссылку на историческую версию измерения;
ссылку на устойчивый идентификатор сущности.

customer_key	customer_id	city	is_current
1	10	Москва	false
2	10	Санкт-Петербург	true

В таблице фактов могут находиться:

fact_id	historical_customer_key	customer_id	amount
1001	1	10	3000

historical_customer_key позволяет получить состояние клиента на момент покупки, а customer_id — обратиться к текущей версии.

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

Тип	Основной механизм	Особенность
Type 0	Не изменять атрибут	История сохраняется без обработки изменений
Type 1	Перезапись	История теряется
Type 2	Новая строка версии	Полная история изменений
Type 3	Старое значение в отдельном столбце	Ограниченная история
Type 4	Отдельная таблица истории	Текущее и историческое состояние разделены
Type 5	Type 4 + Type 1	История и текущее мини-измерение
Type 6	Type 1 + Type 2 + Type 3	Историческое и текущее значение в строках версий
Type 7	Две ссылки из факта	Исторический и текущий взгляд

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

Таблицы фактов

Таблица фактов содержит показатели бизнес-процессов и ссылки на измерения, по которым эти показатели анализируются.

Например, продажу товара можно представить так:

date_id	product_id	customer_id	store_id	quantity	amount
1	101	10	5	2	3000
1	102	10	5	1	1500
2	101	15	7	3	4500

date_id, product_id, customer_id и store_id — ссылки на измерения, а quantity и amount — показатели.

Классификация таблиц фактов

В подходе Kimball выделяют:

транзакционные факты — строка соответствует событию или элементу транзакции: продаже, платежу, заказу;
факты периодического снимка — состояние процесса на определенный момент: остаток на конец дня, баланс на конец месяца;
факты накопительного снимка — один процесс от начала до завершения, например заказ со стадиями «создан → оплачен → доставлен»;
factless fact table — строка фиксирует событие или состояние без числовых показателей: посещение занятия или доступность товара.

Таблицы измерений

Измерение содержит описание объекта, по которому анализируются факты.

Измерение товара:

product_id	product_name	category
101	Ноутбук	Электроника
102	Монитор	Электроника

Измерение даты:

date_id	date	month	year
1	2026-01-10	1	2026
2	2026-01-11	1	2026

Пример аналитического запроса:

sql

SELECT
    d.year,
    p.category,
    SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
JOIN dim_product p ON p.product_id = f.product_id
GROUP BY
    d.year,
    p.category;

Зерно таблицы фактов

Зерно — это то, что представляет одна строка таблицы фактов. Например:

Одна строка fact_sales — одна позиция товара в одном заказе.

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

Типы фактов

Аддитивные можно складывать по всем измерениям: quantity, amount.
Полуаддитивные можно складывать только по некоторым измерениям. Остаток товара можно суммировать по товарам, но обычно нельзя складывать остатки по датам.
Неаддитивные нельзя корректно суммировать. Например, margin_percent требует специальной агрегации.

DWH, SCD и таблицы фактов ​

OLTP и OLAP ​

DWH ​

Загрузка данных в DWH ​

SCD ​

Постановка проблемы ​

Тип 0 (SCD0) ​

Тип 1 (SCD1) ​

Тип 2 (SCD2) ​

Тип 3 (SCD3) ​

Тип 4 (SCD4) ​

Тип 5 (SCD5) ​

Тип 6 (SCD6) ​

Тип 7 (SCD7) ​

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

Таблицы фактов ​

Классификация таблиц фактов ​

Таблицы измерений ​

Зерно таблицы фактов ​

Типы фактов ​

DWH, SCD и таблицы фактов

OLTP и OLAP

DWH

Загрузка данных в DWH

SCD

Постановка проблемы

Тип 0 (SCD0)

Тип 1 (SCD1)

Тип 2 (SCD2)

Тип 3 (SCD3)

Тип 4 (SCD4)

Тип 5 (SCD5)

Тип 6 (SCD6)

Тип 7 (SCD7)

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

Таблицы фактов

Классификация таблиц фактов

Таблицы измерений

Зерно таблицы фактов

Типы фактов