はじめに
概要
dbtのtest機能を使用することで、SQLプロジェクトのテストを自動化できます。
また、dbtでは多種多様なテストがあるので、SQLプロジェクトが期待どおりに機能することを様々な角度から確認できます。基本的なテストとして、NULLチェック、重複レコードチェック、範囲チェック、比較チェックなどがあります。これらのテストを組み合わせることで、SQLプロジェクトをより堅牢にすることができます。
本記事では、このテスト機能を用いて本番環境のオブジェクトと検証環境のオブジェクトの結果が完全に一致しているかを検証するテストを作成します。
対象
- DWHとしてSnowflakeを使用している方
- dbtへの移行やリファクタリングを考えている方
- dbtでオリジナルのテストをつくりたいと考えている方
テストとは
dbtのテストはsingulartestとgenerictestの２種類があります。本記事で使用するのはgeneric testsのcustom generic testsです。
https://docs.getdbt.com/docs/build/tests
singular test
最もシンプルな形のテストで、失敗した行を返す SQL クエリを書くことができれば、そのクエリを tests/配下に.sql ファイルに保存することができます。
generic test
引数を受け付けるパラメータ化されたクエリによるテストです。テストクエリは、特別なテストブロック（マクロのようなもの）で定義されます。一度定義すると、.ymlファイル全体でジェネリックテストの名前を参照することができ、モデル、カラム、ソース、スナップショット、シードに定義することができます。
tests/generic/配下に.sql ファイルを配置すれば独自のテストを作成することもできます。
https://docs.getdbt.com/guides/best-practices/writing-custom-generic-tests
完全一致テストとは
完全一致テストは、指定したSQLクエリの出力結果が正しいかどうかを検証します。このタイプのテストは、dbtへの移行やdbtのリファクタリングなど、完全一致の出力が期待される場合に使用されること想定しています。完全一致テストは、データ不足、余分なカラム、または異なる順序のカラムが含まれる場合に失敗します。
完全一致テストを実現するために、Snowflakeの機能「HASG_AGG()」を使用します。HASH_AGGの存在知ったのはこちらの記事でした。
今までファイル同士の比較で検証を行っていたため、部分的な検証しかできず、数値検証にかなりの時間を要していました。しかし、HASH_AGGを使用すれば件数の多いテーブルでも、ものの数十秒で終わります。(70,000,000レコード30カラムのテーブルで30秒くらいでした)
これをdbtのテストに落とし込むことで、継続的なテストを行うことができるので、検証の精度とスピードが段違いで良くなりました。
環境
Snowflake
- ウェアハウス：X-Small
- データベース（本番想定）：TR_KEITA_UEHARA
- データベース（検証想定）：TR_DEV_KEITA_UEHARA
- スキーマ：
- RAW
- DEV_UTA_STAGING（デフォルトスキーマ_カスタムスキーマ）
dbtCloud
フォルダ構成
test_validate_objects.sql
ポイント
-
- 検証環境はtargetスキーマ_customスキーマ、本番環境はcustomスキーマなので、model.schemaからtargetスキーマを除いたものを変数に格納しています。
- 7、10行目
-
- サブクエリ内のクエリをいじれば、別の用途での一致検証に変えることができます。
- 13~15行目
- minusで重複を排除し０件になれば完全一致とみなします。
- ※minusの使用について結果が複数行になる場合は注意が必要です。
_staging_models.yml
ポイント
s_sale.sql
ポイント
- 9、12、15、30行目
- sourceで定義した外部テーブルをデータソースとしています。
残りのファイル内容は記事の最後にまとめています。
結果
完全一致
ハッシュ値
PROD：-7,068,827,162,810,818,942
DEV：-7,068,827,162,810,818,942
データ不足
ハッシュ値
PROD：-7,068,827,162,810,818,942
DEV：-6,511,983,957,286,626,285
余分なカラムが含まれる
ハッシュ値
PROD：-7,068,827,162,810,818,942
DEV：3,215,789,804,469,934,941
カラムの順序が異なる
ハッシュ値
PROD：-7,068,827,162,810,818,942
DEV：-7,097,726,097,789,478,326
まとめ
本番環境のオブジェクトと検証環境のオブジェクト同士の完全一致を検証するテストを紹介しました。ここで紹介したのは「全データでの完全一致」でしたが、test_validate_objects.sqlの7、10行目のSELECT文を工夫することで、様々な条件下での完全一致テストが行えると思います。
このテストをtargetがdevの時だけ実行されるようにできると、開発時のみテストされるようにできると考えています。しかし方法が見つからず、現状は「テスト設定→テスト成功→YAMLから消す」という使い方になってしまいます。。
先日もdbtのイベントに参加させて頂き、同じ境遇の方の話を聴いたり、知らなかった便利な機能を知れたりと、dbtについてとても楽しく学ばせてもらっています。dbtを触り始めたばかりの新米ですが、私も「これいいな！」と思った機能やテクニックがあれば紹介したいと思います！
参考
テストについて
https://docs.getdbt.com/docs/build/tests
https://docs.getdbt.com/guides/best-practices/writing-custom-generic-tests
HASH_AGGについて
https://note.com/ym202110/n/n25340f9a7828
https://docs.snowflake.com/ja/sql-reference/functions/hash_agg
環境の続き
_raw_sources.yml
dbt_project.yml
ポイント
- 36行目
- transient（一時テーブル）での作成をしないようにしています。
- 38~42行目
- targetでデータベース環境を振り分けています。
- 43~46行目
- models配下のディレクトリでスキーマを振り分けています。
get_custom_schema.sql
ポイント
- 5~14行目
- targetがprodの場合、カスタムスキーマが設定されていればスキーマ名は「カスタムスキーマ」となるよう設定
- 16~25行目
- targetがprod以外の場合、カスタムスキーマが設定されていればスキーマ名は「デフォルトスキーマ_カスタムスキーマ」となるよう設定
