Обнаружение нарушений качества данных

Введение

Измерение completeness (урок 2) даёт общую картину. Но для исправления проблем нужна детализация: какие именно записи нарушают правила качества? В этом уроке мы напишем SQL-запросы для систематического обнаружения нарушений по всем шести измерениям.

Типы нарушений

Data Quality Rules (правила качества данных) определяют допустимые значения, форматы и связи. Каждое нарушение правила — это violation. Типы нарушений:

Hands-On Lab: Quality Lab

Practice these concepts with an intentionally dirty dataset and Great Expectations:

cd labs/quality && cp .env.example .env && docker compose up -d --build

Open JupyterLab at http://localhost:28888 and complete Notebook 01: Data Profiling — explore a dirty e-commerce dataset and discover quality violations using SQL queries.

Requirements: Docker Desktop with 4+ GB RAM allocated. See labs/quality/README.md for full setup.

SQL-запросы для обнаружения нарушений

1. NULL в обязательных полях

-- Найти записи с NULL в required columns
SELECT order_id, 'customer_id is NULL' AS violation
FROM orders WHERE customer_id IS NULL
UNION ALL
SELECT order_id, 'status is NULL' AS violation
FROM orders WHERE status IS NULL
UNION ALL
SELECT order_id, 'amount is NULL' AS violation
FROM orders WHERE amount IS NULL;

2. Дубликаты primary key

-- Найти дублирующиеся order_id
SELECT order_id, COUNT(*) AS duplicate_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

3. Отрицательные суммы

-- Суммы должны быть >= 0
SELECT order_id, amount
FROM orders
WHERE amount < 0;

4. Temporal violations

-- created_at не может быть позже updated_at
SELECT order_id, created_at, updated_at
FROM orders
WHERE created_at > updated_at;

5. Невалидные значения из справочника

-- Статус заказа должен быть из фиксированного списка
SELECT order_id, status
FROM orders
WHERE status NOT IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled');

6. Orphaned records (referential integrity)

-- Заказы без существующего клиента
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL AND o.customer_id IS NOT NULL;

Агрегированный отчёт о нарушениях

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

-- Агрегированный violation report
WITH violations AS (
  SELECT 'null_required' AS type, COUNT(*) AS cnt
  FROM orders WHERE customer_id IS NULL OR status IS NULL OR amount IS NULL
  UNION ALL
  SELECT 'duplicate_id', COUNT(*) - COUNT(DISTINCT order_id)
  FROM orders
  UNION ALL
  SELECT 'negative_amount', COUNT(*)
  FROM orders WHERE amount < 0
  UNION ALL
  SELECT 'temporal', COUNT(*)
  FROM orders WHERE created_at > updated_at
  UNION ALL
  SELECT 'invalid_status', COUNT(*)
  FROM orders WHERE status NOT IN ('pending','confirmed','shipped','delivered','cancelled')
)
SELECT type, cnt AS violation_count,
       ROUND(100.0 * cnt / (SELECT COUNT(*) FROM orders), 2) AS pct_of_total
FROM violations
WHERE cnt > 0
ORDER BY cnt DESC;

Библиотека проверок

Опытные команды создают библиотеку правил — набор повторно используемых проверок:

QUALITY_RULES = {
    "not_null": lambda col: f"SELECT COUNT(*) FROM orders WHERE {col} IS NULL",
    "unique": lambda col: f"SELECT {col}, COUNT(*) FROM orders GROUP BY {col} HAVING COUNT(*) > 1",
    "positive": lambda col: f"SELECT COUNT(*) FROM orders WHERE {col} < 0",
    "in_set": lambda col, vals: f"SELECT COUNT(*) FROM orders WHERE {col} NOT IN ({','.join(repr(v) for v in vals)})",
}

В следующих уроках мы увидим, как dbt и Great Expectations предоставляют готовые библиотеки проверок “из коробки”.

Сценарий: ДатаТех

Сценарий: DataTech Solutions (ДатаТех Солюшенз)

Алексей запустил полный аудит таблицы orders (200,000 записей в PostgreSQL). Результат:

Тип нарушения	Количество	% от total
NULL в required полях	3,200	1.6%
Дублирующиеся order_id	450	0.2%
Отрицательные суммы	180	0.09%
created_at > updated_at	95	0.05%
Невалидный status	12	0.006%
Orphaned records	1,500	0.75%

Всего: 5,437 нарушений (2.7% записей). Наибольшая проблема — NULL в required полях (3,200) и orphaned records (1,500). Orphaned records — следствие удаления клиентов без каскадного удаления заказов.

Итоги

• Нарушения качества классифицируются по измерениям: completeness, uniqueness, validity, accuracy, consistency
• SQL-запросы для обнаружения: NULL checks, GROUP BY HAVING, range checks, NOT IN, LEFT JOIN WHERE NULL, temporal comparisons
• Агрегированный отчёт показывает масштаб проблем и приоритеты исправления
• Библиотека правил — повторно используемые проверки для стандартизации
• Orphaned records — частая проблема при отсутствии FK constraints

В следующем уроке мы разберём дедупликацию — обнаружение и устранение дубликатов, одну из самых сложных задач Data Quality.