Модуль 13. Индустриальные blueprints для витрин (Retail, FinTech, SaaS, AdTech)

Цель — дать готовые архитектурные паттерны и метрики «из коробки» по четырём типовым доменам, чтобы ваша команда могла внедрить слой витрин на ClickHouse «по чертежу», без излишних экспериментов. В каждом блюпринте: модель данных, DDL/VIEW, метрики и паспорта (YAML), DQ/наблюдаемость, дашборд-тайлы, кейсы, риски и митигации.

Как пользоваться блюпринтами

Выберите домен (или несколько).
Склонируйте соответствующую папку-шаблон в репо (/blueprints/<domain>/…).
Пройдите чек-лист настройки (календарь, валюта, ретро-окно, роли).
Заполните «тонкие места» (маппинги статусов, справочники).
Прогоните DQ и перформанс-тесты → подключите BI.

Общая основа для всех доменов:

Партиции по времени (день/месяц), ORDER BY под реальные WHERE.
Тяжёлые метрики (COUNT DISTINCT, квантили, Top-K) — через состояния (…State на записи, …Merge на чтении).
Две валютные логики: «на дату операции» и «на дату отчёта».
Два календаря при необходимости: григорианский и 4-5-4 (Retail).
DQ: свежесть, баланс vs CORE/GL, дубли зерна, плотность, инварианты.
Без FINAL в продуктивных VIEW.
RBAC/RLS: BI видит только vw_*.

Структура каталога блюпринта (единая):

blueprints/
  retail|fintech|saas|adtech/
    sql/tables/        # DDL
    sql/views/         # vw_*.sql
    metrics/           # *.yaml (паспорта метрик)
    tests/             # DQ/регресс/перформанс
    dashboards/        # описания тайлов/источники
    docs/              # краткая дока по внедрению

Retail Blueprint (заказы/чеки/позиции, промо, 4-5-4, возвраты, мультивалюта)

Модель и таблицы

Факты:

mart_sales_wide — чек-позиции (продажи/возвраты), валюта операции.
agg_sales_daily_state — дневные агрегаты-состояния.
events_minute_state — минутные KPI (views/add_to_cart/purchase) — опционально.

Справочники:

d_sku, d_shop, d_calendar_454, price_snapshots (SCD2), fx_rates (словарь).

DDL (ядро):

-- Продажи/возвраты (wide)
CREATE TABLE mart_sales_wide
(
  tx_id UInt64,
  tx_datetime DateTime,
  day Date MATERIALIZED toDate(tx_datetime),
  shop_id UInt32,
  sku_id UInt32,
  qty Int32,
  amount Decimal(14,2),
  currency FixedString(3),
  status_canon LowCardinality(String)  -- PAID/REFUND/CANCELLED/...
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id, sku_id, tx_id);
-- Дневные агрегаты-состояния
CREATE TABLE agg_sales_daily_state
(
  day Date,
  shop_id UInt32,
  category_id UInt32,
  gross_state  AggregateFunction(sum, Decimal(18,2)),  -- валовая выручка (без возвратов)
  refund_state AggregateFunction(sum, Decimal(18,2)),  -- суммы возвратов со знаком +
  qty_state    AggregateFunction(sum, Int64),
  buyers_state AggregateFunction(uniqCombined, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id, category_id);

Наполнение агрегатов (пример):

INSERT INTO agg_sales_daily_state
SELECT
  day,
  shop_id,
  any(d_sku.category_id) AS category_id,
  sumStateIf(amount, status_canon = 'PAID')  AS gross_state,
  sumStateIf(abs(amount), status_canon = 'REFUND') AS refund_state,
  sumStateIf(qty, status_canon = 'PAID')     AS qty_state,
  uniqCombinedStateIf(buyer_id, status_canon = 'PAID') AS buyers_state
FROM mart_sales_wide
LEFT JOIN d_sku USING (sku_id)
GROUP BY day, shop_id, category_id;

Семантика и метрики (VIEW, 4-5-4)

-- Валюта на дату операции: перевод в базовую через словарь курсов
CREATE DICTIONARY dict_fx
(
  date Date, from FixedString(3), to FixedString(3), rate Decimal(18,8)
)
PRIMARY KEY (date, from, to)
SOURCE(CLICKHOUSE(...))
LAYOUT(FLAT());
CREATE OR REPLACE VIEW vw_retail_daily AS
SELECT
  day,
  shop_id,
  category_id,
  sumMerge(gross_state)  AS gross_sales_base,
  sumMerge(refund_state) AS refunds_base,
  (gross_sales_base - refunds_base) AS net_sales_base,
  sumMerge(qty_state)    AS qty,
  uniqCombinedMerge(buyers_state) AS buyers,
  net_sales_base / NULLIF(qty,0)  AS aov
FROM agg_sales_daily_state
GROUP BY day, shop_id, category_id;

4-5-4 календарь:

-- Привязка к финансовой неделе
CREATE OR REPLACE VIEW vw_retail_week_454 AS
SELECT
  c.fin_week_454 AS fin_week,
  shop_id,
  category_id,
  sum(net_sales_base) AS net_sales_base,
  sum(qty) AS qty
FROM vw_retail_daily d
JOIN d_calendar_454 c ON c.date = d.day
GROUP BY fin_week, shop_id, category_id;

Паспорт метрики (YAML) — пример:

id: RETAIL_NET_SALES_DAY
version: 1
grain: day, shop_id, category_id
calendar: gregorian
currency: operation_date
owner: retail_analytics
formula:
  view: vw_retail_daily
  definition: net_sales_base = sumMerge(gross_state) - sumMerge(refund_state)
dq:
  freshness_sla_minutes: 60
  balance_vs_core_rel: 0.002     # ≤0.2%
  invariants:
    - refunds_base >= 0
    - aov >= 0
retro_window_days: 30

Дашборд-тайлы (SQL источники)

CR/AOV по часам за сутки (если есть events_minute_state).
Top-K SKU вчера:

SELECT shop_id, sku_id, sum(net_sales_base) s
FROM vw_retail_daily
WHERE day = yesterday()
GROUP BY shop_id, sku_id
ORDER BY shop_id, s DESC
LIMIT 10 BY shop_id;

DQ-тесты (фрагменты)

-- Баланс vs CORE за вчера
WITH m AS (SELECT sum(net_sales_base) s FROM vw_retail_daily WHERE day = yesterday()),
     c AS (SELECT sum(amount_base) s FROM core.sales WHERE toDate(ts)=yesterday() AND status='PAID')
SELECT abs(m.s-c.s)/NULLIF(c.s,0) <= 0.002 AS ok FROM m,c;
-- Инварианты
SELECT 1 WHERE (SELECT sum(refunds_base) FROM vw_retail_daily WHERE day>=today()-14) >= 0;

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

Возвраты задним числом → митиг: ретро-окно 30 дней, nightly overlay партиций.
Смешение валют/календарей → митиг: раздельные VIEW «операция/отчёт», 4-5-4 отдельно.
COUNT DISTINCT на лету → митиг: uniq*State/…Merge.
Неверный ORDER BY → митиг: (day, shop_id, sku_id[, tx_id]), не усложнять без профита.

FinTech Blueprint (транзакции/остатки, комиссии, FX ASOF, сверка с GL)

Модель и таблицы

Факты:

mart_tx_wide — операции (дебет/кредит), валюта операции, статусы (posted/pending/chargeback).
agg_balances_daily_state — дневные остатки (state).
fees_daily_state — комиссии (state).

Справочники:

accounts_dim (SCD2), fx_rates (словарь), gl_mapping (маппинг транзакций к счетам ГК).

DDL (фрагменты):

CREATE TABLE mart_tx_wide
(
  tx_id UInt64,
  account_id UInt64,
  tx_datetime DateTime,
  day Date MATERIALIZED toDate(tx_datetime),
  amount Decimal(18,2),
  currency FixedString(3),
  tx_type LowCardinality(String),      -- DEBIT/CREDIT
  status  LowCardinality(String)       -- POSTED/PENDING/CHARGEBACK
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (account_id, day, tx_id);

Семантика: «на дату операции» vs «на дату отчёта»

-- FX словарь
CREATE DICTIONARY dict_fx (date Date, from FixedString(3), to FixedString(3), rate Decimal(18,8))
PRIMARY KEY (date, from, to) SOURCE(CLICKHOUSE(...)) LAYOUT(FLAT());
-- Операционный вид (operation date)
CREATE OR REPLACE VIEW vw_fin_ops_day AS
SELECT
  day,
  account_id,
  sumIf( amount * dictGetDecimal64('dict_fx','rate',(day, currency, 'USD')), tx_type='CREDIT' ) AS inflow,
  sumIf( amount * dictGetDecimal64('dict_fx','rate',(day, currency, 'USD')), tx_type='DEBIT'  ) AS outflow,
  inflow - outflow AS net_flow
FROM mart_tx_wide
WHERE status='POSTED'
GROUP BY day, account_id;
-- Отчётный вид (reporting date): FX на дату отчёта (например, EoD)
CREATE OR REPLACE VIEW vw_fin_report_day AS
SELECT
  r.report_day,
  t.account_id,
  sum( t.amount * dictGetDecimal64('dict_fx','rate',(r.report_day, t.currency,'USD')) ) AS balance_usd
FROM report_days r
JOIN mart_tx_wide t ON toDate(t.tx_datetime) <= r.report_day AND t.status='POSTED'
GROUP BY r.report_day, t.account_id;

Сверка с GL (общая идея):

WITH ours AS (SELECT sum(balance_usd) s FROM vw_fin_report_day WHERE report_day=yesterday()),
     gl   AS (SELECT sum(amount_usd)  s FROM gl_balances WHERE day=yesterday())
SELECT abs(ours.s - gl.s)/NULLIF(gl.s,0) <= 0.001 AS ok;  -- ≤0.1%

DQ и инварианты

Двойная запись: сумма DEBIT = сумма CREDIT по проводкам одного класса.
Null-FX/пропуски ставок: тест наличия курсов для доминирующих валют.
Знаковая валидность: комиссии ≥ 0; chargeback корректно «перевернут».

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

«Операция vs отчёт» смешаны → две явные VIEW и паспорт, где это зафиксировано.
ASOF на FX/ценах без сортировки → держать ORDER BY (id, valid_from) для снапшотов, при больших объёмах — pre-join.
Расхождение с GL → баланс-тесты ежедневно, ретро-окно 30 дней.

SaaS Blueprint (подписки/SCD, события, NRR/GRR, когорты, A/B)

Модель и таблицы

Факты:

subscriptions_scd2 — интервалы действия (SCD2), изменения тарифов.
revenue_events — признание выручки/счета.
agg_app_minute_state — DAU/p95/Top-K (если нужно NRT).

DDL (фрагменты):

CREATE TABLE subscriptions_scd2
(
  sub_id UInt64,
  account_id UInt64,
  plan_id UInt32,
  valid_from Date,
  valid_to   Date,
  mrr Decimal(14,2),
  change_type LowCardinality(String)   -- START/EXPANSION/CONTRACTION/CHURN
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(valid_from)
ORDER BY (account_id, valid_from);

Семантика: NRR/GRR и когорты

CREATE OR REPLACE VIEW vw_mrr_month AS
SELECT
  toStartOfMonth(valid_from) AS month,
  sumIf(mrr, change_type='START')       AS start_mrr,
  sumIf(mrr, change_type='EXPANSION')   AS expansion,
  sumIf(-mrr, change_type='CONTRACTION') AS contraction,
  sumIf(-mrr, change_type='CHURN')       AS churn,
  (start_mrr + expansion - contraction - churn) / NULLIF(start_mrr,0) AS nrr,
  1 - (churn / NULLIF(start_mrr,0)) AS grr
FROM subscriptions_scd2
GROUP BY month;

Когорты/ретеншн (идея):

CREATE OR REPLACE VIEW vw_user_cohort AS
SELECT account_id, min(toDate(event_time)) AS cohort_day
FROM product_events WHERE event_name='activation'
GROUP BY account_id;
-- D7/D30 retention на активные события
-- (формирование плотной решётки «cohort_day × day» + countDistinctIf(account_id, diff=7/30))

A/B базовые артефакты: bucketing (детерминированный hash), SRM-чек, CUPED-формулы — из М7.

DQ и инварианты

Пересечения интервалов SCD2 по account_id → не допускаются.
NRR/GRR границы: ∈ [0, …] (NRR может быть >1).
DAU/p95: считать из TDigest-состояний (не на лету).

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

Двойной учёт expansions → фиксировать change_type, регрессы v1↔v2.
SRM в экспериментах → ежедневный χ²-чек и алерт.
План-факт: календарь (фискальные месяцы) → явный справочник.

AdTech Blueprint (клики/показы/конверсии, атрибуция, аудитории/bitmap)

Модель и таблицы

Факты:

ad_impressions, ad_clicks, ad_conversions (event-вьюхи/таблицы).
agg_ad_minute_state — rollup: CTR/CVR/p95 и uniq пользователи.

Аудитории:

aud_active_daily_bitmap — bitmap-состояния по окну.

DDL (фрагменты):

CREATE TABLE agg_ad_minute_state
(
  minute DateTime,
  campaign_id UInt64,
  imp_state AggregateFunction(sum, UInt64),
  clk_state AggregateFunction(sum, UInt64),
  conv_state AggregateFunction(sum, UInt64),
  users_state AggregateFunction(uniqCombined, UInt64),
  latency_state AggregateFunction(quantileTDigest(0.95), Float64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(minute)
ORDER BY (minute, campaign_id);

Семантика: атрибуция и KPI

Last/position/time-decay — как VIEW (см. М7/M8). Пример простого last-touch:

CREATE OR REPLACE VIEW vw_attr_last_touch AS
WITH clicks AS (
  SELECT user_id, event_time, campaign_id FROM ad_clicks
),
conv AS (
  SELECT user_id, event_time AS buy_time, revenue FROM ad_conversions
)
SELECT c.user_id, c.buy_time, c.revenue,
       anyLast(cl.campaign_id) AS campaign_last
FROM conv c
LEFT JOIN clicks cl
  ON cl.user_id = c.user_id
 AND cl.event_time BETWEEN c.buy_time - INTERVAL 7 DAY AND c.buy_time
GROUP BY c.user_id, c.buy_time, c.revenue;

KPI:

CREATE OR REPLACE VIEW vw_ad_kpi_minute AS
SELECT minute, campaign_id,
       sumMerge(imp_state)  AS imps,
       sumMerge(clk_state)  AS clks,
       sumMerge(conv_state) AS convs,
       clks/NULLIF(imps,0)  AS ctr,
       convs/NULLIF(clks,0) AS cvr,
       quantileTDigestMerge(0.95)(latency_state) AS p95_ms
FROM agg_ad_minute_state
GROUP BY minute, campaign_id;

Аудитории/пересечения (bitmap):

CREATE TABLE aud_active_daily_bitmap
(
  day Date,
  campaign_id UInt64,
  users_bm AggregateFunction(groupBitmap, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, campaign_id);

DQ и инварианты

CTR/CVR ∈ [0,1].
Spend vs platform-report — ежедневный баланс в допуске (например, ≤0.5%).
Окно связывания кликов/конверсий — фиксированное (7/30 дней).

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

Дубликаты событий → идемпотентность ingestion, дедуп по (event_id) в STAGE.
Атрибуция «двойной учёт» → ровно одна активная модель на отчёт, версии v1/v2.
p95 «на лету» → только состояния TDigest.

DQ/наблюдаемость и дашборды (общий шаблон)

Таблицы наблюдаемости:

CREATE TABLE sem_meta (view_name String, updated_at DateTime)
ENGINE=MergeTree ORDER BY view_name;
CREATE TABLE dq_results
(
  test_name String, scope String, ts DateTime,
  status LowCardinality(String), value Float64, threshold Float64, details String
) ENGINE=MergeTree ORDER BY (test_name, ts);

Типовые тесты:

Freshness по sem_meta (SLA мин).
Balance vs CORE/GL/AdPlatform по ключевым метрикам.
Uniqueness зерна (day, shop_id, category_id …).
Плотность рядов (без «дыр»).
Инварианты по домену (Retail: refunds>=0; FinTech: «дебет=кредит»; SaaS: «нет перекрытий SCD2»; AdTech: CTR,CVR ∈ [0,1]).

Observability-дашборд:

Freshness, parts/merges backlog, replication lag;
Топ-запросы по read_bytes/duration;
Доля запросов с FINAL (должна быть 0).

BI-дашборды (минимум):

Retail: CR/AOV/Top-K, YoY/4-5-4, net vs gross.
FinTech: inflow/outflow, остатки на отчётные даты, сверка с GL.
SaaS: NRR/GRR, DAU/p95, когорты.
AdTech: CTR/CVR/Spend, атрибуция по моделям, аудитории.

Чек-лист внедрения блюпринта (для любого домена)

Настроен календарь (и 4-5-4 — для Retail), валюта (операция/отчёт).
Партиции и ORDER BY соответствуют типовым WHERE.
Тяжёлые метрики = …State/…Merge, без FINAL в VIEW.
DQ-набор зелёный (freshness, баланс, дубли, плотность, инварианты).
BI подключён к vw_*, стоят дефолт-фильтры (период/лимиты).
RBAC/RLS и маскирование включены (PII/тенанты).
CI/CD: линтеры (запрет FINAL/SELECT *), тесты, stage→prod flip.
Документация: паспорта метрик (YAML), lineage, changelog версий метрик.
Ретро-окно и ночной overlay описаны и автоматизированы.

Риски «разных правд» и как их погасить

Корень проблемы	Как проявляется	Митигация
Не зафиксирована формула метрики	В разных отчётах разные цифры	Паспорт метрики (YAML), VIEW как «истина», PR-процесс, changelog
Смешаны календарь/валюта	YoY «едет», финансы спорят	Разные VIEW: «операция/отчёт», григорианский/4-5-4
DISTINCT/перцентили «на лету»	Медленно/нестабильно	uniq/quantile State + …Merge, rollup-слой
ASOF без правильной сортировки	«Не та» цена/курс	SCD2 с ORDER BY (id, valid_from), pre-join для больших объёмов
Дубликаты событий	метрики «накручены»	Идемпотентность и дедуп в STAGE, quarantine + DQ «дубликаты»
Несогласованные версии атрибуции	ROAS/POAS не сходится	Ровно одна активная модель на отчёт, версии v1/v2 + регресс-сравнение

Кейсы «как есть»

Retail — «Промо-неделя, AOV упал»:
Диагностика: скидочные позиции выросли, возвраты D+3 увеличились.
Фикс: выделили промо-календарь в отдельный измеритель, отделили gross/net, включили ретро-окно 14→30 дней на период распродаж.

FinTech — «GL не сходится на 0.3%»:
Диагностика: FX «на дату отчёта» применялся к части операций с chargeback.
Фикс: разделили VIEW «операция/отчёт», задокументировали правило chargeback, ретро-пересчёт 30 дней.

SaaS — «NRR 108% в отчёте, но продукт видит 103%»:
Диагностика: expansions считались и в START, и в EXPANSION.
Фикс: паспорт метрики, корректные change_type, регресс-сравнение v1↔v2.

AdTech — «конверсий больше кликов»:
Диагностика: окно связывания было 30 дней, а часть конверсий приходила без клика (органика).
Фикс: last-touch 7 дней + отчёт «Unattributed», баланс-тест по spend.

Итог и что отдать команде

Вы получаете 4 рабочих «чертежа» под Retail/FinTech/SaaS/AdTech:

DDL/VIEW и метрики как код (YAML) — можно разворачивать «как есть».
Шаблоны DQ/наблюдаемости и дашборды (источники SQL).
Инструкции по ретро-окнам, атрибуции, 4-5-4, ASOF/FX, bitmap-аудиториям.
Чек-листы внедрения и антипаттерны.

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. Итог — быстрый запуск витрин за недели, снижённые риски в проде и предсказуемая стоимость владения.

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

Модуль 12. Capstone-проект: полный контур витрин (от CORE до BI)

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

Модуль 14. Стриминг и NRT-витрины на масштабе (Kafka→ClickHouse)