lifecycle_report__dm — adversarial review

dm.lifecycle_report__dm: code + live-data review

Цель: найти логические баги, data-quality риски и несостыковки в наполнении lifecycle-витрины. Google Sheet из запроса не редактировался.

checked: 2026-06-29 UTCrepo: airflow_repository master @ 741405alive CH: dm.lifecycle_report__dm2 Claude Code subreviews

Executive verdict

Витрина пригодна только как ограниченная “первые 10 lifecycle stages” воронка, если потребитель явно фильтрует removed/test/admin и понимает, что Attempted/Deposit живут на одном stage. Для “все депозиты”, provider-performance, регуляторных/финансовых выводов или C-level конверсий без фильтров она опасна: дефолтный denominator загрязнен, successes после 10-го silently отрезаны, а numeric stage легко приводит к неверным агрегациям.

Findings by severity

Critical · business-definition risk

Registration denominator включает removed/test/admin пользователей

SQL берет всех пользователей из global_users__ads FINAL и для каждого делает Registration row; фильтра по is_removed/group_name нет. Live: 3,207,201 registration rows; из них 224,349 имеют is_removed=1. В source-users дополнительно 82,868 строк с group_name IN ('Test account','Admin'). В lifecycle нет group_name, значит test/admin нельзя убрать downstream без join-а обратно к users.

Evidence: lifecycle_report__dm.sql:61-77,162-195; global_traffic_quality__dm.sql:41-47 фильтрует is_removed=0; psp_monitoring_hourly__cdm.sql:87-89 исключает Test/Admin.

Critical · silent truncation

Витрина хранит только первые 10 успешных депозитов

Код явно режет successful_deposit_number <= 10 и success_group <= 10. Live сверка: source ads.global_payments__ads содержит 10,756,517 успешных deposit orders; lifecycle показывает 2,105,445 success rows. Missing 8,651,072 orders — это ровно successes beyond the 10th у 112,708 пользователей.

Это может быть intended для lifecycle funnel, но тогда должно быть в названии/описании и явно запрещено использовать как “all successful deposits”. Evidence: lifecycle_report__dm.sql:114,153.

High · aggregation trap

`stage` коллидирует: “N Attempted” и “N Deposit” имеют один номер

1 Attempted Deposit и 1 Deposit оба лежат на stage=2; и так для N=1..10. Live: 1,662,038 orders представлены одновременно как attempt и success rows с тем же sur_order_id. Если BI агрегирует по stage, attempts и successes смешиваются/дублируются.

Evidence: lifecycle_report__dm.sql:102,141,185; live event distribution checked 2026-06-29.

High · metadata/grain mismatch

`unique_key='sur_user_id'` не соответствует grain

Declared key/order_by — только sur_user_id, но фактический grain: (sur_user_id, event_name / funnel event). Live: 7.76M rows на 3.21M users; docs сами описывают “одна строка на (sur_user_id, событие воронки)”. На plain ReplicatedMergeTree это не enforcing bug, но вводит в заблуждение тесты, каталог и будущих maintainers.

Evidence: lifecycle_report__dm.sql:3-7,197-203; catalog dm__lifecycle_report__dm.md:42-44.

Medium · field semantics

`failed_deposit_number` ранжируется по каждому non-SUCCESS статусу отдельно

Поле называется как порядковый номер failed deposit, но window partition = (sur_user_id, status_name), поэтому CANCEL #1 и EXPIRED #1 у одного пользователя оба получают failed_deposit_number=1. Live: 36,955 users имеют duplicate failed-number groups.

Evidence: lifecycle_report__dm.sql:52-56; non-success attempt statuses: CANCEL 481,670 rows, EXPIRED 303,372 rows.

Medium · dictionary dependency

Пустой `brand_name` из словаря брендов

Live: 94 rows have empty brand_name, concentrated in brand_id=405, 406 on luxury and 55 on main. DAG depends only on users+payments datasets, not on brand dictionary freshness; new brands can land blank.

Evidence: dictGetString('dicts.global_brands_dict', ...) in lifecycle_report__dm.sql:16-19,65-68; DAG schedule dags/lifecycle_report__dm.py:21.

Low · code fragility

Registration branch uses empty join to synthesize payment-shaped NULLs

successful_deposits_empty AS (SELECT * ... WHERE 1=0) is joined via USING (sur_user_id) so Registration rows inherit the union schema. It works today, but is fragile and makes unqualified columns hard to reason about. Safer: explicit NULL casts for payment columns and u.-qualified user fields.

Evidence: lifecycle_report__dm.sql:156-160,162-195. Claude subreview flagged analyzer/ambiguity risk; live build currently succeeds.

Low · monitoring blind spot

Freshness check monitors `registration_date`, not payment freshness

Monitoring config tracks max registration_date for lifecycle. This proves new registered users arrive, but does not prove new payment attempts/successes are present. For a payment lifecycle mart, add a payment-created freshness/data-volume DQ check too.

Evidence: pipelines/configs/monitoring_config.py:37-45.

Live ClickHouse evidence

Check	Result	Why it matters
Total / users	7,760,066 rows 3,207,208 users	Many rows per user; lifecycle-event grain, not user grain.
Registration rows	3,207,201	Equals source `ads.global_users__ads FINAL` count; every user gets a registration row.
Removed users included	224,349	Included by default in funnel denominator.
Test/Admin in source users	82,868	Lifecycle lacks `group_name`, cannot filter these without external join.
Successful deposit source vs lifecycle	10,756,517 → 2,105,445	Lifecycle keeps only first 10 successful deposits per user.
Success orders beyond cap	8,651,072	All missing source successes are beyond 10th deposit for 112,708 users.
Attempt rows with SUCCESS status	1,662,038 same-order overlaps	Attempt and success rows can represent the same order; numeric-stage aggregation is unsafe.
Duplicate event rows	0 duplicate user-event groups	No evidence of duplicate `(sur_user_id, brand_id, event_name)` in current live table.
Null / empty dimensions	7 null registration_date rows 94 empty brand_name rows	Small but real dimension holes; brand blanks point at dictionary freshness/coverage.

Check

Result

Why it matters

Total / users

7,760,066 rows
3,207,208 users

Many rows per user; lifecycle-event grain, not user grain.

Registration rows

3,207,201

Equals source ads.global_users__ads FINAL count; every user gets a registration row.

Removed users included

224,349

Included by default in funnel denominator.

Test/Admin in source users

82,868

Lifecycle lacks group_name, cannot filter these without external join.

Successful deposit source vs lifecycle

10,756,517 → 2,105,445

Lifecycle keeps only first 10 successful deposits per user.

Success orders beyond cap

8,651,072

All missing source successes are beyond 10th deposit for 112,708 users.

Attempt rows with SUCCESS status

1,662,038 same-order overlaps

Attempt and success rows can represent the same order; numeric-stage aggregation is unsafe.

Duplicate event rows

0 duplicate user-event groups

No evidence of duplicate (sur_user_id, brand_id, event_name) in current live table.

Null / empty dimensions

7 null registration_date rows
94 empty brand_name rows

Small but real dimension holes; brand blanks point at dictionary freshness/coverage.

Recommended fixes / next actions

Priority	Action	Suggested implementation
P0	Declare contract: “first 10 lifecycle stages”, not all deposits.	Rename/describe mart in catalog + BI docs; add a `max_stage=10` caveat. If stakeholders need all deposit orders, use `global_payments_report__dm(f)` or `ads.global_payments__ads`.
P0	Decide filtering policy for funnel denominator.	Either filter out `is_removed=1` and `group_name IN ('Test account','Admin')` in the mart, or add `group_name`/`is_test` columns and require dashboard filters.
P1	Fix stage semantics.	Split numeric fields: `step_number`=1..10, `event_type`=attempt/success/registration; avoid aggregating by shared `stage`.
P1	Correct materialization metadata.	Use composite key/order_by such as `(sur_user_id, brand_id, event_name, sur_order_id)` or at least document why no unique key applies.
P2	Make Registration branch explicit.	Replace empty join hack with explicit `CAST(NULL AS ...)` payment columns and qualify user fields.
P2	Improve DQ checks.	Add payment-created freshness, row-count deltas by event_name/stage, and brand_name empty-count alert.

Priority

Action

Suggested implementation

Declare contract: “first 10 lifecycle stages”, not all deposits.

Rename/describe mart in catalog + BI docs; add a max_stage=10 caveat. If stakeholders need all deposit orders, use global_payments_report__dm(f) or ads.global_payments__ads.

Decide filtering policy for funnel denominator.

Either filter out is_removed=1 and group_name IN ('Test account','Admin') in the mart, or add group_name/is_test columns and require dashboard filters.

Fix stage semantics.

Split numeric fields: step_number=1..10, event_type=attempt/success/registration; avoid aggregating by shared stage.

Correct materialization metadata.

Use composite key/order_by such as (sur_user_id, brand_id, event_name, sur_order_id) or at least document why no unique key applies.

Make Registration branch explicit.

Replace empty join hack with explicit CAST(NULL AS ...) payment columns and qualify user fields.

Improve DQ checks.

Add payment-created freshness, row-count deltas by event_name/stage, and brand_name empty-count alert.

Evidence snippets

Lifecycle SQL hotspots

-- lifecycle_report__dm.sql
config(materialized='distributed_table', engine='ReplicatedMergeTree', unique_key='sur_user_id', order_by='sur_user_id')

payments_base:
  row_number() over (partition by sur_user_id order by created_at, order_id) as deposit_number
  sum(if(status_name='SUCCESS',1,0)) over (partition by sur_user_id ... 1 preceding)+1 as success_group

payments:
  successful_deposit_number: partition by sur_user_id, status_name
  failed_deposit_number:     partition by sur_user_id, status_name
  success_group_attempt:     partition by sur_user_id, success_group

deposit_attempts:
  stage = success_group + 1
  where success_group <= 10 and success_group_attempt = 1

successful_deposits:
  stage = successful_deposit_number + 1
  where successful_deposit_number <= 10

registration_event:
  from users u left join successful_deposits_empty using (sur_user_id)

Core live checks

overview:
rows=7,760,066; users=3,207,208
registration_rows=3,207,201
successful_rows=2,105,445; attempted_rows=2,447,420
max_created_at=2026-06-29 11:03:36

removed/test/admin:
lifecycle removed registration rows=224,349
source users Test account=72,336; Admin=10,532
source cleanish users=2,900,968 vs registration rows=3,207,201

cap:
source SUCCESS deposit orders=10,756,517
lifecycle success rows=2,105,445
source success orders missing from lifecycle=8,651,072
users with >10 successes=112,708

Sources and caveats

Repo checked locally on 2026-06-29: /home/ubuntu/src/data/airflow_repository, branch master, commit 741405a.

Main model: dbt_project/models/dm/lifecycle_report__dm.sql; DAG: dags/lifecycle_report__dm.py; DQ config: pipelines/configs/monitoring_config.py.

Adjacent models reviewed: global_payments_report__dm.sql, global_traffic_quality__dm.sql, psp_monitoring_hourly__cdm.sql, upstream global_payments__ads.sql, global_users__ads.sql.

Live warehouse checked via ClickHouse HTTP on 2026-06-29 UTC. Query results are point-in-time; table metadata modification time observed: 2026-06-29 11:13:49.

Two independent Claude Code read-only subreviews were run; their findings were used as adversarial input, then cross-checked against code/live data where feasible.

Caveat: I did not modify the Google Sheet and did not deploy code changes. This report is review-only.