はじめに
DMO（Data Management Office）でデータプラットフォームセクションを担当しています東條です。データプラットフォームセクションはOisixを中心としたデータ基盤の構築・整備を担当しています。このデータ基盤にはSnowflakeとdbtを使用しています。今回dbt-core v1.8.0で追加されたUnit testsを導入してわかったことを紹介していきます。
dbtのUnit testsとは
まずは簡単にdbtのUnit testsを紹介します。dbtのUnit testsはソフトウェア開発において実施されるユニットテストをdbtでも実現できるようにしたものです。dbtの1つのmodelをソフトウェアの最小単位としてとらえて、入力データと期待されるデータを準備します。入力データをテスト対象のmodelが処理し、その出力結果と期待されるデータを比較し検証することにより実現しています。dbtのUnit testsを実行することによりmodelのロジック、つまりSQLが意図した通りに動作するかを確認できます。そのため複雑な文字列変換や複雑な文字列による判定、複雑な条件によるcase分などロジックが複雑なSQLの動作検証に適しています。マニュアルにもそのような記載があります。なお、これまでもdbtにはData testsと呼ばれるテスト機能はありました。このテストはデータに対するテスト（例えば、指定された値以外のものが含まれていないかなど）であり、ロジックに対するテストではありませんでした。
dbt Unit testsのサンプルコード
以下では、顧客のステータスに応じて割引金額を算出しているmodelを対象としたUnit testsのサンプルコードを掲載します。 dbtのUnit testsはyamlファイルにunit_testsを定義することにより設定します。（設定内容の詳細については、マニュアルなどを参照ください。）
テスト対象model：int_orders_with_discount
WITH customer_data AS ( SELECT * FROM {{ ref('stg_customers') }} ), order_data AS ( SELECT * FROM {{ ref('stg_orders') }} ), order_added_customer_status AS ( SELECT o.order_id, o.customer_id, o.purchase_amount, c.status FROM order_data AS o JOIN customer_data AS c ON o.customer_id = c.customer_id ), discount_logic AS ( SELECT order_id, customer_id, purchase_amount, CASE WHEN status = 'VIP' THEN purchase_amount * 0.3 -- VIP customers get a 30% discount WHEN purchase_amount >= 100 THEN purchase_amount * 0.2 -- Non-VIP customers get 20% discount for purchases >= 100 ELSE 0 -- No discount for non-VIP customers with < 100 purchase amount END AS discount_amount FROM order_added_customer_status ) SELECT * FROM discount_logic
Unit testsのyamlの定義
- nameには、テスト名を指定する。
- descriptionには、テストの説明を記載する。
- modelには、テスト対象modelを指定する。そのため、テスト対象のmodelであるint_orders_with_discountを指定する。
- givenにはテスト対象modelの入力データを指定する。複数の参照元がある場合には、参照元の分だけinputを指定する。
- expectにはテスト対象modelの期待される出力結果のデータを記載する。
version: 2 unit_tests: - name: test_calculate_discount_for_vip_and_non_vip description: 顧客ステータスに応じて割引を計算するロジックを検証する model: int_orders_with_discount given: - input: ref('stg_orders') rows: - { order_id: 1, customer_id: 101, purchase_amount: 150.00 } - { order_id: 2, customer_id: 102, purchase_amount: 99.99 } - { order_id: 3, customer_id: 103, purchase_amount: 200.00 } - { order_id: 4, customer_id: 104, purchase_amount: 50.00 } - input: ref('stg_customers') rows: - { customer_id: 101, status: "VIP" } - { customer_id: 102, status: "Regular" } - { customer_id: 103, status: "VIP" } - { customer_id: 104, status: "Regular" } expect: rows: - { order_id: 1, customer_id: 101, purchase_amount: 150.00, discount_amount: 45.00, } # VIP customer gets 30% discount - { order_id: 2, customer_id: 102, purchase_amount: 99.99, discount_amount: 0.00, } # Regular customer with < 100 - { order_id: 3, customer_id: 103, purchase_amount: 200.00, discount_amount: 60.00, } # VIP customer gets 30% discount - { order_id: 4, customer_id: 104, purchase_amount: 50.00, discount_amount: 0.00, } # Regular customer with < 100
テストの実行と結果
テストの実行とそのテスト結果は次のとおりです。テスト失敗時には、どこのデータが期待するデータと異なっていたのかがわかります。
# 実行コマンド dbt test --select test_calculate_discount_for_vip_and_non_vip # テスト成功時の結果の抜粋 06:26:35 1 of 1 START unit_test int_orders_with_discount::test_calculate_discount_for_vip_and_non_vip [RUN] 06:26:37 1 of 1 PASS int_orders_with_discount::test_calculate_discount_for_vip_and_non_vip [PASS in 2.21s] # テスト失敗時の結果の抜粋 06:44:37 1 of 1 START unit_test int_orders_with_discount::test_calculate_discount_for_vip_and_non_vip [RUN] 06:44:39 1 of 1 FAIL 1 int_orders_with_discount::test_calculate_discount_for_vip_and_non_vip [FAIL 1 in 2.49s] 06:47:58 Completed with 1 error and 0 warnings: 06:47:58 06:47:58 Failure in unit_test test_calculate_discount_for_vip_and_non_vip (models/unit_tests/test_calculate_discount_for_vip_and_non_vip.yml) 06:47:58 actual differs from expected: @@ ,ORDER_ID,CUSTOMER_ID,PURCHASE_AMOUNT,DISCOUNT_AMOUNT ...,... ,... ,... ,... ,2 ,102 ,99.99 ,0.000 → ,3 ,103 ,200.00 ,70.000→60.000 ,4 ,104 ,50.00 ,0.000
導入の背景
弊社の主力のサービスの1つであるOisixの定期宅配サービスは毎週定期ボックスにお好きな商品を選んでいただきお届けするサービスです。そのため毎週高頻度で新商品が発売されます。データを活用するため、それらの商品を商品データ（商品名や商品属性など）を用いて分類をしています。その分類ロジックをデータ基盤のdbtにより行っており、新商品などにより新しい分類が必要となった場合にはdbtのmodelを修正し対応します。
model修正時の検証として、これまでは上記で述べたdbtのData testsやmodel修正時には本番データによる修正前後のデータ比較などを実施していました。
しかし、この検証には次のような課題がありました。
- 新商品の情報が直前に登録されることもあり、新商品の商品データ（商品名や商品属性など）を使った修正modelの検証ができない。
- 新商品の商品データ（商品名や商品属性など）を使った分類のため、modelのロジックが複雑になり不具合の発生リスクが高い。
これらの課題を放置することは今後の本番データの品質低下を放置することにつながると考えました。そこで課題を解消する手立てがないのかを考えました。そんななかdbt v1.8.0からUnit testsが追加されました。上記で述べたとおり、Unit testsを用いることによりテストデータを用いてmodelのロジックが意図した通りに動作するかを確認できます。そのためまさに我々の課題を解消する手立てとして最適ではないかと考え導入に至りました。
導入の結果
Unit testsを導入することにより、修正modelに対して入力データと期待するデータを準備でき、本番リリース前にmodelのロジックが意図した通りに動作するかを確認できるようになりました。また、テストデータによりmodelのロジックに必要となるデータをすべて準備できるようになるため、複雑なロジックの場合にも漏らさず検証できるようになりました。そのため、Unit testsの導入により抱えていた課題の解消につながりました。一方で今回のUnit testsの導入によりUnit testsの機能としてはまだまだ改善の余地があるということもわかりました。次項以降ではその導入してわかったことを中心に記載します。
導入してわかったこと
実際の導入時は商品分類のロジックを持ったmodelを対象にUnit testsを実施しました。しかしここでは説明をわかりやすくするために上記で紹介したサンプルコードを用いて記載します。
入力データの定義の省略やrefによる参照は利用できない
サンプルコードからもわかるとおりUnit testsを実行するためには、unit_testsのyamlにgivenにより入力データ、expectにより期待するデータを定義する必要があります。サンプルコードでは入力データは2個でしたが、本番の商品分類のロジックを持ったmodelなどは10個近く入力データを持ちます。しかし、修正対象のロジックに関わる入力データはその中の一部の入力データになる場合があります。今回の商品分類のmodelも修正対象のロジックに関わる入力データは1個でした。そこでunit_testsのyamlの準備をできるだけ簡易にできないかと考え、次の試行錯誤を行いました。なお、試行錯誤は入力データstg_customersの定義を変えることにより記載していきます。
試み１：inputを省略する
まず最初はinputを省略してしまうことができないか試みました。結果としては次のエラーとなりできませんでした。inputを省略することにより、dbt runのようにmodel内でref参照しているmodelのテーブルからデータを参照できないかと考えこれを試みました。
試みたunit testsのyaml
- stg_customersのinputを丸ごと省略する。
version: 2 unit_tests: - name: test_calculate_discount_for_vip_and_non_vip description: 顧客ステータスに応じて割引を計算するロジックを検証する model: int_orders_with_discount given: - input: ref('stg_orders') rows: - { order_id: 1, customer_id: 101, purchase_amount: 150.00 } - { order_id: 2, customer_id: 102, purchase_amount: 99.99 } - { order_id: 3, customer_id: 103, purchase_amount: 200.00 } - { order_id: 4, customer_id: 104, purchase_amount: 50.00 } ※expectは変更していないため割愛します。
エラーの結果
Compilation Error in unit_test test_calculate_discount_for_vip_and_non_vip (models/unit_tests/test_calculate_discount_for_vip_and_non_vip.yml) Unit_Test 'unit_test.data_dbt.int_orders_with_discount.test_calculate_discount_for_vip_and_non_vip' (models/unit_tests/test_calculate_discount_for_vip_and_non_vip.yml) depends on a node named 'stg_customers' which was not found
試み２：inputのみを定義する
そこで次は- input:のみを定義する形が取れないのかダメ元で試してみました。結果としては次のエラーとなりできませんでした。
試みたunit testsのyaml
- stg_customersのinputのみを残してrowsをすべて削除する。
version: 2 unit_tests: - name: test_calculate_discount_for_vip_and_non_vip description: 顧客ステータスに応じて割引を計算するロジックを検証する model: int_orders_with_discount given: - input: ref('stg_orders') rows: - { order_id: 1, customer_id: 101, purchase_amount: 150.00 } - { order_id: 2, customer_id: 102, purchase_amount: 99.99 } - { order_id: 3, customer_id: 103, purchase_amount: 200.00 } - { order_id: 4, customer_id: 104, purchase_amount: 50.00 } - input: ref('stg_customers') ※expectは変更していないため割愛します。
エラーの結果
Encountered an error: Parsing Error Unable to find seed 'data_dbt.stg_customers' for unit tests in directories: ['seeds']
試み3：refによる参照を利用する
そこで今度はmodelファイルのようにrefによる参照の形が取れないかを試してみました。結果として次のエラーとなりできませんでした。
試みたunit testsのyaml
- stg_customersのinputのSQLでmodelファイルと同様にrefでstg_customersを参照するように定義する。
version: 2 unit_tests: - name: test_calculate_discount_for_vip_and_non_vip description: 顧客ステータスに応じて割引を計算するロジックを検証する model: int_orders_with_discount given: - input: ref('stg_orders') rows: - { order_id: 1, customer_id: 101, purchase_amount: 150.00 } - { order_id: 2, customer_id: 102, purchase_amount: 99.99 } - { order_id: 3, customer_id: 103, purchase_amount: 200.00 } - { order_id: 4, customer_id: 104, purchase_amount: 50.00 } - input: ref('stg_customers') format: sql rows: | SELECT * FROM {{ ref('stg_customers') }} ※expectは変更していないため割愛します。
エラーの結果
Encountered an error:
Compilation Error
Could not render SELECT * FROM {{ ref('stg_customers') }}
: 'ref' is undefined
結果
結果として入力データのstg_customersは次のように直接テーブルを指定する形を取りました（target.databaseで接続先のデータベースを切り替えています）。入力データ自体を定義する必要がなくなり、inputの定義を簡易にできました。なお、fixtureでも同様な定義ができますが、新たにファイルを追加する必要もあり採用しませんでした。
最終的なunit testsのyaml
version: 2 unit_tests: - name: test_calculate_discount_for_vip_and_non_vip description: 顧客ステータスに応じて割引を計算するロジックを検証する model: int_orders_with_discount given: - input: ref('stg_orders') rows: - { order_id: 1, customer_id: 101, purchase_amount: 150.00 } - { order_id: 2, customer_id: 102, purchase_amount: 99.99 } - { order_id: 3, customer_id: 103, purchase_amount: 200.00 } - { order_id: 4, customer_id: 104, purchase_amount: 50.00 } - input: ref('stg_customers') format: sql rows: | SELECT * FROM {{ target.database }}.staging.stg_customers ※expectは変更していないため割愛します。
Unit testsのyamlの配置について
Unit testsのyamlの配置についても導入にあたり検討しました。dbtの仕様でUnit testsのyamlはmodelsディレクトリ配下に置く必要があります。そのため、導入候補として次の3パターンを検討しました。検討の結果パターン3を採用しました。 パターン3を採用した理由は視認性の良さと本番運用の考慮のしやすさでした。3に対して1は視認性の悪さと1,2は本番運用時に実行されているdbt buildやdbt testでUnit testsが実行されないように考慮する必要があったためです。
- modelディレクトリの各層（intermediateなど）のディレクトリ内にあるmodelのyamlファイルへ追記する。
- modelディレクトリの各層（intermediateなど）のディレクトリ内にUnit testsのyamlファイルを追加する。
- modelディレクトリ内にunit_testsディレクトリを作成し、そこにUnit testsのyamlファイルを配置する。
検討の中でdbt buildやdbt testの実行時にUnit testsを除くには次の方法があることもわかりました。
- dbt buildからUnit testsを除外するには次のどちらかを指定する。
- コマンドオプション：--exclude-resource-type unit_test
- 環境変数：DBT_EXCLUDE_RESOURCE_TYPE=[‘unit_test’]
- dbt testでunit testを除外するには--selectでtest_type:dataを指定する。
サンプルコードを元にdbt buildでUnit testsを除外すると次のようになります。
dbt build -s int_orders_with_discount --exclude-resource-type unit_test # Unit testsを除外する時の出力結果（unit_testが含まれない） Finished running 1 view model, 4 data tests, 4 project hooks in 0 hours 3 minutes and 30.93 seconds (210.93s). # Unit testsを除外しない時の出力結果（unit_testが含まれる） Finished running 1 unit test, 1 view model, 4 data tests, 4 project hooks in 0 hours 3 minutes and 28.69 seconds (208.69s).
その他
上記以外にUnit testsを導入してわかったこととしては、dbt_utils.star macroの不具合やdbt macroのgraph.nodesについてなどがありました。それぞれについては次の通りです。
dbt_utils.star macroの不具合
今回のテスト対象モデルではdbt_utils.star macroを利用しています。dbt_utils.starを使っている場合、こちらの不具合によりUnit testsの実行時にエラーとなります。以下、サンプルコードでdbt_utils.star macroを使うように書き換えて説明します。
サンプルコードのint_orders_with_discountのCTE(customer_data)を次のように書き換えます。
-- 修正前 WITH customer_data AS ( SELECT * FROM {{ ref('stg_customers') }} ), -- 修正後 WITH customer_data AS ( SELECT {{ dbt_utils.star(from=ref('stg_customers'), except=['age']) }} FROM {{ ref('stg_customers') }} ),
このコードに対して、Unit testsを実行すると次のエラーが発生します。
Compilation Error in unit_test test_calculate_discount_for_vip_and_non_vip (models/unit_tests/test_calculate_discount_for_vip_and_non_vip.yml) The `star` macro cannot be used with ephemeral models, as it relies on the information schema. `stg_customers` is an ephemeral model. Consider making it a view or table instead.
では、このエラーに対する対処方法はあるのでしょうか。本件のissueを見ると、その回避案がありました。回避案はUnit testsのoverrides定義を使うことです。overridesはmacroなどの出力を上書きする場合に利用します。そこで次のとおりunit_testsのyamlにdbt_utils.star macroの出力を上書きするためのoverrides定義します。これにより上記のエラーを回避できます。なお、この対応はdbt_utils.starで指定されているmodelが1種類の場合には対処可能ですが、2種類以上の場合には現状対応できず、本不具合の解消を待つ状態となっています。
version: 2 unit_tests: - name: test_calculate_discount_for_vip_and_non_vip description: 顧客ステータスに応じて割引を計算するロジックを検証する model: int_orders_with_discount given: - input: ref('stg_orders') rows: - { order_id: 1, customer_id: 101, purchase_amount: 150.00 } - { order_id: 2, customer_id: 102, purchase_amount: 99.99 } - { order_id: 3, customer_id: 103, purchase_amount: 200.00 } - { order_id: 4, customer_id: 104, purchase_amount: 50.00 } - input: ref('stg_customers') format: sql rows: | SELECT * FROM {{ target.database }}.staging.stg_customers overrides: macros: dbt_utils.star: customer_id, status ※expectは変更していないため割愛します。
dbt macroのgraph.nodesについて
manifest.json（次の画像は抜粋）を参照するとわかりますが、unit_testsはTop-level keysのnodesには存在しません。dbt v1.8から追加されたTop-level keysのunit_testsに存在します。そのためgraph.nodes[リソース]のリソースがData testsの場合には正常に参照できますが、リソースがUnit testsの場合にはnullとなります。弊社ではmacroにおいてselected_resourcesでリソースを取得し、そのリソースをgraph.nodes[リソース]で参照している箇所がありました。そのためselected_resources内にUnit testsのリソースがあった場合にエラーとなりわかりました。
まとめ
実際に導入してみて、使い勝手やmacroに対する不具合など改善の余地はまだまだありそうでした。一方でこれまで検証できていなかった領域に対して検証できるようになり、データ品質のさらなる向上につながると思いました。Unit testsを含めたそれぞれの検証手段ごとに適切な組み合わせをとっていき、データ品質を向上させていければと考えています。
最後に
Oisixには多くのデータ活用の領域が存在します。DMOではデータ基盤の構築・整備だけでなく、これらの領域に対するデータ活用を積極的に推進しております。しかしその活動をさらに広げていくためには仲間が必要です。データに携わる方にとって、Oisixのデータ活用の領域は非常にやりがいを感じていただけるはずです。
アナリティクスエンジニア、データアナリストの方を募集しております。カジュアル面談からでも構いませんので、ぜひお話しましょう。
https://recruit.oisixradaichi.co.jp/application-requirement/
