5 шагов проектирования DWH с подходом Data Vault: практический пример

Data Vault Проектирование пример, проектирование DWH пример, обучение архитектура данных примеры, построение корпоративного хранилища данных, Школа Больших Данных Учебный центр Коммерсант

Как построить хранилище данных с подходом Data Vault: пример проектирования схемы данных и разработка DDL-скрипта для Transformed-слоя DWH интернет-магазина.

Слоистая структура DWH и подход Data Vault

Корпоративное хранилище данных (DWH, Data Warehouse) часто бывает гетерогенным, т.к. организованным с помощью нескольких баз данных, связанных ETL-процессами. Согласно концепции слоистой архитектуры (LSA, Layered Scalable Architecture), например, Raw-слой с исходными данными, загруженными из разных источников, можно реализовать в Greenplum, как и Transformed-слой с преобразованными, т.е. консолидированными записями. А для аналитического слоя с витринами данных (Data Mart) со структурированными и денормализованными данными для их тематического анализа, например, сводная информация о клиентах, продажах и пр., отлично подходят колоночные СУБД типа Clickhouse или вообще конечные BI-инструменты.

Послойная структура DWH
Послойная структура DWH

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

Из всех подходов проектирования DWH именно методология Data Vault мне нравится больше всего. Она проще якорной модели (Anchor Modeling), но также обладает высокой гибкостью и лучше подается дополнению и расширению по сравнению с классическими звездными схемами по Кимбалу и Инмону. Согласно Data Vault, модель хранилища данных описывается следующими концепциями:

  • Хаб (Hub)– основной бизнес-объект, сущность домена, например, клиент, продукт, заказ и пр. Уникальным идентификатором хаб-таблицы является первичный ключ, созданный на основе MD5- или SHA-1-хэша от бизнес-ключа.
  • Ссылка или связь (Link)— отношение между хаб-таблицами. У link-таблицы нет собственных атрибутов, есть только системные метаданные, что рассмотрим позже, первичный ключ и внешние ключи связываемых хабов.
  • Спутник (Satellite)— таблица с атрибутами хаба. Помимо описания сущности хаба, каждая запись в спутнике маркируется составным первичным ключом, состоящим из внешнего ключа на хаб или ссылку и время загрузки записи. Именно за счет спутников достигается гибкость хранилища данных по методологии Data Vault: всегда можно расширить атрибутивный состав, не меняя исходную бизнес-сущность. Также в таблицах-спутниках можно хранить историю изменения контекста, добавляя новую запись при обновлении данных в системе-источнике.

Каждая таблица (хаб, ссылка и спутник) содержит системные метаданные:

  • load_timestamp – дата и время первоначальной загрузки сущности в хранилище;
  • record_source — источник исходных данных, т.е. название внешней системы, базы данных или файла, откуда загружена запись.
КХД, моделирование данных, реляционные таблицы DWH, Data Vault таблицы
Таблицы Data Vault: хабы, ссылки, спутники

Вспомнив, что такое Data Vault, далее рассмотрим последовательность применения этого подхода к проектированию Transformed-слоя DWH на примере интернет-магазина.

Практический пример проектирования схемы данных

Общая последовательность проектирования схемы для Transformed-слоя корпоративного хранилища данных по модели Data Vault будет выглядеть так:

  1. сначала надо определить бизнес-сущности, которые затем станут таблицами-хабами и связи между ними.
  2. далее необходимо определить источники данных, чтобы описать контекст хабов с помощью таблиц-спутников;
  3. затем следует определиться с ключами хабов, включая бизнес-ключ и первичный ключ;
  4. описать таблицы-сателлиты, разделяя разные контексты одного хаба по разным таблицам-спутникам;
  5. построить схему данных целиком и получить 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

(Система управления платежами)

·        Данные по платежу (Номер, Дата и время, Сумма, Статус, Отправитель, Получатель, Основание, Результат)

·        Данные по первичным документам (Номер, Дата, Тип, Операция)

Контекстная диаграмма DFD
Контекстная диаграмма DFD

Поскольку источники данных, т.е. внешние 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

Наконец, можно собрать ранее выполненные шаги воедино, построив графическую схему данных. У меня получилась такая модель.

Схема данных Transformed-слоя DWH по Data Vault
Схема данных Transformed-слоя DWH по Data Vault

Чтобы в дальнейшем работать с этой схемой данных в 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:

Я даю свое согласие на обработку персональных данных и соглашаюсь с политикой конфиденциальности.

Источники

  1. https://habr.com/ru/articles/786822/
  2. https://selectel.ru/blog/data-warehouse/
  3. https://www.databricks.com/blog/data-vault-best-practice-implementation-lakehouse
Контакты авторизированного учебного центра
«Школа Больших Данных»
Адрес:
127576, г. Москва, м. Алтуфьево, Илимская ул. 5 корпус 2, офис 319, БЦ «Бизнес-Депо»
Часы работы:
Понедельник - Пятница: 09.00 – 18.00
Остались вопросы?
Звоните нам +7 (495) 414-11-21 или отправьте сообщение через контактную форму. Также вы можете найти ответы на ваши вопросы в нашем сборнике часто задаваемых вопросов.
Оставьте сообщение, и мы перезвоним вам в течение рабочего дня
Я даю свое согласие на обработку персональных данных и соглашаюсь с политикой конфиденциальности.
Или напишите нам в соц.сетях
Поиск по сайту