ETL-пайплайн для анализа выручки

Тестовое задание на позицию Data-инженера. Решение загружает CSV с заказами, очищает данные, рассчитывает выручку по дням и категориям и сохраняет результат в SQLite. Три SQL-запроса формируют рейтинг категорий, ежедневную динамику и сигналы аномальной выручки.

Стек: Python 3.12, SQLite, стандартная библиотека Python. Сторонние зависимости не требуются. Данные синтетические; фиксированный seed обеспечивает воспроизводимость.

Результат

В проект включены готовая база данных, отчет качества, реестр отказов и результаты SQL-запросов. Их можно изучить без повторного запуска.

Показатель	Значение
Входных строк	301
Принято	290
Отклонено	11
Выручка	991 193,95 ₽
Категорий	3
Дней в аналитическом календаре	30
Сигналов аномальной выручки	1

Показатели относятся к демонстрационному набору, а не к реальному бизнесу.

Быстрый запуск

Требуется Python 3.12 или новее; проверенная версия — 3.12.14. Распакуйте проект и выполните команды из его корневого каталога:

python scripts/generate_data.py --output run/raw/orders.csv --seed 42
python -m src.pipeline --input run/raw/orders.csv --db run/output/sales.sqlite --reports run/reports/etl
python sql/run_queries.py --db run/output/sales.sqlite --output run/reports/sql

Результаты появятся в каталоге run/: исходный CSV, база output/sales.sqlite, отчеты очистки в reports/etl/ и аналитические CSV в reports/sql/. Повторный запуск заменяет результаты по указанным путям. Готовые артефакты из корня проекта сохраняются.

Для обработки предоставленного примера вместо генерации укажите --input data/raw/orders.csv во второй команде. Если интерпретатор доступен как python3, замените имя команды. Особенности использованного окружения вынесены в протокол запуска.

Запуск тестов:

python -m unittest discover -s tests -v

Как устроен пайплайн

CSV → проверка структуры → очистка и дедупликация
    → факты и дневные агрегаты в SQLite → аналитические SQL → CSV

1. Загрузка. Чтение UTF-8 CSV и проверка заголовка. Каждая запись сохраняет связь с физическим номером строки исходного файла.
2. Очистка. Проверка обязательных полей, дат, количества и цены; нормализация строк; обработка повторов и конфликтов ключа.
3. Расчет. Выручка строки равна количеству, умноженному на цену. Цена разбирается через Decimal; дальнейшие расчеты и хранение используют целые копейки.
4. Запись. Факты и агрегаты по дню и категории заменяются в одной транзакции SQLite. После успешной записи публикуются отчеты качества.
5. Аналитика. Отдельный скрипт выполняет три SQL-запроса, открывая базу только для чтения, и сохраняет результаты в CSV.

Python и SQLite выбраны для локального воспроизводимого запуска без внешней инфраструктуры. Разделение фактов и агрегатов позволяет сверить итоговые суммы с очищенными строками заказов.

Данные и правила очистки

Единица учета — строка заказа, уникальный ключ — (order_id, line_id). Все поля CSV обязательны.

Поле	Назначение и допустимые значения
order_id	Непустой идентификатор заказа
line_id	Непустой идентификатор строки внутри заказа
order_date	Календарный день: YYYY-MM-DD, DD.MM.YYYY или YYYY/MM/DD
category	Непустая категория; пробелы и регистр нормализуются
quantity	Положительное целое количество
unit_price	Положительная цена в RUB; точка как разделитель, до двух дробных знаков

Крайние пробелы удаляются во всех полях. В категории последовательности пробелов заменяются одним, регистр приводится через casefold. Обязательные значения не восстанавливаются предположениями. Неоднозначные даты, например 01/02/2026, отклоняются. Время и часовой пояс не моделируются.

Нули, отрицательные значения, NaN, Infinity и значения за пределами допустимой арифметики int64 не принимаются. Переполнение общей выручки останавливает загрузку до изменения БД. Нарушенная структура CSV и многострочные поля также завершают запуск до записи.

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

Схема SQLite

Таблица	Первичный ключ	Содержание
facts	order_id, line_id	Дата, категория, количество, цена и выручка в копейках, номер исходной строки
daily_category	order_date, category	Выручка в копейках за день по категории

Подробные интерфейсы и правила описаны в контракте данных.

Контроль качества

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

Причина	Код в отчете	Строк
Не заполнено обязательное поле	missing_required	2
Недопустимая или неоднозначная дата	invalid_date	2
Некорректное количество	invalid_quantity	2
Некорректная цена	invalid_price	3
Повтор нормализованной записи	duplicate	1
Разные записи с одинаковым ключом	conflicting_key	1
Всего		11

Баланс сходится: 301 = 290 + 11. Отдельно учтены 290 принятых строк с исправленным представлением полей. Генератор намеренно добавляет пробелы в каждую категорию, поэтому нормализация затрагивает все принятые записи. Этот счетчик не увеличивает число отказов.

Аналитические запросы

Запрос	Правило	Результат
Топ категорий	Сумма выручки по категории, сортировка по убыванию	3 категории
Ежедневная динамика	Календарь от первой до последней даты, дни без продаж заполнены нулем, изменение к предыдущему дню	30 дней
Аномалии	Выручка дня превышает среднюю за предыдущие 7 календарных дней более чем втрое	1 сигнал

Лидер по выручке — книги: 399 048,85 ₽; далее электроника — 330 080,50 ₽ и дом — 262 064,60 ₽. В динамике 13 января отражено с нулевой выручкой; изменение для первого дня не рассчитывается.

Для поиска аномалий нужны семь дней истории и ненулевая базовая сумма; отсутствующие продажи считаются нулями. Выявлен сигнал 23 января 2026 года, выручка — 451 449,20 ₽. Всплеск намеренно заложен в синтетический генератор для демонстрации запроса. Аналитический сигнал сам по себе не означает ошибку исходных данных.

Проверка надежности

Шесть автоматических тестов прошли успешно. Они проверяют ручную фикстуру с ожидаемой выручкой 2080 копеек, правила очистки и ключей, причины отказов, числовые ограничения, повторную загрузку, пустой и полностью отклоненный набор, а также откат при искусственно вызванном сбое SQL-записи.

Идемпотентность проверяется сравнением отсортированных бизнес-данных после двух запусков, а не побайтовым сравнением SQLite-файлов. Обе таблицы обновляются в одной транзакции: ошибка записи сохраняет предыдущий результат. Пустой или полностью отклоненный вход намеренно заменяет прежний набор пустым.

Дополнительно проверены целостность SQLite, равенство агрегатов суммам фактов, воспроизводимость исходного CSV и отчетов, а также выполнение команд из распакованного комплекта. Доказательства: приемка и повторная проверка.

Работа с ИИ

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

Реализация и независимые тесты выполнялись параллельно, затем те же исполнители подготовили SQL и документацию. Первичная реализация с приемкой заняла 8 минут 33 секунды календарного времени. Последующая повторная проверка и редактура документации в этот интервал не входят. Это время работы команды ИИ, а не оценка трудозатрат человека.

Состав проекта

src/                       ETL и запись в SQLite
scripts/generate_data.py   Генератор синтетического CSV
tests/                     Независимые автоматические тесты
data/raw/orders.csv        Исходный пример
output/sales.sqlite        Готовая база данных
sql/                       Три запроса и скрипт их выполнения
reports/                   Качество, отказы и результаты SQL
coordination/              Контракт, решения, журнал ИИ и приемка
docs/                      Протоколы проверок и тезисы для собеседования
DATA_ENGINEER_PROMPT.md    Исходный промпт

Ограничения и развитие

Решение рассчитано на небольшую выгрузку, целиком помещающуюся в память, и использует полную замену данных. Валюта одна; возвраты и бесплатные позиции не моделируются. Разрешение конфликтов зависит от порядка строк.

БД и файловые отчеты не объединены общей транзакцией: при сбое публикации отчетов после записи БД требуется повторный запуск. Конкурентная публикация отчетов и большие объемы данных не проверялись.

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