はじめまして！ビデオリサーチでは、主にデータエンジニアリングを行っている田中です。
最近、iPhoneのマップアプリでオフラインマップをダウンロードしておけば、 ネットの繋がらない山道や災害時でもナビが使えることに便利さを感じました。（僻地のキャンプに行くのに便利です）
さて本記事では、「dbt testの導入」を行った際に初めてだとテストの書き方のイメージがつきづらいと感じた経験をもとに、 よく使いそうな具体例を紹介します。
dbtとは
dbtはdbt Labsが運営する、主にSQLを使用したデータ変換とテストを行うためのツールです。
変換やテストに用いるテーブルやカラム情報は、ymlファイルにて定義できます。その他、テンプレートエンジンのjinjaを使用したマクロを作ることで、SQLでも処理の再利用がしやすい設計になっています。
dbt Labsの展開するサービスとして、フルマネージドのdbt Cloud 、OSSのdbt Coreがあります。 本記事では既に導入済みのデータ統合サービスtroccoで動作することから「dbt Core」を採用し、SQL処理するデータウェアハウスとしてGoogle Cloudの「BigQuery」を採用しています。
dbt testの具体例について
dbt testとはdbtツール機能の1つで、テーブル及びSQL処理の実行結果に対してデータ品質のためのテストを実行できます。
例えばパッケージとして用意されているものだと、以下のようなチェックが可能です。
- 対象カラムにNULL値が含まれないこと
- 対象カラムに指定した値以外が含まれないこと
- 指定したカラムの組み合わせで一意なレコードになること
上記のような簡単なテストであれば、dbt_utilsやdbt expectationsのパッケージを使うことで定義ファイルに容易に組み込むことができます。
定義ファイルはmodelsディレクトリ配下の任意の場所に配置します。
テストを記載した定義ファイルの具体例を以下に記載します。
schema.yml
models:
- name: <dbtモデル名: 作成されるテーブル名>
tests:
- dbt_utils.unique_combination_of_columns: # 指定したカラムの組み合わせで一意なレコードになること
combination_of_columns:
- program_code # 番組を表すコードが入るカラム
- broadcast_start_date # 放送開始日が入るカラム
- broadcast_end_date # 放送終了日が入るカラム
columns:
- name: broadcast_week # 放送曜日が入るカラム
tests:
- not_null # 対象カラムにNULL値が含まれないこと
- dbt_utils.not_empty_string # 対象カラムに空の値が含まれないこと
- report_weeks_string # 特定の曜日名のみ含まれること
各カラムに対するテストはcolumns配下のtestsに記載し、テーブル全体に対するテストはmodels配下に記載します。
パッケージを用いるものはdbt_utils.unique_combination_of_columnsのようにパッケージ名を頭につけて記載します。
なお利用するパッケージは、packages.ymlに記載する必要があります。
上記columns配下にあるreport_weeks_stringはパッケージには含まれていませんが、
macrosディレクトリ配下にjinjaを使ったSQLファイルを作成することで定義ファイルに組み込むことができます。
テストに用いるSQLマクロの具体例を以下に記載します。
report_weeks_string.sql
{% test report_weeks_string(model, column_name) %}
SELECT {{ column_name }}
FROM {{ model }}
WHERE NOT REGEXP_CONTAINS( {{ column_name }}, r'月|火|水|木|金|土|日|,') # 曜日名とカンマ以外の文字列はエラーとする
{% endtest %}
テストに用いるSQLは、条件が一致しない場合にSELECTされるように記述します。
任意のSQLをテスト対象のカラムやテーブルに対してかけることができるので、汎用的な処理を作っておけば、 再利用も設定ファイルへの記載だけで行えるのが、良いポイントだと感じました。
注意点として、dbtで実行するSQLファイルはmacros配下であってもmodels配下であっても、dbtのプロジェクト内でユニークなファイル名称が必要です。 実際にはドメイン名、サービス名等を頭につけるなど命名規則を決めて管理することが望ましいです。
dbt test結果の可視化
上記で定義実行したdbt testの結果はコマンドラインで表示されますが、テスト項目が多いとどこで失敗したかの把握が面倒です。 そのためパッケージとして、dbt-elementaryを追加することで、 下記、画像のようにテスト結果の詳細がhtmlファイルとして生成されます。
以上、dbt testの導入と具体例についてご紹介しました。データ品質の向上やテスト効率化に役立てていただければ幸いです。
最後に
ビデオリサーチでは、現在一緒にサービス開発を推進してくれる仲間を大募集しています！
もしビデオリサーチに興味を持っていただいた方は、以下よりお気軽にご応募ください。
