Тестовое задание для аналитика DWH: 4 задачи с разбором типичных ошибок
Тестовое задание для аналитика хранилища данных должно проверять не только знание SQL-синтаксиса. Гораздо важнее понять, умеет ли кандидат анализировать свойства данных, замечать неоднозначности и заранее оценивать последствия запроса для промышленной витрины.
Ниже разобраны четыре задачи, которые используют в найме аналитиков DWH. Они охватывают дедупликацию CDC, оконные функции, исторические измерения SCD Type 2, point-in-time join и контроль целостности истории. Каждый пример выглядит достаточно простым и обычно корректно работает на небольшом наборе тестовых строк. Однако в реальных данных скрытые предпосылки быстро приводят к задвоению выручки, потере записей или неверной атрибуции заказов.
В качестве СУБД можно представить PostgreSQL-совместимый движок, например Greenplum. Запросы приведены в близком к ANSI SQL виде, поэтому при использовании другой системы отдельные конструкции потребуется адаптировать.
Исходные условия
Интернет-магазин загружает заказы через CDC-поток. Изменения попадают в стейджинговую таблицу `stg.orders`, где для одного `order_id` может находиться несколько версий записи.
Вторая таблица содержит историю клиентов:
- `customer_id`;
- `segment`;
- `valid_from`;
- `valid_to`.
История строится по модели SCD Type 2: при изменении сегмента старая строка закрывается, а новая становится действующей.
Бизнес-требование состоит в том, чтобы построить витрину выручки по сегментам. Сегмент необходимо определять не на текущий момент, а на время оформления заказа. Кроме того, внутри каждого сегмента требуется рассчитать дневной нарастающий итог.
В примерах используется правило: текущая версия клиента имеет `valid_to IS NULL`. Допустим и другой вариант - специальная дата вроде `9999-12-31`. Главное, не смешивать оба подхода в одной модели. Иначе часть запросов будет проверять `IS NULL`, а другая часть - сравнивать даты с максимальным значением, что создаст трудноуловимые расхождения.
Особенно важны два пограничных случая:
1. у заказа может быть несколько CDC-версий с одинаковым временем изменения;
2. сегмент клиента может измениться ровно в момент создания заказа.
Задача 1. Дедупликация CDC: почему одного `ROW_NUMBER()` недостаточно
Первое требование - оставить для каждого заказа только последнюю версию:
```sql
SELECT *
FROM (
SELECT
o.*,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC
) AS rn
FROM stg.orders o
) t
WHERE rn = 1;
```
Запрос выглядит стандартно, но в нём есть как минимум две потенциальные проблемы.
Проблема 1. Сортировка может быть недетерминированной
Если у нескольких версий одного заказа одинаковое значение `updated_at`, база данных не обязана выбирать одну и ту же строку при каждом выполнении. План запроса, распределение данных и порядок чтения могут измениться, а вместе с ними - и победитель `ROW_NUMBER()`.
Особенно опасна ситуация, когда в одну микросекунду пришли операции `UPDATE` и `DELETE` либо две последовательные версии записи. Внешне запрос отработает успешно, но результат окажется случайным.
Нужно добавить дополнительный признак, формирующий строгий порядок. Это может быть:
- монотонный идентификатор события;
- позиция сообщения в CDC-журнале;
- номер транзакции;
- сочетание времени, типа операции и технического sequence-значения.
Например:
```sql
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY
updated_at DESC,
event_id DESC
)
```
Если уникального технического ключа нет, это уже проблема модели загрузки. Искусственно добавлять `ORDER BY op` можно только после явного определения бизнес-приоритета операций.
Проблема 2. Удаление может быть потеряно
В CDC обычно встречаются операции `I`, `U` и `D`. Если последним событием была операция удаления, простое ранжирование оставит строку заказа в результате. После дедупликации нужно учитывать действие над записью:
```sql
WITH ranked AS (
SELECT
o.*,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC, event_id DESC
) AS rn
FROM stg.orders o
)
SELECT *
FROM ranked
WHERE rn = 1
AND op <> 'D';
```
При этом нельзя автоматически считать, что символы операций одинаковы во всех источниках. В одном коннекторе удаление обозначается как `D`, в другом - как `DELETE`, а иногда операция хранится отдельным флагом.
Почему не помогают `RANK()` и `DISTINCT`
Замена `ROW_NUMBER()` на `RANK()` не решает задачу. При одинаковом времени несколько строк получат ранг `1`, и дубли сохранятся.
`DISTINCT` также не является полноценным решением. Он удаляет только полностью одинаковые строки. Как только в источник добавится техническое поле или изменится хотя бы один атрибут, записи снова начнут проходить в результат.
Главный вывод: дедупликация CDC требует не только оконной функции, но и понимания порядка событий, семантики операций и поведения источника при одинаковых временных метках.
Задача 2. Нарастающий итог: отличие `RANGE` от `ROWS`
После определения сегмента требуется посчитать дневную выручку и накопительный итог:
```sql
SELECT
segment,
order_date,
SUM(revenue) AS day_revenue,
SUM(SUM(revenue)) OVER (
PARTITION BY segment
ORDER BY order_date
) AS cumulative_revenue
FROM orders_with_segment
GROUP BY segment, order_date;
```
На агрегированных данных такой вариант обычно корректен: на каждую дату приходится одна строка в рамках сегмента.
Но если оконная функция применяется до группировки или по timestamp, появляется важное различие между `RANGE` и `ROWS`.
`RANGE` работает с логическим значением сортировки. Все строки с одинаковым значением `ORDER BY` считаются "соседями" и попадают в один оконный диапазон. `ROWS` ориентируется на физические строки.
Рассмотрим пример:
| Дата | Сумма |
|---|---:|
| 10 марта | 100 |
| 10 марта | 50 |
| 11 марта | 80 |
При использовании `RANGE` обе строки за 10 марта могут получить накопительный результат `150`. При `ROWS` одна из них получит `100`, а другая - `150`, причём порядок между ними должен быть дополнительно определён.
Если бизнес требует итог на уровне дня, безопаснее сначала агрегировать данные:
```sql
WITH daily AS (
SELECT
segment,
order_date,
SUM(revenue) AS day_revenue
FROM orders_with_segment
GROUP BY segment, order_date
)
SELECT
segment,
order_date,
day_revenue,
SUM(day_revenue) OVER (
PARTITION BY segment
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM daily;
```
Если же расчёт выполняется на уровне отдельных заказов, необходимо явно решить, что означает "итог на дату": промежуточный результат внутри дня или одинаковое значение для всех заказов этой даты.
Оконная рамка должна быть частью бизнес-логики, а не случайностью, зависящей от значения по умолчанию в конкретной СУБД.
Задача 3. SCD2 и point-in-time join
Теперь нужно определить сегмент клиента на момент оформления заказа. Наиболее распространённый вариант соединения выглядит так:
```sql
SELECT
o.order_id,
o.customer_id,
o.created_at,
o.amount,
d.segment
FROM deduplicated_orders o
JOIN dim_customer_history d
ON d.customer_id = o.customer_id
AND o.created_at >= d.valid_from
AND o.created_at < d.valid_to;
```
Для текущей версии, у которой `valid_to IS NULL`, условие нужно дополнить:
```sql
AND (
o.created_at < d.valid_to
OR d.valid_to IS NULL
)
```
Почему опасен `BETWEEN`
Запись вида:
```sql
o.created_at BETWEEN d.valid_from AND d.valid_to
```
использует включающий интервал с обеих сторон. Если одна версия заканчивается в `12:00:00`, а следующая начинается в тот же момент, заказ с timestamp `12:00:00` совпадёт сразу с двумя строками.
Для временной истории обычно применяется полуоткрытый интервал:
```text
[valid_from, valid_to)
```
То есть начало включается, а конец - нет. Тогда соседние периоды не пересекаются.
Что делать с точным совпадением времени
Если сегмент изменился в ту же секунду или микросекунду, когда был оформлен заказ, результат зависит от согласованного бизнес-правила:
- сегмент до изменения;
- сегмент после изменения;
- порядок событий в едином журнале;
- отдельный приоритет между заказом и изменением справочника.
Одного timestamp может быть недостаточно. Если источники генерируют события независимо, необходимо определить правило разрешения конфликтов. В некоторых системах для этого используют sequence-номер или техническое время приёма события.
Проверка уникальности point-in-time join
После соединения полезно проверить, что один заказ сопоставился ровно с одной версией клиента:
```sql
SELECT
order_id,
COUNT(*) AS matches
FROM joined_data
GROUP BY order_id
HAVING COUNT(*) <> 1;
```
Наличие нулевых совпадений говорит о пропусках в истории или неверных границах периода. Значения больше единицы указывают на пересечение версий.
Производительность range join
Условие соединения по диапазону дат обычно тяжелее обычного равенства. Особенно это заметно в распределённых системах и на больших таблицах фактов.
Для ускорения применяют:
- предварительную фильтрацию заказов;
- распределение таблиц по `customer_id`;
- партиционирование по дате;
- хранение дат в едином типе и часовом поясе;
- контроль объёма истории;
- предварительное выявление клиентов без подходящей версии.
Но оптимизация не должна заменять проверку корректности. Быстрый join, который задваивает выручку, всё равно остаётся ошибочным.
Задача 4. Проверка истории на целостность
Историю SCD2 нельзя считать корректной только потому, что она была сформирована загрузочным процессом. Для неё нужны автоматические проверки.
1. Не должно быть нескольких текущих строк
```sql
SELECT customer_id
FROM dim_customer_history
WHERE valid_to IS NULL
GROUP BY customer_id
HAVING COUNT(*) > 1;
```
2. Периоды одного клиента не должны пересекаться
Для каждой строки можно сравнить `valid_from` со временем окончания предыдущего периода:
```sql
WITH checked AS (
SELECT
customer_id,
valid_from,
valid_to,
LAG(valid_to) OVER (
PARTITION BY customer_id
ORDER BY valid_from
) AS previous_valid_to
FROM dim_customer_history
)
SELECT *
FROM checked
WHERE previous_valid_to IS NOT NULL
AND valid_from < previous_valid_to;
```
3. Не должно быть разрывов
Если история должна быть непрерывной, начало нового периода обязано совпадать с окончанием предыдущего:
```sql
valid_from = previous_valid_to
```
Однако это правило следует применять только там, где непрерывность действительно предусмотрена моделью. Иногда между версиями допустимы интервалы неизвестности.
4. Даты должны быть логичными
Недопустимы строки, где:
- `valid_from > valid_to`;
- `valid_from` равен `valid_to`, если нулевые интервалы не предусмотрены;
- текущая запись содержит одновременно `valid_to IS NULL` и дату закрытия в другом поле;
- отсутствует обязательный клиентский ключ.
5. История должна давать однозначный результат
Самая практичная проверка - прогнать через таблицу несколько контрольных заказов:
- до первой версии клиента;
- точно в момент начала периода;
- точно в момент его завершения;
- между двумя изменениями;
- после последней версии.
Такой набор быстро показывает ошибки в границах интервалов, часовых поясах и обработке `NULL`.
Что именно проверяет такое тестовое задание
На поверхности кандидат решает четыре SQL-задачи. На практике проверяются более глубокие навыки:
1. способность формулировать предпосылки запроса;
2. понимание природы CDC-событий;
3. умение обеспечивать детерминированность;
4. работа с временными интервалами;
5. понимание разницы между текущим и историческим состоянием;
6. контроль кардинальности соединений;
7. знание особенностей оконных рамок;
8. привычка закладывать проверки качества данных.
Сильный аналитик не ограничивается ответом "запрос работает". Он уточняет, при каких условиях результат гарантированно корректен.
Практические рекомендации для кандидата
Перед написанием SQL стоит составить небольшой список вопросов:
- Может ли ключ встречаться несколько раз?
- Гарантирует ли timestamp уникальный порядок событий?
- Что означает удаление?
- Как обрабатывается текущая версия?
- Могут ли временные интервалы пересекаться?
- Что происходит на границе периодов?
- Возможны ли пропуски в истории?
- Какой результат должен получиться при одновременном изменении сегмента и создании заказа?
- На каком уровне считается накопительный итог - заказ, час, день или месяц?
Полезно также самостоятельно создать пограничные тесты: две записи с одинаковым временем, удаление как последняя CDC-операция, заказ на границе SCD-периода, два заказа в один день и клиент с двумя пересекающимися версиями.
Именно такие сценарии отделяют запрос, который случайно работает на учебных данных, от решения, пригодного для промышленной витрины.
