План реализации ETL

Модель данных

Одна запись CSV описывает один завершённый заказ с одним товаром. Возвраты, скидки, бесплатные товары и несколько валют вне модели. Все пять колонок обязательны в заголовке. Пустое значение допустимо только в category.

Поле	Принятое решение
`order_id`	Положительное целое число. После разбора ведущие нули не влияют на идентичность заказа.
`order_date`	Только календарно корректные даты строго вида `YYYY-MM-DD` или `DD.MM.YYYY`, включая ведущие нули. В базе хранить текст `YYYY-MM-DD`.
`category`	Удалить внешние пробельные символы, каждую внутреннюю последовательность пробельных символов заменить одним пробелом. Первую букву строки привести к верхнему регистру, остальные к нижнему. Пустое значение заменить на «Не указана».
`quantity`	Строго положительное целое количество единиц товара.
`unit_price`	Строго положительная цена единицы в рублях. Принимать целую часть из цифр и необязательную дробную часть из одной или двух цифр после точки или запятой. Разбирать через `Decimal`, переводить в целые копейки точно, без округления.

Для числовых полей принимаем десятичные цифры ASCII, для всех полей при нормализации убираем внешние пробелы. Знаки, экспоненциальная запись, разделители тысяч и неконечные значения, включая NaN и Infinity, отклоняются. Цена 1,230 отклоняется даже при нуле в третьем разряде. Выручка заказа в копейках равна количеству, умноженному на цену единицы в копейках. ID, количество, цена в копейках и произведение должны умещаться в знаковый INTEGER SQLite с максимумом 9 223 372 036 854 775 807. Переполнение отдельного заказа является причиной отбраковки записи.

Порядок обработки

Читать CSV стандартным модулем csv, открывая файл с newline=''. Считать логические записи после заголовка с единицы, включая отклонённые записи. Поле в кавычках с переводом строки входит в одну запись. Сохранять номер у принятого заказа и в отчёте для каждой отброшенной записи.
До очистки сравнить кортежи исходных значений полей, полученных от CSV-парсера. Для каждого кортежа оставить первое вхождение, последующие отметить как точные дубли. Это правило действует и для повторов невалидного заказа.
Проверить число полей. Для записи правильной ширины собрать все ошибки независимых полей в порядке таблицы. Проверить произведение, только если количество и цена прошли проверку. Сохранять один итоговый статус и список фактически установленных причин на запись.
Сгруппировать только валидные записи по нормализованному ID. Сравнивать дату, категорию, количество и цену в копейках. Если значения совпадают у всей группы, принять запись с наименьшим номером, остальные отметить как нормализованные дубли. Если есть хотя бы два варианта значений, отклонить всю валидную группу как конфликт ID, включая повторившиеся варианты. Уже отброшенные точные дубли сохраняют исходную причину.
Вывести количество прочитанных, принятых и отброшенных записей, разбивку по причинам и номера отклонённых записей. Число прочитанных должно равняться сумме принятых и отброшенных. Сумма счётчиков причин может быть больше числа отброшенных записей.
Перед загрузкой БД сформировать статистику через build_report, передав число прочитанных записей. После фиксации транзакции вызвать save_reports(accepted, rejected, report). В outputs/clean_orders.csv записать колонки order_id, order_date, category, quantity, unit_price_kopecks, revenue_kopecks в исходном порядке записей. В outputs/rejected_rows.csv сохранить номер записи, исходный заголовок, значения и причины. В outputs/quality_report.json сохранить статистику. Каждый запуск заменяет файлы, пустые CSV содержат заголовки.

Модули и загрузка

Модуль	Предлагаемый интерфейс и ответственность
`src/extract.py`	`read_records(path) -> Iterator[RawRecord]`. Передаёт номер записи и исходные значения, проверяет заголовок и ширину записи.
`src/transform.py`	`clean_orders(records) -> tuple[list[Order], list[RejectedRecord]]`. Рассчитывает выручку одного заказа и возвращает принятые заказы и отбраковку с причинами.
`src/database.py`	`reload_database(path, orders) -> None`. Обновляет обе таблицы атомарно и рассчитывает дневные агрегаты в SQLite.
`main.py`	Определяет корень по расположению самого файла, собирает пути к ресурсам, вызывает этапы и выводит отчёт.

В orders хранить одну принятую запись на order_id (первичный ключ), включая дату, категорию, количество, цену и выручку в копейках. В daily_category_revenue хранить количество заказов и сумму выручки в копейках с составным первичным ключом из даты и категории.

В одной явной транзакции создать таблицы при первом запуске, очистить обе, загрузить принятые заказы и заполнить daily_category_revenue запросом с SUM и GROUP BY order_date, category из orders. Это единственное место построения дневной витрины. Зафиксировать транзакцию после успешного завершения всех операций. Ошибка загрузки или переполнение суммы группы отменяет транзакцию целиком, сохраняя предыдущие данные.

Неоднозначности и рабочие допущения

Исходный синтетический набор находится в data/raw_orders.csv. Формат — UTF-8 с поддержкой BOM при чтении, запятая между полями, двойные кавычки для значений с запятой или переводом строки. Указать происхождение в README. При обработке исходный файл открывать только на чтение.
Колонки сопоставлять по именам. Отсутствующее, повторное или лишнее имя в заголовке считать ошибкой файла. Неверное число полей в читаемой записи считать ошибкой записи. Если из-за повреждения CSV границы записей определить невозможно, остановить запуск до загрузки.
Файл с корректным заголовком и нулём принятых заказов считать пустым снимком. При полной перезагрузке обе таблицы станут пустыми.
Для аналитики использовать календарь от первой до последней даты принятого заказа включительно. В отчётах заполнять нулями дни без продаж, в том числе для каждой категории, встречающейся в снимке. Для пустого снимка календарь пуст.
Топ — пять категорий по суммарной выручке с сортировкой по имени при равенстве. Для динамики суммировать выручку всех категорий за каждый календарный день. Выводить одну строку на дату с общей выручкой и её изменением в копейках к предыдущему дню. Для первого дня изменение не определено.
Аномалии искать отдельно для каждой категории. По всему календарю, включая нулевые дни, по дневной выручке этой категории рассчитать среднее и стандартное отклонение генеральной совокупности (дисперсия с делением на число дней). Отмечать день при абсолютном отклонении от среднего строго больше двух стандартных отклонений. Проверять как рост, так и падение. При числе дней меньше 7 или нулевой дисперсии аномалии не выдавать.

Критерии готовности реализации

На pytest проверить оба формата дат, невозможные даты, пробелы, пустые поля, дробное количество, варианты цены и переполнение на границе INTEGER. Проверить нумерацию на CSV с многострочным полем.
На размеченном примере сверить статус каждой исходной записи, причины и баланс счётчиков. Включить повтор невалидной записи, одинаковые нормализованные заказы, конфликт из трёх записей с двумя совпадающими вариантами и валидную запись рядом с невалидной того же ID.
Сверить копейки по известному примеру. Две единицы по 10,25 рубля и одна за 0,50 рубля в тот же день и в той же категории должны дать одну строку витрины на 2100 копеек.
Сверить строки outputs/clean_orders.csv с принятыми заказами. Для аналитики проверить календарный пропуск, периоды в 6 и 7 дней, постоянную выручку и значения ниже, выше и точно на границе двух стандартных отклонений.
Запустить пайплайн дважды с тем же CSV, в том числе из другой рабочей папки. Сравнить содержимое обеих таблиц, отчёты и контрольную сумму исходного файла. На изменённом снимке проверить удаление устаревших заказов и агрегатов. На переполнении суммы группы проверить откат обеих таблиц.
Перед сдачей подготовить готовую SQLite-базу, три SQL-запроса с фактическими результатами, README с командами запуска и тестов и журнал фактически выполненных проверок.

14 KiB Raw Permalink Blame History Unescape Escape