📌

dbt Test

2024/04/02に公開

dbt

tech

前提

このDocker環境を使用。

Singular data tests

dbtでは作成したテーブルに対して単一データテストを実施することができる。Generic Testも利用可能で、テーブルやマートに対してどのような制約や条件が保証されているのか明確に保証できるのが凄い。

   tables:
     - name: stock_information
       tags: ['default']
       columns:
         - name: Date
           type: DATE
         - name: Code
           type: VARCHAR(10)
         - name: CompanyName
           type: VARCHAR(255)
         - name: MarketCodeName
           type: VARCHAR(100)
         ...
       primary_key:
         - Code

テストは/modesl/stock_information.ymlに次のように記載する

version: 2

models:
  - name: stock_information
    columns:
      - name: Code
        tests:
          - unique

ユニークであるべきCodeカラムでユニークテストを実行し、テストが成功していることが分かる

/workspace # dbt test
15:00:51  Running with dbt=1.7.11
15:00:51  Registered adapter: postgres=1.7.11
15:00:51  Unable to do partial parsing because profile has changed
15:00:53  Found 1 model, 1 test, 2 sources, 0 exposures, 0 metrics, 401 macros, 0 groups, 0 semantic models
15:00:53  Concurrency: 4 threads (target='dev')
15:00:53  1 of 1 START test unique_stock_information_Code ................................ [RUN]
15:00:53  1 of 1 PASS unique_stock_information_Code ...................................... [PASS in 0.21s]
15:00:54  Finished running 1 test in 0 hours 0 minutes and 0.56 seconds (0.56s).
15:00:54  Completed successfully
15:00:54  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

ユニークでないDateカラムに対して同じユニークテストを実施すると、予想通り失敗する([FAIL])

/workspace # dbt test
15:10:10  Running with dbt=1.7.11
15:10:10  Registered adapter: postgres=1.7.11
15:10:11  Found 1 model, 1 test, 2 sources, 0 exposures, 0 metrics, 401 macros, 0 groups, 0 semantic models
15:10:11  Concurrency: 4 threads (target='dev')
15:10:11  1 of 1 START test unique_stock_information_Date ................................ [RUN]
15:10:11  1 of 1 FAIL 1 unique_stock_information_Date .................................... [FAIL 1 in 0.21s]
15:10:11  Finished running 1 test in 0 hours 0 minutes and 0.60 seconds (0.60s).
15:10:11  Completed with 1 error and 0 warnings:
15:10:11  Failure in test unique_stock_information_Date (models/stock_information.yml)
15:10:11    Got 1 result, configured to fail if != 0
15:10:11    compiled Code at target/compiled/jquants_project/models/stock_information.yml/unique_stock_information_Date.sql
15:10:11  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

前提

Singular data tests

Discussion