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

Домашка: статусы клиента от STG до DDS (и немного DM)

Небольшое практическое задание на 1–2 вечера: по данным о смене статусов клиента (CRM) построить цепочку слоёв STG → ODS → DDS (SCD2) и, по желанию, небольшую витрину в dm.

Цель — потренировать руками:

  • работу со слоями DWH (stg / ods / dds / dm);
  • проектирование и загрузку измерения с историей (SCD Type 2);
  • аккуратную работу со временем (event_ts, valid_from, valid_to).

Исходим из того, что вы уже прошли основную статью dwh-modeling/README.md и познакомились с примером интернет‑магазина.

1. Данные: события смены статуса клиента

Представьте, что в CRM для каждого клиента хранится история статусов:

  • new — только что зарегистрировался;
  • active — делал покупки недавно;
  • vip — часто покупает и много тратит;
  • churned — давно ничего не делал, считаем «отвалившимся».

Эта информация приходит в DWH в виде событий (events): «у клиента X в момент времени Y статус стал Z».

В репозитории в каталоге dwh-modeling/data лежит файл:

  • customer_status_events.csv

Структура файла:

customer_id,status,event_ts,_load_id,_load_ts
101,new,2024-01-01 09:00:00,batch_20240101_1000,2024-01-01 10:00:00
...

Колонки:

  • customer_id — бизнес-ключ клиента (тот же, что и в основном примере — 101, 102, 103);
  • status — статус клиента в CRM (new, active, vip, churned);
  • event_ts — момент, когда статус сменился в CRM;
  • _load_id — идентификатор батча загрузки;
  • _load_ts — момент, когда данные попали в DWH.

Файл содержит несколько клиентов и несколько смен статуса по каждому — этого достаточно, чтобы отработать SCD2.

2. Целевая схема: какие таблицы уже есть

Чтобы не тратить время на DDL, структуры таблиц для домашки уже подготовлены в dwh-modeling/sql:

  • 07_ddl_hw_customer_status.sql — создаёт дополнительные таблицы:
    • stg.customer_status_raw — сырые события о статусе клиента;
    • ods.customer_status — очищенные и типизированные события;
    • dds.dim_customer_status — измерение статусов клиента в формате SCD Type 2.
  • 08_dml_hw_customer_status_template.sql — шаблон DML-скрипта с подсказками и заготовками блоков.

Перед началом работы:

  1. Поднимите demo‑Postgres по инструкции из корневого README.md.
  2. Выполните базовые скрипты DWH:
  3. 01_ddl_stg-dds.sql
  4. 02_dml_stg-dds.sql (нужен как минимум для dds.dim_date)
  5. 05_ddl_dm.sql (создаёт схему dm для витрин)
  6. Выполните DDL для домашки:
  7. 07_ddl_hw_customer_status.sql

После этого схемы stg, ods, dds, dm уже существуют, а дополнительные таблицы для статусов созданы.

3. Часть 1 — STG → ODS (обязательно)

Задача: загрузить CSV в STG и переложить данные в ODS с приведением типов.

3.1. STG: загрузка CSV

Есть два варианта — выберите любой. Для первого прохождения рекомендуем вариант A (самый простой).

Вариант A (рекомендуемый): вставить данные в STG через INSERT

Откройте SQL‑клиент (DBeaver или psql) и вставьте данные текстом:

💡 Если вы уже загружали данные в stg.customer_status_raw и делаете повторный запуск — начните с TRUNCATE stg.customer_status_raw;.

💡 В примере ниже показан минимальный набор для клиента 101. Чтобы получить несколько клиентов и больше событий — используйте вариант B (CSV) или добавьте строки из файла dwh-modeling/data/customer_status_events.csv.

INSERT INTO stg.customer_status_raw (customer_id, status, event_ts, _load_id, _load_ts) VALUES
('101','new','2024-01-01 09:00:00','batch_20240101_1000','2024-01-01 10:00:00'),
('101','active','2024-02-15 10:30:00','batch_20240215_1100','2024-02-15 11:00:00'),
('101','vip','2024-05-10 11:00:00','batch_20240510_1200','2024-05-10 12:00:00'),
('101','churned','2024-09-01 12:15:00','batch_20240901_1300','2024-09-01 13:00:00');

💡 Здесь _load_ts — это время загрузки.

Вариант B: загрузить CSV

Можно загрузить файл dwh-modeling/data/customer_status_events.csv в таблицу stg.customer_status_raw:

  • Через DBeaver: Import Data → CSV → stg.customer_status_raw.
  • Через psql в контейнере (./psql_sh): без установки psql на хост.

Способ: передайте CSV в psql через STDIN и выполните \copy ... FROM STDIN:

./postgres-bookings/psql_sh -c "TRUNCATE stg.customer_status_raw;"
cat dwh-modeling/data/customer_status_events.csv | ./postgres-bookings/psql_sh -c \
  "\\copy stg.customer_status_raw(customer_id,status,event_ts,_load_id,_load_ts) FROM STDIN WITH (FORMAT csv, HEADER true)"

💡 Для части 5 (инкрементальная загрузка) TRUNCATE stg.customer_status_raw делать не нужно — загружайте только новые строки.

После загрузки убедитесь, что данные на месте:

SELECT * FROM stg.customer_status_raw LIMIT 10;

3.2. ODS: очистка и типизация

💡 Обратите внимание: в основном примере ods.customers хранит снимок (одна строка на клиента, PK = customer_id), а здесь ods.customer_status хранит все события (PK = customer_id + event_ts). Это не ошибка, а сознательный выбор: источник данных о статусах - поток событий, и ODS сохраняет эту природу. Подробнее - в комментариях к решению.

В файле 08_dml_hw_customer_status_template.sql найдите заготовку блока ODS и допишите SQL:

  • привести:
    • customer_id → INT,
    • status → VARCHAR(20) (можно оставить как есть),
    • event_ts и _load_ts → TIMESTAMP;
  • аккуратно обработать возможные пустые значения (если бы они были);
  • заполнить _load_id и _load_ts в ods.customer_status.

💡 Для простоты сделайте загрузку идемпотентной через TRUNCATE ods.customer_status; перед INSERT (таблица в ODS имеет первичный ключ (customer_id, event_ts)).

Проверьте, что в ods.customer_status данные выглядят аккуратно:

SELECT * 
FROM ods.customer_status 
ORDER BY customer_id, event_ts;

4. Часть 2 — ODS → DDS (SCD Type 2, обязательно)

Задача: по событиям в ods.customer_status построить измерение dds.dim_customer_status, где каждая строка — период действия статуса.

Целевая таблица уже создана (см. 07_ddl_hw_customer_status.sql):

  • customer_bk — бизнес-ключ клиента (тот же, что customer_id в ODS);
  • status — статус клиента;
  • hashdiff — хэш от атрибутов (здесь достаточно самого status);
  • valid_from / valid_to — период, когда статус был актуален;
  • created_at / updated_at — технические поля.

4.1. Начальная загрузка SCD2

В шаблоне 08_dml_hw_customer_status_template.sql допишите блок начальной загрузки:

  1. Сформируйте промежуточный набор:

  2. customer_bk,

  3. status,
  4. event_ts (как «время начала действия статуса»),

  5. hashdiff (например, md5(status); можно вынести расчёт в отдельную функцию по аналогии с dds.customer_hash для клиентов).

  6. Для каждого клиента отсортируйте события по event_ts и с помощью LEAD() посчитайте (в учебном варианте считаем, что обновление DWH идёт раз в день, поэтому используем DATE):

  7. valid_from — event_ts::date,

  8. valid_to — следующий event_ts::date (а у последней версии valid_to = NULL).

💡 Упрощение для домашки: считаем, что у клиента не бывает двух разных смен статуса в один и тот же день.
Если такое бывает — удобнее строить периоды в TIMESTAMP (или вводить дополнительное правило сортировки), но это уже усложнение.

💡 Если в событиях встречаются повторы одного и того же статуса подряд, можно отфильтровать “не-изменения” через LAG(status) (или LAG(hashdiff)) перед расчётом LEAD().

  1. Вставьте получившиеся строки в dds.dim_customer_status. Актуальная строка для клиента — та, где valid_to IS NULL.

💡 Для первой версии решения можно сделать full refresh: перед вставкой очистить таблицу (TRUNCATE dds.dim_customer_status;), как в основном примере с dds.dim_customer.

INSERT INTO dds.dim_customer_status (
    customer_bk, status, hashdiff,
    valid_from, valid_to,
    created_at, updated_at
)
SELECT
    ...

Проверьте результат:

SELECT *
FROM dds.dim_customer_status
ORDER BY customer_bk, valid_from;

Ожидаемое поведение:

  • у клиента 101 несколько строк с разными статусами и непересекающимися периодами;
  • valid_to IS NULL только у самой свежей строки для каждого клиента.

4.2. Проверка себя

Примеры проверочных запросов (можно придумать свои):

  • «Какой статус был у клиента 101 на дату 2024-06-01?»
    → одна строка с нужным статусом.
  • «Сколько клиентов были в статусе active на 2024-04-10?»
    → несколько строк, если статус активен для диапазона дат.

5. Часть 3 — инкрементальная загрузка (по желанию)

Если хочется потренироваться глубже:

  1. Добавьте ещё несколько событий смены статуса (например, переход части клиентов из churned обратно в active).
  2. Можно дописать в исходный CSV самостоятельно.
  3. Либо взять готовую порцию “для инкремента” из файла dwh-modeling/data/customer_status_events_increment.csv.
  4. Загрузите только новые строки в stg.customer_status_raw (не делайте TRUNCATE):
cat dwh-modeling/data/customer_status_events_increment.csv | ./postgres-bookings/psql_sh -c \
  "\\copy stg.customer_status_raw(customer_id,status,event_ts,_load_id,_load_ts) FROM STDIN WITH (FORMAT csv, HEADER true)"

  1. Напишите логику инкрементального обновления dds.dim_customer_status:

  2. ориентируйтесь на пример из 03_demo_increment.sql для dds.dim_customer;

  3. важно:
    • корректно «закрыть» старую актуальную строку (заполнить valid_to датой начала новой версии);
    • вставить новую строку с valid_to = NULL.

Эта часть особенно полезна, если вы хотите почувствовать, как SCD2 живёт в реальном DWH.

6. Часть 4 — витрина в DM (по желанию)

Опциональное задание для закрепления: собрать небольшую витрину с количеством клиентов по статусам на каждую дату.

DDL витрины уже создан в 07_ddl_hw_customer_status.sql (таблица dm.mart_customer_status_daily).

Идея:

  • использовать dds.dim_date как календарь;
  • для каждой date_actual найти, какой статус был у клиента в этот день (через JOIN на dds.dim_customer_status по диапазону valid_from/valid_to);
  • агрегировать по status.

Пример запроса к витрине:

SELECT
    date_actual,
    status,
    customers_cnt
FROM dm.mart_customer_status_daily
WHERE date_actual BETWEEN '2024-04-01' AND '2024-04-30'
ORDER BY date_actual, status;

7. Как вписать эту домашку в обучение

Рекомендуемое место в дорожке:

  1. Пройти основную теорию по DWH и SCD:
  2. dwh-modeling/README.md
  3. dwh-modeling/SCD.md
  4. Разобрать базовый пример интернет‑магазина (скрипты 01_–06_).
  5. Выполнить эту домашку как первую попытку «самостоятельного» моделирования и ETL:
  6. познакомиться с ещё одним измерением с историей (dim_customer_status);
  7. потренироваться аккуратно работать с датами и периодами;
  8. при желании — собрать простую витрину в dm.

Если что‑то не получается — можно разбирать решения по шагам вместе с ментором: от простого SELECT из STG до полноценного SCD2 в DDS.

8. Эталонное решение

Когда выполните домашку и захотите сверить результат — готовое решение лежит в файле 09_dml_hw_customer_status_solution.sql.

Постарайтесь не подглядывать до того, как напишете свой вариант — основная ценность задания именно в самостоятельном разборе.