Паттерны проектирования КХД

КХД - корпоративное хранилище данных

✒️ Автор

Представьте себе два разных мира баз данных: мир быстрых операций и мир глубокого анализа. Инженер данных строит мост (ETL/ELT-процессы) из мира OLTP в мир OLAP.

OLTP (On-line Transaction Processing) — Транзакционные системы

• Что это? Это системы, которые обслуживают текущую деятельность бизнеса: кассовый аппарат в магазине, банковское приложение, система бронирования билетов.
• Задача: Быстро и надежно выполнять множество мелких операций (запись, изменение, удаление). Например: "Продать товар X", "Перевести 100 рублей со счета A на счет B".
• Ключевая особенность: Данные здесь максимально нормализованы. Это критично для целостности данных и избегания аномалий при частых изменениях. OLTP-системы — это и есть те самые источники данных для хранилища.

OLAP (On-line Analytical Processing) — Аналитические системы

• Что это? Это и есть наши хранилища данных (КХД) и витрины данных.
• Задача: Быстро выполнять сложные запросы на больших объемах исторических данных. Например: "Показать средний чек по всем магазинам за последний год с разбивкой по категориям товаров и регионам".
• Ключевая особенность: Данные здесь часто денормализованны (как в схеме "звезда"), чтобы избежать медленных соединений (JOIN) множества таблиц и ускорить агрегацию.

📦 Что такое нормализация?

Нормализация — это набор правил (форм нормализации), которые помогают организовать таблицы так, чтобы:

• исключить дублирование данных (redundancy),
• избежать аномалий при вставке, обновлении или удалении (insertion/update/delete anomalies),
• упростить сопровождение хранилища.

✅ 1NF (Первая нормальная форма) — “Ячейки = атомарные значения”

📌 Правило:

• В таблице нет повторяющихся групп столбцов,
• В каждой ячейке — одно значение, а не список.

🔍 Пример плохой таблицы:

🔧 После нормализации (1NF):

📎 То есть — разбиваем списки по строкам.

✅ 2NF (Вторая нормальная форма) — “Убираем зависимости не от всего ключа”

📌 Правило:

• Уже есть 1NF.
• Все неключевые поля зависят от всего составного ключа, а не его части.

🔍 Плохой пример:

🔧 Проблема: имя_клиента зависит только от заказ_id, а не от пары (заказ_id, товар).

✅ Надо вынести клиента в отдельную таблицу:

Таблица “заказы”

Таблица “товары в заказах”

✅ 3NF (Третья нормальная форма) — “Нет зависимостей между неключевыми полями”

📌 Правило:

• Уже есть 2NF.
• Никакое неключевое поле не зависит от другого неключевого.

🔍 Плохой пример:

🔧 Проблема: индекс зависит от город, а не от клиент_id.

✅ Надо вынести справочник городов:

Таблица “клиенты”

Таблица “города”

✅ BCNF (Нормальная форма Бойса-Кодда) — "Все детерминанты — ключи"

📌 Отличие от 3NF:

• 3NF допускает, что детерминант (определяющее поле) не обязательно ключ, если зависимое поле — ключ.
• В BCNF — всё, что определяет другие поля, должно быть кандидатом в ключ.

🔍 Пример (тонкий случай):

• преподаватель → предмет, аудитория → предмет, но ни преподаватель, ни аудитория не являются уникальными ключами.

✅ 4NF (Четвёртая нормальная форма) — "Нет многозначных зависимостей"

📌 Когда один ключ связан с несколькими независимыми наборами данных.

🔍 Пример:

→ Здесь студент → курс и студент → кружок, но курс и кружок не зависят друг от друга.

🔧 Надо разнести в две таблицы:

• Таблица “студенты-курсы”
• Таблица “студенты-кружки”

✅ 5NF (Пятая нормальная форма) — "Разложение без потерь"

📌 Сложные связи, когда таблица делится на 3 и более таблиц, но при этом при соединении ничего не теряется и не дублируется.

🔍 Пример: если есть тройная связь Поставщик — Деталь — Проект, и всё работает только в этой тройке, а не по парам.

• Нельзя просто разделить по парам Поставщик-Деталь, Поставщик-Проект, Деталь-Проект — некоторые комбинации не будут существовать.

✅ 6NF (Шестая нормальная форма) — "Минимальные атомы, с учётом времени"

📌 Самая формальная форма. Применяется, когда данные часто меняются со временем, и каждое значение нужно отслеживать отдельно по времени.

🔧 Каждое значение — в своей таблице, с временными метками (valid_from, valid_to).

🧠 Это чаще используется:

• в temporal databases
• для декомпозиции фактов в хранилищах
• при работе с медленно меняющимися измерениями (SCD)

Для инженера данных ключевое значение имеют три основных подхода к проектированию хранилищ данных (КХД): моделирование по Кимбаллу (Kimball), подход Инмона (Inmon) и Data Vault. Каждый из них предлагает свою структуру и философию организации данных для аналитики.

Схема-звезда (Dimensional Modeling / Kimball)

Это, пожалуй, самый популярный и практичный подход для инженеров данных. Его суть — создание простых и понятных структур, оптимизированных для быстрого выполнения запросов.

• Основная идея: Данные организуются в виде "схемы-звезды" (star schema) или "снежинки" (snowflake schema).
• Таблица фактов (Fact Table): В центре находится таблица с количественными показателями бизнеса (например, продажи, клики, транзакции). Она содержит числовые данные и ключи для связи с таблицами-измерениями.
• Таблицы измерений (Dimension Tables): Это таблицы, которые описывают факты. Они содержат атрибуты, по которым можно фильтровать и группировать данные (например, клиенты, продукты, даты, география).
• Когда использовать: Идеально подходит для создания витрин данных (Data Marts), ориентированных на конкретные бизнес-процессы (маркетинг, продажи, финансы). Отлично работает, когда бизнес-пользователям нужен быстрый и лёгкий доступ к данным для отчётности и BI-аналитики.
• Преимущества:
• Простота: Модель интуитивно понятна бизнес-пользователям.
• Производительность: Оптимизирована для чтения и агрегации, что ускоряет выполнение запросов.
• Гибкость: Легко добавлять новые атрибуты и показатели.
• Недостатки:
• Избыточность данных: Денормализация приводит к повторению данных в измерениях.
• Сложность интеграции: Объединение данных из разных "звёзд" может быть затруднительным, что усложняет получение единой картины по всей компании.

Пример: Таблица фактов Sales с колонками product_key, customer_key, date_key, amount и таблицы измерений DimProduct, DimCustomer, DimDate.**

Корпоративное информационное хранилище (Corporate Information Factory / Inmon)

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

• Основная идея: Проектирование "сверху вниз". Сначала все данные из разных источников интегрируются в централизованное хранилище данных (Enterprise Data Warehouse, EDW). Это хранилище находится в третьей нормальной форме (3NF), что устраняет избыточность данных. Уже из этого центрального хранилища создаются витрины данных (Data Marts) для конкретных отделов.
• Когда использовать: В крупных организациях со сложными и разнообразными источниками данных, где целостность и согласованность данных являются главным приоритетом.
• Преимущества:
• Единый источник правды: Централизованное хранилище обеспечивает целостность и согласованность данных по всей компании.
• Низкая избыточность: Нормализация минимизирует дублирование данных.
• Гибкость: Легко создавать новые витрины данных для любых нужд.
• Недостатки:
• Сложность и стоимость: Требует значительных начальных усилий и затрат на проектирование и загрузку данных в нормализованную модель.
• Медленные запросы к EDW: Запросы напрямую к центральному хранилищу могут быть медленными из-за большого количества соединений (JOINs). Аналитика обычно выполняется по витринам.

Data Vault

Это гибридный подход, который пытается взять лучшее от моделей Кимбалла и Инмона. Он спроектирован для обеспечения гибкости, масштабируемости и аудируемости хранилища.

• Основная идея: Модель состоит из трёх основных типов таблиц:
• Хабы (Hubs): Содержат уникальные бизнес-ключи (например, customer_id, product_number). Они определяют основные бизнес-сущности.
• Линки (Links): Определяют связи между хабами. По сути, это таблицы связей "многие-ко-многим".
• Саттелиты (Satellites): Хранят описательные атрибуты хабов и линков, а также историю их изменений. Каждый саттелит содержит данные из одного источника.
• Когда использовать: В средах, где источники данных часто меняются, требования к данным постоянно эволюционируют, и важна полная отслеживаемость (аудируемость) данных. Отлично подходит для построения "сырого" слоя (Raw DWH).
• Преимущества:
• Гибкость и масштабируемость: Легко добавлять новые источники данных, не перестраивая существующую модель.
• Аудируемость: Структура позволяет отслеживать, откуда и когда поступили данные.
• Параллельная загрузка: Компоненты модели (хабы, линки, саттелиты) можно загружать независимо и параллельно.
• Недостатки:
• Сложность для конечных пользователей: Модель не предназначена для прямых запросов от бизнес-аналитиков. Требует создания витрин данных (в виде схемы-звезды) поверх Data Vault для аналитики.
• Большое количество таблиц: Модель генерирует много таблиц и соединений, что усложняет её понимание.

Как всё это связано с паттернами КХД?

Теперь сложим пазл:

• Подход Инмона (Inmon):
• Берет данные из OLTP-систем.
• Строит центральное хранилище (EDW) по принципам OLTP — в третьей нормальной форме (3НФ). Цель — создать единый, целостный источник правды без избыточности.
• Уже из этого нормализованного хранилища строятся OLAP-витрины (часто в виде схемы "звезда") для аналитиков.
• Подход Кимбалла (Kimball):
• Берет данные из OLTP-систем.
• Сразу строит OLAP-витрины (схема "звезда"). Для этого он намеренно ОТКАЗЫВАЕТСЯ от 3НФ и денормализует данные. Атрибуты (например, название категории товара) копируются прямо в таблицу измерений продуктов, чтобы избежать лишних JOIN'ов при запросе.
• Это осознанный компромисс: жертвуем эффективностью хранения ради скорости анализа.
• Data Vault:
• Это гибрид, который интересно использует нормализацию.
• Хабы (бизнес-ключи) и Линки (связи) очень похожи на нормализованную структуру. Здесь нет избыточности ключей и связей.
• А вот Саттелиты, хранящие атрибуты и их историю, концептуально близки к шестой нормальной форме (6НФ). Каждый саттелит хранит историю изменений атрибутов из одного источника, что позволяет детально отслеживать их во времени, как и предполагает 6НФ.
• Data Vault сам по себе не является OLAP-системой. Поверх него, как и в подходе Инмона, строят OLAP-витрины по Кимбаллу.

Итоговая схема:

1. Источник: OLTP-системы (базы данных приложений, CRM, ERP), спроектированные в 3НФ.
2. Трансформация (ETL/ELT): Инженер данных забирает данные и преобразует их.
3. Хранилище (КХД):
• Инмон: Сначала в 3НФ, потом в денормализованные витрины.
• Кимбалл: Сразу в денормализованные витрины (схема "звезда").
• Data Vault: В гибридную модель (Хабы/Линки/Саттелиты), которая похожа на смесь нормализации и 6НФ.
4. Цель: Предоставить данные для OLAP — быстрого и удобного анализа.

⚙️ПРАКТИКА проектирование хранилища

Давайте спроектируем хранилище по методологии Data Vault для реального и понятного бизнеса — интернет-магазина.

Сценарий

• Бизнес: Интернет-магазин "ТехноМир", продающий электронику.
• Источники данных:
1. CRM-система: Информация о клиентах.
2. Система обработки заказов (Sales DB): Данные о заказах и их составе.
3. Каталог товаров (PIM - Product Information Management): Информация о продуктах.
• Наша задача: Построить "сырой" слой хранилища (Raw Data Vault), который интегрирует данные из этих систем, сохраняя всю историю изменений.

Шаг 1: Идентификация бизнес-ключей

Сначала мы находим уникальные, стабильные идентификаторы, которые бизнес использует для отслеживания своих ключевых сущностей. Это "гвозди", на которых будет держаться вся модель.

• customer_id — Уникальный номер клиента в CRM.
• order_number — Уникальный номер заказа.
• product_sku — Артикул товара, уникальный идентификатор продукта.

Шаг 2: Проектирование Хабов (Hubs)

Хабы — это таблицы, хранящие только бизнес-ключи. Они являются якорями для наших бизнес-сущностей.

Шаг 3: Проектирование Линков (Links)

Линки — это таблицы, которые определяют связи между хабами. Они не хранят никаких атрибутов, только хэш-ключи хабов, которые они соединяют.

Шаг 4: Проектирование Сателлитов (Satellites)

Сателлиты — это таблицы, которые хранят описательные, изменяющиеся во времени атрибуты. Они "висят" на хабах или линках и отслеживают всю историю изменений.

Как это работает на практике?

Представим, что 25.07.2025 новый клиент C123 (Иван) делает заказ ORD987 на 2 смартфона SKU-XYZ.

1. Загрузка в Хабы:
• В HUB_CUSTOMER добавляется запись с customer_id = 'C123'.
• В HUB_ORDER добавляется запись с order_number = 'ORD987'.
• В HUB_PRODUCT добавляется запись с product_sku = 'SKU-XYZ' (если ее там еще нет).
2. Загрузка в Линки:
• В LNK_CUSTOMER_ORDER создается связь между хэшами клиента C123 и заказа ORD987.
• В LNK_ORDER_PRODUCT создается связь между хэшами заказа ORD987 и продукта SKU-XYZ.
3. Загрузка в Сателлиты:
• В SAT_CUSTOMER_DETAILS добавляется запись с именем "Иван" и его email, привязанная к хэшу клиента C123.
• В SAT_ORDER_DETAILS добавляется запись со статусом "Processing" и адресом доставки, привязанная к хэшу заказа ORD987.
• В SAT_LNK_ORDER_PRODUCT_DETAILS добавляется запись с quantity = 2, привязанная к хэшу связи заказа и товара.

Что если клиент изменит email? При следующей загрузке данных система вычислит новый HashDiff для атрибутов клиента. Так как он не совпадет со старым, в SAT_CUSTOMER_DETAILS будет добавлена новая строка с тем же H_CUSTOMER_HK, но новой LoadDate и новым email. Старая запись останется нетронутой. Мы сохранили историю!

Что дальше? (Слой витрин данных)

Эта структура идеальна для хранения и интеграции данных, но неудобна для прямого анализа. Поэтому следующим шагом инженер данных строит поверх этого "сырого" слоя витрину данных (Data Mart) в виде привычной схемы-звезды:

• FactSales (Таблица фактов): Собирается из линков и их сателлитов. Будет содержать H_CUSTOMER_HK, H_PRODUCT_HK, quantity, total_amount.
• DimCustomer (Измерение клиентов): Собирается из HUB_CUSTOMER и его сателлита SAT_CUSTOMER_DETAILS (берется последняя, актуальная версия данных).
• DimProduct, DimDate и т.д.

Теперь бизнес-аналитики могут легко делать свои OLAP-запросы к этой простой и понятной "звезде", а в основе у нас лежит надежное, аудируемое и гибкое хранилище Data Vault.