C データエンジニアリング — iOS のカート追加が 38% 欠けたまま 11 日、dbt test は緑（イベント件数の監視）

概要

問題

MOps 販促システムチームは GA4 / Firebase の BigQuery エクスポートを dbt で日次集計し、カゴ落ちメール・リターゲティング・CVR ダッシュボードに使っている。3 月に次のことが起きた（合成データ上の値）。

📋 コピー# sources: freshness warn_after 26 hour（最新の行の時刻だけ）
# stg_events: event_id not_null / unique, platform accepted_values

THRESHOLD = 0.30
def check(client, slack):
    rows = client.query("""select event_date, count(*) as n from `mops.stg_events`
      where event_date >= date_sub(current_date('Asia/Tokyo'), interval 2 day)
      group by 1 order by 1""").result()
    yesterday, today = [r.n for r in rows][-2:]
    if abs(today / yesterday - 1) > THRESHOLD:          # テーブル全体・前日比
        slack.post("#data-alerts", f"stg_events の行数が前日比 {today/yesterday-1:+.0%}")

• 系列 12（ios / android / web × page_view / product_view / add_to_cart / purchase）。曜日・共通のぶれ約 5%・platform ごと約 3% 仮定
• 販促カレンダーあり: 月次セール（25〜28 日、流入 1.6 倍）・Black Friday・年末年始・GW・お盆。カレンダーに無い急増が年 3 日 仮定
• 注文 DB は正しい。web の purchase イベントは注文の 93% しか届かない 仮定
• チームの合意: 誤報は月 1 回まで 仮定
• 案 A（後輩）: Elementary の volume_anomalies を既定値で。案 B（先輩）: SDK の送信カウンタと到着数を照合

期待する回答形式: 問題点 8 点／改善後の dbt モデル・判定ロジック・閾値の決め方・通知の運用／実行例（誤報数と検知の遅れ）／案 A・B の評価と前提が変わったときの判断／扱わなかった範囲

ヒント（段階的開示）

level = median(同じ曜日・催事でない・鳴っていない直近 8 日の log 件数)
resid = log(今日) - level - (催事日なら 過去の同種の催事の resid の中央値)
scale = 1.4826 * MAD(直近 56 日の clean な resid)
fired = 0 件 or |resid / scale| >= K      # K は誤報予算から校正

問題点分析（8 点）

図（SVG）— 基準をどう作るか

I6（週 1.5% ずつ減る）は Good でも 7〜68 日かかった。日々のぶれ 0.07〜0.1 の件数で緩やかな変化を早く見るのは今回の設計の範囲外。

Bad → Good（コード差分）

📋 コピー# テーブル全体の行数
select event_date, count(*) as n
from `mops.stg_events` group by 1

THRESHOLD = 0.30  # 根拠なし
# 前日と比べる（曜日・セールで毎月鳴る）
if abs(today / yesterday - 1) > THRESHOLD:
    slack.post("#data-alerts", ...)
  # 系列を名指せない・持ち主不明
  # 2 日目は壊れた前日と比べて黙る

# dbt test: not_null / unique / accepted_values
#   → 欠けた行は検査の対象に来ない
# freshness: 最新の行の時刻だけ

📋 コピー-- 24 系列: 件数 12・段の比 9・注文 DB との照合 3
-- 0 件の日も行を作る（grid）
{% set K = 4.0 %}  -- 誤報予算 月1回 から校正
level  = 同じ曜日 clean 8 日の中央値
effect = 過去の同種の催事の残差の中央値
         -- 過去 3 回未満なら unchecked
scale  = 1.4826 * MAD(直近 56 日の clean な残差)
fired  = value = 0 or |z| >= K
is_clean = kind is null and not fired
  -- 鳴った日・催事日は基準に戻さない
  -- 意図した変化は mon_baseline_resets に登録

# 通知: 1 日 1 通、系列の持ち主へメンション
#   続く事故は「継続 N 日目」で同じスレッド

模範解答（要点）

方針

dbt モデル（系列を縦持ちで 1 か所に）

📋 コピー-- models/monitoring/mon_series_daily.sql  粒度: d × series
with ev as (
  select parse_date('%Y%m%d', event_date) as d, lower(platform) as platform,
         case event_name when 'view_item' then 'product_view' else event_name end as event,
         count(*) as n
  from {{ source('ga4', 'events') }}
  where _table_suffix >= format_date('%Y%m%d', date_sub(current_date('Asia/Tokyo'), interval 3 day))
    and event_name in ('page_view', 'view_item', 'add_to_cart', 'purchase')
  group by 1, 2, 3
), grid as (  -- 0 件の日も行を作る。行が無いと「0 件」を検査できない（#8）
  select d, platform, event, coalesce(ev.n, 0) as n
  from unnest(generate_date_array(...)) as d
  cross join unnest(['ios', 'android', 'web']) as platform
  cross join unnest(['page_view', 'product_view', 'add_to_cart', 'purchase']) as event
  left join ev using (d, platform, event)
), wide as (...), orders as (  -- 独立した数え先
  select order_date as d, platform, count(*) as n_orders from {{ ref('fct_orders') }} group by 1, 2
)
select d, concat('vol:', platform, ':', event) as series, n as value from grid
union all select d, concat('ratio:', platform, ':', s.name), safe_divide(s.num, s.den) from wide, unnest([...]) s
union all select d, concat('recon:', platform, ':purchase/orders'), safe_divide(pur, n_orders)
          from wide join orders using (d, platform)

判定（1 日ずつ積む incremental）

📋 コピー-- models/monitoring/mon_series_alerts.sql  過去の行（{{ this }}）を基準に使う
{% set K = 4.0 %}  {% set LEVEL_WEEKS = 8 %}  {% set SCALE_DAYS = 56 %}  {% set MAD_TO_SIGMA = 1.4826 %}
level as (  -- 先に直近 8 日に絞り（内側の QUALIFY）、外側で中央値。逆にすると全履歴の中央値
  select series, any_value(med) as level from (
    select series, percentile_cont(lv, 0.5) over (partition by series) as med from (
      select t.series, h.lv from today t join hist h
        on h.series = t.series and h.is_clean
       and extract(dayofweek from h.d) = extract(dayofweek from t.d)
      qualify row_number() over (partition by t.series order by h.d desc) <= {{ LEVEL_WEEKS }}))
  group by series
), ...
select *,
  value = 0 or (not unchecked and abs(coalesce(z, 0)) >= {{ K }})  as fired,
  -- 0 件はばらつきが 0（z が NULL）でも鳴る
  kind is null and not (...fired...)                               as is_clean
  -- 鳴った日・催事日は明日以降の基準に使わない
from judged

seed（人が更新する表）

📋 コピーseeds:
  - name: promo_calendar        # d, kind。販促チームが更新する（監視でいちばん効いた部品）
  - name: mon_baseline_resets   # series, reset_from, reason, approved_by。意図した変化を「新しい水準」に
  - name: mon_series_owners     # ios:* → @ios-team。通知のメンション先

閾値の校正（Python）

📋 コピーdef calibrate(seeds, budget_per_month, base=GoodConfig(), grid=(3.0, 3.5, 4.0, ...)):
    """事故なしの校正用データで、誤報が予算以下になる最小の k を選ぶ（評価用とは別の種）。"""
    datas = [generate(s) for s in seeds]
    for k in grid:
        table[k] = mean(false_alarms_per_month(detect_good(d, replace(base, k=k)), d) for d in datas)
    return next(k for k in grid if table[k] <= budget_per_month)  # → 4.0

実行例（実測・合成データ）

📋 コピーcd 2026-10-14-C-data-engineering-code
python3.14 evaluate.py                                  # 校正 → 4 方式の比較 → 部品を 1 つずつ外す（約 66 秒）
python3.14 -m unittest -v test_volmon                   # Good の約束 15 本
DETECTOR=elementary python3.14 -m unittest test_volmon  # 案 A' に当てる（赤が期待値）
python3.14 mutate.py                                    # Good を 14 通りに壊す

校正（事故なし・校正用の種）

k=3.0: 5.07  k=3.5: 1.84  k=4.0: 0.70  k=4.5: 0.43  k=5.0: 0.32
予算 1.0/月 以下の最小 k = 4.0

検知（遅れ日数 × 5 種、括弧は事故中に鳴った日の割合）

テストと変異テスト

迷った案と採らなかった理由

ポイント解説

1. 欠けた行は行のテストでは見つからない: dbt test が緑であることは、欠落が無いことを何も保証しない
2. 基準を作るのが監視の本体: 前日比は曜日とセール、平均と標準偏差は学習のセール 2 日、鳴った日を入れると続く事故で壊れる。いちばん効いたのはチームの販促カレンダー
3. 閾値は誤報予算から逆算: 「3σ」は誤報の回数を決めない。24 系列 × 毎日なら k=3 で月 5 回
4. 見えない大きさを言える監視に: 件数では −12% は無理。比（0.045）と照合（0.016）を足し、「何 % を何日で」を表で持つ
5. ロバストは保険で、今回は損をした: 正規分布のぶれでは中央値の効率は約 64%。一方で催事の登録漏れ（M14）の被害を小さくしたのは中央値

扱わなかった範囲（止め方）

• BigQuery・dbt・Elementary は未実行。SQL は構文検査もしていない。Python と SQL の一致（契約テスト）も未実施
• 案 A は Elementary のドキュメントから再現した相当実装（exclude_detection_period_from_training の既定値はページが 404 で未確認）。案 B はシミュレーションしていない
• 合成データのぶれは正規分布だけ。本番の裾の重さは未測定
• 世界の実物の中核概念 11 個（鮮度・件数・スキーマ変更・列の異常・系列ごと・季節性・学習と検知の期間・除外・感度・通知先・事故のまとめ）のうち扱ったのは約 6〜7 割。鮮度・スキーマ変更・列の異常は扱っていない
• 被害額は回収率の根拠が無いので出していない。時間単位の監視（当日中の検知）も範囲外

今日のまとめ

欠けた行は行のテストでは見つからず、テーブル全体の前日比では 1% の系列の 38% 欠落は −0.45% にしか見えなかった。系列ごとに同じ曜日の中央値と販促カレンダーの効果を引いた残差で判定し、閾値を誤報予算（月 1 回）から k = 4.0 と校正し、鳴った日を基準から外し、比と注文 DB との照合を足すと、誤報 0.95/月 のまま本問の事故を 5 種とも初日に名指しし、30 日間鳴り続けた（合成データでの実測）。ロバスト統計は今回のデータでは平均と標準偏差に負けた。どちらを採るかは本番の残差を測ってから決める。

自己評価（あとで記入）