Bad OTP — Data & Lineage

How the two OTP tables behind this dashboard are built. server = Cost tabclient = User-Behavior tab

The two tables cannot be joined — the server table has sms_session_id but no trace_id; the client table has otp_trace_id but no sms_session_id. That's why dashboard filters are split per tab (Country/Channel drive server, Platform/Version drive client).

① dwd_botim_logs_server_otp_session_di server · 122 days

One row = one carrier SMS send, enriched with its verification outcome. Powers the Bad OTP / Cost tab and the supplier analysis. Hive EXTERNAL Parquet, partitioned by dt (yyyyMMdd), at obs_abucc.db/…; queried via Grafana StarRocks (fevnxapb1i0hsf).

Lineage — LEFT JOIN two server log streams on sms_session_id

┌─ carrier-send log ─────────────┐ ┌─ VerifyAuthCode log ───────────┐ │ sms_session_id (uuid) │ │ sms_session_id │ │ carrier_ts / ptype / price │ LEFT │ verify_ts / verify_reason │ │ to_number / purpose │ JOIN │ device_type / country_code │ │ is_carrier_success │ ──────► │ is_verify_success │ └────────────────────────────────┘ on └────────────────────────────────┘ sms_session_id ▼ dwd_botim_logs_server_otp_session_di (1 row / carrier send)

Left = the send, always present. price present ⇒ carrier success; NULL ⇒ failure.
Right = the verify, may be absent. Missing ⇒ the NO_VERIFY drop-off (NULL country → "Unknown" bucket).
carrier_to_verify_ms = verify_ts − carrier_ts.

Reconstructed build HQL (daily)

-- source names are pseudo; real ODS/log tables live in the pipeline
INSERT OVERWRITE TABLE obs_abucc.dwd_botim_logs_server_otp_session_di PARTITION (dt)
SELECT
  s.sms_session_id, s.carrier_ts, s.ptype, s.price,
  CASE WHEN s.price IS NOT NULL THEN 1 ELSE 0 END  AS is_carrier_success,
  s.to_number, s.purpose,                              -- 1=SIGNUP 2=VERIFY 8=PAYBY_NOTIFY
  v.verify_ts, v.verify_reason, v.device_type, v.country_code, v.verify_device_key,
  CASE WHEN v.verify_reason = 'success' THEN 1 ELSE 0 END AS is_verify_success,
  v.verify_ts - s.carrier_ts AS carrier_to_verify_ms, s.dt
FROM ods_carrier_send_log s
LEFT JOIN ods_verify_authcode_log v ON s.sms_session_id = v.sms_session_id AND v.dt = s.dt
WHERE s.dt = '${bizdate}';

Column dictionary

column	meaning
`sms_session_id`	SMS session UUID — join key to VerifyAuthCode
`ptype`	vendor: `ks`=Etisalat, `ys`=BytePlus, `rs`=Transland, `cm`=CM.com
`price`	USD; present ⇒ carrier success, NULL ⇒ failure
`purpose`	1=SIGNUP · 2=VERIFY · 8=PAYBY_NOTIFY (payment notify, NOT OTP)
`verify_reason`	success / wrongCode / authCodeExpired / fingerprintRisk …
`device_type`	0=Android, 1=iOS (present on success only)
`is_verify_success`	1 = verified, 0 = failed/never
`carrier_to_verify_ms`	send → verify duration (ms)

Gotchas: purpose=8 is payment-notification SMS, not OTP → for OTP analysis filter purpose IN (1,2). Rates use denominator = total sends. Valid history starts 2026-02-09 (earlier days had verify unlogged = false 100% fail).

② zhifeng_otp_event_flatten_di client · 1 day (2026-06-28)

One row = one client UI-track event (OTP Sent / Entered / Verification Success·Failed / Block / Page State). Powers the User Behavior tab — the "why users drop off" side the server can't see. Impala Parquet, partition dt.

Source & parsing trick

Source vew.logs_footprint_extra is a firehose: a single json string + dt. Inside, name/when/baseInfo are real nested objects, but payload is a stringified JSON with space-containing keys:

field group	technique
`payload.*` (event, Otp Trace Id, Input Method, Time Stamp)	`regexp_extract(pl,'"Key":"([^"]*)"',1)` on the payload string
`name` / `when` / `baseInfo.*`	`get_json_object(json,'$.baseInfo.x')`

Build flow

vew.logs_footprint_extra (dt) -- raw json string + dt │ pl = get_json_object(json,'$.payload') ▼ filter pl event LIKE 'OTP %' │ regexp_extract payload params + get_json_object baseInfo fields ▼ INSERT OVERWRITE PARTITION (dt='20260628') ▼ vew.zhifeng_otp_event_flatten_di -- 1 row / client OTP event

Impala specifics: STORED AS PARQUET (can't write ORC); from_unixtime(CAST(CAST(ts/1000 AS BIGINT) …)) because from_unixtime needs BIGINT. Full script in the repo (analysis/otp_flatten.hql).

Gotchas (all bit us):

device_type = 0=iOS, 1=Android here — OPPOSITE the server table (0=Android,1=iOS). Verified by brand=apple.
ts_ms semantics differ by platform: iOS = absolute epoch, Android = relative input-duration, ≤4.10 = null. Only ~11% is a real timestamp.
auto-fill OTP Entered is orphaned: fresh standalone otp_trace_id (99% have only that event) → auto-fill conversion/failure unmeasurable until it reuses the session trace_id.
failure_reason / otp_block_type mixed EN/Arabic + case variants — normalize.
Single day only; re-run per PARTITION (dt=…) for multi-day.

Server vs client — the pair

	server (Cost tab)	client (User-Behavior tab)
grain	1 carrier SMS send	1 UI-track event
built from	carrier-send ⟕ VerifyAuthCode on sms_session_id	parsed logs_footprint_extra payload JSON
join key	`sms_session_id` (no trace_id)	`otp_trace_id` (no sms_session_id)
history	122 days (from 2026-02-09)	1 day (2026-06-28)
answers	delivery · vendor · cost · fail type	funnel · blocks · auto-fill · latency · platform
`device_type`	0=Android, 1=iOS	0=iOS, 1=Android

📖 Bad OTP — Data & Lineage