Проектируем архитектуру семантического слоя

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

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

Вечный маятник: нормализация против денормализации

Начнём с классики, которая вызывает больше споров, чем выбор стратегии загрузки данных.

Нормализация — это когда вы храните данные в идеальном порядке, разделяя их на сущности, чтобы избежать аномалий. Это прекрасно для транзакционных систем и нижних слоёв хранилища данных (DWH). Места мало, дублирования нет, целостность соблюдена.

Но бизнесу и BI-инструментам на вашу идеальную аккуратность глубоко наплевать. Им нужно всё и сразу. Денормализация — это создание широких, плоских таблиц, где данные уже соединены и готовы к анализу. Запросы летают, JOIN-ов нет, бизнес счастлив.

Ошибка большинства дата-инженеров — попытка денормализовать всё на этапе ETL и хранить в хранилище только гигантские плоские таблицы. Это как сделать татуировку на первой встрече: пока отношения свежие — вроде круто, но когда потребуется что-то поменять, переделать будет очень больно. Как только вам нужно будет добавить новый атрибут, придётся перестраивать всю таблицу.

Правильная архитектура семантического слоя предполагает разделение труда. В самом хранилище (DWH) держите данные в нормализованном виде (третья нормальная форма или Data Vault). А семантический слой должен выступать в роли денормализатора. Он берёт аккуратные нормализованные таблицы и на лету или через материализованные представления собирает из них удобные витрины для бизнеса.

Вот как это выглядит схематично:

---
config:
  flowchart:
    subGraphTitleMargin:
      top: 10
      bottom: 20

---
flowchart LR
    A --> E
    B --> E
    C --> E
    D --> E
    subgraph DWH["Хранилище (DWH)\nнормализованные данные"]

        A[("dim_customer")]
        B[("dim_product")]
        C[("dim_store")]
        D[("fct_sales")]
    end

    subgraph SL["Семантический слой"]
        E["Витрина\n«Продажи по клиентам»"]
        F["Витрина\n«Товарный запас»"]
    end

    subgraph BI["BI-инструменты"]
        G["Tableau / Superset"]
    end


    B --> F
    C --> F
    D --> F
    E --> G
    F --> G

Семантический слой скрывает сложность JOIN-ов от конечного пользователя. Аналитик в Tableau или Superset не должен знать, что для получения имени клиента нужно сделать три JOIN-а. Он просто тянет поле «Имя клиента», а семантический слой делает всю работу под капотом.

В коде семантического слоя (например, на YAML для dbt Semantic Layer или Cube) это выглядит так:

# Пример декларации меры в семантическом слоеmeasures:  - name: total_revenue    sql: amount    type: sum    description: "Сумма подтверждённых продаж без учёта возвратов и НДС" dimensions:  - name: customer_name    sql: "{CUSTOMER}.full_name"    type: string    description: "Полное имя клиента"

Пользователь видит только total_revenue и customer_name, а семантический слой сам подставляет нужные JOIN-ы к таблицам fct_sales и dim_customer.

Медленно меняющиеся измерения (SCD): как не потерять историю

Теперь перейдём к самому важному моменту — работе с историей. Данные в бизнесе меняются. Клиент переехал, менеджер уволился, товар сменил категорию. Если вы не продумаете Slowly Changing Dimensions (SCD), ваши отчёты будут содержать ошибки.

В семантическом слое нужно чётко определить, как мы обрабатываем эти изменения, и, что важнее, как мы это объясняем пользователю.

SCD Тип 0: Неизменяемый атрибут

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

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

Как подавать в семантическом слое: как обычное поле только для чтения. Никакой магии — пользователь просто видит значение, которое было зафиксировано в момент записи.

-- SCD Type 0: значение устанавливается один разINSERT INTO dim_customer (customer_id, full_name, date_of_birth, created_at)VALUES (42, 'Иванов Иван', '1990-05-15', CURRENT_DATE);-- date_of_birth больше никогда не изменится

SCD Тип 1: Перезапись

Вы просто перезаписываете старое значение новым. Клиент жил в Москве, переехал в Санкт-Петербург — в базе он теперь всегда из Санкт-Петербурга.

Когда использовать: когда история не важна. Например, исправление опечатки в названии города.

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

-- SCD Type 1: простая перезаписьUPDATE dim_customerSET city = 'Санкт-Петербург'WHERE customer_id = 42;

SCD Тип 2: Добавление новой строки

Вы не меняете старую запись, а добавляете новую с датами действия (valid_from, valid_to) и флагом актуальности (is_current).

Когда использовать: когда история критична. Если менеджер продал товар в январе, а в феврале перешёл в другой отдел, вы обязаны помнить, что в январе этот товар продал именно он, а не его преемник.

-- SCD Type 2: новая строка с периодом действияINSERT INTO dim_manager (manager_id, full_name, department, valid_from, valid_to, is_current)VALUES (7, 'Иванов Иван', 'Продажи', '2024-02-01', '9999-12-31', true); UPDATE dim_managerSET valid_to = '2024-01-31', is_current = falseWHERE manager_id = 7 AND is_current = true;

Как подавать в семантическом слое: никогда не заставляйте бизнес-пользователя писать фильтры по is_current = true или valid_to = '9999-12-31'. Семантический слой должен создавать два отдельных измерения из одной физической таблицы:

dimensions:  - name: manager_current    sql: "{dim_manager}.full_name"    type: string    description: "Текущий менеджер (актуальное назначение)"    filters:      - sql: "{dim_manager}.is_current = true"   - name: manager_historical    sql: "{dim_manager}.full_name"    type: string    description: "Менеджер на дату факта (исторический срез)"

Пользователь выбирает концепцию — «Текущий менеджер» или «Менеджер на дату продажи», — а не копается в технических флагах.

SCD Тип 3: Добавление новой колонки

Вы добавляете новую колонку, чтобы хранить предыдущее значение. Например, current_salary и previous_salary.

Когда использовать: когда вам нужно сравнивать только текущее и непосредственно предыдущее состояние, и не более.

Как подавать в семантическом слое: создавайте вычисляемые поля. Например, метрику «Рост зарплаты», которая просто берёт разницу между двумя колонками. Скройте физическую реализацию от пользователя.

measures:  - name: salary_growth    sql: current_salary - previous_salary    type: number    description: "Абсолютный рост зарплаты относительно предыдущего периода"

SCD Тип 4: Отдельная таблица истории

Актуальные (текущие) записи хранятся в основной таблице, а устаревшие версии перемещаются в отдельную историческую таблицу. Основная таблица остаётся лёгкой и быстрой, историческая — полной и детальной.

Когда использовать: когда в таблице много часто меняющихся атрибутов, и основная таблица разрастается так, что начинают страдать производительность запросов. Тип 4 — это оптимизационный компромисс: жертвуем простотой запросов ради скорости.

Как подавать в семантическом слое: объединяйте обе таблицы через представление (view) или создавайте два отдельных набора измерений — «текущее состояние» из основной таблицы и «полная история» из объединённого представления.

-- SCD Type 4: основная таблица только с актуальными записямиCREATE TABLE dim_manager_current (    manager_id INT PRIMARY KEY,    full_name VARCHAR(100),    department VARCHAR(50),    valid_from DATE); -- отдельная таблица для истории измененийCREATE TABLE dim_manager_history (    manager_id INT,    full_name VARCHAR(100),    department VARCHAR(50),    valid_from DATE,    valid_to DATE,    changed_at TIMESTAMP); -- представление для полной картиныCREATE VIEW dim_manager_full ASSELECT  manager_id,  full_name,  department,  valid_from,  NULL AS valid_to,  TRUE AS is_currentFROM dim_manager_current UNION ALL SELECT  manager_id,  full_name,  department,  valid_from,  valid_to,  FALSE AS is_currentFROM dim_manager_history;

Как выбрать стратегию SCD

У каждого типа SCD своя зона применения. Вот простая таблица для принятия решения:

Главный совет: не усложняйте. Тип 0 — для неизменяемых атрибутов, Тип 1 — для всего остального по умолчанию. Переходите на Тип 2 только когда появляется реальное требование хранить историю. Тип 3 и Тип 4 — нишевые решения для специфических сценариев.

Гранулярность данных: не смешивайте разнородное

Гранулярность (granularity) и зерно (grain) — это два связанных, но разных понятия. Давайте разберём их на примере.

Зерно — это декларация того, что представляет собой одна строка в таблице. Это ответ на вопрос: «О чём эта строка?». Зерно всегда формулируется как утверждение:

• «Одна строка = один товар в одном магазине за один день»
• «Одна строка = один клиент»
• «Одна строка = одна отгрузка»

Гранулярность — это степень детализации, которая получается из комбинации измерений, образующих зерно. Чем больше измерений в зерне, тем выше гранулярность (детализация). Чем меньше — тем ниже гранулярность (данные более агрегированы).

Зерно — это фундамент. Если вы ошибётесь с зерном, вся ваша модель рухнет, как карточный домик на ветру.

Самая частая ошибка — попытка положить в одну фактическую таблицу данные, которые изначально имеют разную степень детализации. Рассмотрим на примере.

У вас есть два типа данных:

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

Зерно у этих данных разное:

Если вы сложите их в одну таблицу, у неё образуется единое зерно — наиболее детализированное из всех: товар × магазин × день. Но планы такой детализации не имеют, поэтому их значения продублируются на каждый товар. Допустим, в магазине «А» за январь продаётся 10 товаров. План на январь для магазина «А» — 1 000 000 ₽. В одной таблице план придётся повторить 10 раз — по разу на каждый товар:

Теперь, если аналитик попросит SUM(plan_amount), он получит 10 000 000 вместо реальных 1 000 000, потому что план продублирован на каждый товар. Данные начинают врать.

Правильное решение: хранить данные в отдельных таблицах, каждая со своим зерном:

-- Таблица продаж: зерно = товар × магазин × деньCREATE TABLE fct_daily_sales (    product_id INT,    store_id INT,    sale_date DATE,    sales_amount DECIMAL(10,2),    PRIMARY KEY (product_id, store_id, sale_date)); -- Таблица планов: зерно = магазин × месяцCREATE TABLE fct_monthly_plans (    store_id INT,    plan_month DATE,  -- первое число месяца    plan_amount DECIMAL(10,2),    PRIMARY KEY (store_id, plan_month));

А как же аналитик, который хочет в одном отчёте видеть и продажи, и план? Здесь в дело вступает семантический слой. Он не требует сводить всё в одну таблицу физически. Вместо этого он позволяет пользователю строить отчёт, где продажи и план подтягиваются из разных таблиц, а семантический слой сам заботится о корректной агрегации:

measures:  - name: total_sales    sql: sales_amount    type: sum    source: fct_daily_sales  # ← из своей таблицы   - name: total_plan    sql: plan_amount    type: sum    source: fct_monthly_plans  # ← из своей таблицы

BI-инструмент (Tableau, Superset) получит две независимые меры. При построении отчёта семантический слой сгруппирует продажи по магазинам и месяцам, а план просто подставит как есть — без дублирования. Пользователь видит единый дашборд, хотя физически данные лежат в разных таблицах с разным зерном.

Правило железное: данные с разной степенью детализации — в разные таблицы. Если у вас есть продажи с детализацией до товара и планы с детализацией до магазина, не пытайтесь сложить их в одну таблицу — у неё всё равно будет одно зерно (самое детализированное), и менее детальные данные продублируются. Создавайте отдельные таблицы, а семантический слой сам свяжет их на уровне метрик и измерений, не заставляя вас жертвовать целостностью данных.

Именование и документация: язык, на котором говорят деньги

Вы можете построить идеальную архитектурную модель, но если ваши поля называются col_1, usr_sts_v2_final и sum_amt_net_gross, вашим семантическим слоем просто никто не будет пользоваться.

Семантический слой — это переводчик с языка баз данных на язык бизнеса. И этот перевод должен быть безупречным.

1. Используйте бизнес-термины. Не user_id, а ID клиента. Не rev, а Выручка.
2. Добавляйте описания. В каждом современном инструменте (Cube, dbt Semantic Layer, LookML) есть поля для описания метрик и измерений. Пишите там не «сумма продаж», а «Сумма подтверждённых продаж без учёта возвратов и НДС».
3. Договоритесь о глоссарии. Если отдел маркетинга считает выручку по дате оплаты, а отдел продаж — по дате отгрузки, ваш семантический слой должен явно разделять эти метрики. Назовите их Выручка по оплате и Выручка по отгрузке. Не пытайтесь сделать «единую версию правды» там, где бизнес сам не может договориться. Лучше две честные метрики, чем одна лживая. Главное здесь, всё же пытаться договориться, иначе рискуете получить 10 разных версий одной метрики "Выручка".

Пример плохого и хорошего нейминга в семантическом слое:

# ❌ Плохо: технические названияmeasures:  - name: sum_amt_net_gross    sql: amount    type: sum # ✅ Хорошо: бизнес-термины с описаниемmeasures:  - name: total_revenue    sql: amount    type: sum    description: "Сумма подтверждённых продаж без учёта возвратов и НДС"    label: "Выручка"

Резюме: как не дать модели умереть через месяц

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

flowchart TD
    A["Хранилище (DWH)<br/>3NF / Data Vault"] --> B["Семантический слой<br/>денормализация, SCD,<br/>бизнес-термины"]
    B --> C["BI-инструменты<br/>Tableau / Superset"]
    B --> D["Аналитики<br/>самостоятельные отчёты"]

Держите хранилище в нормализованном виде, денормализуйте на уровне семантики. Чётко разделяйте обработку истории (SCD) и прячьте техническую сложность от пользователя. Следите за зерном данных. И говорите с бизнесом на одном языке, даже если этот язык кажется вам нелогичным.

В конце концов, хорошая модель данных — это как хорошо спроектированная система. Она требует терпения, чётких границ, понимания прошлого и умения адаптироваться к новым требованиям. А если вы всё сделаете правильно, то в один прекрасный день бизнес перестанет дёргать вас по мелочам и начнёт строить отчёты самостоятельно.