Как построить хранилище данных с подходом Data Vault: пример проектирования схемы данных и разработка DDL-скрипта для Transformed-слоя DWH интернет-магазина.
Слоистая структура DWH и подход Data Vault
Корпоративное хранилище данных (DWH, Data Warehouse) часто бывает гетерогенным, т.к. организованным с помощью нескольких баз данных, связанных ETL-процессами. Согласно концепции слоистой архитектуры (LSA, Layered Scalable Architecture), например, Raw-слой с исходными данными, загруженными из разных источников, можно реализовать в Greenplum, как и Transformed-слой с преобразованными, т.е. консолидированными записями. А для аналитического слоя с витринами данных (Data Mart) со структурированными и денормализованными данными для их тематического анализа, например, сводная информация о клиентах, продажах и пр., отлично подходят колоночные СУБД типа Clickhouse или вообще конечные BI-инструменты.
С точки зрения проектирования архитектуры данных наибольший интерес представляет Transformed-слой, структуру которого можно спроектировать по-разному, используя популярные подходы к созданию корпоративного хранилища.
Из всех подходов проектирования DWH именно методология Data Vault мне нравится больше всего. Она проще якорной модели (Anchor Modeling), но также обладает высокой гибкостью и лучше подается дополнению и расширению по сравнению с классическими звездными схемами по Кимбалу и Инмону. Согласно Data Vault, модель хранилища данных описывается следующими концепциями:
- Хаб (Hub)– основной бизнес-объект, сущность домена, например, клиент, продукт, заказ и пр. Уникальным идентификатором хаб-таблицы является первичный ключ, созданный на основе MD5- или SHA-1-хэша от бизнес-ключа.
- Ссылка или связь (Link)— отношение между хаб-таблицами. У link-таблицы нет собственных атрибутов, есть только системные метаданные, что рассмотрим позже, первичный ключ и внешние ключи связываемых хабов.
- Спутник (Satellite)— таблица с атрибутами хаба. Помимо описания сущности хаба, каждая запись в спутнике маркируется составным первичным ключом, состоящим из внешнего ключа на хаб или ссылку и время загрузки записи. Именно за счет спутников достигается гибкость хранилища данных по методологии Data Vault: всегда можно расширить атрибутивный состав, не меняя исходную бизнес-сущность. Также в таблицах-спутниках можно хранить историю изменения контекста, добавляя новую запись при обновлении данных в системе-источнике.
Каждая таблица (хаб, ссылка и спутник) содержит системные метаданные:
- load_timestamp – дата и время первоначальной загрузки сущности в хранилище;
- record_source — источник исходных данных, т.е. название внешней системы, базы данных или файла, откуда загружена запись.
Вспомнив, что такое Data Vault, далее рассмотрим последовательность применения этого подхода к проектированию Transformed-слоя DWH на примере интернет-магазина.
Практический пример проектирования схемы данных
Общая последовательность проектирования схемы для Transformed-слоя корпоративного хранилища данных по модели Data Vault будет выглядеть так:
- сначала надо определить бизнес-сущности, которые затем станут таблицами-хабами и связи между ними.
- далее необходимо определить источники данных, чтобы описать контекст хабов с помощью таблиц-спутников;
- затем следует определиться с ключами хабов, включая бизнес-ключ и первичный ключ;
- описать таблицы-сателлиты, разделяя разные контексты одного хаба по разным таблицам-спутникам;
- построить схему данных целиком и получить DDL-скрипт для создания таблиц в базе данных.
Рассмотрим каждый шаг этой последовательности более подробно. Итак, сначала надо определить бизнес-сущности, которые затем станут таблицами-хабами, и связи между ними. Проще всего это сделать, построив концептуальную ER-диаграмму. Например, для магазина с разными складами, который продает и доставляет клиентам товары от различных поставщиков с разными скидками по программам лояльности, такая концептуальная модель может выглядеть так. Цветом выделены бизнес-сущности, с которыми я буду работать дальше в этом примере, чтобы сэкономить время и не превращать демонстрационный материал в огромный проект.
Далее необходимо определить источники данных, чтобы описать контекст хабов с помощью таблиц-спутников. Для этого я обычно использую контекстную DFD-диаграмму и ее описание в табличной форме:
| Источник данных | Какие данные есть в источнике | |
| Название | Расшифровка | |
| MMS | Marketing Management System
(Система управления скидками и маркетингом) |
· Название программы лояльности
· Даты старта и окончания · Товары, на которые распространяется скидка · Клиенты, к которым применима программа лояльности |
| OMS | Order Management System
(Система управления заказами) |
· Данные по заказу (Номер, Дата и время, Товары, Сумма, Комментарий, Покупатель)
· Операции по заказу (Изменения статуса) |
| WMS | Warehouse Management System
(Система управления складом) |
· Складская операция с товаром (Товар, Дата и время операции, Сотрудник)
· Местонахождение товара (Товар, Адрес на складе) |
| CMS | Customer Management System
(Система управления клиентами) |
· Данные по клиентам (Email, Телефон, ФИО, Адрес доставки, Предпочтения, Категория)
· Данные по клиентским обращениям (Дата, Тип, Сотрудник, Основание, Результат) |
| SCM | Supply Chain Management
(Система управления цепочками поставок) |
· Данные по поставщикам (Email, Телефон, ИНН, Адрес, Категория, Контактное лицо)
· Данные по товарам (Название, Единица измерения, Стоимость, Поставщик, Количество) |
| PMS | Payment Management System
(Система управления платежами) |
· Данные по платежу (Номер, Дата и время, Сумма, Статус, Отправитель, Получатель, Основание, Результат)
· Данные по первичным документам (Номер, Дата, Тип, Операция) |
Поскольку источники данных, т.е. внешние OLTP-базы, обозначенные на DFD-диаграмме, тоже имеют табличную структуру, необходимо детализировать, как записи из исходных систем будут загружены в Raw-слой DWH. Причем, схема данных в Raw-слое DWH будет отличаться от исходной схемы в транзакционной системе. Например, если из CRM-системы в Raw-слой DWH попадают данные по клиентам и по клиентским обращениям, это может быть всего 2 таблицы: crm_customers и crm_applications, хотя в исходной OLTP-системе данные сильно нормализованы и разнесены на гораздо большее число таблиц. В свою очередь, названия таблиц в Raw-слое будут использоваться в качестве значений для атрибута record_source в Transformed-слое хранилища данных. Пример проектирования Raw-слоя смотрите в новой статье.
После этого следует определиться с бизнес-ключами для хабов. На практике именно этот шаг чаще всего вызывает затруднения, поскольку выделить уникальный идентификатор доменной сущности не всегда получается с первого раза. Например, email и номер телефона клиента могут измениться, при этом клиент как бизнес-сущность остается тем же самым. Аналогично, идентифицировать человека по паспортным данным тоже не лучшая идея, поскольку они меняются. В этом случае кандидатом на уникальный бизнес-ключ может стать ИНН или СНИЛС, но эти данные есть только у государственных сервисов, и то не у всех. Для интернет-магазина придется придумать что-то другое в качестве бизнес-ключа сущности Клиент. В этом случае проще всего взять GUID (Globally Unique Identifier) или UUID (Universal Unique Identifier) — статический уникальный идентификатор, 128-битное целое число. Он обеспечивает почти нулевую вероятность коллизий, т.е. совпадений у разных объектов, поскольку результат деления 1 на (2 в степени 128) практически равен 0. GUID представляется в шестнадцатеричном виде как последовательность 4 частей, разделенных тире и содержащих вместе 128 бит, например, a1d4ce5d-2757-4699-948c-cfa72ba94f86. Результатом хэш-функции от бизнес-ключа, т.е. GUID/UUID, будет первичный ключ, используемый для связи хаба с другими таблицами. Чтобы повысить скорость поиска, можно использовать целочисленный тип данных (int) с автоинкрементом primary key.
Наконец, надо описать таблицы-сателлиты, разделяя разные контексты одного хаба по разным таблицам-спутникам. Поэтому с одним хабом может быть связано несколько таблиц-спутников. Особенно часто это встречается при интеграции нескольких микросервисов в рамках одной бизнес-задачи, когда одну и ту же бизнес-сущность разные сервисы описывают по-разному. Например, заказ, по которому пришла жалоба клиента в CRM-систему, и заказ в OMS-системе.
Помимо хаба, спутник может также описывать атрибуты связи, имея внешний ключ на таблицу-связь в составе своих столбцов. Чтобы обеспечить идемпотентность ETL-процессов, т.е. избежать дублирования данных при загрузке их в Transformed-слой DWH, первичным ключом для таблицы-саттелита будет комбинация из ссылки на запись в таблице-хаба и отметка времени загрузки load_timestamp. Для первоначального определения структуры сателлитов удобно использовать табличную форму, определив их набор атрибутов и исходную бизнес-сущность, т.е. хаб.
| Исходная таблица
(Хаб или Связь) |
Сателлит | Атрибуты | |||
| Название | Смысл | Название | Смысл | Тип данных | |
| Order | Sat_Order | Описание заказа | order_current_state | Текущее состояние | enum |
| order_timestamp | Дата и время создания заказа | timestamp | |||
| order_summa_wihout_discount | Сумма без скидки | double precision | |||
| order_discount_summa | Сумма скидки | double precision | |||
| order_ammount | Итоговая сумма | double precision | |||
Наконец, можно собрать ранее выполненные шаги воедино, построив графическую схему данных. У меня получилась такая модель.
Чтобы в дальнейшем работать с этой схемой данных в PostgreSQL, я получила из этой модели DDL-скрипт и изменила его, упаковав все эти таблицу в схему данных dwh_transformed:
CREATE SCHEMA IF NOT EXISTS dwh_transformed;
CREATE TABLE dwh_transformed.Sat_Provider(
hub_provider_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
provider_name VARCHAR(255) NOT NULL,
provider_address VARCHAR(255) NOT NULL,
provider_phone VARCHAR(255) NOT NULL,
provider_email VARCHAR(255) NOT NULL,
provider_INN VARCHAR(255) NOT NULL,
provider_bank_account VARCHAR(255) NOT NULL,
PRIMARY KEY(hub_provider_pk, load_timestamp)
);
CREATE TABLE dwh_transformed.Sat_Payment(
hub_payment_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
payment_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
payment_paysystem VARCHAR(255) NOT NULL,
payment_type INTEGER NOT NULL,
payment_account_from VARCHAR(255) NOT NULL,
payment_account_to VARCHAR(255) NOT NULL,
payment_ammount DOUBLE PRECISION NOT NULL,
payment_currency VARCHAR(255) NOT NULL,
payment_current_state VARCHAR(255) NOT NULL,
PRIMARY KEY(hub_payment_pk,load_timestamp)
);
CREATE TABLE dwh_transformed.Hub_Customer(
hub_customer_pk BIGINT NOT NULL,
customer_bk UUID NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(hub_customer_pk)
);
CREATE TABLE dwh_transformed.Sat_Customer(
hub_customer_pk BIGINT NOT NULL,
customer_name VARCHAR(255) NOT NULL,
customer_phone VARCHAR(255) NOT NULL,
customer_email VARCHAR(255) NOT NULL,
customer_birthday DATE NOT NULL,
customer_current_state VARCHAR(255) NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(hub_customer_pk,load_timestamp)
);
CREATE TABLE dwh_transformed.Sat_Order_Product(
link_order_product_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
quantity INTEGER NOT NULL,
discounted_price DOUBLE PRECISION NOT NULL,
PRIMARY KEY(link_order_product_pk,load_timestamp)
);
CREATE TABLE dwh_transformed.Sat_Order(
hub_order_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
order_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
order_current_state VARCHAR(255) NOT NULL,
order_summa_wihout_discount DOUBLE PRECISION NOT NULL,
order_discount_summa DOUBLE PRECISION NOT NULL,
order_ammount DOUBLE PRECISION NOT NULL,
PRIMARY KEY(hub_order_pk,load_timestamp)
);
CREATE TABLE dwh_transformed.Hub_Provider(
hub_provider_pk BIGINT NOT NULL,
provider_bk UUID NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(hub_provider_pk)
);
CREATE TABLE dwh_transformed.Link_Order_Customer(
link_order_customer_pk BIGINT NOT NULL,
hub_customer_pk BIGINT NOT NULL,
hub_order_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(link_order_customer_pk)
);
CREATE TABLE dwh_transformed.Link_Order_Payment(
link_order_payment_pk BIGINT NOT NULL,
hub_order_pk BIGINT NOT NULL,
hub_payment_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(link_order_payment_pk)
);
CREATE TABLE dwh_transformed.Hub_Order(
hub_order_pk BIGINT NOT NULL,
order_bk UUID NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(hub_order_pk)
);
CREATE TABLE dwh_transformed.Hub_Payment(
hub_payment_pk BIGINT NOT NULL,
payment_bk UUID NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(hub_payment_pk)
);
CREATE TABLE dwh_transformed.Link_Order_Product(
link_order_product_pk BIGINT NOT NULL,
hub_order_pk BIGINT NOT NULL,
hub_product_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(link_order_product_pk)
);
CREATE TABLE dwh_transformed.Hub_Product(
hub_product_pk BIGINT NOT NULL,
product_bk UUID NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
PRIMARY KEY(hub_product_pk)
);
CREATE TABLE dwh_transformed.Link_Product_Provider(
link_product_provider_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
hub_product_pk BIGINT NOT NULL,
hub_provider_pk BIGINT NOT NULL,
PRIMARY KEY(link_product_provider_pk)
);
CREATE TABLE dwh_transformed.Sat_Product(
hub_product_pk BIGINT NOT NULL,
load_timestamp TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
record_source VARCHAR(255) NOT NULL,
product_name VARCHAR(255) NOT NULL,
product_category jsonb NOT NULL,
product_measure_unit VARCHAR(255) NOT NULL,
product_price DOUBLE PRECISION NOT NULL,
product_release_date DATE NOT NULL,
product_sell_by_date DATE NOT NULL,
PRIMARY KEY(hub_product_pk,load_timestamp)
);
ALTER TABLE
dwh_transformed.Link_Order_Payment ADD CONSTRAINT link_order_payment_hub_payment_pk_foreign FOREIGN KEY(hub_payment_pk) REFERENCES dwh_transformed.Hub_Payment(hub_payment_pk);
ALTER TABLE
dwh_transformed.Link_Product_Provider ADD CONSTRAINT link_product_provider_hub_provider_pk_foreign FOREIGN KEY(hub_provider_pk) REFERENCES dwh_transformed.Hub_Provider(hub_provider_pk);
ALTER TABLE
dwh_transformed.Link_Order_Customer ADD CONSTRAINT link_order_customer_hub_order_pk_foreign FOREIGN KEY(hub_order_pk) REFERENCES dwh_transformed.Hub_Order(hub_order_pk);
ALTER TABLE
dwh_transformed.Link_Order_Product ADD CONSTRAINT link_order_product_hub_order_pk_foreign FOREIGN KEY(hub_order_pk) REFERENCES dwh_transformed.Hub_Order(hub_order_pk);
ALTER TABLE
dwh_transformed.Link_Order_Product ADD CONSTRAINT link_order_product_hub_product_pk_foreign FOREIGN KEY(hub_product_pk) REFERENCES dwh_transformed.Hub_Product(hub_product_pk);
ALTER TABLE
dwh_transformed.Link_Order_Customer ADD CONSTRAINT link_order_customer_hub_customer_pk_foreign FOREIGN KEY(hub_customer_pk) REFERENCES dwh_transformed.Hub_Customer(hub_customer_pk);
ALTER TABLE
dwh_transformed.Link_Order_Payment ADD CONSTRAINT link_order_payment_hub_order_pk_foreign FOREIGN KEY(hub_order_pk) REFERENCES dwh_transformed.Hub_Order(hub_order_pk);
ALTER TABLE
dwh_transformed.Sat_Order_Product ADD CONSTRAINT link_order_product_link_order_product_pk_foreign FOREIGN KEY(link_order_product_pk) REFERENCES dwh_transformed.Link_Order_Product(link_order_product_pk);
ALTER TABLE
dwh_transformed.Sat_Provider ADD CONSTRAINT sat_provider_hub_provider_pk_foreign FOREIGN KEY(hub_provider_pk) REFERENCES dwh_transformed.Hub_Provider(hub_provider_pk);
ALTER TABLE
dwh_transformed.Sat_Customer ADD CONSTRAINT sat_customer_hub_customer_pk_foreign FOREIGN KEY(hub_customer_pk) REFERENCES dwh_transformed.Hub_Customer(hub_customer_pk);
ALTER TABLE
dwh_transformed.Sat_Product ADD CONSTRAINT sat_product_hub_product_pk_foreign FOREIGN KEY(hub_product_pk) REFERENCES dwh_transformed.Hub_Product(hub_product_pk);
ALTER TABLE
dwh_transformed.Sat_Payment ADD CONSTRAINT sat_payment_hub_payment_pk_foreign FOREIGN KEY(hub_payment_pk) REFERENCES dwh_transformed.Hub_Payment(hub_payment_pk);
ALTER TABLE
dwh_transformed.Sat_Order ADD CONSTRAINT sat_order_hub_order_pk_foreign FOREIGN KEY(hub_order_pk) REFERENCES dwh_transformed.Hub_Order(hub_order_pk);
ALTER TABLE
dwh_transformed.Link_Product_Provider ADD CONSTRAINT link_product_provider_hub_product_pk_foreign FOREIGN KEY(hub_product_pk) REFERENCES dwh_transformed.Hub_Product(hub_product_pk);
В следующей статье я покажу, как работать с этим хранилищем данных, наполнив его с помощью Python-скриптов, запускаемых как ETL-задания в Apache AirFlow, а также построю аналитический дэшборд в Yandex.Datalens.
Узнайте больше про архитектуры современных хранилищ данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, дата-инженеров, администраторов, аналитиков Big Data в Москве и специалистов по Data Science:
Источники
