集計の際にデバッグ可能な情報を埋め込む: SQLを検証可能にする

[takegue]

概要

集計は怖いぞ

• 集計クエリが負債化しやすいのは、集計操作が不可逆な操作でなおかつデータソースというas-wasの状態を含むから
• 集計クエリには、集計対象の情報だけでなく集計元やデバッグ情報を埋め込もう
• こういったメタ情報を埋め込むといいんじゃないかという情報を紹介する

どういう情報を埋め込むとよいか

次のような情報を埋め込むとよいと考えている(他何かいいのあれば募集)
逆にとらえると集計という操作は、元テーブルに対して次のような情報を失うことを意味する。
（そりゃ運用できなくなるな。コンテキストが高すぎる）

• 集計対象のテーブルに対するメタデータ: 対象のテーブルは時間経過とともに変わるかもしれない

  • データソース名
  • 集計日時の範囲
  • 集計時でのテーブルのレコード数

• 集計対象のカラムに対するメタデータ

  • 主要な統計量
  • ユーザやページといった主要なエンティティに関する統計量
    • 特定のユーザIDによるskewが発生しているなどがあとからわかると嬉しい
  • approx_top_countや approx_quantilesが便利である

• 集計操作に関するメタデータ

  • 集計条件
  • 条件内外のデータ

• (JOINする場合) 結合操作に関するメタデータ

  • テーブルとテーブル間の関係は後からではわからない
  • 1:1なのか1:Nなのか 1: 0or1 なのか... といったことがわかるもの

• 実行時のジョブに対するメタデータ

  • 実行日時
  • ジョブの作成に利用したそのほかパラメータ
    • BigQueryであれば __PARTITIONTIME などのパラメータ
    • 集計条件などを入れるとよいかもしれない
    • 関数などのバージョン番号

集計クエリ1 (group by単体)

上記をもとに、集計クエリを書き換えてみる

before:

with source as (
  select * from `table`  --> このテーブルは 日々replaceされるかもしれない. 古いレコードは削除されるかもしれない
)
, agg as (
  select 
     dim1 --> dim1のラベルは減ったり増えたり、欠損するようになるかもしれない
     , dim2
    -- 何かしら集計したい指標
     , sum(value) as sum_value --> 特定のユーザ (user_id)の寄与が99%かもしれない. 値域が変わるかもしれない
  from source
  where 
    dim4 = "some_condition"  --> ドリフトにより 条件外の時のデータが当時と違って増えるかもしれない. dim4のratio情報が必要
  group by dim1, dim2 
)

select * from agg

after:

with source as (
  select  
    _PARTITIONTIME  as __PARTITIONTIME
    , * 
  from `table` 
  where _PARTITIONTIME between "2022-06-10" and "2022-06-12"
)
, _metadata_table as (
   select as struct
    "`table`" as name
     , min(__PARTITIONTIME) as __PARTITIONTIME_from
      , max(__PARTITIONTIME) as __PARTITIONTIME_to
     ,  count(1) as n_records
     -- フィルタ操作に対するメタデータ
     ,  countif(dim4 = "some_condition")  as n_matched
   from source
)
, agg as (
  select 
     dim1
     , dim2
     , sum(value) as sum_value
     -- metadata
     , struct(
       struct(
          -- valueの統計値
          struct(
            min(value) as min
            , min(value) as max
            , avg(value) as avg
            , approx_qunatiles(value, 10) q10tiles
          ) as value
          -- valueに関わるエンティティの統計情報
          , struct(
　	　  approx_top_sum(user_id, sum_value, 10) as top5_sum
	      , approx_top_count(user_id, 10) as top10_count
          ) as user
       ) as column
       ,  _metadata_table as table
       , struct(
         current_timestamp() as created_at
       ) as job
     ) as _metadata
  from source, _metadata_table
  group by dim1, dim2
)

select * from agg