dataformでsourceテーブルから中間テーブルを生成してassertionを書いていた

検算したら件数が合わないなーということで調べた

次のようなSQLでfrom,toを指定して単月分のレコードのみ抜き出すというパターン

SELECT
  *,
  'private' AS workspace
FROM
  `sample.rawdata-private`,
  UNNEST(data) AS d
WHERE
  DATE(start) BETWEEN ${target_date.from}
  AND ${target_date.to}

SQLXなのでtarget_date.toとtarget_date.fromはその時々によって変化する

今回は2021-04-01 ～ 2021-04-30をという感じ

rawdata-privateはAPIのレスポンスをそのまま保存していて1行にtotal_countとdata列に実際のレコードがあるのでUNNESTしてレコード数と比較することで確認している

rawdata-privateのレコードを追ってみると

"start": "2021-04-01T04:57:39+09:00",

のデータがDATE(start)を通すことで2021-03-31になっていた

なるほどUTC

DATE(start, 'Asia/Tokyo'),でタイムゾーン指定の日付データに変換できるのでこれで対応

BigQueryがDATEでよしなにやってくれた結果UTCで解釈すると2021-03-31となってしまうためフィルタ対象から外れてしまい、件数が合わない状態になっていた

正直assertion書いてなかったら気付かなかったのでassertion大事w