Тестовое задание на позицию Data Engineer

Описание задачи

В рамках тестового задания необходимо:

Предложить структуру DWH и DAG для выгрузки данных в хранилище.
Найти ошибки и особенности в исходных данных.
Описать SQL/Python-трансформации для подготовки данных.
Дать комментарии к данным и зафиксировать допущения, принятые во время выполнения задания.

Исходные данные

Исходные данные были предоставлены в виде ссылки на Google Sheets.

Для выполнения задания данные были вручную выгружены из Google Sheets в CSV-файлы:

Файл	Описание
`fact_2025_02.csv`	Фактические данные за февраль 2025 года
`fact_2026_02.csv`	Фактические данные за февраль 2026 года
`plan_2026_02.csv`	Плановые данные на февраль 2026 года

В рамках решения CSV-файлы рассматриваются как входной файловый источник данных.

Файлы факта содержат данные на уровне позиции блюда внутри чека.
Файл плана представляет собой Excel-отчёт, выгруженный из Google Sheets в CSV.

Подход к выполнению

Работа была разделена на несколько этапов:

Первичный анализ исходных CSV-файлов.
Выявление особенностей и потенциальных ошибок данных.
Проектирование структуры DWH.
Подготовка bronze-слоя для хранения исходных данных.
Подготовка silver-слоя для приведения типов, нормализации и маркировки проблемных строк.
Подготовка gold-слоя для сравнения факта 2026, факта 2025 и плана 2026.
Описание DAG для последовательного выполнения загрузки и трансформаций.

Структура проекта

italy-data-engineer-test/
├── README.md
├── data/
│   └── source/
│       ├── fact_2025_02.csv
│       ├── fact_2026_02.csv
│       └── plan_2026_02.csv
├── python/
│   ├── inspect_data.py
│   └── load_bronze.py
├── sql/
│   ├── 01_create_bronze_tables.sql
│   ├── 02_dq_bronze_checks.sql
│   ├── 03_create_silver_tables.sql
│   ├── 04_transform_plan_to_silver.sql
│   ├── 05_create_silver_plan_revenue_long.sql
│   ├── 06_transform_fact_to_silver.sql
│   └── 07_create_gold_sales_plan_fact_daily.sql
├── dags/
│   └── sales_plan_fact_dag.py
└── requirements.txt

Назначение SQL-файлов

Папка sql/ содержит DDL-скрипты и SQL-трансформации для построения слоёв DWH.

Файл	Назначение
`01_create_bronze_tables.sql`	Создание bronze-таблиц для исходных CSV-файлов
`02_dq_bronze_checks.sql`	Создание DQ-таблиц и технические проверки после загрузки bronze-слоя
`03_create_silver_tables.sql`	Создание silver-таблиц
`04_transform_plan_to_silver.sql`	Приведение плановых данных из bronze к типам silver
`05_create_silver_plan_revenue_long.sql`	Преобразование плана из широкого формата в длинный формат по каналам продаж
`06_transform_fact_to_silver.sql`	Приведение фактических продаж к silver-слою, маппинг каналов и расчёт флагов особенностей данных
`07_create_gold_sales_plan_fact_daily.sql`	Создание финальной gold-витрины сравнения факта, плана и прошлого года

В публичный репозиторий реальные исходные данные не добавляются.
Для запуска проекта файлы необходимо положить локально в директорию data/source/.

Первичный анализ данных

Перед проектированием DWH был выполнен первичный анализ исходных CSV-файлов с помощью скрипта:

python/inspect_data.py

Цель анализа — понять структуру входных данных, определить уровень детализации, найти потенциальные ошибки и зафиксировать допущения для дальнейших трансформаций.

Общая структура файлов

Файл	Количество строк	Количество колонок	Комментарий
`fact_2025_02.csv`	49 836	12	Факт за февраль 2025
`fact_2026_02.csv`	48 402	12	Факт за февраль 2026
`plan_2026_02.csv`	30	16	План на февраль 2026

Файлы факта за 2025 и 2026 годы имеют одинаковую структуру и могут обрабатываться единой логикой.

Файл плана имеет другую структуру: это Excel-отчёт, выгруженный из Google Sheets в CSV. В нём присутствуют пользовательские заголовки, повторяющиеся названия колонок и несколько смысловых блоков.

Уровень детализации факта

Количество строк в файлах факта значительно больше количества уникальных чеков:

Период	Количество строк	Количество уникальных чеков
Февраль 2025	49 836	6 498
Февраль 2026	48 402	6 386

На основании этого сделано допущение, что одна строка факта соответствует позиции блюда внутри чека, а не чеку целиком.

Это важно для проектирования DWH: фактовая таблица должна хранить данные на уровне позиции чека, например fact_order_items.

Проверка пропусков

В фактических данных пропуски не обнаружены.

При этом отсутствие NULL не означает, что данные полностью корректны. Дополнительно были проверены:

нулевые значения;
отрицательные суммы;
строки вне заявленного периода;
расхождения между временем и производными полями часа;
дубли;
типы заказов и возможность их приведения к единым каналам продаж.

Найденные особенности и потенциальные ошибки

Первичный анализ качества данных был выполнен в python/inspect_data.py. Технические проверки пригодности данных к трансформациям вынесены в SQL-скрипт 02_dq_bronze_checks.sql и выполняются после загрузки bronze-слоя.

Эти проверки работают как DQ-gate между bronze- и silver-слоями. Их задача — не оценивать бизнес-корректность строк, а проверить, что данные технически можно безопасно привести к типам и использовать в дальнейших SQL-трансформациях.

Проверяются:

пустые обязательные поля;
числовые поля, которые не приводятся к numeric / int;
даты и timestamps, которые не соответствуют ожидаемым форматам.

Результаты проверок записываются в две таблицы:

Таблица	Назначение
`dq.check_results`	Сводный результат проверок и количество проблемных строк
`dq.issue_rows`	Конкретные проблемные строки из bronze-таблиц

Если хотя бы одна проверка возвращает failed_rows_count > 0, DAG останавливается до построения silver-слоя. Это сделано намеренно: такие строки могут сломать приведение типов или исказить результат трансформаций, поэтому их нужно найти в dq.issue_rows, исправить в исходном файле и повторить загрузку.

Так как полные бизнес-правила по спорным строкам не были предоставлены, найденные особенности не удаляются и не исправляются автоматически. Вместо этого они сохраняются в silver-слое в виде флагов.

Такой подход позволяет сохранить исходные данные и не принимать бизнес-решения на основании предположений.

Нулевые и отрицательные значения

В фактических данных обнаружены строки с нулевыми и отрицательными значениями.

Проверка	2025	2026
Количество блюд = 0 и сумма = 0	375	814
Количество блюд > 0 и сумма = 0	11 938	12 254
Сумма < 0	0	10

Большая часть строк с положительным количеством и нулевой суммой относится к категориям МОДИФИКАТОРЫ, ДОПЫ, АКЦИИ/ПОДАРКИ. Такие строки могут быть корректными для ресторанной системы, но их необходимо отделять от платных товарных позиций.

Все строки с отрицательной суммой в 2026 году относятся к позиции Округление в пользу гостя. Такие строки считаются технической корректировкой чека и не удаляются из факта.

Для silver-слоя предлагается добавить флаги:

Флаг	Назначение
`is_zero_qty_zero_revenue`	Количество блюд = 0 и сумма = 0
`is_zero_revenue_positive_qty`	Количество блюд > 0 и сумма = 0
`is_rounding_adjustment`	Техническая корректировка округления

Проверка периода

По условию задания фактические данные должны относиться к февралю 2025 и февралю 2026.

В файле fact_2025_02.csv обнаружены строки с учетным днем 2025-03-01.

Файл	Строки вне февраля
`fact_2025_02.csv`	2 217
`fact_2026_02.csv`	0

При просмотре строк вне февраля видно два типа случаев:

чек открыт в феврале, но закрыт в марте;
чек открыт и закрыт в марте.

Строки, полностью относящиеся к марту, не должны попадать в февральскую витрину.

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

Для silver-слоя предлагается добавить флаги:

Флаг	Назначение
`is_out_of_period`	Строка не относится к заявленному периоду
`is_cross_period_check`	Чек открыт в одном периоде, а закрыт в другом

Расхождение производных полей

Поля Час открытия и Час закрытия являются производными от полей Время открытия и Время закрытия.

В факте за 2026 год обнаружены 9 строк, где поле Час закрытия не совпадает с часом, рассчитанным из поля Время закрытия.

Пример:

Поле	Значение
`Время закрытия`	`2026-02-13 22:00:00`
`Час закрытия` из источника	`21`
Расчётный час закрытия	`22`

В silver-слое исходные значения Час открытия и Час закрытия сохраняются без пересчёта. Без бизнес-правил нельзя однозначно решить, чему доверять: исходному производному полю или часу, рассчитанному из timestamp.

Для фиксации таких случаев в silver-слое добавляются флаги:

Поле	Назначение
`is_open_hour_mismatch`	Расхождение исходного и расчётного часа открытия
`is_close_hour_mismatch`	Флаг расхождения исходного и расчётного часа закрытия

Такие строки требуют уточнения бизнес-правила: использовать час из источника или пересчитывать его из времени события.

Проверка дублей

Полные дубли строк не обнаружены.

Также не обнаружены дубли по выбранному бизнес-ключу:

учетный день;
номер чека;
время открытия;
время закрытия;
блюдо;
категория блюда;
тип заказа;
количество блюд;
количество гостей;
сумма со скидкой.

Типы заказов и каналы продаж

В фактах за 2025 и 2026 годы используются разные наборы типов заказов:

Период	Количество типов заказов
2025	9
2026	14

Для корректного сравнения факта с планом типы заказов были приведены к укрупнённым каналам продаж.

Канал продаж	Типы заказов
`restaurant`	`Заказ в ресторане`, `Веранда`
`banquet_own`	`Банкет`
`banquet_cb`	`Банкет C&B`
`aggregator`	`Яндекс.ультима`, `Агрегатор (Курьером)`, `Агрегатор (Самовывоз)`
`delivery`	`Доставка курьером`, `Приложение (доставка курьером)`, `Сайт (доставка курьером)`
`pickup`	`Самовывоз гостем`, `Приложение (самовывоз гостем)`, `Сайт (самовывоз гостем)`, `Заказ с собой`

После применения маппинга незамапленных типов заказов не осталось.

Принятые допущения:

Заказ с собой отнесён к каналу pickup;
Веранда отнесена к каналу restaurant.

Плановые данные

Файл plan_2026_02.csv представляет собой Excel-отчёт, выгруженный из Google Sheets в CSV.

Особенности файла:

первая строка содержит пользовательские заголовки;
в файле присутствуют повторяющиеся названия колонок;
в одной таблице объединены разные смысловые блоки:
- плановая выручка;
- средний чек на стол/заказ;
- средний чек на гостя.

Для основной витрины используется только блок плановой выручки:

Поле	Описание
`plan_date`	Дата плана
`plan_total_revenue`	Общий план на день
`restaurant`	План по ресторану
`banquet_own`	План по банкетам
`banquet_cb`	План по банкетам C&B
`aggregator`	План по агрегаторам
`pickup`	План по самовывозу
`delivery`	План по доставке

Показатели среднего чека рассматриваются как дополнительные KPI и могут быть вынесены в отдельную таблицу на следующем этапе.

Допущение по подготовке планового файла

Плановый файл из Google Sheets имеет формат управленческого Excel-отчёта: в нём присутствуют дополнительные строки шапки, пустые разделительные столбцы, итоговая строка и несколько смысловых блоков показателей.

Для загрузки в DWH принято допущение, что плановый файл должен быть приведён к плоской табличной структуре. В рамках прототипа эта подготовка выполняется в Python-скрипте python/load_bronze.py:

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

В bronze-слой загружаются:

плановая выручка по дням и каналам продаж;
средний чек на стол/заказ;
средний чек на гостя.

Пустые технические столбцы из Excel-отчёта в DWH не загружаются, так как не содержат аналитически полезных данных.

Предложенная структура DWH

Для решения задачи предлагается использовать medallion-подход к послойной структуре хранилища:

source files -> bronze -> silver -> gold

Bronze-слой

Bronze-слой предназначен для хранения данных после минимальной технической подготовки входных CSV-файлов, без бизнес-трансформаций и приведения типов.

На этом слое данные не приводятся к бизнес-логике и сохраняются в текстовом виде. Минимальная техническая подготовка применяется только к плановому файлу, чтобы привести Excel-отчёт к табличной CSV-структуре.

Основная задача bronze-слоя — сохранить данные после минимальной технической подготовки для аудита и повторной обработки.

Предлагаемые таблицы:

Таблица	Назначение
`bronze.fact_sales`	Исходные фактические данные за 2025 и 2026 годы
`bronze.plan_sales`	Исходные плановые данные

Фактические данные за 2025 и 2026 годы предлагается хранить в одной таблице bronze.fact_sales, так как файлы имеют одинаковую структуру.
Для разделения периодов добавляется техническое поле source_period.

Также в bronze-таблицы добавляются технические поля:

Поле	Назначение
`source_file`	Имя исходного файла
`load_dttm`	Дата и время загрузки
`source_period`	Период, к которому относится исходная выгрузка

Поле source_period не заменяет business_date.
business_date хранит учетный день из строки, а source_period показывает, к какому периоду относится исходная выгрузка. Это позволяет выявлять ситуации, когда строка пришла из февральского файла, но имеет учетный день за март.

Silver-слой

Silver-слой предназначен для технической подготовки данных.

На этом слое выполняются:

переименование колонок в технические названия;
приведение типов данных;
парсинг дат и времени;
проверка производных полей через флаги расхождений;
маппинг типов заказов в каналы продаж;
добавление флагов качества данных.

Предлагаемые таблицы:

Таблица	Назначение
`silver.fact_order_items`	Подготовленные строки факта на уровне позиции чека
`silver.plan_sales`	Подготовленный план в широком формате с приведёнными типами
`silver.plan_revenue`	Плановая выручка в длинном формате по дням и каналам продаж

В таблице silver.fact_order_items предлагается хранить данные на уровне позиции блюда внутри чека.

Основные поля:

Поле	Назначение
`business_date`	Учетный день
`check_number`	Номер чека
`opened_at`	Время открытия чека
`closed_at`	Время закрытия чека
`dish_name`	Название блюда
`dish_category`	Категория блюда
`order_type`	Исходный тип заказа
`sales_channel`	Укрупнённый канал продаж
`dish_qty`	Количество блюд
`guest_qty`	Количество гостей
`revenue`	Сумма со скидкой
`source_period`	Период файла, из которого пришла строка
`source_file`	Имя исходного файла

Флаги качества данных:

Флаг	Назначение
`is_zero_qty_zero_revenue`	Количество блюд = 0 и сумма = 0
`is_zero_revenue_positive_qty`	Количество блюд > 0 и сумма = 0
`is_rounding_adjustment`	Строка округления в пользу гостя
`is_out_of_period`	Строка вне заявленного периода
`is_cross_period_check`	Чек открыт в одном периоде, а закрыт в другом
`is_open_hour_mismatch`	Расхождение исходного и расчётного часа открытия
`is_close_hour_mismatch`	Расхождение исходного и расчётного часа закрытия

Gold-слой

Gold-слой предназначен для аналитики и сравнения факта, плана и прошлого года.

Предлагаемая витрина:

Таблица	Назначение
`gold.sales_plan_fact_daily`	Сравнение факта 2026, плана 2026 и факта 2025 по дням и каналам продаж

Основной уровень витрины:

дата + канал продаж

Такой уровень выбран, потому что:

факт 2026 нужно сравнить с планом 2026 по дням;
факт 2026 нужно сравнить с фактом 2025;
плановые данные представлены по дням и каналам продаж.

Для финальной витрины принято допущение: выручка относится к отчетному периоду по полю business_date. Поэтому в gold-слой включаются только строки факта, где is_out_of_period = false, то есть учетный день относится к периоду исходной выгрузки.

Остальные найденные особенности данных не исключаются из расчёта без дополнительных бизнес-правил. Они сохраняются в silver-слое в виде флагов и могут быть отдельно проанализированы.

Основные поля витрины:

Поле	Назначение
`business_date`	Дата продаж / дата плана
`sales_channel`	Канал продаж
`fact_2026_revenue`	Фактическая выручка за февраль 2026
`fact_2025_revenue`	Фактическая выручка за февраль 2025
`plan_2026_revenue`	Плановая выручка за февраль 2026
`deviation_from_plan`	Отклонение факта 2026 от плана
`deviation_from_plan_pct`	Отклонение факта 2026 от плана в процентах
`yoy_growth`	Изменение факта 2026 к факту 2025
`yoy_growth_pct`	Изменение факта 2026 к факту 2025 в процентах

Логика загрузки данных

В рамках задания данные предоставлены в виде месячных CSV-выгрузок из Google Sheets.

Для выполнения задания были получены:

факт за февраль 2025;
факт за февраль 2026;
план за февраль 2026.

Пайплайн рассчитан на обработку файлов за конкретный отчетный период.
Для каждой загрузки сохраняются технические поля:

Поле	Назначение
`source_file`	Имя исходного CSV-файла
`source_period`	Отчетный период, к которому относится файл
`load_dttm`	Дата и время загрузки в DWH

Поле source_period не заменяет business_date.

source_period показывает, к какому отчетному месяцу относится файл;
business_date хранит учетный день конкретной строки из источника.

Общая логика пайплайна:

CSV-файлы
    ↓
python/load_bronze.py
    ↓
bronze.fact_sales / bronze.plan_sales
    ↓
DQ-проверки bronze-слоя -> dq.check_results / dq.issue_rows
    ↓
SQL-трансформации
    ↓
silver.fact_order_items / silver.plan_sales / silver.plan_revenue
    ↓
gold.sales_plan_fact_daily

Логика DAG

Файл dags/sales_plan_fact_dag.py содержит описание предполагаемой DAG-логики для оркестрации пайплайна.

В рамках тестового задания Airflow локально не запускался. DAG приведён как пример промышленной последовательности выполнения задач.

SQL-скрипты в DAG выполняются через SQLExecuteQueryOperator, а не через BashOperator и psql.
Для запуска в Airflow необходимо создать подключение к PostgreSQL с conn_id = postgres_italy_test.

Python-загрузка CSV в bronze-слой вынесена в отдельный скрипт python/load_bronze.py и запускается из DAG через BashOperator.

DQ-проверки выполняются сразу после загрузки bronze-слоя. Агрегированные результаты пишутся в dq.check_results, а проблемные строки — в dq.issue_rows. Если хотя бы одна проверка возвращает failed_rows_count > 0, DAG останавливается до построения silver-слоя, чтобы строки можно было найти в исходнике и исправить вручную.

Предполагаемый порядок задач:

create_bronze_tables
    ↓
load_bronze
    ↓
dq_bronze_checks
    ↓
create_silver_tables
    ↓
transform_plan_to_silver
    ↓
create_silver_plan_revenue_long
    ↓
transform_fact_to_silver
    ↓
create_gold_sales_plan_fact_daily

Как запустить проект локально

Установить зависимости:

pip install -r requirements.txt

Создать базу данных PostgreSQL:

createdb italy_test

Создать bronze-таблицы:

psql -U postgres -d italy_test -f sql/01_create_bronze_tables.sql

Загрузить CSV-файлы в bronze-слой:

python python/load_bronze.py

Выполнить DQ-проверки bronze-слоя:

psql -U postgres -d italy_test -f sql/02_dq_bronze_checks.sql

Посмотреть результаты DQ-проверок:

select *
from dq.check_results
order by check_name;

Посмотреть проблемные строки:

select *
from dq.issue_rows
order by check_name, issue_id;

Создать silver-таблицы и выполнить SQL-трансформации:

psql -U postgres -d italy_test -f sql/03_create_silver_tables.sql
psql -U postgres -d italy_test -f sql/04_transform_plan_to_silver.sql
psql -U postgres -d italy_test -f sql/05_create_silver_plan_revenue_long.sql
psql -U postgres -d italy_test -f sql/06_transform_fact_to_silver.sql
psql -U postgres -d italy_test -f sql/07_create_gold_sales_plan_fact_daily.sql

Проверить итоговую витрину:

select *
from gold.sales_plan_fact_daily
order by business_date, sales_channel;

Navigation Menu

Repository files navigation

Тестовое задание на позицию Data Engineer

Описание задачи

Исходные данные

Подход к выполнению

Структура проекта

Назначение SQL-файлов

Первичный анализ данных

Общая структура файлов

Уровень детализации факта

Проверка пропусков

Найденные особенности и потенциальные ошибки

Нулевые и отрицательные значения

Проверка периода

Расхождение производных полей

Проверка дублей

Типы заказов и каналы продаж

Плановые данные

Допущение по подготовке планового файла

Предложенная структура DWH

Bronze-слой

Silver-слой

Gold-слой

Логика загрузки данных

Логика DAG

Как запустить проект локально

About

Releases

Packages

Contributors

Languages

Name		Name	Last commit message	Last commit date
dags		dags
data/source		data/source
python		python
sql		sql
.gitignore		.gitignore
README.md		README.md
requirements.txt		requirements.txt

Latest commit

History

Folders and files

Repository files navigation

Тестовое задание на позицию Data Engineer

Описание задачи

Исходные данные

Подход к выполнению

Структура проекта

Назначение SQL-файлов

Первичный анализ данных

Общая структура файлов

Уровень детализации факта

Проверка пропусков

Найденные особенности и потенциальные ошибки

Нулевые и отрицательные значения

Проверка периода

Расхождение производных полей

Проверка дублей

Типы заказов и каналы продаж

Плановые данные

Допущение по подготовке планового файла

Предложенная структура DWH

Bronze-слой

Silver-слой

Gold-слой

Логика загрузки данных

Логика DAG

Как запустить проект локально

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages