一言まとめ
SAFE_CAST と CAST はケースに応じて使い分けよう。
問題の発端
それは、いつものようにデータ基盤（BigQuery）の開発を進めていたある日のこと。
「このテーブルの○月×日以降のデータが入って無さそうなので確認してほしい」
この利用者からの連絡を受けてソワソワしながら確認したところ、
以下のテーブルのような状況になっていました。
| dt | aaa | bbb |
|---|---|---|
| 2023-07-10 | xxxx1 | yyyy1 |
| 2023-07-11 | xxxx2 | yyyy2 |
| 2023-07-12 | xxxx3 | yyyy3 |
| 2023-07-13 | NULL | yyyy4 |
| 2023-07-14 | NULL | yyyy5 |
| 2023-07-15 | NULL | yyyy6 |
2023-07-13 以降の aaa カラムのデータが NULL になっている...
連絡がなければ、全然気づけなかった事象でした。
データの欠損に気づけなかった原因
データの欠損自体は起こりうるものとした時に、
「データの欠損に長期間気づけなかったこと」は大きな問題です。
この問題を調査したところ、原因が大きく二つありそうでした。
- 定期的なデータの品質テストを行っていなかった
- データ変換時にエラー出力がなかったため、気づけなかった
「1.」は今後取り組んでいく課題として設定しました。
一方で「2.」を掘り下げてみると、
データの変換時に SAFE_CAST 関数を利用したことが、欠損に気づけなかった原因だということが見えてきました。
（前提情報として）データ基盤のシステム構成
そもそも SAFE_CAST 関数をどの処理で利用していたのかを説明するために、
データ基盤のシステム構成の一部を簡単に紹介します（※）。
※簡略化した構成図になっています。詳細なシステム構成は別記事で紹介します。
運用するデータ基盤には、Google BigQuery を採用しています。
データのワークフローは以下の図のようになっていて、ELT の形でデータを処理しています。
T（Transform） の部分は dbt を利用して SQL で変換処理を行う形です。
上図の dbt でデータの型を変更する際に、SAFE_CAST or CAST 関数を利用しています。
参考：CAST｜BigQuery リファレンス
参考：SAFE_CAST｜BigQuery リファレンス
CAST と SAFE_CAST の挙動の違い
BigQuery にはデータの型変換をする関数に SAFE_CAST と CAST 関数が用意されています。
CAST 関数は、変換先に指定した型に変換できるデータでない場合、エラーを出力します。
一方で SAFE_CAST 関数は、変換先に指定した型に変換できるデータでない場合、NULL として出力します。
今回のポイントは、SAFE_CAST 関数がエラーにならないという点です。
エラーにならない、つまり ELT の処理を止めないことによって、
意図しないデータが入った場合にすぐ気づけない状況になっていました。
SAFE_CAST と CAST の使い分け
ではエラー検知を考慮するなら全て CAST にすれば良いのでは、、
と思うところですが、SAFE_CAST も利用するメリットがあります。
そこでそれぞれの利用ケースをまとめてみました。
SAFE_CAST の利用ケース
- 指定した型に変換できないデータは全て
NULLとする場合 - 別途テストなどでエラー検知する仕組みを用意している場合
特に「空文字 or 特定の型のデータのみを含む変換」の場合には、
SAFE_CAST だと SAFE_CAST({column_name} AS INT64) で OK ですが、
CAST だと CAST(IF({column_name} = '', NULL, {column_name}) AS INT64) のように
IF 文を挟む必要が出てくるので、SAFE_CAST の方がシンプルです。（※）
※ CAST('' AS INT64) を実行すると Bad int64 value: のエラーになります。
CAST の利用ケース
- 指定した型に変換できないデータはエラーとする場合
- 別途テストなどでエラー検知する仕組みを用意していない場合
意図しないデータの混入をいち早く検知するのであれば、
CAST で型変換するようにしておく方が無難かな、という所感です。
今回のケースは CAST を利用するようにし、意図しない型のデータが入る場合はエラーになるように変更しました。
まとめ
SAFE_CAST でデータの欠損に気づけなかった問題の経緯と CAST との使い分け案をまとめてみました。
データの型変換処理を実装する際に気を付けるポイントとして、参考になれば幸いです。
データの欠損、一緒に防いでいきましょう！
Discussion