Качество данных: пропуски, дубликаты, поздние события

Понедельник, 9:00. Аналитик открывает недельный отчёт: воскресенье просело на 62 %. Через час продуктовая команда ищет сбой, дежурный инженер поднимает графики сервисов. К обеду выясняется, что не случилось ничего — половина событий воскресенья ещё не доехала, и в четверг тот же день выглядит обычным выходным. Ошибка была не в цифре: база показала ровно то, что в ней лежало. Ошибка была в выводе — из данных, которые физически не могли быть полными, сделали заключение, требующее полноты.

Качество данных для инженера — свойство таблиц: схема, свежесть, доля null, целостность строк; про это написано в Качество данных, контракты и governance. Качество данных для аналитика — свойство пары «данные + вопрос»: таблица с 8 % пропусков отлично отвечает на вопрос «какой город лидирует» и совершенно негодна для вопроса «выросла ли конверсия на один пункт». Поэтому здесь не чек-лист проверок пайплайна, а три семейства дефектов и для каждого — единственный важный вопрос: какой вывод оно убивает и убило ли оно ваш.

1. Дефект — это не про строки, а про смещение

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

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

Интуиция работает против этого различия. Доверительные интервалы, p-значения, размеры эффекта измеряют только шум; смещение они не видят и с ростом $n$ лишь плотнее сжимают интервал вокруг сдвинутой точки. Следствие, которое встретится в главе трижды: узкий доверительный интервал ничего не говорит о качестве данных. И опасны не громкие поломки, а молчаливые — когда загрузка зелёная, мониторинг доволен, все проверки пройдены, а ломается соответствие данных реальности.

2. Профиль данных: пятнадцать минут против недели

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

-- Профиль таблицы событий за 30 дней: один проход вместо пяти запросов
SELECT
    count(*)                                            AS rows_total,
    count(DISTINCT event_id)                            AS unique_event_ids,
    count(*) - count(DISTINCT event_id)                 AS duplicate_rows,
    round(100.0 * count(*) FILTER (WHERE user_id IS NULL)
          / NULLIF(count(*), 0), 2)                     AS pct_null_user_id,
    min(event_time)                                     AS first_event,
    max(event_time)                                     AS last_event,
    count(DISTINCT date_trunc('day', event_time))       AS days_covered
FROM events
WHERE event_time >= now() - interval '30 days';

duplicate_rows > 0 — идентификатор не уникален, и count(*) больше ничего не означает. days_covered < 30 — какие-то дни отсутствуют целиком; в недельной агрегации это выглядит как безобидный спад на 14 %. min/max ловят классику: данные «за месяц» начинаются с четвёртого числа, потому что трекинг включили не сразу. А pct_null_user_id сам по себе не значит ничего: важно не сколько пропусков, а чем строки с пропуском отличаются от остальных.

Отдельная привычка, ловящая больше дефектов, чем все автоматические проверки: сверка с внешним якорем. Если в витрине 4,17 млн рублей за день, а в биллинге 4,31 млн — дефект найден до того, как стал выводом; систематическую потерю 3 % заказов не увидит ни одна проверка внутри хранилища (Откуда берутся данные).

3. Пропуски: почему нельзя «просто выбросить строки с null»

3.1. Три механизма

Классификация Дональда Рубина (1976) — рабочий инструмент, потому что отвечает ровно на вопрос «что мне позволено сделать».

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

Левая ветка — главное на схеме: самый опасный пропуск не выглядит как null. Отсутствующая строка не попадёт ни в count(*) FILTER (WHERE ... IS NULL), ни в тесты dbt, ни в Great Expectations; её видно только по сравнению с внешним источником или ожидаемым числом строк.

3.2. Диагностика: сравнивать, а не считать

-- Строки с пропущенным источником ведут себя так же, как остальные?
-- Если нет — выбрасывать их нельзя ни при каких условиях.
SELECT
    (utm_source IS NULL)                                AS source_missing,
    count(*)                                            AS users,
    round(avg(orders_30d), 3)                           AS avg_orders,
    round(100.0 * avg((orders_30d > 0)::int), 2)        AS pct_converted,
    round(100.0 * avg((platform = 'android')::int), 2)  AS pct_android
FROM users_daily
WHERE snapshot_date = DATE '2026-07-15'
GROUP BY 1;

Три исхода. Строки с пропуском не отличаются ни по одному наблюдаемому признаку — гипотеза MCAR хотя бы не опровергнута. Отличаются по наблюдаемым признакам (80 % Android против 45 % в целом) — картина MAR: считаем в разрезе и взвешиваем. Отличаются по самой метрике (конверсия 3 % против 11 %) — красный флаг: агрегат описывает подмножество, отобранное по признаку, связанному с результатом.

3.3. Четыре приёма, один запрет и честная альтернатива

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

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

$$(1-m)\,\bar{y}{\text{obs}} + m \cdot y{\min} ;\le; \bar{Y} ;\le; (1-m)\,\bar{y}{\text{obs}} + m \cdot y{\max}$$

где $m$ — доля пропусков, $\bar{y}_{\text{obs}}$ — среднее по наблюдаемым. Пусть у 8 % пользователей событие оплаты не доезжает вовсе, а среди остальных 92 % конверсия 10,0 %. Тогда истинная конверсия лежит между $0{,}92 \cdot 0{,}10 = 9{,}2\ %$ и $9{,}2 + 8 = 17{,}2\ %$.

Интервал шириной восемь пунктов выглядит пораженчески — и потому полезен: если вы ищете эффект в один пункт, эта арифметика говорит, что вопрос не решается никакими статистическими средствами. Не потому что мало данных, а потому что способ сбора не позволяет. Границы сужают не подстановками, а допущениями, произнесёнными вслух: «предположим, среди пропущенных конверсия не выше, чем среди наблюдаемых» — и верхняя граница схлопывается до 10 %. Допущение это ваше, а не данных.

3.4. Объём не спасает: парадокс больших данных

Сяо-Ли Мэн (2018) показал, что ошибка среднего по $n$ доступным строкам из популяции $N$ раскладывается тождественно:

$$\bar{y}{n} - \bar{Y}{N} ;=; \rho_{R,Y} \cdot \sqrt{\frac{N-n}{n}} \cdot \sigma_{Y}$$

Здесь $\rho_{R,Y}$ — корреляция между фактом попадания строки в данные и значением метрики, $\sigma_Y$ — стандартное отклонение метрики: первый множитель отвечает за качество данных, второй — за количество, третий — за изменчивость величины. При доле доступных строк $f = n/N$ отсюда следует эффективный размер выборки — сколько честных случайных наблюдений стоят ваши $n$ смещённых строк:

$$n_{\text{eff}} ;=; \frac{f}{\rho^{2}\,(1-f)}$$

Числа. В приложении 2 млн активных пользователей за неделю, события доезжают от 1,6 млн ($f = 0{,}8$): блокировщики, отказ от трекинга, старые версии, офлайн. Конверсия по наблюдаемым 10 %, то есть $\sigma_Y = \sqrt{0{,}1 \cdot 0{,}9} = 0{,}3$. Пусть разрешившие трекинг покупают чуть чаще и $\rho = 0{,}02$ — величина, которую глазами не заметить. Смещение тогда равно $0{,}02 \cdot \sqrt{0{,}2/0{,}8} \cdot 0{,}3 = 0{,}003$, то есть 0,3 пункта, а доверительный интервал по 1,6 млн строк — всего $\pm 1{,}96 \cdot 0{,}3/\sqrt{1{,}6 \cdot 10^{6}} \approx \pm 0{,}05$ пункта: смещение в шесть раз шире всего интервала, а $n_{\text{eff}} = 0{,}8/(0{,}0004 \cdot 0{,}2) = 10\ 000$.

Полтора миллиона строк дают точность десяти тысяч — и рисуют интервал, соответствующий полутора миллионам. Реальный случай задокументирован в Nature: панель Delphi–Facebook опрашивала 250 тысяч человек в неделю о вакцинации и завышала охват примерно на 17 пунктов относительно данных CDC, тогда как опрос Axios-Ipsos на тысяче человек с честной процедурой отбора попадал в цель, а эффективный размер выборки Delphi–Facebook авторы оценили меньше чем в десяток наблюдений (Bradley et al., 2021). Правило: прежде чем радоваться размеру данных, спросите, кто в них не попал и связано ли это с тем, что вы измеряете.

4. Дубликаты: тихое умножение

4.1. Откуда берутся

Дубликаты — не признак плохих инженеров, а следствие устройства доставки: сети ненадёжны, и почти любая очередь даёт гарантию at-least-once.

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

4.2. Найти, понять природу, схлопнуть

-- Есть ли дубликаты по бизнес-ключу и одинаковы ли копии
SELECT order_id,
       count(*)                      AS copies,
       count(DISTINCT amount_minor)  AS distinct_amounts,
       min(ingested_at)              AS first_seen,
       max(ingested_at)              AS last_seen
FROM payments_raw
WHERE event_date = DATE '2026-07-15'
GROUP BY order_id
HAVING count(*) > 1;

distinct_amounts = 1 — технический дубликат, копии совпадают, схлопывать безопасно. distinct_amounts > 1 — под одним order_id лежат разные операции: это не дубликат, а сломанный идентификатор, и схлопывание уничтожит реальные платежи. Разрыв между first_seen и last_seen подсказывает механизм: секунды — ретрай, сутки — перезапуск загрузки.

-- Одна строка на бизнес-ключ; побеждает последняя по времени приёма.
-- event_id в ORDER BY — детерминированный тай-брейк: без него строки
-- с одинаковым ingested_at получат произвольные ранги, и повторный
-- запуск отчёта даст другое число.
WITH ranked AS (
    SELECT p.*,
           row_number() OVER (PARTITION BY order_id
                              ORDER BY ingested_at DESC, event_id DESC) AS rn
    FROM payments_raw p
    WHERE event_date BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'
)
SELECT * FROM ranked WHERE rn = 1;

В ClickHouse, BigQuery, Snowflake и DuckDB то же пишется короче через QUALIFY row_number() OVER (...) = 1; в PostgreSQL такого синтаксиса нет, нужен CTE. Оконные функции подробно — в SQL для анализа.

4.3. Fan-out: дубликаты, которые создаёте вы сами

Заказ на 5 000 рублей с тремя позициями после JOIN order_items превращается в три строки по 5 000 рублей: выручка растёт ровно на среднее число позиций. Запрос не падает, значения правдоподобны — просто «выручка немного выше, чем в биллинге».

-- НЕВЕРНО: сумма заказа умножается на число позиций
SELECT sum(o.total_minor) AS revenue_minor
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.created_at >= DATE '2026-07-01';

-- ВЕРНО: агрегировать правую таблицу ДО соединения
SELECT sum(o.total_minor) AS revenue_minor, sum(i.items_cnt) AS items_total
FROM orders o
JOIN (SELECT order_id, count(*) AS items_cnt FROM order_items GROUP BY order_id) i
  ON i.order_id = o.order_id
WHERE o.created_at >= DATE '2026-07-01';

-- Симметричная проверка: сколько строк INNER JOIN, наоборот, потерял бы
SELECT count(*) FILTER (WHERE s.store_id IS NULL)           AS orphan_orders,
       sum(o.total_minor) FILTER (WHERE s.store_id IS NULL) AS orphan_revenue_minor
FROM orders o
LEFT JOIN stores s ON s.store_id = o.store_id;

Привычка, закрывающая почти весь класс: перед каждым join проверять уникальность ключа справа (SELECT count(*), count(DISTINCT user_id) FROM user_profiles — разные числа означают размножение), а после join считать сирот. Если сирот заметно больше нуля, ваш «отчёт по всем заказам» — на деле отчёт по магазинам, попавшим в справочник, а попадание туда связано с чем-то содержательным.

4.4. Что дубликаты делают с выводом

Пусть доля лишних копий в числителе метрики — $d_p$, в знаменателе — $d_s$. Тогда

$$\widehat{CR} ;=; CR \cdot \frac{1 + d_{p}}{1 + d_{s}}$$

Симметричные дубликаты почти безобидны: множители сокращаются, и в A/B-тесте одинаковое загрязнение обеих групп сдвигает уровни, но не отношение. Асимметричные убивают вывод. Задвоение 3 % покупок при чистых сессиях превращает истинные 10 % конверсии в наблюдаемые 10,3 % — терпимо. Но если ретраи чаще случаются на медленных соединениях, а тестируемое изменение как раз влияет на скорость загрузки, доля дубликатов различается между группами — и вы измеряете разницу в дубликатах, а не в поведении. Поэтому в A/B-тестах дедупликация делается до разбиения на группы.

5. Поздние события: время, которого у вас два

5.1. Event time против processing time

У каждого события минимум две отметки: event time — когда действие произошло у пользователя, и processing time (ingested_at) — когда строка появилась в хранилище. Разрыв бывает от миллисекунд до недель: приложение работало офлайн, шлюз подтвердил асинхронно, инженер запустил backfill за месяц. Отчёт по event time содержательно правилен («сколько покупок сделали в воскресенье»), но нестабилен: вчерашнее число завтра вырастет. Отчёт по processing time стабилен, но неверен: он складывает воскресные покупки, доехавшие во вторник, с вторничными. Обе схемы легальны; смертельно — не знать, какая у вас, и сравнивать числа из разных. Механика окон и водяных знаков — в Потоковой обработке.

5.2. Эффект последнего дня

Это самый частый способ, которым дашборд обманывает честными данными: правый край любого графика по event time систематически занижен, потому что для свежих суток прошло меньше времени, чем нужно для полной доставки, а глаз читает край как тренд. Три способа не попасться, в порядке предпочтения: отсечка — не показывать сутки, пока не истёк срок дозагрузки; явная маркировка — рисовать неполные сутки штриховкой с подписью «данные неполные»; корректировка на полноту — делить наблюдаемое на историческую долю доехавшего при явно названном допущении, что задержка не связана со значением метрики. Худший вариант — тот, что стоит по умолчанию: рисовать как есть и надеяться, что читатель помнит про задержку. Он не помнит. Про другие способы графика соврать — Визуализация.

5.3. Измерить полноту

Отсечку выбирают не по словам инженеров, а по измеренному профилю задержки.

-- Кривая полноты: какая доля событий доехала за N часов после совершения
WITH lags AS (
    SELECT
        floor(extract(epoch FROM ingested_at - event_time) / 3600)::int AS lag_hours,
        count(*)                                                        AS events
    FROM events
    WHERE event_time >= DATE '2026-07-01'
      AND event_time <  DATE '2026-07-08'
    GROUP BY 1
)
SELECT
    lag_hours,
    events,
    round(100.0 * events / sum(events) OVER (), 2)                      AS pct,
    round(100.0 * sum(events) OVER (ORDER BY lag_hours ROWS UNBOUNDED PRECEDING)
          / sum(events) OVER (), 2)                                     AS pct_cumulative
FROM lags
ORDER BY lag_hours;

Типичный ответ: 71 % за 6 часов, 86 % за 12, 95 % за 24, 99,2 % за 48. Ждать двое суток ради 99,2 % или публиковать через сутки, зная про 5 % недоучёта, — решение продукта, но принимается оно на числах. Стабильный отчёт получается одним условием (WHERE event_time < date_trunc('day', now()) - interval '2 days'), а если публиковать надо раньше, наблюдаемое за неполные сутки делится на полноту: при 0,86 это множитель $1/0{,}86 \approx 1{,}16$, то есть плюс 16 % — оценка, а не факт. Полноту при этом считают в разрезах: если за 12 часов доезжает 78 % событий Android и 93 % iOS, любой отчёт по свежим суткам преувеличивает долю iOS.

5.4. Жизненный цикл суток и пересчёт истории

Ценность схемы — в предпоследнем переходе. Данные в аналитике не неизменны: история переписывается регулярно, и это нормально; ненормально переписывать её молча. Минимум гигиены: у витрины есть версия, у пересчёта — запись в журнале, у пользователей — уведомление. Инженерная сторона — в Моделировании данных.

6. Соседи по классу

Тайм-зоны. События хранятся в UTC, отчёт нужен в местном времени; три часа разницы перекладывают вечерний трафик в соседние сутки, и «ночной пик» оказывается артефактом. Рядом ловушка недель: date_trunc('week', ...) в PostgreSQL начинает неделю с понедельника, а многие BI-инструменты — с воскресенья.

-- Сутки по Москве, а не по UTC
SELECT date_trunc('day', event_time AT TIME ZONE 'Europe/Moscow') AS msk_day, count(*)
FROM events
WHERE event_time >= TIMESTAMPTZ '2026-07-01 00:00+03'
  AND event_time <  TIMESTAMPTZ '2026-08-01 00:00+03'
GROUP BY 1 ORDER BY 1;

Молчаливая смена семантики. Поле amount было в копейках, стало в рублях: схема та же, тип тот же, загрузка зелёная, выручка упала в сто раз. Или у status появилось значение pending_review, ваш WHERE status = 'paid' его не учитывает, и конверсия тихо просела на 4 %.

Не-люди в данных. Боты, нагрузочные тесты, внутренние аккаунты, мониторинг доступности дают идеально валидные строки; признаки — ровный интервал между событиями, отсутствие ночного спада, один user-agent. В B2B-продуктах служебный трафик легко даёт десятки процентов «активности».

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

7. От дефекта к выводу: что нельзя утверждать

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

8. Как встроить в работу

Проверка живёт в запросе, а не в голове. Минимальный автоматический набор — уникальность бизнес-ключа, доля пропусков по ключевым полям, число дней в периоде, сходимость с внешним якорем — через тесты dbt, Great Expectations или Soda; важно не какой инструмент, а что проверка выполняется сама и падает громко.

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

Дашборд обязан сообщать о своей неполноте. Тот, что показывает в понедельник утром несуществующий провал, обучает команду не смотреть на дашборды; про остальные причины — Дашборды.

Дефект — повод изменить сбор, а не только запрос. Если 8 % пропусков не дают ответить, надо не изобретать подстановку, а договориться о новом событии; техника формулирования требований — в Системном анализе.

9. Типичные ошибки

1. Считать долю null мерой качества. Ноль пропусков ничего не значит, если 20 % событий не доехало вовсе: отсутствующая строка не имеет null.
2. Заполнять пропуски средним. Сужает интервалы и производит ложную уверенность.
3. Верить узкому доверительному интервалу. Он измеряет только случайность и слеп к смещению.
4. SELECT DISTINCT вместо разбора причины. Механизм остаётся, а копии, отличающиеся временем приёма, DISTINCT вообще не схлопнет.
5. Читать тренд по последней точке графика. Правый край почти всегда занижен.
6. Сравнивать периоды до и после изменения трекинга. Скачок метрики в день релиза аналитики — это релиз аналитики; сюда же — молчаливый пересчёт истории.
7. Не проверять уникальность ключа перед join и отбрасывать выбросы, не разобравшись: заказ на 4 млн рублей — это ошибка ввода или ваш крупнейший клиент, см. Распределения.

10. Мини-итог

• Качество данных — свойство пары «данные и вопрос», а не таблицы; шум лечится объёмом, смещение — нет, а статистические инструменты измеряют только шум.
• MCAR можно выбросить, MAR — взвесить, MNAR — только ограничить сверху и снизу; механизм определяется знанием о сборе, а не анализом самих данных. Самый опасный пропуск — отсутствующая строка: она не выглядит как null и ловится только сверкой с независимым источником.
• Дубликаты неизбежны при at-least-once: симметричные искажают абсолюты, асимметричные — сравнения, дедупликация обязана быть детерминированной. Fan-out создаёт сам аналитик, INNER JOIN тихо теряет строки.
• Поздние события делают правый край графика систематически заниженным: измерьте кривую полноты и выберите отсечку, маркировку или корректировку с названным вслух допущением.
• «Эти данные не отвечают на этот вопрос» — профессиональный вывод, а не признание поражения.

Источники

• Donald B. Rubin. Inference and Missing Data. Biometrika, 1976 — doi:10.1093/biomet/63.3.581. Оригинал классификации MCAR / MAR / MNAR; развёрнуто — Little & Rubin, Statistical Analysis with Missing Data, Wiley, 2019.
• Charles F. Manski. Partial Identification of Probability Distributions. Springer, 2003.
• Xiao-Li Meng. Statistical Paradises and Paradoxes in Big Data (I). Annals of Applied Statistics, 2018 — doi:10.1214/18-AOAS1161SF.
• Valerie C. Bradley et al. Unrepresentative big surveys significantly overestimated US vaccine uptake. Nature 600, 2021 — doi:10.1038/s41586-021-04198-4.
• Tyler Akidau. Streaming 101: The world beyond batch — oreilly.com, и PostgreSQL: Window Functions.

Что дальше

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

SQL для анализа: агрегаты, окна, воронки