[dbt] 作成したデータモデルに対してテストを実行する

データ変換もCI/CDで回していく時代や

データ分析

#ビジネス・アナリティクス

#データ分析

#dbt

たまちゃん

2021.01.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

大阪オフィスの玉井です。

dbtはSQLだけで柔軟なデータ変換を作ることが出来ますが、作成したデータに対してテストを実行することができます。

「データをテストする」ということ

ソフトウェアエンジニアリングではテストするのが普通

何らかのアプリケーションを開発されたことのある方ならわかると思いますが、開発したものに対しては、必ずテストを行うと思います。特に、昨今のアプリケーション開発では、テストコードなるものを記述することも珍しくありませんよね（私は「人力+Excelにスクショ貼り付け」の時代しか知らない人間です）。

コードにできるということは、バージョン管理ができるということです。そして、CI/CDの手法がとれるということです。CIというのは、継続的インテグレーションの略で、ざっくり言うと、共有リポジトリにコードをマージした際、自動でビルドとテストをやってくれるというものです。

この、アプリケーション開発では当たり前の概念・手法を取り入れた例としてInfrastructure as Codeがあると思います。インフラ基盤の構成等をコードで運用管理する手法ですね。これにより、インフラ自体も（間接的に？）バージョン管理することができます。私は詳しくありませんが、「インフラCI/CD」という言葉もあるようです。

dbtは生成したデータに対するテストをコードで書ける

dbtは、作成したデータ変換（データモデル）に対して、コードでテストを定義できるようになっています。コードでテストを書けるということは、テスト自体のバージョン管理もできるし、CIも実現できます。

dbtは「アナリティクスエンジニアリング」という考え方を提唱しており、アプリケーション開発の手法をデータ変換作業に用いています。このテスト機能は、まさにそれの一環ですね。

もう少し砕いて言うと、「dbtで作ったテーブルやビューのデータが間違ってないかどうか（要件に沿っているかどうか）テストできる（コードが書ける）」感じです。アプリケーション開発におけるテストコードより、ずっと簡単にできます。

dbtにおけるデータのテスト

dbtでは2種類のテストを定義・実行することができます。

Schema test
- YAMLファイルで定義する
- 「このカラムにNULLがないこと」等の簡易なテストを実行できる
Data test
- SQL（SELECT文）で定義するテスト
- 結果が0件になるようなテストクエリを書いて実行する

やってみた

環境

macOS Catalina 10.15.7
dbt CLI 0.18.1
Google BigQuery

Schema testの設定と実行

テストを実行するモデル

下記のモデルに対してテストを実行します。

こちらは、よくある注文テーブルだと思ってください。

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from orders

こちらは、よくある顧客テーブルです。

select
    id as customer_id,
    first_name,
    last_name

from customers

生成するテーブルには要件があると思います。例えば「顧客IDは、値はそれぞれユニークである（ダブってはいけない）」「オーダーIDにNULLが入っててはいけない」など。当たり前っちゃ当たり前の要件ですが、データをいじくり回しているうちに、こういった要件を破ってしまうことは往々にしてあります。ですので、dbtでそういった条件を予めテストできるようにしましょう。

Schrma testを記述する

定義方法ですが、データモデルのファイルと同じ階層にYAMLファイルを作成して、そこにテストを定義する形となります。で、そのYAMLファイルに定義する内容ですが、今回は以下の通りです。

version: 2

models:
    # テストしたいモデルを指定
    - name: stg_customers
    # テストしたいカラムを指定
      columns: 
        - name: customer_id
        # 実行するテストを指定
          tests:
            - unique
            - not_null
    - name: stg_orders
      columns: 
        - name: order_id
          tests:
            - unique
            - not_null
        - name: status
          tests:
            - accepted_values:
                values:
                  - completed
                  - shipped
                  - returned
                  - placed
                  - return_pending
        - name: customer_id
          tests:
            - relationships:
                to: ref('stg_customers')
                field: customer_id

めちゃくちゃ単純ですね。データモデルの名前→カラムの名前→実行したいテストの種類、という順番に書いていくだけです。

`unique`と`not_null`

例えば、stg_customersのcustomer_idに対しては、uniqueとnot_nullが定義されています。

これは、customer_idの値が一意かつNULLが入ってないかどうかをテストするようになっています。値がダブっていたり、NULLがあったりすると、テストは通らないということです。

`accepted_values`

stg_ordersのstatusに対しては、accepted_valuesというテストが定義されています。

これは、その名の通り、「指定した値以外が入っていないかどうかテストする」というものです。valuesというパラメータで、このカラムの値として許可するものをリストアップします。ここに書いてある値以外が入ってくるとエラーとなります。

`relationships`

stg_ordersのcustomer_idには、relationshipsというテストが定義されています。

これはシンプルにいうと参照整合性をテストするものです。この場合でいうと、stg_ordersのcustomer_idは、必ずstg_customersのcustomer_idに存在することをテストします。顧客マスタに存在しない顧客IDの注文が入っていたらおかしい…ということですね。

昨今のDWHは、その性質上、参照整合性の制約を機能として持たなかったりするので、dbtで参照整合性をテストできるのは、非常に便利だと思います。

実行

記述したSchrma testは、dbt testで実行できます。

$ dbt test
Running with dbt=0.18.1
Found 5 models, 7 tests, 0 snapshots, 0 analyses, 155 macros, 0 operations, 0 seed files, 0 sources

16:04:35 | Concurrency: 4 threads (target='learn')
16:04:35 |
16:04:35 | 1 of 6 START test accepted_values_stg_orders_status__completed__shipped__returned__placed__return_pending [RUN]
16:04:35 | 2 of 6 START test not_null_stg_customers_customer_id................. [RUN]
16:04:35 | 3 of 6 START test not_null_stg_orders_order_id....................... [RUN]
16:04:35 | 4 of 6 START test relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ [RUN]
16:04:38 | 4 of 6 PASS relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ [PASS in 3.03s]
16:04:38 | 2 of 6 PASS not_null_stg_customers_customer_id....................... [PASS in 3.03s]
16:04:38 | 5 of 6 START test unique_stg_customers_customer_id................... [RUN]
16:04:38 | 6 of 6 START test unique_stg_orders_order_id......................... [RUN]
16:04:38 | 1 of 6 PASS accepted_values_stg_orders_status__completed__shipped__returned__placed__return_pending [PASS in 3.05s]
16:04:39 | 3 of 6 PASS not_null_stg_orders_order_id............................. [PASS in 3.45s]
16:04:41 | 5 of 6 PASS unique_stg_customers_customer_id......................... [PASS in 2.96s]
16:04:42 | 6 of 6 PASS unique_stg_orders_order_id............................... [PASS in 3.50s]
16:04:42 |
16:04:42 | Finished running 6 tests in 8.97s.

Completed successfully

Done. PASS=6 WARN=0 ERROR=0 SKIP=0 TOTAL=6

これらのテストが実際にどう行われているかなのですが、先程作成したYAMLファイルの記述に沿って、テスト用のSQLクエリがコンパイルされて実行されます。要するに、テストもSQLで行われるということです（dbtではなくDWH側で行われる処理となる）。

例えば、relationshipsのテストは、下記のように行われていました（コンパイル後のクエリは、プロジェクトの/target/compiled/下に保存されます。DWH側のログを見るのもアリです）。

select count(*) as validation_errors
from (
    select customer_id as id from `スキーマ名`.`stg_orders`
) as child
left join (
    select customer_id as id from `スキーマ名`.`stg_customers`
) as parent on parent.id = child.id
where child.id is not null
  and parent.id is null

このテストクエリの結果が0件だったら、テスト通過となります。

Data testの設定と実行

Data testは、Schema testとは異なり、実際にSELECT文を書いて定義します。テスト用のクエリなので、書き方としては「結果が0件だったらOK」という形になります。個人的な感想なのですが、Schema testでは実現できない、ユーザー独自のテストがある場合は、こちらのData testで定義する感じです。アプリケーション開発におけるテストコードに近いのは、このData testでしょうか。

今回は、公式ドキュメントのサンプルのData test用のクエリを使います。

select
  order_id,
    sum(amount) as total_amount
from {{ ref('stg_payments') }}
group by 1
having not(total_amount >= 0)

stg_paymentsという、支払いデータが入っているテーブルがあるとします。

このData testは、注文ID毎に集計した合計金額がマイナスにならないかどうかをテストします（ファイル名はassert_positive_value_for_total_amount.sqlとします）。システム上、払い戻し等の処理があるため、減額処理の計算の誤り等で、合計金額がマイナスになっていないかどうかチェックする必要があるということですね。

Data testを書いたら、SQLファイルとして、プロジェクトの/tests配下に配置します。基本、1テスト1ファイルにします（1ファイルにSELECT文が1つ）。

実行

これもdbt testで実行できます。ちなみに、dbt test --dataにすると、Data testだけ実行することができます。

$ dbt test --data
Running with dbt=0.18.1
Found 5 models, 7 tests, 0 snapshots, 0 analyses, 155 macros, 0 operations, 0 seed files, 0 sources

16:19:58 | Concurrency: 4 threads (target='learn')
16:19:58 |
16:19:58 | 1 of 1 START test assert_positive_value_for_total_amount............. [RUN]
16:20:02 | 1 of 1 PASS assert_positive_value_for_total_amount................... [PASS in 4.04s]
16:20:02 |
16:20:02 | Finished running 1 test in 5.62s.

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Data testは元々テスト用のクエリをこちらで記述するものなので、コンパイルされるクエリもさほど違いはありません。せっかくなので、実際に実行したBQ側のスクショを貼り付けておきます。

自動テスト

今まで実行してきたテストは、手動でdbtコマンドを叩くものでした。しかし、dbtを使えば、データ変換の自動テストを行うことができます。

今回はdbt CLIを使っていますが、例えばdbt Cloudであれば、クラウド上でdbtを本番運用するためのジョブを設定することができます。

このジョブにテストコマンドを仕込んでおけば、スケジュール実行で定期的にテストを自動実行させることができます。リポジトリへのプルリクエストがあった時に実行するように設定することもできます。これにより、データ変換のCIを実現することができます。新しいデータ変換のロジックやデータモデル等が追加される時、自動でテストが実行され、他のデータモデルに影響が出ていないか（デグレってないか）チェックすることができます。

詳細はこちらをどうぞ。