Практика: спроектировать путь данных до отчёта

Это самостоятельный учебный кейс: таблицы заказов нужны только для практики. В базе есть поставки версий заказов, текущая таблица заказов, история признаков клиентов и журнал загрузок. На этих четырёх таблицах проверим свежесть поставок, выбор исторической версии и согласованность текущего состояния перед публикацией отчёта.

Какие данные пришли

Период версии клиента имеет вид [valid_from, valid_to): начало включено, конец исключён. В учебных данных встречаются пропуски и перекрытия периодов. У части заказов customer_id отсутствует в справочнике. Поэтому соединение по одному customer_id не гарантирует, что для заказа найдётся ровно один сегмент.

Допустим, к 09:00 UTC нужен дневной отчёт: сколько заказов создали вчера по сегменту клиента на дату создания и сколько из них сейчас имеют каждый статус. Вторая часть зависит от того, до какой поставки мы успели применить изменения. Если вчерашний заказ изменился сегодня в 08:58, но поставка ещё не обработана, отчёт не должен притворяться, что знает новый статус.

Путь к витрине

Приём. Сохраняем полученные версии и дату поставки. Если источник отдаёт только текущий снимок заказов, он не восстановит статусы, которые успели измениться до первого снимка. Дальше можно получать инкремент или CDC, но начальная граница и дальнейшие изменения должны стыковаться без пропуска и двойного применения.

Staging. Проверяем типы, допустимые статусы и обязательные ключи. Пара order_id, updated_at помогает различать версии; повторная доставка той же пары не создаёт новое изменение. Некорректную строку отправляют в отдельный набор ошибок с причиной, а число принятых и исключённых строк сверяют с поставкой.

Ядро. Для текущей таблицы выбирают последнюю версию каждого заказа по updated_at. Если две разные записи имеют одинаковый ключ и время, нужно дополнительное правило порядка, а не случайный выбор. Отдельно сохраняют историю клиента. Чтобы получить сегмент на дату заказа, соединяют по customer_id и условию created_at::date >= valid_from AND created_at::date < valid_to. Нулевое или двойное совпадение — проблема качества, которую нельзя скрывать INNER JOIN.

Витрина. Зерно может быть «день создания, исторический сегмент, текущий статус». Заказ без надёжного сегмента либо попадает в явно обозначенную группу неизвестных, либо задерживает публикацию — правило выбирают заранее. При поздней версии заказа пересчитывают затронутый день и публикуют новую согласованную версию результата.

Три проверки перед публикацией

1. Свежесть. В load_log есть статусы success, failed и running. Последняя попытка не обязательно успешна. Для каждой таблицы берём максимальную дату только успешной загрузки.
2. Однозначность истории. Для каждого заказа считаем версии клиента, действовавшие на дату created_at. Должна найтись ровно одна. Ноль означает потерянную связь или промежуток в истории, больше одной — перекрытие версий.
3. Сверка состояния. Последняя по updated_at версия из stg_orders должна совпадать со статусом в orders. Если расходится, витрина не должна молча использовать «какой-нибудь» источник.

Эти проверки не равны проверке одного общего количества строк. Можно сохранить число заказов и одновременно приписать часть из них неверному сегменту. Журнал поставок, однозначное историческое соединение и сверка текущего состояния отвечают на разные вопросы.

Проверьте решение на таблицах

Начните со свежести. Условие внутри FILTER важно: неуспешная попытка не делает данные готовыми.

SELECT table_name,
       max(load_date) FILTER (WHERE status = 'success') AS last_ok
FROM load_log
GROUP BY table_name
ORDER BY table_name;

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