Модуль 1. Бизнес-метрики и семантический слой витрин для ClickHouse

Фундамент, артефакты, календарь/валюты/единицы, первые реализации в CH

Зачем вообще семантический слой

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

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

В нашей схеме это выглядит так:
CORE (источник правды) → MARTS/витрины на ClickHouse → SEMANTIC (единые представления/метрики) → BI-дашборды.
Аналитик работает с SEMANTIC (готовыми представлениями), а не с сырыми таблицами.

Из чего состоит семантический слой (по-человечески)

Паспорт метрики — короткий документ (обычно в wiki), где написано:
- как именно считается метрика (формула, что включаем/исключаем);
- на каком календаре живём (обычный/финансовый 4-5-4; часовой пояс);
- в какой валюте и по какому правилу (на дату операции или на дату отчёта);
- «зерно» (детализация: день × магазин × категория и т. п.);
- окно допустимых поздних корректировок (например, «14 дней дозаливаем»);
- кто бизнес-владелец и кто техвладелец;
- минимальные проверки качества (например, «расхождение с источником ≤ 0,2%»).
Справочники и «нормализации»:
Календарь (обычный и/или финансовый 4-5-4) — чтобы «неделя к неделе» считалась одинаково.
Валюты — согласованная базовая валюта и правило пересчёта.
Статусы — маппинг «сырых» статусов («paid», «captured», «refund» и т. п.) к каноническим, которыми пользуемся в отчётах.
в них уже «вшиты» формулы метрик, календарь, валюты и статусы;
именно к ним подключается BI, чтобы все видели одно и то же.
Единые представления (VIEW) — «окна» в витрины для BI:

Как выглядят витрины для аналитика

Широкая таблица («wide») — одна строка = один факт (например, чек-позиция) + сразу нужные атрибуты (магазин, товар, бренд и т. д.). Удобно для быстрых срезов без джойнов.
«Звезда» — факты отдельно, справочники отдельно. Гибче для сложных разрезов, но запросы тяжелее.

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

Самые частые метрики — как мы их трактуем

Net Sales (чистые продажи) — оплаченные продажи минус возвраты/отмены. Важно заранее договориться, какие статусы считаем «оплаченными», а какие — нет.
GM (валовая прибыль) = Net Sales минус себестоимость; GM% = GM / Net Sales. Нельзя усреднять проценты «по магазину» — сначала сложите числители/знаменатели, потом делите.
AOV (средний чек) = выручка / количество заказов.
DAU/WAU/MAU — уникальные активные пользователи за день/неделю/месяц (что такое «активный» — важно зафиксировать).
Конверсия — доля пользователей/сессий, дошедших до целевого действия (зафиксируйте, что именно считаем «действием»).
Retention/Churn — удержание/отток по когортам (когорта — пользователи, впервые что-то сделавшие в один период).
LTV — накопленная выручка от когорты/пользователя по периодам с момента первой покупки.
Перцентили (p95 и т. п.) — «95% событий быстрее X мс»; для сумм чека: «90% чеков меньше Y». Это не среднее: помогает видеть «хвосты» распределения.
Top-k — топ-10 товаров/каналов по частоте или сумме.

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

Время — главный источник путаницы

Календари. Есть обычный (месяцы, кварталы) и финансовый (например, 4-5-4 в ритейле). Сравнивать «неделя к неделе» надо в одном и том же календаре, иначе цифры «не сходятся».
Скользящие окна (7/28/90 дней). Вам показывают суммы/средние за «последние N дней» — важно, чтобы в ряду не было «дыр»; для этого витрина добавляет «нулевые» дни, где не было событий.
MTD/YTD — «с начала месяца/года». Всегда уточняйте: календарный или финансовый год.
YoY/WoW/MoM — сравниваем эквивалентные периоды (например, 12-ю финансовую неделю этого года с 12-й прошлогодней), а не просто «минус 52 недели».

Валюта и единицы — за что отвечает семантический слой

Валюта. Часто считаем в двух вариантах:
1. в базовой валюте на дату операции (для управленки и сверки с кассой);
2. «на дату отчёта» (для сравнения между датами, когда важнее текущий курс).
  Эти две логики не смешиваем: если нужна другая — даём второе представление.
Единицы измерения. Продукты могут продаваться в штуках/кг/л. Семантический слой умеет приводить к базовой единице и чётко это отмечает.

Качество и согласование (что проверяется регулярно)

Баланс с источником: сумма в отчёте за вчера отличается от «золотой таблицы» не более чем на, скажем, 0,2%.
Отсутствие дублей по ключевым полям (зерну).
Плотность данных: нет «внезапных пустых дней» там, где они невозможны.
Инварианты: GM не может быть больше Net Sales; NRR не отрицательный и т. п.
Статусы: все неизвестные статусы попадают в отчёт «на разбор», а не в агрегаты.

Аналитик видит эти проверки в виде «сигналов здоровья»: «свежесть витрины», «расхождение с источником», «новые статусы».

Версии метрик: v1 → v2 без боли

Метрики живут: правила меняются. Чтобы не ломать историю:

создаём новую версию (v2) с описанием, что поменяли;
какое-то время держим две версии параллельно (чтобы BI/бизнес сравнили и согласовали);
затем «основная» вьюха переключается на v2; v1 убирается позже.

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

Доступ и безопасность (в двух словах)

BI ходит только в представления (а не во внутренние таблицы) — там уже маскированы PII и учтены ограничения доступа (например, «каждый регион видит только себя»).
Политики доступа настроены так, чтобы фильтры накладывались автоматически; задача аналитика — не «ломиться» в сырые таблицы.

Как аналитик работает с этим слоем на практике

Открываете каталог метрик — читается паспорт (что это, где берётся, какие ограничения).
В BI подключаетесь к готовым представлениям (они называются в духе vw_*).
Строите отчёты, используя одни и те же поля/метрики, что и коллеги.
Если цифры «не сходятся» — идёте по маршруту: «посмотреть свежесть → проверить, не поменялась ли версия → посмотреть отчёт о расхождениях с источником → создать задачу владельцу метрики».

Риски, которые стоит помнить (и как их снимать)

Разные календари → «неделя к неделе» не бьётся.
Что делать: всегда указывать календарь метрики, не смешивать.
Валютные сюрпризы → суммы «пляшут».
Что делать: уточнять правило пересчёта (на дату операции или отчёта), смотреть соответствующую вьюху.
Дубли/опоздавшие события → вчерашняя цифра сегодня другая.
Что делать: знать «окно корректировок» (например, последние 14 дней), понимать, что витрина дозаливает события.
«Среднее среднего» по процентам (например, GM%) → искажения.
Что делать: всегда агрегировать через «числитель/знаменатель», а не «среднее по группам».
«Своя формула в каждом отчёте» → хаос.
Что делать: использовать только метрики из семантических представлений, не заводить «локальные» вычисления в BI.

Подробнее обо всем:

Зачем семантический слой, если есть витрины

Слой витрин (marts) отвечает за быструю выдачу данных под BI/аналитику. Но даже идеальная витрина без семантического слоя обречена на расхождения: разные команды посчитают Gross Sales, Net Sales, GM% по-разному, применят разные статусы/фильтры/курсы валют. Семантический слой — это:

Единые определения метрик и правил фильтрации данных.
Отделение бизнес-смыслов от физической реализации таблиц.
Контракты (data contracts) между CORE → MARTS → BI: что считать, как, и кто отвечает.
Миграции смысла (версионирование метрик) без ломки схем.

В ClickHouse семантический слой удобно реализовать комбинацией:

VIEW (логика метрик, единые фильтры/исключения).
Materialized/логические витрины для дорогих вычислений (AggregatingMergeTree).
Dictionary (внешние словари) для справочников валют/единиц/маппингов.
Паспорта метрик (артефакты вне БД) + автотесты DQ.

Где жить семантике: в CH или вне (dbt/metrics-store/BI)

Нет единственного правильного ответа. Практично:

В ClickHouse держать базовую семантику (VIEW с формулами, фильтрами, RLS, маскированием).
Снаружи (в репозитории) держать паспорт метрики, автотесты, CI/CD и миграции (в духе dbt, но это не обязательно dbt).
В BI — только тонкую презентационную логику (формат, подписи). Не дублировать формулы.

Правило: если формула метрики меняется — меняем VIEW/паспорт, а не 50 отчётов в BI.

Термины и неизменяемые правила

Факт — запись события/состояния (чек-позиция, транзакция, CDR).
Измерение — атрибуты для разрезов (магазин, товар, клиент, дата, канал).
Показатель (measure) — числовая величина (количество, сумма, продолжительность).
Метрика — бизнес-определённая агрегированная/расчётная величина (Net Sales, GM%, ARPU).
KPI — метрика с целевым коридором/порогом и периодическим мониторингом.

Аддитивность:

Аддитивные: SUM(amount), SUM(qty) — складываются по всем измерениям.
Полуаддитивные: остатки — складываются по измерениям, но не по времени (берём снимок на конец периода).
Неаддитивные: проценты, средние, коэффициенты (GM%) — агрегируются через исходные числители/знаменатели, а не «среднее средних».

Типы фактов:

Транзакционный (каждое событие): чек-позиции, клики, платежи.
Снапшот (срез на момент времени): остатки, баланс, активные подписки.
Накопительный (accumulating snapshot): жизненный цикл сущности (заказ: создан → оплачен → отгружен → доставлен).

Золотые правила:

Метрика = формула + фильтры + зерно + календарь + валюты/единицы.
Метрики версионируются (v1/v2) — изменения вносим контролируемо.
Любая агрегация поверх неаддитивной метрики — риск; агрегируйте через исходные величины (числитель/знаменатель).
Смена формулы — это breaking change для бизнес-отчётности; оформляйте как v2.

Паспорт метрики: живой контракт

Хранится в Git (YAML/Markdown), привязан к коду (VIEW/SQL). Пример сокращённого YAML:

id: NET_SALES
name: Чистые продажи
grain: [day, shop_id, category_id]
definition:
  formula: "SUM(amount_base) - SUM(returns_amount_base)"
  filters:
    - "status IN ('paid','captured')"
  currency: "BASE"
  calendar: "FISCAL_454"
  late_window_days: 14
materialization:
  view: "vw_net_sales_daily"
  base_table: "mart_sales_wide"
  aggregates: "agg_sales_daily_state"
dq:
  - "balance_vs_core <= 0.2%"
  - "no_duplicate_grain"
owners:
  business: "Head of Sales Finance"
  technical: "DWH Architect"
version:
  current: "v1"
  changes:
    - "2025-08-01: created v1"

Практика: при PR на изменение vw_net_sales_daily.sql CI проверяет, что в metrics/NET_SALES.yaml обновлена версия/лог изменений, и запускает сравнение агрегатов на окне последних N дней.

Календарь — первая опора семантики

Естественный vs финансовый календарь

Григорианский: календарные дни, месяцы, кварталы.
Финансовый 4-5-4 / 4-4-5 / 5-4-4: равные недели внутри квартала (ритейл).
Сдвиги начала недели (Mon/Sun), фискальный год (начинается не 1 января).

Нельзя смешивать календари без явной трансформации — это источник «небьющихся» недель/кварталов.

Реализация календаря в ClickHouse

Заведите отдельную таблицу d_calendar со всеми нужными колонками (годы, месяцы, финнедели, номер 4-5-4, кварталы, флаги выходных/праздников). Заполняется генератором (внешним скриптом) или SQL-функциями.

Пример (упрощённый):

CREATE TABLE d_calendar
(
  d Date,
  y UInt16,
  m UInt8,
  day_of_month UInt8,
  week_start Date,          -- начало недели
  week_of_year UInt16,
  qtr UInt8,
  is_weekend UInt8,
  fiscal_year UInt16,
  fiscal_week UInt16,       -- 4-5-4
  fiscal_month UInt8,
  is_month_end UInt8
)
ENGINE = MergeTree
ORDER BY d;
-- Пример присоединения календаря к фактам
CREATE VIEW vw_sales_with_calendar AS
SELECT
  f.*,
  c.y, c.m, c.qtr, c.week_of_year, c.fiscal_year, c.fiscal_week, c.fiscal_month
FROM mart_sales_wide f
LEFT JOIN d_calendar c ON c.d = toDate(f.tx_datetime);

Риски:

Неправильные правила 4-5-4 → «разъезды» отчётов на недели.
Разная локаль/часовой пояс → дата попадает в «не ту» неделю/день.
Митигация:
Фиксируйте единственный календарь для метрик в паспорте.
Для мульти-TZ — храните event_time_utc и event_date_local(tz); считайте на фиксированном tz.

Валюты и единицы измерения

Мультивалютность

В витринах почти всегда надо иметь:

Локальную сумму (в валюте операции).
Сумму в базовой валюте на момент транзакции.
Часто — пересчёт в отчётную валюту на дату отчёта (может отличаться от курса на момент транзакции).

Паттерн: фиксируйте в факте курс на момент события (snapshot) и считайте amount_base сразу при записи в mart_sales_wide. Для пересчёта «на дату отчёта» делайте отдельную витрину/VIEW с присоединением курса на дату отчёта.

Курсы через Dictionary

ClickHouse External Dictionaries позволяют держать курсы валют (ежедневные) и быстро подтягивать их по ключу currency, date. Источник — файл/HTTP/БД. Пример структуры источника:

-- Табличный источник курсов
CREATE TABLE d_fx_rates
(
  d Date,
  ccy FixedString(3),
  rate_to_base Decimal(12,6)
)
ENGINE = MergeTree
ORDER BY (ccy, d);

Пример словаря (для иллюстрации синтаксиса; фактически укажите ваш source):

CREATE DICTIONARY dict_fx
(
  ccy FixedString(3),
  d Date,
  rate_to_base Decimal(12,6)
)
PRIMARY KEY (ccy, d)
SOURCE(CLICKHOUSE(TABLE 'd_fx_rates'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

Использование:

-- Считаем сумму в базовой валюте по курсу на дату транзакции
SELECT
  tx_id,
  amount * dictGetDecimal64('dict_fx', 'rate_to_base', (currency, toDate(tx_datetime))) AS amount_base
FROM mart_sales_wide;

Риски:

Отсутствие курса на конкретный день (выходной/праздник).
Пересчёт «на дату отчёта» ломает историческую сопоставимость с денежными потоками.
Митигация:
В словаре держать правило backfill: «если нет курса — взять курс предыдущего дня».
В паспорте метрики фиксировать, какой тип пересчёта используется (на дату транзакции или отчёта).

Единицы измерения (шт/кг/л/…)

Храните в измерении SKU единицу измерения и коэффициенты перекладки (например, в базовую единицу). Для отчётов допускайте пересчёт в нужную единицу по справочнику:

CREATE TABLE d_uom
(
  uom_code LowCardinality(String),   -- 'PCS', 'KG', ...
  to_base_multiplier Float64         -- сколько базовых единиц в 1 uom_code
)
ENGINE = MergeTree
ORDER BY uom_code;
-- Вьюха нормализации в базовую единицу
CREATE VIEW vw_sales_in_base_uom AS
SELECT
  f.*,
  f.qty * dictGetFloat64('dict_uom', 'to_base_multiplier', (f.uom_code)) AS qty_base
FROM mart_sales_wide f;

Категории/статусы/маппинги: приведите хаос к одному набору

Частая боль — статусы заказов/транзакций: «paid/complete/captured», «done/success», «cancelled/refund/chargeback» и т. п. Несогласованные статусы рождают неконсистентные метрики.

Решение: таблица-маппинг (или dictionary) канонических статусов, которая используется везде (в витринах, VIEW, DQ, тестах).

CREATE TABLE d_status_map
(
  src_system LowCardinality(String),
  src_status LowCardinality(String),
  canonical_status LowCardinality(String)  -- 'PAID', 'REFUND', 'CANCELLED', ...
)
ENGINE = MergeTree
ORDER BY (src_system, src_status);
-- Вьюха нормализации статусов
CREATE VIEW vw_sales_canonical AS
SELECT
  f.*,
  coalesce(m.canonical_status, 'UNKNOWN') AS status_canon
FROM mart_sales_wide f
LEFT JOIN d_status_map m
  ON m.src_system = f.src_system
 AND m.src_status = f.status;

Риск: новые статусы появляются внезапно → уходят в «UNKNOWN», BI «молчит».
Митигация: алерты DQ на новые src_status, nightly-отчёт «неизвестные статусы → бизнес на маппинг».

Классификация метрик: через что агрегируем

Разделите метрики на группы — это помогает выбирать правильную материализацию.

Суммовые (аддитивные): выручка, количество.
- Материализация: SummingMergeTree (если нет переигрываний) или AggregatingMergeTree.
Процентные/доли: GM%, конверсия, success_rate.
Считать как числитель/знаменатель отдельно → затем делить.
Считать снапшотом на конец периода; не суммировать по времени.
Использовать uniq* state/merge (AggregatingMergeTree), а не COUNT(DISTINCT) в BI на лету.
quantile*State/…Merge.
Полуаддитивные по времени: остатки/балансы.
Уникальные: уникальные покупатели, пользователи.
Квантили/перцентили: p95 latency.

Пример агрегирования доли (success_rate):

-- Храним состояния числителя/знаменателя
CREATE TABLE agg_calls_minute_state
(
  ts_minute DateTime,
  cell_id UInt64,
  ok_state  AggregateFunction(sum, UInt64),
  all_state AggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(ts_minute)
ORDER BY (ts_minute, cell_id);
-- Чтение
SELECT
  ts_minute, cell_id,
  sumMerge(ok_state) / NULLIF(sumMerge(all_state),0) AS success_rate
FROM agg_calls_minute_state
GROUP BY ts_minute, cell_id;

Семантические VIEW: «метрика как код»

Базовая идея

Для каждой ключевой метрики делайте VIEW с формулой, фильтрами, привязкой к календарю/валютам/статусам. BI читает VIEW, а не «сырые» витрины. Это:

Устраняет дублирование формул в BI.
Даёт контроль версии: CREATE OR REPLACE VIEW или vw_net_sales_v2.
Позволяет RLS/маскирование встроить в слой VIEW.

Пример: Net Sales (день × магазин × категория)

Исходим из того, что мы:

Нормализовали статусы → status_canon.
Имеем amount_base (через словарь курсов).
Имеем календарь и нужные разрезы.

Материализация (устойчивые состояния):

-- 1) Агрегируем состояния (устойчиво)
CREATE TABLE agg_net_sales_daily_state
(
  day Date,
  shop_id UInt32,
  category_id UInt32,
  net_amount_state AggregateFunction(sum, Decimal(14,2)),
  qty_state        AggregateFunction(sum, Int64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id, category_id);
-- 2) Заполнение состояний (инкрементами/батчем)
INSERT INTO agg_net_sales_daily_state
SELECT
  toDate(tx_datetime) AS day,
  shop_id,
  category_id,
  sumState( if(status_canon = 'PAID', amount_base,
           if(status_canon IN ('REFUND','CANCELLED'), -amount_base, 0)) )  AS net_amount_state,
  sumState( if(status_canon = 'PAID', qty,
           if(status_canon IN ('REFUND','CANCELLED'), -qty, 0)) )          AS qty_state
FROM vw_sales_canonical   -- уже нормализованные статусы
GROUP BY day, shop_id, category_id;

VIEW для чтения:

CREATE OR REPLACE VIEW vw_net_sales_daily AS
SELECT
  s.day, s.shop_id, s.category_id,
  sumMerge(s.net_amount_state) AS net_sales_amount,
  sumMerge(s.qty_state)        AS net_sales_qty
FROM agg_net_sales_daily_state s
GROUP BY s.day, s.shop_id, s.category_id;

Риски:

Забыли …Merge → BI увидит бинарные состояния.
Изменили трактовку статусов — сломали ретро-данные.
Митигация:
Оберните SELECT …Merge в VIEW, BI читает только VIEW.
Меняйте статусы через v2-маппинг + side-by-side пересчёт окон.

Временные сравнения: YoY/WoW/MoM

Сравнения по периодам — часть семантики. Правило — сравнивать эквивалентные периоды одного календаря.

Пример: сравнить неделю к такой же неделе прошлого года (ритейл 4-5-4), на базе d_calendar:

-- d_calendar содержит fiscal_year/fiscal_week
WITH current AS (
  SELECT fiscal_year, fiscal_week
  FROM d_calendar
  WHERE d = today()
)
SELECT
  cur.fiscal_year, cur.fiscal_week,
  sumMerge(s.net_amount_state)                AS net_sales_cur,
  sumMerge(s_prev.net_amount_state)           AS net_sales_prev,
  net_sales_cur - net_sales_prev              AS delta,
  100.0 * delta / NULLIF(net_sales_prev, 0)   AS delta_pct
FROM current c
LEFT JOIN agg_net_sales_daily_state s
  ON (s.day BETWEEN date_sub( toDate(today()), INTERVAL 6 DAY ) AND toDate(today()))
LEFT JOIN agg_net_sales_daily_state s_prev
  ON (s_prev.day BETWEEN addYears(date_sub(toDate(today()), INTERVAL 6 DAY), -1)
                    AND addYears(toDate(today()), -1))
GROUP BY cur.fiscal_year, cur.fiscal_week;

Риск: календарь не совпадает → «косые» недели.
Митигация: используйте fiscal_week из d_calendar и делайте join по семантическим неделям, а не по «минус 52 недели».

Версионирование метрик и change management

Метрика «живет». Вчера GM% считали gross_margin / net_sales, сегодня — «исключить маркетинговые скидки». Это другая метрика.

Подход:

В YAML паспорта — version: v1/v2, effective_from, список изменений.
В ClickHouse:
- Опция 1: CREATE OR REPLACE VIEW vw_gm_percent → замена формулы (но отчёты «вчера» станут отличаться).
- Опция 2: новая вьюха vw_gm_percent_v2 и BI переключается с даты effective_from.
- Опция 3: добавить колонку metric_version или параметризованную вьюху (через два VIEW).

Риски:

«Переписали» формулу задним числом — бизнес увидел «другие» исторические цифры.
Митигация:
Делать v2 и фиксировать миграцию: когда и почему. Хранить «старую» вьюху до окончания переходного периода.

Согласование слоёв: CORE → MARTS → SEMANTIC → BI

Data contract между слоями

CORE → MARTS: зерно фактов, уникальность, статус-маппинг, валюты/курсы, календарь, окна корректировок.
MARTS → SEMANTIC (VIEW): определение метрик, агрегирование через состояния, фильтры, пост-правки.
SEMANTIC → BI: только чтение из VIEW, без дублирования формул; лимиты/фильтры по умолчанию.

Где хранить «истину»

Gold («истина») — нормализованные данные + контрольные модели (часто вне CH или в отдельном слое CH).
Marts — под запросы, могут содержать денормализацию и «отрефлексированные» бизнес-исключения.
Semantic VIEW — интерфейс для потребителей.

Антипаттерн: BI идёт в «сырые» таблицы, каждый «пилит» свою формулу.
Правильно: BI читает только VIEW/агрегаты, за которые отвечает DWH.

Кейс «Retail»: полный контур NetSales/GM/GM% (упрощённо)

Бизнес: хотим ежедневные Net Sales, GM, GM%, в разрезах магазин × категория, с корректной работой возвратов, мультивалюты и календаря 4-5-4.

Исходные условия

mart_sales_wide: tx_datetime, shop_id, sku_id, qty, amount, currency, status, src_system.
d_calendar: календарь c fiscal_year, fiscal_week, fiscal_month.
dict_fx: курсы валют.
d_status_map: маппинг в канонические статусы PAID/REFUND/CANCELLED.
d_sku: содержит category_id.
d_margin: таблица «себестоимость» для расчёта GM, зафиксированная на день (или sku_cost_snapshot(day, sku_id)).

Денормализация и нормализация

CREATE VIEW vw_sales_enriched AS
SELECT
  s.tx_datetime,
  toDate(s.tx_datetime) AS day,
  s.shop_id,
  sku.category_id,
  s.qty,
  s.amount,
  s.currency,
  coalesce(map.canonical_status, 'UNKNOWN') AS status_canon,
  s.amount * dictGetDecimal64('dict_fx', 'rate_to_base', (s.currency, toDate(s.tx_datetime))) AS amount_base
FROM mart_sales_wide s
LEFT JOIN d_status_map map ON map.src_system = s.src_system AND map.src_status = s.status
LEFT JOIN d_sku sku ON sku.sku_id = s.sku_id;

Состояния Net Sales и GM

CREATE TABLE agg_retail_daily_state
(
  day Date,
  shop_id UInt32,
  category_id UInt32,
  net_amount_state  AggregateFunction(sum, Decimal(14,2)),
  qty_state         AggregateFunction(sum, Int64),
  gm_amount_state   AggregateFunction(sum, Decimal(14,2))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id, category_id);
INSERT INTO agg_retail_daily_state
SELECT
  day,
  shop_id,
  category_id,
  sumState( if(status_canon='PAID', amount_base, if(status_canon IN ('REFUND','CANCELLED'), -amount_base, 0)) ) AS net_amount_state,
  sumState( if(status_canon='PAID', qty, if(status_canon IN ('REFUND','CANCELLED'), -qty, 0)) ) AS qty_state,
  sumState( if(status_canon='PAID',
                amount_base - (qty * cost.cost_per_unit_base),
                if(status_canon IN ('REFUND','CANCELLED'),
                   - (amount_base - (qty * cost.cost_per_unit_base)), 0)) ) AS gm_amount_state
FROM vw_sales_enriched s
LEFT JOIN d_cost_per_day cost
  ON cost.day = s.day AND cost.sku_id = s.sku_id  -- себестоимость зафиксирована на день
GROUP BY day, shop_id, category_id;

VIEW для чтения:

CREATE OR REPLACE VIEW vw_retail_daily AS
SELECT
  day, shop_id, category_id,
  sumMerge(net_amount_state) AS net_sales,
  sumMerge(qty_state)        AS qty,
  sumMerge(gm_amount_state)  AS gm,
  gm / NULLIF(net_sales,0)   AS gm_percent
FROM agg_retail_daily_state
GROUP BY day, shop_id, category_id;

Риски и митигация:

Себестоимость пересчиталась задним числом — GM меняется.
→ Митигация: вести d_cost_per_day как снэпшот на день, ретро-окно N дней пересчитывается ночным джобом; для глубокой истории — side-by-side.
Новые статусы → попали в UNKNOWN.
→ Митигация: nightly-алерт + оперативный маппинг.
Разные календари (операционная неделя ≠ отчётная).
→ Митигация: чётко фиксируйте календарь в паспорте; при необходимости делайте две метрики (operational vs fiscal) и два VIEW.

Антипаттерны семантического слоя

Считать проценты «средним средних» — искажение; всегда через числитель/знаменатель.
Держать логику исключительно в BI — формулы размножатся; меняйте в одном месте.
Смешивать календари — недели никогда не «сойдутся».
Игнорировать версии метрик — бизнес «теряет» историю; вводите v2.
Поддерживать Summing без дисциплины — удвоения после повторных заливок.
Не фиксировать курсы на момент транзакции — невозможно объяснить расхождения.

Мини-чек-лист перед стартом семантического слоя

У вас есть паспорт метрики (формула, фильтры, календарь, валюты, разрезы, DQ).
Календарь единый, таблица d_calendar готова.
Курсы валют и единицы — через словари/справочники, зафиксированы правила backfill.
Статусы/категории нормализованы (d_status_map).
Выбрана материализация: AggregatingMergeTree/ReplacingMergeTree (без FINAL в чтении).
Семантическая логика в VIEW, BI читает только VIEW.
Настроены DQ-проверки и nightly-сверки с CORE.
Версионирование метрик описано (v1/v2), есть регламент перехода.

Семантика времени: окна, сравнения, скользящие метрики

Время — главный «разрез» витрин. Семантический слой обязан одинаково трактовать:

Скользящие окна: 7-дневный rolling average, 28-дневная выручка, 90-дневный MAU.
Кумулятивы (running totals) и период-к-периоду: MTD/YTD/QTD, WoW/MoM/YoY.
Фильтры календаря: рабочие/выходные, промо-периоды, «финансовые недели 4-5-4».

Скользящие окна (rolling, trailing)

ClickHouse поддерживает оконные функции OVER и классические агрегаты. Пример: 7-дневный rolling average по net_sales:

SELECT
  day,
  shop_id,
  sumMerge(net_amount_state) AS net_sales,
  avg(sumMerge(net_amount_state)) OVER (
    PARTITION BY shop_id
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS net_sales_roll7
FROM agg_retail_daily_state
GROUP BY day, shop_id
ORDER BY shop_id, day;

Риски и митигации

Разная плотность дат: пропуски ломают rolling. → Сгенерировать матрицу дат × shop_id (join на d_calendar, заменить NULL на 0).
Окна по «дырявому» календарю: праздничные переносы. → Работать по финансовому календарю (fiscal_day_seq) и считать окна по нему.

Кумулятивы и MTD/YTD/QTD

Кумулятив за месяц (MTD):

SELECT
  day, shop_id,
  sumMerge(net_amount_state) AS net_sales,
  sum(sumMerge(net_amount_state)) OVER (
    PARTITION BY shop_id, toYYYYMM(day)
    ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS net_sales_mtd
FROM agg_retail_daily_state
GROUP BY day, shop_id
ORDER BY shop_id, day;
YTD: группируем по toYear(day) (или по fiscal_year из d_calendar).

Сравнения период-к-периоду (WoW/MoM/YoY)

Критично сравнивать эквивалентные периоды (см. Часть 1): неделя-к-аналогичной неделе с тем же номером в 4-5-4. Вьюха для YoY по дням:

WITH base AS (
  SELECT day, shop_id, sumMerge(net_amount_state) AS net_sales
  FROM agg_retail_daily_state
  GROUP BY day, shop_id
)
SELECT
  b.day,
  b.shop_id,
  b.net_sales AS net_sales_cur,
  b_prev.net_sales AS net_sales_prev,
  b.net_sales - b_prev.net_sales AS delta,
  100.0 * (b.net_sales - b_prev.net_sales) / NULLIF(b_prev.net_sales, 0) AS delta_pct
FROM base b
LEFT JOIN base b_prev
  ON b_prev.day = addYears(b.day, -1)
 AND b_prev.shop_id = b.shop_id
ORDER BY b.shop_id, b.day;

Для 4-5-4 — джойним d_calendar и сопоставляем по финнеделям/годам.

Когорты, retention, churn, LTV

Когорты фиксируют «момент рождения» сущности (первой покупки/подписки) и далее строят поведение по периодам +n. В ClickHouse удобно использовать окна, массивы, groupArray/arrayJoin.

Когорты по пользователям/клиентам

Шаг 1. Найти дату 1-го события (кохортную метку):

CREATE VIEW vw_first_purchase AS
SELECT
  customer_id,
  min(toDate(tx_datetime)) AS cohort_day
FROM mart_sales_wide
WHERE status IN ('paid','captured')
GROUP BY customer_id;

Шаг 2. Присвоить каждой транзакции «возраст периода» (offset в днях/неделях/месяцах):

CREATE VIEW vw_sales_with_cohort AS
SELECT
  s.customer_id,
  toDate(s.tx_datetime) AS day,
  fp.cohort_day,
  dateDiff('week', fp.cohort_day, toDate(s.tx_datetime)) AS cohort_week,
  s.amount_base
FROM mart_sales_wide s
JOIN vw_first_purchase fp USING (customer_id)
WHERE s.status IN ('paid','captured');

Retention/CRR (коэффициент удержания)

Retention по недельным когортам: доля клиентов, совершивших хотя бы одну покупку в неделе k.

-- Размер когорты
WITH cohort_size AS (
  SELECT cohort_day, countDistinct(customer_id) AS n
  FROM vw_first_purchase
  GROUP BY cohort_day
),
activity AS (
  SELECT cohort_day, cohort_week, countDistinct(customer_id) AS active
  FROM vw_sales_with_cohort
  GROUP BY cohort_day, cohort_week
)
SELECT
  a.cohort_day,
  a.cohort_week,
  a.active / NULLIF(c.n, 0) AS retention_rate
FROM activity a
JOIN cohort_size c USING (cohort_day)
ORDER BY a.cohort_day, a.cohort_week;

Риски

Дубликаты customer_id: при миграциях систем. → Суррогатный ключ клиента и дедуп на CORE; countDistinct по суррогату.
Выбор метрики активности: покупка/вход/просмотр. → Зафиксировать в паспорте метрики.

Churn (отток)

Churn по когорте = 1 − retention_rate. Для подписок — отдельные определения (например, «не продлил в течение X дней после окончания периода»). В CH удобно хранить состояние подписки по дням (snapshots).

LTV (Lifetime Value)

Подход: для каждой когорты суммируем выручку по периодам от cohort_day.

WITH ltv AS (
  SELECT
    cohort_day,
    cohort_week,
    sum(amount_base) AS rev
  FROM vw_sales_with_cohort
  GROUP BY cohort_day, cohort_week
),
ltv_cum AS (
  SELECT
    cohort_day,
    cohort_week,
    sum(rev) OVER (PARTITION BY cohort_day ORDER BY cohort_week
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ltv_value
  FROM ltv
)
SELECT * FROM ltv_cum
ORDER BY cohort_day, cohort_week;

Расширения

LTV на пользователя: разделить на размер когорты.
Дисконтированный LTV: применить коэффициент дисконтирования по cohort_week.
LTV по сегментам: PARTITION BY cohort_day, segment.

Риски

Поздние возвраты/чарджбеки — меняют LTV задним числом. → Ретро-окно пересчёта (N недель), side-by-side для больших правок.
Мультивалюты — сумма пляшет. → Считать LTV в базовой валюте по курсу события.

Статистические метрики: перцентили, уникальные, top-k

Перцентили (latency p95/p99, сумма чека p90)

ClickHouse предоставляет семейство quantile*. Для устойчивости — состояния:

CREATE TABLE agg_check_amount_p_state
(
  day Date,
  p90_state AggregateFunction(quantileTDigest(0.90), Decimal(12,2)),
  p99_state AggregateFunction(quantileTDigest(0.99), Decimal(12,2))
)
ENGINE = AggregatingMergeTree
ORDER BY day;
INSERT INTO agg_check_amount_p_state
SELECT
  toDate(tx_datetime) AS day,
  quantileTDigestState(0.90)(amount_base),
  quantileTDigestState(0.99)(amount_base)
FROM mart_sales_wide
GROUP BY day;
CREATE OR REPLACE VIEW vw_check_amount_p AS
SELECT
  day,
  quantileTDigestMerge(0.90)(p90_state) AS p90,
  quantileTDigestMerge(0.99)(p99_state) AS p99
FROM agg_check_amount_p_state
GROUP BY day;

Риски

Смешивание окон: считать по дням, а в BI требуются недели. → На чтении объединять по неделям toStartOfWeek(day) и делать …Merge.
Тяжёлые перцентили на лету → материализовать в состоянии.

Уникальные (DAU/WAU/MAU, уникальные клиенты)

Выбор алгоритма:

uniqExact — точно, но медленно/память.
uniqCombined — компромисс (рекомендуется для витрин).
uniqHLL12 — быстрый, допуск на погрешность.

Материализация состояний:

CREATE TABLE agg_dau_state
(
  day Date,
  dau_state AggregateFunction(uniqCombined, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY day;
INSERT INTO agg_dau_state
SELECT
  toDate(event_time) AS day,
  uniqCombinedState(user_id)
FROM mart_events_wide
GROUP BY day;
CREATE VIEW vw_dau AS
SELECT day, uniqCombinedMerge(dau_state) AS dau
FROM agg_dau_state
GROUP BY day;

MAU: агрегируем окнами или суммируем состояния по месяцам и делаем …Merge.

Риски

Смена ID (anon → auth). → Делать маппинг идентификаторов (device_id ↔ user_id), «склеивать» при наличии пары.
Погрешность HLL неприемлема в финансовых отчётах. → Для денег — только exact/combined.

Top-k (товары/поисковые фразы/каналы)

ClickHouse имеет агрегат topK(N) и topKWeighted(N). Для устойчивости — состояния:

CREATE TABLE agg_topk_sku_state
(
  day Date,
  topk_state AggregateFunction(topK, UInt32)
)
ENGINE = AggregatingMergeTree
ORDER BY day;
INSERT INTO agg_topk_sku_state
SELECT
  toDate(tx_datetime) AS day,
  topKState(10)(sku_id)
FROM mart_sales_wide
GROUP BY day;
CREATE VIEW vw_topk_sku AS
SELECT
  day,
  topKMerge(10)(topk_state) AS top10_sku -- возвращает массив
FROM agg_topk_sku_state
GROUP BY day;

Риски

Требуется топ-k по сегментам/категориям → увеличить измерения в ключе/группировке.
Требуется «вес» топа (по сумме выручки) → topKWeightedState(N)(sku_id, amount_base).

Промо-метрики: uplift, инкрементальные продажи, каннибализация

Промо-анализ — зона повышенных рисков из-за путаницы в контрфактах (что было бы без промо). Минимально воспроизводимые паттерны в CH:

Тест/контроль (A/B) с CUPED-коррекцией (если возможно)

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

До промо вычислить среднюю выручку/количество по паре «тест-контроль»;
Во время промо — посчитать дельту;
Применить CUPED (коррекция по ковариате «до промо»), если нужна.

SQL-набросок (упрощённо):

-- 1) Базовая ковариата: средняя дневная выручка за до-промо-период
CREATE VIEW vw_baseline AS
SELECT
  shop_id,
  sku_id,
  avg(net_sales) AS baseline
FROM vw_retail_daily -- (из части 1: net_sales по day × shop × category)
WHERE day BETWEEN '2025-06-01' AND '2025-06-30'
GROUP BY shop_id, sku_id;
-- 2) Эффект во время промо
CREATE VIEW vw_promo_effect AS
SELECT
  p.promo_id,
  s.day,
  s.shop_id,
  s.sku_id,
  s.net_sales AS sales_during,
  b.baseline
FROM promo_calendar p
JOIN vw_retail_daily s
  ON s.day BETWEEN p.start_day AND p.end_day
 AND s.shop_id = p.shop_id AND s.sku_id = p.sku_id
LEFT JOIN vw_baseline b
  ON b.shop_id = s.shop_id AND b.sku_id = s.sku_id;

Дальше — соединяем с контролем (подобранным заранее алгоритмом) и считаем разницу. Для CUPED вводим параметр θ (оценка ковариаты), в CH это можно «зашить» как константу или вьюху с рассчитанным θ.

Риски

Плохой контроль → ложный uplift. → Инструментальный отбор (matching), хотя бы по простым ковариатам (traffic, sales без промо).
Спилловеры/каннибализация → рассматривать близлежащие магазины/товары, считать «negative uplift» на соседях.

Инкрементальные продажи на истории (без А/В)

Если А/В невозможно — считаем «до/после» относительно baseline с сезонной поправкой:

-- season_index(day, category) заранее рассчитан (1.0 +/-)
SELECT
  p.promo_id, s.day, s.shop_id, s.sku_id,
  s.net_sales - (b.baseline * season.season_index) AS incremental
FROM vw_promo_effect s
LEFT JOIN seasonality_index season
  ON season.day = s.day AND season.category_id = s.category_id;

Риски

Сезонность/тренд искажают оценку. → Использовать индекс сезонности и скользящий baseline (rolling).
Эффект хвоста (до/после промо). → Считать «halo-эффект» в окне ±N дней.

Каннибализация (внутри категории)

Суммарные продажи категории растут на X, продажи акционного SKU растут на Y, но продажи «соседей» падают на Z. Каннибализация = min(Y - X, 0) (очень упрощённо), лучше считать на уровне эластичности:

В CH собираем агрегаты по группе SKU и смотрим дифференциал в промо-период относительно baseline;
Выносим в отдельную витрину agg_promo_effect с incremental_by_sku и incremental_by_category;
Каннибализация = incremental_by_sku - incremental_by_category, если отрицательная — есть каннибализация.

Атрибуция маркетинга: first/last touch, U-shape, Markov (упрощённо)

Сессии пользователя несут последовательность каналов (campaign → medium). В CH удобно собирать цепочки как массивы.

Последовательности каналов и last-touch

-- Сессии с каналом по user_id
CREATE VIEW vw_sessions AS
SELECT
  user_id,
  session_id,
  toDateTime(min(event_time)) AS session_start,
  anyLast(channel) AS channel,                -- или более точная логика из атрибутов
  max(is_purchase) AS purchased,
  maxIf(order_amount, is_purchase=1) AS amount
FROM mart_events_wide
GROUP BY user_id, session_id;
-- Сортированный массив каналов до конверсии
CREATE VIEW vw_paths AS
SELECT
  user_id,
  groupArray(channel ORDER BY session_start) AS path,
  anyLastIf(amount, purchased=1) AS order_amount,
  max(purchased) AS purchased
FROM vw_sessions
GROUP BY user_id;
-- Last touch атрибуция
SELECT
  if(purchased=1, arrayElement(path, length(path)), NULL) AS last_channel,
  sum(order_amount) AS revenue
FROM vw_paths
WHERE purchased=1
GROUP BY last_channel
ORDER BY revenue DESC;

First touch и U-shape

First touch — arrayElement(path, 1).
U-shape — распределяем вес w_first, w_last, остальное равномерно по середине (в CH — через arrayEnumerate, arrayMap, arrayJoin).

Пример наброска U-shape:

WITH weights AS (
  SELECT 0.4 AS w_first, 0.4 AS w_last
)
SELECT
  ch AS channel,
  sum(contrib) AS revenue
FROM (
  SELECT
    user_id, order_amount,
    path,
    arrayEnumerate(path) AS idxs,
    arrayMap((ch, i) ->
      multiIf(
        i=1,            w_first     * order_amount,
        i=length(path), w_last      * order_amount,
                        (1 - w_first - w_last) * order_amount / greatest(length(path)-2, 1)
      ),
      path, idxs) AS contribs,
    arrayJoin(arrayZip(path, contribs)) AS t,
    t.1 AS ch,
    t.2 AS contrib
  FROM vw_paths, weights
  WHERE purchased=1
)
GROUP BY ch
ORDER BY revenue DESC;

Марковская атрибуция (очень упрощённо)

Строим переходы channel_i → channel_{i+1}, добавляем поглощающие состояния START и CONVERSION. Оценка вклада — через removal effect (исключение канала и пересчёт вероятности конверсии). В CH можно собрать матрицу переходов и симулировать, но полноценная оценка — за пределами простого SQL. Практично:

В CH: посчитать частоты переходов и вероятности конверсии по коротким цепочкам;
Аналитику removal-effects выполнить вне (Python), результат вернуть как weights per channel и использовать в витрине атрибуции.

Риски

Длинные цепочки → память. → Ограничить длину путей (N последних).
Самоссылающиеся спам-каналы → нормализовать каналы; не учитывать «direct» как генератор пути, а только как last touch fallback.

Управление доступом и маскирование на уровне семантики

Политики строк (RLS)

RLS лучше вешать на базовые таблицы витрин, а BI направлять на VIEW — тогда любые JOIN/VIEW наследуют политику.

-- Пример: пользователь видит только свой регион
CREATE ROW POLICY rp_sales_region
ON db_marts.mart_sales_wide
FOR SELECT
USING region_id = currentSetting('region_id');
ALTER USER analyst1 SETTINGS region_id = 77;

Маскирование PII через VIEW

CREATE VIEW vw_sales_masked AS
SELECT
  day, shop_id, category_id, net_sales, qty,
  NULL AS customer_id,                               -- скрываем
  substring(toString(customer_hash), 1, 10) AS cid   -- или хеш, если нужно связать
FROM vw_retail_daily;

Раздаём доступ BI только к вьюхам vw_*, доступ к «сырым» витринам — группе разработчиков.

Риски

Случайный прямой доступ к таблицам. → Гранты: DENY на таблицы, GRANT только на vw_*.
Логические дыры RLS. → Тестировать «неявные» пересечения (например, суммы по редкому региону).

CI/CD семантического слоя

Репозиторий артефактов

/sql/views/*.sql — код VIEW.
/sql/tables/*.sql — схемы витрин/агрегатов.
/metrics/*.yaml — паспорта метрик.
/tests/*.sql — DQ/регрессионные тесты.
/docs — автогенеримую документацию по метрикам.

Pipeline

PR с изменениями VIEW/метрик.
Локальный прогон на стейдже:
- миграции схем,
- наполнение тестовым окном,
- прогон tests/*.sql (балансы, отсутствие дублей, непротиворечивость).
Сравнение v1 vs v2 на окне N дней:
чек-таблица «дельты по ключевым метрикам»,
пороги допустимых расхождений (0 для большинства).
Деплой: side-by-side, alias/view переключение.
Пост-мониторинг (freshness, ошибки запросов, parts/merges).

Примеры автотестов (SQL)

Баланс vs CORE за вчера:

SELECT
  'net_sales_diff' AS test_name,
  abs(
    (SELECT sum(net_sales) FROM vw_retail_daily WHERE day=yesterday()) -
    (SELECT sum(amount_base) FROM core.sales WHERE toDate(tx_datetime)=yesterday() AND status IN ('paid','captured'))
  ) / NULLIF(
    (SELECT sum(amount_base) FROM core.sales WHERE toDate(tx_datetime)=yesterday() AND status IN ('paid','captured')), 0
  ) AS rel_diff
HAVING rel_diff <= 0.002; -- 0.2%

Дубликаты grain (day, shop_id, category_id):

SELECT 'no_duplicate_grain' AS test_name
FROM (
  SELECT day, shop_id, category_id, count(*) AS c
  FROM vw_retail_daily
  WHERE day BETWEEN today()-7 AND today()-1
  GROUP BY day, shop_id, category_id
  HAVING c = 1
)
HAVING count() > 0;

Нулевые значения только при «тишине»:
сверяем, что net_sales=0 идёт рука об руку с отсутствием чеков в CORE.

Кейсы и практические схемы

SaaS: MRR, ARR, Churn, Net Revenue Retention (NRR)

Определения:

MRR: сумма ежемесячных регулярных платежей активных подписок на дату day_end.
Churn MRR: MRR, потерянный из-за оттока (снимаем на срезе).
Expansion MRR: MRR прироста (upsell/cross-sell).
NRR: (MRR_start + Expansion - Churn) / MRR_start.

Снапшоты подписок (по дням, агрегируем к месяцу):

CREATE TABLE subs_snapshot
(
  day Date,
  account_id UInt64,
  plan_id UInt32,
  mrr Decimal(12,2),
  is_active UInt8
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, account_id);
-- MRR по месяцу
CREATE VIEW vw_mrr_month AS
SELECT
  toStartOfMonth(day) AS mon,
  sum(mrr) AS mrr
FROM subs_snapshot
WHERE is_active=1
GROUP BY mon;

NRR по месяцам:

WITH base AS (
  SELECT mon, mrr AS mrr_end FROM vw_mrr_month
),
prev AS (
  SELECT addMonths(mon, 1) AS mon, mrr AS mrr_start FROM vw_mrr_month
)
SELECT
  b.mon,
  p.mrr_start,
  b.mrr_end,
  b.mrr_end / NULLIF(p.mrr_start, 0) AS nrr
FROM base b
LEFT JOIN prev p USING (mon);

Риски

Единоразовые платежи мешают MRR. → Чётко отделить recurring от one-time.
Смена плана (downgrade/upgrade) → дробим на churn/expansion. → Отдельная витрина «изменений тарифов».

Финансы: Take-Rate, ARPU, NPL (кредитный портфель)

Take-Rate (доля дохода платформы в обороте): platform_revenue / GMV.
ARPU: total_revenue / active_users.
NPL: доля портфеля в просрочке > X дней.

Пример ARPU за месяц:

WITH revenue AS (
  SELECT toStartOfMonth(tx_datetime) AS mon, sum(amount_base) AS rev
  FROM mart_tx_wide
  WHERE status='paid'
  GROUP BY mon
),
actives AS (
  SELECT toStartOfMonth(event_time) AS mon, uniqCombined(user_id) AS mau
  FROM mart_events_wide
  GROUP BY mon
)
SELECT
  r.mon,
  r.rev / NULLIF(a.mau, 0) AS arpu
FROM revenue r
JOIN actives a USING (mon);

Риски

Несогласованные активные пользователи: разные фильтры каналов/событий. → Зафиксировать семантику MAU/DAU в паспорте метрик.

«Ловушки» и prophylaxis для семантического слоя

Считать проценты от процентов.
Проблема: «среднее GM% по магазинам» отличается от SUM(gm)/SUM(net_sales).
Решение: агрегировать через числитель/знаменатель.
Скользящее окно по дырявому календарю.
Проблема: «roll7» не 7 дней, а 3 (из-за пропусков).
Решение: матрица дат × ключ и fill zeros.
COUNT(DISTINCT) на лету при больших объёмах.
Проблема: дорого/медленно.
Решение: uniq*State/…Merge в AggregatingMergeTree.
Мультивалютность «на дату отчёта» смешана с «на дату транзакции».
Проблема: расхождения с FP&A.
Решение: две метрики, два VIEW, паспорта фиксируют логику.
RLS забыли на одной из базовых таблиц.
Проблема: утечки.
Решение: доступ BI только к vw_*, политики на базовые, тест «утечки» в CI.
Неподконтрольное использование FINAL.
Проблема: внезапная деградация SLA.
Решение: запрещайте FINAL в BI (линтер запросов), обеспечивайте консистентность при записи.
SummingMergeTree с повторными заливками.
Проблема: удвоения.
Решение: rebuild из чистого источника или переход на AggregatingMergeTree.

Шаблоны VIEW для частых задач

Rolling 28-day net sales (готовая вьюха)

CREATE OR REPLACE VIEW vw_net_sales_roll28 AS
WITH base AS (
  SELECT day, shop_id, sumMerge(net_amount_state) AS net_sales
  FROM agg_retail_daily_state
  GROUP BY day, shop_id
),
dense AS (
  SELECT
    c.d AS day, s.shop_id,
    coalesce(b.net_sales, 0) AS net_sales
  FROM (SELECT DISTINCT shop_id FROM base) s
  CROSS JOIN (SELECT d FROM d_calendar WHERE d >= today()-INTERVAL 120 DAY) c
  LEFT JOIN base b ON b.day = c.d AND b.shop_id = s.shop_id
)
SELECT
  day, shop_id,
  sum(net_sales) OVER (
    PARTITION BY shop_id
    ORDER BY day
    ROWS BETWEEN 27 PRECEDING AND CURRENT ROW
  ) AS net_sales_28d
FROM dense;

DAU/WAU/MAU в одной вьюхе

CREATE OR REPLACE VIEW vw_dau_wau_mau AS
WITH dau AS (
  SELECT toDate(event_time) AS day, uniqCombined(user_id) AS dau
  FROM mart_events_wide
  GROUP BY day
),
wau AS (
  SELECT toDate(event_time) AS day,
  uniqCombined(user_id) OVER (
    ORDER BY toDate(event_time)
    RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
  ) AS wau
  FROM mart_events_wide
),
mau AS (
  SELECT toDate(event_time) AS day,
  uniqCombined(user_id) OVER (
    ORDER BY toDate(event_time)
    RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW
  ) AS mau
  FROM mart_events_wide
)
SELECT
  d.day,
  any(d.dau) AS dau,
  maxBy(w.wau, w.day=d.day) AS wau,
  maxBy(m.mau, m.day=d.day) AS mau
FROM dau d
LEFT JOIN wau w ON w.day = d.day
LEFT JOIN mau m ON m.day = d.day
GROUP BY d.day;

(Если версия CH не поддерживает оконный uniqCombined, делаем состояния в AggregatingMergeTree и …Merge.)

Контроль качества семантики (над DQ фактов)

Помимо «физических» DQ, введите семантические тесты:

Идентичность метрики в разных срезах: SUM(child) == parent на дереве категорий.
Инварианты: GM <= NetSales, NRR ∈ [0, +∞).
Согласование PII-маскировок: отсутствие PII в vw_*.
Стабильность формул: «вчера/сегодня без изменений кода VIEW → совпадение на окне без корректировок».

Пример инварианта:

SELECT 'gm_le_net_sales' AS test_name
FROM (
  SELECT
    sumMerge(gm_amount_state) AS gm,
    sumMerge(net_amount_state) AS ns
  FROM agg_retail_daily_state
  WHERE day BETWEEN today()-7 AND today()-1
)
HAVING gm <= ns;

Наблюдаемость семантического слоя

Добавьте в мониторинг бизнес-метрики:

Freshness ключевых VIEW (время последнего обновления состояний, можно хранить в маленькой табличке sem_meta(view, last_update) и обновлять в конце джоба).
«Здравье» метрик: дельта vs CORE, дубли grain.
Объём/время выполнения VIEW (через system.query_log), топ-потребители.

Пример «таймстемп» таблицы:

CREATE TABLE sem_meta
(
  view_name LowCardinality(String),
  updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY view_name;
-- В конце любого джоба пересчёта:
INSERT INTO sem_meta VALUES ('vw_retail_daily', now());

«Как продать» семантический слой BI-командам

Одна формула — одно место: исправления мгновенно попадают во все отчёты (через VIEW).
SLA и обратимость: v1/v2, side-by-side, быстрый откат.
Тестируемость: SQL-тесты, nightly-сверки.
Прозрачность: паспорта метрик (YAML/MD), автодоки.
Производительность: тяжёлое в AggregatingMergeTree, BI — лёгкие SELECT из VIEW.

Маршрут внедрения семантического слоя за 2–3 дня

Ниже — «боевой» план, который можно повторять. Предполагаем, что слой витрин (таблицы mart_*, агрегаты agg_*) уже есть или строится параллельно (см. Модуль 0).

День 1 — Фундамент и артефакты

Собрать список метрик (приоритет TOP-10 для запуска: Net Sales, GM, GM%, Orders, AOV, DAU/WAU/MAU, Conversion Rate, Retention D7/D30, ARPU).
Создать репозиторий marts-semantic со структурой:

/sql/tables/         -- схемы витрин/агрегатов (истина исполнения)
/sql/views/          -- семантические VIEW для потребителей
/metrics/            -- YAML-паспорта метрик
/tests/              -- SQL-тесты DQ и регрессии
/ci/                 -- скрипты деплоя и проверки
/docs/               -- автогенеримая документация (md/html)

Завести календарь, словари и маппинги (см. Часть 1):
- d_calendar (включая финансовый 4-5-4 при необходимости),
- d_status_map (канонические статусы),
- dict_fx (курсы валют), dict_uom (единицы измерения),
- (опционально) словари сегментов клиентов/товаров.
Описать метрики в YAML-паспортax (шаблон в секции 29), зафиксировать: формулу, фильтры, календарь, валюты, окно ретро-пересчёта, владельцев, тесты и версию v1.
Собрать «скелет» VIEW под каждую метрику (пусть даже временно без сложной логики, но с правильными join на календарь/статусы/валюты) и проверить первые результаты на окне 7–14 дней.

День 2 — Тесты и стабильность

Добавить DQ-тесты (см. секцию 31): балансы с CORE, отсутствие дублей grain, домены статусов, инварианты (GM ≤ Net Sales и т. п.).
Собрать регрессионные тесты между v1 и «эталонной» таблицей/отчётом (или между v1 и v1-staging после изменений).
Настроить CI: линтер SQL, прогон тестов на стейдже, генерация документации по метрикам (md → html).
Включить наблюдаемость для семантики: таблица sem_meta(view_name, updated_at), алерты свежести, проверка «утечек PII» (доступ BI — только к vw_*).

День 3 — Презентация и переход в эксплуатацию

Согласовать с BI-командой:
- BI подключается к VIEW, не к таблицам;
- дефолтные фильтры в отчётах (календарь, период, необходимые срезы);
- лимиты (например, не более 90 дней за раз).
Подготовить runbook на случай расхождений: шаги диагностики (сверка с CORE, проверка ретро-окна, дублей, статусов).
Оформить «политику изменений метрик»: любая корректировка — через PR, bump версии в YAML, сравнение v1→v2 на окне, side-by-side переключение VIEW/alias (см. секцию 30).

Каркас семантического слоя: пример файлов и Makefile

Пример sql/views/vw_net_sales_daily.sql:

CREATE OR REPLACE VIEW db_marts.vw_net_sales_daily AS
WITH base AS (
  SELECT
    toDate(tx_datetime) AS day,
    shop_id,
    category_id,
    -- нормализация статуса и валюты вынесена во vw_sales_enriched
    sumMerge(net_amount_state) AS net_sales,
    sumMerge(qty_state)        AS qty
  FROM db_marts.agg_retail_daily_state
  GROUP BY day, shop_id, category_id
)
SELECT
  b.day,
  b.shop_id,
  b.category_id,
  b.net_sales,
  b.qty,
  cal.fiscal_year,
  cal.fiscal_week,
  cal.fiscal_month
FROM base b
LEFT JOIN db_marts.d_calendar cal ON cal.d = b.day;

Пример metrics/NET_SALES.yaml (см. шаблон в секции 29, тут укорочено):

id: NET_SALES
name: Net Sales (Чистые продажи)
grain: [day, shop_id, category_id]
definition:
  formula: "sumMerge(net_amount_state)"
  filters:
    - "status_canon IN ('PAID','REFUND','CANCELLED')"
  calendar: "FISCAL_454"
  currency: "BASE (на дату транзакции)"
late_window_days: 14
materialization:
  view: "db_marts.vw_net_sales_daily"
  state_table: "db_marts.agg_retail_daily_state"
dq:
  - "balance_vs_core <= 0.2%"
owners:
  business: "Head of Sales Finance"
  technical: "DWH Architect"
version:
  current: "v1"
  changes:
    - "2025-08-03: created v1"

Пример tests/t_balance_vs_core.sql:

-- Проверяем расхождение с CORE за вчера
WITH marts_sum AS (
  SELECT sum(net_sales) AS s
  FROM db_marts.vw_net_sales_daily
  WHERE day = yesterday()
),
core_sum AS (
  SELECT sum(amount_base) AS s
  FROM core.sales_enriched
  WHERE toDate(tx_datetime) = yesterday()
    AND status_canon = 'PAID'
)
SELECT
  'net_sales_balance_vs_core' AS test_name,
  abs(m.s - c.s) / NULLIF(c.s, 0) AS rel_diff
FROM marts_sum m, core_sum c
HAVING rel_diff <= 0.002;

Пример Makefile (или bash-скрипт) для деплоя на стейдже:

deploy-stage:
        @echo "Applying tables..."
        cat sql/tables/*.sql | clickhouse-client --host $(CH_STAGE_HOST) --multiquery
        @echo "Applying views..."
        cat sql/views/*.sql | clickhouse-client --host $(CH_STAGE_HOST) --multiquery
test-stage:
        @echo "Running tests..."
        for f in tests/*.sql; do \
          echo "Test $$f"; \
          clickhouse-client --host $(CH_STAGE_HOST) --multiquery < $$f || exit 1; \
        done
docs:
        @python ci/gen_docs.py  # генерит docs/ из metrics/*.yaml
ci:
        make deploy-stage && make test-stage && make docs

Управляемый доступ: роли, RLS и PII-маскирование в терминах «семантики»

Роли:
- semantic_reader — SELECT только на db_marts.vw_*;
- semantic_dev — SELECT на источники + CREATE VIEW, ALTER VIEW;
- semantic_admin.
Гранты:

CREATE ROLE semantic_reader;
GRANT SELECT ON db_marts.vw_* TO semantic_reader;
CREATE USER bi_ro IDENTIFIED BY '...';
GRANT semantic_reader TO bi_ro;

RLS:

CREATE ROW POLICY rp_sales_region
ON db_marts.mart_sales_wide
FOR SELECT
USING region_id = currentSetting('region_id');
ALTER USER bi_ro SETTINGS region_id = 77;

PII-маскирование через VIEW:

CREATE OR REPLACE VIEW db_marts.vw_sales_masked AS
SELECT
  day, shop_id, category_id, net_sales, qty,
  NULL as customer_id, substring(sha256HEX(toString(customer_id)),1,12) AS cid_hash
FROM db_marts.vw_net_sales_daily;

Политика: BI имеет права только на vw_*. Прямые SELECT к физическим таблицам — запрещены (не выдавать грантов) и мониторятся.

Шаблон паспорта метрики (полный, копируй и используй)

id: <METRIC_ID>            # Уникальный идентификатор, A-Z0-9_ (например, GM_PERCENT)
name: <Человекочитаемое название>
purpose: |
  Короткое описание бизнес-цели метрики (где используется, кем и зачем).
owners:
  business: <ФИО/Роль>
  technical: <ФИО/Роль>
stakeholders:
  - <Подразделение/Роль>
grain:
  - <измерение-1>          # Например: day
  - <измерение-2>          # Например: shop_id
  - <измерение-3>          # Например: category_id
calendar:
  type: <GREGORIAN|FISCAL_454|FISCAL_445|CUSTOM>
  tz: <Europe/Moscow|UTC|...>
currency:
  basis: <BASE|LOCAL|REPORTING>
  rule: <на дату транзакции|на дату отчёта|смешанная>
uom:
  base: <шт|кг|...>
  notes: |
    Если есть перекладка единиц, опишите источники коэффициентов.
definition:
  formula_sql: |
    -- SQL/псевдо: формула вычисления (через числитель/знаменатель, если неаддитивная)
  filters:
    - "status_canon IN ('PAID','REFUND','CANCELLED')"
    - "source NOT IN ('test')"
  notes: |
    Подводные камни формулы (возвраты, скидки, налоги).
materialization:
  view: db_marts.vw_<...>
  tables:
    - db_marts.mart_<...>
    - db_marts.agg_<...>
  mode: <read_from_state|read_from_wide|hybrid>
  late_window_days: 14
  retro_recalc:
    schedule: "00:30 daily"
    depth_days: 30
dq:
  acceptance:
    - name: balance_vs_core
      threshold: 0.002
      sql: |
        -- SQL тест (окно 'yesterday')
    - name: no_duplicate_grain
      sql: |
        -- SQL тест
  invariants:
    - "GM <= NetSales"
    - "NRR >= 0"
bi_guidance:
  default_filters:
    - "period = last_28_days"
    - "region IN (allowed)"
  limits:
    rows: 1000000
    max_period_days: 180
change_management:
  version: v1
  effective_from: 2025-08-01
  changes:
    - "2025-08-01: created v1"
deprecation:
  policy: |
    v1 считается устаревшей через 60 дней после выпуска v2.
documentation:
  links:
    - "Confluence: ... "
    - "Dashboard: ..."

Миграции семантики: v1→v2 без простоя (side-by-side)

Процедура

Создаём новую вьюху vw_metric_v2 c изменённой формулой/фильтрами.
Запускаем регрессионное сравнение vw_metric_v1 vs vw_metric_v2 на окне N дней:
- ожидаемые расхождения — формально описаны (например, +2–3% за счёт исключения тест-заказов).
Обновляем YAML-паспорт: version: v2, effective_from.
На стейдже — тестируем, BI переключаем в тестовый режим; публикуем заметку.
В проде — CREATE OR REPLACE VIEW vw_metric AS SELECT * FROM vw_metric_v2;
Сохраняем vw_metric_v1 30–60 дней для «отката» и исторических сверок.
Через N дней — удаляем v1, закрываем тикет.

SQL-набор сравнения

-- Сравнение суммы по окну и по ключевым разрезам
WITH v1 AS (
  SELECT day, shop_id, sum(metric) AS s
  FROM db_marts.vw_metric_v1
  WHERE day BETWEEN today()-30 AND today()-1
  GROUP BY day, shop_id
),
v2 AS (
  SELECT day, shop_id, sum(metric) AS s
  FROM db_marts.vw_metric_v2
  WHERE day BETWEEN today()-30 AND today()-1
  GROUP BY day, shop_id
)
SELECT
  coalesce(v1.day, v2.day) AS day,
  coalesce(v1.shop_id, v2.shop_id) AS shop_id,
  v1.s AS v1_sum,
  v2.s AS v2_sum,
  v2.s - v1.s AS diff_abs,
  100.0 * (v2.s - v1.s) / NULLIF(v1.s,0) AS diff_pct
FROM v1
FULL OUTER JOIN v2 USING (day, shop_id)
ORDER BY day, shop_id;

Риски и профилактика

Подмена истории: бизнес видит «другие» вчерашние цифры.
→ Публикуйте changelog и ожидаемую дельту, держите обе версии параллельно.
Развал BI-дашбордов: меняется схема/названия колонок.
→ Вводите v2 с теми же колонками; если меняется схема — договоритесь о переходном периоде и используйте alias.

Регрессионные тесты семантики: что и как проверять

Категории тестов:

Баланс/согласование: MARTS vs CORE, таблица vs VIEW, v1 vs v2.
Неконсистентности: дубли grain, «дыры» в плотности данных, некорректные статусы.
Инварианты: предикаты, которые всегда должны быть истинными.
Производственные guardrails: отсутствие FINAL в длительно работающих запросах VIEW (линтер текста SQL).
Доступ: PII-утечки (нет customer_id в vw_*), RLS-покрытие.

Примеры SQL тестов (дополнение к секции 27):

Дыры в плотности (по shop_id должны быть все дни):

WITH days AS (
  SELECT d FROM db_marts.d_calendar
  WHERE d BETWEEN today()-30 AND today()-1
),
shops AS (
  SELECT DISTINCT shop_id FROM db_marts.vw_net_sales_daily
)
SELECT 'no_gaps' AS test_name
FROM (
  SELECT s.shop_id, d.d AS day
  FROM shops s
  CROSS JOIN days d
  LEFT JOIN db_marts.vw_net_sales_daily v
    ON v.shop_id = s.shop_id AND v.day = d.d
  WHERE v.shop_id IS NULL
)
HAVING count() = 0;
Отсутствие FINAL в VIEW (грубый скриптовый линтер):
bash
КопироватьРедактировать
# ci/check_no_final.sh
for f in sql/views/*.sql; do
  if grep -i "final" "$f"; then
    echo "ERROR: FINAL found in $f"; exit 1;
  fi
done

Инвариант GM ≤ NetSales (на окне):

SELECT 'gm_le_net' AS test_name
FROM (
  SELECT
    sum(gm) AS gm, sum(net_sales) AS ns
  FROM db_marts.vw_retail_daily
  WHERE day BETWEEN today()-14 AND today()-1
)
HAVING gm <= ns;

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

GM и GM% (валовая прибыль и маржинальность)

GM = NetSales − COGS (COGS — себестоимость).
GM% = GM / NetSales.
Важно фиксировать правила возвратов (REFUND/CANCELLED), скидки, налоги.
Себестоимость должна быть снэпшотом на дату транзакции или на дату отгрузки (бизнес-решение, фиксируется в паспорте).

CH-паттерн: считайте GM в AggregatingMergeTree (состояния), GM% храните как вычисляемый столбец в VIEW (агрегируете числитель/знаменатель и делите).

ARPU / AOV

ARPU: total_revenue / active_users за период.
- active_users — чётко определить (DAU/WAU/MAU по событиям).
AOV (Average Order Value): revenue / orders.
При возвратах решите — учитывать «нетто» (рекомендуется для управленческих отчётов).

CH-паттерн: для active_users используйте uniq*State/…Merge (см. часть 2). Для orders — агрегаты состояний.

ROAS / POAS

ROAS = revenue_attributed / ad_spend.
POAS (Profit On Ad Spend) = gross_profit_attributed / ad_spend.
Ключ — атрибуция (см. часть 2: last/first touch, U-shape).
Правила атрибуции фиксируйте в YAML метрики, источник расходов (ad_spend) — откуда и как частится по каналам/кампаниям.

CH-паттерн: материализуйте таблицу атрибуции attr_orders(channel, revenue_share, gm_share) и считайте ROAS/POAS в VIEW.

CAC/CPA

CAC: acquisition_cost / acquired_users.
- acquired_users — пользователи с cohort_day в периоде.
CPA: cost / actions.
Действие (purchase/registration) — зафиксировать в паспорте.

Риск: разные окна (стоимость в одном месяце, пользователи в другом). Делайте персонифицированную связку (user_id ↔ стоимость канала в пути, приписывайте расходы по атрибуции), либо фиксируйте «операционную» версию CAC (на календарном месяце) и «маркетинговую» (по атрибуции).

NRR (Net Revenue Retention) — для SaaS/повторных продаж

NRR = (MRR_start + Expansion − Churn − Contraction) / MRR_start.
Требуются снапшоты подписок/контрактов на границе месяцев (см. часть 2).
Отдельно учитывать расширения (upsell) и сжатия (downgrade).

CH-паттерн: храните таблицу subs_snapshot(day, account_id, mrr, is_active), для переходов собирайте delta_mrr по счетам (события изменения тарифов).

Кейсы eCom и Telecom: дополнительные VIEW и практики

eCom: AOV, CR, Retention, RFM

AOV (за день × канал)

CREATE OR REPLACE VIEW db_marts.vw_aov_daily AS
WITH orders AS (
  SELECT toDate(tx_datetime) AS day, channel, countDistinct(order_id) AS orders
  FROM db_marts.mart_orders_wide
  WHERE status_canon='PAID'
  GROUP BY day, channel
),
revenue AS (
  SELECT toDate(tx_datetime) AS day, channel, sum(amount_base) AS rev
  FROM db_marts.mart_orders_wide
  WHERE status_canon='PAID'
  GROUP BY day, channel
)
SELECT r.day, r.channel, r.rev / NULLIF(o.orders,0) AS aov
FROM revenue r JOIN orders o USING(day, channel);

CR (конверсия сессий в заказ)

CREATE OR REPLACE VIEW db_marts.vw_cr_daily AS
WITH sessions AS (
  SELECT toDate(session_start) AS day, channel, countDistinct(session_id) AS sess
  FROM db_marts.vw_sessions   -- из части 2
  GROUP BY day, channel
),
orders AS (
  SELECT toDate(tx_datetime) AS day, channel, countDistinct(order_id) AS orders
  FROM db_marts.mart_orders_wide
  WHERE status_canon='PAID'
  GROUP BY day, channel
)
SELECT o.day, o.channel, o.orders / NULLIF(s.sess,0) AS cr
FROM orders o JOIN sessions s USING(day, channel);

RFM (recency/frequency/monetary) сегментация (набросок)

CREATE OR REPLACE VIEW db_marts.vw_rfm AS
SELECT
  customer_id,
  dateDiff('day', max(toDate(tx_datetime)), today()) AS recency,
  countIf(status_canon='PAID') AS frequency,
  sumIf(amount_base, status_canon='PAID') AS monetary
FROM db_marts.mart_orders_wide
GROUP BY customer_id;

Далее — пороговые сегменты в BI или во VIEW (CASE по квантилям).

Риски eCom:

Каналы в заказах и в сессиях — несогласованные. → Нормализуйте через d_channel_map.
Мульти-touch атрибуция — двойной учёт. → Чёткие правила веса каналов.

Telecom: KPI сети (успешность, задержка, отказоустойчивость)

Сводка по сотам (часовая)

CREATE OR REPLACE VIEW db_marts.vw_cell_kpi_hour AS
SELECT
  toStartOfHour(ts_minute) AS hour,
  cell_id,
  sumMerge(calls_state) AS calls,
  sumMerge(ok_state) AS ok,
  quantileTDigestMerge(0.95)(latency_q95_state) AS p95_latency,
  avgMerge(duration_avg_state) AS avg_duration,
  ok / NULLIF(calls,0) AS success_rate
FROM db_marts.agg_cdr_minute_state
GROUP BY hour, cell_id;

Аномалии (спайки отказов)

CREATE OR REPLACE VIEW db_marts.vw_cell_anomalies AS
SELECT
  hour, cell_id, success_rate,
  success_rate < 0.98 AS is_alert
FROM db_marts.vw_cell_kpi_hour;

Риски Telecom:

Бурсты → множество маленьких частей. → Микробатчи, Kafka-настройки, контроль parts.
DQ: пропуски CDR. → Балансы по источнику, сигналы «тишины» по сотам.

Анти-паттерны семантического слоя (и что вместо)

Метрика «закодирована» в 10 отчётах BI, а не во VIEW.
Вместо: Один VIEW, версия метрики в YAML, регресс-тесты, автодоки.
Смешение календарей (операционный vs финансовый) в одном VIEW.
Вместо: Две вьюхи (operational/fiscal), чёткое описание в паспорте.
COUNT(DISTINCT) в BI на миллиардах строк.
Вместо: AggregatingMergeTree + uniq*State/…Merge, «плоский» SELECT из VIEW.
SummingMergeTree для данных, которые часто переигрываются.
Вместо: AggregatingMergeTree (состояния) или ReplacingMergeTree с версией.
Широкие витрины с PII без маскировки, BI видит «всё».
Вместо: VIEW с маскировкой, гранты только на vw_*, RLS на базовых таблицах.
FINAL в текстах VIEW «на постоянку».
Вместо: Идемпотентность при записи и консистентность upstream, отсутствие конкурирующих версий.
Проекции как «универсальный ускоритель» с первого дня.
Вместо: Профилировать, вводить точечно; начинать с правильного ORDER BY/партиций.

Документация семантики: автогенерация, навигация, связь с дашбордами

Из metrics/*.yaml генерируйте md/html (скрипт на Python), публикуйте в wiki.
Для каждой метрики:
- ссылка на VIEW,
- на исходные витрины/агрегаты,
- на дашборды/репорты,
- на чек-тесты.
Добавьте матрицу трассируемости (источник → витрина → VIEW → дашборд).
Храните пример запроса к VIEW (SELECT TOP-n, фильтры по умолчанию).
Обновляйте docs в CI после каждого merge.

Наблюдаемость семантики и «здоровье» метрик

Таблица свежести:

CREATE TABLE db_marts.sem_meta
(
  view_name LowCardinality(String),
  updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY view_name;

Обновляйте её в конце каждого джоба пересчёта. Алерт, если now() - updated_at > SLA_window.

Дэшборд здоровья:

Плитки: Freshness ключевых VIEW, Parts/Merges для критичных таблиц, DQ-расхождения vs CORE, кол-во дублей, запросы-лидеры по времени/байтам (system.query_log).
Отдельная плитка: версия метрик (список v1/v2 и effective_from).

Часто задаваемые вопросы (расширенные)

Q: Можно ли держать семантику «не в CH», а в dbt/BI?
A: Можно, но риски: дублирование формул и отсутствие единого места правды. Базовый слой семантики лучше в CH-VIEW (или в виде dbt-моделей, но всё равно централизовано), BI — только презентация.

Q: Как жить с мульти-TZ?
A: Храните event_time_utc и event_date_local(tz) (или tz_id), семантика считает в фиксированном tz (зафиксировать в паспорте). Для отчётов по локальным TZ — отдельные VIEW.

Q: Что делать с «плавающими» статусами (например, заказ «paid» → позже «refunded»)?
A: Либо ReplacingMergeTree с версией и nightly пересчёт окна, либо AggregatingMergeTree с сигналами (плюс/минус), в любом случае описать окно ретро-пересчёта.

Q: У нас сильная сезонность, MTD не «сходится» с планом.
A: Введите «индекс сезонности» и публикацию прогнозных/нормированных метрик отдельно. Фиксируйте в паспорте, что именно показывает MTD.

Итоговые чек-листы (для закрепления)

A. Чек-лист запуска метрики

Паспорт метрики заполнен (формула, фильтры, календарь, валюты, окно).
VIEW написан, покрывает календарь/валюты/статусы.
Материализация устойчивых агрегатов — AggregatingMergeTree/ReplacingMergeTree.
DQ-тесты добавлены (баланс, дубли, инварианты).
CI настроен: линтер, тесты, docs.
BI читает только VIEW; включены дефолтные фильтры.
sem_meta обновляется; мониторинг свежести включён.

B. Чек-лист изменения метрики (v2)

YAML: bump версии и changelog.
Новая VIEW vw_*_v2 + регресс-сравнение с v1 на окне.
Описаны ожидаемые дельты и дата effective_from.
BI предупреждён, переключение через alias; v1 доступна 30–60 дней.
Пост-мониторинг расхождений и производительности.

C. Чек-лист безопасности

Роли и гранты: BI → semantic_reader → только vw_*.
RLS на базовых таблицах (по региону/организации/тенанту).
PII маскирована во VIEW; тест «утечек PII».
Логи и аудит доступов включены.

Заключение Модуля 1

В этом модуле мы «прошили» бизнес-смысл в слой витрин ClickHouse: от паспортов метрик и семантических VIEW до устойчивых материализаций (Aggregating/Uniq/Quantile state), календаря/валют/статусов, регламентов версионирования, регрессионных тестов и наблюдаемости.
Ключевые принципы, которые обеспечат воспроизводимость и предсказуемость:

Метрики — как код: один источник правды (VIEW + YAML), CI, тесты, доки.
Агрегируем через числитель/знаменатель и состояния — избавляемся от искажений и проблем с повторной загрузкой.
Контракты времени и валют: календарь, TZ, правило курсов — без двусмысленности.
Изменения — контролируемо: v1→v2 side-by-side, регресс-сравнения, пост-мониторинг.
Доступ — безопасно: RLS/маскирование, BI — только VIEW.

Arenadata QuickMarts (ADQM) — корпоративная платформа на базе ClickHouse для быстрого слоя витрин и near-real-time аналитики. Решает задачи «быстрых» дашбордов и API с низкой латентностью и высокой конкуррентностью, работает поверх вашего DWH/лейкхауса как serving-уровень. Даёт предсказуемую производительность на терабайтно-петабайтных объёмах за счёт колоночного хранения, компрессии и предагрегатов (Materialized Views, AggregatingMergeTree), подключается к Kafka/S3 и стандартным BI-инструментам по SQL/HTTP. Для корпоративных ИТ ADQM предлагает поддержку и SLA, отказоустойчивые кластеры (HA/DR), безопасность (RBAC, LDAP/OIDC, шифрование трафика и данных), мониторинг и резервное копирование. Платформа хорошо ложится на методологию курса: семантика vw_*, роллап-слои, NRT-ингест, SLO/наблюдаемость и «гвардейки» для BI/API. Итог — быстрый запуск витрин за недели, снижённые риски в проде и предсказуемая стоимость владения.

← Предыдущая статья

Модуль 0. ClickHouse как слой витрин: зачем, где он силён, как проектировать, какие риски учесть

Следующая статья →

Модуль 2. Моделирование витрин под ClickHouse