SQL для аналитиков: как устроить корректные витрины (data mart) и избежать двойного учета

Витрина данных для аналитики — не просто «таблица для отчетов». Это договор между источниками данных, правилами трансформации и логикой метрик. Если договор сформулирован плохо, вы получаете классические симптомы: «размножение» пользователей, двойной учет заказов, скачки метрик между версиями отчета и невозможность объяснить, откуда взялось число.

В этой статье разберем практический подход к построению корректных витрин (data mart) на уровне SQL и архитектурных решений: как устроить слой очистки данных, как делать дедупликацию, как выполнять инкрементальные обновления и — главное — какие правила предотвращают повторное начисление метрик. Будет много конкретики: типовые схемы, паттерны и SQL-примеры, которые реально помогают удерживать метрики в консистентном состоянии.

Почему «двойной учет» — это не баг SQL, а сбой модели данных

Двойной учет возникает не только из-за неверного JOIN. Чаще проблема глубже:

1. Разная гранулярность. Например, одна таблица хранит события «клик», другая — агрегаты «день/пользователь/кампания». Если соединить их без контроля, события начнут «умножать» друг друга.
2. Неопределенные ключи сущностей. Если у пользователя нет стабильного идентификатора (или он меняется), вы получите несколько строк на одного человека в витрине.
3. Поздние и пересобираемые данные. В логах могут приходить события с задержкой или ретро-исправления. Если витрина пересчитывается «частично» и без правильной стратегии, учет может стать двойным.
4. Отсутствие идемпотентности трансформаций. Идемпотентность — свойство операции: повторный запуск приводит к тому же результату. Если витрина «добавляет» данные, не проверяя, были ли они уже учтены, вы получите размножение метрик.

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

Базовые принципы корректной витрины

Прежде чем писать сложные запросы, закрепите модель. Ниже — набор принципов, которые почти всегда работают.

Гранулярность: определите «зерно» (grain) каждой таблицы

Зерно — уровень детализации строки в таблице.

• dim_user — один ряд на пользователя: ключ user_id (или суррогатный ключ).
• fct_orders — один ряд на заказ: ключ order_id (или order_id + version).
• fct_user_events — один ряд на событие: ключ event_id.
• fct_user_daily_metrics — одна строка на (user_id, date, …).

Если зерно не определено и не закреплено, любая «умная» аналитика начнет умножать строки.

Идемпотентные обновления: витрина должна быть повторно применима

Любой инкрементальный пайплайн должен уметь:

• безопасно пересчитывать последние периоды,
• корректно обрабатывать поздние данные,
• не дублировать уже загруженное.

Дедупликация выполняется по событию, а не «после» джойнов

Самая частая ошибка: сначала соединить таблицы, а потом попытаться «убрать дубликаты». На самом деле дубликаты обычно возникают как раз из-за джойнов или некорректных ключей. Поэтому дедупликацию делайте на «входе» — до того, как данные начнут участвовать в умножающих объединениях.

Разделяйте сущности и факты

Обычно витрина состоит из:

• dim-таблиц (сущности): пользователи, устройства, кампании;
• fct-таблиц (факты): события, заказы, транзакции;
• агрегаций (по желанию): ежедневные/линейные метрики.

Если смешивать сущности и факты в одной «универсальной» таблице, появляется соблазн неправильно соединить данные и получить двойной учет.

Архитектура: слои витрины, которые предотвращают размножение метрик

Рассмотрим практическую архитектуру пайплайна, ориентированную на SQL.

Схема слоев

1. Raw → Staging

   • минимальные преобразования;
   • нормализация типов;
   • приведение ключей;
   • добавление техслужебных полей (например, ingested_at).

2. Cleaning / Dedup

   • очистка данных (валидация, фильтры);
   • дедупликация;
   • унификация идентификаторов сущностей.

3. Facts (fct)

   • формирование зерна факта (одна строка = один факт);
   • контроль уникальности ключа.

4. Dimensions (dim)

   • обработка медленно меняющихся атрибутов (если нужно);
   • связывание сущностей с фактами через стабильные ключи.

5. Mart / Metrics

   • агрегаты и витрина для отчетов;
   • метрики считаются поверх «чистых фактов», а не поверх сырых таблиц.

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

Слой очистки данных: что именно валидировать

Очистка не должна превращаться в «ручной фильтр». Она должна быть предсказуемой и проверяемой.

Примеры проверок для входных данных

1. Проверка ключей

   • order_id не должен быть NULL;
   • event_id должен существовать и быть уникален на уровне источника (или иметь прокси-ключ).

2. Согласованность типов и единиц измерения

   • суммы в одном валютном масштабе;
   • время в единой таймзоне и формате.

3. Фильтрация заведомо некорректных записей

   • отрицательные значения там, где они не ожидаются;
   • статусы вне справочника.

Ниже пример типового staging + cleaning на SQL (концептуально; синтаксис может отличаться в зависимости от СУБД).

-- staging_orders: минимальная нормализация
create table staging_orders as
select
  cast(order_id as bigint) as order_id,
  cast(user_id as bigint) as user_id,
  cast(status as varchar(32)) as status,
  cast(amount as numeric(18,2)) as amount,
  cast(currency as varchar(3)) as currency,
  cast(updated_at as timestamp) as updated_at,
  cast(event_time as timestamp) as event_time,
  cast(ingested_at as timestamp) as ingested_at
from raw.orders;

-- cleaning_orders: валидация и базовые фильтры
create table cleaning_orders as
select *
from staging_orders
where order_id is not null
  and user_id is not null
  and amount >= 0
  and currency in ('RUB','USD','EUR');

Важно: очистка не должна «ломать» идемпотентность

Если вы применяете фильтры, убедитесь, что они основаны на детерминируемых правилах. Иначе при пересборке одни и те же строки могут то проходить, то не проходить.

Дедупликация: как выбрать «правильную версию» записи

В витрине чаще всего приходится сталкиваться с двумя классами дубликатов:

1. Повтор одного и того же факта (например, одинаковый event_id пришел дважды).
2. Обновление факта (например, заказ пересобрали: статус поменялся, но order_id тот же).

Для корректного учета ключевой вопрос: какая запись считается истинной.

Паттерн 1: дедуп по event_id (когда факт атомарен)

Если событие должно быть уникальным по event_id, дедуп делается так:

with ranked as (
  select
    *,
    row_number() over (
      partition by event_id
      order by ingested_at desc, updated_at desc
    ) as rn
  from staging_events
  where event_id is not null
)
select
  -- берем последнюю версию события
  -- (или первую — зависит от вашего правила истины)
  *
from ranked
where rn = 1;

Здесь ключевая мысль: вы превращаете «множество версий» в «единую истину» до того, как начинаются джойны и агрегаты.

Паттерн 2: SCD/версии фактов по order_id (когда факт обновляется)

Если заказ может приходить много раз, но метрика должна считаться по актуальному состоянию, вы должны выбрать стратегию:

• Latest state: считать по последней версии (по updated_at).
• Event-sourced: учитывать все версии как отдельные события статуса (обычно это отдельная модель, не для «простого заказа»).

Для метрики «сколько заказов в статусе X» чаще подходит Latest state. Тогда:

with ranked as (
  select
    *,
    row_number() over (
      partition by order_id
      order by updated_at desc, ingested_at desc
    ) as rn
  from staging_orders
)
select
  order_id,
  user_id,
  status,
  amount,
  currency,
  event_time,
  updated_at
from ranked
where rn = 1;

Подводный камень: не путайте «дедуп» с «агрегацией»

Часто люди делают так: берут сырые данные, группируют по order_id, суммируют amount. Но если в сырых данных одна и та же запись повторилась дважды, сумма удвоится — вы задедупили не то. Дедупликация должна быть до суммирования, либо суммирование должно опираться на уже уникализированный факт.

Инкрементальные обновления: как не «слепить» дубликаты в времени

Инкрементальность — главный источник двойного учета в продакшене. Есть два популярных подхода:

1. Приложение изменений только по “новым” ключам
2. Ретроспективные пересчеты окна (backfill window)

На практике чаще выигрывает второй подход: вы пересчитываете последние N дней/часов, потому что именно там обычно появляются поздние данные и ретро-исправления.

Стратегия “recompute window” для витрины фактов

Предположим, event_time — время события, а updated_at — время изменения. Тогда:

• вы определяете окно updated_at >= now - interval '7 day' либо event_time >= now - interval '7 day';
• пересобираете только этот сегмент;
• обновляете витрину детерминированно (через MERGE или замену партиций).

Пример для SQL с MERGE (синтаксис усреднен):

-- Вход: staging_events_clean (уже прошла cleaning+dedup на уровне event_id)
-- Выход: fct_user_events с зерном event_id (1 событие = 1 строка)

merge into fct_user_events as t
using (
  select *
  from staging_events_clean
  where event_time >= (current_date - interval '14 day')
) as s
on t.event_id = s.event_id
when matched then update set
  t.user_id = s.user_id,
  t.event_time = s.event_time,
  t.event_type = s.event_type,
  t.updated_at = s.updated_at
when not matched then insert (
  event_id, user_id, event_time, event_type, updated_at
) values (
  s.event_id, s.user_id, s.event_time, s.event_type, s.updated_at
);

Почему это снижает риск двойного учета:

• если событие пришло повторно — event_id совпадает, и MERGE обновляет строку, а не вставляет новую;
• если событие пришло с задержкой и попало в “окно” — оно будет вставлено один раз.

Важное правило: ключ витрины должен быть ключом дедупа

Если вы дедупили по event_id, то именно event_id должен быть ключом в fct_user_events.
Если в факте ключ другой (например, user_id + event_time), то повторения во времени могут привести к коллизиям и двойному учету.

Правила против «размножения» метрик: что ломается чаще всего

Теперь перейдем к практикам, которые буквально предотвращают умножение рядов в метриках.

Правило 1: метрики считаются только поверх факт-таблиц с корректным зерном

Если вы посчитали метрику, например total_revenue, из fct_orders (зерно = order_id), то вы защищены от умножения, пока соблюдаете ключевые условия join’ов.

Плохой вариант: считать sum(amount) из staging или из таблицы, где несколько строк на один order_id.

Хороший вариант:

select
  date_trunc('day', event_time) as day,
  sum(amount) as revenue
from fct_orders
where status in ('paid','shipped')
group by 1;

Правило 2: любые JOIN к измерениям должны быть “один-к-одному” или “один-к-многим” только на правильной стороне

• fct нужно джойнть так, чтобы на стороне факта строки не умножались.
• dim должны быть дедуплены по ключу, иначе JOIN умножит факты.

Пример типичной ошибки:

-- dim_campaign не дедуплен, на один campaign_id может быть несколько строк
select
  c.campaign_name,
  count(*) as orders_count
from fct_orders o
join dim_campaign c
  on o.campaign_id = c.campaign_id
group by c.campaign_name;

Если dim_campaign содержит несколько версий (например, разные названия во времени), count(*) умножится.
Решение: либо использовать surrogate key актуальной версии, либо джойнть по ключу версии/датам.

Правило 3: при связывании медленно меняющихся атрибутов учитывайте время

Если вы делаете dim_user с атрибутами, меняющимися со временем (например, сегмент пользователя), то джойн по user_id без условия времени — почти гарантированный источник двойного учета.

Подход: SCD Type 2 и джойн по диапазону дат:

• в dim хранится valid_from, valid_to и user_dim_key;
• в факте — event_time (или время заказа);
• джойн делается по event_time внутри валидного интервала.

Пример:

select
  du.segment,
  count(*) as events_cnt
from fct_user_events fe
join dim_user_scd du
  on fe.user_id = du.user_id
 and fe.event_time >= du.valid_from
 and fe.event_time < du.valid_to
group by du.segment;

Правило 4: агрегаты не должны повторно суммировать уже агрегированные величины

Частая ловушка — построить fct_daily_metrics, потом джойнить его с чем-то и снова агрегировать сумму.

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

Практическая рекомендация: добавляйте метрики в витрину либо как:

• политика “складывания” (например, sum_revenue можно суммировать по дням),
• или “уникальные множества” (например, distinct_users — нельзя просто суммировать без учета пересечений).

Дизайн ключей и контроль уникальности: как сделать ошибки невозможными

Корректность витрины — это не только логика, но и инфраструктурные барьеры.

Используйте уникальные ключи там, где они есть

Если event_id уникален по смыслу, заведите в витрине constraint (если возможно) или хотя бы тест:

• в аналитическом контуре минимум: периодический контроль count(*) vs count(distinct event_id).

Пример тест-запроса:

select
  count(*) as total_rows,
  count(distinct event_id) as distinct_event_ids
from fct_user_events
where event_time >= current_date - interval '14 day';

Если разница есть — витрина не соблюдает зерно.

Тесты на «размножение» после JOIN

Для ключевых отчетов можно делать контрольные запросы: сравнить метрику до и после join.

Например: revenue из факта и revenue после join с размерностью:

-- базовая метрика
with base as (
  select sum(amount) as revenue
  from fct_orders
  where status = 'paid'
),
with_joined as (
  select sum(o.amount) as revenue_joined
  from fct_orders o
  join dim_user u on o.user_id = u.user_id
  where o.status = 'paid'
)
select *
from base, with_joined;

Если значения отличаются — где-то dim не “1-к-1” относительно ключа или вы нарушили время (для SCD).

Как организовать “дедуп + инкремент” без сложной магии: практический рецепт

Соберем все вместе в рабочий процесс.

Рецепт: формируем витрину заказов без двойного учета

Предположим, что из источника в raw.orders приходят строки, где один и тот же order_id может повторяться, а обновления отличаются updated_at.

1. staging_orders: нормализуем типы.
2. orders_dedup: выбираем “последнюю версию” по order_id.
3. fct_orders: делаем MERGE по order_id с пересчетом окна.

Пример:

-- 1) staging
create or replace temp table staging_orders as
select
  cast(order_id as bigint) as order_id,
  cast(user_id as bigint) as user_id,
  cast(status as varchar(32)) as status,
  cast(amount as numeric(18,2)) as amount,
  cast(currency as varchar(3)) as currency,
  cast(event_time as timestamp) as event_time,
  cast(updated_at as timestamp) as updated_at,
  cast(ingested_at as timestamp) as ingested_at
from raw.orders;

-- 2) dedup: latest version per order_id
create or replace temp table orders_dedup as
with ranked as (
  select
    *,
    row_number() over (
      partition by order_id
      order by updated_at desc, ingested_at desc
    ) as rn
  from staging_orders
  where order_id is not null
)
select *
from ranked
where rn = 1;

-- 3) increment + merge with recompute window
merge into fct_orders as t
using (
  select *
  from orders_dedup
  where event_time >= (current_date - interval '30 day')
) as s
on t.order_id = s.order_id
when matched then update set
  t.user_id = s.user_id,
  t.status = s.status,
  t.amount = s.amount,
  t.currency = s.currency,
  t.event_time = s.event_time,
  t.updated_at = s.updated_at
when not matched then insert (
  order_id, user_id, status, amount, currency, event_time, updated_at
) values (
  s.order_id, s.user_id, s.status, s.amount, s.currency, s.event_time, s.updated_at
);

Ключевой момент: order_id — это и ключ дедупликации, и ключ витрины.

Как избегать двойного учета в метриках “уникальные пользователи”

Двойной учет часто возникает не в суммах, а в COUNT(DISTINCT ...).

Почему? Потому что:

• distinct нельзя корректно “суммировать по кускам”, если вы делите на сегменты;
• при повторных версиях факта пользователь может попадать в несколько строк.

Тогда принцип такой:

1. Для distinct должен быть чистый факт с зерном, которое не дублирует одного пользователя в рамках “окна подсчета”.
2. Если факт — событие, то COUNT(DISTINCT user_id) может быть корректен только если события дедуплены по event_id и не порождают повторов одного и того же события.

Пример ежедневного активного пользователя:

select
  date_trunc('day', event_time) as day,
  count(distinct user_id) as active_users
from fct_user_events
where event_type = 'session_start'
  and event_time >= current_date - interval '30 day'
group by 1;

Если fct_user_events корректна (зерно=event_id, event_id уникален), то distinct будет стабильным.
Если нет — вы увидите “плавающие” метрики даже при неизменных отчетных запросах.

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

Когда метрика “не сходится” (например, в новой сборке выросло число заказов), вам нужны системные шаги.

Шаг 1: сравните агрегат на уровне фактов “до и после” витрины

• посчитайте count(distinct order_id) из staging
• посчитайте то же из fct_orders

Если расхождение большое — проблема в дедуп/ключах. Если расхождение небольшое — проблема может быть в фильтрах статусов или окне пересчета.

Шаг 2: проверьте уникальность ключа витрины

• count(*) vs count(distinct order_id) (или event_id)

Если ключ не уникален — значит витрина нарушает зерно.

Шаг 3: проверьте join’ы на “умножение”

Для каждого джойна проверьте:

• сколько строк в dim на один ключ факта;
• не нарушается ли SCD-логика.

Шаг 4: проверьте инкрементальность и окно пересчета

Если вы пересчитываете только “новые” строки и источник делает ретро-обновления, витрина начнет расходиться. Решение обычно одно: добавить recompute window и обеспечить идемпотентное применение через MERGE по ключу.

Типовые ошибки аналитиков и инженеров

1. Суммировать из таблицы, где зерно “шире”
   Пример: суммировать выручку из таблицы событий, где один заказ представлен несколькими строками за разные статусы.

2. Дедуп делать после JOIN
   Это приводит к “невидимым” умножениям строк — даже если вы потом примените distinct, метрика может быть уже искажена.

3. Джойн dim без контроля версий
   Особенно опасно для SCD Type 2 — без условий по времени пользовательские атрибуты будут пересекаться.

4. Инкремент без идемпотентности
   INSERT без MERGE или без проверки уникальности ключа почти гарантирует двойной учет при ретранах и пересборках.

5. “Разделили на периоды” и потом суммировали уникальные метрики
   distinct и пересечения не сохраняют аддитивность.

Резюме: чеклист корректной витрины

Если вы хотите построить data mart так, чтобы избежать двойного учета, держите этот чеклист:

• Определите зерно каждой таблицы (dim и fct).
• Сделайте очистку данных детерминированными правилами.
• Дедупликация выполняется до джойнов, по осмысленному ключу факта.
• Ключ витрины совпадает с ключом дедупа.
• Инкремент строится через recompute window и идемпотентное применение (например, MERGE).
• Джойны к dim не должны умножать факты (или должны быть версии по времени).
• Метрики считаются поверх чистых фактов; агрегаты не ломают семантику.
• Добавьте минимум базовых тестов: уникальность ключа, стабильность агрегатов, проверка join’ов.

Если хочется углубиться именно в SQL-паттерны (окна, дедуп, инкрементальные модели, инварианты зерна и практику построения надежных пайплайнов), полезно посмотреть специализированные материалы. Например, курс, посвященный этим темам, можно пройти как способ систематизировать подход и быстрее нарабатывать «мышечную память» для корректных витрин: вот ссылка на /course/.

Вывод

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

SQL здесь — не магия и не гонка за «самым сложным запросом». Это язык, на котором вы закрепляете инварианты данных: один факт — одна строка, один ключ — одна истина, инкремент — повторно применим. Именно эти принципы в итоге и удерживают метрики от «размножения» при росте данных и изменениях источников.