SQL-проверки качества данных: дубли, пропуски и поиск расхождений

Коротко

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

• Проверьте размер таблицы и период загрузки.
• Найдите NULL в ключевых полях и неожиданные пустые значения.
• Проверьте дубли на уровне бизнес-события.
• Найдите события без пользователя и платежи без связанной сущности.
• Сравните дневной объём с предыдущими днями и ожидаемым диапазоном.

Сначала зафиксируйте контракт данных

SQL-проверка имеет смысл только относительно ожидаемого правила. Для users одна строка — один пользователь, user_id уникален и заполнен. Для events одна строка — одно событие, user_id может быть анонимным только по согласованному правилу. Для payments сумма не должна быть отрицательной без отдельного типа возврата.

Запросы ниже выполняются на учебной базе из песочницы симулятора. События в ней чистые, поэтому там, где проверка ничего не находит, следом или разделом ниже идёт тот же запрос на испорченной загрузке: её строки собраны в CTE loaded, в базе их нет.

Проверка NULL и обязательных полей

Начните с полей, без которых метрика меняет смысл: user_id, event_time, event_name, amount. Не каждый NULL является ошибкой, но каждый должен иметь объяснение. Если user_id может быть анонимным, это правило нужно отразить в расчёте DAU, а не скрывать фильтром.

На учебной базе запрос возвращает 35 341 событие и три нуля. Пропуски есть в users.country: 55 строк из 4 613.

select
  count(*) as all_events,
  count(*) filter (where user_id is null) as missing_user_id,
  count(*) filter (where event_time is null) as missing_event_time,
  count(*) filter (where event_name is null) as missing_event_name
from events;

Дубли: одна строка или повтор события

Дубликат нужно определять по бизнес-ключу, а не только по полному совпадению строки. Для события это может быть event_id, либо комбинация user_id, дня события, event_name и session_id. Для payments лучше иметь отдельный payment_id. Если ключа нет, сначала договоритесь, что именно считается повтором.

На учебной базе первый запрос ничего не возвращает: каждое событие записано не чаще раза в день на человека. Во втором пачка за 29 августа, 10:00–10:05, загружена дважды, и проверка находит три ключа с rows_count = 2.

select
  user_id,
  cast(event_time as date) as event_day,
  event_name,
  count(*) as rows_count
from events
group by user_id, cast(event_time as date), event_name
having count(*) > 1
order by rows_count desc, user_id, event_day, event_name;

with loaded as (
  select event_id, user_id, event_time, event_name from events
  union all
  -- испорченная загрузка: пачка за 29 августа, 10:00–10:05, пришла второй раз
  select event_id, user_id, event_time, event_name from events
  where event_time >= timestamp '2026-08-29 10:00:00'
    and event_time < timestamp '2026-08-29 10:06:00'
)
select
  user_id,
  cast(event_time as date) as event_day,
  event_name,
  count(*) as rows_count
from loaded
group by user_id, cast(event_time as date), event_name
having count(*) > 1
order by rows_count desc, user_id, event_day, event_name;

Orphan rows: события без пользователя

Событие с user_id, которого нет в users, может быть нормальным для ещё не загруженного справочника, а может означать сломанную связь или неверный идентификатор. LEFT JOIN помогает найти такие строки, не теряя события из исходной таблицы.

На учебной базе первый запрос возвращает ноль строк. Во втором к событиям добавлены три строки с двумя user_id, которых нет в users, и проверка называет оба.

select
  e.user_id,
  count(*) as events_count
from events as e
left join users as u using (user_id)
where u.user_id is null
group by e.user_id
order by events_count desc, e.user_id;

with loaded as (
  select event_id, user_id, event_time, event_name from events
  union all
  -- испорченная загрузка: пользователей 900001 и 900002 в users нет
  values
    (35342, 900001, timestamp '2026-08-30 12:10:00', 'app_open'),
    (35343, 900001, timestamp '2026-08-30 12:40:00', 'report_created'),
    (35344, 900002, timestamp '2026-08-30 12:50:00', 'app_open')
)
select
  e.user_id,
  count(*) as events_count
from loaded as e
left join users as u using (user_id)
where u.user_id is null
group by e.user_id
order by events_count desc, e.user_id;

• Проверьте, не различаются ли типы user_id: integer и text.
• Сверьте задержку загрузки users и events.
• Отдельно обработайте anonymous_id, если он есть в продукте.
• Не удаляйте orphan rows до понимания причины.

Доля NULL важнее их количества, а уникальность проверяется дважды

Условный пример: в среду запрос из раздела про пропуски вернул 15 600 событий без user_id, в четверг — 81 600. Объём потока при этом не изменился: около 1,2 млн строк в оба дня. В абсолютных числах это «пропусков стало больше», в долях — рост с 1,3% до 6,8%, то есть впятеро. Сравнивать можно только доли: объём событий сам по себе ходит вверх-вниз по дням недели, и 15 600 пустых ключей на миллион строк и на три миллиона — разные новости.

Дневная метрика этот сдвиг почти не заметила. DAU считается как count(distinct user_id), строки с пустым ключом в подсчёт просто не попадают, а у большинства затронутых людей в тот же день были и другие события с заполненным ключом — уникальных пользователей в этот день стало меньше менее чем на процент. Зато сегментация по каналу и retention этой когорты уже неполные: у события без user_id нет ни источника, ни истории.

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

В запросе ниже поломка собрана на учебной базе: с 28 августа export_completed приходит без user_id. За 28 августа без ключа 21 строка из 690 (3,04%), все в одном событии. DAU по всем событиям — 575 вместо 586: у 11 из 21 затронутых других событий в этот день не было.

С уникальностью та же история про зерно. Проверка count(*) против count(distinct event_id) относится к сырому слою: она ловит повторную загрузку. Витрина может быть испорчена и при идеальном сыром слое. Пользователь, заходивший в один день с телефона, планшета и ноутбука, лежит в витрине user × day тремя строками, потому что при сборке в GROUP BY добавили платформу и зерно сменилось молча. Знаменатель activation rate у таких людей раздут втрое, хотя ни одно событие не задублировано.

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

with loaded as (
  select
    event_id,
    -- испорченная загрузка: с 28 августа export_completed приходит без user_id
    case
      when event_name = 'export_completed'
        and event_time >= timestamp '2026-08-28 00:00:00' then null
      else user_id
    end as user_id,
    event_time,
    event_name
  from events
)
select
  event_name,
  count(*) as rows_total,
  count(*) filter (where user_id is null) as rows_without_user,
  round(100.0 * count(*) filter (where user_id is null) / count(*), 2) as null_share_pct
from loaded
where event_time >= timestamp '2026-08-28 00:00:00'
  and event_time < timestamp '2026-08-29 00:00:00'
group by event_name
order by rows_without_user desc, event_name;

Скачки дневного объёма

Аномальный объём событий не всегда означает рост активности. Причиной может быть дубль загрузки, новый SDK, изменение названия события или задержка нескольких дней, которая пришла одной пачкой. Сначала агрегируйте объём по дню, затем сравните с предыдущим днём через LAG.

На учебной базе в результате 91 день, и больше чем вдвое объём меняется один раз: 30 августа 226 событий против 588 накануне. Причина — неполный день: выгрузка обрывается в 12:59.

with daily as (
  select
    cast(event_time as date) as event_day,
    count(*) as events_count
  from events
  group by cast(event_time as date)
), daily_with_previous as (
  select
    event_day,
    events_count,
    lag(events_count) over (order by event_day) as previous_count
  from daily
)
select
  event_day,
  events_count,
  previous_count,
  events_count - previous_count as change
from daily_with_previous
order by event_day;

Сверка метрики с независимым расчётом

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

На учебной базе активным действием взято app_open — единственное событие, которое у пользователя повторяется: за июнь запрос возвращает 30 строк, от 32 человек 1 июня до 232 — 30 июня.

select
  cast(event_time as date) as event_day,
  count(distinct user_id) as dau
from events
where event_name = 'app_open'
  and user_id is not null
  and event_time >= timestamp '2026-06-01 00:00:00'
  and event_time < timestamp '2026-07-01 00:00:00'
group by cast(event_time as date)
order by event_day;

DAU 10 240 против 10 080: расхождение локализуют по слоям

Условный пример: контрольный запрос дал 10 240 активных пользователей за 2 июля, дашборд показывает 10 080. Разница — 160 человек, 1,6%: достаточно мало, чтобы её захотелось списать на округление, и достаточно заметно, чтобы на планёрке спорили о двух слайдах. Первая реакция «у меня правильно, а в дашборде ошибка» обычно неверна: оба запроса считают ровно то, что в них написано, просто написано в них разное.

Сверка идёт сверху вниз по слоям, и на каждом фиксируются три числа: строк, уникальных пользователей, значение числителя. В нашем случае зазор распался на два разных шага. Очистка убрала 50 тестовых аккаунтов — это задокументированное правило, и 10 240 превращаются в 10 190 законно. Дашборд теряет ещё 110: в его фильтре выбраны три платформы из четырёх, а четвёртая появилась в трекинге только в июне. То есть половина расхождения живёт не в SQL, а в настройке визуализации.

Запрос ниже воспроизводит второй шаг на учебной базе: «дашборд» считает DAU по трём каналам, а paid_search запущен 1 июля, и в первую неделю июля теряется от 11 до 34 человек в день.

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

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

Заканчивается сверка названием слоя, а не словом «починили». Запишите, где сидела разница: в определении, в источнике, в фильтре, в расписании обновления или в самой визуализации. У расхождения появляется адрес, и в следующий раз спор занимает десять минут вместо дня.

with control as (
  select
    cast(event_time as date) as event_day,
    count(distinct user_id) as dau
  from events
  where event_name = 'app_open'
    and event_time >= timestamp '2026-07-01 00:00:00'
    and event_time < timestamp '2026-07-08 00:00:00'
  group by cast(event_time as date)
), dashboard as (
  -- так считает «дашборд»: в его фильтре три канала из четырёх
  select
    cast(e.event_time as date) as event_day,
    count(distinct e.user_id) as dau
  from events as e
  join users as u on u.user_id = e.user_id
  where e.event_name = 'app_open'
    and u.channel in ('organic', 'referral', 'partner')
    and e.event_time >= timestamp '2026-07-01 00:00:00'
    and e.event_time < timestamp '2026-07-08 00:00:00'
  group by cast(e.event_time as date)
)
select
  event_day,
  c.dau as control_dau,
  d.dau as dashboard_dau,
  coalesce(d.dau, 0) - coalesce(c.dau, 0) as delta
from control as c
full join dashboard as d using (event_day)
where coalesce(d.dau, 0) <> coalesce(c.dau, 0)
order by event_day;

Выручка разошлась на 24 800 ₽: дубль позиций, скидки, возвраты

На деньгах тот же приём выглядит убедительнее, потому что у каждого слоя есть сумма. В условном примере SQL за 2 июля даёт 137 300 ₽, финансовая система — 112 500 ₽. Разница 24 800 ₽, и одной формулой она не объясняется: внутри сидят два независимых механизма, и разбирать их нужно по очереди.

Первый шаг — количество заказов, а не сумма. Оплаченных заказов в обоих источниках 1 000, значит на уровне сущности ничего не потерялось и не задвоилось. Средний чек при этом 125 ₽ по финансовому отчёту и 137,3 ₽ по SQL: расхождение появилось не в заказах, а в том, что к ним присоединили.

Второй шаг — зерно. Запрос считал sum(o.amount) после соединения orders с order_items. У 98 заказов было по две позиции, их сумма 12 300 ₽ попала в итог дважды, и 125 000 превратились в 137 300. Деньги размножила не агрегатная функция, а связь один-ко-многим: строк стало больше, чем заказов, а SUM послушно сложил всё, что ему дали.

Третий и четвёртый шаги — бизнес-правила. Скидки 8 300 ₽ дают 116 700 ₽, возвраты 4 200 ₽ — ровно 112 500 ₽, то есть то, что показывает финансовая система. Здесь важно, где правило уже применено: если скидка вычтена в amount самого заказа, повторное вычитание уводит отчёт в другую сторону и расхождение меняет знак. Возврат тоже допускает два честных варианта: списывать его по дате возврата или относить к дате исходного заказа. Оба отчёта правильные, пока подписаны.

Пятый слой — дата, и он ломает сверку чаще остальных. created_at против paid_at: заказ, созданный 30 июня и оплаченный 1 июля, попадёт в июнь в одном отчёте и в июль в другом. На дневном срезе это похоже на случайный шум в пределах процента, на месячном — на устойчивое расхождение ровно на стыке периодов. Шестой слой, если в продукте несколько валют, — курс: на дату оплаты или на дату закрытия месяца.

Вывод у этой сверки организационный. У финансовой метрики есть владелец и официальное определение. SQL может объяснить разницу и назвать слой, в котором она возникла, но не должен молча предлагать своё понимание net revenue — иначе через месяц в компании будет две выручки и ни одной согласованной.

Чеклист перед публикацией отчёта

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

• Размер таблицы и свежесть последней даты ожидаемые?
• Ключи заполнены и не дублируются?
• Связи между таблицами не дают неожиданное число orphan rows?
• Дневной объём и доля событий по типам не скачут без объяснения?
• Контрольный расчёт метрики совпадает с дашбордом по определению?

Слой quality_checks для регулярного отчёта

Разовые запросы полезны для расследования, но регулярному отчёту нужен отдельный слой проверок. Соберите результат в таблицу или view, где одна строка — одна проверка за один запуск: имя проверки, дата среза, фактическое значение, ожидаемый диапазон и статус. Тогда дашборд может показать не только метрику, но и состояние входных данных.

Порог должен быть связан с процессом. Для свежести источника можно проверить, что последняя дата не старше двух часов. Для доли NULL допустим диапазон до 1%, а для уникальности user_id — ноль повторов. Не копируйте пороги из чужого проекта: они зависят от частоты загрузки, размера продукта и стоимости ошибки.

В запросе ниже такая таблица собрана в CTE из четырёх проверок учебной базы на срез 30 августа, 13:00 (в рабочем отчёте это время запуска). Три проверки зелёные, одна красная: страна пуста у 1,19% пользователей при условном пороге 1%.

Если проверка красная, отчёт не обязан исчезать. Иногда безопаснее показать последний подтверждённый срез с явной пометкой, чем заменить его неполными данными. Это решение должно быть заранее согласовано с владельцем дашборда, а не приниматься в момент инцидента.

with quality_check_results as (
  select
    'events: минут от последнего события до среза' as check_name,
    cast(
      extract(epoch from timestamp '2026-08-30 13:00:00' - max(event_time)) / 60
      as decimal(10, 2)
    ) as actual_value,
    0 as expected_min,
    120 as expected_max
  from events
  union all
  select 'events: событий без пользователя в users', count(*), 0, 0
  from events as e
  left join users as u on u.user_id = e.user_id
  where u.user_id is null
  union all
  select 'users: повторов user_id', count(*) - count(distinct user_id), 0, 0
  from users
  union all
  select
    'users: доля пустой страны, %',
    round(100.0 * count(*) filter (where country is null) / count(*), 2),
    0,
    1
  from users
)
select
  check_name,
  date '2026-08-30' as snapshot_date,
  actual_value,
  expected_min,
  expected_max,
  case
    when actual_value between expected_min and expected_max
      then 'ok'
    else 'alert'
  end as status
from quality_check_results
order by check_name;

Как отличить поломку данных от продуктового события

Скачок объёма или падение DAU требует двух параллельных проверок. Сначала проверьте pipeline: freshness, дубли, долю NULL и распределение event_name. Затем посмотрите на релизы, маркетинговые кампании и реальные изменения поведения. Нельзя объявлять багом любой необычный график, но нельзя и списывать поломку трекинга на активность пользователей.

Полезно сравнивать несколько независимых сигналов. Если DAU упал, но число сессий, оплат и серверных запросов осталось стабильным, проблема может быть в событии активности. Если все сигналы изменились одновременно после релиза, вероятность продуктового события выше. Данные не дают готовую причинность, но помогают правильно поставить расследование.

В итоговом отчёте пишите не только статус «данные плохие». Укажите, какая проверка сработала, какой диапазон ожидался, сколько строк затронуто и кто должен подтвердить следующий шаг. Так quality-check становится рабочим инструментом, а не набором тревожных красных ячеек.

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

Ревью чужого запроса начинается с вопроса, что такое одна строка результата

Коллега присылает запрос на шестьдесят строк с четырьмя CTE и число 4 812 — «активных пользователей за июль». Запрос выполняется, имена понятные, отступы аккуратные. Читать его построчно бессмысленно: ревью начинается с одного вопроса — что описывает одна строка результата. Если ответ звучит как «пользователя... ну или событие, смотря что там после join», дальше смотреть незачем, у числа нет определения.

Следом идут ещё два вопроса про то же зерно. Откуда в запросе DISTINCT: он убирает известные повторы или прикрывает размножение строк, которое автор не разобрал? И сохраняется ли зерно до конца — соединение с платежами после агрегации по пользователю превращает одну строку на человека в одну строку на платёж, а SUM в финальном SELECT аккуратно это замаскирует.

Логический блок короткий, но именно он даёт тихие ошибки. Границы периода: полуоткрытый интервал или BETWEEN по timestamp, который отрежет весь последний день, кроме полуночи. Место фильтра: условие по правой таблице в WHERE превращает LEFT JOIN в INNER и молча выкидывает строки без пары. Отрицания: NOT IN с подзапросом, где встречается NULL, вернёт пустой результат, а не «всех остальных».

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

Эксплуатационный блок решает, можно ли этому запросу доверять регулярно. На каком объёме его запускали, сколько он стоит при обновлении дашборда каждые пятнадцать минут, кто владелец числа и где лежит определение метрики. Разведочному запросу это всё не нужно, числу в KPI — нужно целиком.

И последний приём, который экономит больше всего времени: попросите автора назвать два контрольных значения, проверенных руками. Не итог, а что-то маленькое — один пользователь, один день, один заказ. Если таких значений нет, ревью сводится к «выглядит разумно», а 4 812 выглядят разумно при любой ошибке в пределах десятков процентов.

Мини-практика и следующий шаг

Соберите мини-аудит таблицы events: пропуски, дубли, orphan user_id и дневной объём. Затем сравните контрольный DAU с результатом из отчёта. В конце запишите, какие проверки можно запускать автоматически перед обновлением дашборда.