Качество данных и сверки

Загрузка, которая отработала без ошибок, не означает, что данные верны. Дубли, сироты, потерянный день, статус в другом регистре не роняют пайплайн. Их находят проверки, и эти проверки пишутся на SQL. В dbt они называются тестами, в Great Expectations ожиданиями, суть одна: запрос, который возвращает ноль строк, если всё хорошо.

Пять проверок каждой витрины

Уникальность ключа. Зерно таблицы соблюдено, если ключ не повторяется:

SELECT order_id, count(*)
FROM fact_orders
GROUP BY order_id
HAVING count(*) > 1;

Для orders результат пуст, для stg_orders по ключу «заказ и версия» находятся 254 дубля, для events по event_id 572. Это первая проверка, потому что дубли ломают все остальные цифры.

Обязательные поля. count(*) FILTER (WHERE total_amount IS NULL) даёт 84 заказа без суммы. Проверка не всегда означает «должно быть ноль»: промокод отсутствует у 6436 заказов, и это нормально. Порог задаёт бизнес-правило, а проверка следит, чтобы доля не менялась внезапно.

Допустимые значения. Статус обязан быть одним из шести известных:

SELECT status, count(*)
FROM orders
WHERE status NOT IN ('new', 'paid', 'shipped', 'delivered', 'cancelled', 'refunded')
GROUP BY status;

Находятся 63 строки в верхнем регистре. Похожая проверка для формата: email, не подходящий под шаблон адреса, 22 штуки с пробелом и заглавными буквами.

Ссылочная целостность. Внешних ключей в хранилище нет, поэтому сироты ищутся anti join: 84 заказа без клиента, 27 позиций без товара, 25 платежей без заказа. Такая проверка защищает от отчётов, в которых суммы «не сходятся» из-за INNER JOIN.

Свежесть и полнота. Данные должны приходить вовремя и целиком:

SELECT table_name,
       max(load_date) FILTER (WHERE status = 'success') AS last_ok,
       count(*) FILTER (WHERE status = 'failed')        AS failed,
       count(*) FILTER (WHERE status = 'running')       AS running
FROM load_log
GROUP BY table_name;
-- events     | 2026-06-29 | 5  | 1
-- payments   | 2026-06-30 | 19 | 0
-- stg_orders | 2026-06-30 | 17 | 0

События отстают на день, и одна загрузка висит в статусе running. Пропуски в датах находят сравнением с полным календарём: generate_series порождает все даты периода, а NOT EXISTS оставляет те, за которые записи в журнале нет.

Аномалии объёма

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

SELECT load_date, rows_loaded,
       avg(rows_loaded) OVER (ORDER BY load_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS avg_prev_7
FROM load_log
WHERE table_name = 'stg_orders' AND status = 'success';

Строки, где rows_loaded заметно меньше среднего за прошлую неделю, например ниже 70%, стоят внимания. Рамка 7 PRECEDING AND 1 PRECEDING намеренно исключает текущую строку из среднего. Порог это эвристика: для стабильных источников его ужесточают, для сезонных смотрят на ту же неделю прошлого года.

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

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

SELECT order_id, total_amount FROM orders WHERE total_amount IS NOT NULL
EXCEPT
SELECT order_id, round(sum(quantity * unit_price * (1 - coalesce(discount, 0))), 2)
FROM order_items GROUP BY order_id;

Результат: строки, которые есть слева и отсутствуют справа. Сверять нужно в обе стороны, потому что EXCEPT несимметричен: 32 заказа расходятся с позициями по сумме, а 75 расчётов по позициям не находят пары в заказах, это заказы без суммы и заказы, которых нет вовсе. FULL JOIN с условием IS DISTINCT FROM даёт то же за один запрос и показывает обе стороны рядом.

Сверка stg_orders с orders по последней версии возвращает ноль расхождений, сверка load_log.rows_loaded с фактическим числом строк за дату тоже. Это те гарантии, которые заложены в базу, и проверки подтверждают их.

Для больших таблиц построчная сверка дорога. Тогда сравнивают агрегаты по партициям: число строк, сумма ключевой меры, count(DISTINCT ключ) за каждый день с обеих сторон. Расхождение локализует день, а построчно сверяют уже только его.

Отчёт о качестве одним запросом

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

SELECT 'orders_without_customer' AS check_name, count(*) AS failed_rows
FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id)
UNION ALL
SELECT 'orders_null_amount', count(*) FROM orders WHERE total_amount IS NULL
UNION ALL
SELECT 'orders_bad_status', count(*) FROM orders WHERE status <> lower(status)
UNION ALL
SELECT 'events_duplicate_id', count(*) - count(DISTINCT event_id) FROM events;
-- 84 | 84 | 63 | 572

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

Практика